Jeff's SQL Server Blog

Random Thoughts & Cartesian Products with Microsoft SQL Server
posts - 155, comments - 2679, trackbacks - 64

My Links



Welcome to my weblog. My name is Jeff Smith, I am software developer in Boston, MA and I was recently named a 2009 SQL Server MVP. Check in frequently for tips, tricks, commentary and ideas on SQL Server and .NET programming.



Post Categories





Random links from the "Internet tubes" that you may enjoy -- free of charge!
SQL Server 2005 Foreign Key Constraints: SET NULL and SET DEFAULT

Most people know about cascading updates and deletes, but did you know there are two other foreign key constraint options you can use to maintain referential integrity? Read all about them in my latest article over at These features, introduced with SQL Server 2005, haven't got a lot of publicity, but they can be very useful.  I just used the SET NULL option recently for the first time (inspiring me to put together an article on it) and it works great.

posted @ Wednesday, August 13, 2008 9:56 AM | Feedback (3) | Filed Under [ Database Design Links Joins/Relations ]

Log Buffer #98

Hello and welcome to the 98th edition of Log Buffer. My name is Jeff Smith and I will hosting this week's exciting episode. If, for some reason, you are not completely satisfied with this edition, simply write in and complain to Dave over at The Pythian Group and you will receive Log Buffer #99 absolutely free! Now that is a guarantee you can feel good about. OK, let's get to work. I have only limited exposure to both PostgreSQL and MySQL, but I have often wondered why MySQL is so popular while...

posted @ Friday, May 23, 2008 9:09 AM | Feedback (10) | Filed Under [ Miscellaneous Links ]

Implementing Table Inheritance with SQL Server

I have a new article up at SQLTeam: Implementing Table Inheritance with Sql Server It discusses the situation where you have multiple entities that are distinct, yet they have many attributes or relations in common.   There is an easy way to simplify your database design and your code if you use the concept of a "base table" for that common data, which is very similar to the concept of Inheritance in Object-Oriented programming. I recently had to do this for a client that tracks Contributions to their foundation; there is a base set of data that all Contributions have, such as who donated,...

posted @ Thursday, February 21, 2008 11:05 AM | Feedback (6) | Filed Under [ SQL Server 2005 Database Design Links ]

Working with Time Spans and Durations in SQL Server

If you read this blog on a regular basis, you probably know that a frequently covered topic is dates and times and the use of the DateTime data type.   Well, along those same lines, my latest article has just been published over at SQL Team: Working with Time Spans and Durations in SQL Server. From the Article: What is the best way to return the "duration" of an event in SQL, given the start and end datetime values? How can we add up these durations to return grand totals? What data types should be used to return this data to our clients?...

posted @ Monday, October 15, 2007 9:28 AM | Feedback (0) | Filed Under [ T-SQL Links DateTime Data ]

SQL Server 2008 - Enhancements in Date and Time Data Types (link)

Speaking of dates and times, there's a nice post from Ravi. S. Maniam over at the msdn blogs regarding the new and exciting Enhancements in Date and Time Data Types for SQL Server 2008. It will be very interesting to see how date and time usage changes once SQL Server 2008 becomes the most commonly used edition.  Of course, since even SQL 2005 still doesn't seem to be as widely adopted as I would like, who knows when that will be!

posted @ Thursday, August 30, 2007 10:05 PM | Feedback (1) | Filed Under [ Links SQL Server 2008 DateTime Data ]

More on GROUP BY; Examining SUM(Distinct)

I've written a two part article on using SQL GROUP BY clauses over at read more...

posted @ Tuesday, July 31, 2007 12:44 PM | Feedback (6) | Filed Under [ T-SQL Links GROUP BY ]

Using LINQ with SQL (link)

There's a great series of posts over at Scott Guthrie's Blog covering LINQ, a new feature in the upcoming version of Visual Studio ("Orcas"). Check it out; I have not had a chance to play around with it yet, but it certainly looks very interesting. The articles are very well done and explain the concept very clearly with lots of examples.

posted @ Friday, July 13, 2007 4:27 PM | Feedback (1) | Filed Under [ ASP.NET Links ]

SQL 2005 PIVOT Operator (link)

There's two interesting posts over at the MSDN blogs from Craig Freedman about the new PIVOT operator in SQL 2005. First, he gives a nice overview of the operator and how to use it, and then he follows it up with a performance analysis as well. A good read, check it out.

posted @ Thursday, July 12, 2007 9:24 AM | Feedback (2) | Filed Under [ CrossTabs / Pivoting Data SQL Server 2005 Links ]

SQL Server 2005: CROSS APPLY

SQL Server User-Defined Functions (UDFs) can return either a single value or virtual tables. However, sometimes we might like for a User-Defined Function to simply return more than 1 piece of information, but an entire table is more than what we need.

posted @ Tuesday, June 12, 2007 8:52 AM | Feedback (3) | Filed Under [ Techniques SQL Server 2005 Links ]

Slightly more dynamic ORDER BY in SQL Server 2005

There's a very interesting way of handling complicated, multi-column dynamic sorts over at I am not sure about the efficiency of this approach, but in general dynamic sorting tends not to be very efficient anyway (unless you use dynamically-created SQL statements).  Overall, it is a very clever use of RANK() and definitely worth a look. Thanks for the great idea, John!

posted @ Tuesday, June 05, 2007 10:24 AM | Feedback (2) | Filed Under [ Techniques Links ]

Using SQLBulkCopy to copy any .NET Object to a SQL Server Table

In my SQLBulkCopy article, I mentioned that you can quickly copy data from anything that implements IDataReader to a SQL Server table using SQLBulkCopy (new in .NET 2.0). In this SQLTeam forum post, Jesse Hersch (jezemine) tells us that SQLBulkCopy only actually uses 3 methods of the interface. This means that it is very easy to quickly create your own custom class which implements this interface to bulk copy pretty much any type of object collection or array to a SQL Server table.

posted @ Tuesday, May 22, 2007 2:40 PM | Feedback (3) | Filed Under [ .NET (C# / VB) Links Imports/Exports ]

SQL Server 2005: Specifying Partitions for Aggregate Functions

Did you know that a new feature in SQL Server 2005 allows you to specify an OVER partition for aggregate functions in your SELECT statements?


posted @ Monday, May 21, 2007 2:52 PM | Feedback (7) | Filed Under [ T-SQL SQL Server 2005 Links GROUP BY ]

SQL Challenge Response: Finding Consecutive Available Blocks in a Schedule

Here's my response to a SQL Challenge, regarding how to find consecutive free time slots in a schedule. This can sometimes be tricky to solve in SQL, but using either of the two techniques shown here, it is actually pretty easy. (Updated to show 2 possible solutions)


posted @ Tuesday, May 15, 2007 9:52 AM | Feedback (6) | Filed Under [ T-SQL Techniques Efficiency Links DateTime Data ]

Using SQLBulkCopy to quickly transfer data from .NET to SQL Server

My latest article has just been posted over at Use SqlBulkCopy to Quickly Load Data from your Client to SQL Server .NET Framework 2.0 introduces a very handy new class in the System.Data.SqlClient namespace called SqlBulkCopy that makes it very easy and efficient to copy large amounts of data from your .NET applications to a SQL Server database. You can even use this class to write a short .NET application that can serve as a "middleman" to move data between database servers. If you ever need to move large amounts of data to SQL Server from a .NET application, SQLBulkCopy...

posted @ Monday, May 07, 2007 9:29 AM | Feedback (3) | Filed Under [ .NET (C# / VB) Links Imports/Exports ]

Custom Auto-Generated Sequences in SQL Server

Be sure to visit to check out my latest article, Custom Auto-Generated Sequences in SQL Server. It addresses a common question we see in the SQL Team forums.

posted @ Tuesday, April 24, 2007 8:36 AM | Feedback (3) | Filed Under [ T-SQL Database Design Links ]

Have you seen Joe's "Blog" Yet?

I just discovered Joe Celko's weblog; it is really entertaining, at least the 5-6 posts I've read so far! It seems the "blog" is actually just a collection of questions and Celko's rather, umm, "honest" responses from various newsgroups and forums.

posted @ Saturday, April 21, 2007 10:13 AM | Feedback (6) | Filed Under [ Links ]

Unique Passwords?

Today's article at is one that the SQL Server community might find enjoyable.  I may try to implement something similar in my next project .... or, maybe not! Are there another other great designs like this that you've come across?  Maybe even some that you designed yourself?  Come on, you can admit it, let us know! I won't tell anyone.  After all, no one ever reads the comments ... see also: Discussing VB and/or MS Access in a Programming Forum Top 10 Things I Hate About SQL Server SQLTeam Dating Advice ! ...

posted @ Tuesday, April 03, 2007 7:38 PM | Feedback (1) | Filed Under [ Humor Database Design Links ]

A true MVP

Congratulations to our good friend Alex Papadimoulis over at the weblogs on his Microsoft MVP award. Well-earned!


posted @ Wednesday, November 30, 2005 5:08 PM | Feedback (0) | Filed Under [ Humor Links ]

You've got to have an IDENTITY!

Here's kind of a funny/scary thread over at the DailyWTF:


posted @ Friday, November 04, 2005 11:18 PM | Feedback (3) | Filed Under [ Techniques Humor Database Design Links ]

Powered by:
Powered By Subtext Powered By ASP.NET