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
                              CREATE TABLE TestTable                                                                ( FirstID INT NULL,                                SecondID INT NULL )
                              GO
                              ALTER TABLE TestTable ADD CONSTRAINT                                                                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
                              CREATE TABLE TestTable1                                 ( FirstID INT NULL,                                SecondID INT                                NULL )                                 GO 
                              ALTER TABLE TestTable1 ADD                                 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
                              values (NULL, NULL); -- throws an                                                                error, violates unique constraint
                              select * from TestTable1; -- look                                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.