syntax

Connect To Remote Database Without A Linked Server

by
Facebooktwitterredditpinterestlinkedinmail

Most of the time that people connect to a remote database they use linked servers.  But have you ever wanted to connect to another database server without creating a linked server?  This post will show you how to connect to an ad hoc database server while executing a query.

To accomplish this, one method (samples below) that SQL Server provides is to use opendatasource.  Opendatasource allows you to connect and query a remote data source by passing your connection parameters in to the command.

NOTE: Microsoft does not recommend using this method of connecting to a remote database server if this data source is accessed more than a few times.  If the data source is frequently used, they recommend that you create a linked server.

Connect To Remote Database Using Windows Authentication

SELECT	*
FROM	OPENDATASOURCE('SQLNCLI', 'Data Source=RemoteServerName;Integrated Security=SSPI').Billing.dbo.Invoices

Connect To Remote Database Using SQL Server Authentication

SELECT	*
FROM	OPENDATASOURCE('SQLNCLI', 'Data Source=RemoteServerName;user id=username;password=password).Billing.dbo.Invoices

In the examples above, SQLNCLI tells SQL Server to connect to the database using an SQL Server Native Client provider.  This will automatically use the latest SQL Server Native Client OLE DB Provider.

 

Reference: http://technet.microsoft.com/en-us/library/ms179856.aspx  –  OPENDATASOURCE (Transact-SQL)

Option Recompile

by
Facebooktwitterredditpinterestlinkedinmail

Option recompile is one of those hidden gems when it comes to increasing stored procedure performance.  When used in the right spot, this can speed up a never ending stored procedure call to instantaneous.  If you don’t know what option recompile is… it’s just a query hint that can be added to the end of a sql query.

What Option Recompile Does

When option recompile is added to the query hints, it does a couple things.  The first thing that it does is it tells SQL Server to discard any stored execution plan for that query.  This will cause SQL Server to rebuild the execution plan for this query.  The next thing it does is the magical part.  When SQL Server goes to build the execution plan, it will replace the local variables with the actual values in those variables.  This allows SQL Server to pull the data SUPER fast.

Symptoms

Keep in mind that many times the slowdown in a stored procedure call is just one statement inside the stored procedure.

The easiest way that I have found to figure out if this will help a slow stored procedure is to decompose the stored procedure in to separate sql statements.  I then run the sql statements to see what one is the slow one.  If I run a statement that is slow and uses a local variable, this may be a good candidate.  Next I replace the local variables in that statement with the explicit values.  If I rerun the statement and it performs fast, I know that this could use option recompile.

Syntax

DECLARE @CustomerName   VARCHAR(250)

SET     @CustomerName = "Tester"

SELECT  CustomerID,
        CustomerName,
        Address
FROM    Customer
WHERE   CustomerName = @CustomerName
OPTION  (RECOMPILE)

Delete Using Join

by
Facebooktwitterredditpinterestlinkedinmail

The DELETE statement is one of the most fundamental statements on SQL Server.  Just about everyone knows how to use the delete statement.  But did you know that you can delete using a join?  It’s actually pretty easy.

Delete Using Join

To delete from a table using a join condition, you need to first format your statement like a regular select statement.  Instead of using a SELECT statement, you will use a DELETE statement.  After the delete command you will specify the table that you want to delete from in your join.

In the example below, we want to delete all orders for this employee.  I have aliased the Orders table with the letter O.  So my delete statement will say “DELETE O”… where O is the alias for the Orders table.  This will only delete data from the Orders table and not from the Employees table.

DELETE	O
FROM	Orders O
INNER	JOIN Employees E ON E.EmployeeID = O.EmployeeID
WHERE	E.LastName = 'Tester'

One thing to note is that you cannot delete from multiple tables this way.

Reference: http://technet.microsoft.com/en-us/library/ms189835.aspx

Sleep Command In Sql Server

by
Facebooktwitterredditpinterestlinkedinmail

Most programming languages have a command that allows a process to sleep (or wait) before continuing.  SQL Server is no exception.  Since SQL Server 2005 there has support for a sleep command through the use of the WAITFOR command.

Read more