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 :
- Improve the speed of the execute of queries.
- Inforce uniqueness of data.
- Speed up joins between table
Disadvantage of using indexes
- Take time to c create an index
- Take large amount of disk space to store data along with the original data source on the table
- Gets update each time the data is modified.
Type of index in sql server
Sql server support two type of index.
- Clustered index
- 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 indexINDEX INDEX-NAME,
ON TABLE-NAME(COLUMN-NAME1)
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)
)
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')
select * from test
by default clustered index create on primary key
You can check run this command