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




April 2007 Blog Posts

Thinking Set-Based .... or not?

So, I hear you're a "set-based SQL master" ! As Yoda once said, you've "unlearned what you have learned". You've trained yourself to attack your database code not from a procedural, step-by-step angle, but rather from the set-based "do it all at once" approach. However, don't completely forget the most important skill that you learned in the procedural world!


posted @ Monday, April 30, 2007 1:03 PM | Feedback (21) | Filed Under [ T-SQL Techniques Report Writing Joins/Relations ]

Regular Expression Replace in SQL 2005 (via the CLR)

I had to do some data clean up the other day, and really needed some regular expression replacements to do the job. Since .NET has a great RegularExpressions namespace, and since SQL 2005 allows you to integrate .NET CLR functions in your T-SQL code, I thought I'd go ahead and experiment with creating a RegExReplace() function.


posted @ Friday, April 27, 2007 4:20 PM | Feedback (13) | Filed Under [ .NET (C# / VB) SQL Server 2005 Code Library - C# / VB.NET ]

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 ]

Sorting Columns with the C# Pivot Function

Time for another exciting edition of the mailbag! This time: making an improvement to the C# Pivot function.

posted @ Friday, April 20, 2007 12:05 PM | Feedback (5) | Filed Under [ CrossTabs / Pivoting Data .NET (C# / VB) Code Library - C# / VB.NET ]

Better Alternatives to a FULL OUTER JOIN

As many of you know, I strongly recommend that you avoid using RIGHT OUTER JOINS, since they make your SQL code less readable and are easily rewritten as LEFT OUTER JOINs. In addition, I have yet to find a situation where a FULL OUTER JOIN makes sense or is necessary -- I have found that in just about every case other techniques work better.


posted @ Thursday, April 19, 2007 11:56 AM | Feedback (69) | Filed Under [ T-SQL Techniques Efficiency Report Writing Joins/Relations GROUP BY ]

How to format a Date or DateTime in SQL Server

Everything you ever wanted to know about how to use SQL Server's advanced features to format Dates into any format that you need! Here, for the first time, all in one place, is the secret that no one wants you know about how easy it is to format data in T-SQL!


posted @ Friday, April 13, 2007 4:45 PM | Feedback (56) | Filed Under [ T-SQL Techniques Efficiency Database Design DateTime Data ]

How to be an Effective Report Writer

A non-technical guide to writing reports, with a focus on managing requirements, results, and expectations.


posted @ Friday, April 13, 2007 11:03 AM | Feedback (11) | Filed Under [ Techniques Report Writing ]

SQL Check Constraints: Like George Costanza, sometimes you need to do the Opposite!

It's been a while, so let's open up the old mailbag. Today's question is about writing a character-based check constraint. The answer: Simple ... do the *opposite* !

read more..

posted @ Thursday, April 05, 2007 2:26 PM | Feedback (16) | Filed Under [ T-SQL Database Design ]

Why do only the "Experts" use online help?

Studies have shown that the "F1" key is the least commonly pressed key on today's keyboards! Ok, well maybe not actual studies, but from my own experience, I am convinced that on many keyboards here around the world the F1 key still has that shiny "new key" look and smell because it's never been used.

read more

posted @ Thursday, April 05, 2007 9:10 AM | Feedback (11) | Filed Under [ Miscellaneous ]

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 ]

Conditional Joins in SQL Server

Sometimes, when writing SELECTs, we come across situations in which we we need to write a join containing a condition of some sort. You might think to do this with either a CASE expression or with some OR boolean logic in your join expression. There's a much better way to approach the problem.


posted @ Tuesday, April 03, 2007 12:15 PM | Feedback (39) | Filed Under [ T-SQL Report Writing Joins/Relations ]

Powered by:
Powered By Subtext Powered By ASP.NET