Menu
header photo

SQL-Talk

Talks all about SQL Server and Azure

Index Basics

--step1:
create table books(bid int,bname varchar(40),qty int)
--step2:
insert books values(4,'Oracle',450),(2,'SQL',466)
--step3:
select * from books where bid=2 --ctrl+L (Table Scan)
--
create clustered index indx on books(bid)
select *from books
select * from books where bid=4 --ctrl+L
create index indx2 on books(bname)
select * from books where bname='SQL' --ctrl+L
/*
bname bid
SQL 2
*/
drop index books.indx
select * from books where bname='SQL' --ctrl+L
/*
bname rowid
*/

--INCLUDED COLUMNS AND MISSING INDEX PRACTICE

--Step1: Creating table from existing one
use AdventureWorks2014
go
select * into Add1 from Person.Address
--step2: Placing non clustered index
CREATE NONCLUSTERED INDEX IX_Address_PostalCode
ON Person.Address (PostalCode)
INCLUDE (AddressLine1, AddressLine2, City, StateProvinceID)
GO
--step3: Placing nonclustered without include
CREATE NONCLUSTERED INDEX INDX1 ON Add1(PostalCode)
--step4: Testing the performance
SELECT AddressLine1, AddressLine2, City, StateProvinceID, PostalCode
FROM Person.Address WHERE PostalCode BETWEEN '98000' AND '99999';
GO
SELECT AddressLine1, AddressLine2, City, StateProvinceID, PostalCode
FROM Add1 WHERE PostalCode BETWEEN '98000' AND '99999';
GO