SQL Server: What I Have Learned in May 2011

Dedicated to Most Energetic SQL Expert Gail Shaw

SQL Tips & Tricks:

SQL Server: Column Value with Leading Zeros

SQL Server: How to Remove Extra Spaces From String Value

SQL Server: How to Remove Special Characters

SQL Server: Conditional WHERE clause (Filter for a Filter)

Third Party Tools:

SQL Server: How SQL Diag Became Easy for Every DBA

SQL Server: Shortcuts for TSQL Code in SSMS

SQL Server: sp_WhoIsActive, a Must Tool for a DBA

SQL Server: Keeping Log of Each Query Executed Through SSMS

Performance Tuning:

SQL Server: Why to Avoid TRIM functions in WHERE and JOIN clauses

SQL Server: Short Code Doesn’t Mean Smart Code

SQL Server: Which Performs Better, IN or EXISTS

SQL Server Management Studio:

SQL Server: Query Template, a Time Saving Feature

Database Management Views & Functions:

SQL Server: Create/Drop Scripts for All Existing Foreign Keys

SQL Server: How to Get All Indexes List With Involved Columns Name

SQL Server: Create/Drop Scripts for All Existing Foreign Keys

Today, when I need a script to get create and drop scripts of all existing foreign keys on a specific table (or in a whole database), I searched my query bank and find my required script quickly but on opening it I found that I was written for SQL Server 2000, time when there was no concept of schema, so I have made some changes so I can use it for SQL Server 2005 or 2008.

Create Foreign Keys:

SELECT  'ALTER TABLE ' + SCHEMA_NAME(F.schema_id) + '.'

        + OBJECT_NAME(F.parent_object_id) + ' ADD CONSTRAINT ' + F.name

        + ' FOREIGN KEY ' + '(' + COL_NAME(FC.parent_object_id,

                                           FC.parent_column_id) + ')'

        + ' REFERENCES ' + SCHEMA_NAME(RefObj.schema_id) + '.'

        + OBJECT_NAME(F.referenced_object_id) + ' ('

        + COL_NAME(FC.referenced_object_id, FC.referenced_column_id) + ')'

FROM    SYS.FOREIGN_KEYS AS F

        INNER JOIN SYS.FOREIGN_KEY_COLUMNS AS FC ON F.OBJECT_ID = FC.constraint_object_id

        INNER JOIN sys.objects RefObj ON RefObj.object_id = f.referenced_object_id

--WHERE   OBJECT_NAME(F.PARENT_OBJECT_ID) = 'YourObjectName'

Drop Foreign Keys

SELECT  'ALTER TABLE ' + SCHEMA_NAME(F.schema_id) + '.'

        + OBJECT_NAME(F.parent_object_id) + ' DROP CONSTRAINT ' + F.name

FROM    SYS.FOREIGN_KEYS AS F

        INNER JOIN SYS.FOREIGN_KEY_COLUMNS AS FC ON F.OBJECT_ID = FC.constraint_object_id

--WHERE   OBJECT_NAME(F.PARENT_OBJECT_ID) = 'YourObjectName'

SQL Server: Better Intellisence for TSQL Code in SSMS

For a developer or DBA it’s common to start their day with “SELECT * FROM” and in a daily routine work we type same lines of script many times. If you are a lazy developer or DBA like me then sometime it feels boring to type same code again and again. Intellisence in SQL Server 2008, saved lot of time but still it lacks few features, which other third party tools were offering before SQL Server 2008. 
Through such tools like SQL PROMPT, we have ability to type shortcuts in editor which after pressing Enter or Tab turn into a predefined code block. Like I will just type * and it will convert it to “SELECT * FROM”.

If you don’t like to purchase these tools but still want to make your life easy then you need SSMS Tools by Mladen Prajdić, totally free and you can download from here. Beside other good tools it has an option of SQL Snippets. Although it already have a good list of shortcuts but still you can add of your choice.

It has shortcuts like:

                                    SSC        =       SELECT  COUNT(*) FROM

                                    SSF         =       SELECT * FROM

                                    UPD       =       UPDATE <>

 SET    <>

 FROM   <>

SQL Server: Column Value with Leading Zeros

Till date I was using old CASE option to add zeros before a digit to get all values with same length for columns like social security number but today SQL Expert Shakeeb Younas shared a very simple method to achieve above mentioned goal.

DECLARE @Emp TABLE ( SSN VARCHAR(9) )

INSERT  INTO @Emp

        SELECT  '1'

        UNION ALL

        SELECT  '654'

        UNION ALL

        SELECT  '824741'

        UNION ALL

        SELECT  '123456789'

        UNION ALL

        SELECT  '37'

-- Get SSN with leading zeros       

SELECT  REPLACE(STR(SSN, 9), ' ', '0') AS SSN

FROM    @Emp

SQL Server: Which Performs Better, IN or EXISTS

It’s true that to solve a problem in TSQL you have many choices, and adopting any of these techniques you can get your desired results. Today we will discuss two very commonly used IN and EXISTS clause to filter query result set based on records from a sub-query. As compared to EXISTS, IN is more commonly used, but which one is more efficient? That’s what we are looking for, today.

 IN and EXISTS perform in a same way if we use them with single column search. Like as follow:

USE AdventureWorks

GO

--IN

SELECT * FROM Production.Product pr

WHERE ProductID IN

      (SELECT ProductID FROM Purchasing.ProductVendor)

--EXISTS

SELECT * FROM Production.Product pr

WHERE EXISTS

      (SELECT 1 FROM Purchasing.ProductVendor  
              WHERE ProductID = pr.ProductID)

Logical reads and query elapsed time is also same for both queries.

In situations where you need to filter records based on more than one columns existence in sub-query, you will find EXISTS much better in performance. To observe this, let’s create two temporary tables.

CREATE TABLE #Cars (Make VARCHAR(50), Color VARCHAR(30), Seats INT)

CREATE TABLE #CarIssuance (Make VARCHAR(50),Color VARCHAR(30),IssuanceDate DATETIME)

GO

INSERT INTO #Cars

SELECT 'Honda','Black',2 union all

SELECT 'Honda','White',2 union all

SELECT 'Toyota','Black',4 union all

SELECT 'Toyota','Silver',4 union all

SELECT 'BMW', 'Red',2

GO

INSERT INTO #CarIssuance

SELECT 'Honda','Black','2011-05-20' union all

SELECT 'BMW','Red','2011-05-03' union all

SELECT 'Toyota','Black','2011-05-03'

If we need to get all records from #Cars table, where records exists in #CarIssuance table on basis of “make” and “color” columns. Let’s first try traditional IN clause.

SELECT * FROM #Cars

WHERE Color  IN

 (SELECT Color FROM #CarIssuance)

 AND Make IN

      (SELECT Make FROM #CarIssuance)

Table '#CarIssuance Scan count 2, logical reads 8, physical reads 0, read-ahead reads 0

Table '#Cars Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0

SQL Server Execution Times:

   CPU time = 0 ms,  elapsed time = 40 ms.

Now execute same query with EXISTS option

SELECT * FROM #Cars Cr

WHERE

 EXISTS

 (SELECT 1 FROM #CarIssuance CI

                        WHERE Make = Cr.Make

                              AND Color = Cr.Color)

Table '#CarIssuance Scan count 1, logical reads 5, physical reads 0, read-ahead reads 0

Table '#Cars Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0

SQL Server Execution Times:

   CPU time = 0 ms,  elapsed time = 1 ms.

Exists performed much better (We got result in 1ms instead of 40ms) just because we have used single sub-query but in case of “IN”, we have used multiple sub-queries. So if somehow, we change our first query of “IN” clause so that we can use only one sub-query, performance will be same as to EXISTS. But code will be bit mixed up and in real life sometime even its not possible.

SELECT * FROM #Cars Cr

WHERE Color  IN

 (SELECT Color FROM #CarIssuance WHERE Make = Cr.Make)

Summary: Exists performs much better when used for more then one column filter from a sub-query but as a good programming practice EXISTS must be preferred even when handling with single column filter.

SQL Server: How to Remove Special Characters

One more useful function from my query bank, which I like to use to remove special characters from a string.

CREATE FUNCTION dbo.RemoveSpecialChars ( @InputString VARCHAR(8000) )

RETURNS VARCHAR(8000)  

BEGIN

    IF @InputString IS NULL

        RETURN NULL

    DECLARE @OutputString VARCHAR(8000)

    SET @OutputString = ''

    DECLARE @l INT

    SET @l = LEN(@InputString)

    DECLARE @p INT

    SET @p = 1

    WHILE @p <= @l

        BEGIN

            DECLARE @c INT

            SET @c = ASCII(SUBSTRING(@InputString, @p, 1))

            IF @c BETWEEN 48 AND 57

                OR @c BETWEEN 65 AND 90

                OR @c BETWEEN 97 AND 122

                  --OR @c = 32

                SET @OutputString = @OutputString + CHAR(@c)

            SET @p = @p + 1

        END

    IF LEN(@OutputString) = 0

        RETURN NULL

    RETURN @OutputString

END

How to use it.

SELECT dbo.RemoveSpecialChars ('This string contains special chracters:/ Which * & we % need @ to #remove')

According to this function space is a special character and if you want to ignore spaces then uncomment “OR @c = 32”.

SQL Server: How to Remove Extra Spaces From String Value

Extra spaces between characters of a string value is a common problem and if you’re a developer then you must have faced the problem. On request of a blog reader here is a script from my query bank which I like to use to remove such extra spaces.

--Create a temp table for testing our query

CREATE TABLE #ExtraSpaces ( MyVal VARCHAR(8000))

--Insert some value to test

INSERT  INTO #ExtraSpaces

SELECT  'This     is my                         message.               '

UNION ALL

SELECT 'This      message   contains            tabs and    extra       spaces'

-- Lets remove extra spaces and tabs

WHILE 1 = 1

    BEGIN

        UPDATE  #ExtraSpaces

        SET    MyVal = REPLACE(

SUBSTRING(MyVal, 1,

CHARINDEX('  ', MyVal, 1) - 1) + ' '

                + LTRIM(

SUBSTRING(MyVal,

CHARINDEX('  ', MyVal, 1), 8000)),'  ',' ')

        WHERE   CHARINDEX('  ', MyVal, 1) > 0

        IF @@rowcount = 0

            BREAK

    END

--Lets see the updated result

SELECT  MyVal FROM    #ExtraSpaces

--drop temp table when not required

DROP TABLE #ExtraSpaces

SQL Server: Query Template, a Time Saving Feature

While working with SQL Server, its common to have a saved script which you need to execute for different server or databases and usually you open this saved script, make changes according to requirements and execute. SQL Server 2008 has one more time saving facility. You can save your script as template, like following is a simple query script, which I like to execute for different databases and with different order by clause.

USE AdventureWorks

GO

SELECT     s.SalesPersonID, c.Title, c.FirstName, 

c.MiddleName, c.LastName, c.Suffix,e.Title AS JobTitle,s.SalesQuota, s.SalesYTD

FROM        Sales.SalesPerson AS s INNER JOIN

HumanResources.Employee AS e 

ON e.EmployeeID = s.SalesPersonID

LEFT OUTER JOIN

Sales.SalesTerritory AS st 

ON st.TerritoryID = s.TerritoryID 

INNER JOIN

Person.Contact AS c 

ON c.ContactID = e.ContactID 

ORDER BY c.FirstName

One solution to avoid any labor work to change above script manually according to my requirements, is to create a dynamic query. But, in SQL Server 2008 I would prefer Query Template, by saving above query in following Template.

USE 

GO

SELECT     s.SalesPersonID, c.Title, c.FirstName, 

c.MiddleName, c.LastName,

c.Suffix,e.Title AS JobTitle,s.SalesQuota, s.SalesYTD

FROM        Sales.SalesPerson AS s INNER JOIN

HumanResources.Employee AS e 

ON e.EmployeeID = s.SalesPersonID

LEFT OUTER JOIN

Sales.SalesTerritory AS st 

ON st.TerritoryID = s.TerritoryID 

INNER JOIN

Person.Contact AS c 

ON c.ContactID = e.ContactID 

ORDER BY 

Next time when I need to execute this script, I will just open the template and press Ctlr+Shift+M or click on replace button, and it will ask me  for template parameters value and on pressing OK button it will replace template parameters with my values on whole script.

Defining a template needs three parameters separated with comma and enclosed with < and > signs.

<TemplateParameter, ParameterDataType, DefaultValue>

Replace Button

SQL Server: sp_WhoIsActive, a Must Tool for a DBA

A mail with subject “Low Database Response” or a call with words “What happened to the database?” is normal in a SQL Server DBA’s life. To cope with such bad situations normally a DBA keeps his drawer filled with necessary tools and scripts. But like me, most of DBAs first query to check “What is happening actually” is shortest query in SQL Server i.e. sp_who or sp_who2. Second returns almost same data to sp_who but with more detail information.

sp_who 

sp_who2 

Though both system stored procedures are helpful to collect basic information to find out problematic sessions of database but to reach your desired rows, you have to skim through large number of rows and you will find more then 95% of rows useless. Some time information provided by sp_who or sp_who2 is not enough to understand the real problem which leads you to query some other DMVs for such information.

Thanks to Adam Machanic who resolved this problem. His stored procedure WhoIsActive is a must tool for a DBAs kit. This single stored procedure collects almost all necessary information for a DBA, which will be helpful to understand the real problem of a database. The beauty of this stored procedure is that it collects information using almost 15 DMVs but still returns only necessary rows.

SQL Server: Short Code Doesn’t Mean Smart Code

Recently a blog reader shared an interesting thing. This mail was basically in response to my early post Why to Avoid TRIM functions in WHERE and JOIN clauses,  where we have discussed, that why we should avoid functions (user defined or system) in where clause columns, because these functions in WHERE, JOIN and GROUP  clauses mislead query optimizer for proper index selection and ultimately results in poor query performance.

Blog reader asked that he tried to remove functions from WHERE clause of all the queries but few queries where date was involve, was hard to correct and after asking at some forum he got a solution and now his code is shorter and quicker.

Actual query was something like as following:

Use AdventureWorks

Go

DECLARE @FindDate DATETIME

SET @FindDate = '2005/09/12 12:00:00'

SELECT  *

FROM    Purchasing.PurchaseOrderDetail

WHERE   CAST(CONVERT(VARCHAR(30), ModifiedDate, 101) AS DATETIME) = CAST(CONVERT(VARCHAR(30), @FindDate, 101) AS DATETIME)

It’s an everyday query, where records from “Purchasing.PurchaseOrderDetail” table are required but where modifieddate column values are equal to given parameter (date). But comparison should be based on date only and time portion should be ignored.

Modified smart query :

SELECT  *

FROM    Purchasing.PurchaseOrderDetail

WHERE   DATEDIFF(DD,ModifiedDate,@FindDate) = 0

Though new query is shorter, but is it quick? Let’s checkout input/output statistics, query time and execution plan for both quires.

Table 'PurchaseOrderDetail'. Scan count 1, logical reads 66, physical reads 0

CPU time = 0 ms,  elapsed time = 11 ms.

Table 'PurchaseOrderDetail'. Scan count 1, logical reads 66, physical reads 0

CPU time = 0 ms,  elapsed time = 9 ms

Though second query looks more smart but if we ignore minor difference of query elapsed time, both query are almost same as both queries are using clustered index scan and have same value of logical reads. Because problem still exists i.e. Function on WHERE clause columns. For best query performance we have to get rid of this DATEDIFF function too. Here is a better version, as per performance and not the code because our first priority should be performance.

SELECT  *

FROM    Purchasing.PurchaseOrderDetail

WHERE   ModifiedDate >= CAST(

CONVERT(VARCHAR(30), @FindDate, 101) AS DATETIME)

        AND ModifiedDate < = DATEADD(SS, 86399,

                                     CAST(CONVERT(VARCHAR(30), @FindDate, 101) AS DATETIME))

Query code is even more lengthy then first version but what about performance, lets check out input/output stats with query time.

Table 'PurchaseOrderDetail'. Scan count 1, logical reads 4, physical reads 0

CPU time = 0 ms,  elapsed time = 1 ms.

From execution plan, it’s clear that after removing functions from modifieddate column (used in WHERE clause), query optimizer selected proper non clustered index, which searched only 4 pages for result and finally query performance is increased.

SQL Server: Conditional WHERE clause (Filter for a Filter)

No one is unaware of WHERE clause. Everyone knows that we can filter output records by using WHERE clause but recently I found my team member stuck when he need a Conditional WHERE clause (filter for a filter).  Let’s try to figure out the problem and its solution with a simple scenario.

Suppose we have a table to keep students result with follow structure

CREATE TABLE #Result

    (

      StudentId INT,

      TeacherId INT,

      GroupId INT,

      Result VARCHAR(10),

      MarksObtained INT

    )

GO

INSERT INTO #Result

SELECT 101,1,1,'PASS',510 UNION ALL

SELECT 102,1,1,'PASS',622 UNION ALL

SELECT 103,2,1,'FAIL',174 UNION ALL

SELECT 104,2,2,'PASS',652 UNION ALL

SELECT 105,3,2,'FAIL',134

Our requirement is to create a stored procedure with only two parameters, one for id (it could be student, teacher or group id), we will call it @id and other to hold information that will decide that what type of id is being passed to stored procedure i.e. student, teacher or group, we will call it @idType

DECLARE @Id INT -- It could be StudentId,TeacherId,GroupId

DECLARE @IdType VARCHAR(10) -- Type could be Student,Teacher or Group

We need a query which can be used for all three criteria

SELECT @Id = 2, @IdType= 'Teacher'

--OR-- @Id = 102, @IdType= 'Student'

--OR-- @Id = 1, @IdType= 'Group'

Let’s move to our targeted query, with conditional where clause.

SELECT * FROM #Result

WHERE 1 = (CASE

            WHEN @IdType='Student' AND StudentId = @Id

                  THEN 1

            WHEN @IdType='Teacher' AND TeacherId = @Id

                  THEN 1

            WHEN @IdType='Group' AND GroupId = @Id

                  THEN 1

            ELSE 0 END)
(Note: TSQL is a rich query language, a problem can be solved by several methods. Above query is just written to show conditional WHERE clause)