Category: SQL Server

  • Index Covering

    What is Index Covering?

    Index covering means to add a non-clustered index on every column  which are used in Query. Easy solution to improve query performance

     

    Scenario: Speed Up a Query for a Table with a Clustered Index

    Consider a very typical scenario: you have a table EmployeeSalary table with a clustered index on Employee_ID column. You need to speed up a select query quickly:

    SELECT Salary_DATE, SUM(AMOUNT) FROM EmployeeSalary GROUP BY Salary_DATE

     

    The query’s execution plan is quite simple: the database engine scans the whole clustered index, and then it sorts the intermediate result set to satisfy the GROUP BY clause.

     

    Can a non-clustered index speed up the query? Definitely. Just create a non-clustered index that contains all the columns used in the query:

    CREATE  INDEX sal_amt ON dbo.EmployeeDetail(Salary_DATE, AMOUNT)

     

    Re-execute the query it will run faster than the previous query.  If you, look at the execution plan: the query accesses only the index salary_amt; it doesn’t touch the table at all. In fact, there is no need to access the table because all the columns necessary to satisfy the query are already stored in the index. This is called index covering.

  • How to change SQL Server Instance Name?

    First collect the output of the current instance configuration. You can get the instance name stored in the SQL Server metadata.

    Make sure you have backup of all the database if you are changing the production server instance name.

    <br />
	sp_helpserver<br />
	select @@servername<br />

    You can change the instance name using below query.
    Default Instance 
    <br />
	sp_dropserver 'old_name'<br />
        go<br />
        sp_addserver 'new_name','local'<br />
	go<br />

    Named Instance 

    <br />
	sp_dropserver 'Server Name\old_Instance_name'<br />
        go<br />
        sp_addserver 'ServerName\New Instance Name','local'<br />
	go<br />

    Verify sql server instance configuration by running below queries

    <br />
	sp_helpserver<br />
	select @@servername<br />

    Restart the SQL Server Services.

    <br />
	net stop MSSQLServer<br />
	net start MSSQLServer<br />

    Renaming instances in the AI era

    The procedure above still works — sp_dropserver and sp_addserver haven’t changed. Where this comes up in 2026 is consolidation: before a cloud migration or a container rollout, teams rename and merge instances so connection strings and automation stay sane. Get the naming right once, because every pipeline, agent, and dashboard downstream will hardcode it. If you’re heading down that modernization path, start here: Legacy to Cloud-Native: A Database Modernization Playbook

  • Exception Handling using Try-Catch in SQL Server 2005

    /*
    Error Handling using Try Catch Block
    */

    CREATE PROCEDURE usp_GetErrorInfo
    AS
    BEGIN
    SET NOCOUNT ON
    SELECT
    ERROR_NUMBER() AS ErrorNumber,
    ERROR_SEVERITY() AS ErrorSeverity,
    ERROR_STATE() AS ErrorState,
    ERROR_PROCEDURE() AS ErrorProcedure,
    ERROR_LINE() AS ErrorLine,
    ERROR_MESSAGE() AS ErrorMessage
    END
    GO

    /*
    TRY – CATCH in user defined stored procedure
    */

    ALTER PROCEDURE TEST
    AS
    BEGIN
    BEGIN TRY
    — Generate divide-by-zero error.
    SELECT 1/0;
    END TRY
    BEGIN CATCH
    — Execute error retrieval routine.
    EXECUTE usp_GetErrorInfo;
    END CATCH
    END
    GO

    EXECUTE TEST
    GO;

  • Replication Agents

    Replication Agents

    Replication uses a number of standalone programs, called agents, to carry out the tasks associated with tracking changes and distributing data. By default, replication agents run as jobs scheduled under SQL Server Agent, and SQL Server Agent must be running for the jobs to run. Replication agents can also be run from the command line and by applications that use Replication Management Objects (RMO). Replication agents can be administered from SQL Server Replication Monitor and SQL Server Management Studio.

    Snapshot Agent The Snapshot Agent is typically used with all types of replication. It prepares schema and initial data files of published tables and other objects, stores the snapshot files, and records information about synchronization in the distribution database. The Snapshot Agent runs at the Distributor.


  • Replication in SQL Server 2005

    Replication in SQL Server 2005

    Replication is a set of technologies for copying and distributing data and database objects from one database to another and then synchronizing between databases to maintain consistency. Using replication, you can distribute data to different locations and to remote or mobile users over local and wide area networks, dial-up connections, wireless connections, and the Internet.

    Replication Types

    • Snapshot Replication
    • Transactional Replication
    • Merge Replication