How to Create Indexes in SQL Server for Better Performance

Indexes play a critical role in improving query performance in SQL Server.
A well-designed index can drastically reduce query execution time, lower CPU usage,
and enhance overall database efficiency. This post explains how to create SQL Server indexes,
why they matter, and how to apply best practices in real-world environments.

Why Indexes Are Important in SQL Server

An index is a data structure that helps SQL Server quickly locate rows in a table
without scanning the entire dataset. Proper indexing is essential for scalable and
high-performance database systems.

• Improves SELECT query performance
• Reduces disk I/O and CPU usage
• Speeds up JOIN, WHERE, and ORDER BY operations
• Helps SQL Server optimize execution plans
• Supports high-volume transactional systems

SQL Server Index Creation Example

Below is a practical example of creating a nonclustered index in SQL Server
to optimize queries that filter data using the CustomerID column.

 -- Create a nonclustered index on the CustomerID column
 CREATE NONCLUSTERED INDEX IX_Orders_CustomerID
 ON dbo.Orders (CustomerID)
 INCLUDE (OrderDate, TotalAmount);
  

Step-by-Step Explanation

• CREATE NONCLUSTERED INDEX defines a nonclustered index structure.
• IX_Orders_CustomerID is a meaningful index name that follows naming conventions.
• dbo.Orders specifies the target table.
• (CustomerID) is the key column used for search and filtering.
• INCLUDE adds non-key columns to avoid key lookups and improve query coverage.

Best Practices and Notes

• Create indexes based on actual query patterns, not assumptions.
• Avoid over-indexing as it increases INSERT, UPDATE, and DELETE overhead.
• Regularly review index usage using sys.dm_db_index_usage_stats.
• Maintain indexes with REBUILD or REORGANIZE to reduce fragmentation.
• Follow official guidance from Microsoft:
  
  SQL Server Indexes Overview
  
• Test index changes in a non-production environment before deployment.

LAST_VALUE(Value) IGNORE NULLS
OVER (
ORDER BY TimestampUTC
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
)

 SELECT
 TimestampUTC,
 Value,
 LAST_VALUE(Value) IGNORE NULLS
 OVER (
 ORDER BY TimestampUTC
 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
 ) AS Filled_Value
 FROM DummyEnergyData;

sqlcmd -S .\SQLEXPRESS -E -Q "EXEC sp_BackupDatabases @backupLocation='D:\SQLBackups\', @backupType='F'"

sqlcmd -U <YourSQLLogin> -P <StrongPassword> -S .\SQLEXPRESS -Q "EXEC sp_BackupDatabases @backupLocation ='D:\SQLBackups', @BackupType='D'"

sqlcmd -S .\SQLEXPRESS -E -Q "EXEC sp_BackupDatabases @backupLocation='D:\SQLBackups\',@backupType='L'"

sqlcmd -S .\SQLEXPRESS -E -Q "EXEC sp_BackupDatabases @backupLocation='D:\SQLBackups\',@backupType='F'"

The sqlcmd utility lets you enter Transact-SQL statements, system procedures, and script files. In SQL Server 2014 and lower versions, the utility is shipped as part of the product. Starting with SQL Server 2016, sqlcmd utility is offered as a separate download. For more information, review sqlcmd Utility.

USE [master]  
GO  


SET ANSI_NULLS ON  
GO  
SET QUOTED_IDENTIFIER ON  
GO  


CREATE PROCEDURE [dbo].[sp_BackupDatabases]   
            @databaseName sysname = null, 
            @backupType CHAR(1), 
            @backupLocation nvarchar(200)  
AS  
       SET NOCOUNT ON;  
            DECLARE @DBs TABLE 
            ( 
                  ID int IDENTITY PRIMARY KEY, 
                  DBNAME nvarchar(500) 
            ) 
            
            INSERT INTO @DBs (DBNAME) 
            SELECT Name FROM master.sys.databases 
            where state=0 
            AND name= ISNULL(@databaseName ,name)
            ORDER BY Name
           
            IF @backupType='F' 
                  BEGIN 
                  DELETE @DBs where DBNAME IN ('tempdb','Northwind','pubs','AdventureWorks') 
                  END 
            ELSE IF @backupType='D' 
                  BEGIN 
                  DELETE @DBs where DBNAME IN ('tempdb','Northwind','pubs','master','AdventureWorks') 
                  END 
            ELSE IF @backupType='L' 
                  BEGIN 
                  DELETE @DBs where DBNAME IN ('tempdb','Northwind','pubs','master','AdventureWorks') 
                  END 
            ELSE 
                  BEGIN 
                  RETURN 
                  END 
           
            DECLARE @BackupName nvarchar(100) 
            DECLARE @BackupFile nvarchar(300) 
            DECLARE @DBNAME nvarchar(300) 
            DECLARE @sqlCommand NVARCHAR(1000)  
        DECLARE @dateTime NVARCHAR(20) 
            DECLARE @Loop int                   
           
            SELECT @Loop = min(ID) FROM @DBs 
      WHILE @Loop IS NOT NULL 
      BEGIN 


      SET @DBNAME = '['+(SELECT DBNAME FROM @DBs WHERE ID = @Loop)+']' 


      SET @dateTime = REPLACE(CONVERT(VARCHAR, GETDATE(),101),'/','') + '_' +  REPLACE(CONVERT(VARCHAR, GETDATE(),108),':','')   


      IF @backupType = 'F' 
            SET @BackupFile = @backupLocation+REPLACE(REPLACE(@DBNAME, '[',''),']','')+ '_FULL_'+ @dateTime+ '.BAK' 
      ELSE IF @backupType = 'D' 
            SET @BackupFile = @backupLocation+REPLACE(REPLACE(@DBNAME, '[',''),']','')+ '_DIFF_'+ @dateTime+ '.BAK' 
      ELSE IF @backupType = 'L' 
            SET @BackupFile = @backupLocation+REPLACE(REPLACE(@DBNAME, '[',''),']','')+ '_LOG_'+ @dateTime+ '.TRN' 


      IF @backupType = 'F' 
            SET @BackupName = REPLACE(REPLACE(@DBNAME,'[',''),']','') +' full backup for '+ @dateTime 
      IF @backupType = 'D' 
            SET @BackupName = REPLACE(REPLACE(@DBNAME,'[',''),']','') +' differential backup for '+ @dateTime 
      IF @backupType = 'L' 
            SET @BackupName = REPLACE(REPLACE(@DBNAME,'[',''),']','') +' log backup for '+ @dateTime 


       IF @backupType = 'F'  
                  BEGIN 
               SET @sqlCommand = 'BACKUP DATABASE ' +@DBNAME+  ' TO DISK = '''+@BackupFile+ ''' WITH INIT, NAME= ''' +@BackupName+''', NOSKIP, NOFORMAT' 
                  END 
       IF @backupType = 'D' 
                  BEGIN 
               SET @sqlCommand = 'BACKUP DATABASE ' +@DBNAME+  ' TO DISK = '''+@BackupFile+ ''' WITH DIFFERENTIAL, INIT, NAME= ''' +@BackupName+''', NOSKIP, NOFORMAT'         
                  END 
       IF @backupType = 'L'  
                  BEGIN 
               SET @sqlCommand = 'BACKUP LOG ' +@DBNAME+  ' TO DISK = '''+@BackupFile+ ''' WITH INIT, NAME= ''' +@BackupName+''', NOSKIP, NOFORMAT'         
                  END 


       EXEC(@sqlCommand) 


SELECT @Loop = min(ID) FROM @DBs where ID>@Loop 
END 

In a text editor, create a batch file that is named Sqlbackup.bat, and then copy the text from one of the following examples into that file, depending on your scenario:

• All the scenarios below use D:\SQLBackups as a place holder. The script needs to be adjusted to the right drive and Backup folder location in your environment.
• If you are using SQL authentication, ensure that access to the folder is restricted to authorized users as the passwords are stored in clear text.

USE AdventureWorks2008R2;
GO
-- Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE AdventureWorks2008R2
SET RECOVERY SIMPLE;
GO explain in points
-- Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (AdventureWorks2008R2_Log, 1);
GO
-- Reset the database recovery model.
ALTER DATABASE AdventureWorks2008R2
SET RECOVERY FULL;
GO

• SIMPLE Model: Truncates log and frees space; no point-in-time recovery.
• Shrink Operation: Reduces the log file size on disk.
• FULL Model: Allows for comprehensive recovery options; log management is necessary.