Description
In this series, we will dive deeply in the SQL Server Indexing field, starting from the surface by understanding the internal structure of the SQL Server tables and indexes, going deeper by describing the guidelines and best practices that we can follow to design the most efficient index and what operations can be performed on the created indexes. Having these knowledges about the SQL Server indexes, we have all the tools that help us in testing the lower part of the ocean and dive deeper with the two main types of the SQL Server Indexes; the Clustered and Non-Clustered, and the other types of indexes that can be customized to serve us improving your environment. After that, the adventure becomes more interesting when learning how to use this knowledge to tune the performance of our queries and touch the bottom of the ocean. In our way back to the surface, and before celebrating our achievements, we will collect statistical information about these indexes and use this information to maintain the indexes to take benefits from it continuously and gain the best application performance.
SQL Server table structure overview
The tables are the database objects that behave as containers for the data, in which the data will be logically organized in rows and columns format. Tables can be also used as a security boundary/mechanism, where database users can be granted permissions at the table level.
Publish date: March 7, 2018
SQL Server index structure and concepts
SQL Server index is created to speed up the data retrieval and the query processing operations from a database You can imagine the table index as a book’s index that allows you to find the requested information very fast.
Publish date: March 19, 2018
SQL Server index design basics and guidelines
The decision of choosing the right index that fits the system’s workload is not an easy task, as you need to compromise between the speed of data retrieval operations and the overhead of adding that index on the data modification operations
Publish date: April 5, 2018
SQL Server index operations
In this article, we will go through the operations that can be performed on the SQL Server indexes. A SQL Server index can be created using the CREATE INDEX T-SQL statement or from the New Index dialog box using the SQL Server Management Studio tool.
Publish date: April 19, 2018
Designing effective SQL Server clustered indexes
We will see how we could design an effective clustered index that the SQL Server Query Optimizer will always take benefits from. SQL Server allows us to create only one Clustered index per each table, as the data can be sorted in the table using one order criteria.
Publish date: May 3, 2018
Designing effective SQL Server non-clustered indexes
A Non-clustered index is different from a Clustered index in that, the underlying table rows will not be stored and sorted based on the Non-clustered key, and the leaf level nodes of the Non-clustered index are made of index pages instead of data pages.
Publish date: May 7, 2018
Working with different SQL Server indexes types
We will go through the different types of SQL Server indexes (Unique index, Filtered index, Spatial index, XML index), above and beyond Clustered and Non-clustered indexes classification, and when to use them.
Publish date: May 8, 2018
Tracing and tuning queries using SQL Server indexes
We are ready to design the most effective SQL Server index that the SQL Server Query Optimizer will always take benefits from, in speeding up the data retrieval process on our queries, which is the main goal of creating an index, with the minimum disk I/O operations and the least system resources usage.e.
Publish date: May 18, 2018
Gathering SQL Server indexes statistics and usage information
After creating the indexes, we should proactively know which indexes are badly used, or totally unused in order to perform the correct decision to maintain these indexes or replace it with more efficient ones.
Publish date: May 24, 2018
Maintaining SQL Server indexes
One of the most important administration tasks that every database administrator should care about is maintaining database indexes. The index that fits you now, may degrade the performance of your query in the future, due to the different changes performed on your database data or schema.
Publish date: May 31, 2018
He is a Microsoft Certified Solution Expert in Data Management and Analytics, Microsoft Certified Solution Associate in SQL Database Administration and Development, Azure Developer Associate and Microsoft Certified Trainer.
Also, he is contributing with his SQL tips in many blogs.
View all posts by Ahmad Yaseen
- Azure Data Factory Interview Questions and Answers - February 11, 2021
- How to monitor Azure Data Factory - January 15, 2021
- Using Source Control in Azure Data Factory - January 12, 2021