Filtered Indexes Part 1: Nullable Columns

SQL Server 2008 introduced the concept of filtered indexes, whereby the index only indexes a portion of the data based on a WHERE clause in the filter definition. 
I’ve toyed with some potential uses of this, such as keeping archived data in its original table (an entry for another day).  However, recently a more immediate use came up for it.  Solving the age old problem of placing a unique constraint on a nullable field. 
An index treats a NULL like any other value and so a unique index on a nullable field only allows a single record with a NULL value.  In prior versions of SQL Server you would need to create triggers to enforce a unique constraint.  However, in SQL 2008, by filtering the index to exclude NULLs, you can create a unique constraint on  nullable.
Imagine the following table:

CREATE TABLE tblMyTable(
    MyTableId int IDENTITY(1,1) NOT NULL
    ,UniqueCode nvarchar(10) NULL
)

If we want to create a unique constraint on UniqueCode but retain the ability to have nulls you’d create an index as follows:

CREATE UNIQUE NONCLUSTERED INDEX IX_tblMyTable_UniqueCode
    ON tblMyTable (
        UniqueCode ASC
    )
    WHERE UniqueCode IS NOT NULL

By using the WHERE clause in the CREATE INDEX script you are removing NULLs from the index and thus ensuring that only non-null values are unique. 

Getting Feedback from Stored Procedures

I'm one of those people who puts as much business logic in the database. Stored procedures, views and functions are my "middle tier". That can sometimes lead to long running procedures with little feedback from the UI. I've often wished that you could return interim status information back from SQL.


Well, it turns out you can. The MSDN blog post, "Getting feedback / progress from batches and stored procedures", details how using RAISERROR.

BTW, the article also notes why there's a missing "E" in RAISERROR.

Putting SQL to Sleep

Have you ever wanted to test how your application would handle a long running stored procedure, but didn't want to create a multi-gigabyte database to do so?  Add a WAITFOR statement. 

WAITFOR is similar to .NET's Thread.Sleep() in which you can stop a thread from processing for a specified period of time.  You can either pass WAITFOR a delay time for the batch to pause for or a specific time for the batch to continue afterwards as follows:

WAITFOR DELAY '00:00:45' --Delays the batch for 45 seconds.

WAITFOR TIME '12:30'  --Restarts the batch at the next line at 12:30.

Note that there is no date specified in the WAITFOR TIME statement.  You can only delay it within a 24 hour period using the TIME clause.

One thing to watch out for are transactions.  If you put a WAITFOR within a transaction, any locked resources will remain so for the duration of the delay, potentially causing timeouts and deadlocks.

WAITFOR is also used for waiting for Service Broker messages, but that's a whole other blog entry.

I'm sure there are other uses for WAITFOR but for now, happy napping!

SQL Connection Kill Switch

Aaaarrrrg……

OK, so a few SSMS functions, such as Detach Database, have a checkbox letting you kill all current connections before performing the requested operation.  But some, say Restore, don’t (could someone give me a good reason why?), and end up failing if someone else (or yourself in another query window) has an open connection.  Your solution is to open the Activity Monitor, sort by database and kill each of the connections and hope that no new ones are opened in the mean time.  And what if you want to do this all in a script anyway?

To solve the above problems you can run the following script:

USE master

DECLARE @SQLStatement VARCHAR(MAX)
SET @SQLStatement = ''

SELECT
        @SQLStatement = @SQLStatement
            + 'KILL '
            + convert(VARCHAR(10), spid)
            + CHAR(13) + CHAR(10)
    FROM master.dbo.sysprocesses
    WHERE DB_NAME(dbid) = 'MyDatabase'
        AND dbid <> 0
        AND spid <> @@SPID

EXEC(@SQLStatement)

GO

So what exactly is this doing?  The SELECT statement is building a collection of KILL n statements where n is a process ID of a process connected to the specified database (in this case “MyDatabase”).  The CHAR(13) + CHAR(10) are really only there for aesthetics and debugging purposes.  Note that the WHERE clause explicitly excludes the connection of the current process.

Once this collection of KILL statements is built it executes them.  By using @SQLStatement = @SQLStatement + {next KILL statement}, it’s continually appending the next KILL statement to itself and the resulting value in the @SQLStatement variable is the entire list of KILL statements.

Giving credit where credit is due, I would like to note that I copied and tweaked the above statement from the Aaquagua blog.  In that blog, it is noted that the following statements will have the same effect:

ALTER DATABASE MyDatabase
    SET SINGLE_USER WITH ROLLBACK IMMEDIATE

ALTER DATABASE MyDatabase
    SET MULTI_USER WITH ROLLBACK IMMEDIATE

What we’re doing here is setting the database to single user mode, which immediately kills any current connections, and then setting back to multi-user mode.  Although this will in fact have the same effect, there’s something hackish about it that makes me a tad uncomfortable.

But that’s just my neuroses speaking.  You can pick whichever one works for you.

UPDATE:

OK.  So after writing this, I was thinking about the method of setting the database to single user mode first.  My issue with using it to kill connections is that your using a statement that does one thing, set a database to single user mode, to perform something else, kill all the connections.  Yes it works, but if someone were to come along and review your script, they may wonder what you’re doing.

Anyway, upon further pondering, I decided that setting the database to single user mode before performing an action, such as a restore or major update, is probably a good idea anyway by preventing any other connections from interfering with the action.  The idea would be to wrap your action inside the two set statements.

My Shiny New Blog

Welcome to my new DataGeek blog.  New you say?  What's with all these old posts?  Did you create some sort of time machine?

Don't we all wish.  I've retired (at least temporarily) my prior blog, DynamicsGeek, to focus (or not) on my real passion, data.  Databases, database development, data warehouses, data cubes, data marts, data analysis...pineapple shrimp, lemon shrimp, coconut shrimp, pepper shrimp, shrimp soup, shrimp stew...You get the picture.  Since I seemed to write a number of non-dynamics GP specific articles in DynamicsGeek anyway, I thought I'd move them over (and make myself look a little more productive)

Just like I veered off the subject of Dynamics GP, I'm sure I'll veer off the subject of data as well but hopefully it will be relevant to someone!

Strip Time from SQL Date

Often, particularly when saving dates to Dynamics GP, we need to strip the time out of a SQL date value. I found a neat little trick for doing that recently in an article on SQLServerCentral.com by Seth Phelabaum.

SELECT DATEADD(dd, DATEDIFF(dd,0,GETDATE()), 0).

This works by getting the number of days since date 0, which truncates the time, and then adding it back on to date 0.

In his article he has a few other DATEADD/DATEDIFF tricks. It’s well worth a read.

King Me

It appears that I had a bit of a hiatus from blogging.  This was primarily due to a whirlwind of a successful job search, followed by a brief transition.   The search itself was quite enlightening as the last time I formally interviewed for a job was 1997 and the last time I actually got a job from the resume/interview process was 1987.

Anyway, during one of my interviews I was asked what I would do as a manager if three of my developers came up with equally good but different solutions to a problem but couldn’t agree on which one to implement.  My answer was something to the affect that, although I prefer to be a consensus builder, management by committee is no management at all, the buck would stop with me and I would have to pick one.

My interviewer had another interesting idea.  If remember it correctly, he called it his “King Theory”.  What he would do is pick one of the developers and make him or her “The King”.  It would be his or her job to pick one of the solutions and be responsible for it’s success.  They would be able to pick any or any combination of the solutions but the buck would stop with them.

Now the interesting question is what would be the best solution for the developer to choose and what would they?  The natural instinct would probably be to pick their own but might it not be better to pick someone else’s or a combination to invest the other team members in the success of the implementation?

I thought it was a pretty interesting solution to the problem.  Has anyone else run across this?  What do you think of it?