Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
kprasadreddy
Starting Member
41 Posts |
 Posted - 2006-11-16 : 09:47:20
|
| want to store 20 digit numeric bank routing in sql server 2005 what datatype to be used? |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-11-16 : 09:51:46
|
| DECIMAL(20, 0)?NUMERIC(20, 0)?(N)VARCHAR(20)?(N)CHAR(20)?Peter LarssonHelsingborg, Sweden |
 |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2006-11-16 : 12:30:36
|
| I assume that it will be fixed at always being exactly 20 digits, so varchar(20) or char(20) are going to use 20 bytes of storage per row, whereas decimal(20, 0) will use 13, which is a fairly significant saving if you're going to have lots of rows (and I assume that it will need to be indexed so you'll get the saving in the index(es) too.). Also, if you definitely will only have digits then decimal provides a certain level of data integrity in that it will not allow values that contain anything other than digits. |
|
|
|
KenW
Constraint Violating Yak Guru
391 Posts |
Posted - 2006-11-16 : 14:18:57
|
| I'd agree with Brett. Varchar(20) would be much better than decimal(20, 0), even though it might use slightly more storage.Bank routing and account numbers often contain leading zero characters, which are significant. These would be stripped using the decimal() datatype, whereas they'd be retained using varchar(). This is worth the insignificant difference (7 bytes/row) in increased storage space.Ken |
|
|
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2006-11-16 : 15:30:25
|
| If there are leading zeros then varchar may be better, that's why I specfically said "I assume that it will be fixed at always being exactly 20 digits".Don't write 7 bytes per row off as insignificant though - not because you can't afford the disk space, but because longer rows mean less rows per page, which means worse performance in a very large table (I also qualified that when I said "if you're going to have lots of rows"). If your row length is 100 bytes, you'll get about 80 rows per page, if it 107 bytes you'll get about 75 rows per page. |
|
|
|
|
|
|
|
|
want to store 20 digit numeric bank routing