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

Question

In my database, I have several computed columns to provide referential integrity constraints. I use calculated columns, not default columns, because LINQ2SQL, which I use, does not understand the default values ​​from the database.

My attempt to use

CAST('C' as CHAR(1)) 

which automatically converts to

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

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

If I just use 'C' or ISNULL(NULL,'C') (which results in a type other than NULL), the column is selected as VARCHAR (?). And, if I combine the two to use ISNULL(NULL,CONVERT([char](1),'C',0)) , I will return to the NULL-capable CHAR (1).

There are two reasons for this:

• The calculated column will participate in the association with the outer column CHAR (1).

• Non-NULL CHAR (1) maps directly to the .NET Character type in LINQ2SQL.

---

Update:

This works for me with ISNULL(CONVERT([char](1),'C',0),0) , but I'm not sure why. Anyway, it looks like ISNULL(..,0) will be of un-unifiy type next.

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

Answer 1

1) In string literals, SQL Server without the N prefix is ​​always considered as having the value 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 described in Computed Columns

The database engine automatically determines the invalidity of calculated columns based on the expressions used. The result of most expressions is considered null, even if only immutable columns are present ... An expression that is null can be turned False by specifying ISNULL(check_expression, constant) , where the constant is a non-null value replaced by any null result.

3) Documentation for ISNULL ( check_expression , replacement_value )

Return Data Types Returns the same type as check_expression. If the NULL literal is provided as check_expression, returns the replacement value data type.

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