Create Database myindexdemo
Go
Use myindexdemo
Go
Create Table ABC
(
A int not null,
B char (10 ),
C varchar (10)
)
Go
Insert into ABC
Select 1, 'B', 'C'
Union
Select 5, 'B', 'C'
Union
Select 7, 'B', 'C'
Union
Select 9, 'B', 'C'
Go
Select * from ABC
-- Create a clustered index on the ABC table
Create clustered index clu_abc
On ABC ()
Go
-- View Indexes
Sp_helpindex ABC
-- Insert data
Insert into ABC
Values (2, 'B', 'C ')
-- The physical structure of the entire table has changed because of clustered indexes.
-- The content queried Based on the index is:
Select * from ABC with (Index = clu_abc) where a> 1 and a <5
-- After the index is deleted
Drop index ABC. clu_abc
-- The physical order of the query content is still in order.
Select * from ABC
-- Create a non-clustered index on the ABC table
Create nonclustered index nonclu_abc
On ABC ()
-- View Indexes
Sp_helpindex ABC
-- Insert data
Insert into ABC
Values (4, 'B', 'C ')
-- The physical structure of the entire table has changed because of clustered indexes.
-- The query content is:
Select * from ABC with (Index = nonclu_abc)
-- After the index is deleted
Drop index ABC. nonclu_abc
-- The physical order of the query content is in the insert order.
Select * from ABC