Computed column definition is null

Question

(A.) The below is the condition of "d_no" column in table test. Here you can see that it is a computed column and its definition is NULL.So I want to know that how this case is possible to generate? I am unable to create a computed column with NULL definition.

table name : test
column name : d_no
is_nullable : 1
definition : NULL
is_computed : 1

This case is for SQL Server 2012 (11.0.6523) version.

(B.) Scenario : There is "User1" who is real database owner(own database). There is another user "User2". The User2 has some permissions to access the database of User1, like SELECT or get DDL. When I login with User2 and tried to get CREATE query for the table which has a column as Computed Column. For that table we are not getting computed column definition in the result query. We are getting xyz (numeric (18,0) null)

So is it possible that there also same permission issue is occur which may result null in previous case(A)?

Answer 1

You can't get null definition.Even creating computed column as null,won't change the defintion to null..

create table dbo.t4
(
id int null,
idplus as cast(null as int)
)

select definition 
from sys.computed_columns 

Output:

(CONVERT([int],NULL,0))

You are seeing NULL defintion due to permissions issue..

Below is what MSDN has to say about this..

The rules for viewing the Transact-SQL source code of database objects are stricter than for viewing metadata.

To see the Transact-SQL definition of an object, a user must either own the object or have been granted one of the following permissions on the object:

CONTROL
ALTER
TAKE OWNERSHIP
VIEW DEFINITION

Same is the case with below views as well,if you don't have permissions,Definition would be null..