1

What is the best way to store the following value in SQL Server ? 

1234-56789 or
4567-12892

The value will always have 4 digits followed by a hyphen and 5 digits

char(10) is a possibility that I was thinking of using or removing the hyphen and storing as int

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
DeadlyDan
  • 669
  • 2
  • 8
  • 20
  • It depends what you need to do with that value. Do you, for example, every need to do math with either number, or search on the part after the dash? If not, what is the downside of storing it as a string (varchar)? – sampierson Nov 11 '13 at 23:03
  • Depends on if the format could change in future or if invalid format is possible and has to be allowed. – Tim Schmelter Nov 11 '13 at 23:04

5 Answers5

1

If it is a business requirement to have "The value will always have 4 digits followed by a hypen and 5 digits" Then CHAR(10) but if you think Users should be able to add values even if isnt in the expected format then VARCHAR(10) or VARCHAR(15) whatever suits you better.

M.Ali
  • 67,945
  • 13
  • 101
  • 127
1

You should store those kind of values as int only if really represents a number as opposed to a series of digits. Number means something that you can make calculations on, compare are numbers, etc.

Otherwise store it as char. Make it length of 10 if the format is set and won't change.

Szymon
  • 42,577
  • 16
  • 96
  • 114
1

Another option would be to create a CHAR(4) column and a CHAR(5) column. This would be useful (only) if you envision ever having to query against one or the other part independently.

Very easy to concatenate these back together using a view, computed column, or inline - so you don't have to waste storage space on a dash that will always be there, and so that you can keep these two pieces of data separate if, in fact, they are independent.

Since you didn't provide much detail about what these "numbers" represent or how they will be used / queried, you're going to get a whole bunch of opinions, some of which might not be very relevant to your data model.

Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
1

Well, if it's guaranteed to always be like that, a char(10) datatype seems appropriate.

But you should also add a check constraint:

column LIKE '[0-9][0-9][0-9][0-9]-[0-9][0-9][0-9][0-9][0-9]'
geomagas
  • 3,230
  • 1
  • 17
  • 27
0

Here is a SO answer that should help you sort out what you need -

  1. nchar and nvarchar can store Unicode characters.
  2. char and varcharcannot store Unicode characters.
  3. char and nchar are fixed-length which will reserve storage space for number of characters you specify even if you don't use up all that space.
  4. varchar and nvarchar are variable-length which will only use up spaces for the characters you store. It will not reserve storage like char or nchar.
Community
  • 1
  • 1
Mukus
  • 4,870
  • 2
  • 43
  • 56
  • 1
    This is just copy pasted, in my opinion it fits better as comment. All the more because it's not just about `char` and `varchar`. What is the relevant part that really helps to sort out what he needs? – Tim Schmelter Nov 11 '13 at 23:30
  • That is copy pasted an referenced. You missed the key word there. The options are using char, varchar, nchar, nvarchar and not int, smallint or number. – Mukus Nov 11 '13 at 23:37