CODEWITHSUNDEEP
sql-server
Alejandro
Alejandro

Reputation: 7813

What 'nullability' do columns have when DDL doesn't explicitly set it?

There are often DB schema change scripts creating or modifying columns that don't state if columns are nullable or not. The concrete cases in question are those which look like this:

--Create a new table or add a new column to an existing table
CREATE TABLE dbo.test (SomeColumn INT) ;
ALTER TABLE dbo.test ADD SomeColumn INT ;
--Change the definition of an existing column
ALTER TABLE dbo.test ALTER COLUMN SomeColumn BIGINT ;

In such cases, the statements don't explicitly state if the column is NULL or NOT NULL. Question is, what NULL option is applied by default in each of these cases (new table, alter existing table with new column, and alter existing column altering existing column.)

Of particular interest is an existing column definition that is changed (suppose to enlarge a data type) but the script does not specify nullability; What would the final state be, NULL or NOT NULL? Will it preserve its previous state? Depending on what?

Upvotes: 1

Views: 163

Answers (1)

Martin Smith
Martin Smith

Reputation: 453287

In a create table It depends.

Most clients will SET ANSI_NULL_DFLT_ON ON which means that the columns will end up NULL (except for columns in a PK where NOT NULL is always the default).

But it is possible to SET ANSI_NULL_DFLT_OFF ON In which case they will be NOT NULL.

If both are OFF it falls back to the is_ansi_null_default_on database option.

So best to be explicit.

In an alter table it always acts as though ansi defaults are on and the column will end up allowing null unless otherwise specified. When altering an existing column this takes no account of its existing state so not null must be specified explicitly to retain that behaviour.

Upvotes: 4

Related Questions