Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


SQL SERVER Indexes – ALL (Covers almost everything)

https://msbiskills.com/

Download – SQL SERVER Indexes(Almost Everything) Interview Notes – Pawan Kumar Khowal

Index – Important Points

1 An index is a structure stored on the disk. This structure is called B+ Trees.
2 An index can only be created on a table or view.
3 They speeds retrieval of rows from the table or view.
4 An index contains keys built from one or more columns in the table or view.
5 These helps SQL Server to find the row or rows associated with the key values quickly and efficiently.
6 B+ Trees are used to store indexes in SQL SERVER.
7 B+ Tree structure always contains at least 2 levels. Root and leaf level. Number of intermediate levels can vary.
8 Every index will take some space in your database.
9 Index will be modified when DML (Insert, update and delete) operation(s) are performed. This means reordering B+ Tree occurs every time the index changes (Updates, Inserts, and Deletes).
10 Index size can be maximum of 900 bytes.

Update from Expert – Kind of. It’s really that the KEY of an index can be 900 bytes OR 16 columns – whichever comes first. But, even that’s not always true. If a table has an XML column then the PK can have a maximum of 15 columns AND the PK *must* be the CL key.
11 If the table is too small then don’t create indexes on it. In this case we will be wasting our resources because table scan will be faster than index scan.

Update from Expert – Every table should have indexes – even small tables. Indexes can be used to enforce uniqueness and allow for point queries (which do not require index scans). Having said that, I suppose the only case would be a table that has only one page. But, you don’t really care about performance for that table anyway (as far as access). What you might care about there is locking. Indexes can be beneficial to allow SQL Server to lock only the necessary row(s).
12 You can use ALT + F1 to check out what all indexes are created on a table.
13 Indexes are a lot of “trial and error” thing depending on database design, SQL queries, and database size.
14 Sample B+ Tree is given below

Types of Indexes

  1. Clustered Index
  2. NonClustered Index

Clustered Index

1 Clustered index sort and store the data rows based on their key values. These are the columns included in the index definition.
2 We can have only one clustered index per table. We cannot have more than one clustered index per table.
3 Because data rows can be sorted in only one order.
4 At leaf level you will get entire data. Means even if you create clustered index on a single column , at leaf level you will get all the columns means actual data.
5 Clustered Index will be modified when DML (Insert, update and delete) operation(s) are performed. This means reordering B+ Tree occurs every time the index changes (Updates, Inserts, and Deletes). Here physical ordering of the data changes after each DML operation.
6 The CL index leaf level will have ALL columns of the table in it.
7 If a table has Clustered Index it is called Clustered Table.
8 If a table does not contain Clustered Index, then it is stored in Heap. Heap is an unordered structure.
9 Clustered index can be unique or non-unique.
10 If you create primary key, SQL Internally creates unique clustered index and primary key constraint. Primary key is basically a constraint used to enforce uniqueness in a table. The primary key columns cannot hold NULL values. Please check out the example below.

Update – It’s really that the PK defaults to being enforced by a unique clustered index. None of the columns that make up the primary key can allow nulls (or, be nullable). However, if you have a unique constraint then ALL of the columns that make up the unique constraint can allow nulls but no more than one row can be NULL for all columns. With a PK NONE of the columns can allow NULL.
11 If you just create a unique clustered index, one null value will be allowed in the table.
12 Note – Do not use GUID as Clustered Index. It will create fragmentation issues.
13 Don’t create Clustered Index blindly. It should be created based on the business need. Try to make Clustered key’s size small and if possible integer type. Other simple rule is column should be somewhat ordered and frequently queried column. For e.g. EmplD in Employee table. Also please note that these are just simple tips, there might be scenarios where you to choose different data type or multi column clustered key. So all in all I can say choose wisely.
14 We can create Clustered Index with multiple columns also. These types of indexes are called Composite Index.
15 The example of clustered index is given below. 

--

--OPTION 1 -- This is the first way by which we can create clustered index (Primary Key Way)

CREATE TABLE testClusteredIndex
(
	 ID INT PRIMARY KEY
	,Name VARCHAR(50)
	,OrderStatus BIT --can be 0,1 OR NULL
)
GO

SELECT ID,Name,OrderStatus FROM testClusteredIndex
WHERE ID = 1

Output

---------------
Screen shot below

--

CL1


--OPTION 2 -- Explicitly creating CLustered Index using Create Index command

CREATE TABLE testClusteredIndex
(
ID INT
,Name VARCHAR(50)
,OrderStatus BIT --can be 0,1 OR NULL
)
GO

CREATE UNIQUE CLUSTERED INDEX Ix_Cls ON testClusteredIndex(ID)

SELECT ID,Name,OrderStatus FROM testClusteredIndex
WHERE ID = 1

Output

---------------

Screen shot below

---------------

--

Clustered Index

Clustered Index
16 We can change fill factor while creating Clustered Index. See example below. 

--

CREATE UNIQUE CLUSTERED INDEX Ix_Inx ON Pawan(ID)
WITH (FILLFACTOR=80)

--

NonClustered Index

1 A NonClustered index contains index key values and each key value entry has a row locator.
2 The structure of the row locator depends on whether the data pages are stored in a heap or a clustered table.
3 For a clustered table, the row locator is the clustered index key.
4 For a heap, a row locator is a RID pointer to the row.
5 NonClustered indexes can be unique or non-unique.
6 Indexes are internally updated after each DML operation (Insert, Update & Delete).
7 Data is not physically sorted order here.
8 We can have maximum 999 non clustered index per table.
9 A NonClustered index does not affect the ordering and storing of the data.
10 We can create duplicate NonClustered Indexes obviously with different name but we should not because indexes will be updated after every DML operation and we have pay for their maintenance also. Extra Indexes will lead to CPU and disk overhead, so craft indexes carefully and test them thoroughly.
11 Use NonClustered indexes on foreign Keys that are used in joins.
12 The example of NonClustered index is given below. 

--

CREATE TABLE testNonClusIndexes
(
	 ID INT
	,Name VARCHAR(50)
	,OrderStatus BIT --can be 0,1 OR NULL
)
GO

INSERT INTO testNonClusIndexes VALUES(1,'Pawan',1),(2,'Isha',0)

CREATE NONCLUSTERED INDEX Ix_NonCls ON testNonClusIndexes(Name)

SELECT Name FROM testNonClusIndexes
WHERE Name = 'Pawan'

Output
-----------

--

NCL1
13 We can change fill factor while creating NonClustered Index. See example below 

--

CREATE UNIQUE NONCLUSTERED INDEX Ix_NonInx_WithFillFactor ON Pawan(ID) WITH (FILLFACTOR = 90)

--
14 Even If you create a NonClustered primary key, SQL does not allow a single NULL value. E.g. below 

--

CREATE TABLE Pawan
(
	ID INT PRIMARY KEY NONCLUSTERED
)
GO

INSERT INTO Pawan(ID) VALUES(NULL)

Output

------

Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column 'ID', table 'AdventureWorks2012.dbo.Pawan'; column does not allow nulls. INSERT fails.
The statement has been terminated.

--