June 2008 Blog Posts
Introduction
There is a very simple rule when it comes to storing (and returning) data, which I see violated all the time, making life so much more complicated for everyone involved. In case you haven't noticed, that's a common theme I discuss here on this blog -- different ways programmers make life more difficult for themselves, instead of simply following good practices and doing things the easy way. This is yet another example of that situation.
The "Golden Rule of Data Manipulation" is a simple, but important rule that you should always follow when designing a database, writing database code, or really...
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...
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...