Link Server

Link Server

Linked Servers is a concept in SQL Server by which we can add other
SQL Server to a Group and query both the SQL Server dbs usig T-SQL Statements.
With a linked server, you can create very clean, easy to follow, SQL statements that allow remote data to be retrieved, 
joined and combined with local data.
Stored Procedure sp_addlinkedserver, sp_addlinkedsrvlogin will be used add new Linked Server. 

Increase SQL Server stored procedure performance

 Increase SQL Server stored procedure performance



1. SET NOCOUNT ON
This help to stored procedures to stop the message indicating the number of rows affected by a Transact-SQL statement.

This can reduce network traffic.

2.Use return values


3.Don't write select * from [tablename]

write select [columnname1],[columnname2] from [tablename]
This helps to speed of the query.


4.Don't use Prefix "Sp_" in your store procedure.

Becoz if you use "Sp" then SQL Server looks in the master database then your database.


5.Use sp_executesql stored procedure instead of the EXECUTE statement.


6.Avoid using temporary tables inside your stored procedure.

Becoz Using temporary tables inside stored procedure reduces the chance to reuse the execution plan.


7.Avoid using DDL (Data Definition Language) statements inside your stored procedure.

Using DDL statements inside stored procedure reduces the chance to reuse the execution plan.