Tag : index


Covering index and its usage

What is a covering index

A covering index is an index which satisfies all the columns being used in a query without performing a further lookup either to the clustered index or to the base table. It contains all the columns whether they are used in SELECT, JOIN, WHERE, GROUP BY, or ORDER BY clause of the query. Covering index works like a pseudo-clustered index for the query. It improves the performance of the query by adding non-key columns in the index which help the query optimizer to remove any lookup which can be a key lookup or a RID lookup. A key lookup is performed in case the base table accommodates a clustered index otherwise a RID lookup is used by the query optimizer to fetch the non-key columns needed by the query. To know more about key lookup and RID lookup, click here.

The optimizer doesn’t need … More


Index on computed column 2

Today, we are going to learn how we can create index on a computed column which improves the performance of SELECT queries. To create a computed column, we will use an user defined function instead of inline code. Also to know about computed columns, refer my previous blog post “What is Computed Column in sql server“.

Create index on computed column

Let’s create a function, “dbo.fn_GetTotalCourseFee“, which accepts @CourseFee and @TaxPercentage as input parameters and returns the computed @TotalFee amount. To highlight some important prerequisites related to index creation on computed column, I have used a function, otherwise we can also use the inline code in column definition.

Below is the function being used to create a computed column:

CREATE FUNCTION dbo.fn_GetTotalCourseFee
(
 @CourseFee NUMERIC(9, 2),
 @TaxPercentage NUMERIC(9, 2)
)
RETURNS NUMERIC(9, 2)
WITH SCHEMABINDING
AS
BEGIN
 DECLARE @TotalFee NUMERIC(9, 2) = 0.0
 SET @TotalFee = 
More

Creating primary key without clustered index 1

One of my colleague asked me a question that “Can we create a primary key without a clustered index?”. I answered him that “Yes, of-course!” and also did not forget to share this information to all of you in this post named “Create nonclustered primary key”. I know many of us are well aware of this and have did this many times in our day to day SQL activity. Still for those who don’t know how they can accommodate a primary key with a nonclustered index, i am sharing this post, in which i will show you that how we can create a non-clustered primary key on a table.

Before creating this nonclustered primary key, here i am showing you the default behavior of SQL Server during Primary key creation. In case table does not have any Clustered index created on it, SQL Server creates a PRIMARY KEY on a … More


Index Join in sql server 1

Lets explore the topic “Index Join in sql server” starting with “what is index join?”. Index join is a technique which uses an index intersection with two or more indexes to fulfill a query completely. If a table has multiple small index instead of a large covering index, the optimizer can choose index intersection on these small indexes to satisfy a query. In index intersection, it has to perform logical reads on all the indexes being used to serve the query.

If the querying table is large and  it has multiple narrow indexes instead of one wide index, index intersection can be used by sql server to fetch the entire data to be returned.

Example;

Create a table as below;


CREATE TABLE DemoTable
(
EmpID INT,
EmpName VARCHAR(100),
EmpAddress VARCHAR(500),
EmpContact VARCHAR(15),
EmpPinCode VARCHAR(15)
)

Now we have created a table and as per the next step, we have to … More


Bookmark Lookup in sql server 3

What is Bookmark Lookup?

If all the columns required by the query are not available in the nonclustered index itself, a lookup is required to base table to pick those columns which are not part of the nonclustered index being used to retrieve the data. Lookup fetches the corresponding data row from the table by following the row locator value from the index row. It requires extra logical read on data page excluding the logical read on the index page.

Types of lookup

This lookup may be a RID lookup against a clustered index or a key lookup against a  heap.

RID Lookup

If base table does not have any clustered index created on it, a Row ID of heap, will be used as a row locator and if any query which will require a lookup using this heap row id row locator will perform a lookup, the lookup will … More


Clustered Vs NonClustered indexes (When to choose which one)

In this topic we are going to compare clustered indexes with nonclustered indexes with their usage and performance and will also discuss some points regarding the topic “when to choose which one”.  Clustered Vs NonClustered indexes is an old topic and you can get many article written so far. But in this post I will cover the definitions, structure of indexes, syntax of clustered and nonclustered indexes and the most interesting part is “when to choose which one”. So lets explore clustered and nonclustered indexes with “When to use Clustered vs NonClustered indexes” in deep.

Clustered index

As we know that a Clustered indexes sorts and stores the data rows in the table or view based on their key values. These are the columns included in the index definition. And the most important thing is we can have only one clustered index per table, because the data rows themselves can … More


Rebuild indexes dynamically for all databases having fragmentation level more than defined level

Rebuild indexes dynamically for all databases

In this article we are going to learn how we can rebuild indexes for all the databases having fragmentation level more than defined level. Fragmentation level to rebuild the indexes may vary databases to database and In our example we are assuming it as 20%. Which can be suitable for most database servers.
We will also log the details of the indexes before rebuilding them and after rebuilding them in a table to get any report in near future.
And we will exclude all those indexes which have page counts less than 8, i.e. the table is too small and can fit in various different locations intentionally by DB engine.

Log Table Script:

CREATE TABLE MyDB.dbo.cmn_RebuildIndexesLog
(
 in_BatchNo INT,
 in_DBId INT,
 vc_DBName VARCHAR(256),
 vc_SchemaName VARCHAR(256),
 vc_TableName VARCHAR(256),
 vc_IndexName VARCHAR(256),
 num_AvgFragPercent NUMERIC(18,2),
 vc_TypeDesc CHAR(1) --Will store 'B' for before rebuild, 'A' for after rebuild
)

More