Australian Lefty on Politics, Governance, Science and Info Management

MS-SQLServer still B.A.D. : @@nestlevel

Posted by Dave Bath on 2008-08-10

Microsoft SQL-Server 2008 is still Broken As Designed with a dainbramaged hardcoded limit (32) on procedures calling procedures that hasn’t been fixed since they bought the Sybase codebase.  (Typically, MS fools people that they developed something they didn’t or couldn’t).

Basically, if one procedure calls another, which calls another, and your function stack is deeper than 32…. KABOOM!  Refer to snippets below, or to @@NESTLEVEL and Nesting Stored Procedures if you don’t believe me.

Of course, REAL databases like Oracle and PostgreSQL have no such arbitrary limits until you’ve run out of virtual memory on your disk – and on my PC thats 4GB RAM and 16GB swap.

(Qualification: the freebie version of Oracle, Express Edition has a 1GB limit on addressable memory, but that doesn’t affect how deep you can go. If you like here are the download pages for Linux or M$-WinDoze versions.)

Years ago, with Oracle v7 on Linux ELF (see notes below), I’d tried to break the stack – a recursive function passing (on average) 2K of data down each call (and not by reference) – 4000 levels deep.  It was slow – but it didn’t break.

I’m not just being a purist that has no impact in the real world: I know of cases where integration of a couple of MS-SQL applications caused "random" crashes that were almost impossible to duplicate because they were dependent on variables dotted all over the statespace (remember trigger chaining also incremented (@@nestlevel).  It took ages for them to figure out it wasn’t their application code, but MS-SQL.  Two years (and probably a man-century) of work down the drain – so they ported across to Oracle – problem solved.

So… use MS-SQL Server if you are doing anything sophisticated and you accept "random" errors and are prepared to manage a large application codebase (remembering that bug probability increases exponentially with the size of the codebase) and long times-to-delivery.

When will Microsoft fix this?  When their customers are smart enough to demand that Microsoft SQL server gets dragged into the early 1990s, so maybe by 2036.  Until then, tree structured queries (like Oracle’s CONNECT BY PRIOR clause, which has a limit of 256 levels – therefore probably enough to represent the organizational structure and employee reporting chains of the planet) will be pretty useless.

You’ve been warned.

Notes/See Also:

  • "To err is human, to forgive divine." (proverb) and "To iterate is human, to recurse divine" (software engineering aphorism) can provide insight into the quality of Microsoft products.
  • MS VB thinks 9.72 – 3.16 – 6.56 does not equal 0 (2008-03-13) is another example of MS software B.A.D., especially when MS-Excel formulae don’t show the same problem, while MS-Excel VB macros do, implying they have two different maths libraries!!
  • If you are asking how I was running Oracle 7 on Linux ELF (on a 16Mb box) when Oracle’s first Linux release was v8, you’re just like the Oracle guys from Nordic-land who phoned me up because they had to demonstrate their first-cut of a Linux ELF version with v8 within a fortnight of starting the port, and had found my name grovelling around the net.  "What code did you have to change?" they asked.  "Didn’t have the code" I answered.  "WTF???"  "Yeah, I pulled apart the SCO COFF libraries into their component .o’s, converted them to ELF with GNU binutils, then reassembled the .a’s, strip(1)ped them, relinked, then strip(1)ped the final executables."  "WTF???  Oh well, that’ll only take a few hours, and that sort of smoke and mirrors as a fallback will be able to fool the execs if we get stuck doing the port for real." said the Oracle guys.  "Awww, shucks, that’s nothing compared to redefining exit in the C libraries distributed by Ingres so near-real-time client programs would stay up even when the backend crashed."  "WTF???  You can do that?"
  • From MS-SQL Server 2008 Nesting Stored Procedures (my bolding):

    Stored procedures are nested when one stored procedure calls another or executes managed code by referencing a CLR routine, type, or aggregate.  You can nest stored procedures and managed code references up to 32 levels.  The nesting level increases by one when the called stored procedure or managed code reference begins execution and decreases by one when the called stored procedure or managed code reference completes execution.  Attempting to exceed the maximum of 32 levels of nesting causes the whole calling chain to fail.  The current nesting level for the stored procedures in execution is stored in the @@NESTLEVEL function.

  • In the real world, if you test for @@nestlevel for safety, the hardcoded limit is 30 or 31, not 32, as described in MS-SQL Servier 2008 @@NESTLEVEL:

    Each time a stored procedure calls another stored procedure or executes managed code by referencing a common language runtime (CLR) routine, type, or aggregate, the nesting level is incremented.  When the maximum of 32 is exceeded, the transaction is terminated
    When @@NESTLEVEL is executed within a Transact-SQL string, the value returned is 1 + the current nesting level.  When @@NESTLEVEL is executed dynamically by using sp_executesql the value returned is 2 + the current nesting level.


6 Responses to “MS-SQLServer still B.A.D. : @@nestlevel”

  1. Jacques Chester said

    I’ve been working on a new project recently and decided to use Postgresql. It’s nice to have a featuresome database — CHECK constraints, for example.

    One thing that’s very nifty about Postgres is that it treats user-created types as first class. So for example, you can define an email type which can be used for columns. Or a hash type. Or so on.

    This means I can put the validation right next to the data. The praetorian database, if you like.

  2. Dave Bath said

    Yep – for me – there are only two databases worth playing with when data integrity is important:
    (1) PostgreSQL
    (2) Oracle

    And having user-written functions in almost any language you like (particularly plperlu which can use any perl module on your system), as well as trusting the author with mutating tables and mutating code in the middle of transactions is a bonus.

    However, Oracle’s point of marking code as “dirty” and needing compilation (raises an error once and then recompiles automagically) and preventing triggers playing with other rows of mutating tables is occasionally awkward, but philosophically pure.

    But then, pg comes from the unix world with the attitude of “trust the programmer, if they want to explicitly shoot themselves in the head, let them”. I love the old aphorism “No-one truly appreciates the power and responsibility of programming in C until they’ve accidentally written over their kernel”.

    But then, I’ve even had to binary patch libraries to redefine exit(3) to use a static to determine whether _exit() is called, or whether I do a longjmp() to a previous setjmp() before in near-real-time land. Nanny-state software stops you doing that when you need it.

  3. Jacques Chester said

    The perl integration thing is wild when you take CPAN into account. I dropped email types into google and there’s two generations of thinking.

    The first lot create a one-off regex inside the function. The second lot just load Email::Valid from CPAN and let it do the pondering.

    Similarly I have pondering whether to create a specific type for SHA1 hashes of various bits and bobs.

    Creating my own types means that, later on, I can replace the perl stuff with C if I have to. Or not; I don’t expect it to be a bottleneck. Eg, Digest::SHA-1 module clocks at least 60Mb/s which is faaaaaar more than I need.

    UWA teach databases from MySQL which strikes me as misleading students about the scope and purpose of RDBMSes.

    Incidentally, I downloaded the postgres source to get a sense of how it’s put together. It’s probably the best-commented C I’ve seen, not that I’ve read a lot of C.

  4. Dave Bath said

    For PostgreSQL pedigree back to 1973…

    Yep, your DARPA money at work!!!
    And yes, I’ve used QUEL, which was in many respects superior to SQL from a purist’s viewpoint. It would actually be nice if PostgreSQL had a quel interface.

    On C comments….
    Does anyone point to the early TCP/IP .h files that included “cannot reliably ftp to mars” next to the timeout definition?

  5. Dave Bath said

    Oh, from

    I am the very model of a database relational,
    My updates are atomic and ACIDic and transactional,
    My planner aims to optimise your queries scatological,
    My indexes will cope with SQL that is pathological

    My data types encompass from mundane to geographical,
    My data safety record shows concern that’s quite fanatical,
    My cost per TPC will beat both DB2 and Oracle,
    And yet the plebs persist in writing apps for bloody MySQL!

  6. […] insertrealname on Thu 04-12-2008 Spoiling for a fight Saved by evilgothgirl24 on Thu 04-12-2008 MS-SQLServer still BAD : @@nestlevel Saved by mrichme on Thu 04-12-2008 No babies! Saved by energeticmonkey on Thu 04-12-2008 What […]

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: