One of the core ways of making dynamic sql safe is the use of the QUOTENAME() function. QUOTENAME wraps either square brackets (default), double quotes, or single quotes around a provided string. This is crucial for dynamic SQL because if, for example, you need to concatenate a column name, the only way to make it 100% safe is to wrap it in square brackets so it's treated as an identifier. Conceptually the same as doing this:

select [Not A Valid Column] = @@version
Naively you might think then, that if you had a dynamic sql string like this, you would be safe, since you're wrapping brackets around the identifier
declare @ColumnName nvarchar(128) = ''' union all select ''injected code'';--' declare @SQL nvarchar(max) = 'select [' + @ColumnName + '] from sys.columns' select @sql exec (@SQL) /* -- dsql string select [' union all select 'injected code';--] from sys.columns */
And in this example, you are. The …

Yin and Yang of Data Compression

I plan to write about data compression more in the future but for now I just wanted to share a quick diagram I drew up for a colleague.
Any time you're considering data compression on a table, two primary factors come into play. How much will it save you in disk space vs how much I/O will 

On the Y axis, we have the space savings. You might have a table that's 10GB and but after compressing it, its 1GB. You might have another table that's 1TB and compressed it's 700GB. Clearly the first shrinks by a larger percent, but the second saves much more space.
On the X axis, we have table IO. Compressed data has to be decompressed when it's read or modified, and it's all done by the CPU. So the more I/O going on in a compressed table, the more pressure it will put on your CPU.
So how can we understand the diagram, strictly from the standpoint of these two opposing forces?
Tables in the green area you should almost always compress. These are large tables with big spac…

Embrace The Non-DBO Schema

I don't know how it is everywhere, but in my experience, custom schemas (i.e. something other than dbo) have been seldom used, and I think it's a bit of a shame.

First, what's wrong with dbo? Inherently, nothing. But as time passes and databases grow, dbo can become a cluttered place with objects of overlapping interest. Disentangling those objects from one another can be a bit of a pain.
What Do Schemas Address? Schemas can help with several things, but in my mind, they boil down to three big ones:

OrganizationKeeping objects that belong in the same "place" togetherIsolationMaking clear delineations between the responsibilities of the objects in the schemaManagementDifferentiating permissionsBeing able to do operations on a specific subset of objects without having to rely on external documentation or naming conventionsHow Else Can You Achieve Those Benefits? There are really three ways you can achieve the points from the previous section, and they sort of fall al…

Named Constraints on Temp Tables

Momma sed don't name constraints on temp tables. But why?

How SQL Makes Temp Tables Globally Unique

When you first build a temp table, SQL stores the definition of that temp table away  and every subsequent time you create it, it's actually a clone of that one, original table definition. To allow each individual connection to create a clone of the table, it has to do something about the name so that it won't collide with itself. Let's see what that looks like