Win One Of Ken Henderson's SQL Server Guru Books By Creating A PhotoShop Joe Celko Action Figure

That's right you can win one of the following three books

The Guru's Guide to Transact-SQL
The Guru's Guide to SQL Server Architecture and Internals
The Guru's Guide to SQL Server Stored Procedures, XML, and HTML

All you have to do is create a photoshopped depiction of Joe Celko as an action figure.

The best one gets a signed copy of one of his books (too bad I already have all three)

Ken Henderson's Blog

Friday Joke and Frappr Map Update

Since it's Friday today I will keep it light
Let's start with a joke that I read in Wired magazine yesterday

What do you call a blonde who dyes her hair brown?

Artificial intelligence

Next topic

Yesterday I created a Frappr map and asked for you (the reader) to add yourself to the map so that I can see where some of you are located
As of now the countries that are marked are

United States 6 people
Russia 2 people
Canada 1 person
Norway 1 person

So not quite close to my IQ yet

SqlServerCode Frappr Map

I created a Frappr Map and would like for you (yes you the reader) to add yourself to the map because it would be interesting to see where some of the readers of this blog are located
So far I have less members (2) than my IQ, I would like to have more members than my IQ (believe me not a hard task at all)

So this is your chance; beat my IQ

My member name is Denis The SQL Menace

Quest Toad for SQL Server 2.0 Released

Quest Software, Inc. announced the newest version of its award-winning Toad™ database management product family. Toad for SQL Server 2.0 includes new features such as one-of-a-kind integrated debugging, SQL optimization, and integrated comparison and synchronization capabilities for SQL Server 2005. These new features further improve the productivity of SQL Server developers and database administrators (DBAs).


New features in version 2.0 of Toad for SQL Server include:

• Database Back-Up Indicator – Demonstrates when it is necessary to back up a system, and integrates seamlessly with LiteSpeed for SQL Server


• SQL Optimization - Automates the process of identifying problematic SQL then re-writing and benchmarking it for optimal SQL Server performance.


• Compare and Sync – Provides advanced integrated comparison and synchronization of schema, data and server settings of two SQL Server environments.


• Debugging – Offers integrated debugging capabilities for SQL Server 2005.

“With Toad for SQL Server 2.0, Quest offers customers a single interface to develop, administer and optimize their SQL Server environments,” said Douglas Chrystall, chief architect, Quest Software. “In addition to this release, Quest is making its Comparison Suite of tools free of charge. This further demonstrates Quest’s commitment to the SQL Server market – not only by expanding the breadth and depth our SQL Server portfolio with the enhancements to Toad, but also making essential tools, such as the Comparison Suite, freely available to the market.”

Pricing and Availability
A freeware version of 2.0 of Quest Toad for SQL Server is available now at http://www.toadsoft.com/toadsqlserver/toad_sqlserver.htm

A full production version is also available now with North American pricing beginning at $470 per seat (USD). For more information and to download free evaluation copies please visit: http://www.quest.com/toad_for_sql_server/

DDL Trigger Events Revisited

Many many moons ago I wrote about DDL Trigger Events
I promised that I would find out all the events that you can combine for example DDL_VIEW_EVENTS instead of CREATE_VIEW
,ALTER_VIEW and DROP_VIEW
So I finally did it, I created a bunch of triggers and then checked with a query that joined sys.triggers and sys.trigger_events

I started by creating a trigger and I used DDL_VIEW_EVENTS

CREATE TRIGGER ddlTestEvents
ON DATABASE
FOR DDL_VIEW_EVENTS
AS
PRINT 'You must disable Trigger "ddlTestEvents" to drop, create or alter Views!'
ROLLBACK;
GO

After that I would check the sys.triggers and sys.trigger_events views to see what was inserted

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

In this case 3 rows were inserted

DDL_VIEW_EVENTS
----------------------------------
41 CREATE_VIEW
42 ALTER_VIEW
43 DROP_VIEW

Then I altered the trigger as follows

CREATE TRIGGER ddlTestEvents
ON DATABASE
FOR DDL_USER_EVENTS
AS
PRINT 'You must disable Trigger "ddlTestEvents" to drop, create or alter Users!'
ROLLBACK;
GO

I checked again with the same query

SELECT name,te.type,te.type_desc
FROM sys.triggers t
JOIN sys.trigger_events te on t.object_id = te.object_id
WHERE t.parent_class=0
AND name IN('ddlTestEvents')
ORDER BY te.type,te.type_desc
in this case 3 rows were also inserted


DDL_USER_EVENTS
----------------------------------
131 CREATE_USER
132 ALTER_USER
133 DROP_USER

I kept repeating this until I had all the events and below is the result
The number that you see before the event is the type from the sys.trigger_events view

DDL_XML_SCHEMA_COLLECTION_EVENTS
----------------------------------
177 CREATE_XML_SCHEMA_COLLECTION
178 ALTER_XML_SCHEMA_COLLECTION
179 DROP_XML_SCHEMA_COLLECTION


DDL_VIEW_EVENTS
----------------------------------
41 CREATE_VIEW
42 ALTER_VIEW
43 DROP_VIEW


DDL_TRIGGER_EVENTS
----------------------------------
71 CREATE_TRIGGER
72 ALTER_TRIGGER
73 DROP_TRIGGER


DDL_USER_EVENTS
----------------------------------
131 CREATE_USER
132 ALTER_USER
133 DROP_USER


DDL_TYPE_EVENTS
----------------------------------
91 CREATE_TYPE
93 DROP_TYPE


DDL_TABLE_EVENTS
----------------------------------
21 CREATE_TABLE
22 ALTER_TABLE
23 DROP_TABLE


DDL_SYNONYM_EVENTS
----------------------------------
34 CREATE_SYNONYM
36 DROP_SYNONYM


DDL_STATISTICS_EVENTS
-----------------------------------
27 CREATE_STATISTICS
28 UPDATE_STATISTICS
29 DROP_STATISTICS


DDL_SERVICE_EVENTS
------------------------------------
161 CREATE_SERVICE
162 ALTER_SERVICE
163 DROP_SERVICE


DDL_SCHEMA_EVENTS
------------------------------------
141 CREATE_SCHEMA
142 ALTER_SCHEMA
143 DROP_SCHEMA


DDL_ROUTE_EVENTS
------------------------------------
164 CREATE_ROUTE
165 ALTER_ROUTE
166 DROP_ROUTE


DDL_ROLE_EVENTS
----------------------------------
134 CREATE_ROLE
135 ALTER_ROLE
136 DROP_ROLE


DDL_REMOTE_SERVICE_BINDING_EVENTS
-----------------------------------
174 CREATE_REMOTE_SERVICE_BINDING
175 ALTER_REMOTE_SERVICE_BINDING
176 DROP_REMOTE_SERVICE_BINDING


DDL_QUEUE_EVENTS
---------------------------------
157 CREATE_QUEUE
158 ALTER_QUEUE
159 DROP_QUEUE


DDL_PROCEDURE_EVENTS
----------------------------------
51 CREATE_PROCEDURE
52 ALTER_PROCEDURE
53 DROP_PROCEDURE


DDL_PARTITION_SCHEME_EVENTS
------------------------------------
194 CREATE_PARTITION_SCHEME
195 ALTER_PARTITION_SCHEME
196 DROP_PARTITION_SCHEME


DDL_PARTITION_FUNCTION_EVENTS
------------------------------
191 CREATE_PARTITION_FUNCTION
192 ALTER_PARTITION_FUNCTION
193 DROP_PARTITION_FUNCTION


DDL_EVENT_NOTIFICATION_EVENTS
----------------------------------
74 CREATE_EVENT_NOTIFICATION
76 DROP_EVENT_NOTIFICATION


DDL_ASSEMBLY_EVENTS
-----------------------------------
101 CREATE_ASSEMBLY
102 ALTER_ASSEMBLY
103 DROP_ASSEMBLY


DDL_CONTRACT_EVENTS
-----------------------------------
154 CREATE_CONTRACT
156 DROP_CONTRACT


DDL_FUNCTION_EVENTS
---------------------------------
61 CREATE_FUNCTION
62 ALTER_FUNCTION
63 DROP_FUNCTION


DDL_INDEX_EVENTS
---------------------------------
24 CREATE_INDEX
25 ALTER_INDEX
26 DROP_INDEX
206 CREATE_XML_INDEX


DDL_MESSAGE_TYPE_EVENTS
------------------------------------
151 CREATE_MESSAGE_TYPE
152 ALTER_MESSAGE_TYPE
153 DROP_MESSAGE_TYPE

Architecting Microsoft.com – SQL Server Podcast And Video

Channel 9 has an interesting video and podcast about the microsoft.com architecture with SQL Server
From the site: "At the heart of most web sites once you dig down below the routers, below the web servers, below the ASP.NET code you find a critical piece. The heart of the matter if you will is the database and if that heart stops... well let’s just say your website will assume room temperature quickly. The question is; what do the guys at Microsoft.com do to insure that the heart of the system is thumpin’ and pumpin’ day in and day out 24x7 while still needing to do updates, patches, server maintenance etc.? Well never fear because we have the answer for you so listen up and learn something!"

The podcast is available in WMA and MP3 formats and runs for 27 minutes and 55 seconds
The video length is 26 minutes and 9 seconds

you can get the podcast and the video here (http://channel9.msdn.com/Showpost.aspx?postid=226519)

Query Multiple Databases

"Query Multiple Databases"
This search term has been used 13 times in the last week on this blog. So here is a little blog post about it
It's really not complicated to query multiple databases. You have to have permissions on both databases
The syntax looks like this:
SELECT *
FROM Database1.ObjectOwner.TableName
JOIN Database2.ObjectOwner.TableName ON.....

So here is a little script so that you can test it out

USE master
GO

--Create the first Database
CREATE DATABASE DBtest1
GO

--Create the second Database
CREATE DATABASE DBtest2
GO



USE DBtest1
GO

--Create Table1 on DBtest1
CREATE TABLE Table1(id INT, DescriptionValue VARCHAR(49))
INSERT Table1 VALUES (1,'DBtest1')
GO


USE DBtest2
GO

--Create Table2 on DBtest2
CREATE TABLE Table2(id INT, DescriptionValue VARCHAR(49))
INSERT Table2 VALUES (1,'DBtest2')
GO



USE master
GO

--Do the join between DBtest1 and DBtest2
SELECT *
FROM DBtest1.dbo.Table1 t1
JOIN DBtest2.dbo.Table2 t2 ON t1.id = t2.id
GO


--Another way
SELECT *
FROM DBtest1..Table1 t1
JOIN DBtest2..Table2 t2 ON t1.id = t2.id
GO



--Drop these databases
DROP DATABASE DBtest1,DBtest2

Analysis Services Stored Procedure Project Released On CodePlex

The Analysis Services Stored Procedure project is a set of sample stored procedures for Analysis Services 2005. These samples have been written in C# and sample MDX queries are included that demonstrate the use of the procedures against the Adventure Works DW sample database. It was developed by a group of community volunteers with 2 main aims in mind:

To provide a set of useful extensions to Analysis Services 2005.
To provide a variety of example source code for people looking to write their own stored procedures.

Visit the CodePlex site to download the source code

Use The Sieve of Eratosthenes To Find All PrimeNumbers Below 1 Million

In mathematics, the Sieve of Eratosthenes is a simple, ancient algorithm for finding all prime numbers up to a specified integer. It was created by Eratosthenes, an ancient Greek mathematician. Wheel factorization is often applied on the list of integers to be checked for primality, before Sieve of Eratosthenes is used, to increase the speed.


Algorithm
Write a list of numbers from 2 to the largest number you want to test for primality. Call this List A. (This is the list of squares on the left-hand-side of the picture.)
Write the number 2, the first prime number, in another list for primes found. Call this List B. (This is the list on the right-hand-side of the picture.)
Strike off 2 and all multiples of 2 from List A.
The first remaining number in the list is a prime number. Write this number into List B.
Strike off this number and all multiples of this number from List A. The crossing-off of multiples can be started at the square of the number, as lower multiples have already been crossed out in previous steps.
Repeat steps 4 through 6 until no more numbers are left in List A.

Read more on Wikipedia


So here is the SQL Version, it runs in 7 seconds on my machine when I run it a
second time, first run is 16 seconds

SET NOCOUNT ON


DECLARE @i INT

-- Create a 10-digit table
DECLARE @D TABLE (N INT)
INSERT INTO @D (N) 
SELECT 0 UNION ALL 
SELECT 1 UNION ALL 
SELECT 2 UNION ALL 
SELECT 3 UNION ALL 
SELECT 4

INSERT INTO @D (N) 
SELECT N+5 FROM @D

-- build a small sieve table between 2 and 1000
DECLARE @T TABLE (N INT)
INSERT INTO @T( N )
SELECT 1+A.N+10*(B.N+10*C.N)
FROM @D A, @D B, @D C

DELETE FROM @T WHERE N = 1

SET @I = 2
WHILE @I <= SQRT(1000)
BEGIN
    DELETE FROM @T WHERE N % @I = 0 AND N > @I
    SET @I = @I + 1
END

-- Create large table between 1001 and 1000000
SELECT A+10*(B+10*(C+10*(D+10*(E+ 10*F)))) AS N
INTO #P
FROM
(    SELECT A.N AS A, B.N AS B, C.N AS C, D.N AS D, E.N AS E, F.N AS F
    FROM @D A, @D B, @D C, @D D, @D E, @D F
    WHERE A.N in (1, 3, 7, 9)  -- Not divisible by 2 or 5
) blah
WHERE (A+B+C+D+E+F) % 3 <> 0 -- Or 3
    AND (A+3*B+2*C-D-3*E-2*F) % 7 <> 0 -- Or 7
    AND (B-A+D-C+F-E) % 11 <> 0 -- Or 11
    AND D|E|F <> 0 -- Don't include the first 1000 numbers, 
--we already have these in the small sieve table
UNION ALL SELECT 1000000

-- sieve the big table with smaller one
SELECT @I = 2 
WHILE @I IS NOT NULL
BEGIN
    DELETE FROM #P WHERE N% @I = 0
    SELECT @I = MIN(N) FROM @T WHERE N > @I
END

-- add primes up to 1000
INSERT INTO #P SELECT N FROM @T

-- Here are the results
--78498 rows
SELECT  * FROM #P ORDER BY 1

drop table #P
go

Try Quest LiteSpeed And Win An iPod Nano (Hopefully)

Quest has a nice little give away. They are giving away 40 2GB Apple Nano iPods to people who are willing to try LiteSpeed for SQL Server.
I am already using LiteSpeed on the production machines but the cool thing is that Quest has a free version that you can use with the developer editions of SQL Server at no cost at all
So check it out here

SQL Server Book Sales Up 98% Compared To Last Year

Tim O’Reilly has posted his latest State of the Computer Book Market article. Here are 2 excerpts: “Looking at the Database rollup, we again see the strength of SQL Server, the decline of Oracle book sales, and that while MySQL is still a much larger category than Postgres, Postgres is showing some curious strength. This is one of the things that treemap visualizations are good for. Small, bright green categories stand out, and you can start paying closer attention. (Ruby also showed bright green while it was still a tiny category before its remarkable surge over the past year.) We also see the continuing popularity of personal databases like Access and Filemaker.
“


“A few high-level observations:
• C# book sales continues to gain on Java, with a 49% unit sales increase compared to Java's 10% decrease.
• Ruby continues its momentum, and is the fastest-growing programming language in terms of book sales.
• Microsoft's new release of SQL Server has continued to drive significant book sales, with that market up 86%. ASP.Net is also on a roll, with book sales up 61%.
“
Read the whole article here

Store The Output Of A Stored Procedure In A Table Without Creating A Table

I saw this technique in the Inside Microsoft SQL Server 2005: T-SQL Querying T-SQL and today I saw it also here The OPENROWSET Trick: Accessing Stored Procedure Output In A SELECT Statement @ Ward Pond's SQL Server blog

I decided to expand this a little
Sometimes I need to quickly find out who is blocking what (or what is blocking who for that matter) so I run sp_who2 then look at the BlkBy column and run a DBCC INPUTBUFFER or fn_get_sql to get the sql statement

When you have a lot of connections it's a pain in the neck to look for the BlkBy where it's not . since the result is ordered by SPID
You can always do
CREATE TABLE
INSERT TABLE
EXEC Proc

But who wants to create tables all the time (not me, at least not for this stuff)
SPID is twice in the resultset of sp_who2 (who know why?) so that complicates things a little
So let's start with sp_who


SELECT * INTO #TempSpWho
FROM OPENROWSET ('SQLOLEDB','Server=(local);TRUSTED_CONNECTION=YES;','set fmtonly off exec master.dbo.sp_who')

--return only the connections to master
SELECT * FROM #TempSpWho
WHERE dbname ='master'



--Let's try sp_who2
SELECT * INTO #TempSpWho2
FROM OPENROWSET ('SQLOLEDB','Server=(local);TRUSTED_CONNECTION=YES;','set fmtonly off exec master.dbo.sp_who2')

--Oops
Server: Msg 2705, Level 16, State 3, Line 1
Column names in each table must be unique. Column name 'SPID' in table '#TempSpWho2' is specified more than once.

--No problem list the columns
SELECT SPID, Status, Login, HostName, BlkBy,DBName, Command, CPUTime, DiskIO, LastBatch, ProgramName
INTO #TempSpWho2
FROM OPENROWSET ('SQLOLEDB','Server=(local);TRUSTED_CONNECTION=YES;','set fmtonly off exec master.dbo.sp_who2')

--Get the connections to master only
SELECT * FROM #TempSpWho2
WHERE dbname ='master'

--Get the blocking SPID's
SELECT * FROM #TempSpWho2
WHERE BlkBy NOT LIKE '% .'

--Get user connections only (everything with a SPID below 51 is reserved for sql server processes)
SELECT * FROM #TempSpWho2
WHERE SPID >= 50

A couple of notes
The OPENROWSET call opens a separate connection to SQL Server, so there is some overhead associated with this approach
This technique is not supported inside a declared transaction
Blocking can occure with a poorly architected stored procedure

Top SQL Server Google Searches For July 2006

These are the top SQL Searches on this site for the month of July. I have left out searches that have nothing to do with SQL Server or programming. As you can see it has been a slow(er) month. And Google reports only searches that have been searched for more than once. For example if you have these 4 searches
insert triggers
insert trigger
insert triggers
insert trigger!

Then only insert triggers will show up since the other two were used only one time


Here are the results...

increase maximum row size in mssql
crystal report division by zero code
insert triggers
select sum where
select from
delete a null record


Let's talk about the last search (delete a null record ) a little bit
The code below will show you how exactly you delete a row with a NULL value. You can not use =NULL but you have to use IS NULL

CREATE TABLE #TestDeleteNull (id INT identity, SomeDate DATETIME)
INSERT #TestDeleteNull VALUES(GETDATE())
INSERT #TestDeleteNull VALUES(GETDATE())
INSERT #TestDeleteNull VALUES(NULL)


--Check what's in the table
SELECT * FROM #TestDeleteNull

--This won't work
DELETE #TestDeleteNull
WHERE SomeDate = NULL
--(0 row(s) affected)

--This is the way
DELETE #TestDeleteNull
WHERE SomeDate IS NULL
--(1 row(s) affected)

--Check again
SELECT * FROM #TestDeleteNull

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

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

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

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

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

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