primary key is a varchar but should be number? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

primary key is a varchar but should be number?

Author

Topic

qwertyjjj
Posting Yak Master

131 Posts

Posted - 2007-09-06 : 11:40:54

I have some tables with invoice ids and customer ids from another system.
These are in the format 0000012345
They are all varchar because of the leading zeros.
WOuld it be more efficient to make them integers ?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-09-06 : 11:45:11

if you don't need the leading zeroes than yes it would

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-09-06 : 11:45:30

the performance increase might not be very significant if you use integer instead of varchar. Having varchar do have it's advantages. Especially in your case the IDs are from another system. What if the IDs are alphanumeric ? or your client specify a alphanumeric IDs ?

KH
[spoiler]Time is always against us[/spoiler]

Kristen
Test

22859 Posts

Posted - 2007-09-06 : 11:51:24

Leading zeros are a pain. We have customers who sue them, and every time they paste a list of product codes into Excel it thinks they are numeric and strips off the leading zeros ...

We tell Clients not to use systems that have leading zeros

Kristen

qwertyjjj
Posting Yak Master

131 Posts

Posted - 2007-09-06 : 12:09:37

I think they're varchar(10) or 15.
Any significant difference in that?
If not, I'll leave as is.
Was just thinking that searching through thousands of records would make it slower based on text...

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-09-06 : 12:11:13

not much significance.

search will be slower because you can use an index because you don't know how many leading zeros are there.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-09-06 : 12:15:25

<<
We tell Clients not to use systems that have leading zeros
>>

Also tell your developers to format the number at front end

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2007-09-06 : 12:19:24

They don't need to Madhi, we don't allow leading zeros

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-09-06 : 19:51:59

I don't like leading zeroes, I prefer more trailing zeroes in my bank account (before the decimal point)

KH
[spoiler]Time is always against us[/spoiler]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-09-07 : 02:18:26

quote:
Originally posted by khtan

I don't like leading zeroes, I prefer more trailing zeroes in my bank account (before the decimal point)

KH
[spoiler]Time is always against us[/spoiler]


select 
       balance*1000000000 as balance 
from 
       accounts
where 
       name='KhTan'

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2007-09-07 : 02:39:56

As always Madhi, a nice elegant solution!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources