SQL Server Code,Tips and Tricks, Performance Tuning

Wednesday, August 02, 2006

Tuesday, August 01, 2006

6 Different Ways To Get The Current Identity Value From A Table

This article will show you how to get the current identity value from a table and also some things that might act a little different than you would expect

Let's first create our two simple tables
CREATE TABLE TestOne (id INT identity,SomeDate DATETIME)
CREATE TABLE TestTwo (id INT identity,TestOneID INT,SomeDate DATETIME)

--Let's insert 4 rows into the table
INSERT TestOne VALUES(GETDATE())
INSERT TestOne VALUES(GETDATE())
INSERT TestOne VALUES(GETDATE())
INSERT TestOne VALUES(GETDATE())

Here are 6 ways to check for the current value

--1 @@IDENTITY
SELECT @@IDENTITY
--this returns 4

--2 DBCC CHECKIDENT
DBCC CHECKIDENT (TestOne, NORESEED)
after running DBCC CHECKIDENT the message returned is
Checking identity information: current identity value '4', current column value '4'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

--3 MAX function
SELECT MAX(id)
FROM TestOne
you can also check with the MAX function but this is not recommended becuase you might get some other identity value that is not yours but from a different user

--4 TOP 1 and ORDER BY DESC
SELECT TOP 1 id
FROM TestOne
ORDER BY id DESC
--The same applies here as for the max function, this is not recommended

--5 IDENT_CURRENT
SELECT IDENT_CURRENT('TestOne')
--IDENT_CURRENT is another way to check

--6 SCOPE_IDENTITY
SELECT SCOPE_IDENTITY()
--This one is very similar to @@IDENTITY with one BIG difference (shown later)

--Let's add a trigger to the TestOne table
CREATE TRIGGER trTestOne ON [dbo].[TestOne]
FOR INSERT
AS
DECLARE @CreditUserID INT

SELECT @CreditUserID = (SELECT ID FROM Inserted)

INSERT TestTwo VALUES(@CreditUserID,GETDATE())
GO

--Let's insert another row into the TestOne table
INSERT TestOne VALUES(GETDATE())

SELECT @@IDENTITY --1
SELECT @@IDENTITY,SCOPE_IDENTITY(),IDENT_CURRENT('TestOne') --1,5,5

Now why doesn't @@IDENTITY return 5 but 1?
This is because @@IDENTITY doesn't care about scope and returns the last identity value from all the statements, which in this case is from the code within the trigger trTestOne

So the bottom line is this: Always use SCOPE_IDENTITY() unless you DO need the last identity value regradless of scope (for example you need to know the identity from the table insert inside the trigger)

--Clean up this mess
DROP TABLE TestOne,TestTwo

Wednesday, July 26, 2006

@@ROWCOUNT And Trigger Woes

Here I go again linking to other people's posts instead of writing my own ;-(
Louis Davidson has a nice little entry on his SQL Doctor blog about Triggers and the @@ROWCOUNT function
You can check it out here

Tuesday, July 25, 2006

Integer Math In SQL Server

If you divide with 2 integers in SQL Server the result will be also an integer( 3/2 =1, 5/2 =2)
If you need the fractions then you need to convert/cast to decimal/numeric or multiply one of the integers by 1.0

Let's test this out

DECLARE @Value1 INT
DECLARE @Value2 INT

SELECT @Value1 =3,@Value2 =2

--here is where we get 1 instead of 1.5
SELECT @Value1/@Value2

--after using convert the result is correct
SELECT @Value1/CONVERT(DECIMAL(3,2),@Value2)

--after using cast the result is correct
SELECT @Value1/CAST(@Value2 AS DECIMAL(3,2))

--after multiplying with 1.0 the result is correct
SELECT @Value1/(@Value2*1.0)

--this won't work since the multiplication is done after we have the result
SELECT @Value1/@Value2*1.0

Return All the Rows From A Table Where At Least One Of The Columns IS NULL

Sometimes you inherit a table/database and you need to check if any of the rows in the table have columns with null values
I will show you a great timesaver to accomplish this task
First let's create the table

CREATE TABLE TestNulls (c1 INT,c2 INT, c3 INT,c4 INT,c5 INT, c6 INT)
INSERT TestNulls VALUES (1,1,1,1,1,1)
INSERT TestNulls VALUES (1,1,1,1,1,1)
INSERT TestNulls VALUES (1,1,1,1,1,1)
INSERT TestNulls VALUES (1,NULL,1,1,1,1)
INSERT TestNulls VALUES (1,1,1,1,1,1)
INSERT TestNulls VALUES (1,1,1,1,1,1)
INSERT TestNulls VALUES (1,1,1,NULL,1,1)

One way to check is to write an OR for every column
SELECT *
FROM TestNulls
WHERE C1 IS NULL
OR C2 IS NULL
OR C3 IS NULL
OR C4 IS NULL
OR C5 IS NULL
OR C6 IS NULL

A better (faster) way is to do it like this

SELECT *
FROM TestNulls
WHERE C1+C2+C3+C4+C5+C6 IS NULL

What if you have 50 columns, who wants to write them all out? Well not me.
No problem in Query Analyzer hit F8, this will bring up the Object Browser
In SQL Server Managment Studio use the Object Explorer
Navigate to the table click on the + and drag the whole column folder into the query window
You will see something like this c1, c2, c3, c4, c5, c6
Copy that open up notepad and paste it into notepad. Hit CTRL + H and in the Find box type a , and in the Replace box type a +, hit OK. After that do a CTRL + A and CTRL + C
Paste it back into Query Analyzer/SQL Server Managment Studio and you are done with that part

This will work everytime if all the columns are integers, what if you have varchars?
Let's test that out

CREATE TABLE TestNullsChar (c1 CHAR(1),c2 CHAR(1), c3 CHAR(1),c4 CHAR(1),c5 CHAR(1), c6 CHAR(1))
INSERT TestNullsChar VALUES (1,1,1,1,1,1)
INSERT TestNullsChar VALUES (1,1,1,1,1,1)
INSERT TestNullsChar VALUES (1,1,1,1,1,1)
INSERT TestNullsChar VALUES (1,NULL,1,1,1,1)
INSERT TestNullsChar VALUES (1,1,1,1,1,1)
INSERT TestNullsChar VALUES (1,1,1,1,1,1)
INSERT TestNullsChar VALUES (1,1,1,NULL,1,1)

No problem here (depending on your CONCAT_NULL_YIELDS_NULL setting)

SELECT *,C1+C2+C3+C4+C5+C6
FROM TestNullsChar
WHERE C1+C2+C3+C4+C5+C6 IS NULL

Let's set our CONCAT_NULL_YIELDS_NULL setting to off
SET CONCAT_NULL_YIELDS_NULL OFF

Run the query again

SELECT *,C1+C2+C3+C4+C5+C6
FROM TestNullsChar
WHERE C1+C2+C3+C4+C5+C6 IS NULL

As you can see nothing was returned so make sure that CONCAT_NULL_YIELDS_NULL is set to on before executing such a query because NULL + any other value will return NULL if CONCAT_NULL_YIELDS_NULL is set to on

SET CONCAT_NULL_YIELDS_NULL ON

Monday, July 24, 2006

Undocumented Options For UPDATE STATISTICS

Since I am still sleep deprived and can't think straight I have decided to post a link to a good article on the Tips, Tricks, and Advice from the SQL Server Query Optimization Team blog about some undocumented options that can be used with UPDATE STATISTICS

The link to that post is here: UPDATE STATISTICS Undocumented Options

Wednesday, July 19, 2006

A Father Again

I am a father again; my wife gave birth to 2 beautiful babies on Monday night. The boy is named Nicholas and the girl is name Catherine (yes just like Catherine the Great and Czar Nicholas II) I was working Monday and knew we were launching some new products this Wednesday. So I did all the work on staging on Monday and said to myself I will put it on the production box on Tuesday just in case my wife goes into labor. Well my wife didn’t feel that good on Monday and went in for a checkup and they decided to deliver the babies the same day. My son was with my wife so the person who I report to drove me to the hospital (I did not bring my keys to work) after that he drove me home so I could get some stuff and then he drove me back again (who else has a boss like that? ) A friend of ours came to the hospital to watch our son because my wife insisted that I had to be in the delivery room. My wife’s sisters were on vacation in Ocean City, they decided to visit that night and drove to the hospital. They arrived during the time that the babies were born. After the babies were born the sister took our son to our home and stayed there and I slept on the most comfortable (yeah right) hospital pull out chair. The sisters left on Tuesday and I went back home at 9PM. After putting my son to bed I logged in to my computer at work and worked for about an hour to move all the code over and to test that I didn’t mess up anything. My wife is still in the hospital but she will be coming home with the babies Friday morning. That’s it for now, next post will be SQL related again, probably sometime next week.

Below are some pictures, click on the pictures to get a bigger picture

Susan, Christian, Catherine and Nicholas

Nicholas

Catherine

Monday, July 17, 2006

Flat File Bulk Import Speed Comparison In SQL Server 2005

Mladen has compared the following 4 methods for importing a flat file in SQl Server 2005

1. BCP
2. Bulk Insert
3. OpenRowset with BULK option
4. SQL Server Integration Services - SSIS

Which do you think is the fastest? If you answered 1 or 2 you might be in for a surprise. Visit Mladen's blog to find out the answer

Three Ways To Get The Time Portion Of A Datetime Value

This popped up in a newsgroup today so I decided to do a little post about it. How do you get the time information only from a datetime value
These are at least 3 ways to do this

1) use the 3 different datepart functions and concatenate them
2) convert and grab the 8 right most characters
3) convert to varchar and use style 108

So here we go

--1 use the 3 different datepart functions and concatenate them
SELECT CONVERT(VARCHAR(2),DATEPART(HH,GETDATE())) + ':' +
CONVERT(VARCHAR(2),DATEPART(MI,GETDATE())) + ':' +
CONVERT(VARCHAR(2),DATEPART(S,GETDATE()))

--2 convert and grab the 8 right most characters
SELECT RIGHT(CONVERT(VARCHAR(22),GETDATE(),120),8)

--3 convert to varchar and use style 108
SELECT CONVERT(VARCHAR(12),GETDATE(),108)

Thursday, July 13, 2006

Visual Studio 2005 Team Edition for Database Professionals Interview On Channel 9

Channel 9 has a cool webcast with several people talking about Visual Studio 2005 Team Edition for Database Professionals (Data Dude)

From the site: "Cameron Skinner, Gert Drapers, Robert Merriman, Thomas Murphy, and Matt Nunn sat down me with to discuss a new edition to the Visual Studio Team product family: Visual Studio 2005 Team Edition for Database Professionals. We talked about why this product was created and Cameron (the product unit manager) gave a walk through of the current CTP release. Targeted for delivery at the end of the year, you can grab the CTP bits and learn more at the team’s website: http://msdn.microsoft.com/vstudio/teamsystem/dbpro/"

You can download the webcast here (http://channel9.msdn.com/Showpost.aspx?postid=212797) or click on the picture

Visual Studio 2005 Team Edition for Database Professionals CTP 4 Available

Visual Studio 2005 Team Edition for Database Professionals (Data Dude) CTP 4 is available for download

Brief Description
Community Technology Preview (CTP) 4
Tools for building SQL databases in a managed project environment with support for versioning, deployment, unit testing, refactoring, and off-line SQL development.

Overview
Tools for building SQL databases in a managed project environment with support for versioning, deployment, unit testing, refactoring, and off-line SQL development. This release is focused on completing a full functionality scenario for SQL 2000 and includes an updated project system with reverse engineering, a new SQL Query Editor, Schema and Data Compare, Data Generation, Unit Testing, and Rename Refactoring. This release does not have functionality changes from CTP3, only stability work and the new project UI.

Get it here

Wednesday, July 12, 2006

SqlBlog

Peter DeBetta and Adam Machanic have created SqlBlog.com. It has a very nice list of SQL Server blogs and if you click on roller you will see a about 40 of them displayed (mine is missing ;-( )
So what are you waiting for? Sign up here, I already did

Tuesday, July 11, 2006

Non Updating Update Performance Improvement In SQL Server 2005

When you do a non updating update like the one below

UPDATE t
SET i = 1
WHERE i=1

In SQL 2000, the lack of the optimization leads to updating the nonclustered index even if the value is not changing.

In the SQL 2005 plan, it is possible to appreciate
- a “Compute Scalar” operator that compares the current value and new value of the column being modified
- a new filter operator that on a row by row basis will determine whether the nonclustered index is being affected or not
- the fact that nonclustered index maintenance is now bypassed

Read the whole article (including statistics profile output screenshot) at the Tips, Tricks, and Advice from the SQL Server Query Optimization Team blog

Monday, July 10, 2006

Setting Identity Value Back To 1 After Deleting All Rows From A Table

Sometimes you want the identity value to start from 1 again after you delete all the rows from a table
There are 2 ways to accomplish that
1 Use the TRUNCATE TABLE command instead of DELETE
2 If you do use DELETE then run DBCC CHECKIDENT and reseed the table

Here is some code to explain what I mean

CREATE TABLE TestValues (Id INT identity,DateVale DATETIME)
INSERT INTO TestValues VALUES(GETDATE())
INSERT INTO TestValues VALUES(GETDATE())
INSERT INTO TestValues VALUES(GETDATE())
INSERT INTO TestValues VALUES(GETDATE())
INSERT INTO TestValues VALUES(GETDATE())

SELECT *
FROM TestValues

-- This is the output
-- 1 2006-07-10 12:31:17.050
-- 2 2006-07-10 12:31:17.050
-- 3 2006-07-10 12:31:17.050
-- 4 2006-07-10 12:31:17.050
-- 5 2006-07-10 12:31:17.050

DELETE TestValues

INSERT INTO TestValues VALUES(GETDATE())

SELECT *
FROM TestValues

-- Output
-- 6 2006-07-10 12:31:29.143

TRUNCATE TABLE TestValues

INSERT INTO TestValues VALUES(GETDATE())

SELECT *
FROM TestValues

-- Output
-- 1 2006-07-10 12:31:38.317

INSERT INTO TestValues VALUES(GETDATE())

DELETE TestValues

DBCC CHECKIDENT(TestValues,RESEED,0)
-- Message
-- Checking identity information: current identity value '2', current column value '0'.
-- DBCC execution completed. If DBCC printed error messages, contact your system administrator.

INSERT INTO TestValues VALUES(GETDATE())

SELECT *
FROM TestValues

-- Output
-- 1 2006-07-10 12:31:52.503

DROP TABLE TestValues

Building SQL Server Integration Services Packages

Learn how easy it is to build and debug your first SQL Server Integration Services (SSIS) Package. Donald Farmer introduces the development environment and the basic concepts of packages and walks through the creation and testing of a simple data flow. See why users describe SSIS as the most productive environment for data integration.

Watch the video at MSDN TV

Get the files here

Wednesday, July 05, 2006

Designing Effective Aggregations In Analysis Services 2005

If you're into Analysis Services, Integration Services, Reporting Services and BI in general - you'll want to check out Elizabeth Vitt's new blog I found the link to her blog on Kimberly Tripps blog

In her first entry, Elizabeth Vitt highlights the design components that determine which attributes are considered for aggregation:

Aggregation Definition
Attribute Relationships
Aggregation Candidates
Aggregation Usage

Read the article here: Influencing Aggregation Candidates

Tuesday, July 04, 2006

Free Quest LiteSpeed For SQL Server Developer Edition

Here is an Independence Day gift for you. You can get the fast backup and recovery solution LiteSpeed for free (to be used with the developer edition only)

From the Quest site:

"The fast backup and recovery solution for Microsoft SQL Server Developer Edition is here - LiteSpeed™ for SQL Server Developer Edition. LiteSpeed's low-impact, high-performance compression technology allows you to dramatically reduce storage costs and backup/recovery windows.

With LiteSpeed, you'll achieve:

Fast backup and recovery. LiteSpeed's technology achieves 50 percent faster backup and recovery times, allowing you to cut your restore time in half.
Reduced file sizes and storage costs. LiteSpeed's compression technology compresses data up to 95 percent, saving disk space.
Optimized backup and recovery. You'll maintain complete control, while backup and recovery performance is improved right out of the box.

Reduce the time and costs associated with backup and recovery projects today with this full-version download. LiteSpeed for SQL Server Developer Edition - a $45 value - is completely FREE to SQL Server Central readers for a limited time"

Get it here

Monday, July 03, 2006

DDL Triggers And Events

I promised I would write a 'real' SQl subject so here it is: DDL triggers and DDL events for use with DDL triggers

Let's start by creating 2 simple triggers

USE AdventureWorks
GO

CREATE TRIGGER ddlTestEvents1
ON DATABASE
FOR DROP_TABLE, ALTER_TABLE, CREATE_TABLE
AS
PRINT 'You must disable Trigger "ddlTestEvents1" to drop,create or alter tables!'
ROLLBACK;
GO

CREATE TRIGGER ddlTestEvents2
ON DATABASE
FOR DDL_TABLE_EVENTS
AS
PRINT 'You must disable Trigger "ddlTestEvents2" to drop, create or alter tables!'
ROLLBACK;
GO

Let's try creating a table
CREATE TABLE wasabi(id INT)
GO

And here is the error message, the first trigger fired
You must disable Trigger "ddlTestEvents1" to drop,create or alter tables!
Msg 3609, Level 16, State 2, Line 1
The transaction ended in the trigger. The batch has been aborted.

Let's disable the ddlTestEvents1 trigger and see what happens
DISABLE TRIGGER [ddlTestEvents1] ON DATABASE
GO

Let's try creating a table again
CREATE TABLE wasabi(id INT)
GO

Aha, same error message(almost) different trigger
You must disable Trigger "ddlTestEvents2" to drop, create or alter tables!
Msg 3609, Level 16, State 2, Line 1
The transaction ended in the trigger. The batch has been aborted.

so basically the event DDL_TABLE_EVENTS is the same as the DROP_TABLE, ALTER_TABLE, CREATE_TABLE events. To check that run the following code

SELECT name,te.type_desc,te.type
FROM sys.triggers t
JOIN sys.trigger_events te on t.object_id = te.object_id
WHERE t.parent_class=0
AND name IN('ddlTestEvents1','ddlTestEvents2')
ORDER BY name,te.type_desc

The ouput is the same for both triggers

name...........,type_desc,type
ddlTestEvents1,ALTER_TABLE,22
ddlTestEvents1, CREATE_TABLE,21
ddlTestEvents1, DROP_TABLE, 23
ddlTestEvents2, ALTER_TABLE, 22
ddlTestEvents2, CREATE_TABLE, 21
ddlTestEvents2, DROP_TABLE, 23

So when you use DDL_TABLE_EVENTS in the trigger, then 3 events will be in the sys.trigger_events catalog view. This is all fine but how do you find out these things? I can't find DDL_TABLE_EVENTS anywhere. Also I know that DDL_LOGIN_EVENTS is the same as CREATE LOGIN, ALTER LOGIN and DROP LOGIN combined but where is this documented?

Script out the ddlDatabaseTriggerLog trigger (this is the only trigger that is in the AdventureWorks database when you install SQL server), you will see a DDL_DATABASE_LEVEL_EVENTS event
when you run the following query (same as before, different trigger name) you will see that it returns 76 rows (basically all events)

SELECT name,te.type_desc,te.type
FROM sys.triggers t
JOIN sys.trigger_events te on t.object_id = te.object_id
WHERE t.parent_class=0
AND name ='ddlDatabaseTriggerLog'
ORDER BY name,te.type_desc

I am sure I will edit this (many times)
But first I will list all the events
You will see the events followed by a grouped event in red for example
CREATE_TABLE
ALTER_TABLE
DROP_TABLE
DDL_TABLE_EVENTS

I will have to go through all these events and use the sys.trigger_events to find this info out before I post this (unless someone points to in the right direction where to find this)

DDL Statements with Database Scope:
CREATE_APPLICATION_ROLE (Applies to CREATE APPLICATION ROLE statement and sp_addapprole. If a new schema is created, this event also triggers a CREATE_SCHEMA event.) ALTER_APPLICATION_ROLE (Applies to ALTER APPLICATION ROLE statement and sp_approlepassword.)
DROP_APPLICATION_ROLE (Applies to DROP APPLICATION ROLE statement and sp_dropapprole.)

CREATE_ASSEMBLY
ALTER_ASSEMBLY
DROP_ASSEMBLY
-- should be DDL_ASSEMBLY_EVENTS but who knows?

ALTER_AUTHORIZATION_DATABASE (Applies to ALTER AUTHORIZATION statement when ON DATABASE is specified, and sp_changedbowner.)

CREATE_CERTIFICATE
ALTER_CERTIFICATE
DROP_CERTIFICATE

CREATE_CONTRACT
DROP_CONTRACT

GRANT_DATABASE
DENY_DATABASE
REVOKE_DATABASE

CREATE_EVENT_NOTIFICATION
DROP_EVENT_NOTIFICATION

CREATE_FUNCTION
ALTER_FUNCTION
DROP_FUNCTION

CREATE_INDEX
ALTER_INDEX
DROP_INDEX

CREATE_MESSAGE_TYPE
ALTER_MESSAGE_TYPE
DROP_MESSAGE_TYPE

CREATE_PARTITION_FUNCTION
ALTER_PARTITION_FUNCTION
DROP_PARTITION_FUNCTION

CREATE_PARTITION_SCHEME
ALTER_PARTITION_SCHEME
DROP_PARTITION_SCHEME

CREATE_PROCEDURE
ALTER_PROCEDURE
DROP_PROCEDURE

CREATE_QUEUE
ALTER_QUEUE
DROP_QUEUE

CREATE_REMOTE_SERVICE_BINDING
ALTER_REMOTE_SERVICE_BINDING
DROP_REMOTE_SERVICE_BINDING

CREATE_ROLE (Applies to CREATE ROLE statement, sp_addrole, and sp_addgroup.)
ALTER_ROLE
DROP_ROLE (Applies to DROP ROLE statement, sp_droprole, and sp_dropgroup.)

CREATE_ROUTE
ALTER_ROUTE
DROP_ROUTE

CREATE_SCHEMA (Applies to CREATE SCHEMA statement, sp_addrole, sp_adduser, sp_addgroup, and sp_grantdbaccess.)
ALTER_SCHEMA (Applies to ALTER SCHEMA statement and sp_changeobjectowner.)
DROP_SCHEMA

CREATE_SERVICE
ALTER_SERVICE
DROP_SERVICE

CREATE_STATISTICS
DROP_STATISTICS
UPDATE_STATISTICS

CREATE_SYNONYM
DROP_SYNONYM

CREATE_TABLE
ALTER_TABLE
DROP_TABLE
DDL_TABLE_EVENTS

CREATE_TRIGGER
ALTER_TRIGGER
DROP_TRIGGER

CREATE_TYPE (Applies to CREATE TYPE statement and sp_addtype.)
DROP_TYPE (Applies to DROP TYPE statement and sp_droptype.)

CREATE_USER (Applies to CREATE USER statement, sp_adduser, and sp_grantdbaccess.)
ALTER_USER
DROP_USER (Applies to DROP USER statement, sp_dropuser, and sp_revokedbaccess.)

CREATE_VIEW
ALTER_VIEW
DROP_VIEW

CREATE_XML_SCHEMA_COLLECTION
ALTER_XML_SCHEMA_COLLECTION
DROP_XML_SCHEMA_COLLECTION

DDL Statements with Server Scope:
ALTER_AUTHORIZATION_SERVER

CREATE_DATABASE
ALTER_DATABASE
DROP_DATABASE

CREATE_ENDPOINT
ALTER_ENDPOINT
DROP_ENDPOINT

CREATE_LOGIN (Applies to CREATE LOGIN statement, sp_addlogin, sp_grantlogin, xp_grantlogin, and sp_denylogin when used on a nonexistent login that must be implicitly created.)
ALTER_LOGIN (Applies to ALTER LOGIN statement, sp_defaultdb, sp_defaultlanguage, sp_password, and sp_change_users_login when Auto_Fix is specified.)
DROP_LOGIN (Applies to DROP LOGIN statement, sp_droplogin, sp_revokelogin, and xp_revokelogin.)
DDL_LOGIN_EVENTS

GRANT_SERVER
DENY_SERVER
REVOKE_SERVER

And like I said I will edit this after I run my tests