我正在编写一个小工具，为我生成一些SQL语句。语句如下所示：
SET IMPLICIT_TRANSACTIONS ON;
BEGIN TRANSACTION x;
ALTER TABLE schema.MyTable ADD MyNewColumn INT DEFAULT(-7777);
--ALTER TABLE schema.MyTable ADD CONSTRAINT CHK_MyTable_MyNewColumn CHECK(MyNewColumn IN(1, 2, 3, 4, 5));
EXEC sp_addextendedproperty @name = N'MS_Description', @value = 'texttexttext',
@level0type = N'Schema', @level0name = 'schema',
@level1type = N'Table', @level1name = 'MyTable',
@level2type = N'Column', @level2name = 'MyNewColumn';
INSERT INTO schema.SomeOtherTable VALUES(1, 2, 3);
-- Other statements....
--ROLLBACK TRANSACTION x;
--COMMIT TRANSACTION x;
表名和类似的东西来自工具界面。 带有ADD CONSTRAINT的行失败并invalid column 'MyNewColumn'...我认为这是因为未提交的交易...但是我需要，如果其他一些语句失败，则应删除列，约束和其他数据...
所以。。。有没有办法将新列ADD到现有表，并在单个语句中将命名CHECK CONSTRAINTADD到同一个表？或者您有其他想法如何解决这个问题？
问题不是由于未提交的事务，而是因为必须在执行之前编译整个批处理，并且 DDL 中引用的列尚不存在。
若要避免此错误，可以在单个语句中添加列和约束：
ALTER TABLE dbo.MyTable
ADD MyNewColumn INT DEFAULT(-7777)
,CONSTRAINT CHK_MyTable_MyNewColumn CHECK(MyNewColumn IN(1, 2, 3, 4, 5));
如果您的工具不支持此构造，则需要使用动态 SQL 在单独的批处理中或在同一批处理中执行语句，如下所示：
ALTER TABLE dbo.MyTable
ADD MyNewColumn INT DEFAULT(-7777);
EXECUTE(N'ALTER TABLE dbo.MyTable ADD CONSTRAINT CHK_MyTable_MyNewColumn CHECK(MyNewColumn IN(1, 2, 3, 4, 5));');
另外，我建议您删除SET IMPLICIT_TRANSACTIONS ON;因为脚本中已经有一个显式事务。否则，在COMMIT之后，您仍将有未提交的事务。
为我的评论添加更多上下文：">影响列(即INSERT到其中，添加一个扩展属性，等等)，它需要在不同的批次中。即使提交事务也不会改变这一点。在该批处理完成之前，您将无法对该列执行"操作"。DB<>小提琴">
我的说法在这里实际上是错误的。发生错误的原因是，添加列然后尝试插入(或添加CONSTRAINT)的批处理实际上失败。编译器分析 SQL，并看到您正在尝试INSERT(向其添加CONSTRAINT)当前不存在的列，因此失败。
举个例子：
CREATE TABLE dbo.MyTable (ID int);
GO
ALTER TABLE dbo.MyTable ADD MyColumn int DEFAULT (100);
INSERT INTO dbo.MyTable
VALUES(1,100);
此操作失败，并显示以下错误：
列名或提供的值的数量与表定义不匹配。
但是，如果命名列，则会收到不同的错误：
ALTER TABLE dbo.MyTable ADD MyColumn int DEFAULT (100);
INSERT INTO dbo.MyTable (ID, MyColumn)
VALUES(1,100)
无效的列名称"我的列"。
但是，对于 SPsp_addextendedproperty，对列是否存在的检查将推迟到执行 SP 之后;此时该列将存在。如果尝试以下操作，则不会收到错误：
CREATE TABLE dbo.MyTable (ID int);
GO
ALTER TABLE dbo.MyTable ADD MyColumn int DEFAULT (100);
EXEC sp_addextendedproperty @name = N'MS_Description', @value = 'texttexttext',
@level0type = N'Schema', @level0name = 'dbo',
@level1type = N'Table', @level1name = 'MyTable',
@level2type = N'Column', @level2name = 'MyColumn';
GO
INSERT INTO dbo.MyTable (ID, MyColumn)
VALUES(1,100)
GO
DROP TABLE dbo.MyTable;
至于你的CONSTRAINT，这会遇到与INSERT相同的问题。它需要位于单独的批处理中。
您可以执行此操作的一种方法是延迟添加CONSTRAINT的语句的编译，使用sp_executesql：
ALTER TABLE schema.MyTable ADD MyNewColumn INT DEFAULT(-7777);
EXEC sp_executesql N'ALTER TABLE schema.MyTable ADD CONSTRAINT CHK_MyTable_MyNewColumn CHECK(MyNewColumn IN(1, 2, 3, 4, 5));';