The next myth that is quite prevalent is the idea that primary keys are always clustered. While this is true in many cases, you cannot assume that all primary keys are also clustered indexes. Earlier in this book, I discussed how a table can have only a single clustered index on it. If a primary key is created after the clustered index is built, then the primary key will be created as a nonclustered index.
To illustrate the indexing behavior of primary keys, I’ll use another demonstration that includes building two tables. On the first table, named dbo.MythTwo1, I’ll build the table and then create a primary key on the RowID column. For the second table, named dbo.MythTwo2, after the table is created, the script will build a clustered index before creating the primary key. The code for this is in Listing 5.
Listing 5. Two Ways to Create Primary Keys
CREATE TABLE dbo.MythTwo1(RowID int NOT NULL,Column1 nvarchar(128),Column2 nvarchar(128));
ALTER TABLE dbo.MythTwo1 ADD CONSTRAINT PK_MythTwo1 PRIMARY KEY (RowID);
GO
CREATE TABLE dbo.MythTwo2
(RowID int NOT NULL,Column1 nvarchar(128),Column2 nvarchar(128));
CREATE CLUSTERED INDEX CL_MythTwo2 ON dbo.MythTwo2 (RowID);
ALTER TABLE dbo.MythTwo2
ADD CONSTRAINT PK_MythTwo2 PRIMARY KEY (RowID);
GO
SELECT OBJECT_NAME(object_id) AS table_name,name,index_id,type,type_desc,is_unique,is_primary_key
FROM sys.indexes WHERE object_id IN (OBJECT_ID('dbo.MythTwo1'),OBJECT_ID('dbo.MythTwo2'));
After running the code segment, the final query will return results like those shown in Figure 1. This figure shows that PK_MythTwo1, which is the primary key on the first table, was created as a clustered index. Then on the second table, PK_MythTwo2 was created as a nonclustered index.
Figure 1. Primary key sys.indexes output
The behavior discussed in this section is important to remember when building primary keys and clustered indexes. If you have a situation where they need to be separated, the primary key will need to be defined after the clustered index.
For all your application development needs, visit www.verbat.com for a fiscally conscious proposal that meets your needs ( So I can keep this blog going as well!!!!)
Alternatively click through the link if you found this article interesting. (This will help the companies Search engine rankings)
Life Less Ordinary 
Leave a Reply