主页 / dba / 问题 / 209488
Accepted
Vorster
Asked: 2018-06-13 21:30:00 +0800 CST

在 SQL Server 2008 R2 中重写查询以不使用 CONCAT 函数？

  • 772

我知道 CONCAT 函数在2012之前的 SQL Server 中不存在。我将如何优化我的代码以不使用 CONCAT 函数？

         SELECT  dbname = DB_NAME(),
                  file_name = name, 
                  TotalMB = CONVERT(decimal(12,1),size/128.0),
                  UsedMB = CONVERT(decimal(12,1),FILEPROPERTY(name,'SpaceUsed')/128.0),
                  FreeMB = CONVERT(decimal(12,1),(size - FILEPROPERTY(name,'SpaceUsed'))/128.0),
                  Command = CONCAT('USE MASTER; ALTER DATABASE ', DB_NAME(), '; MODIFY FILE (name = ',
                        [name], ', size = ', 
                        convert(int,round(1.15 * FILEPROPERTY(name,'SpaceUsed')/128,-1)), 'MB)')
           FROM sys.database_files WITH (NOLOCK)
           WHERE type_desc = 'ROWS'
           ORDER BY file_id;
sql-server sql-server-2008

1 个回答

  1. Best Answer

    只需使用+运算符即可在 SQL Server 中连接字符串。因此，为避免使用CONCAT，您的代码可以重写为：

    SELECT  dbname = DB_NAME(),
                 file_name = name, 
                 TotalMB = CONVERT(decimal(12,1),size/128.0),
                 UsedMB = CONVERT(decimal(12,1),FILEPROPERTY(name,'SpaceUsed')/128.0),
                 FreeMB = CONVERT(decimal(12,1),(size - FILEPROPERTY(name,'SpaceUsed'))/128.0),
                 Command = 'USE MASTER; ALTER DATABASE ' + DB_NAME() + '; MODIFY FILE (name = ' + 
                    [name] + ', size = ' + convert(int,round(1.15 * FILEPROPERTY(name,'SpaceUsed')/128,-1)) + 'MB)'
FROM sys.database_files WITH (NOLOCK)
WHERE type_desc = 'ROWS'
ORDER BY file_id;
    • 2

相关问题