EDITED after reading the MSDN forum link from the comment, very interesting.
Regardless of isolation level, two users cannot update a single page simultaneously, nor can any user read a partially updated page. Just imagine how SQL Server would deal with a page where the header says Col3 starts at byte 17. But it really starts at byte 25, because that part of the row hasn't been updated yet. There's no way a database could handle that.
But for rows larger than 8k, multiple pages are used, and that makes a half-updated column possible. Copied from the MSDN link (in case the link breaks), start this query in one window:
if object_id('TestTable') is not null
drop table TestTable
create table TestTable (txt nvarchar(max) not null)
go
insert into TestTable select replicate(convert(varchar(max),
char(65+abs(checksum(newid()))%26)),100000)
go 10
update TestTable set txt=replicate(convert(varchar(max),
char(65+abs(checksum(newid()))%26)),100000)
go 100000
This creates a table and then updates it with a string of 100.000x the same character. While the first query is running, start this query in another window:
while 1=1 begin
if exists (select * from TestTable (nolock) where left(Txt,1) <> right(Txt,1))
break
end
The second query stops when it reads a column that is half updated. That is, when the first character is different from the last. It will finish quickly, proving that it is possible to read half-updated columns. If you remove the nolock
hint, the second query will never finish.
Surprising result! A half-updated XML column might break a (nolock)
report, because the XML would be malformed.
Best Answer
As far as my testing goes (using SQL Server Express 2014, SP1 and SQL Server Developer 2012 SP2, both 64-it), the formula when not using an authenticator for the return value (
VARBINARY
) length of:is:
Try the following:
Returns:
And, if you change the datatype of
@ClearText
to instead beNVARCHAR(4000)
, and run it again, it returns:Note: the formula looks like it can be reduced by cancelling out the
8
s. However, that will cause it to not work correctly since it is banded-ranges of datalengths that fit into a "bucket":So the
(DATALENGTH(@ClearText) / 8)
portion of the formula is enforcing that decimal values are ignored rather than the value be rounded. And that is accomplished by default behavior of dividing two INT values ;-).UPDATE:
The testing done above does not use an option available to ENCRYPTBYPASSPHRASE: specifying an "authenticator". Doing so adds 16 bytes to the minimum length, and then while the increments are still in 8-byte steps, and while the banded-ranges are still 8 bytes each, the initial range is only 4 bytes so the ranges are offset by 4 as compared to the range boundaries when not using an authenticator. To help illustrate, the following chart shows the ranges and their corresponding result lengths:
The formula when using an authenticator for the return value (
VARBINARY
) length of:is:
NOTES:
PassPhrase
has no effect on the result lengthPassPhrase
has no effect on the result lengthAuthenticator
value to have any effect, the value ofAdd_Authenticator
(the 3rd input parameter) must be set to1
Authenticator
has no effect on the result length as long as it is at least 1.Authenticator
will have an effect on the result length, and that effect is the same as settingAdd_Authenticator
to0
.Add_Authenticator
is set to0
, orAuthenticator
is an empty string orNULL
, then the formula is the same as if there was no "authenticator".The following is an expanded and improved test that shows both with and without an "authenticator", and makes it easier to change the
@PassPhrase
value: