commands

SQL Server Trim

by
Facebooktwitterredditpinterestlinkedinmail

SQL Server trim function does not exist!  Although this seems a bit odd, SQL Server does somewhat have support for trimming data.  SQL Server has built in functions for Right Trim and for Left Trim.  Using these functions, you can accomplish a full trim.  I’ll first show you how to trim the different sides of the text and then show how to trim the full thing.

 
Trim Text Inline

The functions to trim text are RTrim() and LTrim().

DECLARE	@TextToTrim VARCHAR(50) = '     TEXT TO TRIM      '

-- Trims the spaces from the left side of the data
SELECT	LTRIM(@TextToTrim) AS LeftSpacesRemoved

-- Trims the spaces from the right side of the data
SELECT	RTRIM(@TextToTrim) AS RightSpacesRemoved

-- Trims the spaces from both sides of the data
SELECT	LTRIM(RTRIM(@TextToTrim)) AS FullTrim

 
If a full trim is something that you do often, you can create a SQL Server Trim function to make the process a little simpler.

CREATE	FUNCTION TRIM(@TextToTrim VARCHAR(8000))
RETURNS	VARCHAR(8000)
BEGIN

	RETURN	LTRIM(RTRIM(@TextToTrim))

END

You can use this new fancy SQL Server Trim function by calling it like this:

DECLARE	@TextToTrim VARCHAR(50) = '     TEXT TO TRIM      '

SELECT	dbo.TRIM(@TextToTrim) AS FullTrim

Cannot Insert Explicit Value For Identity Column

by
Facebooktwitterredditpinterestlinkedinmail

If you try to insert in to a table with an ID column, you could get this error “Cannot insert explicit value for identity column in table ‘<<table>>’ when IDENTITY_INSERT is set to OFF.”  This is not the end of the world and is actually pretty easy to get around.

 
Why Are You Getting This Error?

The reason you’re getting this error is because you are trying to tell SQL Server what the ID value for a row in the table should be.  If you have an identity column on your table, SQL Server will want to generate that ID value for you automatically.  The error is essentially saying, “Hey, I’m supposed to generate the ID for you… you aren’t supposed to tell me what it is!”

 
How Can You Fix This?

There are two easy ways to get around this.

 
If You Want To Tell SQL Server What The ID Value Should Be

If want to specify what the ID values should be for the records you are inserting, then before you execute the INSERT statement, you need to run a small sql command to turn identity inserts on.  Identity inserts allow you to populate the value of an identity column with a specific value.

SET		IDENTITY_INSERT Animal ON

INSERT
INTO	Animal
		(AnimalID, AnimalName)
VALUES	(1,        'Horse'),
		(2,        'Pig'),
		(3,        'Cow')

SET		IDENTITY_INSERT Animal OFF

NOTE: You can only have identity insert on for one table at a time.  To insert in to a second table, you will need to turn the identity insert off on the first table before inserting in to the second table.

 
If You Want To Allow SQL Server To Generate The ID

If you don’t care about what the ID values are for these records, you should just allow SQL Server to generate the ID values for you.  To do this, just specify the columns that are in the table and leave off the identity column.

INSERT
INTO	Animal
		(AnimalName)
VALUES	('Horse'),
		('Pig'),
		('Cow')

 
Reference: http://msdn.microsoft.com/en-us/library/ms188059.aspx

Show Query Execution Time

by
Facebooktwitterredditpinterestlinkedinmail

One of the limitations of SQL Server Management Studio is that it shows query execution time in seconds.  In most daily activities, this is accurate enough.  But when it comes to query permanence tuning, it is helpful to have a more accurate measurement.

Here are a 3 different ways to show you an accurate query execution time:

 
Statistics On
To have your exact query time show in the separate messages tab, all you need to do is run the following queries.

SET STATISTICS TIME ON

GO

-- Insert query here

GO

SET STATISTICS TIME OFF

GO

Then you can view the time by clicking on the Messages tab.

Show Query Execution Time

 
Include Client Statistics
To get a lot of in-depth information about your query, you can turn on Client Statistics before running your query.  The details of all the results will be shown in the Client Statistics tab after execution was completed.

Turn on client statistics by choosing the Include Client Statistics option from query menu.

show query execution time client stats

You can then view the query time by looking under the Client Statistics tab by your query results

Show Query Execution Time Client Stats

 
Inline Query Time Results
If you would like to just show the execution time in the standard results window, this query allows you to do this.  It will also allow you to show exact time of execution of a specific code segment in a query.

To use this, just place the code that you would like to get the execution time for in the center of the below script.  The exact time will be shown in the results.

DECLARE @Time1 DATETIME

DECLARE @Time2 DATETIME

SET     @Time1 = GETDATE()

-- Insert query here

SET     @Time2 = GETDATE()

SELECT  DATEDIFF(MILLISECOND,@Time1,@Time2) AS Elapsed_MS

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

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)