SQL Server Management Studio’s Ugly ANSI_PADDING Bug

It’s always a bit hard to define what exactly constitutes a bug.

In this case, I’ll argue that if/when Microsoft says that you shouldn’t USE a particular feature—and then its tooling USES that feature (by default—and in the exact wrong configuration that they warn against), then that behavior constitutes a bug.

Related: Protect Yourself in SQL Server Management Studio with Color Coding Instances

To that end, just note for the purposes of this post, that turning ANSI_PADDING OFF is bad or something you really want to avoid. Microsoft’s own documentation for SQL Server (as of 2005), has also warned SQL Server users and developers to stay away from trying to set ANSI_PADDING OFF—as per this warning in Books Online:

In a future version of SQL Server ANSI_PADDING will always be ON and any applications that explicitly set the option to OFF will produce an error. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

Where this becomes a bug, however, is that SQL Server Management Studio (SSMS) will—by default—script "SET ANSI_PADDING OFF;" statements out into the scripted definitions of your objects when you generate scripts.

It's a known issue—but one that Microsoft doesn’t seem to think is a big deal.

Recently, this bug was responsible for one of my clients losing over 8 years' worth of financial data. (Side Note: Nice to note that all of that testing and verification of the Disaster Recovery Plan worked out in the end—as we were able to fully recover.)

The Problem with SSMS' Generation of Scripts with ANSI_PADDING OFF

SSMS will script SET ANSI_PADDING OFF statements out whenever you script out a wide variety of objects (especially tables). So, for example, if I go create a very simple table, right click on it and script it—then I'll end up with something that basically looks like the following:

SET ANSI_PADDING ON;  -- table definition here SET ANSI_PADDING OFF;

The problem with the outputs above, however, are that once SET ANSI_PADDING OFF has been set, it'll stay like that for the rest of your session. If you're just scripting the creation of a new table that might not be an issue—but if you're scripting the creation of a complex table (along with its other objects—like indexes, triggers, constraints, and the likes) and then using that as the basis of additional scripting or logic, then you've managed to set ANSI_PADDING OFF all the way through the rest of your session (i.e., either until you terminate your connection/scripting-window or explicitly reset ANSI_PADDING ON).

For my client, this meant that they had accidentally set ANSI_PADDING OFF, then fired off a couple of statements to create computed columns on the table they were working with, and then executed an UPDATE against the table. However, the UPDATE failed because a filtered index was in place on the table in question—and SQL Server noted that ANSI_PADDING was off, and therefore 'barfed' on the UPDATE statement, cascading a series of crashes and problems that resulted in temporarily screwing up all of the data in the table.

Detecting and Dealing with the Problem

To determine if your ANSI_PADDING settings are on/off, you can simply run the following:

SELECT SESSIONPROPERTY('ANSI_PADDING')

Which will report on what the current setting is for your session. That'll, at least, let you know if ANSI_PADDING is on or off (and, it should—frankly—always be ON).

Ideally, though, SQL Server wouldn't actually script those ANSI_PADDING commands into the definition of various objects. And, happily, you CAN control that—by simply selecting the Tools > Options menu option and then making the changes specified in the following screenshot:

The problem, however, is that (by default) this setting is set to True—meaning that SSMS will always throw these statements out (by default) into object definitions being scripted. Given how Microsoft is going to remove this feature in the future—and given how UGLY it is that a stray "SET ANSI_PADDING OFF" statement can be, I'm arguing that SSMS' behavior of leaving this setting defaulted to True is a bug.

Related: Policing the Use of SQL Server Management Studio