How Do I Set The Allow Nulls Property In Table Designer Of Ssms To Be Always False?

In SQL Server Management Studio when creating a New Table or adding columns to an existing table through the Table Designer, there are 3 columns presented: Column Name Data Type A

Solution 1:

If you are fine with manipulating the registry key, here is an option to force the SSMS to always assign the value false to Allow Null property.

NOTE: Please be careful while altering registry keys.

1. Type regedit in the Windows Start --> Run command to open the Registry Editor.

2. Navigate to HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL Server\100\Tools\Shell\DataProject You might need to change the SQL Server version accordingly. I am using SQL Server 2008 R2 Express and hence the version 100.

3. Under the above mentioned registry path, look for the key name SSVDefaultAllowNull.

4. Right-click on the key and select Modify option. Change the value from 1 to 0. Default value is 1, which represents True.

5. If you are already running SQL Server Management Studio, restart it. Thanks to @Andriy M for pointing out this step.

Hope that helps.

Solution 2:

Honestly, I am not exactly sure whether the default value for Allow Nulls is configurable or not. I expect it is not. In fact, I hope it will not be configurable, as it might be confusing at some times. Here's my reasoning.

Defaulting Allow Nulls to True seems to me consistent with defaulting the absence of NULL/NOT NULL in a column declaration of a DDL statement to NULL. That is, whether I omit that bit of column definition when using a CREATE TABLE statement or I don't pay attention to it when using a dialogue mode of some tool like SSMS, I would expect the outcome to be the same.

Since the absence of NULL or NOT NULL have been interpreted as NULL by many people for quite some time already, I don't think it would make much sense to change that meaning now. And consequently, the default value for the Allow Nulls option in a New Table dialogue shouldn't be changed either.

What might make sense, though, is a Default new columns to Deny Null switch (turned off initially, of course) in the dialogue box to change the default behaviour in this particular session of table editing.

Solution 3:

I imagine it is not configurable, just because of the fits that SSMS throws when you try to turn it off in edit table mode (assuming they use the same underlying code base).

However, you may be able to achieve what you want using SET ANSI_NULL_DFLT_ON. This will override default nullability of new columns (for the session).

Note the difference in the "Nullable" column in sp_help with the below query.

set ANSI_NULL_DFLT_ON ONcreatetable test (id int)
go
sp_help test
go
set ANSI_NULL_DFLT_ON OFF

droptable test
createtable test (id int)
go
sp_help test

I am assuming that you want to do this to enforce yourself or people you work with to default their columns as NOT NULL instead of NULL.