Computed column does not include one of the values

Question

I'm trying to create a computed column in order to have a unique index on a nullable column that ignores NULL rows¹. I've composed this test case:

SELECT TEST_ID, CODE, UNIQUE_CODE, CAST(UNIQUE_CODE AS VARBINARY(4000)) AS HEX FROM ( SELECT TEST_ID, CODE, ISNULL(CODE, CONVERT(VARCHAR, SPACE(10)) + CONVERT(VARCHAR, TEST_ID)) AS UNIQUE_CODE FROM ( SELECT 1 AS TEST_ID, 'ABCDEFGHIJ' AS CODE UNION ALL SELECT 2, 'XYZ' UNION ALL SELECT 3, NULL ) TEST ) X;

It works as expected when CODE is not null but I only get a string of whitespaces when CODE is null (i.e., the trailing TEST_ID is missing):

TEST_ID | CODE | UNIQUE_CODE | HEX --------+------------+-------------+----------------------- 1 | ABCDEFGHIJ | ABCDEFGHIJ | 0x4142434445464748494A 2 | XYZ | XYZ | 0x58595A 3 | NULL | | 0x20202020202020202020

The funny thing is that I already use this technique successfully in another table and I can't spot the difference:

CREATE TABLE SOME_OTHER_TABLE ( SOME_OTHER_TABLE_ID INT IDENTITY(1, 1) NOT NULL, NOMBRE VARCHAR(50), -- This works just fine: NOMBRE_UNICO AS ISNULL(NOMBRE, CONVERT(VARCHAR, SPACE(50)) + CONVERT(VARCHAR, SOME_OTHER_TABLE_ID)), CONSTRAINT SOME_OTHER_TABLE_PK PRIMARY KEY (SOME_OTHER_TABLE_ID) );

What am I missing?

(¹) This was a workaround for SQL Server 2005 that's no longer necessary in later versions thanks to filtered indexes.

You should explicitly define size/length for all varchar conversions and constant values. — IVNSTN
– IVNSTN, Commented Feb 18, 2016 at 15:51
"in order to have a unique index on a nullable column that ignores NULL rows". Why not do create index [idxName] on [yourTable] (Code) where Code IS NOT NULL? — Ben Thul
– Ben Thul, Commented Feb 18, 2016 at 17:47
@BenThul Because I had no idea you could do that. None of the docs I found said so and some suggested the complicate workaround I'm using... — Álvaro González
– Álvaro González, Commented Feb 18, 2016 at 18:00
@ÁlvaroGonzález: Filtered indexes were added in SQL 2008, so you should be good to go. A lot fewer moving parts than you've got going here. — Ben Thul
– Ben Thul, Commented Feb 18, 2016 at 18:33
@BenThul I've just tried filtered indexes and got rid of my workaround. App was started on SQL Server 2005 but that's no longer an excuse. Thank you very much. — Álvaro González
– Álvaro González, Commented Feb 19, 2016 at 10:37

IVNSTN · Accepted Answer · 2016-02-18 16:07:07Z

There you go with " 3"

SELECT TEST_ID, CODE, UNIQUE_CODE, CAST(UNIQUE_CODE AS VARBINARY(4000)) AS HEX FROM ( SELECT TEST_ID, CODE, ISNULL(CODE, CONVERT(VARCHAR(20), SPACE(10)) + CONVERT(VARCHAR(20), TEST_ID)) AS UNIQUE_CODE FROM ( SELECT 1 AS TEST_ID, cast('ABCDEFGHIJ' as varchar(20)) AS CODE UNION ALL SELECT 2, 'XYZ' UNION ALL SELECT 3, NULL ) TEST ) X;

'ABCDEFGHIJ' (first value in the union list) is exactly 10 characters and this column is a first argument of IsNull. So it takes 10 characters as size for IsNull result. Which is enough only for spaces. Replacing this constant with 'ABCDEFGHIJKLMNOPQR' would do the trick also.

Thank you. Let me check if I can make it work with an actual table (CODE is actually a VARCHAR(10) column) and I'll report back.
Your answer is correct. Just a note: when CODE is a real column you need to convert inside ISNULL(), e.g.: ISNULL(CONVERT(VARCHAR(50), CODE), ....

Steven · Accepted Answer · 2016-02-18 16:04:05Z

It looks like SQL is trying to help out to define the column length in your inner query. By casting/converting to a specific size this fixes the problem. Once your UNIQUE_CODE field exceeds this value, the returned value is limited to the size of the column.

SELECT TEST_ID, CODE, UNIQUE_CODE, CAST(UNIQUE_CODE AS VARBINARY(4000)) AS HEX FROM ( SELECT TEST_ID, CODE, ISNULL(CODE, CONVERT(VARCHAR, SPACE(10)) + CONVERT(VARCHAR, TEST_ID)) AS UNIQUE_CODE FROM ( SELECT 1 AS TEST_ID, CONVERT(VARCHAR(50), 'ABCDEFGHIJ') AS CODE UNION ALL SELECT 2, 'XYZ' UNION ALL SELECT 3, NULL ) TEST ) X;

TheGameiswar · Accepted Answer · 2016-02-18 16:12:20Z

You can run below piece of code to find out why ?

this fails :

declare @a char(20) set @a=null declare @b char(10) set @b='aaaaaa' select isnull(@a,convert(char(10),space(10)+@b))

This works:

declare @a char(20) set @a=null declare @b char(10) set @b='aaaaaa' select isnull(@a,convert(char(30),space(10)+@b))

Collectives™ on Stack Overflow

Computed column does not include one of the values

3 Answers 3

3 Comments

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Related