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)

Feedback:

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

Feedback:

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.

Feedback:

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.

Feedback:

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.

Feedback:

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

Feedback:

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.

Feedback:

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

Feedback:

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

Feedback:

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

Feedback:

Important DMVs to monitor CPU – SQL Server

Dynamic Management Views (DMVs) can be very useful to diagnose and troubleshoot common performance problems in SQL Server. Here are some important Dynamic Management Views (DMV’s) to monitor the CPU where SQL Server is installed.

Click on the DMV’s given below to learn more on how to use them.

sys.dm_os_threads - Returns a list of all SQL Server Operating System threads that are running under the SQL Server process. sys.dm_os_threads can provide information about rogue threads that consume resources in the SQL Server process.

sys.dm_os_workers - Returns a row for every worker in the system. sys.dm_os_workers helps you find out how long a worker has been running in a SUSPENDED or RUNNABLE state.

sys.dm_os_schedulers - Returns one row per scheduler in SQL Server where each scheduler is mapped to an individual processor. sys.dm_os_scheduler helps to monitor the condition of a scheduler or to identify runaway tasks.

sys.dm_os_tasks - Returns one row for each task that is active in the instance of SQL Server. sys.dm_os_tasks helps monitor parallel requests. You can also associate a session ID value with a Windows thread ID and then monitor the performance of the thread in the Windows Performance Monitor.

Another important document I would suggest all of you read is Troubleshooting CPU Bottlenecks in SQL Server

Feedback:

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. A very good link to check out the different Date and Time styles is the CAST and CONVERT functions in BOL

Here’s an example of displaying dates in ‘dd/mm/yyyy’ format

DECLARE @TT TABLE
(
   ID int,
   DateOfEntry DateTime
)

INSERT INTO @TT
SELECT 1, '08/22/2010' UNION ALL
SELECT 2, '09/01/2010' UNION ALL
SELECT 3, '10/03/2010' UNION ALL
SELECT 4, '10/07/2010'

SELECT ID, CONVERT(VARCHAR(10),DateOfEntry,103) AS 'dd/mm/yyyy'
 FROM @TT

OUTPUT

Feedback:

AutoGenerate Numeric ID’s in SQL Server with No Sequence

Sometime back, I had written a post to AutoGenerate an AlphaNumeric Sequence in SQL Server

Tumo Jobas wrote back today asking if it was possible to use a similar method to generate a 10 digit number but with no sequence. Here’s one way that SQL MVP Peter Larsson uses often using NEWID() and I think it’s pretty slick. This solution will work in SQL Server 2005/2008.

DECLARE @TT TABLE
(
ID  int, CircuitName varchar(10),
NonSeqID  AS ABS(CHECKSUM(NEWID())) % 9000000000 + 1000000000
)

INSERT @TT
SELECT 1, 'Circuit 1' UNION ALL
SELECT 2, 'Circuit 2' UNION ALL
SELECT 3, 'Circuit 3' UNION ALL
SELECT 4, 'Circuit 4' UNION ALL
SELECT 5, 'Circuit 5' UNION ALL
SELECT 6, 'Circuit 6' UNION ALL
SELECT 7, 'Circuit 7' UNION ALL
SELECT 8, 'Circuit 8' UNION ALL
SELECT 9, 'Circuit 9' UNION ALL
SELECT 10, 'Circuit 10'

SELECT * FROM @TT

OUTPUT

As shown above, the NonSeqID gets generated automatically using the NEWID() system function.

Feedback:

Duplicate a Column in SQL Server

Here’s a very simple but useful post that shows how to duplicate an existing column in SQL Server.

Assuming we have the following table structure with data:

CREATE TABLE #TmpTable(ColA varchar(10))
INSERT INTO #TmpTable SELECT 'ValueOne'
INSERT INTO #TmpTable SELECT 'ValueTwo'
INSERT INTO #TmpTable SELECT 'ValueThree'
INSERT INTO #TmpTable SELECT 'ValueFour'
INSERT INTO #TmpTable SELECT 'ValueFive'
INSERT INTO #TmpTable SELECT 'ValueSix'

Now to duplicate ColA, use the following code:

ALTER TABLE #TmpTable
ADD ColB varchar(10)
GO

UPDATE #TmpTable
SET ColB = ColA
GO

SELECT * FROM #TmpTable

As you can see, we have added a new column to the Table and used a simple Update statement to duplicate ColA. If the column already exists, you can avoid the ALTER TABLE.

OUTPUT

Feedback:

Check if Temp Table Exists and Drop it in SQL Server

I use a lot of Temp Tables in my examples. A SqlServerCurry.com reader Rishab asked me if there was a way to check for an existing Temp Table and drop it using T-SQL. Temp Tables like Table Variables have a session level scope and not a statement-level scope, so the following query will give an error

CREATE TABLE #TmpTable(i int)
INSERT INTO #TmpTable
SELECT 345
GO
CREATE TABLE #TmpTable(i int)
INSERT INTO #TmpTable
SELECT 200

Note: Madhivanan wrote a nice post about Temp Table VS Table Variable in SQL Server

To avoid this error, here' a way to check if a Temp Table exists and drop it

-- Check if TmpTable exists and Drop it
IF OBJECT_ID('tempdb..#TmpTable') IS NOT NULL
DROP TABLE #TmpTable

-- Create TmpTable
CREATE TABLE #TmpTable(i int)
INSERT INTO #TmpTable
SELECT 200

Feedback:

SQL Server 2008 Service Pack 2 (SP2) Released

Microsoft recently released SQL Server 2008 Service Pack 2 (SP2).

As given in the release documentation, here are some new improvements around reporting and management:

15K partitioning Improvement. Introduced support for a maximum of 15,000 partitions in tables and indexes in Microsoft SQL Server 2008 Service Pack 2 in the Enterprise, Developer and Evaluation Editions.

Reporting Services in SharePoint Integrated Mode. SQL Server 2008 SP2 provides updates for Reporting Services integration with SharePoint products. SQL Server 2008 SP2 report servers can integrate with SharePoint 2010 products. SQL Server 2008 SP2 also provides a new add-in to support the integration of SQL Server 2008 R2 report servers with SharePoint 2007 products.

SQL Server 2008 R2 Application and Multi-Server Management Compatibility with SQL Server 2008.

SQL Server 2008 Instance Management.With SP2 applied, an instance of the SQL Server 2008 Database Engine can be enrolled with a SQL Server 2008 R2 Utility Control Point as a managed instance of SQL Server.

Data-tier Application (DAC) Support.Instances of the SQL Server 2008 Database Engine support all DAC operations delivered in SQL Server 2008 R2 after SP2 has been applied. You can deploy, upgrade, register, extract, and delete DACs. SP2 does not upgrade the SQL Server 2008 client tools to support DACs. You must use the SQL Server 2008 R2 client tools, such as SQL Server Management Studio, to perform DAC operations. A data-tier application is an entity that contains all of the database objects and instance objects used by an application. A DAC provides a single unit for authoring, deploying, and managing the data-tier objects.

Download SQL Server 2008 Service Pack 2 (SP2)

Feedback: