You can extend the functionality of nonclustered indexes by adding nonkey columns to the leaf level of the nonclustered index. By including nonkey columns, you can create nonclustered indexes that cover more queries. This is because the nonkey columns have the following benefits:
They can be data types not allowed as index key columns.
They are not considered by the Database Engine when calculating the number of index key columns or index key size.
An index with included nonkey columns can significantly improve query performance when all columns in the query are included in the index either as key or nonkey columns. Performance gains are achieved because the query optimizer can locate all the column values within the index; table or clustered index data is not accessed resulting in fewer disk I/O operations.
Anything that is in the where clause would be a key, anything in your select statement would be an included column.
When an index contains all the columns referenced by the query it is typically referred to as covering the query.
This blog is useful for Database, Business Intelligence, Bigdata and Data Science professionals.
Subscribe to:
Post Comments (Atom)
Creating DataFrames from CSV in Apache Spark
from pyspark.sql import SparkSession spark = SparkSession.builder.appName("CSV Example").getOrCreate() sc = spark.sparkContext Sp...
-
Optimizing SQL Server CPU Performance A number of factors can affect CPU utilization on a database server: compilation and recompilation of ...
-
This post contains examples of a number of the most commonly performed DBA activities. They are all listed in alphabetical order by the comm...
-
Move distribution database to another drive: There are scenarios in SQL server Replication that we need to move datafiles and logfiles of di...
No comments:
Post a Comment