CODEWITHSUNDEEP
sqlsql-servercharnullablecalculated-columns
user166390
user166390

Reputation:

Create non-NULL computed column for constant CHAR(1) in SSMS

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.

Upvotes: 4

Views: 555

Answers (1)

Martin Smith
Martin Smith

Reputation: 453677

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.

Upvotes: 2

Related Questions