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 |
|
krittim
Starting Member
6 Posts |
 Posted - 2006-07-25 : 06:46:12
|
| I have a column name GoodsID (Datatype is varchar 50). This is the primary (primary key creates index). It is the combination of 3 characters and 6 numeric values, i.e. MAC100000. Every time the user entering new record in this table I am calculating the max number extracting the primary key- Syntax is: SELECT MAX(CONVERT(numeric, RIGHT(RTRIM(GoodsID), LEN(GoodsID)-3))) AS MaxID FROM GoodsMasterNow my question is if I have a table with 1,00,000 transaction per month, is it the best solution to create the primary key?I'm using indexing as well . |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2006-07-25 : 07:17:56
|
| Why not have two columnsGoodsIDSeqThen maybe Seq could be an identity or calculated as you are now.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
krittim
Starting Member
6 Posts |
Posted - 2006-07-25 : 07:41:31
|
| thanks NR. Can u please explain a little bit more? I didn't get u properly. |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2006-07-25 : 08:06:02
|
| Your GoodsID is actually the combination of two fields. It is probably better to model them as such so the table structure would beGoodsID char(3) ,seq decimal(6,0) -- or intThen to get the max seqselect max(seq) from AS MaxID FROM GoodsMasterIf seq is indexed then it won't have to scan the values like it will as you have it.As you have it the index on GoodsID won't be very useful as it would have to scan all the values to get the max of the numeric part.If you are just doing this to calculate the next value then it could be an identity if you don't mind gaps or calculated and set in a trigger.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
krittim
Starting Member
6 Posts |
Posted - 2006-07-25 : 08:26:42
|
| Thanks a lot NR. |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
|
|
|
Need some Help