Jeff's SQL Server Blog

Random Thoughts & Cartesian Products with Microsoft SQL Server
posts - 157, comments - 2686, trackbacks - 64

My Links

Advertisement

News

Welcome to my weblog. My name is Jeff Smith, I am software developer in Boston, MA and I was recently named a 2009 SQL Server MVP. Check in frequently for tips, tricks, commentary and ideas on SQL Server and .NET programming.


Subscribe





Archives

Post Categories

Programming

Sports

SQL

But it's not missing. It's just ... invisible, that's all.

Microsoft Access has a pretty handy boolean property that you can set for any "text" column in your tables:

AllowZeroLength

True means that empty strings ('') are allowed in the column, False means that they are not.

This is actually quite nice, because by using this along with the Required (i.e., disallow NULLS) property, you can ensure that your column has an actual, non-Null, non-Empty String value without the need for any additional constraints.

SQL Server does not have this property, but we can easily achieve the same effect by using a CHECK constraint:

create table foo
(
    column1 varchar(100) not null check (column1 <> '')
)

This enforces that column1 is not only never null, but it also cannot be an empty string.  For example:

insert into foo (column1) values ('')

Msg 547, Level 16, State 0, Line 2
The INSERT statement conflicted with the CHECK constraint "CK__foo__column1__6E8B6712". The conflict occurred in database "PlayGround", table "dbo.foo", column 'column1'.
The statement has been terminated.

However, we need to remember that there is another side effect of this check constraint:  SQL Server ignores any trailing spaces when comparing two values, so this effectively also means that a non-empty string that consists of all space characters is also not valid in your table.  To demonstrate:

insert into foo (column1) values (space(10))

Msg 547, Level 16, State 0, Line 2
The INSERT statement conflicted with the CHECK constraint "CK__foo__column1__6E8B6712". The conflict occurred in database "PlayGround", table "dbo.foo", column 'column1'.
The statement has been terminated.

Now, this can be a good thing or a bad thing depending on what you are looking for.  Overall, I'd say it's a good thing -- to me, a string of 30 spaces is just as bad as a NULL or an empty string in a column that is required, such as a project code or a first/last name.  Of course, what ultimately is best for any specific situation depends on the requirements.

If you decide that you do want to disallow only empty strings but strings consisting of all spaces are valid, then you can write your check constraint like this:

create table foo
(
    column1 varchar(100) not null check (column1 + 'x' <> 'x')
)

By appending 'x' to the end of the column's potential value and comparing the result with 'x', we are sure that if the column contains at least one space this check will be TRUE, but we are still not allowing empty strings.

So, don't forget about handling empty and/or "invisible" strings in your columns.  It won't generally satisfy your client if you argue that "the username isn't missing -- it isn't null.  It is just ... invisible!" 

After all, you don't want to end with "invisible" payments on your invoices!

Print | posted on Wednesday, March 19, 2008 3:51 PM | Filed Under [ T-SQL ]

Feedback

Gravatar

# re: But it's not missing. It's just ... invisible, that's all.

column1 varchar(100) not null check (column1 + 'x' <> 'x')

Not forgetting the trim function also:


column1 varchar(100) not null check (rtrim(column1) <> '')
3/20/2008 12:16 AM | Chris
Gravatar

# re: But it's not missing. It's just ... invisible, that's all.

Hi Chris -- two things:

1) as mentioned, comparisons ignore trailing spaces. Using RTRIM() to trim trailing spaces is not necessary and has no effect

and

2) the check constraint you wrote is not equivalent to the one you quoted -- they are completely different.

3/20/2008 8:49 AM | Jeff
Gravatar

# re: But it's not missing. It's just ... invisible, that's all.

LEN() and DATALENGTH() might be a cleaner set of choices. To wit:
DECLARE @foo TABLE
(
column1 varchar(100) not null check (column1 <> '')
)

insert into @foo (column1)
values (' ')

DECLARE @foo1 TABLE
(
column1 varchar(100) not null check ( LEN(column1) <> 0)
)

insert into @foo1 (column1)
values (' ')


DECLARE @foo2 TABLE
(
column1 varchar(100) not null check ( DATALENGTH(column1) <> 0)
)

insert into @foo2 (column1)
values (' ')


Nice post!
3/20/2008 10:41 AM | Chris McKenzie
Gravatar

# re: But it's not missing. It's just ... invisible, that's all.

Hi Jeff,

Nice post. I want to thank everybody on this and on other forums for sharing your knowledge. Sometimes I am about to go search for something to help me then I come upon my answer in this way. I am not quite new to SQL but mostly from a developers point of view. I like the idea of being a DBA and I am like a vacuum cleaner, sucking up all the knowledge I can get. Thanks again!!
3/26/2008 2:54 AM | Manie Verster
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET