How long does it take to fix a bug

In the development cycle, it is needless to less we all in some part of our carrier, have injected  bugs in different scale. However, when we were told it is a bug, I am sure most of you try to fix it or prioritize it to fix it.

But can you imagine, if there is a bug in SQL Server for more than 5 years, ranging three SQL Server Products (SQL Server 2005, SQL Server 2008 and R2).

This bug is in CTE.

Now CTE was introduced primarily to support recursive queries.

Let’s say I have a table and data like this.

So I have a hierarchy as following.

Let’s assume that, you need to retrieve, only two levels starting from Richard, here is the query you should use.

so number of levels are handled by , MAXRECURSION = 2 option. In this of course, you will get the result but also and ERROR!!!,

yes, error says,

Msg 530, Level 16, State 1, Line 1
The statement terminated. The maximum recursion 2 has been exhausted before statement completion.

You can say that since it giving the correct result, what is the big deal about this. But the problem here is, since it is a Level 16 error, if you are using this inside of your code, your code will throw an exception.

You have a workaround which I have given in my sample code at the end of this post.

Now this error first appeared in SQL Server 2005 and Microsoft has make sure that they don’t fix this so you can see the same bug in SQL Server 2008 and in R2. I got to know that this was initially reported in 2002 when SQL Server 2005 CTP was released. So it is nine years now.

To confirm the stuff, I checked this in Denali CTP1. Guess what, still this issue in the backlog.

Download sample code from here

Altering a Column Which has a Default Constraint

If you are altering a column with default column, you need to drop it. but the problem will be if you created a default constraint without explicitly specifying  constraint name. Here is a faq from me.

xp_msver

xp_msver is a extended stored procedure, to get some valuable information in SQL Server.

EXEC master.dbo.xp_msver

Will return,

Most important parameters that I can think of is, ProductVersion (not a big deal since you can get this from ServerProperty function), ProcessorCount, Physical Memory.

Next question is, why you would need this?

Let’s say, you need to set the Maximum Degree of the Parallelism (MAXDOP) as a function of number of Processor count. For example, some DBAs want to have MAXDOP to, ProcessorCount – 2.

 

-

Similarly you can set the memory allocation as well.

Download relevant script from here