0

What Transact-SQL nvarchar value is not null and <> '' and datalength = 0?

I have a table with Field1 of data type nvarchar(max). For the value of Field1 in one particular record, the following statements are true:

  • Field1 is not null
  • DataLength(Field1) = 0
  • Cast(IsNull(Field1, '') As nvarchar(1000)) is null

I am presuming Field1 <> '' because Cast('' As nvarchar(1000)) is not null.

Background: Cast(IsNull(Field1, '') As nvarchar(1000)) is throwing an error from a stored procedure trying to insert that value where null is not allowed. When I modified the statement to use

 IsNull(Cast(Field1 As nvarchar(1000)), '')

instead, things were happy. This issue has occurred twice in a remote clients database and I do not know how to reproduce it at will.

A backup copy of their database restored on my machine no longer had this issue. Is this some kind of database corruption? Or is there some character / collation combination that satisfies the conditions above?

Thank you for your input.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
RJBreneman
  • 787
  • 7
  • 21
  • 2
    Was the data originally `ntext` and then upgraded to `nvarchar(max)`? If so might be the issue here http://stackoverflow.com/q/10295771/73226 – Martin Smith Jan 04 '16 at 18:27
  • 1
    There are probably a number of special characters that could cause this. You might consider using the ASCII() function to analyze the contents of the field. – Tab Alleman Jan 04 '16 at 18:28
  • @MartinSmith No, it's been nvarchar(max) for a long time. – RJBreneman Jan 04 '16 at 19:01
  • @TabAlleman do you know of an example special character that evaluates datalength = 0? – RJBreneman Jan 04 '16 at 19:03
  • 1
    Sounds like a corrupted null bitmap then. `DATALENGTH` measures bytes and won't care what characters are in there. Not sure why you wouldn't get this on your restored backup though. Is there more than one copy of the value? Is it `INCLUDE-`d in any indexes? – Martin Smith Jan 04 '16 at 19:05
  • @MartinSmith It is not included in any indexes. On two occasions, I have restored a backup copy of a database with this behavior and both times I could not reproduce the behavior of the original. I don't know enough about null bitmaps to conjecture if a backup/restore would resolve the issue but it sounds reasonable. What maintenance would fix a corrupt null bitmap on a live database? Thank you. – RJBreneman Jan 04 '16 at 19:23
  • Not off the top of my head, no. I would use ASCII() to investigate if I were the one having this problem. – Tab Alleman Jan 04 '16 at 19:24
  • You should run `DBCC CHECKDB` and see if that picks up anything. The accepted answer in the linked question also says `UPDATE Test SET Field1= Field1` worked for that particular case. – Martin Smith Jan 04 '16 at 19:28
  • 1
    @MartinSmith you get my accepted answer. I found out my earlier comment was wrong and there was a recent conversion from ntext. Thanks so much. – RJBreneman Jan 04 '16 at 19:51

0 Answers0