T-SQL and SQL Server Administration Articles Link List – October 2010

Here’s a quick wrap up of the articles published on this blog in the month of October

T-SQL Posts

Check if Temp Table Exists and Drop it in SQL Server - Check for an existing Temp Table and drop it using T-SQL

Duplicate a Column in SQL Server - A very simple but useful post that shows how to duplicate an existing column in SQL Server.

AutoGenerate Numeric ID’s in SQL Server with No Sequence - Generate a 10 digit number but with no sequence

Display Dates in a Particular Format in SQL Server - Questions of displaying dates in either ‘mm/dd/yy’ format or ‘dd/mm/yyyy’ format and so on have been asked numerous times. Here’s a solution.

Truncate Hour, Minute, Second and MilliSecond in SQL Server - How to Truncate a DateTime in SQL Server.

Find and Delete Duplicate Rows From Tables without Primary Key - How to find and delete duplicate rows from sample tables, which do not have a Primary Key

Analyze and validate T-SQL code using SQL Enlight - How to use SQL Enlight tool to analyze and validate T-SQL Code

Calculate Running Total and Row Total in SQL Server - How to calculate Row Totals and Running Totals in SQL Server

List all System Stored Procedures in SQL Server - How to display all schema-scoped user-defined objects as well as system objects.

Generate Numbers Between two Numbers in SQL Server - Generate numbers between two numbers in the shortest possible way

Execute T-SQL Code Only on Certain Days - Simple tip using IF-ELSE to prevent my T-SQL code from running on Saturday’s

SQL Server Administration

Important DMVs to monitor CPU – SQL Server - Some important Dynamic Management Views (DMV’s) to monitor the CPU where SQL Server is installed

Get Free Disk space using SQL Server - Programmatically determine Free Hard Disk Space using T-SQL.

Miscellaneous

Enter a SQL Server Quiz to win Prizes - Jacob Sebastian of the BeyondRelational fame is hosting a SQL Server Quiz which gives you the chance to win lots of prizes

SQL Server 2008 Service Pack 2 (SP2) Released - News about Microsoft recently releasing SQL Server 2008 Service Pack 2 (SP2)

Execute T-SQL Code Only on Certain Days

I always clean up my databases manually on Sundays. But at times, if need be, I clean it up on other days as well. However I never do it on Saturdays, since the traffic is usually the most on that day.

So here’s a simple tip using IF-ELSE to prevent my T-SQL code from running on Saturday’s, even if I run the code manually

IF DATEPART(dw,GETDATE())<> 7  -- 7 is Saturday, 1 is Sunday
BEGIN
    -- YOUR T-SQL Code Comes Here   
     SELECT GETDATE()
     RETURN
END
ELSE
BEGIN
    PRINT '*** Sorry This Code will Not Execute on Saturdays'
END

OUTPUT

You may also want to read Make a T-SQL Query Sleep for a certain amount of time

Generate Numbers Between two Numbers in SQL Server

I was working on a query where I had generate numbers between two numbers in the shortest possible way. Here’s how to do it. In this example, we will be generating all the numbers between 10 and 18 (inclusive of both) using a quick and dirty way

SELECT DISTINCT number
FROM master..spt_values
WHERE number
BETWEEN 10 AND 18

OUTPUT

As I said, we needed a quick way to generate numbers. If you are looking for Production ready code, check this article here Creating a Number table in T-SQL which offers various ways of generating numbers, including a nice one from Itzik Ben-Gan.

List all System Stored Procedures in SQL Server

In order to list all the System Stored Procedures in SQL Server 2005/2008, you can query the sys.all_objects catalog view, that displays all schema-scoped user-defined objects as well as system objects.

SELECT NAME FROM SYS.ALL_OBJECTS WHERE type='P'

OUTPUT (Partial)

Similarly to find CLR Stored Procedures, use type=’PC’

SELECT NAME FROM SYS.ALL_OBJECTS WHERE type='PC'

OUTPUT

Note: You can add ‘is_ms_shipped = 1’ to the query if you intend listing only those objects created by an internal SQL Server component.

Enter a SQL Server Quiz to win Prizes

Jacob Sebastian of the BeyondRelational fame is hosting a SQL Server Quiz which gives you the chance to win lots of prizes.

Each SQL Server quiz will be a series of 31 questions, managed by 31 quiz masters who are experts in SQL Server (MVP’s).

Each quiz master will ask one question each and will moderate the discussions and answers and finally will identify the winner of each quiz. This quiz series is scheduled from the 1st to 31st of October with a new question asked every day. Each question will be open for 30 days and at the end of 30 day period, the evaluation team will review your answers/explanation and grade them.

Check out the Questions and Good luck! You can visit the T-SQL Tips page to check if any of the Tips solve a question.

Calculate Running Total and Row Total in SQL Server

Let us see how to calculate Row Totals and Running Totals in SQL Server. In the code shown below, we will first calculate the sum of Col1 and Col2 for each row and then also maintain a Running Total of the same.

Update: As williamdurkin and TheSQLGuru pointed out, the method in the T-SQL query below does not guarantee the order that the update runs in.

The correct solution is given by Jeff Moden over here http://www.sqlservercentral.com/articles/Advanced+Querying/61716/ (Requires Registration to view) . Thanks guys for the correction!

SAMPLE CODE

CREATE TABLE #TmpTable
(
    ID int, Col1 int, Col2 int,
    RowTotal int, RunningTotal int
)

INSERT INTO #TmpTable SELECT 1, 5, 2, 0, 0
INSERT INTO #TmpTable SELECT 2, 14, 65, 0, 0
INSERT INTO #TmpTable SELECT 3, 34, 22, 0, 0
INSERT INTO #TmpTable SELECT 4, 56, 22, 0, 0
INSERT INTO #TmpTable SELECT 5, 7, 23, 0, 0

QUERY (results not guaranteed)

DECLARE @rowtot int
DECLARE @runtot int
SET @rowtot = 0 -- set rowtotal to 0
SET @runtot = 0 -- set runningtotal to 0

UPDATE #TmpTable
SET RowTotal = @rowtot,
RunningTotal = @runtot,
@rowtot = COALESCE(Col1, 0) + COALESCE(Col2, 0),
@runtot = @runtot + @rowtot

SELECT * FROM #TmpTable

The code is quite easy to understand. We are maintaining two variables @rowtot (for RowTotal) and @runtot (Running Total) and use the Update Table command to update the Row Total and Running Total for each row.

OUTPUT

Analyze and validate T-SQL code using SQL Enlight

This article has been guest blogged by Iliyan Stoyanov of Ubitsoft

As databases get bigger, the number of database objects increase and the amount of business logic implemented using T-SQL code becomes quite substantial, often even more critical than the application code; the automation of the process of ensuring code quality is becoming more and more important.

Static code analysis is a popular method for code verification and defect detection in the .NET, C++ and Java development worlds, but very often left behind and neglected by the SQL Server development community.

SQL Enlight is a tool for SQL Server that can automate and facilitate the T-SQL code and database schema analysis. The tool works in a way similar to the .NET focused tools like FxCop and StyleCop. It provides design-time code, database schema and query plan analysis. The tool comes with about 70 out of the box analysis rules and also supports custom analysis rules.

Code Analysis

One of the supported types of analysis by SQL Enlight is the T-SQL script analysis. It can be used validating the code for syntax errors as well as for conforming to best practices or your own standards and requirements.

Some of the T-SQL code aspects that can be checked are:

• Deprecated syntax (old outer joins, IMAGE and TEXT data types, deprecated stored procedures usage and etc.)
• Naming conventions ( ‘sp_’ prefix, special characters or reserved keywords in object names)
• Not recommended statements, expressions and operators that can impact maintenance, produce unwanted results or have a good chance to be written by mistake.
• Statements, expressions and operators that may have impact on the query performance

Database Analysis

Besides the T-SQL code analysis, SQL Enlight supports database schema analysis and can combine and use both forms of analysis.

The database schema analysis can identify several issues that can have negative impact on the performance:

• Query performance issue because not optimal index usage
• Index fragmentation issues
• Missing or not used indexes
• Missing primary keys and clustered indexes
• Missing column and index statistics
• Missing indexes on foreign key columns
• Inefficient indexes which may be dropped
• Existence of better candidate for clustered index than the current table index
• Overlapping or duplicate indexes
• Outdated statistics

Database analysis can be applied at once on single or multiple databases as well as on a SQL Server instance.

Query Execution Plan Analysis

Viewing the graphical execution plan in SQL Server Management Studio provides a great way to visually read through the operations of the statements of the execution plan and identify possible issues which may affect your query performance. This is a fairly useful feature, but it has some limitations:

• Large plans can be hard and time consuming to read.
• The execution plan for procedures, views and functions cannot be viewed directly.
• Encrypted procedures, views and functions are not supported.

Next to these, reading and analyzing through all your database objects and scripts can take quite some time when you have several dozen or more of T-SQL database objects, especially when there are more than one developer responsible for modifying and maintaining the specific database. Adding or dropping indexes, creating or updating statistics, inserting, deleting or updating large amount can negatively affect the performance of existing stored procedures, views and functions. The impact can be often hard to as it may require the affected T-SQL objects and their execution plans to be revised for issues.

SQL Enlight provides a feature which can very well automate and provide solution for the above problems:

• Automate execution plan analysis, identify and notify for: missing indexes that have high impact on the analyzed query; Bookmark Lookups(Key Lookup, RID Lookup), Table Scan, Index Scan, Sort and Hash Match operations that have significant estimated operator cost.
• Support for direct execution plan analysis of stored procedure, views and functions.
• At once execution plan analysis of all T-SQL objects in one or all databases of a single SQL Server instance.

Additional Features

SQL Enlight is provided as an add-on to Microsoft SQL Server Management Studio and Microsoft Visual Studio. The tool has a command line version and includes MSBuild tasks which can be used for scheduling analysis or integrating with automated builds. Along with the static analysis, SQL Enlight offers a set of T-SQL code refactoring features: code layout and formatting, encapsulate script, script summary and executed statements history.

The best way to find out how SQL Enlight will work for you is to download and try it out during the 14-day free trial.

Find and Delete Duplicate Rows From Tables without Primary Key

Someone recently asked me how to find and delete duplicate rows from sample tables, which do not have a Primary Key. I have written one such sample on MSDN code Find and/or Delete Duplicate Rows which I will share here

SAMPLE Data

-- Suppress data loading messages
SET NOCOUNT ON

-- Create Table
CREATE TABLE #Customers (ID integer, CustName varchar(20), Pincode int)

-- Load Sample Data in Table
INSERT INTO  #Customers VALUES (1, 'Jack',45454 )
INSERT INTO  #Customers VALUES (2, 'Jill', 43453)
INSERT INTO  #Customers VALUES (3, 'Tom', 43453)
INSERT INTO  #Customers VALUES (4, 'Kathy', 22343)
INSERT INTO  #Customers VALUES (5, 'David', 65443)
INSERT INTO  #Customers VALUES (6, 'Kathy', 22343)
INSERT INTO  #Customers VALUES (7, 'Kim', 65443)
INSERT INTO  #Customers VALUES (8, 'Hoggart', 33443)
INSERT INTO  #Customers VALUES (9, 'Kate', 61143)
INSERT INTO  #Customers VALUES (10, 'Kim', 65443)

To indentify duplicate rows, the trick is to Group the rows by CustName, Pincode. Rows having similar CustName and Pincode will have more than one rows in the grouping. So locate them using HAVING COUNT(*) > 1. If duplicate values are encountered, return the maximum ID for each duplicate row. Using the outer query, delete any ID returned by subquery.

-- Find Duplicate Rows
SELECT    MAX(ID) as ID, CustName, Pincode FROM #Customers
GROUP BY CustName, Pincode
HAVING COUNT(*) > 1

-- Delete Duplicate Rows
DELETE FROM #Customers
WHERE ID IN
( SELECT    MAX(ID) FROM #Customers
GROUP BY CustName, Pincode
HAVING COUNT(*) > 1)

SELECT * FROM #Customers

OUTPUT

Truncate Hour, Minute, Second and MilliSecond in SQL Server

Here’s how to Truncate a DateTime in SQL Server

SELECT GETDATE() as CurrentDateTime;
SELECT DATEADD(day,DATEDIFF(day,0,GETDATE()),0) as [Truncate-HrMinSecMilliSec];
SELECT DATEADD(hour,DATEDIFF(hour,0,GETDATE()),0) as [Truncate-MinSecMilliSec];
SELECT DATEADD(minute,DATEDIFF(minute,0,GETDATE()),0) as [Truncate-SecMilliSec];

Now when I did the same for seconds using

SELECT DATEADD(second,DATEDIFF(second,0,GETDATE()),0);

I got the error

Msg 535, Level 16, State 0, Line 1

The datediff function resulted in an overflow. The number of dateparts separating two date/time instances is too large. Try to use datediff with a less precise datepart.

To resolve the error, do the following:

SELECT DATEADD(second,DATEDIFF(second, '2010-10-01',GETDATE()),'2010-10-01') as [Truncate-MilliSec];

Here’s the Output after running all the queries

Get Free Disk space using SQL Server

In order to programmatically determine Free Hard Disk Space using T-SQL, use the extended stored procedure master.dbo.xp_fixeddrives which returns a list of all hard drives and the free space(in MB) for each of the hard drive.

DECLARE @OSDriveSpace TABLE
(
  DriveNm char(1),
  FreeDriveSpaceInMB int
)

INSERT INTO @OSDriveSpace
EXEC master.dbo.xp_fixeddrives

SELECT *  FROM @OSDriveSpace

Note: The T-SQL query shown above does not take into account Volume Mount Points. If you need to include mount points and monitor them, then make sure you check out this article CLR stored procedure to get disk space information which shows how to use a CLR stored procedure to return the capacity and free space of a mount point.

OUTPUT