How to create a "match result" data type?

Question

I'm building a football match table which contains one column "match result", which should consist of data like "3-1","0-0"... I'm wondering what kind of data type is best appropriate for it, varchar or else? I'm thinking of creating a new data type for this column, using CREATE TYPE statement, whose form is like "integer"+"-"+"integer", is it possible?

Answer 1

Maybe erical already has two columns that keep the "home score and away score as two separate int fields", as Mark and Phil already commented. Then, the remaining part of the question would be whether it is possible to create a user-defined type for a separate string column that stores the match result for faster printing.

So, the answer in this situation would be that yes, it is possible. If unsure about the syntax of the CREATE TYPE statement, it is documented at the following URL:

https://msdn.microsoft.com/en-us/library/ms175007.aspx

The following would be an example of how to do it:

CREATE TYPE MatchType
FROM [VARCHAR](5);

DECLARE @Draw [MatchType];

SET @Draw = '00-00';

DECLARE @Final [MatchType];

SET @Final = '02-01';

SELECT   @Draw  AS [Draw Match]
       , @Final AS [Final Match Sample];

-- Draw Match   Final Match Sample
-- 00-00        02-01

Or, maybe erical was thinking about a composite user defined type. Instead of creating an alias based on an already existing type, what if it were possible to mix two already existing types into a brand new compound?

Microsoft SQL Server does not support that. Instead, Oracle comes with a RECORD type. An example would be the following:

-- Define a user type to hold (HomeScore, AwayScore)
TYPE MatchType IS RECORD
(  HomeScore VARCHAR(2)
 , AwayScore VARCHAR(2));