Points to Consider While Writing Queries - Microsoft SQL Server 2012 Performance Tuning - page 268

Getting ready

There is no automatic way to find this behavior; this is simply a manual process. You
either keep this step in mind while developing the database schema or while working
on performance tuning project.

In this recipe, we are going to see the magic of defining a proper primary key and foreign
key relationship.

How to do it...

Follow the steps given here to perform this recipe:

1. First of all, we have to create two tables, for demonstration of this recipe, from the

AdventureWorks2012 database:
IF OBJECT_ID('ProductDemo') IS NOT NULL
DROP TABLE ProductDemo
GO

IF OBJECT_ID('ProductModelDemo') IS NOT NULL
DROP TABLE ProductModelDemo
GO

select * into ProductModelDemo from Production.ProductModel
select * into ProductDemo from Production.Product WHERE
ProductModelID is not null
GO

2. After creating the child table, ProductDemo , and the parent table,

ProductModelDemo , let us make sure that there is no the NULL value
in the foreign key ( ProductModelID ) of the ProductDemo table:
ALTER TABLE ProductDemo
ALTER COLUMN ProductModelID INT NOT NULL
GO

3. Now is the time to create a primary key constraint for the child table, ProductDemo :

ALTER TABLE ProductDemo ADD CONSTRAINT [PK_ProductDemo_ProductID]
PRIMARY KEY CLUSTERED
(
[ProductID] ASC
)
GO