I need to increase the size of a field in a table from VARCHAR(100) to VARCHAR(255). For different reasons, this causes the table to be dropped and recreated (along with relationships, FKs, etc). That is acceptable however, I’m performing this change in a SQL2008R2 (express) (or 2008 Express, same result), but the script doesn’t work in SQL2000 (and has to), despite me setting the sql2000 scripting compat.
After doing the above, I go to the table, Design it, change the (100) to (255) and click Generate Change Script.
The result contains some of the following that will not execute under SQL 2000:
BEGIN TRANSACTION
GO
ALTER TABLE dbo.mytable
DROP CONSTRAINT FK_mytable_othertable
GO
ALTER TABLE dbo.othertable SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
The SET (LOCK_ESCALATION) doesn’t work under SQL 2000.
Other SQL2005/8 > things I see are:
WITH (HOLDLOCK TABLOCKX)
and
ALTER TABLE dbo.mytable ADD CONSTRAINT
PK_mytable PRIMARY KEY CLUSTERED
(
idmytable
) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
All that “WITH” thing doesn’t belong on SQL 2000.
Why does SQL Management Studio scripts for (in)compatibility? What does that “option” do then?
EDIT: I’ve filed a bug at Microsoft Connect until someone proves me wrong :) You can find it here.