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 |
|
joshherman
Starting Member
13 Posts |
 Posted - 2011-06-02 : 21:10:24
|
| in sql server there 2 type of primary key 1. primary key (generate by system automatically) - autoincrement2. manual key but to be primary key - manualWhich is better used in the database?perhaps in terms of speed or performance databasethank you |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
joshherman
Starting Member
13 Posts |
Posted - 2011-06-02 : 22:58:16
|
| for example i have table SALE:#1 SALE:TRANSNUMBER (INT) -> AUTOINCREMENTDATE (DATETIME)the record generated automatically by system when record created#2 SALE:TRANSNUMBER VARCHAR(9) -> 201101002DATE (DATETIME) |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
joshherman
Starting Member
13 Posts |
Posted - 2011-06-02 : 23:12:02
|
| TRANSNUMBER in #2 generated by Visual Basic. I want to ask is about the performance between #1 and #2 ? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
joshherman
Starting Member
13 Posts |
Posted - 2011-06-03 : 05:45:32
|
| thank you :) |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-06-03 : 06:39:55
|
| Depends on how it is to be used. #2 looks like a meaningful value (natural key rather than artificial key). It could be that you spend a lot of resources getting that value from the autoincrement value in which case it could be less efficient. It could also be held in an int from the format you have given.If it is generated from an application then there might be other issues though - like making sure it ius unique if multiple copies of the application are needed (scalability).==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2011-06-03 : 10:52:48
|
| The problem with incrementing it yourself is the locking you have to do to prevent other threads from using the same one, so you end up locking the max one and everything else has to wait. This does not occur with an identity column. We have this manual increment thing on my most critical system (I didn't design it and want it changed), and that manual increment is painful in regards to performance.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
|
|
|
Primary Key ?