mardi 12 janvier 2016

Why script doesn't work in SQL Server (2005, 2014, ...) the first time

I created a table Table1 in separate connection

CREATE TABLE [dbo].[Table1]
(
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Column1] [varchar](50) NULL,

    CONSTRAINT [PK_Table1] 
       PRIMARY KEY CLUSTERED ([Id] ASC)
           WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
                 IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
                 ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

then I add rows in Column1 in a same connection

INSERT INTO [dbo].[Table9]([Column2])VALUES('abc')

After that, in other connection I try to add Column2 and update it

alter table Table1 
   add Column2 VARCHAR(20) NULL

update Table1 
set Column2 = 'a'

but get an error Invalid column name 'Column2'.

then trying add GO keyword

alter table Table1 add Column2 VARCHAR(20) NULL
go
update Table1 set Column2='a'

and get a success message (1 row(s) affected)

when I drop column Column2 and trying execute non workable script block from point 2 without keyword GO

alter table Table1 add Column2 VARCHAR(20) NULL
update Table1 set Column2='a'

I get a success message (1 row(s) affected)

Why ?

Aucun commentaire:

Enregistrer un commentaire