Difference between Clustered index and Non-Clustered index
Indexing is way to sort and search
records in the table. It will improve the speed of locating and
retrieval of records from the table
In SQL there are two types of index.
1. Clustered index
2. Non-Clustered index.
Clustered index is unique for any given table
and we can have only one clustered index on a table. The leaf level of a
clustered index is the actual data and the data is resorted in case of
clustered index.
Whereas in case of non-clustered index the leaf level is actually a pointer to the data in rows so we can have as many non-clustered indexes as we can on the db
Whereas in case of non-clustered index the leaf level is actually a pointer to the data in rows so we can have as many non-clustered indexes as we can on the db
In SQL 2005
Clustered index - 1 Clustered index
Non-Clustered index - 249 Non-Clustered index
In SQL 2008
Clustered index - 1 Clustered index
Non-Clustered index - 999 Non-Clustered index
Example
USE Sample
GO
CREATE TABLE Employee
(ID bigint PRIMARY KEY CLUSTERED,
Name nvarchar(150),
Address nvarchar(250),
Salary varchar(25) )
GO
CREATE NONCLUSTERED INDEX EmpName ON Employee(Name)
GO
Example
USE Sample
GO
CREATE TABLE Employee
(ID bigint PRIMARY KEY CLUSTERED,
Name nvarchar(150),
Address nvarchar(250),
Salary varchar(25) )
GO
CREATE NONCLUSTERED INDEX EmpName ON Employee(Name)
GO
Subscribe to:
Post Comments (Atom)
Subscribe to my blog using RSS
0 comments:
Post a Comment