Poor men see sharp – more cursor optimization

After making my post on cursor optimization I received some comments that triggered me to do some further investigation. Adam Machanic wrote in my blog’s comments that using SQLCLR to loop over a SqlDataReader would be much faster than any T-SQL based cursor. And Erland Sommarskog wrote in a usenet thread that he has some colleagues who think that a “poor man’s cursor” is always better than a real cursor. So I decided to give these options a try and see what comes out in a real test. I simply reused the test cases I had already used for testing…
Read More

Curious cursor optimization options

The best way to optimize performance of a cursor is, of course, to rip it out and replace it with set-based logic. But there is still a small category of problems where a cursor will outperform a set-based solution. The introduction of ranking functions in SQL Server 2005 has taken a large chunk out of that category – but some remain. For those problems, it makes sense to investigate the performance effects of the various cursor options. I am currently preparing a series of blog posts on a neat set-based solution I found for a problem that screams “cursor” from…
Read More

So-called “exact” numerics are not at all exact!

Attempting to dispel myths tends to make me feel like Don Quixote, riding against hordes of windmills that won’t budge. In this case, even some of my fellow MVPs and Microsoft’s own Books Online are among the windmills… Books Online says that there are two categories of numeric data types: “approximate” (float and real), and “exact” (all others, but for this discussion mainly decimal and numeric). It also says that “floating point data is approximate; therefore, not all values in the data type range can be represented exactly”, thereby suggesting that other numeric data types are capable of representing all…
Read More

How NOT to pass a lot of parameters

Did you know that SQL Server allows stored procedures to have up to 2100 parameters? And more important: do you care? Well, some people do care, and Joe Celko seems to be one of them. If you are a regular reader of SQL Server newsgroups, you probably know Joe Celko from his always unfriendly and often incorrect replies. Here is a typical example, one that I have seen several times recently, in a paraphrased form: Question: I want to send a list of values to my stored procedure, but WHERE ColumnName IN (@ValueList) does not work – how to solve…
Read More

What if null if null is null null null is null?

In this fourth and final part in my series about NULL, I’ll discuss some well-known and some less well-known functions and keywords that are specifically created to deal with NULL values. And I will, of course, explain why null if null is null null null is null. In case you have not yet read them, you can click these links to read the first, second, and third part.   IS NULL is not = NULL   I have already explained why tests for equality with NULL will always return Unknown instead of True or False. This holds true in all…
Read More

Dr. Unknown, or how I learned to stop worrying and love the NULL

Two months ago, I posted the first two parts of a series about NULL. After that, I went quiet. Much to do and little time was one excuse. But to be honest, I also lost interest. However, I felt I owe my readers to conclude the series, so I have now forced myself to write and publish the last parts before moving on to more interesting subjects J.   Before reading on, I suggest that you first read (or reread) the first and second part of this series, so that we’re all on the same level again.   Finished reading?…
Read More

Upcoming speaking events

I know I’ve been neglecting my blog lately, but I at least have a valid excuse this time. Four weeks ago, I received two emails on a single day, both inviting me to speak at two different Dutch events that will be held in the same week.   I replied “yes” to both mails, and since have been preparing my sessions. For those readers who would like to catch me speaking, I’ll give the details below.   For the “Software Developer Conference 2007”, to be held in Arnhem, the Netherlands on the 17th and 18th of September, I’ll deliver two…
Read More

A Better Developer in 6 months? Deal!

It’s all the fault of Denis Gobo. He tagged me, and now I feel obliged to publish my plans for the last half of 2007 as well. I am not quite as ambitious as Denis, though.   My first commitment Like Denis, I will read more technical books. I have a huge stack of unread books on my desk, that I will try to finish. Here are the books, in planned reading order: Inside Microsoft SQL Server 2005: T-SQL Programming, by Itzik Ben-Gan. A Developer’s Guide to SQL Server 2005, by Bob Beachemin and Dan Sullivan. MCITP Self-Paced Training Kit…
Read More

The logic of three-valued logic

In my previous post, I explained what NULL does and does not mean, how and why the rule of NULL propagation forces any expression involving NULL to result in NULL. I also mentioned that comparisons involving NULL do not result in NULL but, rather, in a new “truth value” called “Unknown”. But there’s a lot more to be written about Unknown and it’s effect on logical expressions, and that’s exactly what this blog post (and the next one as well) will be about.   Most software developers will be intimately familiar with what is popularly known as Boolean logic but…
Read More

NULL – The database’s black hole

I once read a scientific article on black holes that started of on the observation that, since a hole is the absence of anything and black is invisible in space, a black hole is in fact an invisible nothing – so what the heck are we talking about? Well, almost the same can be said about NULL in databases.   Let’s first take a look at what NULL is supposed to be. Here is the definition of NULL from the SQL-2003 standard:               null value: A special value that is used to indicate the absence of any data value.…
Read More

By continuing to use the site, you agree to the use of cookies. more information

The cookie settings on this website are set to "allow cookies" to give you the best browsing experience possible. If you continue to use this website without changing your cookie settings or you click "Accept" below then you are consenting to this.