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.
Read more →
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 ProcessProductsasdeclare @Products cursor, @ProductID intset @Products = cursor for select ProductID from Products order by ProductIDopen @Productsfetch next from @Products into @ProductIDwhile (@@FETCH_STATUS=0)beginexec DoSomething @ProductID fetch next from @Products into @ProductIDenddeallocate @Products Ah ha!
Read more →
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".
Read more →
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.
Read more →
Here's a very common situation that is very easy to optimize and simplify, submitted via the mailbag:
Nate writes: Hey, I have a read a bunch of your stuff on your blog and you seem tobe right on the money.
Read more →
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.
Read more →
Introduction A question I see very often in the SQLTeam forums is how to return data in a summarized form by concatenating multiple values into single CSV string columns. For example, taking data like this:
Read more →
Often, we need to create a flexible stored procedure that returns data that is optionally filtered by some parameters. If you wish to apply a filter, you set the parameter to the necessary value, if not, you leave it null.
Read more →
Introduction Previously, I wrote about grouping transactions by month. Another common area of difficulty or confusion for SQL beginners is how to efficiently retrieve data just for a single month.
Read more →
The Importance of Data Types Imagine that SQL Server only provided two data types: the MONEY data type to store numeric values, and VARCHAR to store text. If you are designing a database in this scenario and you need to store or return integer values, which data type – MONEY or VARCHAR – would you use?
Read more →
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 →
That's right boys and girls, it's what you've been waiting for all weekend: Another edition of the mailbag! Damian writes:
Hi I have a tricky SQL question that I have been trawling the net and workmates to find an answer.
Read more →
(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 →
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.
Read more →
Question: How do you create a DateTime in a specific format in SQL Server? Answer: You don't. You can't. The only way to translate a DateTime into a specific format is to convert it to a VARCHAR or other "string" data type.
Read more →
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 →
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 .
Read more →
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 →
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 →
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.
Read more →