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.
CREATE TABLE TestTable ( FirstID INT NULL, SecondID INT NULL )
ALTER TABLE TestTable ADD CONSTRAINT PK_TestTable PRIMARY KEY CLUSTERED ( FirstID, SecondID )
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:
CREATE TABLE TestTable1 ( FirstID INT NULL, SecondID INT NULL ) GO
ALTER TABLE TestTable1 ADD CONSTRAINT IX_TestTable1 UNIQUE ( FirstID, SecondID )
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
- 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.