SQL Server Planet

Adding a primary key can be done either after a table is created, or at the same a table is created. It is important to note, that by default a primary key is clustered. This may or may not be the preferred method of creation. For more information on clustered vs non clustered indexes, please see this article. Either way, you can specify the clustered / non clustered option when creating a primary key.

To create a clustered primary key on an existing table:

ALTER TABLE dbo.Person ADD PRIMARY KEY (PersonID);

To create a non clustered primary key on an existing table:

ALTER TABLE dbo.Person ADD PRIMARY KEY NONCLUSTERED (PersonID);

To create a composite primary key on an existing table:

ALTER TABLE dbo.Person ADD PRIMARY KEY (PersonID, DOB);

For an existing table, there are two places that can be used to specify the primary key. The first is inline to the column. Using this method, you cannot create a composite primary key:

CREATE TABLE [dbo].[Person](
	PersonID [int] IDENTITY(1,1) PRIMARY KEY NONCLUSTERED NOT NULL,
	FirstName VARCHAR(50) NULL
)

The second way is to specify the primary key after the creation of the table, this will enable you to create a composite primary key. Keep in mind that in order to use a column in the primary key, the field must not be nullable.

CREATE TABLE [dbo].[Person](
	PersonID [int] IDENTITY(1,1) NOT NULL,
	FirstName VARCHAR(50) NOT NULL
	PRIMARY KEY NONCLUSTERED (PersonID, FirstName)
)

The SQL Server Select statement is the first statement used when returning data. It is the most used and most important statement in the T-SQL language. The Select statement has many different clauses. We will step through each clause further in the tutorial, however now, we will look at Select itself.

The following

SELECT 'Mike' AS FirstName

As we can see from our results, the code returns one row, one column. The data of the column returned is the value: ‘Mike’. The column’s name is ‘FirstName’.

The ‘AS’ allows you to specify a column alias. This alias could be anything, in this case we made it ‘FirstName’. We can also Select multiple columns of data by separating the columns using a comma:

SELECT 'Mike' AS FirstName, 'Nichols' AS LastName

Returning two columns

These examples so far, have only used the Select statement by itself. Now we will explore using Select in addition with the ‘From’ clause. The From Clause allows a source to be specified for the Select Statement. For the purposes of this example, the source is always a table. Let’s look at an example.

SELECT *
FROM Person.Person

Phonetically the above query is pronounced: Select star from Person dot Person. The star (*) or asterisks is notation for saying “all columns”. Basically, the query is saying give me all columns from the Person table contained within the Person Schema. The person schema is basically a class, or a way to better organize tables. This example will return every column for every row of the Person.Person table. We could also specify specific columns in order to limit the results:

SELECT
	FirstName,
	MiddleName,
	LastName
FROM Person.Person

This query would return all rows for the three columns listed. Now suppose we did not want to return all the columns listed. Instead, we only want to return people with the last name of ‘Adams’. This is where the ‘From’ clause comes in.

SELECT
	FirstName,
	MiddleName,
	LastName
FROM Person.Person
WHERE LastName = 'Adams'

The where clause is followed by the column name we want to filter. The equal sign separates the value we want the column to be equal to. And we place ‘Adams’ within single quotes because it is a string value (or not strictly numeric). This now returns only people that have the last name of Adams.

The Where clause is where the power of SQL begins to get introduced. We can also provide more specifics regarding the rows we want to return by adding additional criteria in the where clause. We do this by specifying the AND statement.

SELECT
	FirstName,
	MiddleName,
	LastName
FROM Person.Person
WHERE LastName = 'Adams' AND FirstName = 'John'

This query now only returns the rows where the FirstName is ‘John’ and LastName is ‘Adams’. Suppose we wanted either people with the last name of ‘Adams’ OR people with the last name of ‘Smith’. In order to return either of these names, we use the OR statement.

SELECT
	FirstName,
	MiddleName,
	LastName
FROM Person.Person
WHERE LastName = 'Adams' OR LastName = 'Smith'

Comparing the results of two different stored procedures output can be difficult task. The first difficulty is to return the result sets of the stored procedures into temporary tables. There are two different methods of doing this outlined in the link above. For this example we will use the local linked server method. Your method may differ depending upon whether your system setup will support one of the methods. It may also differ if you are looking at passing dynamic parameters (this method is also covered in the link above).

After the result sets are in the temp tables, we need to compare the datasets. We typically need to see the data in one of three ways:

1. What is in one result set and not in the other
2. All results that do not match
3. All results that do match

First, let’s dump the procedures into tables:

-- Where local server is the name we gave to our local linked server
SELECT *
INTO #Procedure1
FROM OPENQUERY(LOCALSERVER, 'set fmtonly off exec dbo.Procedure1 param')

SELECT *
INTO #Procedure2
FROM OPENQUERY(LOCALSERVER, 'set fmtonly off exec dbo.Procedure2 param')

Now, considering we have SQL 2005 and above, we will compare what is in Procedure1 that does not match what is in procedure2. We do this using the new EXCEPT Statement.

-- Give us what IS in procedure1
SELECT *
FROM #Procedure1

EXCEPT
-- That does not match procedure2
SELECT *
FROM #Procedure2

Next, we will get what is in procedure2 and not in procedure1.

-- Give us what IS in procedure2
SELECT *
FROM #Procedure2

EXCEPT
-- That does not match procedure1
SELECT *
FROM #Procedure1

The next result set comparison is to get all results from both procedures that do not match. We do this using the CHECKSUM function. We do not need SQL 2005+ for this.

SELECT *
FROM (
	SELECT ID
		,ChkSum = CHECKSUM(*)
	FROM #Procedure1 ds
) ds
JOIN(
	SELECT ID
		,ChkSum = CHECKSUM(*)
	FROM #Procedure2 ps
) ps
ON ds.ID = ps.ID
AND ds.ChkSum <> ps.ChkSum

Lastly, let’s get what does match in both procedures. We can do this using either INTERSECT (2005+) or CHECKSUM.

-- Using intersect, we don't need to worry about the order of the table selects
SELECT *
FROM #Procedure1
INTERSECT
SELECT *
FROM #Procedure2 ds

SELECT *
FROM (
	SELECT ID
		,ChkSum = CHECKSUM(*)
	FROM #Procedure1 ds
) ds
JOIN(
	SELECT ID
		,ChkSum = CHECKSUM(*)
	FROM #Procedure2 ps
) ps
ON ds.ID = ps.ID
AND ds.ChkSum <> ps.ChkSum