Jeff Smith Blog

SQL Server 2005 Foreign Key Constraints: SET NULL and SET DEFAULT

Wed Aug 13, 2008 by Jeff Smith in database-design, links, joins-relations

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 SQLTeam. Read more →

Log Buffer #98

Fri May 23, 2008 by Jeff Smith in miscellaneous, links

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! Read more →

Implementing Table Inheritance with SQL Server

Thu Feb 21, 2008 by Jeff Smith in sql-server-2005, database-design, links

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. Read more →

Working with Time Spans and Durations in SQL Server

Mon Oct 15, 2007 by Jeff Smith in t-sql, links, datetime-data

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. Read more →

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

Thu Aug 30, 2007 by Jeff Smith in links, sql-server-2008, datetime-data

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. Read more →

More on GROUP BY; Examining SUM(Distinct)

Tue Jul 31, 2007 by Jeff Smith in t-sql, links, group-by

I've written a two part article on using SQL GROUP BY clauses over at SQLTeam.com. It's always a common topic of discussion and confusion amongst beginner and intermediate SQL programmers alike, so I thought I'd write a fairly long and hopefully comprehensive piece that takes a common summary report request and works towards the solution step-by-step. Read more →

Using LINQ with SQL (link)

Fri Jul 13, 2007 by Jeff Smith in asp-net, links

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. Read more →

SQL 2005 PIVOT Operator (link)

Thu Jul 12, 2007 by Jeff Smith in crosstabs-pivoting-data, sql-server-2005, links

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. Read more →

SQL Server 2005: CROSS APPLY

Tue Jun 12, 2007 by Jeff Smith in techniques, sql-server-2005, links

If you are using SQL 2005 and find User Defined Functions helpful, be sure to read my new article over at SQLTeam: Returning Complex Data from User-Defined Functions with CROSS APPLY Read more →

Slightly more dynamic ORDER BY in SQL Server 2005

Tue Jun 5, 2007 by Jeff Smith in techniques, links

There's a very interesting way of handling complicated, multi-column dynamic sorts over at john-sheenan.com. 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). Read more →

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

Tue May 22, 2007 by Jeff Smith in net-c-vb, links, imports-exports

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. Read more →

SQL Server 2005: Specifying Partitions for Aggregate Functions

Mon May 21, 2007 by Jeff Smith in t-sql, sql-server-2005, links, group-by

Did you know that a new feature in SQL Server 2005 allows you to specify an OVER partition for aggregate functions in your SELECT statement? For example: Read more →

SQL Challenge Response: Finding Consecutive Available Blocks in a Schedule

Tue May 15, 2007 by Jeff Smith in t-sql, techniques, efficiency, links, datetime-data

(Note: Updated 5/15/2007 @ 12:25 PM EST to show another possible solution with different results.) Here's my response to the SQL Challenge given here. The challenge involves having a schedule table with days and times, and displaying all available consecutive free time slots to schedule an event. Read more →

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

Mon May 7, 2007 by Jeff Smith in net-c-vb, links, imports-exports

My latest article has just been posted over at SQLTeam.com: 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. Read more →

Custom Auto-Generated Sequences in SQL Server

Tue Apr 24, 2007 by Jeff Smith in t-sql, database-design, links

Be sure to visit SQLTeam.com 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. From the intro: Read more →

Have you seen Joe's "Blog" Yet?

Sat Apr 21, 2007 by Jeff Smith in links

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. Read more →