如何在 SQL Server 中使用变量指定文件组

发布于 2024-09-04 20:41:15 字数 656 浏览 9 评论 0原文

我想在 SQL Server 数据库升级期间更改表以添加约束。

该表通常在名为“MY_INDEX”的文件组上建立索引 - 但也可能在没有该文件组的数据库上。在这种情况下，我希望在“PRIMARY”文件组上完成索引。

我尝试使用以下代码来实现此目的：

DECLARE @fgName AS VARCHAR(10)

SET @fgName = CASE WHEN EXISTS(SELECT groupname
                                FROM sysfilegroups
                                WHERE groupname = 'MY_INDEX')
                    THEN QUOTENAME('MY_INDEX')
                    ELSE QUOTENAME('PRIMARY')
              END

ALTER TABLE [dbo].[mytable]
ADD CONSTRAINT [PK_mytable] PRIMARY KEY
(
    [myGuid] ASC
)
ON @fgName -- fails: 'incorrect syntax'

但是，最后一行失败，因为似乎无法通过变量指定文件组。

这可能吗？

原文

I want to alter a table to add a constraint during upgrade on a SQL Server database.

This table is normally indexed on a filegroup called 'MY_INDEX' - but may also be on a database without this filegroup. In this case I want the indexing to be done on the 'PRIMARY' filegroup.

I tried the following code to achieve this:

DECLARE @fgName AS VARCHAR(10)

SET @fgName = CASE WHEN EXISTS(SELECT groupname
                                FROM sysfilegroups
                                WHERE groupname = 'MY_INDEX')
                    THEN QUOTENAME('MY_INDEX')
                    ELSE QUOTENAME('PRIMARY')
              END

ALTER TABLE [dbo].[mytable]
ADD CONSTRAINT [PK_mytable] PRIMARY KEY
(
    [myGuid] ASC
)
ON @fgName -- fails: 'incorrect syntax'

However, the last line fails as it appears a filegroup cannot be specified by variable.

Is this possible?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

把人绕傻吧 2024-09-11 20:41:15

我发现动态 sql 在 DDL 语句中传递变量时有效。

尝试这样的事情：

DECLARE @fgName AS VARCHAR(10) 

SET @fgName = CASE WHEN EXISTS(SELECT groupname 
                                FROM sysfilegroups 
                                WHERE groupname = 'MY_INDEX') 
                    THEN QUOTENAME('MY_INDEX') 
                    ELSE QUOTENAME('PRIMARY') 
              END 

DECLARE @sql as varchar(1024)

SET @sql = 'ALTER TABLE [dbo].[mytable] ADD CONSTRAINT [PK_mytable] PRIMARY KEY ( 
    [myGuid] ASC ) ON ' + @fgName

EXEC(@sql)

我希望有帮助......

I've found that dynamic sql works when passing variables in DDL statements.

Try something like this:

DECLARE @fgName AS VARCHAR(10) 

SET @fgName = CASE WHEN EXISTS(SELECT groupname 
                                FROM sysfilegroups 
                                WHERE groupname = 'MY_INDEX') 
                    THEN QUOTENAME('MY_INDEX') 
                    ELSE QUOTENAME('PRIMARY') 
              END 

DECLARE @sql as varchar(1024)

SET @sql = 'ALTER TABLE [dbo].[mytable] ADD CONSTRAINT [PK_mytable] PRIMARY KEY ( 
    [myGuid] ASC ) ON ' + @fgName

EXEC(@sql)

I hope that helps....

回复收藏 0 原文

新人笑 2024-09-11 20:41:15

不幸的是，我相信如果 SQL Server 返回不正确的语法，可能没有办法做到这一点。

您需要将文件组名称指定为字符串文字。

您可能只需要重写脚本，如下所示：

IF EXISTS(SELECT groupname FROM sysfilegroups WHERE groupname = 'MY_INDEX')
     ALTER TABLE [dbo].[mytable]
       ADD CONSTRAINT [PK_mytable] 
       PRIMARY KEY([myGuid] ASC) ON 'MY_INDEX'
ELSE
     ALTER TABLE [dbo].[mytable]
       ADD CONSTRAINT [PK_mytable] 
       PRIMARY KEY([myGuid] ASC) ON 'PRIMARY'

I would believe that if SQL Server returns an incorrect syntax, there's probably no way to do this, unfortunately.

You will need to specify your filegroup names as string literals.

You will probably just have to rewrite your script to be something like:

IF EXISTS(SELECT groupname FROM sysfilegroups WHERE groupname = 'MY_INDEX')
     ALTER TABLE [dbo].[mytable]
       ADD CONSTRAINT [PK_mytable] 
       PRIMARY KEY([myGuid] ASC) ON 'MY_INDEX'
ELSE
     ALTER TABLE [dbo].[mytable]
       ADD CONSTRAINT [PK_mytable] 
       PRIMARY KEY([myGuid] ASC) ON 'PRIMARY'

回复收藏 0 原文