Index Key Column VS Index Included Column

index key columns vs included columns
sql server index include all columns
sql server covering index vs include columns
oracle index include columns
non clustered index on multiple columns
postgres index include
cannot specify included columns for a clustered index.
sql server filtered index include columns

Can someone explain this two - Index Key Column VS Index Included Column?

Currently, I have an index that has 4 Index Key Column and 0 Included Column.

Thanks

Index key columns are part of the b-tree of the index. Included columns are not.

Take two indexes:

CREATE INDEX index1 ON table1 (col1, col2, col3)
CREATE INDEX index2 ON table1 (col1) INCLUDE (col2, col3)

index1 is better suited for this kind of query:

SELECT * FROM table1 WHERE col1 = x AND col2 = y AND col3 = z

Whereas index2 is better suited for this kind of query:

SELECT col2, col3 FROM table1 WHERE col1 = x

In the first query, index1 provides a mechanism for quickly identifying the rows of interest. The query will (probably) execute as an index seek, followed by a bookmark lookup to retrieve the full row(s).

In the second query, index2 acts as a covering index. SQL Server doesn't have to hit the base table at all, since the index provides all the data it needs to satisfy the query. index1 could also act as a covering index in this case.

If you want a covering index, but don't want to add all columns to the b-tree because you don't seek on them, or can't because they aren't an allowed datatype (eg, XML), use the INCLUDE clause.

Should SQL Server index columns be in the key or included?, They can be data types not allowed as index key columns. in the query are included in the index either as key or nonkey columns. Now we see a difference in I/O (95 reads versus 1,560), cost (0.848 vs 1.55), and a subtle  They are not considered by the Database Engine when calculating the number of index key columns or index key size. For example, a varchar(max) column cannot be part of an index key, but it can be an included column. Further, that varchar(max) column doesn't count against the 900-byte (or 16-column) limit imposed for the index key.

Let's think about the book. Every page in the book has the page number. All information in this book is presented sequentially based on this page number. Speaking in the database terms, page number is the clustered index. Now think about the glossary at the end of the book. This is in alphabetical order and allow you to quickly find the page number specific glossary term belongs to. This represents non-clustered index with glossary term as the key column.

Now assuming that every page also shows "chapter" title at the top. If you want to find in what chapter is the glossary term, you have to lookup what page # describes glossary term, next - open corresponding page and see the chapter title on the page. This clearly represents key lookup - when you need to find the data from non-indexed column, you have to find actual data record (clustered index) and look at this column value. Included column helps in terms of performance - think about glossary where each chapter title includes in addition to glossary term. If you need to find out what chapter the glossary term belongs - you don't need to open actual page - you can get it when you lookup the glossary term.

So included column are like those chapter titles. Non clustered Index (glossary) has addition attribute as part of the non-clustered index. Index is not sorted by included columns - it just additional attributes that helps to speed up the lookup (e.g. you don't need to open actual page because information is already in the glossary index).

Example:

Create Table Script

CREATE TABLE [dbo].[Profile](
    [EnrollMentId] [int] IDENTITY(1,1) NOT NULL,
    [FName] [varchar](50) NULL,
    [MName] [varchar](50) NULL,
    [LName] [varchar](50) NULL,
    [NickName] [varchar](50) NULL,
    [DOB] [date] NULL,
    [Qualification] [varchar](50) NULL,
    [Profession] [varchar](50) NULL,
    [MaritalStatus] [int] NULL,
    [CurrentCity] [varchar](50) NULL,
    [NativePlace] [varchar](50) NULL,
    [District] [varchar](50) NULL,
    [State] [varchar](50) NULL,
    [Country] [varchar](50) NULL,
    [UIDNO] [int] NOT NULL,
    [Detail1] [varchar](max) NULL,
    [Detail2] [varchar](max) NULL,
    [Detail3] [varchar](max) NULL,
    [Detail4] [varchar](max) NULL,
PRIMARY KEY CLUSTERED 
(
    [EnrollMentId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

Stored procedure script

CREATE Proc [dbo].[InsertIntoProfileTable]
As
BEGIN
SET NOCOUNT ON
Declare @currentRow int
Declare @Details varchar(Max)
Declare @dob Date
set @currentRow =1;
set @Details ='Let''s think about the book. Every page in the book has the page number. All information in this book is presented sequentially based on this page number. Speaking in the database terms, page number is the clustered index. Now think about the glossary at the end of the book. This is in alphabetical order and allow you to quickly find the page number specific glossary term belongs to. This represents non-clustered index with glossary term as the key column.        Now assuming that every page also shows "chapter" title at the top. If you want to find in what chapter is the glossary term, you have to lookup what page # describes glossary term, next - open corresponding page and see the chapter title on the page. This clearly represents key lookup - when you need to find the data from non-indexed column, you have to find actual data record (clustered index) and look at this column value. Included column helps in terms of performance - think about glossary where each chapter title includes in addition to glossary term. If you need to find out what chapter the glossary term belongs - you don''t need to open actual page - you can get it when you lookup the glossary term.      So included column are like those chapter titles. Non clustered Index (glossary) has addition attribute as part of the non-clustered index. Index is not sorted by included columns - it just additional attributes that helps to speed up the lookup (e.g. you don''t need to open actual page because information is already in the glossary index).'
while(@currentRow <=200000)
BEGIN
insert into dbo.Profile values( 'FName'+ Cast(@currentRow as varchar), 'MName' + Cast(@currentRow as varchar), 'MName' + Cast(@currentRow as varchar), 'NickName' + Cast(@currentRow as varchar), DATEADD(DAY, ROUND(10000*RAND(),0),'01-01-1980'),NULL, NULL, @currentRow%3, NULL,NULL,NULL,NULL,NULL, 1000+@currentRow,@Details,@Details,@Details,@Details)
set @currentRow +=1;
END

SET NOCOUNT OFF
END

GO

Using the above SP you can insert 200000 records at one time.

You can see that there is a clustered index on column "EnrollMentId".

Now Create a non-Clustered index on " UIDNO" Column.

Script

CREATE NONCLUSTERED INDEX [NonClusteredIndex-20140216-223309] ON [dbo].[Profile]
(
    [UIDNO] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

Now Run the following Query

select UIDNO,FName,DOB, MaritalStatus, Detail1 from dbo.Profile 
--Takes about 30-50 seconds and return 200,000 results.

Query 2

select UIDNO,FName,DOB, MaritalStatus, Detail1 from dbo.Profile
where DOB between '01-01-1980' and '01-01-1985'
 --Takes about 10-15 seconds and return 36,479 records.

Now drop the above non-clustered index and re-create with following script

CREATE NONCLUSTERED INDEX [NonClusteredIndex-20140216-231011] ON [dbo].[Profile]
(
    [UIDNO] ASC,
    [FName] ASC,
    [DOB] ASC,
    [MaritalStatus] ASC,
    [Detail1] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

It will throw the following error

Msg 1919, Level 16, State 1, Line 1 Column 'Detail1' in table 'dbo.Profile' is of a type that is invalid for use as a key column in an index.

Because we can not use varchar(Max) datatype as key column.

Now Create a non-Clustered Index with included columns using following script

CREATE NONCLUSTERED INDEX [NonClusteredIndex-20140216-231811] ON [dbo].[Profile]
(
    [UIDNO] ASC
)
INCLUDE (   [FName],
    [DOB],
    [MaritalStatus],
    [Detail1]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

Now Run the following Query

select UIDNO,FName,DOB, MaritalStatus, Detail1 from dbo.Profile --Takes about 20-30 seconds and return 200,000 results.

Query 2

select UIDNO,FName,DOB, MaritalStatus, Detail1 from dbo.Profile
where DOB between '01-01-1980' and '01-01-1985'
 --Takes about 3-5 seconds and return 36,479 records.

SQL Server Index Terms, Key vs. Nonkey Columns. Key columns: the columns specified to create a Included columns do not count against the 900 byte index key limit  Could someone please explain to me what is the main difference between the Index key column and the Included Nonkey columns. Would it be beneficial to add the same column in both the index key and

Included columns don't form part of the key for the index, but they do exist on the index. Essentially the values will be duplicated, so there is a storage overhead, but there is a greater chance that your index will cover (i.e. be selected by the query optimizer for) more queries. This duplication also improves performance when querying, since the database engine can return the value without having to look at the table itself.

Only nonclustered indexes can have included columns, because in a clustered index, every column is effectively included.

Index key or Included columns – SQLServerCentral, Index key column is the column used in the index, and used by the optimizer to decide of the index is applicable. It's also used by the execution  The following illustrates the syntax for creating a non-clustered index with included columns: CREATE [UNIQUE] INDEX index_name ON table_name(key_column_list) INCLUDE (included_column_list); In this syntax: First, specify the name of the index after CREATE INDEX clause. If the index is unique, you need to add the UNIQUE keyword.

Included columns dont form part of the key for the index, but they do exist on the index. Essentially the values will be duplicated Below Take two type of indexes with example column

CREATE clustered INDEX NC_index1 ON tableName (column1, column1, column1,column4)
CREATE clustered INDEX NC_index2 ON tableName (column1) INCLUDE (column2, column3,column4)

NC_index1 is better suited for this kind of query:

SELECT * FROM tableName WHERE column1 = x AND column1 = y AND column1 = z and column4=n

Whereas NC_index2 is better suited for this kind of query:

SELECT column1, column2 FROM tableName WHERE column1 = a

because sql server can't allowed to create index on datatype (eg, XML,text, etc)

Create Indexes with Included Columns, Include nonkey columns in a nonclustered index to avoid exceeding the current index size limitations of a maximum of 32 key columns and a  Index Size. Included columns can’t exceed its size limit, which is 900 byte only for index keys. So, when designing your index with large index key size, only columns used for searching and lookups are key columns, and all other columns that cover the query are non-key columns.

SQL Server Indexes with Included Columns Explained By Examples, By including non-key columns in non-clustered indexes, you can create nonclustered indexes that cover more queries. Note that when an index contains all the  Include nonkey columns in a nonclustered index to avoid exceeding the current index size limitations of a maximum of 32 key columns and a maximum index key size of 1,700 bytes (16 key columns and 900 bytes prior to SQL Server 2016 (13.x)).

Index INCLUDE Clause: How it works and when to use it, The INCLUDE clause for CREATE INDEX is available in some SQL databases: Microsoft The index is solely ordered by its key columns.4 This has two consequences: include Note the emphasis: searching vs. filtering. 1 = Index key column has a descending sort direction. 0 = Index key column has an ascending sort direction, or the column is part of a columnstore or hash index. is_included_column

SQL Server non-clustered indexes with included columns, A column cannot be involved as key and non-key in the same index. It is either a key column or a non-key, included column. The main  create index idx_MyTest on MyTest(ID) include (Name) Case 3: An index on Name including ID. This is the best. The index is built on the column in my WHERE clause, so I get a SEEK operation, and the index covers the query because of the included column. create index idx_MyTest on MyTest(Name) include (ID)

Comments
  • There is slight performance improvement if your unneeded keys are in the included column: logicalread.com/tidbit-sql-server-indexing-part-1
  • What is the disadvantage of adding col2 and col3 as the index key?
  • @Hp93, the disadvantage is that you force SQL Server to maintain a index structure on the columns. The cost of this structure depends on how often the values change. If values rarely change, the cost will be low. If values change frequently, the cost will be higher. In either case, the cost will be greater than INCLUDE, since included values don't affect the relative position of items in the index. Reads are also a bit faster with INCLUDE, since there's less structure to traverse and therefore higher throughput.
  • What is the difference between CREATE INDEX index2 ON table1 (col1) INCLUDE (col2, col3) and CREATE INDEX index2 ON table1 (col1)?
  • INCLUDE adds col1 and col2 to the index b-tree, so if your query doesn't require any other columns from the table, then SQL Server can skip the bookmark lookup to retrieve the full row and satisfy the query directly from the index alone.
  • social.msdn.microsoft.com/Forums/sqlserver/en-US/…
  • +1 IOW the row density of covering indexes is higher, meaning fewer pages need to be fetched by SQL.
  • @nonnb: with a covering index, you don't need a bookmark lookup. Yes, fewer pages are read, and SQL Server has to do less work, but this is because all the information it needs is contained within the index. "Information density" may be higher, for some definition thereof. Not sure what you mean by row density, though.