更改列时快速删除并重新创建多个索引，视图，统计信息 [英] Quickly dropping and re-creating multiple indexes, views, statistics when altering a column

问题描述

我的项目表中有一列 StoreNumber，我想更改为 NOT NULL。我最近清理了所有旧数据，以确保没有空条目。但是，当我执行以下语句时，由于对各种视图，索引和统计信息的多个依赖性而失败

I have a column "StoreNumber" in my "Project" table which I want to change to be "NOT NULL". I recently sanitized all of the old data so that there are no null entries. However, when I execute the following statement it fails due to multiple dependencies to various views, indexes, and statistics

ALTER TABLE [Project]
    ALTER COLUMN StoreNumber VARCHAR(9) NOT NULL
GO

什么是以最快的方式删除所有这些视图/索引/统计信息，然后运行alter语句，然后再次重新创建所有视图/索引/统计信息？我知道我可以复制所有drop并逐个创建语句，但是我希望在单个查询中生成脚本。

What is the fastest way to drop all of these views/indexes/statistics then run the alter statement, and then recreate all of the views/indexes/statistics again? I know that I could copy out all of the drop and create statements one by one but I would prefer to generate the script in a single query.

为什么我使列更具限制性，所以SQL Server会在乎吗？数据不包含空值，并且我不会更改数据类型或列的大小。这种类型的更改将如何打破依赖视图，索引或统计数据？我确定没有合理的理由，但我想举一个例子。

On a side note, why does SQL Server care if I'm making the column more restrictive? The data does not contain nulls and I'm not altering the data type or the size of the columns. How would this type of change ever break a dependent view, index, or statistics? I'm sure there is sound reasoning that I'm not seeing but I would like an example.

推荐答案

以下内容将删除多个索引。请注意，最后一条语句不包含逗号。

The following will drop multiple indexes. Note that the final statement does not include the comma.

DROP INDEX [index1_1] ON [schema].[table1],
           [index1_2] ON [schema].[table1],
           [index2_1] ON [schema].[table2],
           [index3_1] ON [schema].[table3],
           ...n,
           [lastIndexToDrop] ON [schema].[tableName]

Drop View看起来像这样。请注意用分号终止该语句。

Drop View looks like this. Note the semicolon to terminate the statement.

DROP VIEW [schema].[view1], [schema].[view2];

目前，我只在应用程序中关注索引。为了快速重新创建索引，我将一个.sql文件读入代码并在ExecuteNonQuery调用中执行它。如果我有意见，我将采用相同的方法将文件读入命令以使用ExecuteNonQuery执行。

I am only concerned with Indexes in my application at this time. To quickly recreate the indexes, I am reading a .sql file into code and executing it in an ExecuteNonQuery call. If I had views to consider, I would follow the same method of reading from a file into a command to execute with ExecuteNonQuery.

https://msdn.microsoft.com/en-us/library/ms173492.aspx

https://msdn.microsoft.com /en-us/library/ms176118.aspx

这篇关于更改列时快速删除并重新创建多个索引，视图，统计信息的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！