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 |
|
flamblaster
Constraint Violating Yak Guru
384 Posts |
 Posted - 2009-12-21 : 00:52:09
|
| I usually ask questions to help me get through a query, but I've been looking at the database we use (provided by a vendor) and was wondering if any of you might know the reason a developer would choose to create a table with no primary key.The table in question is a trackerlog table which is basically used to audit who created/modified records.When I look at the table in SQL Server 2005, it appears the only identifiers of each record in the trackerlog table is a tracetime and tracedate.There are about 5.5 million records there and the performance for queries against that table are very slow.I'm not sure if it's slow because of the number of records or because the query is searching through the table with no primary key (or both).Anyone have a clue as to the motives for something like this?Thanks! |
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2009-12-21 : 03:07:27
|
| It's probably lazyness. Are there any indexes on the table at all? Any queries will not perform better if you only add a primary key constraint on one or more columns, the performance gain comes from introducing indexes that are designed to suit your queries. If you post the table schema and some common queries I'm sure we'll be able to increase performance quite a bit.- Lumbagohttp://xkcd.com/327/ |
 |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-12-21 : 04:03:06
|
You should listen to Lumbago. Nowadays he's an expert in this matter 
N 56°04'39.26"E 12°55'05.63" |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2009-12-21 : 06:28:53
|
Especially those clustered ones!!  - Lumbagohttp://xkcd.com/327/ |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-12-21 : 06:59:29
|
With the design of your current application, you better be!
N 56°04'39.26"E 12°55'05.63" |
|
|
|
flamblaster
Constraint Violating Yak Guru
384 Posts |
Posted - 2009-12-21 : 10:38:45
|
| Hi guys,Thanks for the quick reply. Yes, there are 7 different indexes on the table, though some of them are null depending on the situation. My queries are doing "ok" I more just wanted to understand 1. why a developer would choose to do that and 2. if there are any benefits to not having a unique identifier. It sounds like lumbago answered #1 :) And it's not sounding like there's an inherent benefit to leaving the PK out. Thanks a lot! I appreciate it. |
|
|
|
|
|
|
Table with No Primary Key