Thursday, May 26, 2011

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'

Wednesday, May 25, 2011

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


Tuesday, May 24, 2011

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

Monday, May 23, 2011

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.

Friday, May 20, 2011

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

Thursday, May 19, 2011

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


Wednesday, May 18, 2011

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

Friday, May 13, 2011

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.



Tuesday, May 10, 2011

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.

Friday, May 6, 2011

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)