SSC Eights

Interesting. I had assumed that his table had a PK but he was ignoring it as I'm often forced to. Why? Because it is an Identity Col, we know it is unique, but it is useless. Why is it useless in detecting duplicate rows? Because if you insert the same row row multiple times it will blindly add a new PK value.

Sometimes it is what you want & othertimes things are broken.

eg: For a Grocery Orders Database, 6 consecutive rows each saying "Bottle of milk" is very likely. But for a Tool Booth solution, if the database shows the same car going thru a toll booth 3 times in the same minute. They are likely to be duplicate rows.

Thus it is difficult to have a "one size fits all" solution. Clearly you could have a unique composite key on nearly every column. But that would effectively duplicate the entire table in the index. So unless it is a clustered index, it would be impractical. For the car example above, perhaps a unique index on an alternate key of Number plate & time (to the minute precision) would be sufficient.