Tags
2.NonClustered Index, Advanced SQL tutorial pdf, change fill factor while creating NonClustered Index, clustered and non clustered index in sql, Clustered and Nonclustered Indexes Described, Clustered Index, clustered index in sql, Clustered Index vs. Non-Clustered Index in SQL Server, Clustered versus Non Clustered Index, Clustered vs. Nonclustered Index Structures in SQL Server, Covering Index, Covering Index in SQL, Difference Between Clustered and Non-Clustered Indexes, Difference between clustered and nonclustered index, difference between clustered and nonclustered index in sql, difference between clustered and nonclustered index in sql server with example, Download SQL Questions, Download SQL Server Interview Question in PDF, Download SQL SERVER Interview questions, Download SQL Server Interview questions and answers, download sql server interview questions and answers pdf, download sql server interview questions by Pawan Khowal, download sql server interview questions by Pawan Kumar, download sql server interview questions by Pawan Kumar Khowal, Download T-SQL Interview Questions, Fill factor in Indexes, Free Download SQL SERVER Interview questions, Indexes in detail, Learn Indexes in detail, NonClustered Index with Included columns, SQL, SQL Common Interview Questions, SQL Common Interview Questions and answers, SQL FAQ, SQL FAQs, SQL Interview Q & A, SQL Interview Questions, SQL Queries asked in interviews, SQL Questions, SQL Server - General Interview Questions and Answers, SQL Server developer Interview questions and answers, SQL Server developer Interview questions with answers, SQL SERVER Indexes, SQL SERVER Interview questions, SQL SERVER Interview questions & Answers, SQL Server Interview questions and answers, SQL Server Interview Questions and Answers - Free PDF, sql server interview questions and answers for net developers, SQL SERVER Tips, SQL Tips & Tricks, SQL Tips and Tricks, SQL Tricks, T-SQL Server Interview Questions, Types of Indexes, Types of Indexes in SQL
SQL SERVER Indexes – ALL (Covers almost everything) [Continued – Page 2]
Download – SQL SERVER Indexes Interview Notes – Pawan Kumar Khowal
Other Index Types
- NonClustered Index with Included columns
- 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 -- |
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 --------- -- |
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. -- |
point no 9 .
PLease make small correction:
Data types not allowed in key columns are allowed in nonkey columns. All data types but text, ntext, and image are “NOT” allowed.
LikeLike