sql server index

sql index

An index is an internal table structure that sql server uses to provide quick access to rows of a table based on the values of one or more column.

Advantage of using indexes :

1. Improve the speed of the execute of queries.
2. Inforce uniqueness of data.
3. Speed up joins between table

Disadvantage of using indexes

1. Take time to c create an index
2. Take large amount of disk space to store data along with the original data source on the table
3. Gets update each time the data is modified.

Type of index in sql server

Sql server support two type of index.

1. Clustered index
2. None clustered index

Clustered index

• the data is physically sorted.
• Only one clustered index can be created per table

Non-clustered index

• The  physically order of the rows is not the same as the index order
• No-clustered indexes are typically created on columns used in join and where clause and whose value by modified frequently.
• SQL server creates non-clustered indexes by defaults when the create index command is given .
• There can be as many as 249 non-clustered indexes per table
• A clustered index should be created before a none-clustered index.
• A clustered index would need to be rebuilt it is built before a clustered index .
• Clustered index is create on primary key
• Non-clustered index create on foreign key

 Syntax for clustered index

Create [UNIQUE][CLUSTERED]
INDEX INDEX-NAME,
ON TABLE-NAME(COLUMN-NAME1)

Syntax for none clustered index
Create [UNIQUE][NONECLUSTERED]
INDEX INDEX-NAME,
ON TABLE-NAME(COLUMN-NAME1)

More example

At first create a table for testing a index
create table test
(
     id int primary key not null,
     name varchar(50),
     address varchar(70),
     descrip nvarchar(90)
)

Than after creating this table insert value
insert into test values(1,'ram','hgd','cat')
insert into test values(2,'dfs','aaa','cat dog')
insert into test values(3,'sdf','bbb','cat dog cow')
insert into test values(4,'dsf','ccc','cat dog cow')
insert into test values(5,'sdf','zzz','cat dog camale cow')
insert into test values(6,'eew','lll','cat dog camale cow')
insert into test values(7,'asd','ppp','ss')

then show the result

select * from test

by default clustered index create on primary key
You can check  run this command

sp_helpindex test

 

run this command for catering a clustered index

 create clustered index in_test

on test(id)