5

I'm writing a query that will be mapped to a C# class, and when getting the type of the query's columns, it results that some columns in sys.columns can be NULL

  • is_nullable
  • is_replicated
  • is_merge_published
  • is_dts_replicated

And in the same sys.columns there are others that are NOT NULL

  • is_rowguidcol
  • is_identity
  • is_computed

NOTE: Those columns are bit

I think they should always be NOT NULL, because SQL Server must know the columns' properties.

So, why some/those columns in sys.columns can be NULL and/or in which cases they will be?

  • Yes, but what the OP means is, why the column is_nullable itself is nullable? – Olivier Jacot-Descombes Oct 1 '18 at 14:04
  • @Kin the OP means: i.stack.imgur.com/vIEOT.png – Aaron Bertrand Oct 1 '18 at 14:10
  • I guess that there might exist situations where this information cannot be retrieved like in linked CSV tables maybe. – Olivier Jacot-Descombes Oct 1 '18 at 14:16
  • The same column is nullable in the INFORMATION_SCHEMA view for columns as well. The exact ISO standard is paywalled, but maybe it is a legacy artifact from that. – LowlyDBA Oct 1 '18 at 14:48
  • @LowlyDBA Good catch! But looking at the code for sys.columns, I think it's more incidental than intentional. – Aaron Bertrand Oct 2 '18 at 0:31
5

You are right that it is not possible for the outcome to ever be NULL. From quick investigation, take a look at the definition of sys.columns. If I run:

SELECT OBJECT_DEFINITION(OBJECT_ID(N'sys.columns'));

I see this:

...
sysconv(bit, 1 - (c.status & 1)) AS is_nullable,        -- CPM_NOTNULL
...
FROM sys.syscolpars c
...

If I change to a DAC connection, I can look at the columns here:

SELECT name, is_nullable
  FROM sys.all_columns
  WHERE [object_id] = OBJECT_ID(N'sys.syscolpars')
  AND name = N'status';

Results:

name    is_nullable
------  -----------
status  0

So the source is definitely not nullable, right? Yet if you do the same for sys.columns, columns involving expressions around c.status are marked as is_nullable = 1. That's more a reflection of the expressions / data types involved than the actual possibility of a NULL value.

I initially thought maybe SYSCONV() did something differently from CONVERT() (we can't use the former to check, but I thought maybe it might internally work more like TRY_CONVERT()). This is not the case, in fact this is quite easy to reproduce even without any internals knowledge:

CREATE TABLE dbo.foo(bit_column bit NOT NULL);
GO

CREATE VIEW dbo.bar 
AS                                                      -- nullable?
  SELECT bit_col1 = CONVERT(bit, 1 - (bit_column & 1)), -- YES
         bit_col2 = bit_column & 1, -- no convert       -- NO
         bit_col3 = 1 - bit_column & 1                  -- YES
  FROM dbo.foo;
GO

SELECT name, is_nullable
  FROM sys.dm_exec_describe_first_result_set
       (N'SELECT * FROM dbo.bar', NULL, 1);
GO

DROP TABLE dbo.foo;
DROP VIEW dbo.bar;

Results:

name      is_nullable
--------  -----------
bit_col1  1
bit_col2  0
bit_col3  1

All that said, I don't know what advice to give you. You could conditionally hard-code that this specific column is not nullable in spite of what the metadata says, or you could play it safe and use what the metadata tells you (which will future-proof you in the event the underlying definitions change in future versions).

  • This dba.se answer quotes the standard: IS_NULLABLE INFORMATION_SCHEMA.YES_OR_NO CONSTRAINT COLUMNS_IS_NULLABLE_NOT_NULL NOT NULL. – philipxy Oct 1 '18 at 20:32
  • Great advise! changing the type in the future may bring some troubles – Enrique Zavaleta Oct 3 '18 at 19:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.