Jeff's SQL Server Blog

Random Thoughts & Cartesian Products with Microsoft SQL Server
posts - 157, comments - 2686, 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





Here are those random thoughts I warned you about. Beware!
I'm back ... with some news!

I apologize for not posting any new content in quite some time, but now I am back and will soon start posting on a (hopefully!) regular basis once again. The reason for my hiatus was a pretty good one, though: I recently became a father with the birth of my son Benjamin on October 2, 2008!   He is doing great and already knows that he should always do his formatting at the client and never within the database.  He's a natural! As if that wasn't enough, I also found out recently I have been named as a 2009 SQL Server MVP!  Now...

posted @ Thursday, October 16, 2008 4:04 PM | Feedback (9) | Filed Under [ Miscellaneous ]

The MailBag --- Super-Sized Edition! String Parsing, Crosstabs, SQL Injection, and more.

OK, boys and girls, it's time for the mailbag!  There's lots of stuff to cover, so let's get to it! --- Greg E writes: Hello Jeff, I just found your blog and wanted to know if you could point me in the right direction or possibly toss me a solution. I am looking at a badly formed telelphone number column in a MS SQL Server db. Entries contain '(555) 555-1212' or '555.555.1212, etc. Do you know how I would go about stripping out unwanted characters from the telephone number? Thanks for the brain cycles. Greg -- A simple UDF should do the trick for you.  For example,...

posted @ Wednesday, July 16, 2008 4:32 PM | Feedback (0) | Filed Under [ Miscellaneous ]

The Joy of Blog Feedback

Introduction I have been writing my little blog here for some time now, and my favorite part of doing this is of course the feedback.  It's always great to hear from the readers, to have mistakes corrected, to debate various topics and techniques, and to learn a lot about SQL and the various topics I discuss here.  At this point, I have received over 1,700 comments over the years, and while all of them are truly appreciated, I have noticed that unfortunately many of the, uh, less helpful comments do seem to consistently fall neatly into various categories.  Let's take a...

posted @ Friday, June 06, 2008 3:43 PM | Feedback (11) | Filed Under [ Miscellaneous Humor ]

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 ]

Need an Answer? Actually, No ... You Need a Question

Welcome! The reason you were directed here is because you need assistance, and I am here to help.  I am not, however, here to provide you with any answers!  You see, it looks like the assistance you need is not finding an answer; it is rather that you need assistance finding a question. As you know, there are all kinds of questions.  Questions that test memory recall.  Questions that test logic skills.  Brain-teasers and mathematical questions and so on.  But there is one requirement that all good questions must have in common before they can be answered: A proper question MUST provide ALL...

posted @ Tuesday, May 13, 2008 11:04 AM | Feedback (22) | Filed Under [ Miscellaneous ]

You Know, There's a Much Easier Way...

Let's say you are struggling on a programming project.  Your code is growing exponentially and becoming more convoluted by the day, and it is clearly out of control.  You're getting run-time errors, compile-time errors, wrong output, no output, endless loops, your machine is overheating, and perhaps you are starting to feel like you might be a little over your head.  Maybe this is the first time you are using a particular programming tool or language, or it's the first time you've worked with a particular database, or maybe it's your first complicated project overall in any technology and you're getting...

posted @ Thursday, March 13, 2008 2:51 PM | Feedback (11) | Filed Under [ Miscellaneous ]

An INT primary key .... yet not an Identity?

Ah, this is not an anti-identity rant, don't worry! Though, in a round-a-bout sort of way, it is yet another argument against always blindly using them -- but not in the way you might expect. There is a simple rule I'd like to propose, let me know what you think: "If your client code, SQL code, or configuration files reference the primary key column of a table to determine any application logic, that primary key column should not be an identity." Now, I am not saying that primary key column can't be a meaningless integer.  I am just saying it should not be an...

posted @ Thursday, February 21, 2008 12:08 PM | Feedback (12) | Filed Under [ Miscellaneous Database Design ]

Why use Google when a better, more accurate, and more powerful "search engine" exists?

Raise your hand if you've ever done this: At a programming forum that you regularly visit, you see a post asking for help The post describes a situation that you may not intimately familiar with, or that you know has been covered elsewhere many times, and it is clear a quick Google search will find a good answer. So, you think of some good key words, perform a few quick searches, examine the results, and determine which of your findings is a good...

posted @ Thursday, January 17, 2008 2:56 PM | Feedback (28) | Filed Under [ Miscellaneous ]

The Mailbag: Referencing Assemblies in Reporting Services; some SQL help

As David Letterman would say, wake the kids, call the neighbors, it's time for The Mailbag!  Just some quickies today. Christopher writes: Greetings Jeff, First and foremost, great job with all of the blogs. I have a questions that I cannot seem to get a straight answer for. I am working with SQL Server Reporting Services (SSRS) and have the need to create VB functions to customize the reports generated. For example, a setter/getter to display information that would not be readily available from the query. SSRS allows this type of custom Visual Basic code to reside in the report itself, but since most of my code is across...

posted @ Thursday, October 18, 2007 12:19 PM | Feedback (0) | Filed Under [ Miscellaneous Report Writing GROUP BY Reporting Services ]

Is it a String Literal or an Alias?

Every now and then I see T-SQL code written like this: select somecolumn as 'columnname' from sometable Notice that 'columnname', despite its appearance, is not a string literal or a string expression, it is the alias that we are assigning to the column somecolumn.  Putting the name of an alias in single quotes in T-SQL is completely valid syntax, and it will work fine, but I feel that it is a really bad practice.   It makes the distinction between string literals and object names very blurry, and it can lead to confusion when examining and maintaining your code. For example, to me this is...

posted @ Thursday, August 30, 2007 11:36 AM | Feedback (4) | Filed Under [ Miscellaneous T-SQL ]

SELECT * FROM TABLE -- except for these columns

A occasional question seen in the forums, which was just recently asked today, is: "I know I can use SELECT * FROM table to get all of the columns from a table, but is there a way to write SELECT *  minus columnX FROM Table to get all of the columns except for certain ones?" Now, my goal isn't to debate whether not "SELECT *" is bad or good or should be used or not.  The fact of the matter is, people use it all the time because it is quicker and shorter than typing out all of the column names. People are lazy, right? ...

posted @ Thursday, July 26, 2007 3:36 PM | Feedback (25) | Filed Under [ Miscellaneous T-SQL ]

Becoming a Better Developer, plus a SQL Crime Drama!

Sorry for the delay, Denis; thinking of ways to improve our developer skills is a great idea, thank you for including me.  Here are my thoughts on the next 6 months.  More design, less code.  I'm going to try to focus a little more on user interfaces, graphics, layouts, and so on.  I tend to write boring, plain vanilla corporate-style applications that frankly aren't much fun. Sure, they work great (usually!) and the layout is clean and easy to use (hopefully!), but my goodness -- it is boring.  I plan on focusing a little more on the aesthetic design of...

posted @ Thursday, July 26, 2007 10:36 AM | Feedback (2) | Filed Under [ Miscellaneous ]

Distinguishing data from code

What is data, and what is code? How do we define the difference, and decide what goes where? It is great to say "keep data out of your code", but what if that data is integral to the application itself? Isn't it therefore code, and not data? read more...

posted @ Tuesday, July 24, 2007 9:02 AM | Feedback (18) | Filed Under [ Miscellaneous Database Design ]

But *WHY* Must That Column Be Contained in an Aggregate Function or the GROUP BY clause?

Column 'xyz' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Arggh!! There it is, yet again .. that annoying error message. Why is SQL so picky about this? What's the deal!? read more...

posted @ Friday, July 20, 2007 10:33 AM | Feedback (35) | Filed Under [ Miscellaneous GROUP BY ]

Data Types -- The Easiest Part of Database Design

I see it time and time again in forums -- "dates" that don't sort properly, "numbers" that don't add correctly, "boolean" data with 10 different values, and so on ... Since we are rarely provided any DDL to review, it often takes many posts going back and forth until we finally realize: "wait ... you aren't using a datetime data type to store these dates?!" read more...

posted @ Tuesday, July 03, 2007 10:13 AM | Feedback (9) | Filed Under [ Miscellaneous Database Design DateTime Data ]

Cross Join Updates!

A couple of quick blog updates for those that are interested: A Comment Milestone! Recently, I realized that I have received 1,000 comments since this little blog was started way back in September 2003.  Has it really been that long!?  Some of the comments are trackbacks, but overall, it's pretty amazing to think about.  Only about 30% of them are people calling me a moron, which is an excellent ratio and well above what I expected.  In all seriousness, though, I love getting feedback, and it is a lot of fun to debate techniques and ideas, so please be sure to leave...

posted @ Tuesday, June 26, 2007 11:43 AM | Feedback (0) | Filed Under [ Miscellaneous ]

What I learned about SQL from using Access

Microsoft Access / JET SQL doesn't support cursors; the only way to process rows one by one is using VBA and opening a Recordset. This means that you're stuck using set-based "queries". So ... is this a good thing or a bad thing? A very, very good thing -- in fact, it's what makes Access a great tool for learning SQL! read more...

posted @ Wednesday, June 13, 2007 1:12 PM | Feedback (13) | Filed Under [ Miscellaneous Techniques MS Access ]

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 ]

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 ]

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 ]

Alias Naming Conventions

Let's go to the mailbag!

In response to this post, Chris writes:


posted @ Monday, July 31, 2006 10:01 AM | Feedback (14) | Filed Under [ Miscellaneous Techniques ]

Rendering Excel and Word files with SQL Server

I've been playing around with a handy tool for creating Word and Excel files called OfficeWriter that's pretty impressive.


posted @ Tuesday, July 18, 2006 3:05 PM | Feedback (2) | Filed Under [ Miscellaneous .NET (C# / VB) Report Writing ]

Programming is a funny thing ....

In most industries, beginners work on simple, short projects, using only the most basic concepts that they have learned. The experts in those industries, meanwhile, work on things that are very large, complicated, difficult to manage, and require lots of time, energy and resources.


posted @ Thursday, March 30, 2006 1:58 PM | Feedback (27) | Filed Under [ Miscellaneous Techniques ]

A Quick Lesson on SQL Indexes

Just a quick post of some benchmarking code for our good friend Guayo at TheDailyWTF, in regards to this discussion.

posted @ Friday, November 12, 2004 4:42 PM | Feedback (14) | Filed Under [ Miscellaneous T-SQL Techniques ]

Using SQL to solve the "Sliding Tiles" puzzle

I just wanted to take a minute to highlight this thread in the sqlteam forums.

posted @ Wednesday, September 22, 2004 12:05 PM | Feedback (0) | Filed Under [ Miscellaneous T-SQL Techniques ]

PARSENAME() - a simple way to parse (some) strings in SQL

Handy reminder: The PARSENAME() function can be useful for parsing small strings. It returns parts 1-4 (working right to left) of a string, with each part delimited by periods.


posted @ Tuesday, September 30, 2003 5:09 PM | Feedback (2) | Filed Under [ Miscellaneous T-SQL Code Library - SQL ]

Hello All !

This also is my first ever blog.  Very nice!  I am definitely looking forward to the content on this page, we've got some great people here and the posts should be informative and I'm sure quite entertaining as well!

posted @ Friday, September 26, 2003 8:59 AM | Filed Under [ Miscellaneous ]

Powered by:
Powered By Subtext Powered By ASP.NET