Jeff's SQL Server Blog

Random Thoughts & Cartesian Products with Microsoft SQL Server
posts - 157, comments - 2686, trackbacks - 64

My Links

Advertisement

News

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.


Subscribe





Archives

Post Categories

Programming

Sports

SQL

Efficiency

Tips and tricks on getting things done more efficiently in SQL or other programming languages.
Database Column Names != Report Headings

Always remember that the column names returned in a result set do not have to be the same as what you eventually output at your presentation layer.   For example, suppose you have stored procedure that accepts a @CurrentYear parameter and returns a sales variance between the current year and the previous year for each customer.  I often see programmers struggling with writing dynamic SQL to produce output like this: CustomerID   2008 Total    2007 Total   Variance ----------   ----------    ----------   -------- ABC          $100          $50          $50 DEF          $200          $250         -$50 That is, the names of the columns vary based on the data; that is not a good way...

posted @ Wednesday, August 06, 2008 11:43 AM | Feedback (3) | Filed Under [ Techniques Efficiency Report Writing ]

The Truth about "Cursor Busting" in SQL

Let's say you are called in to troubleshoot a stored procedure that is performing poorly. You dive in to investigate and this is what you find: create procedure ProcessProducts as     declare @Products cursor, @ProductID int     set @Products = cursor for select ProductID from Products order by ProductID     open @Products     fetch next from @Products into @ProductID     while (@@FETCH_STATUS=0)         begin         exec DoSomething @ProductID         fetch next from @Products into @ProductID         end     deallocate @Products Ah ha! A cursor!  It seems we have identified the bottleneck: Clearly, the performance problems are because the code is not doing things in a set-based manner, but rather...

posted @ Thursday, June 05, 2008 10:56 AM | Feedback (14) | Filed Under [ T-SQL Efficiency ]

Does SQL Server Short-Circuit?

I got an email recently regarding one of my early blog posts from the olden days: Steve Kass wrote about your post:"there is no guarantee that WHERE <filter 1> OR <filter 2> will be optimized so that the filters are evaluated in the order typed". I am not certain that optimization changes the priority of the expressions, but I do...

posted @ Friday, February 22, 2008 11:04 AM | Feedback (8) | Filed Under [ T-SQL Efficiency ]

On RIGHT OUTER JOINS ...

Because I feel pretty strongly about this and the entire focus of my blog is writing clear, clean and efficient SQL, I thought I'd repeat my response from a SQLTeam forum question here. smithje asks this, regarding OUTER JOINS: Left/Right, does it matter. Is one better than the other? A few years ago consultant on a project for our company advised me to always write my queries to use Left joins. He had worked on the project to convert the original database application to MS SQL when Microsoft took it over. He claimed the design of the query engine handled Left joins...

posted @ Wednesday, February 13, 2008 11:22 AM | Feedback (10) | Filed Under [ Efficiency Joins/Relations ]

Rewriting correlated sub-queries with CASE expressions

Here's a very common situation that is very easy to optimize and simplify, submitted via the mailbag.

posted @ Wednesday, January 09, 2008 8:42 AM | Feedback (3) | Filed Under [ CrossTabs / Pivoting Data Efficiency Joins/Relations ]

Simplify Your SQL with Variables and Derived Tables (or Common Table Expressions)

As with any programming language, it is important in SQL to keep your code short, clear and concise. Here are two quick tips that I find are very helpful in obtaining this goal.

posted @ Thursday, December 20, 2007 12:20 PM | Feedback (7) | Filed Under [ T-SQL Techniques Efficiency GROUP BY ]

Creating CSV strings in SQL: Should Concatenation and Formatting Be Done at the Database Layer?

A question I see very often in the SQLTeam forums is how to return data in a summarized form by concatenating a list of values into a single CSV column. This can be done fairly easily in T-SQL, but as the formatting and concatenation requirements becomes more elaborate, be sure to ask yourself: Am I forcing presentation code into the database layer? read more...

posted @ Tuesday, October 09, 2007 3:02 PM | Feedback (19) | Filed Under [ T-SQL .NET (C# / VB) Efficiency Report Writing ASP.NET Joins/Relations ]

Optimizing Conditional WHERE Clauses: Avoiding ORs and CASE Expressions

Here's some quick tips on optimizing your SELECT statements when implementing conditions in your WHERE clause based on optional parameter values. read more...

posted @ Tuesday, September 18, 2007 4:25 PM | Feedback (27) | Filed Under [ Efficiency ]

Filter by month (plus other time periods)

Another common area of difficulty or confusion for SQL beginners is how to efficiently retrieve data just for a single month. What is the best way to declare parameters that will be used to indicate which month you are looking for, and how can we efficiently and easily make use of those parameters to get back the data we need? read more...

posted @ Friday, September 14, 2007 12:21 PM | Feedback (5) | Filed Under [ Techniques Efficiency Report Writing DateTime Data ]

Working with Date and/or Time values in SQL Server: Don't Format, Don't Convert -- just use DATETIME

The next time you are working with dates and times, please remember: how would you handle things if you were working with integers and decimals? The same logic and reasoning applies. Be smart, let SQL do the work for you and use the right data types for the job, even if things don't always "look" right. read more...

posted @ Wednesday, August 29, 2007 10:04 AM | Feedback (33) | Filed Under [ T-SQL Efficiency Database Design DateTime Data ]

Using GROUP BY to avoid self-joins

Sometimes, it appears that a necessary solution to common SQL problems is to join a table to itself. While self-joins do indeed have their place, and can be very powerful and useful, often times there is a much easier and more efficient way to get the results you need when querying a single table.

read more...

posted @ Tuesday, June 12, 2007 11:35 AM | Feedback (5) | Filed Under [ T-SQL Techniques Efficiency Joins/Relations GROUP BY ]

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 ]

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)

read more...

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

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.

read more...

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!

read more...

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

Dear DBA ....

Dear DBA -- Thanks so much for helping us developers out with the latest changes you've made to the stored procedures in our system. While it may have been nice if we got together first to discuss these changes, I do appreciate that you worked very hard to make things much easier for us.

read more...

posted @ Wednesday, March 28, 2007 12:36 PM | Feedback (13) | Filed Under [ Techniques Efficiency Humor Database Design ]

Some Simple SQL Rules to Live By

Most of these are really basic. Some are my take on established ideas and standards, and may be controversial. Agree or disagree? All feedback is welcome! (well ... mostly the "I agree, you're a genius" feedback is welcome ... but I'll accept all of it, I suppose)

read more...

posted @ Tuesday, March 14, 2006 12:42 PM | Feedback (33) | Filed Under [ T-SQL Techniques Efficiency SQL Server 2005 Security ]

Data belongs in your tables -- not in your code

Here’s my advice of the day: Try your best to store all of the data relating to your database in the tables. Sounds pretty obvious, I know, but you might be surprised just how much data in your application is not in your tables, where it belongs, but rather nestled away in your code.

read more..

posted @ Friday, February 10, 2006 10:41 AM | Feedback (37) | Filed Under [ T-SQL Techniques Efficiency Database Design ]

.NET CrossTabs versus SQL Server CrossTabs

As promised in my last post, here is some performance testing to help you determine the performance benefits (if any) of performing your crosstabs at the presentation or code layer, as opposed to forcing SQL Server to do this.

read more...

posted @ Thursday, May 12, 2005 10:01 AM | Feedback (7) | Filed Under [ T-SQL CrossTabs / Pivoting Data .NET (C# / VB) Efficiency Report Writing Code Library - C# / VB.NET ]

SQL WHERE clauses: Avoid CASE, use Boolean logic

As some of you may know, I recommend to avoid using CASE expressions in the WHERE clause of a query to express conditional logic. I prefer to have everything translated to simple ANDs, ORs and NOTs to keep things a) portable, b) easier to read and c) efficient.

posted @ Friday, November 14, 2003 12:12 PM | Feedback (38) | Filed Under [ T-SQL Techniques Efficiency ]

Powered by:
Powered By Subtext Powered By ASP.NET