Chapter 2. “Yes, we are all individuals” A look at uniqueness in the world of SQL

 

Rob Farley

This chapter looks at the idea of uniqueness, in both database design and query design. I explain the ways in which uniqueness can be enforced and compare the features of each. I then examine the idea of uniqueness within datasets, and challenge some basic methods that people use to create GROUP BY clauses. I hope you gain a new appreciation for uniqueness so that you can echo the Monty Python team in shouting “Yes, we are all individuals!”

Note

For all my examples, I’ll use the AdventureWorks sample database, running on a SQL Server 2005 instance, connecting with SQL Server 2008 R2 Management Studio. I prefer the way that the later versions of SSMS display execution plans, but want to demonstrate functionality that applies in earlier versions as well as the newer ones. You can download AdventureWorks by searching for it at codeplex.com.

Introducing uniqueness

Uniqueness is often taken for granted—we learned about it in our earliest days of database development. But I plan to show you that uniqueness is something that shouldn’t be taken lightly at all. It’s a powerful feature that you should consider carefully—not only when designing databases, but also when writing queries.

Constrained to uniqueness

Unique constraint or unique index?

Uniqueness in results

Summary

About the author