StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Yes, it is eminently doable.</p> <p>Adding a column where NULL is acceptable and has no default value does not require a long-running lock to add data to the table.</p> <p>If you supply a default value, then SQL Server has to go and update each record in order to write that new column value into the row.</p> <p><strong>How it works in general:</strong></p> <pre><code>+---------------------+------------------------+-----------------------+ | Column is Nullable? | Default Value Supplied | Result | +---------------------+------------------------+-----------------------+ | Yes | No | Quick Add (caveat) | | Yes | Yes | Long running lock | | No | No | Error | | No | Yes | Long running lock | +---------------------+------------------------+-----------------------+ </code></pre> <p><strong>The caveat bit:</strong></p> <p><strike>I can't remember off the top of my head what happens when you add a column that causes the size of the NULL bitmap to be expanded. I'd like to say that the NULL bitmap represents the nullability of all the the columns <em>currently in the row</em>, but I can't put my hand on my heart and say that's definitely true.</strike></p> <p>Edit -> @MartinSmith pointed out that the NULL bitmap will only expand when the row is changed, many thanks. However, as he also points out, if the size of the row expands past the 8060 byte limit in SQL Server 2012 then <a href="http://rusanu.com/2012/02/16/adding-a-nullable-column-can-update-the-entire-table/">a long running lock may still be required</a>. Many thanks * 2.</p> <p><strong>Second caveat:</strong></p> <p>Test it.</p> <p><strong>Third and final caveat:</strong></p> <p>No really, test it.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload