Federated primary Key, clustered index, nonclustered index in SQL Server

We all know that when you need more than 2 columns in a table to determine the uniqueness of a record, you need to use the federated primary key, and when the Federated primary Key is established, the performance will be greatly improved when querying the data, but it is not a single query for any column of the Federated primary Key. But we can still improve performance by setting up nonclustered indexes on other columns except the first column in the Federated primary key.
This article illustrates the impact of federated primary keys, clustered indexes, and nonclustered indexes on query performance.
Step one, create a test table, and insert more than 3.5 million data.

/* Create test data table */
CREATE TABLE Mytesttable
(
ID varchar (TEN) is not NULL,
Parent varchar (+) NOT NULL,
Addtime datetime Default (GETDATE ()),
Intcolumn int Default (10),
Bitcolumn bit default (1)
)
Go
/* Add a random string test data time-consuming minutes */
DECLARE @count int=3557643
DECLARE @i int =0
DECLARE @id varchar (), @parent varchar (40)
while (@i< @count)
Begin
Select @id =left (NEWID (), 10)
if (@i% 20=0)
Begin
Select @parent =left (NEWID (), 40)
End
Insert Mytesttable (id,parent) VALUES (@id, @parent)
Select @[email protected]+1
End
Go

Step two, do not establish any index query tests
/* Query when index is not established */
DECLARE @beginTime datetime =getdate ()
DECLARE @elapsedSecond int =0
SELECT * from mytesttable where parent= ' dd7d9f34-3a9c-43ca-836b-f2babd78ce70 ' and id= ' 103ace5c-7 '
Select @elapsedSecond =datediff (microsecond, @beginTime, GETDATE ())
print ' Find data consumption microseconds when index is not established '
Print @elapsedSecond
Select @beginTime =getdate ()
SELECT * from mytesttable where parent= ' f535c18f-bd48-4d45-88df-9653bb9b422d '
Select @elapsedSecond =datediff (microsecond, @beginTime, GETDATE ())
print ' Lookup second column of data consumption microseconds ' when not indexed
Print @elapsedSecond
--(1 row (s) affected)
--Find data consumption microseconds when index is not established
--530000
--(s) affected)
--Find the second column of data consumption microseconds when index is not established
--500000
From the execution results we can see that when there is no index, SQL Server traverses the entire table, so it takes a long time.
Step three, establish the Federated primary key (the clustered index is automatically created) and query the test
Go
/* Establish federated PRIMARY KEY */
ALTER TABLE mytesttable ADD constraint Pk_id_parent primary key (ID asc,parent ASC)
/* Indexed Query */
DECLARE @beginTime datetime =getdate ()
DECLARE @elapsedSecond int =0
SELECT * from mytesttable where parent= ' dd7d9f34-3a9c-43ca-836b-f2babd78ce70 ' and id= ' 103ace5c-7 '
Select @elapsedSecond =datediff (microsecond, @beginTime, GETDATE ())
print ' Find data consumption microseconds ' when indexing
Print @elapsedSecond

Select @beginTime =getdate ()
SELECT * from mytesttable where parent= ' f535c18f-bd48-4d45-88df-9653bb9b422d '
Select @elapsedSecond =datediff (microsecond, @beginTime, GETDATE ())
print ' Find second column of data consumption microseconds ' after index set
Print @elapsedSecond

Go
--(1 row (s) affected)
--Find data consumption microseconds when indexing
--0

--(s) affected)
--Find the second column of data consumption microseconds after indexing
--500000
As can be seen from the above, when a federated primary key is established, querying the first column or querying both columns (and relationships) is very fast and less than 1 subtle, but the second column of the Federated primary key is queried particularly slowly because it cannot be queried by the index.
Step four, set up a nonclustered index for the second column of the Federated primary key, and test
Go
/* Create a nonclustered index for the second column */
Create INDEX index_parent on mytesttable (parent ASC)
DECLARE @beginTime datetime =getdate ()
DECLARE @elapsedSecond int =0
SELECT * from mytesttable where parent= ' dd7d9f34-3a9c-43ca-836b-f2babd78ce70 ' and id= ' 103ace5c-7 '
Select @elapsedSecond =datediff (microsecond, @beginTime, GETDATE ())
print ' Find data consumption microseconds when indexing the second column '
Print @elapsedSecond

Select @beginTime =getdate ()
SELECT * from mytesttable where parent= ' 9a75dc47-ddf7-4922-9179-e87b91fe3921 '
Select @elapsedSecond =datediff (microsecond, @beginTime, GETDATE ())
print ' Find second column of data consumption microseconds after index of second column '
Print @elapsedSecond

--(1 row (s) affected)
--Find data consumption microseconds when indexing the second column
--0

--(s) affected)
--Find the second column of data consumption microseconds after indexing the second column
--0
As you can see from the execution results, it is very fast to query the second column after the index is indexed.
Summarize
In general, for a table T, a federated primary key (A, a, a, b), SQL Server can query from the index at a faster rate when queried in the following cases:
SELECT * from T where A=value and B=value
SELECT * from T where B=value and A=value
SELECT * FROM T where a=value
The following query is not indexed and slower
SELECT * from T where A=value or B=value
SELECT * FROM T where b=value

Reprint: http://www.cnblogs.com/fjchenqian/archive/2011/09/22/2184656.html

Federated primary Key, clustered index, nonclustered index in SQL Server