4

In my database I have some computed columns to help ensure referential integrity constraints. I use computed columns and not default-value columns because LINQ2SQL, which is what I use, does not understand default values from the database.

My attempt is to use

CAST('C' as CHAR(1))

which is automatically converted to 

(CONVERT([char](1),'C',0))

by SSMS. However, this results in a NULL-able CHAR(1) column type.

If I just use 'C' or ISNULL(NULL,'C') (which does result in a non-NULL type) the column is picked up as a VARCHAR(?). And, if I combine the two to use ISNULL(NULL,CONVERT([char](1),'C',0)), I'm back to a NULL-able CHAR(1).

There are two reasons I want this:

  1. The computed column will participate in a relation with a foreign CHAR(1) column.

  2. A non-NULL CHAR(1) maps directly to the .NET Character type in LINQ2SQL.

Update:

It Works For Me with ISNULL(CONVERT([char](1),'C',0),0), but I'm not really sure "why". If anything, it seems like ISNULL(..,0) would un-unifiy the type further.

I would be more than happy for an answer with a good explanation.

  • Seems to be lack-of-awesome in SSMS window: http://stackoverflow.com/questions/2050007/marking-persisted-computed-columns-not-null-in-sql-server-management-studio –  Mar 23 '13 at 01:21

1 Answers1

2

1) In SQL Server string literals without an Nprefix are always treated as being of varchar(x) where x is derived from the length of the string.

 +---------------+--------------+
| String Length |  Data Type   |
+---------------+--------------+
| 0             | varchar(1)   |
| 1-8000        | varchar(x)   |
| > 8000        | varchar(max) |
+---------------+--------------+

2) As documented in Computed Columns

The Database Engine automatically determines the nullability of computed columns based on the expressions used. The result of most expressions is considered nullable even if only nonnullable columns are present, ... An expression that is nullable can be turned into a nonnullable one by specifying ISNULL(check_expression, constant), where the constant is a nonnull value substituted for any null result.

3) The documentation for ISNULL ( check_expression , replacement_value )

Return Types Returns the same type as check_expression. If a literal NULL is provided as check_expression, returns the datatype of the replacement_value.

These three pieces of information explain all the behavior in your question.

Martin Smith
  • 438,706
  • 87
  • 741
  • 845
  • They sure do! Thanks. For some reason I had it in my head that ISNULL unified across both arguments .. silly me, should have RTFM'ed. –  Mar 23 '13 at 09:31
  • 1
    @pst - `COALESCE` looks at all arguments and uses datatype precedence rules but `ISNULL` doesn't. `SELECT ISNULL(CAST(NULL AS VARCHAR(3)),'ABCDEFG'),COALESCE(CAST(NULL AS VARCHAR(3)),'ABCDEFG')` – Martin Smith Mar 23 '13 at 10:00