Indexes Archives - {coding}Sight

SQL Server Resumable Index: Is it good for you?

Carla Abanes — Tue, 07 Sep 2021 05:02:38 +0000

SQL 2017 introduced the ability to pause and resume index rebuild operations during database maintenance. This feature offers more flexibility to database administrators as it allows them to choose between offline and online reindexing along with pausing and resuming the index rebuild whenever needed. What is Resumable Index in SQL Server? Before the release of …

The post SQL Server Resumable Index: Is it good for you? appeared first on {coding}Sight.

A Simple Use Case for Indexes on Primary Keys

Kenneth Igiri — Mon, 14 Jun 2021 15:13:58 +0000

Introduction Recently we encountered an interesting performance problem on one of our SQL Server databases that process transactions at a serious rate. The transaction table used to capture these transactions became a hot table. As a result, the problem showed up in the application layer. It was an intermittent timeout of the session seeking to …

The post A Simple Use Case for Indexes on Primary Keys appeared first on {coding}Sight.

Hash What? Understanding Hash Indexes

Philipp Zhenov — Fri, 11 Jun 2021 09:40:38 +0000

Hash indexes are an integral part of databases. If you’ve ever used a database, chances are that you have seen them in action without even realizing it. Hash indexes differ in work from other types of indexes because they store values rather than pointers to records located on a disk. This ensures faster searching and …

The post Hash What? Understanding Hash Indexes appeared first on {coding}Sight.

Easy Answers to 5 Vital Questions about Composite Index in SQL

Edwin Sanchez — Wed, 19 May 2021 08:53:38 +0000

Imagine you need to query a table with a WHERE clause on columns a, b, and c. Sometimes, you filter the results with columns a and b only. Should you create 2 indexes? One would be with columns a, b, and c, and another one would be for columns a and b only, right? The …

The post Easy Answers to 5 Vital Questions about Composite Index in SQL appeared first on {coding}Sight.

Clustered and Non Clustered Index: 7 Top Points Explained

Edwin Sanchez — Tue, 18 May 2021 10:46:14 +0000

Indexes are speed-boosters in SQL databases. They can be clustered or non-clustered. But what does it mean and where should you apply each? I know this feeling. I’ve been there. First-timers are often confused about which index to use on which columns. However, even experts need to think this issue through before making a decision, and …

The post Clustered and Non Clustered Index: 7 Top Points Explained appeared first on {coding}Sight.

3 Methods to Rebuild All Indexes for All Tables with T-SQL in SQL Server Database

Nisarg Upadhyay — Fri, 26 Mar 2021 10:36:49 +0000

There are several methods of how to rebuild all indexes of all tables in SQL Server, among them: Using SQL Server maintenance plans. Using T-SQL script based on the fragmentation percent. Using ALTER INDEX command. In this article, we’ll explore these methods and illustrate them with practical examples. 1. SQL Server Rebuild Index Maintenance Plan …

The post 3 Methods to Rebuild All Indexes for All Tables with T-SQL in SQL Server Database appeared first on {coding}Sight.

SQL Server: Renaming Indexes using the sp_rename Procedure

Nisarg Upadhyay — Wed, 24 Mar 2021 10:56:51 +0000

Not so long ago, I was working on a project where we needed to change the data type of a table. The table had millions of rows, and we decided to create a separate table using the existing table definition and insert the rows into that table. After exporting data into the new table, we …

The post SQL Server: Renaming Indexes using the sp_rename Procedure appeared first on {coding}Sight.

22 Different T-SQL Indexes Examples to Warp Speed Your Queries

Edwin Sanchez — Tue, 09 Mar 2021 11:30:31 +0000

Indexing the database tables is one of the ways to up your game in tuning queries. How do you do it? Today’s post will give you 22 SQL index examples of SQL query optimization to run your queries in a flash. You can add an index using CREATE TABLE along a column, or in the …

The post 22 Different T-SQL Indexes Examples to Warp Speed Your Queries appeared first on {coding}Sight.

Virtual Columns and Functional Indexes

Tomas Helgi Johannsson — Wed, 13 Jan 2021 10:06:26 +0000

Much too often, we see poorly written complex SQL queries running against the database tables. Such queries may take a very short or a very long time to execute, but they consume a huge amount of CPU and other resources. Nevertheless, in many cases, complex queries provide valuable information to the application/person. Therefore, it brings …

The post Virtual Columns and Functional Indexes appeared first on {coding}Sight.

SQL Server Indexes Management Using Index Manager for SQL Server

Ahmad Yaseen — Thu, 29 Oct 2020 16:01:38 +0000

SQL Server Index Overview When talking about SQL Server performance tuning and queries enhancement, the first thing to consider is the SQL Server Index. It serves to accelerate reading data from underlying tables by providing quick access to the requested rows. Thus, it won’t need to scan all the table’s records. The SQL Server index …

The post SQL Server Indexes Management Using Index Manager for SQL Server appeared first on {coding}Sight.