Why does SQL Server Management Scripting ignores the version?

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.

Try to use Generate scripts... in the context menu on a database -> Tasks -> Generate scripts... and select "Script for Server Version" there.

I feel your pain. Thats why when we have to generate scripts for prod deployment it's best to generate them using the version-appropriate GUI. ie: Query Analyser for 2000, SSMS 2005, SSMS 2008. And keep an equivalent staging environment to test your scripts on.

Or have really flexible Prod DBAs that just read between the lines and fix your code for ya. :)