Change columns to not nullable if they're nullable

Question

I have a group of tables being updated/replaced by an outside process. I need to add a primary key to make my queries run faster. However, if the column I'm using is nullable, it obviously won't be able to be added to the key.

Is there a way to script this so that it will change the column if it's nullable, but leave it alone if it's not?

I know this is similar to how to test if a column definition allows nulls, but it's not quite the answer I need.

I have these 3 code statements, but only want them run if they're nullable.

ALTER TABLE [dbo].[AS_tblTBCOV] 
    ALTER COLUMN COV_SOC_NUM numeric(5,0) NOT NULL;

ALTER TABLE [dbo].[AS_tblTBCOV] 
    ALTER COLUMN COV_CLASS_NUM smallint NOT NULL;

ALTER TABLE [dbo].[AS_tblTBCOV] 
    ALTER COLUMN COV_EFF_DATE date NOT NULL;

When I add the keys I use something like this, so I'm looking for something similar with an If/Then functionality:

IF NOT EXISTS(SELECT 1 FROM sys.objects 
              WHERE type = 'PK' 
                AND parent_object_id = OBJECT_ID ('[dbo].[AS_tblTBCOV]'))
    ALTER TABLE [dbo].[AS_tblTBCOV] 
        ADD CONSTRAINT [PK_Soc_Class_Date] 
            PRIMARY KEY CLUSTERED (COV_SOC_NUM ASC, COV_CLASS_NUM ASC, COV_EFF_DATE ASC)

` I need to add a Primary Key to make my queries run faster` An index could do that to, but why would you have a table without a primary key ? There are more important reasons to have it then performance — GuidoG
– GuidoG, Commented Oct 21 at 14:36
I'm sorry but i don't think you thought this through exactly, if you don't have control over nullability of a column, you have no business changing it. Let's say the script that does what you want changes some columns to not nullable, but the process that populates them eventually tries to insert a NULL value, which then crashes which leads to issues later on. And if you do have control over the nullability, you should just be able to change to NOT NULL. As they say, if you have to ask, you can't afford it — siggemannen
– siggemannen, Commented Oct 21 at 17:43

Lukasz Szozda · Accepted Answer · 2025-10-21 13:53:48Z

2

INFORMATION_SCHEMA.COLUMNS can be used for testing column's nullability:

IF EXISTS (SELECT * 
          FROM INFORMATION_SCHEMA.COLUMNS 
          WHERE TABLE_SCHEMA = '...'
            AND TABLE_NAME = '...'
            AND COLUMN_NAME = '...'
            AND IS_NULLABLE = 'YES')
  ALTER TABLE [dbo].[AS_tblTBCOV] ALTER COLUMN COV_SOC_NUM numeric(5,0) NOT NULL;

answered Oct 21 at 13:53

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Aaron Bertrand · Accepted Answer · 2025-10-21 14:52:53Z

You can make this quite dynamic, where all you have to supply is the table name and the names of the candidate key columns. But you should also check to make sure if the column currently contains NULL values; otherwise, the ALTER will fail.

/* input */
DECLARE @table sysname       = N'AS_tblTBCOV',
        @cols  nvarchar(max) = N'COV_SOC_NUM,COV_CLASS_NUM,COV_EFF_DATE';


DECLARE @qt sysname = QUOTENAME(@table);
DECLARE @obj int = OBJECT_ID(N'dbo.' + @qt);
        
DECLARE @keycols table(col sysname, dt nvarchar(128), nullable bit); 

INSERT @keycols SELECT r.name, r.system_type_name, r.is_nullable
  FROM STRING_SPLIT(@cols, N',') AS s
 INNER JOIN sys.dm_exec_describe_first_result_set
       (N'SELECT ' + @cols + ' FROM dbo.' + @qt, NULL, 0) AS r
    ON r.name COLLATE DATABASE_DEFAULT = s.value;

DECLARE @sql nvarchar(max) = N'DECLARE @go bit = 1';

SELECT @sql += STRING_AGG(CONVERT(nvarchar(max), 
       N'IF EXISTS (SELECT 1 FROM dbo.' + @qt + ' 
       WHERE ' + QUOTENAME(col) + ' IS NULL)
       BEGIN 
         SET @go = 0;
         RAISERROR(''' + col + ' contains NULLs'', 0, 1) WITH NOWAIT;
       END'+ char(13) + char(10)), '')
  FROM @keycols
 WHERE nullable = 1;

   SET @sql += N'IF @go = 1
   BEGIN
   ';

SELECT @sql += STRING_AGG(CONVERT(nvarchar(max), 
       N'  ALTER TABLE dbo.' + @qt 
       + ' ALTER COLUMN ' + QUOTENAME(col) + ' ' + dt) + N' NOT NULL;',   
          char(13) + char(10) + char(9))
  FROM @keycols
 WHERE nullable = 1;

SET @sql += N'
    END';


PRINT @sql;
--EXEC sys.sp_executesql @sql;

Seems quite messy but, in your case, it produces the much more tedious and repetitive:

DECLARE @go bit = 1

IF EXISTS (SELECT 1 FROM dbo.[AS_tblTBCOV] 
          WHERE [COV_CLASS_NUM] IS NULL)
          BEGIN 
            SET @go = 0;
            RAISERROR('COV_CLASS_NUM contains NULLs', 0, 1) WITH NOWAIT;
          END
IF EXISTS (SELECT 1 FROM dbo.[AS_tblTBCOV] 
          WHERE [COV_EFF_DATE] IS NULL)
          BEGIN 
            SET @go = 0;
            RAISERROR('COV_EFF_DATE contains NULLs', 0, 1) WITH NOWAIT;
          END
IF EXISTS (SELECT 1 FROM dbo.[AS_tblTBCOV] 
          WHERE [COV_SOC_NUM] IS NULL)
          BEGIN 
            SET @go = 0;
            RAISERROR('COV_SOC_NUM contains NULLs', 0, 1) WITH NOWAIT;
          END

IF @go = 1
BEGIN
      ALTER TABLE dbo.[AS_tblTBCOV] ALTER COLUMN [COV_CLASS_NUM] smallint NOT NULL;
      ALTER TABLE dbo.[AS_tblTBCOV] ALTER COLUMN [COV_EFF_DATE] date NOT NULL;
      ALTER TABLE dbo.[AS_tblTBCOV] ALTER COLUMN [COV_SOC_NUM] numeric(5,0) NOT NULL;
END

You write that constructor code once and it is reusable for any table / column combo.

(Keep in mind that changing a column's nullability can be a size-of-data operation and not simply a metadata change. Also that adding a primary key won't necessarily / broadly make queries more efficient - it depends on the queries and on whether the primary key is clustered. THat just happens to be the default.)

Collectives™ on Stack Overflow

Change columns to not nullable if they're nullable

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related