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:
>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)
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.
Connect SQL 