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 |
|
karlman
Starting Member
11 Posts |
 Posted - 2009-12-17 : 12:41:24
|
| I have a table (SQL 2005) used to hold job ticket information. The selection for the job number has already been made.YYMMDDSSSSSYY - Last 2 digits of yearMM - MonthDD - DaySSSSS - Sequence for that day (resets each day)Example: 09121700001I am not exactly sure the best way to make this my primary key. It is too large for an INT so I figured I would either make it a BIGINT or perhaps make the key a combination of a date field and an int field (sequence). The two field PK seems cleaner but I suspect that the BIGINT would have better performance. Any other sugestions? Perhaps a surrogate key that is simply an int and hold the job number in another field? Also, what is the best method of creating the keys during runtime to prevent duplicate keys? Would this best be handled by a stored procedure?Thank you! |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
karlman
Starting Member
11 Posts |
Posted - 2009-12-17 : 13:52:10
|
quote:
Originally posted by tkizer
You can't use int, bigint, or any of the number data types if you are going to lead the data with a 0 for 2009. You should probably use a CHAR instead, since it's fixed width.I wouldn't split the data up into two columns.
If used BIGINT then I would store the full year and display as required in reporting, folder and file names.Any idea what kind of impact to performance I would have using a CHAR(13) compared with a BIGINT?Thank you!Karl |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
karlman
Starting Member
11 Posts |
Posted - 2009-12-18 : 00:55:59
|
| I will.Thank you!Karl |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
|
|
Primary Key Choice