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.
