SQL Server Q&A

Symptoms
A query that contains a large number of predicates that are AND-ed together may cause a stack overflow exception. Several thousand conditions are typically required for this problem to occur, and you may reach a limit on the maximum batch size of your query tool before encountering this situation. Many query tools limit batches to 128 KB, whereas SQL Server 7.0 now accepts a batch size as large as (65536 * network packet size).
Resolution
During optimization, memory for selectivity calculation was being allocated from the stack. This allocation depends on the number of nodes being AND-ed together and can result in a stack overflow.

Symptoms
When you run the Index Tuning Wizard, this error message may occur:

Server: Msg 3628, Level 16, State 1, Line 2
A floating point exception occurred in the user process. Current transaction is canceled.
Resolution
When a double was out of range from an underflow or overflow, the Index Tuning Wizard incorrectly converted the number into the maximum or minimum float value.

Symptoms
When converting a floating point value to an integer value on Intel PentiumPro and Pentium II processors, a negative value much less than the smallestnegative integer does not cause a “Run-Time Error ‘6′: Overflow,” as wouldbe expected. This problem is actually caused by an erratum in the PentiumPro and Pentium II processors. The following code illustrates this problem:

Debug.Print CInt(-2.59615E+33)’Where -2.59615E+33 = -2,596,150,000,000,000,000,000,000,000,000,000 Normally, this code will generate “Run-Time Error ‘6′: Overflow.” However,on Pentium Pro and Pentium II processors, this code does not generate anoverflow.
For conversion to type Integer, the failure to report an overflow occursonly when the value is in the range -1.84 x 10^19 to -2.60 x 10^33;that is, between:
-18,400,000,000,000,000,000
-and-
-2,600,000,000,000,000,000,000,000,000,000,000 Within the range above, fewer than one out of every 65,000 values isaffected.
For conversion to type Long, the failure to report an overflow occurs onlywhen the value is in the range -1.84 x 10^19 to -3.96 x 10^28;that is, between:
-18,400,000,000,000,000,000
-and-
-39,600,000,000,000,000,000,000,000,000. Within the range above, fewer than one out of every 4,000,000,000 values isaffected.
For this problem to be relevant to the Visual Basic developer, theapplication must meet the following conditions: Large negative numbers in the ranges given above could possibly beused by the application.The application may attempt to convert those numbers to an Integer ora Long.The application uses an On Error statement to catch the run-time errorthat would be caused by an overflow during the conversion.
Resolution
Intel Corporation has identified an erratum in the Pentium Pro andPentium II processors relating to the conversion of floating point valuesto signed integer values when using the Floating Point Integer Store (FIST)instruction available with these processors. The FIST instruction convertsfloating point numbers to 16-, 32-, or 64-bit signed integers. Because therange of a floating point number is larger than any of these formats, somefloating point numbers cannot be converted to integers. When attempting toconvert a floating point number that is too large to an integer, theprocessor should signal an “Invalid Operation” exception. Visual Basic usesthis signal to report “Run-Time Error ‘6′: Overflow.” The erratum in thePentium Pro and Pentium II processors causes them to not signal the”Invalid Operation” exception for certain numbers in the ranges givenabove.