SSMS + ALTER TABLE

Jag använder SQL Server Management Studio dagligen för att utföra diverse saker i de databasmiljöer jag jobbar i. Nästan alltid går detta smärtfritt och den gör som jag förväntar mig att den skall göra.
Ibland blir dock resultatet inte helt till belåtenhet….som tex när man vill ändra en befintlig tabell.

Låt oss titta på ett exempel.
Vi skapar en ny tabell:

CREATE TABLE test( id int identity(1,1), col varchar(50));

Antag att vi nu vill ändra på kolumnlängden på “col” till VARCHAR(100). Om vi nu beslutar oss för att göra detta i Management Studio så öppnar vi tabellen i design läge, ändrar kolumnlängden och skriptar ut förändringen för att se vad Management Studio egentligen hittar på bakom kulisserna.

Följande skript uppenbarar sig:

/* To prevent any potential data loss issues, you should review this script in detail before running it outside the context of the database designer.*/

BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT

BEGIN TRANSACTION
GO
CREATE TABLE dbo.Tmp_test  (  id int NOT NULL IDENTITY (1, 1),  col varchar(100) NULL  )  ON [PRIMARY]
GO
ALTER TABLE dbo.Tmp_test SET (LOCK_ESCALATION = TABLE)
GO
SET IDENTITY_INSERT dbo.Tmp_test ON
GO
IF EXISTS(SELECT * FROM dbo.test)  EXEC(‘INSERT INTO dbo.Tmp_test (id, col)   SELECT id, col FROM dbo.test WITH (HOLDLOCK TABLOCKX)’)
GO
SET IDENTITY_INSERT dbo.Tmp_test OFF
GO
DROP TABLE dbo.test
GO
EXECUTE sp_rename N’dbo.Tmp_test’, N’test’, ‘OBJECT’
GO
COMMIT

Vi kan se att Management Studio först skapar en temporär tabell enl den nya definitionen, kopierar över allt data, tar bort den gamla tabellen och döper till sist om den nya tabellen till rätt namn.
Detta känns onekligen som en omväg, om vi hade gjort detta med TSQL istället hade det blivit något enklare:

ALTER TABLE test ALTER COLUMN Col VARCHAR(100);

Har detta då någon betydelse? Ja, om tabellen vi vill ändra på är stor så kan det bli väldigt mycket data som måste flyttas runt eller om tabellen vi vill ändra tex är replikerad så kommer Managment Studios variant inte att fungera eftersom det faktiskt droppar tabellen medan ALTER TABLE… fungerar alldeles utmärkt mot en replikerad tabell.

Jag vet att detta inte är något nytt beteende hos Management Studio men icke desto mindre kan det vara något att ha i åtanke innan man börjar ändra i sina tabeller via designläget.