![]() ![]() That index will become bigger, which still has some risks, but it is generally better to extend existing indexes compared to introducing new ones. There is little risk to break anything: DROP INDEX idx1 ON MyTable ĬREATE INDEX idx1 ON MyTable (Col1, another_col) INCLUDE (Col2, Col3) Is is rather safe to add another_column to the end of the key-part of the index (after col1). Now you know that Col2 and Col3 are not part of the index tree and are thus not used to narrow the read index range nor for ordering the rows. If you check what indexes you have on that table already, your previous index might still be there: CREATE INDEX idx1 ON MyTable (Col1) INCLUDE (Col2, Col3) To optimize that query, the following index would be great: CREATE INDEX idx1 ON MyTable (Col1, another_col) INCLUDE (Col2) You need to tune this query: SELECT TOP 1 col2 Let's assume this is not the case and we have col2 in the INCLUDE clause because there is just no benefit of having it in the tree part of the index. Both of the following queries would actually prefer the col2 column in the key of the index. Of course you should not put columns in INCLUDE if you can get an additional benefit from having them in the key part. That index is best if your query looks like this: SELECT col2, col3 That makes evolving indexes much more easy in the future.Ĭonsidering your example: CREATE INDEX idx1 ON MyTable (Col1) INCLUDE (Col2, Col3) One reason to prefer INCLUDE over key-columns if you don't need that column in the key is documentation. Notice that if a column - that is added as an included-column of index - is used in the restriction: As long as the index as such can be used (based on restriction against index- key-columns) - then SQL Server is matching the column-restriction against the index (leaf-node-values) instead of going the expensive way around the table itself. The average number of rows per same values in keys (id1, id2. If the columns you consider to add as included-columns are often updated (without the index- key-columns being updated) - or - if it is so many of them that the index becomes close to a copy of your table - use option 1 I'd suggest! Also if adding certain include-column(s) turns out to make no performance-difference - you might want to skip the idea of adding them:) Verify that they are useful! The overhead is the extra memory and CPU used to store redundant info on the index. (The option to include all of these columns as part of the index-key is just always silly (unless they are also used in restrictions) - cause it would always be more expensive to maintain since the index must be updated and sorted even when the "keys" have not changed).Īnswer: If your table is rarely updated - mostly inserted into/deleted from - then it is relatively inexpensive to use the include-mechanism to include some "hot columns" (that are often used in selects - but not often used on restrictions) since inserts/deletes require the index to be updated/sorted anyway and thus little extra overhead is associated with storing off a few extra columns while already updating the index. ![]() colN are columns often selected, but typically not used in restrictions idN are columns often used in restrictions and col1, col2. The question is how expensive it is to use the include-mechanism to include columns that are not really needed in index? (typically not part of where-clauses, but often included in selects). This discussion is missing out on the important point: The question is not if the "non-key-columns" are better to include as index-columns or as included-columns. Obviously, you cannot include every column in every non-clustered index - but if you do have queries which are missing just one or two columns to be "covered" (and that get used a lot), it can be very helpful to INCLUDE those into a suitable non-clustered index. Just by seeking in the non-clustered index and finding your employees for a given department, you have all the necessary information, and the bookmark lookup for each employee found in the index is no longer necessary -> you save a lot of time. ![]() Then all the information you need is available in the leaf level of the non-clustered index. If you had included that lastname in your index: CREATE NONCLUSTERED INDEX NC_EmpDep That can get pretty expensive in terms of performance, if you find a lot of employees. If you happen to have a non-clustered index on (EmployeeID, DepartmentID), once you find the employees for a given department, you now have to do "bookmark lookup" to get the actual full employee record, just to get the lastname column. SELECT EmployeeID, DepartmentID, LastName Imagine you need to query for an employee's ID, department ID, and lastname. ![]() You would use the INCLUDE to add one or more columns to the leaf level of a non-clustered index, if by doing so, you can "cover" your queries. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |