How long is a NULL?

Click here to monitor SSC

<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>

SQLServerCentral is supported by Red Gate Software Ltd.

Log in :: Register :: Not logged in

<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>

Recent Posts

Popular Topics

Home

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Arto Ahlstedt » How long is a NULL?

Add to briefcase

31 posts, Page 3 of 4

«««1 2 3 4 »»

How long is a NULL?

Expand / Collapse

Author

Message

Posted Monday, December 14, 2009 7:24 AM

SSCrazy

Group: General Forum Members
Last Login: Monday, May 06, 2013 5:31 AM
Points: 2,226, Visits: 438

I got the answer correct, but only because I guessed there was a catch somewhere. I really don't like implicit conversions, especially when I get unexpected results Angry

Angry

/Håkan Winther
MCITP:Database Developer 2008

Post #833808

Posted Monday, December 14, 2009 7:42 AM

Hall of Fame

Group: General Forum Members
Last Login: Thursday, June 13, 2013 8:50 PM
Points: 3,208, Visits: 4,178

Hugo Kornelis (12/12/2009)

I don't have a SQL Server 2000 instance running, so I can only guess there.
...
My guess is that on SQL Server 2000, the engine assumes a zero length string for NULL.

After reading this, I became interested in 'zero-length strings'. I found one mention of varchar(0) in BOL (http://msdn.microsoft.com/en-us/library/ms143359(SQL.90).aspx):

Behavior Changes to Database Engine Features in SQL Server 2005

SQL Server 2000 behavior
A zero-length string or binary value that is used as the definition of a computed table column creates a column of type varchar(0), nvarchar(0), or varbinary(0).

After that I found a SQL Server 2000 instance on one of our development servers. Some interesting results were obtained.

SELECT 'abcde' AS a, 'abcde' + NULL AS b INTO TestTable; 
go 
EXEC sp_help TestTable; 
go 
DROP TABLE TestTable;

Column 'b' has type varchar(5).

Creating a table with a zero-length computed column:

CREATE TABLE TestTable (a VARCHAR(10), b AS SUBSTRING(a, 1, 0)) 
go 
EXEC sp_help TestTable; 
go 
DROP TABLE TestTable;

Column 'b' has type varchar(0)!

Trying to create a copy of the above table:

CREATE TABLE TestTable (a VARCHAR(10), b AS SUBSTRING(a, 1, 0))
go
SELECT * INTO TestTable_Copy FROM TestTable

This code causes the error: "Msg 2731, Level 16, State 1, Line 1. Column 'b' has invalid width: 0."

So SQL Server 2000 sometimes uses varchar(0) data type. I was surprised Smile

Smile

Post #833831

The Dixie Flatline

Posted Thursday, December 17, 2009 4:51 PM

Hall of Fame

Group: General Forum Members
Last Login: Today @ 2:35 PM
Points: 3,840, Visits: 5,641

Excellent question. I totally missed it, and this has finally driven me to join the COALESCE camp.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? -- Stephen Stills

Post #836055

Posted Friday, December 18, 2009 3:13 PM

Right there with Babe

Group: General Forum Members
Last Login: Yesterday @ 7:44 AM
Points: 787, Visits: 169

Odly enough, when i ran the code I got '1234' as a result...

Post #836680

gopal.sharma 27953

Posted Wednesday, December 23, 2009 6:36 AM

SSC Rookie

Group: General Forum Members
Last Login: Wednesday, December 23, 2009 6:27 AM
Points: 27, Visits: 4

logic is here...

SET CONCAT_NULL_YIELDS_NULL ON
SELECT ISNULL('1234'+NULL,'1234567890')

=>> 12345

SET CONCAT_NULL_YIELDS_NULL ON
SELECT ISNULL('1234'+NULL,'1234567890')

=>> 12345

SET CONCAT_NULL_YIELDS_NULL ON
SELECT ISNULL('1234'+NULL+Null,'1234567890')

=>> 123456

SET CONCAT_NULL_YIELDS_NULL ON
SELECT ISNULL('1234'+NULL+Null+Null,'1234567890')

=>> 1234567

---
Logic >> '1'+'2'+'3'+'4'+'\0'+'\0'+'\0'

Post #838482

L' Eomot Inversé

Posted Thursday, February 04, 2010 1:32 PM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 12:10 PM
Points: 7,185, Visits: 7,285

Ouch! I didn't know this had changed in SQLS 2005.

I wonder how much code (if any) the change has broken?

Tom
Is minic a gheibheann béal oscailte dorn dúnta.
Is minig a cheapas beul fosgailte dòrn dùinte.

http://es.linkedin.com/in/tomthomsonsoftware

Post #859938

Posted Saturday, March 20, 2010 2:28 PM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, March 15, 2013 10:35 AM
Points: 594, Visits: 654

I cannot tell a lie... I ran the code before answering this one. Tongue

Tongue

I wanted to pick '1234567890' but I wanted to prove I was "right" first. What a surprise! I, too, will be using COALESCE more often...

Who knew that a NULL would have a character length of 1??? (in this case) Can't wait to stump my friends with this one!

Peter Trast
Microsoft Certified ...(insert many literal strings here)
Microsoft Design Architect with Alexander Open Systems

Post #886963

Posted Thursday, July 08, 2010 5:54 AM

SSC Veteran

Group: General Forum Members
Last Login: Friday, February 22, 2013 3:17 AM
Points: 242, Visits: 87

SET CONCAT_NULL_YIELDS_NULL ON
SELECT ISNULL('abcd'+NULL+1,'1234567890')
if i add any numeric value then it gives->1234567890 why?

Post #949214

Posted Thursday, July 08, 2010 6:56 AM

SSC Veteran

Group: General Forum Members
Last Login: Thursday, June 06, 2013 3:37 AM
Points: 298, Visits: 580

Adding a numeric value turns the expression into an int because of type precedence rules. The fact that 'abcd' does not readily convert into an int does not stop this nor generate a run-time error.

So we have a NULL of type int as the first argument and a string which is readily convertible into an int as the second argument and we get an int out of ISNULL.

Try adding one to the result. Or '1'.

Using COALESCE instead of ISNULL also returns an int. The difference is usually in the length of the varchar which is returned and this is sometimes subtle enough to sneak into production code waiting to bite you.

Post #949256

Posted Thursday, July 08, 2010 7:34 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 2:44 PM
Points: 5,297, Visits: 7,239

sukhendass (7/8/2010)

SET CONCAT_NULL_YIELDS_NULL ON
SELECT ISNULL('abcd'+NULL+1,'1234567890')
if i add any numeric value then it gives->1234567890 why?

The explanation posted by Arto is almost correct, but not entirely.

The inner expression ('abcd'+NULL+1) is evaluated left to right. The first part is 'abcd'+NULL. The 'abcd' is character (varchar(4) to be precise); the NULL is untyped, but based on context SQL Server concludes that you probably meant character as well, and uses the minimum length (varchar(1)). The result is varchar(5); because of the CONCAT_NULL_YIELDS_NULL, the value is NULL.

The second part is ( ('abcd'+NULL) + 1). The 'abcd' + NULL is, as we have seen, varchar(5); the 1 is considered to be integer. Rules of data type precedence say that in this case, varchar(5) gets converted to int - so NULL typed as varchar(5) gets converted to NULL typed as int, 1 is added, and the result is still NULL (and still typed as int)

Now, for a mental detour - what happens if you change SET CONCAT_NULL_YIELDS_NULL to OFF? In that case, the evaluation remains the same, but the result of 'abcd' + NULL is now 'abcd' (again, tpyed as varchar(5)). The + 1 again introduces conversion to integer - but for 'abcd', this conversion fails, so now you get a runtime error.

Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

Post #949279

« Prev Topic | Next Topic »

Add to briefcase

31 posts, Page 3 of 4

«««1 2 3 4 »»

Permissions

Expand / Collapse