Tags

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


SQL SERVER Indexes – ALL (Covers almost everything) [Continued – Page 2]

https://msbiskills.com/

Download – SQL SERVER Indexes Interview Notes – Pawan Kumar Khowal

Other Index Types

  1. NonClustered Index with Included columns
  2. Covering Index

NonClustered Index with Included columns

1 A NonClustered index can be extended by including nonkey columns in addition to the index key columns. The nonkey columns are stored at the leaf level of the index b+ Tree.
2 The general syntax of a Non Clustered Index with Included column is given below-CREATE INDEX [Index_Name] ON table_name (KeyColumns) INCLUDE (NonKeyColumns)
3 KeyColumns – These columns are used for row restriction and processing E.g they were used in WHERE, JOIN, ORDER BY, GROUP BY etc.
4 NonKeyColumns – These columns are used in SELECT and Aggregation. For e.g. AVG(col) after selection/restriction.
5 So always choose KeyColumns and NonKeyColumns based on the query requirements only
6 Please note that you cannot add a column in Key column section and a NonKey column section.  It is either a key column or a non-key, included column.
7 A nonkey column will only be stored at the leaf level, however. There are some benefits of using non-key columns Columns can be accessed without extra lookups (RID lookup / Clustered Key lookup). It will reduce IO operation and improve performance of queries.
8 Included columns do not count against the 900 byte index key limit enforced by SQL Server.
9 Data types not allowed in key columns are allowed in nonkey columns. All data types but text, ntext, and image are allowed.
10 Included columns do not count against the 900 byte index key limit enforced by SQL Server. Please check out the above example where we have a column in Include column with size greater than 900 byte.
11 Example of NonClustered Index with Included column is given below- 

--

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

--Insert some rows
INSERT INTO testNonClusIncludedColsIndexes VALUES(1,'Pawan',1),(2,'Isha',0)

--Now create a normal nonclustered index
CREATE NONCLUSTERED INDEX Ix_NonCls ON testNonClusIncludedColsIndexes(Name)

--Execute the below query
SELECT Name,OrderStatus FROM testNonClusIncludedColsIndexes
WHERE Name = 'Pawan'

--Now here you can see that even you have NonClusteredIndex still table scan is used. Now let’s drop the above index and create NonClusteredIndex with Included column.

DROP INDEX Ix_NonCls ON testNonClusIncludedColsIndexes

CREATE NONCLUSTERED INDEX Ix_NonClsIncluded ON testNonClusIncludedColsIndexes(Name) INCLUDE(OrderStatus)

--Execute the below query and check the execution plan.

--Now execute and check the execution plan

SELECT Name,OrderStatus FROM testNonClusIncludedColsIndexes
WHERE Name = 'Pawan'

--

Output below in the screen shot

--

NCIWithIncludedColumn

Covering Index

1 In covering index all columns returned in a query are in the available in the index, so no additional reads are required to get the data.
2 A covering index will reduce the IO operations and improve performance of queries.
3 Please find the example of covering index below. 

--

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

--Insert some rows
INSERT INTO testCoveringIndexes VALUES(1,'Pawan',1),(2,'Isha',0),(2,'Isha',0),(2,'Nisha',1),(2,'Isha',0)

CREATE NONCLUSTERED INDEX Ix_Covering ON testCoveringIndexes(Name,OrderStatus)

SELECT Name,OrderStatus FROM testCoveringIndexes
WHERE Name = 'Isha' AND OrderStatus = 0

Output
---------

--

CoveringIndex
4 Now let’s check whether can create index size greater than 900 bytes. NOTE – yes we can but a warning will come. 

--

CREATE TABLE Pawan
(
	 ID INT
	,Name VARCHAR(100)
	,Addre VARCHAR(1000)
)
GO

CREATE UNIQUE NONCLUSTERED INDEX Ix_CoveringInx ON Pawan(Name,Addre)

Output
---------
Warning! The maximum key length is 900 bytes. The index 'Ix_CoveringInx' has maximum length of 1100 bytes. For some combination of large values, the insert/update operation will fail.

--