Can scripts be run with database is in use?

Question

Can this script be run while the database is in use? Any down side?

Jeff

USE [SecretDatabase]
GO

UPDATE client
SET ns1_sign = 0, ns2_sign = 0


ALTER TABLE [dbo].[client] ADD  CONSTRAINT [DF_Client_NS1_sign]  DEFAULT ((0)) FOR [ns1_sign] 
GO
ALTER TABLE  [client] ALTER COLUMN [ns1_sign] BIT NOT NULL
GO

ALTER TABLE [dbo].[client] ADD  CONSTRAINT [DF_Client_NS2_sign]  DEFAULT ((0)) FOR [ns2_sign] 
GO
ALTER TABLE  [client] ALTER COLUMN [ns2_sign] BIT NOT NULL
GO

Accepted Answer

Without a transaction you will acquire and release locks multiple times on the table. Better to start a transaction, get an exclusive lock on the table, then do all the work.

set xact_abort on
begin transaction

UPDATE client with (tablockx)
SET ns1_sign = coalesce(ns1_sign,0), 
    ns2_sign = coalesce(ns2_sign,0)
where  ns1_sign is null or ns2_sign is null

ALTER TABLE  [client] ALTER COLUMN [ns1_sign] BIT NOT NULL
ALTER TABLE  [client] ALTER COLUMN [ns2_sign] BIT NOT NULL

ALTER TABLE [dbo].[client] ADD  CONSTRAINT [DF_Client_NS1_sign]  DEFAULT ((0)) FOR [ns1_sign] 
ALTER TABLE [dbo].[client] ADD  CONSTRAINT [DF_Client_NS2_sign]  DEFAULT ((0)) FOR [ns2_sign] 

commit transaction

Answer

Thank you very much.

Answer

You need two UPDATE statements to update the values of the columns [NS1_Sign] and [NS2_Sign], respectively. See the following code:

/*  
CREATE TABLE [dbo].[Client] (  
	[Id] int IDENTITY(1, 1) NOT NULL,  
	[NS1_Sign] bit,  
	[NS2_Sign] bit  
);  
  
INSERT INTO [dbo].[Client]([NS1_Sign], [NS2_Sign]) VALUES  
(1, 1), (1, NULL), (NULL, 1), (NULL, NULL);  
*/

Here is the original data:

SET XACT_ABORT ON;  
BEGIN TRANSACTION;  
  
UPDATE [dbo].[Client] SET [NS1_Sign] = 0 WHERE [NS1_Sign] IS NULL;  
UPDATE [dbo].[Client] SET [NS2_Sign] = 0 WHERE [NS2_Sign] IS NULL;  
  
-- Incorrect  
--UPDATE [dbo].[Client] SET [NS1_Sign] = 0, [NS2_Sign] = 0 WHERE [NS1_Sign] IS NULL OR [NS2_Sign] IS NULL;  
  
ALTER TABLE [dbo].[Client] ALTER COLUMN [NS1_Sign] bit NOT NULL;  
ALTER TABLE [dbo].[Client] ALTER COLUMN [NS2_Sign] bit NOT NULL;  
  
ALTER TABLE [dbo].[Client] ADD CONSTRAINT [DF_Client_NS1_Sign] DEFAULT (0) FOR [NS1_Sign];  
ALTER TABLE [dbo].[Client] ADD CONSTRAINT [DF_Client_NS2_Sign] DEFAULT (0) FOR [NS2_Sign];  
  
COMMIT TRANSACTION;

The output should be

But if you use one UPDATE statement to update both columns, you would get the incorrect data:

Answer

The WHERE clause came from David Browne's post above. My code did not have this line in it.

where  ns1_sign is null or ns2_sign is null

Thank you for letting me know and I have created a second UPDATE line.

Jeff

Share via

Can scripts be run with database is in use?

3 additional answers

Your answer