Understanding Indexing in SQL Server: Types and Usage

What is an Index?   

An index in SQL Server is a data structure associated with a table or view that speeds up the retrieval of rows based on the values in one or more columns. It serves as a well-organized reference guide, allowing SQL Server to efficiently locate rows that match query criteria without scanning the entire table.

Types of Indexes:

1. Clustered Index: Determines the physical order of data in a table, affecting the order of data when modified.
2. Non-clustered Index: Creates a separate structure with sorted references to actual data rows, useful for enhancing SELECT query performance.
3. Unique Index: Ensures uniqueness of values in the indexed column(s) across the table, aiding in data integrity.
4. Covering Index: Includes all columns needed to fulfill a query, minimizing I/O operations and improving query performance.
5. Filtered Index: Includes only a subset of rows in the table based on a WHERE clause, useful for optimizing queries targeting specific subsets of data.
6. Spatial Index: Specialized for spatial data types, facilitating efficient spatial queries such as distance calculations and intersections.
7. Columnstore Indexes: Organizes data by columns, beneficial for analytical queries involving aggregations and scans across large datasets.

Usage of Indexes:

 Faster Data Retrieval: Provides a shortcut to desired rows, reducing the time to locate and retrieve data, particularly helpful for SELECT queries.  
Optimizing Joins: Indexes on join columns enhance performance by quickly identifying matching rows.  
Sorting and Grouping: Speed up ORDER BY and GROUP BY operations by efficiently retrieving and organizing data.  
Constraint Enforcement: Unique indexes ensure data integrity by preventing duplicate values in indexed columns.  
Covering Queries: Minimizes I/O operations and speeds up query execution by scanning the index alone.  
Reducing I/O Operations: Efficient use of indexes minimizes I/O operations required to satisfy a query.

Best Practices for Indexing:

1. Selective Indexing: Focus on columns frequently used in WHERE clauses, JOIN conditions, and ORDER BY clauses to avoid unnecessary overhead.
2. Regular Maintenance: Monitor and maintain indexes regularly, including rebuilding or reorganizing to minimize fragmentation.
3. Avoid Over-Indexing: Strike a balance between performance gains and maintenance overhead to avoid diminishing returns.
4. Consider Clustered Index Carefully: Choose based on typical table queries and access patterns.
5. Use Indexing Tools: Leverage tools such as the Database Engine Tuning Advisor to recommend appropriate indexes based on query performance analysis.
6. Understand Query Execution Plans: Analyse plans to identify areas where indexes can optimize query performance.

Conclusion:  

Indexes in SQL Server play a crucial role in enhancing query speed by enabling quicker data retrieval and minimizing the need for full-table scans. Selecting the right type of index and adhering to best practices, including regular maintenance and thorough understanding of database access patterns, are vital for extracting maximum benefits from indexing. 

How to identify duplicate indexes along with columns in SQL Server?

To get the key column list from indexes that are duplicates in SQL Server, you can use the following query:

use databasename
go

WITH DuplicateIndexes AS (
    SELECT 
        i.OBJECT_ID,
        i.index_id
    FROM 
        sys.index_columns ic
    JOIN 
        sys.indexes i ON i.OBJECT_ID = ic.OBJECT_ID 
                     AND i.index_id = ic.index_id
    WHERE 
        i.type_desc <> 'HEAP' 
		AND OBJECT_NAME(i.OBJECT_ID) NOT LIKE '%sys%' --excluding system tables
    GROUP BY 
        i.OBJECT_ID, i.index_id
    HAVING 
        COUNT(*) > 1 -- to check duplicates 
)

SELECT 
    SCHEMA_NAME(o.schema_id) AS SchemaName,
    OBJECT_NAME(ic.OBJECT_ID) AS TableName,
    i.name AS IndexName,
    STRING_AGG(c.name, ', ') WITHIN GROUP (ORDER BY ic.key_ordinal) AS IndexedColumns
FROM 
    sys.index_columns ic
JOIN 
    sys.indexes i ON i.OBJECT_ID = ic.OBJECT_ID 
                 AND i.index_id = ic.index_id
JOIN 
    sys.objects o ON o.OBJECT_ID = ic.OBJECT_ID
JOIN 
    sys.columns c ON ic.OBJECT_ID = c.OBJECT_ID 
                 AND ic.column_id = c.column_id
JOIN 
    DuplicateIndexes di ON di.OBJECT_ID = ic.OBJECT_ID 
                        AND di.index_id = ic.index_id
GROUP BY 
    o.schema_id, ic.OBJECT_ID, i.name;
  

This query first identifies the indexes that are duplicates, and then retrieves the table name, index name, and the key column list for each duplicate index.

Execute this query in your SQL Server management tool to get the key column list from indexes that are duplicates in your database.

How to Review transaction order and lock acquisition in SQL Server

In SQL Server, you can review the transaction order and lock acquisition by analysing the queries and transactions that are being executed against the database. Here are some approaches to review transaction order and lock acquisition:

1. Transaction isolation levels:

   • Review the transaction isolation levels used in your database transactions. Isolation levels such as Read Uncommitted, Read Committed, Repeatable Read, and Serializable can impact the order of lock acquisition and the behaviour of concurrent transactions.

2. Query execution plans:

   • Use SQL Server Management Studio (SSMS) or other database management tools to analyse the query execution plans for your transactions.
   • The execution plans can provide insights into the order in which data is accessed and the types of locks acquired during query execution.

3. Locking and blocking:

   • Monitor and analyse the locking and blocking behaviour of concurrent transactions using tools like SQL Server Profiler, Extended Events, or dynamic management views (DMVs) such as sys.dm_tran_locks and sys.dm_os_waiting_tasks.
   • Identify instances of blocking and analyse the lock types and resources involved to understand the order of lock acquisition.

4. Transaction log and history:

   • Review the transaction log and history to understand the sequence of transactions and their impact on lock acquisition.
   • SQL Server's transaction log and history can provide valuable information about the order in which transactions are executed and their associated locks.

By using these approaches, you can gain insights into the transaction order and lock acquisition behaviour in SQL Server, which can help in identifying potential issues related to deadlocks, blocking, and overall transaction concurrency.

What is deadlock priority and how to address in SQL Server

In SQL Server, deadlock priority is a mechanism that allows you to influence the selection of the transaction that will be chosen as the deadlock victim when a deadlock occurs. You can use deadlock priority to specify the importance of individual transactions in the event of a deadlock.

To address deadlock priority in SQL Server, you can consider the following:

1. Setting deadlock priority:
   • You can use the SET DEADLOCK_PRIORITY statement to specify the priority of a session or transaction.
   • The priority levels range from -10 to 10, where -10 is the lowest priority and 10 is the highest.
   • By setting the deadlock priority, you can influence the selection of the victim transaction when a deadlock occurs.

Here's an example of how to set the deadlock priority for a session:

SET DEADLOCK_PRIORITY LOW; -- Set the deadlock priority to low
  

2. Adjusting transaction logic:

   • Design your transaction logic to handle the potential impact of being chosen as the deadlock victim based on the assigned deadlock priority.
   • Consider implementing retry logic for transactions with lower deadlock priority after being chosen as the deadlock victim.

3. Analyzing and tuning deadlock priority:

   • Evaluate the impact of deadlock priority settings on your application's transactions and overall performance.
   • Tune the deadlock priority based on the specific requirements and characteristics of your application to effectively manage deadlocks.

It's important to carefully consider the implications of deadlock priority settings in SQL Server and design your transaction logic to handle deadlock situations appropriately. Understanding the behavior of deadlock priority in SQL Server is crucial for effectively addressing and managing deadlocks.

How to implement retry logic for DB Transactions

In SQL Server, you can implement retry logic for transactions using T-SQL and error handling. Here's an example of how you can create a stored procedure that includes retry logic for handling deadlock errors:

CREATE PROCEDURE usp_RetryTransaction
AS
BEGIN
    DECLARE @retryCount INT = 0
    DECLARE @maxRetries INT = 3

    WHILE @retryCount < @maxRetries
    BEGIN
        BEGIN TRY
            BEGIN TRANSACTION
            -- Your transactional logic goes here
            COMMIT TRANSACTION
            RETURN
        END TRY
        BEGIN CATCH
            IF ERROR_NUMBER() = 1205  -- Deadlock error number
            BEGIN
                ROLLBACK TRANSACTION
                SET @retryCount = @retryCount + 1
                WAITFOR DELAY '00:00:01'  -- Wait for 1 second before retrying
            END
            ELSE
            BEGIN
                -- Handle other types of errors
                THROW
            END
        END CATCH
    END
    -- If the maximum number of retries is reached, handle the situation as needed
    -- For example, raise an error or log the issue
END
  

In this example, the stored procedure attempts the transaction logic within a retry loop, and if a deadlock error (error number 1205) occurs, it rolls back the transaction, increments the retry count, and waits for a short duration before retrying the transaction. If the maximum number of retries is reached, you can handle the situation as needed based on your application's requirements.

You can then call this stored procedure whenever you need to perform a transaction with retry logic for deadlock handling.

Top 10 FREE courses to learn SQL in 2023

FREE Certificates

❯ DBMS, SQL http://learn.saylor.org/course/view.php?id=93

❯ SQL http://openclassrooms.com/en/courses/2071486-retrieve-data-using-sql

❯ MySQL http://coursera.org/learn/database-structures-and-management-with-mysql

Paid Certificate

❯ SQL Server http://learn.microsoft.com/training/paths/program-transact-sql/ *

Free Badge

❯ PostgreSQL http://coursera.org/specializations/postgresql-for-everybody

Paid Certificate

❯ Oracle http://mygreatlearning.com/academy/learn-for-free/courses/oracle-sql

❯ RDBMS, SQL http://online.stanford.edu/courses/soe-ydatabases0005-databases-relational-databases-and-sql

Paid Certificate

❯ RDBMS, SQL http://udacity.com/course/database-systems-concepts-design—ud150

No Certificate

❯ PostgreSQL http://freecodecamp.org/learn/relational-database/

❯ Big Query http://kaggle.com/learn/intro-to

Happy SQL

How to implement impersonation in SQL Server

To implement impersonation in SQL Server, you can follow these steps:

1. Create a Login:
First, create a SQL Server login for the user you want to impersonate. Use the `CREATE LOGIN` statement to create the login and provide the necessary authentication credentials.

Example:

CREATE LOGIN [ImpersonatedUser] WITH PASSWORD = 'password';
  

2. Create a User:
Next, create a user in the target database associated with the login you created in the previous step. Use the `CREATE USER` statement to create the user and map it to the login.

Example:  

CREATE USER [ImpersonatedUser] FOR LOGIN [ImpersonatedUser];
  

3. Grant Permissions:
Grant the necessary permissions to the user being impersonated. Use the `GRANT` statement to assign the required privileges to the user.

Example:

GRANT SELECT, INSERT, UPDATE ON dbo.TableName TO [ImpersonatedUser];
  

4. Impersonate the User:
To initiate impersonation, use the `EXECUTE AS USER` statement followed by the username of the user you want to impersonate. This will switch the execution context to the specified user.

Example:

EXECUTE AS USER = 'ImpersonatedUser';
  

5. Execute Statements:
Within the impersonated context, execute the desired SQL statements or actions. These statements will be performed with the permissions and privileges of the impersonated user.

Example:

SELECT * FROM dbo.TableName;
-- Perform other actions as needed
  

6. Revert Impersonation:
After completing the necessary actions, revert back to the original security context using the `REVERT` statement. This will switch the execution context back to the original user.

Example:

REVERT;
  

By following these steps, you can implement impersonation in SQL Server. Ensure that you grant the appropriate permissions to the user being impersonated and consider security implications when assigning privileges.

Here is the full syntax:

EXECUTE AS LOGIN = 'DomainName\impersonatedUser'
EXEC  uspInsertUpdateGridSettings @param1, @param2
REVERT;
  

Additionally, be mindful of auditing and logging to track and monitor impersonated actions for accountability and security purposes.

Find tables or procedures that are associated in SQL Jobs via Query

Recently we need to look for a procedure where we are using in SQL Jobs. There is no easy way to find unless you script all jobs and find in the script.

But there is some easy way to find it using below query. You could also might have similar ask to find a procedure or table that you might have used in SQL Jobs in any of those steps. It could be any string like comment, procedure, function or table, this below query works.

USE msdb
GO

SELECT [sJOB].[job_id] AS [JobID]
	,[sJOB].[name] AS [JobName]
	,step.step_name AS JobStepName
	,step.command AS JobCommand
	,[sJOB].enabled AS ActiveStatus
FROM [msdb].[dbo].[sysjobs] AS [sJOB]
LEFT JOIN [msdb].dbo.sysjobsteps step ON sJOB.job_id = step.job_id
WHERE step.command LIKE '%uspPopulateAggregatorUsageData%' ----You can change here what you are searching for
ORDER BY [JobName]
  

Thank you

How to get comma separated values from SQL

There are few types where you can get comma separated values form SQL SERVER using SQL

1. XML PATH method:

SELECT 
   STUFF((SELECT ', ' + column_name
          FROM table_name
          WHERE conditions
          FOR XML PATH('')), 1, 2, '') AS csv_values;
  

2. COALESCE and FOR XML method:

SELECT 
   STUFF((
      SELECT ', ' + column_name
      FROM table_name
      WHERE conditions
      FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '') AS csv_values;
  

3. FOR XML PATH method

SELECT 
   STUFF((SELECT ',' + column_name
          FROM table_name
          WHERE conditions
          FOR XML PATH('')), 1, 1, '') AS csv_values;
  

In these examples, replace column_name with the actual column name and table_name with the appropriate table name. Customize the WHERE clause to filter the desired rows if necessary.

When executing these queries, a single row with a single column will be returned, containing the comma-separated values from the specified column. Please note that the XML-related methods convert the values to XML and then manipulate them, resulting in a string of comma-separated values.

Hope this helps!!

How to Find Tables that Contain a Specific Column in SQL Server?

Basic concept to understand about SQL Server is that of catalog views, which are effectively database tables (catalogs in this case) that display system-wide information about the SQL Server Database Engine.

All catalog views are accessed via a SELECT SQL statement FROM a specific catalog within the sys. namespace.
For example, the following statement can be used to view information about all database tables in the system via the sys.tables catalog

use mobility
go
select  
        s.[name]            'Schema',
        t.[name]            'Table',
        c.[name]            'Column',
        d.[name]            'Data Type',
        c.[max_length]      'Length',
        d.[max_length]      'Max Length',
        d.[precision]       'Precision',
        c.[is_identity]     'Is Id',
        c.[is_nullable]     'Is Nullable',
        c.[is_computed]     'Is Computed',
        d.[is_user_defined] 'Is UserDefined',
        t.[modify_date]     'Date Modified',
        t.[create_date]     'Date created'
from        sys.schemas s
inner join  sys.tables  t
on s.schema_id = t.schema_id
inner join  sys.columns c
on t.object_id = c.object_id
inner join  sys.types   d
on c.user_type_id = d.user_type_id
where c.name like '%ProjectManagerID%'
  

Hope this helps!!

How to check active transactions in SQL Server

A transaction is a single unit of work. If a transaction is successful, all of the data modifications made during the transaction are committed and become a permanent part of the database.

Some times, if there are any issues we will get into this deadlock stage and we don't get any responses from database. In those cases, if you want know what are active connections going on at that point of time, will help identifying issue.

if you want to know more details about active sessions like session ID, Host Name, Login Name, Transaction ID, Transaction Name, Transaction Begin Time,Database ID,Database Name etc.

Use the below query for details,

SELECT
trans.session_id AS [SESSION ID],
execSession.host_name AS [HOST NAME],login_name AS [Login NAME],
trans.transaction_id AS [TRANSACTION ID],
tas.name AS [TRANSACTION NAME],tas.transaction_begin_time AS [TRANSACTION 
BEGIN TIME],
tds.database_id AS [DATABASE ID],DBs.name AS [DATABASE NAME]
FROM sys.dm_tran_active_transactions tas
JOIN sys.dm_tran_session_transactions trans
ON (trans.transaction_id=tas.transaction_id)
LEFT OUTER JOIN sys.dm_tran_database_transactions tds
ON (tas.transaction_id = tds.transaction_id )
LEFT OUTER JOIN sys.databases AS DBs
ON tds.database_id = DBs.database_id
LEFT OUTER JOIN sys.dm_exec_sessions AS execSession
ON trans.session_id = execSession.session_id
WHERE execSession.session_id IS NOT NULL
  

What is the Query to display the failed SQL Jobs

We can get this information by the standard reports available in SQL Server, right click on SQL server agent> Standard reports and select the desired report to see Job History

It can be frustrating to find recently failed jobs in the job history in SQL Server Management Studio. A quicker way to do it is to just run a query to see what jobs have failed recently.

Below Query will give you list of failed jobs, you can also filter by name.

select j.name
    ,js.step_name
    ,jh.sql_severity
    ,jh.message
    ,jh.run_date
    ,jh.run_time
FROM msdb.dbo.sysjobs AS j
INNER JOIN msdb.dbo.sysjobsteps AS js
   ON js.job_id = j.job_id
INNER JOIN msdb.dbo.sysjobhistory AS jh
   ON jh.job_id = j.job_id AND jh.step_id = js.step_id
WHERE jh.run_status = 0
--and name = 'jobName'
order by run_date desc
  

If you want to check results based on data range, you can use below query to find desired results.

-- Variable Declarations 
DECLARE @FinalDate INT;
SET @FinalDate = CONVERT(int
    , CONVERT(varchar(10), DATEADD(DAY, -2, GETDATE()), 112)
    ) -- last two days date as Integer in YYYYMMDD format

-- Final Logic 

SELECT  j.[name],  
        s.step_name,  
        h.step_id,  
        h.step_name,  
        h.run_date,  
        h.run_time,  
        h.sql_severity,  
        h.message,   
        h.server  
FROM    msdb.dbo.sysjobhistory h  
        INNER JOIN msdb.dbo.sysjobs j  
            ON h.job_id = j.job_id  
        INNER JOIN msdb.dbo.sysjobsteps s  
            ON j.job_id = s.job_id 
                AND h.step_id = s.step_id  
WHERE    h.run_status = 0 -- Failure  
         AND h.run_date > @FinalDate  
ORDER BY h.instance_id DESC;
  

Hope this helps!

How to Get the Last Day of the Month in T-SQL

We’ll use the function EOMONTH() to find the last day of the month.

DECLARE @fromdate DATETIME, @EOMMonthdate DATETIME

SET @fromdate = '11/01/2022'

SET @EOMMonthdate = EOMONTH(@fromdate,0) -- for current month

SELECT @EOMMonthdate as lastdayOfthemonth
  

If you want to return the last day of the second, third, etc. month from a given date, use EOMONTH()’s optional second argument: the number of months to add. Look at the examples for last day of next month or last day of the previous month.

DECLARE @fromdate DATETIME, @EOMMonthdate DATETIME

SET @fromdate = '11/01/2022'

SET @EOMMonthdate = EOMONTH(@fromdate,1) -- for next month last day

SELECT @EOMMonthdate as nextMonthLastDayOfthemonth

SET @EOMMonthdate = EOMONTH(@fromdate,-1) -- for previous month last day

SELECT @EOMMonthdate as previousMonthLastday
  

Hope this helps!!

SQL SERVER – How to find table rows count?

At some point in time we’ve all had to find out how many rows are in a table. The first answer you’ll usually get when you ask someone how to do it is select count(*) from table.

Here is the simple and accurate query to get this information from SQL Server

SELECT SCHEMA_NAME(schema_id) AS [SchemaName],
[Tables].name AS [TableName],
SUM([Partitions].[rows]) AS [TotalRowCount]
FROM sys.tables AS [Tables]
JOIN sys.partitions AS [Partitions]
ON [Tables].[object_id] = [Partitions].[object_id]
AND [Partitions].index_id IN ( 0, 1 )
-- WHERE [Tables].name = N'name of the table'
GROUP BY SCHEMA_NAME(schema_id), [Tables].name
order by  [TotalRowCount] desc

Get Current TimeZone Name in SQL Server

Here is how we can get current time zone from sql server

DECLARE @TimeZone VARCHAR(50)
EXEC MASTER.dbo.xp_regread 'HKEY_LOCAL_MACHINE',
'SYSTEM\CurrentControlSet\Control\TimeZoneInformation',
'TimeZoneKeyName',@TimeZone OUT
SELECT @TimeZone

Quick note: How to get backup history of a database?

Here is how you can get via SQL Query, This works on any SQL Server version

USE database_name
GO
-- Get Backup History for required database
SELECT TOP 100
s.database_name,
m.physical_device_name,
CAST(CAST(s.backup_size / 1000000 AS INT) AS VARCHAR(14)) + ' ' + 'MB' AS bkSize,
CAST(DATEDIFF(second, s.backup_start_date,
s.backup_finish_date) AS VARCHAR(4)) + ' ' + 'Seconds' TimeTaken,
s.backup_start_date,
CAST(s.first_lsn AS VARCHAR(50)) AS first_lsn,
CAST(s.last_lsn AS VARCHAR(50)) AS last_lsn,
CASE s.[type] WHEN 'D' THEN 'Full'
WHEN 'I' THEN 'Differential'
WHEN 'L' THEN 'Transaction Log'
END AS BackupType,
s.server_name,
s.recovery_model
FROM msdb.dbo.backupset s
INNER JOIN msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id
WHERE s.database_name = DB_NAME() -- Remove this line for all the database
ORDER BY backup_start_date DESC, backup_finish_date
GO

Hope this helps!

Quick note: How to find queries, SP's or jobs that use a linked server?

Here is the query below that can pull all above results.

Declare @VName varchar(256)
Declare Findlinked cursor
LOCAL STATIC FORWARD_ONLY READ_ONLY
     FOR
Select name
   From sys.servers
   Where is_linked = 1
      
Open Findlinked;
Fetch next from Findlinked into @VName;

while @@FETCH_STATUS = 0
Begin
   SELECT OBJECT_NAME(object_id) 
      FROM sys.sql_modules 
      WHERE Definition LIKE '%'+@VName +'%' 
      AND OBJECTPROPERTY(object_id, 'IsProcedure') = 1 ;
      
   Fetch next from Findlinked into @VName;
End

Close Findlinked
Deallocate Findlinked

SSIS Jobs are different, Here is one for SSIS jobs with Job Name and Details

Declare @VName varchar(256)
Declare Findlinked cursor
LOCAL STATIC FORWARD_ONLY READ_ONLY
     FOR
Select name AS name
   From sys.servers
   Where is_linked = 1
      
Open Findlinked;
Fetch next from Findlinked into @VName;

while @@FETCH_STATUS = 0
Begin
   SELECT OBJECT_NAME(object_id) as ProcedureName 
      FROM sys.sql_modules 
      WHERE Definition LIKE '%'+@VName +'%' 
      AND OBJECTPROPERTY(object_id, 'IsProcedure') = 1 ;
      
   Fetch next from Findlinked into @VName;
END
Close Findlinked

Open Findlinked;
Fetch next from Findlinked into @VName;

while @@FETCH_STATUS = 0
Begin
   SELECT j.name AS JobName,js.command 
      FROM msdb.dbo.sysjobsteps js
         INNER JOIN msdb.dbo.sysjobs j
            ON j.job_id = js.job_id
      WHERE js.command LIKE '%'+@VName +'%'
   Fetch next from Findlinked into @VName;
END

Close Findlinked
Deallocate Findlinked

Determining which version and edition of SQL Server Database Engine is running

Open SQL Server Management Studio (SSMS) and connect to SQL Server. Run below query to find version and edition of SQL server.

SELECT  
    SERVERPROPERTY('productversion') as 'Product Version', 
    SERVERPROPERTY('productlevel') as 'Product Level',  
    SERVERPROPERTY('edition') as 'Product Edition',
    SERVERPROPERTY('buildclrversion') as 'CLR Version',
    SERVERPROPERTY('collation') as 'Default Collation',
    SERVERPROPERTY('instancename') as 'Instance',
    SERVERPROPERTY('lcid') as 'LCID',
    SERVERPROPERTY('servername') as 'Server Name'

Difference between #temptable and ##TempTable and table variable?

Local Temporary tables

Local temp tables are only available to the current connection for the user; and they are automatically deleted when the user disconnects from instances. Local temporary table name is stared with hash ("#") sign. scope of Local Temporary table is only bounded with the current connection of current user.

CREATE TABLE #TempTable(
 ID int,
 Name varchar(50))

Global Temporary tables

Global Temporary tables name starts with a double hash ("##"). Once this table has been created by a connection, like a permanent table it is then available to any user by any connection. It can only be deleted once all connections have been closed. Global temporary tables are visible to all SQL Server connections. When you create one of these, all the users can see it.

CREATE TABLE ##GlobalTempTable(
 ID int,
 Name varchar(50))

Table variables in T-SQL

Microsoft introduced table variables with SQL Server 2000 as an alternative to using temporary tables. In many cases a table variable can outperform a solution using a temporary table
Table variables store a set of records, so naturally the declaration syntax looks very similar to a CREATE TABLE statement, as you can see in the following example:

DECLARE @UserTable TABLE
( ID int,
  Name varchar(50))

Unlike the majority of the other data types in SQL Server, you cannot use a table variable as an input or an output parameter. In fact, a table variable is scoped to the stored procedure, batch, or user-defined function just like any local variable you create with a DECLARE statement. The variable will no longer exist after the procedure exits - there will be no table to clean up with a DROP statement.

Because of the well-defined scope, a table variable will generally use fewer resources than a temporary table. Transactions touching table variables only last for the duration of the update on the table variable, so there is less locking and logging overhead.
Using a temporary table inside of a stored procedure may result in additional re-compilations of the stored procedure. Table variables can often avoid this recompilation hit.

Configure the remote query timeout Server Configuration Option

Use below query to increase default time out or disable time out time.

USE ps;  
GO  
EXEC sp_configure 'remote query timeout', 0 ;  
GO  
RECONFIGURE ;  
GO 

The remote query timeout option specifies how long, in seconds, a remote operation can take before SQL Server times out. The default value for this option is 600, which allows a 10-minute wait. This value applies to an outgoing connection initiated by the Database Engine as a remote query. This value has no effect on queries received by the Database Engine. To disable the time-out, set the value to 0. A query will wait until it completes.

For more information, see Server Configuration Options (SQL Server).