How add defaults values to existing rows while using GETDATE() and SUSER_SNAME() for newly added data in SQL Server 2019

Advertisements

I have a table with data. I need to add two new columns author and time_ct. I ran this query for adding the data. My expectation is to add 0000-00-00 00:00:00.0000000 and not_set to existing rows.

ALTER TABLE [tpsone].[ss_auth]
    ADD [time_ct] DATETIME2 (7) NOT NULL 
        CONSTRAINT DF_ss_authi DEFAULT '0000-00-00 00:00:00.0000000';

ALTER TABLE [tpsone].[ss_auth]
    ADD [author] VARCHAR (200) NOT NULL 
        CONSTRAINT DF_ss_authii DEFAULT 'not_set';

The I added these lines of T-SQL, to use GETDATE() and SUSER_SNAME() for new rows:

ALTER TABLE [ss_auth]
    ADD CONSTRAINT [DF_ss_auth5] DEFAULT GETDATE() FOR [time_ct];
ALTER TABLE [ss_auth]
    ADD CONSTRAINT [DF_pss_auth6] DEFAULT SUSER_SNAME() FOR [author];

Once I run these queries, I am getting this error:

Column already has a DEFAULT bound to it.

When I run this query it executes without error:

ALTER TABLE [tpsone].[ss_auth]
    ADD [time_ct] DATETIME2 (7) NOT NULL 
        CONSTRAINT DF_ss_authi DEFAULT GETDATE();
ALTER TABLE [tpsone].[ss_auth]
    ADD [author] VARCHAR (200) NOT NULL 
        CONSTRAINT DF_ss_authii DEFAULT SUSER_SNAME();

I need to know how can I add "0000-00-00 00:00:00.0000000" and "not_set" for existing rows?

>Solution :

If you are on Enterprise (or developer) then use a DEFAULT CONSTRAINT at the time you create the column will likely be the better choice (especially if you’re unlikely to need to UPDATE the existing rows in the future), as this change is performed online (see this answer from Solomon Rutzky for some discussion). Then, after you ALTER the table to ADD the new columns, DROP the constraint and then add the new one:

--Add new not-NULL columns with default value
ALTER TABLE [tpsone].[ss_auth]
ADD [time_ct] datetime2(7) NOT NULL CONSTRAINT DF_ss_authi
                                    DEFAULT '0000-00-00 00:00:00.0000000';

ALTER TABLE [tpsone].[ss_auth]
ADD [author] varchar(200) NOT NULL CONSTRAINT DF_ss_authii
                                   DEFAULT 'not_set';
GO
--Drop named constraints for initial values
ALTER TABLE [tpsone].[ss_auth] DROP CONSTRAINT DF_ss_authi;
ALTER TABLE [tpsone].[ss_auth] DROP CONSTRAINT DF_ss_authii;
GO
--Add new default constraints
ALTER TABLE tpsone.[ss_auth]
ADD CONSTRAINT [DF_ss_auth5]
    DEFAULT GETDATE()FOR [time_ct];
ALTER TABLE tpsone.[ss_auth]
ADD CONSTRAINT [DF_pss_auth6]
    DEFAULT SUSER_SNAME()FOR [author];

If you are, however, going to be doing lots of UPDATEs against the existing rows, then using marc_s‘s solution may be the better option; as although it result in an "offline" process (locks the table), the cost of adding the values to all the rows is "paid up front". As such, it depends on your and the business’ needs.

Leave a ReplyCancel reply