Strange behaviour converting NVARCHAR(MAX) to BINARY

Few days back I was writing a CLR function to be used for hashing string values. The only option was the CLR functions since T-SQL doesn’t have any functionality to convert a string to hashed value using a key. Using the HASHBYTES function you can only provide the algorithm.

DECLARE @Data NVARCHAR(4000);  
SET @Data = CONVERT(NVARCHAR(4000),'My Secret Message');  
SELECT HASHBYTES('SHA1', @Data);  

I have written the CLR function to achieve the requirement, but during testing the validation was failing and when I go through the code I couldn’t find any issue in the function as well. But inspecting carefully I noticed that when a variable type NVARCHAR(n) and a variable type of NVARCHAR(MAX) gives different results when it’s converted to Binary. Which was the root cause for the issue I was facing.

DECLARE 
	@Data1	AS NVARCHAR(MAX) = '1111'
	,@Data2	AS NVARCHAR(10) = '1111'

SELECT 
	CAST(@Data1 AS BINARY(30)) AS ValueMax
SELECT 
	CAST(@Data2 AS BINARY(30)) AS ValueN

As you can see the above example the zero bytes are represented differently for NVARCHAR(MAX) when it’s converted to BINARY.

I do not have any explanation for this. I am sharing the information in case anyone come across this issue. Please feel free to comment.

Behaviour of IDENTITY Columns and SEQUENCES with TRANSACTIONS

Few days back, I was caught in a discussion with couple of my colleagues, regarding a problem they are facing with an IDENTITY column.

The issue was that when a transaction is rolled back the identity seed isn’t rolling back as expected. This was causing the business application to loose the id sequence.

There is no fix or a workaround for this. All that I could provide was an explanation.

I will illustrate the issue and an explanation why it’s happening.

Behaviour of IDENTITY Columns

We will create the following table to hold employee details.

CREATE TABLE dbo.EmployeeInfo(
	Id			INT IDENTITY(1,1) NOT NULL,
	EmpName		VARCHAR(100) NOT NULL
)

Now we will insert few records to the table in the following manner.

1. Without a transaction
2. With a transaction. But we will rollback the transaction.
3. With a transaction. But we will commit it.

INSERT INTO dbo.EmployeeInfo (EmpName)
VALUES('John')

BEGIN TRAN
	INSERT INTO dbo.EmployeeInfo (EmpName)
	VALUES('Jane')
ROLLBACK

INSERT INTO dbo.EmployeeInfo (EmpName)
VALUES('James')

SELECT 
	EI.Id
	,EI.EmpName 
FROM
	dbo.EmployeeInfo AS EI

And when checked, you could see the following results.

Usually the expectation is to see the employee “James” with an Id of 2.

What you should understand here is that this isn’t a flaw or a bug. This is the exact intended behaviour and it has been explained in the following MSDN article.

https://docs.microsoft.com/en-us/sql/t-sql/statements/create-table-transact-sql-identity-property

Behaviour of SEQUENCES

SEQUENCEs were introduced in SQL Server 2012. The purpose of the SEQUENCE objects were to aid in handling the auto increment numbers, in case you prefer to handle the sequence without using an IDENTITY column.

First we will create a sequence object. The minimum syntax required to create a sequence object is a name and the data type. Additionally you can mention many other attributes like starting index, increment seed etc.

CREATE SEQUENCE dbo.TempNumberSequence AS INT

Further details regarding other options can be found on the following URL:

https://docs.microsoft.com/en-us/sql/t-sql/statements/create-sequence-transact-sql

Now we will create a similar table like we created in the previous example, but without an IDENTITY column.

CREATE TABLE dbo.EmployeeInfoSeq(
	Id			INT 
	,EmpName	VARCHAR(100) NOT NULL
)

We will insert 3 records in the same way like we did in the previous example.

DECLARE @NextSeq AS INT
SELECT @NextSeq = NEXT VALUE FOR dbo.TempNumberSequence
INSERT INTO dbo.EmployeeInfoSeq (
	Id
	,EmpName
)
VALUES (
	@NextSeq
	,'John'
)
GO

DECLARE @NextSeq AS INT
SELECT @NextSeq = NEXT VALUE FOR dbo.TempNumberSequence
BEGIN TRAN
	INSERT INTO dbo.EmployeeInfoSeq (
		Id
		,EmpName
	)
	VALUES (
		@NextSeq
		,'Jane'
	)
ROLLBACK
GO


DECLARE @NextSeq AS INT
SELECT @NextSeq = NEXT VALUE FOR dbo.TempNumberSequence
INSERT INTO dbo.EmployeeInfoSeq (
	Id
	,EmpName
)
VALUES (
	@NextSeq
	,'James'
)
GO

Afterwards if you check, you will see the following results.

Hope this will help you in you day to day development work.