This might be a stupid question. I need to have a Id field in a table. This Id is not a key (primary or whatever). The requirement says that user can input 10 digits in this field from the UI. This Id has no relation as of now with any otehr object in the system. Do you think If one can use nvarchar or bigint for this field. Does bigint would be an overhead for this purpose?
A:
There are chances that business can ask you to start storing alphanumeric data in this key. I would suggest that you stick with nvarchar or varchar. Even if it remains numeric and not used anywhere in some kind of computation, it is perfectly valid to have it as varchar.
Pradeep
2010-10-13 09:36:30
+4
A:
If you will ever perform any mathematical work with this column, then obviously an integral type is to be preferred. Alternatively, if this is a pure, opaque identifier, then a char or varchar column would seem to make more sense. And if leading zeros might be important, then obviously the integral types will not work, e.g. if storing a UK phone number, 01234 567890, you'd want that in a char column.
Edit and for bonus points, if it is an id that should always be ten digits:
CREATE TABLE T (
/* Other columns */
Id char(10),
constraint CK_ID_Valid CHECK (LEN(ID)=10 and not ID like '%[^0-9]%')
)
Damien_The_Unbeliever
2010-10-13 09:37:25
This begs a question. What If the Id has a validation (number, only 10 digits) in the UI iteself? Should one still create a constraint on that column?
ydobonmai
2010-10-14 08:21:58
@ydobonmai - I would, I cannot speak for others. If there's any possibility that someone might be manipulating this DB through (for instance) SSMS, then it's an extra safety net. I won't come along in a years time and find strange values
Damien_The_Unbeliever
2010-10-14 08:32:17
@Damien, I would agree. Are you aware of any performance implications of having constraints?
ydobonmai
2010-10-14 08:43:42
@ydobonmai - I've certainly got far more complex constraints than the one illustrated above on my schemas, and not encountered any issues. I think you'd by hard pressed to come up with a CHECK constraint that takes more time during INSERT/UPDATE operations than, for instance, a FOREIGN KEY constraint (since that has to do I/O against another table or index).
Damien_The_Unbeliever
2010-10-14 09:02:28
A:
You may use NUMERIC(10,0) type or if you dont any mathematical operation you can use nvarchar type.
ismailperim
2010-10-13 10:12:02