以编程方式指定char宽度和压缩类型(Specifying char width and compression type programatically)

以编程方式指定char宽度和压缩类型(Specifying char width and compression type programatically)

在SQL Server 2008中，请考虑该表

create table myTable (id int, filler char(X)) with (data_compression = Y)

其中X是不同大小的整数，Y是“row”，“page”或“none”。 在一个实验中，我想在T-SQL中以编程方式指定X和Y，这样我就可以测量大量（！）量的不同（X，Y）对的多个性能指标。

但是，不允许以下内容：

declare @mySize int = 100 create table myTable (id int, filler char(@mySize)) ...

然后我如何以编程方式指定字符宽度和压缩类型？

In SQL Server 2008, consider the table

create table myTable (id int, filler char(X)) with (data_compression = Y)

where X is an integer of varying size, and Y is either "row", "page" or "none". In an experiment, I would like to specify X and Y programatically in T-SQL, so that I can measure a number of performance metrics for a large (!) amount of different (X,Y) pairs.

However, the following is not allowed:

declare @mySize int = 100 create table myTable (id int, filler char(@mySize)) ...

How can I then specify the char width and compression type programatically?

最满意答案

您可以使用动态SQL，例如：

declare @sql varchar(max) set @sql = 'create table myTable (id int, filler char(' + cast(@X as varchar(12)) + ')) with (compression = ' + @Y + ')' exec (@sql)

You could use dynamic SQL, for example:

declare @sql varchar(max) set @sql = 'create table myTable (id int, filler char(' + cast(@X as varchar(12)) + ')) with (compression = ' + @Y + ')' exec (@sql)