BUG: Rule IE028 raises warning when adding a PERSISTED computed column

Jacco

There is an IE028 warning ("Adding NOT NULL column without DEFAULT value") on the third statement, but not on the other two. 

CREATE TABLE dbo.Test_EI028 (t1 AS (1) PERSISTED NOT NULL);

ALTER TABLE dbo.Test_EI028

ADD t2 AS (1) NOT NULL;

ALTER TABLE dbo.Test_EI028

ADD t3 AS (1) PERSISTED NOT NULL;

Tianjiao_Li

Hi @Jacco

The rule definitely doesn't apply to the first statement:

"CREATE TABLE dbo.Test_EI028 (t1 AS (1) PERSISTED NOT NULL);"
Because the rule is checking only ALTER TABLE.

The second statement can't be parsed and that why Code analysis didn't take into account this statement.
"ALTER TABLE dbo.Test_EI028
ADD t2 AS (1) NOT NULL;"
It's incorrect syntax:
https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-table-computed-column-definition-transact-sql?view=sql-server-2016

It allows this:
ALTER TABLE dbo.Test_EI028
ADD t2 AS (1) PERSISTED;
or
ALTER TABLE dbo.Test_EI028
ADD t2 AS (1) PERSISTED NOT NULL;
but not this:
ALTER TABLE dbo.Test_EI028
ADD t2 AS (1) NOT NULL;

rsocol

The bug is that SQL Prompt shows the warning about default values when adding a computed column (if the PERSISTENT keyword is used). We do not need a default value (and cannot add a default value) for a computed column.

Tianjiao_Li

Thanks for your clarification. We can see the problem and have logged it as SP-7133 in our internal bug tracking system. Please keep an eye on the release note!