SQL Server Performance Forum – Threads Archive

Numeric or Char?

Hi All, I have 3 fields which are PK. They are supposed to keep digits. If data size is less than what it should be, then it would be paded with leading zeros. For example:
F1 Char(3) value: 027
F2 Char(5) value: 38762
F3 Char(10)value: 0000198723 There is no calculation on these fields. The question is that, what data type is better for these fields? Numeric or Char? If I get them as Numeric, then SQL processes like indexing would be faster? I know in that case I have to convert to Char in my application and pad with leading zeros. Thanks,
Farhad

Well even if there are no calcs done…you’ll have to worry about sorting…and by saying that you are going to lpad them with zeroes, you are infeering char… Look uo datatypes in BOL to see the internal storage sizes… And as far as speed goes…the smaller the better… Brett :cool:

Numeric and decimal now use 5, 9, 13, or 17 bytes of storage. So in your case I would suggest use of Numeric datatype. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

I’ll second satya. Use int or something like that. Numerical data is easier to search and sort. And as you’ve already mentioned, when actually presenting the data you can virtually turn in into anything you like. Frank
http://www.insidesql.de
http://www.familienzirkus.de

<blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by satya Numeric and decimal now use 5, 9, 13, or 17 bytes of storage. So in your case I would suggest use of Numeric datatype. <hr noshade size="1">Satya SKJ Moderator http://www.SQL-Server-Performance.Com/forum</a> <center>This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.</center> <hr height="1" noshade id="quote"></blockquote id="quote"> Do I have an old version of BOL? <blockquote id="quote">quote:<hr height="1" noshade id="quote"> int, bigint, smallint, and tinyint Exact number data types that use integer data. bigint Integer (whole number) data from -2^63 (-922337203685477580<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> through 2^63-1 (9223372036854775807). Storage size is 8 bytes. int Integer (whole number) data from -2^31 (-2,147,483,64<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> through 2^31 – 1 (2,147,483,647). Storage size is 4 bytes. The SQL-92 synonym for int is integer. smallint Integer data from -2^15 (-32,76<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> through 2^15 – 1 (32,767). Storage size is 2 bytes. tinyint Integer data from 0 through 255. Storage size is 1 byte. <hr height="1" noshade id="quote"></blockquote id="quote"> Brett :cool:

I’ve recently updated my SQL 2K BOL from website, may check. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>