SQL Server Index | SQL Server Clustered Index | SQL Server Non Clustered | SQL Server Unique Index

SQL Server Index | SQL Server Clustered Index | SQL Server Non Clustered | SQL Server Unique Index

use DbSuperstore
select * from Orders

create Clustered index IX_ID_Orders
on orders([Row ID])

drop index IX_ID_Orders on orders

create table t9(id int, name varchar(10))
insert into t9 values(14,'Bina')
select * from t9

create index IX_ID_T9 on t9(id asc)
drop index IX_ID_T9 on t9


EXECUTE sp_helpindex t9

alter index ixcl_name_t9 on t9 rebuild

create clustered index ixcl_name_t9 on t9(name desc)

drop table t9


use MyClass

drop table Persontbl

Create Table Persontbl(EmpID int, Fname Varchar(50), Lname Varchar(50),
Dept varchar(30), City Varchar(30))

insert into Persontbl values(124,'Tom','Kim','HR','Tokyo'),
(153,'George','Ray','IT','Berlin'),
(109,'Jay','Dev','Admin','Mumbai'),
(150,'Mike','Roy','Admin','Mumbai'),
(160,'Raj','Kumar','HR','Kualalmpur'),
(120,'Ravi','Kumar','Ops','Rome'),
(144,'Donald','Trump','HR','Paris'),
(136,'Tina','Munim','Admin','Tokyo'),
(137,'Joy','Mike','IT','Rome')

select * from Persontbl order by EmpID
where EmpID}140


create Clustered/Nonclustered Index
{Index_Name} on {Object Name} ({Column_Name asc,Column_Name asc)

Create Clustered Index
Ix_Cl_empID on persontbl(empid)

select * from Persontbl

insert into Persontbl values(110,'Tom','George','Ops','Tokyo')

execute sp_helpindex persontbl

alter Index [Ix_Cl_empID] on persontbl
rebuild/disable

alter Index [Ix_Cl_empID] on persontbl
disable

alter Index [Ix_Cl_empID] on persontbl
rebuild

drop index [Ix_Cl_empID] on persontbl

drop table Persontbl


--Non Clustered Index

Create Table Persontbl(EmpID int, Fname Varchar(50), Lname Varchar(50),
Dept varchar(30), City Varchar(30))

insert into Persontbl values(124,'Tom','Kim','HR','Tokyo'),
(153,'George','Ray','IT','Berlin'),
(109,'Jay','Dev','Admin','Mumbai'),
(150,'Mike','Roy','Admin','Mumbai'),
(160,'Raj','Kumar','HR','Kualalmpur'),
(120,'Ravi','Kumar','Ops','Rome'),
(144,'Donald','Trump','HR','Paris'),
(136,'Tina','Munim','Admin','Tokyo'),
(137,'Joy','Mike','IT','Rome')

select * from Persontbl

create Unique Clustered index IX_NC_Fname
on persontbl(fname)

select * from Persontbl
where fname='Jay'

create clustered index IX_C_LD
on persontbl(lname asc, city asc)

drop index IX_C_LD on persontbl

drop table Persontbl

create table tbz(id int primary key, name varchar(20)
)
insert into tbz values(85,'Kumar')
select * from tbz

create table tbx(id int, name varchar(20))
drop table tbx

create clustered Index IX_C_Name
on tbx(name asc)

insert into tbx values(7, 'Vina')
select * from tbx

alter table tbx
add constraint PK_ID_tbx primary key(id)

alter table tbx
alter column id int not null

-- to set not null on unique then would it be same to primary key???

--Ansesr: No, never, because by default primary key is clustered index
--if already not any clustered index in that table.