Learn the distinctions between unique constraint and primary key constraint and the easiest way to get random rows from a table
Q: How does a unique constraint differ from a primary key constraint when enforcing uniqueness on applied columns?
A: A primary key constraint and a unique constraint enforce uniqueness on applied columns in the same way when NULL isn’t involved. A primary key constraint can’t be created on a column that accepts NULL attributes. Many DBAs incorrectly believe that if a column accepts NULL attributes but the column doesn’t contain any NULL attributes, a primary key constraint can be created on that column. Although this is true in other DBMS environments, it isn’t possible in SQL Server. If a SQL Server column is set to accept NULL attributes then you can’t create a primary key constraint on it. The following code tries to create a primary key constraint on a table that accepts NULL attributes.
USE AdventureWorks; GO ( FirstID INT NULL,
SecondID INT NULL )
GO PK_TestTable PRIMARY KEY CLUSTERED
( FirstID, SecondID )
GO This code creates a table called TestTable, but the primary key constraint creation fails with the error message that Figure 1 shows.
A unique constraint can be created on a column that accepts NULL attributes. The table and constraint will both be created successfully. The following code creates a table called TestTable, as well as a unique constraint:
USE AdventureWorks; GO ( FirstID INT NULL,
SecondID INT
NULL )
GO
CONSTRAINT
IX_TestTable1 UNIQUE
( FirstID, SecondID )
GO A table can have maximum of one unique, but a maximum of 249 non-unique indexes. After a NULLable column has been altered to the status of a unique index, only a single instance of NULL can be stored in the table as shown by the following example code:
insert into TestTable1 values (NULL, NULL); -- successful insert into TestTable1 error, violates unique constraint
at the table content
Q: What is the easiest way to retrieve random rows from a table?
A: Several efficient methods exist for retrieving random rows from a database table. One method that’s simple and easy to remember is to use the function NEWID(), as the following code shows.
USE AdventureWorks GO SELECT TOP (10) FirstName, LastName FROM Person.Contact ORDER BY NEWID(); This method works only on SQL Server 2005 and later. The function NEWID() generates a sequencer used in the ORDER BY clause that creates the random order. NEWID() returns a unique value of type uniqueidentifier.
