Monthly Archives: May 2011

>SQL Server: What I Have Learned in May 2011

Dedicated to Most Energetic SQL Expert Gail Shaw

SQL Tips & Tricks:
Third Party Tools:
Performance Tuning:
SQL Server Management Studio:
Database Management Views & Functions:
Advertisements

>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: Shortcuts 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.

>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.