Why sys.columns.is_nullable can be null?

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty margin-bottom:0;

up vote
5
down vote

favorite

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?

edited 1 hour ago

a_horse_with_no_name

36.6k769108

asked 1 hour ago

Enrique Zavaleta

1263

New contributor

is_nullable - means that column allows null values.
â€“Â Kin
1 hour ago

Yes, but what the OP means is, why the column is_nullable itself is nullable?
â€“Â Olivier Jacot-Descombes
1 hour ago

@Kin the OP means: i.stack.imgur.com/vIEOT.png
â€“Â Aaron Bertrandâ™¦
1 hour ago

I guess that there might exist situations where this information cannot be retrieved like in linked CSV tables maybe.
â€“Â Olivier Jacot-Descombes
1 hour ago

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
1 hour ago

add a commentÂ |Â

up vote
5
down vote

favorite

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?

edited 1 hour ago

a_horse_with_no_name

36.6k769108

asked 1 hour ago

Enrique Zavaleta

1263

New contributor

is_nullable - means that column allows null values.
â€“Â Kin
1 hour ago

Yes, but what the OP means is, why the column is_nullable itself is nullable?
â€“Â Olivier Jacot-Descombes
1 hour ago

@Kin the OP means: i.stack.imgur.com/vIEOT.png
â€“Â Aaron Bertrandâ™¦
1 hour ago

I guess that there might exist situations where this information cannot be retrieved like in linked CSV tables maybe.
â€“Â Olivier Jacot-Descombes
1 hour ago

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
1 hour ago

add a commentÂ |Â

up vote
5
down vote

favorite

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?

edited 1 hour ago

a_horse_with_no_name

36.6k769108

asked 1 hour ago

Enrique Zavaleta

1263

New contributor

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?

sql-server system-tables

edited 1 hour ago

a_horse_with_no_name

36.6k769108

asked 1 hour ago

Enrique Zavaleta

1263

New contributor

edited 1 hour ago

a_horse_with_no_name

36.6k769108

asked 1 hour ago

Enrique Zavaleta

1263

New contributor

edited 1 hour ago

a_horse_with_no_name

36.6k769108

edited 1 hour ago

a_horse_with_no_name

36.6k769108

edited 1 hour ago

a_horse_with_no_name

36.6k769108

asked 1 hour ago

Enrique Zavaleta

1263

New contributor

asked 1 hour ago

Enrique Zavaleta

1263

asked 1 hour ago

Enrique Zavaleta

1263

New contributor

Enrique Zavaleta is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

is_nullable - means that column allows null values.
â€“Â Kin
1 hour ago

Yes, but what the OP means is, why the column is_nullable itself is nullable?
â€“Â Olivier Jacot-Descombes
1 hour ago

@Kin the OP means: i.stack.imgur.com/vIEOT.png
â€“Â Aaron Bertrandâ™¦
1 hour ago

I guess that there might exist situations where this information cannot be retrieved like in linked CSV tables maybe.
â€“Â Olivier Jacot-Descombes
1 hour ago

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
1 hour ago

add a commentÂ |Â

is_nullable - means that column allows null values.
â€“Â Kin
1 hour ago

Yes, but what the OP means is, why the column is_nullable itself is nullable?
â€“Â Olivier Jacot-Descombes
1 hour ago

@Kin the OP means: i.stack.imgur.com/vIEOT.png
â€“Â Aaron Bertrandâ™¦
1 hour ago

I guess that there might exist situations where this information cannot be retrieved like in linked CSV tables maybe.
â€“Â Olivier Jacot-Descombes
1 hour ago

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
1 hour ago

is_nullable - means that column allows null values.
â€“Â Kin
1 hour ago

Yes, but what the OP means is, why the column is_nullable itself is nullable?
â€“Â Olivier Jacot-Descombes
1 hour ago

@Kin the OP means: i.stack.imgur.com/vIEOT.png
â€“Â Aaron Bertrandâ™¦
1 hour ago

I guess that there might exist situations where this information cannot be retrieved like in linked CSV tables maybe.
â€“Â Olivier Jacot-Descombes
1 hour ago

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
1 hour ago

add a commentÂ |Â

1 Answer
1

active

oldest

votes

up vote
4
down vote

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).

answered 1 hour ago

Aaron Bertrandâ™¦

145k19280468

add a commentÂ |Â

Your Answer

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "182"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
convertImagesToLinks: false,
noModals: false,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);

);

Enrique Zavaleta is a new contributor. Be nice, and check out our Code of Conduct.

draft saved

draft discarded

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f218979%2fwhy-sys-columns-is-nullable-can-be-null%23new-answer', 'question_page');

);

Post as a guest

Name

1 Answer
1

active

oldest

votes

1 Answer
1

active

oldest

votes

up vote
4
down vote

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

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

answered 1 hour ago

Aaron Bertrandâ™¦

145k19280468

add a commentÂ |Â

up vote
4
down vote

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

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

answered 1 hour ago

Aaron Bertrandâ™¦

145k19280468

add a commentÂ |Â

up vote
4
down vote

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

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

answered 1 hour ago

Aaron Bertrandâ™¦

145k19280468

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

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

answered 1 hour ago

Aaron Bertrandâ™¦

145k19280468

answered 1 hour ago

Aaron Bertrandâ™¦

145k19280468

answered 1 hour ago

Aaron Bertrandâ™¦

145k19280468

answered 1 hour ago

Aaron Bertrandâ™¦

145k19280468

add a commentÂ |Â

Enrique Zavaleta is a new contributor. Be nice, and check out our Code of Conduct.

draft saved

draft discarded

Enrique Zavaleta is a new contributor. Be nice, and check out our Code of Conduct.

draft saved

draft discarded

Post as a guest

Name

Search This Blog

Iyfjky