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




May 2007 Blog Posts

More on Runs and Streaks in SQL

That's right boys and girls, it's what you've been waiting for all weekend: Another edition of the mailbag!

posted @ Wednesday, May 30, 2007 8:43 AM | Feedback (11) | Filed Under [ T-SQL Techniques Efficiency ]

Real Programmers don't need to write test applications!

You are a very important, talented, enterprise-level programmer! You write and maintain millions of lines of code, compiling your applications takes several hours, and your databases contain hundreds of tables with millions of rows. You clearly do not have time to write silly test applications!


posted @ Wednesday, May 23, 2007 12:27 PM | Feedback (17) | Filed Under [ Miscellaneous Techniques Humor ]

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 ]

Criteria on Outer Joined Tables

When using an OUTER JOIN, you should put criteria on the outer table in the join condition, not in the WHERE clause. However, I often see a "workaround" to avoid this simple and solid rule, which might seem to work but actually doesn't. Since it is hard to explain why over and over in forum posts, I thought it might be helpful to address that here once and for all with an example.


posted @ Monday, May 14, 2007 10:12 AM | Feedback (16) | Filed Under [ T-SQL Joins/Relations ]

Hamming Distance Algorithm in SQL

Here's a simple User-Defined Function implementation of the Hamming Distance Algorithm for SQL Server, useful for tracking down transposition errors in your data.


posted @ Wednesday, May 09, 2007 10:39 AM | Feedback (6) | Filed Under [ T-SQL Code Library - SQL ]

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 ]

In SQL, it's a Case Expression, *not* a Case Statement

A Google search for the phrase sql "case statement" returns 127,000 results. Meanwhile, if we do a search for the phrase sql "case expression" we get back only 43,900 results. What does this mean? Most people don't understand what SQL's CASE feature really is, how to use it, or how it works!


posted @ Thursday, May 03, 2007 9:15 AM | Feedback (18) | Filed Under [ Miscellaneous T-SQL ]

SQL Server 2005: Using EXCEPT and INTERSECT to compare tables

Previously, I had written that UNION ALL (combined with a GROUP BY) is a really quick and easy way to compare two tables. Well, now in SQL 2005, we have another option: using EXCEPT and INTERSECT. And these are even easier!


posted @ Wednesday, May 02, 2007 2:47 PM | Feedback (26) | Filed Under [ T-SQL SQL Server 2005 ]

Powered by:
Powered By Subtext Powered By ASP.NET