Index fundamentals are most important concept for any DBA to understand and has lot of value in interviews. Below are some of the common fundamental questions asked about the SQL Server Indexes.
What type of Indexes can a Table consist of in SQL Server 2012?
A table in SQL Server can have below types of Indexes.
One Clustered Index
Index with included columns
what is a Clustered index in sql server 2008 R2? How many clustered Indexes can we create on a table?
Clustered index is an on-disk structure which contain all the columns and rows of the table. We can create only one clustered index as it represents the table itself and we cannot represent the same table in another way, as it is as good as a new table.
what is a Non-Clustered index in sql server 2008 R2? How many clustered Indexes can we create on a table?
Clustered index is an on-disk structure which contains one or more columns as key columns defined while creating the index and also consists of clustered index key column.
Can we create Index on a View?
Yes, we can create indexes on a view and it will be persisted on the database, just like a index created on a table.
Does the Clustered and Non-Clustered Indexes unique data in the key column?
Not necessarily, we can create a Clustered and Non-Clustered Indexes as unique as well and
non-unique where multiple rows can share the same key value.
A table having Primary Key meaning it also has Clustered Index?
Not necessary, by default, creating a Primary key also creates a clustered index, but we can change that behavior and make it create a non-clustered index or no index at all, in which case the table creates unique constraint to maintain the uniqueness in the primary key column.
Can we create Clustered Index without a primary key in the table?
Yes, we can have a table without a primary key, but with a clustered index.
What are Heap tables in SQL Server 2014?
Any table which does not have a Clustered index is called as heap table. But it is recommended to have clustered index on all table, of-course there can be special cases where you do not require a clustered index.
Can Non-clustered indexes be created on Heap tables?
Yes, we can create non-clustered indexes on Heap tables, it is fully supported, but not recommended.
Do we need to perform any regular maintenance on the server?
Yes, we need to regularly perform smart index optimization by rebuilding or reorganizing of all the indexes based on the fragmentation percentage. Also statistics need to be updated on all the indexes with Full Scan preferably to improve performance.
Does Shrink operation has any impact on the Indexes?
Yes, Shrink operation on entire database or data files cause high fragmentation and slowdown the performance significantly, so it is not recommended to Shrink data files. Even in special cases if we have to Shrink a data file, then we need to rebuild the indexes immediately to reduce the fragmentation.
Are creating multiple indexes always improves performance?
Not necessarily, having too many or improper indexes also negatively affects the performance, so only after careful evaluation and testing, proper indexes need to be created.
Can we create Indexes on Views in SQL Server 2012?
Yes, definitely we can create indexes on views.
What kind of indexes can we create on Views?
First index we create on a View must be Unique Clustered Index and then we can create multiple Non-Clustered Indexes.
Do we have to performance any maintenance on the indexed Views to control fragmentation?
Yes, we need to perform regular smart re-indexing and also statistics updates on the indexed views.
Does creating Indexes on View help the performance of queries?
Yes, it will improve the performance, but also can negatively impact the performance in some cases, so test it thoroughly before implementing on Production servers.
Do we need to reference the indexed Views in the query for it to be used by the SQL optimizer?
No, even if we do not reference the indexed views, SQL Server may still use the Indexed Views.
If my table has high number DML(Insert/Update/Delete) operations performed frequently, then does creating indexes improves the performance?
Not always, need to very carefully test and create the indexes which are required which improves the performance and remove and indexes which are not used much. DML operations will have additional overhead with lot of indexes and can slowdown the performance.
Are there any tools which help me identify which indexes to create in SQL Server 2008 R2?
– Yes, with SQL Server Management Studio (SSMS) 2008 R2 or higher, we can generate a execution plan and it also suggests if it finds any missing indexes.
– Also, with regular installation of SQL Server, a tool named Database Tuning Advisor (DTA) is also available, which will help in analyzing a query or trace file data and suggest any missing indexes or statistics.
– We can also use SQL Server built-in DMV’s which provide us list of missing indexes based on the index usage data. This index usage data is removed with restart of SQL Server, so be careful.
The suggestions returned by the tools and DMV’s are not accurate and as a DBA, we need to carefully evaluate and test the indexes on a test server and then only create them on Production servers.
How does the indexes help with SQL Server performance?
With just a table without any indexes, there is only one path to access table data which is by reading entire table in to memory and this data is not stored in any particular order, so for a request which requires specific data, we need to read the entire table data and also at a given point in time, only one session can use all the data, thus blocking other sessions. By creating clustered index, now the data will be stored in a particular order based on the columns chooses as index keys, now this allows us to directly point to the required data with the help of the index keys, thus improving performance. Creating additional non-clustered indexes on few columns improves the performance further, where
multiple queries can access the same data without blocking each other, thus improving the performance further.
What are Filtered Indexes?
Filter Indexes are introduced Starting SQL Server2008, we can can create index on set of columns with only subset of rows by using a WHERE condition, so the index will only have the rows which are satisfied by the where condition.
What are Included Columns in Indexes?
An index contans key columns, which act as pointer to the entire row data and this normally have fewer columns based on where condition, however if the index does not have other columns which are to be returned for the query to complete, SQL Server again have to reach out to clustered index for additional columns which are not present in non-clustered index. We can eliminate additional clustered index search, by including the additional columns in the non-clustered index, but not as key columns. These additional columns are added as “Included Columns”.
This is applicable on below versions of SQL Server
SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014
Hope this was helpful.
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.