2

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?

3
  • I think you can choose varchar for this
    – vijayp
    Nov 28 '14 at 8:33
  • 5
    As someone who has previously built a football related database, I'd recommend you store home score and away score as two separate int fields. Nov 28 '14 at 8:55
  • 1
    As Mark states, this sounds like a poor design choice. It may not be, but storing this as a string means you can't do anything useful with it. Nov 28 '14 at 11:31
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));
2
  • Why bother suggesting an Oracle solution to an MS-SQL problem? Apr 1 '15 at 15:10
  • One never knows when the environment might be switched. Otherwise, it could be a situation similar to the one when a C++ software developer would always avoid sample Java code. It doesn't hurt, does it?
    – user62547
    Apr 2 '15 at 6:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.