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.
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 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
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
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
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
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
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
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
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
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