varbinary to varchar or nvarchar When retrieving a text value, which has been stored in a varbinary field, and converting back to text, is there a way to determine whether it was stored as a VARCHAR or NVARCHAR string?  If it is unknown, telling the CONVERT function it is a NVARCHAR string, when it is in fact VARCHAR will return gibberish.  Run the below for an example:

IF OBJECT_ID(N'dbo.Temp') IS NOT NULL
  DROP TABLE dbo.Temp;

CREATE TABLE Temp(
   ID INT IDENTITY(0,1) NOT NULL
  ,Test VARBINARY(50) NULL 
);

INSERT INTO dbo.Temp (Test) VALUES( CONVERT(VARBINARY(50), 'varchar test string'));
INSERT INTO dbo.Temp (Test) VALUES( CONVERT(VARBINARY(50), N'nvarchar test string'));

SELECT CONVERT(VARCHAR, Test) AS [VARCHAR], CONVERT(NVARCHAR, Test) AS [NVARCHAR]
FROM dbo.Temp;
This isn't really possible in general.

The full range from 0x00 to 0xFF is valid for varchar and from 0x0000 to 0xFFFF for nvarchar so it is impossible to distinguish between two bytes that represent 2 varchar characters or a single nvarchar character.

0x4141 could be 'AA' or N'䅁'

If DATALENGTH is odd then you know it is varchar though. 

Also if the text is primarily standard ASCII as in your example then it will contain a lot of 0x00 when stored as binary if nvarchar. 

This is probably not a character you expect to reasonably find at all in varchar strings so the presence of that could be taken as evidence that the string is nvarchar.