Mike S's questions

我有一个包含单个选择查询的存储过程。查询中有几个代表公式或表达式的别名。

是否有可能提取其中的计算/公式/表达式？

在下面的例子中，我希望提取类似以下内容的内容：

“s * 2” 表示 ScoreTimesTwo

“s * 5” 表示 ScoreTimesFive

ConditionalScore 的“CASE...”（整个 case 语句）

另外，我以这样的方式编写了查询来涵盖这样一个事实：字段/表达式可以以不同的方式（等于或“AS”）分配给别名，并且表达式可以位于同一行或多行上。

我试图使用 sys.dm_exec_describe_first_result_set，但它似乎只能识别直接引用的源列（即，当它们不是公式的一部分时），而不能识别公式本身，而这正是我需要的。

/* Drop and create the test score table */
DROP TABLE IF EXISTS Test_ScoreTable
GO

CREATE TABLE Test_ScoreTable(s INT)
GO

/* Insert sample data into the score table */
INSERT INTO Test_ScoreTable(s)
SELECT t.s
FROM   (VALUES (100), (50), (10)) t(s)
GO

/* Drop and create test score stored procedure */
DROP PROCEDURE IF EXISTS Test_ScoreStoredProcedure
GO

CREATE PROCEDURE Test_ScoreStoredProcedure
AS

SELECT Score = t.s,
       ScoreTimesTwo = t.s * 2,
       t.s * 5 AS ScoreTimesFive,
       ConditionalScore = CASE t.s
                            WHEN 100 THEN 'Good'
                            WHEN 10 THEN 'Bad'
                            ELSE 'Eh...'
                          END
FROM   Test_ScoreTable t

GO

/* Attempt to extract the calculations and expressions */
SELECT s.[name], s.source_column, s.is_computed_column, *
FROM   sys.dm_exec_describe_first_result_set('Test_ScoreStoredProcedure', NULL, 1) s

这是我的理解，基于众多来源（包括这个来源：https ://techcommunity.microsoft.com/t5/sql-server/migrating-sap-workloads-to-sql-server-just-got-2-5x-faster /ba-p/384910 ) SQL Server 跟踪标志 715 应该等同于 TABLOCK 查询提示——不同之处在于 TF715 可以在会话和全局级别设置，而 TABLOCK 只能用作查询提示。

我的问题是，我在 SQL Servers 2016 和 2019 开发版上都以简单的恢复模型进行了测试，无论我使用哪种服务器，TABLOCK 和 TF715 都得到了不同的结果。TABLOCK 按预期工作，而 TF715 没有。具体来说，TABLOCK 告诉 SQL Server 为批量操作取出整个表锁，并最少记录操作。

考虑以下代码。这会将 10,000 行插入到堆中。

DROP TABLE IF EXISTS Test
GO

CREATE TABLE Test (t VARCHAR(100))
GO

INSERT INTO Test (t)
SELECT TOP (10000)
    x = ROW_NUMBER() OVER (ORDER BY s1.[object_id])
FROM       sys.columns AS s1
CROSS JOIN sys.columns AS s2

现在，如果您运行下一个代码块，您可以看到它记录了 10,000 行。

SELECT 
    [Fully Logged Rows] = COUNT_BIG(*) 
FROM sys.fn_dblog(NULL, NULL) AS FD
WHERE
    FD.Operation = N'LOP_INSERT_ROWS'
    AND FD.Context = N'LCX_HEAP'
    AND FD.AllocUnitName = N'dbo.Test';

好的，所以现在如果我们使用 TABLOCK 提示重新执行此操作，您可以看到它变成了最小记录操作（即，完全记录的行数 = 0）。

DROP TABLE IF EXISTS Test
GO

CREATE TABLE Test (t VARCHAR(100))
GO

INSERT INTO Test WITH (TABLOCK) (t)
SELECT TOP (10000)
    x = ROW_NUMBER() OVER (ORDER BY s1.[object_id])
FROM       sys.columns AS s1
CROSS JOIN sys.columns AS s2

--0 Fully Logged Rows
SELECT 
    [Fully Logged Rows] = COUNT_BIG(*) 
FROM sys.fn_dblog(NULL, NULL) AS FD
WHERE
    FD.Operation = N'LOP_INSERT_ROWS'
    AND FD.Context = N'LCX_HEAP'
    AND FD.AllocUnitName = N'dbo.Test';

但是，当我尝试启用 TF715（并取消 TABLOCK 提示）时，我仍然会得到 10,000 行完全记录的行，而我希望进行最少记录的操作。

--Enable TF715 on both a session and global level to cover all bases...
DBCC TRACEON(715)
GO
DBCC TRACEON(715, -1)
GO
DBCC TRACESTATUS
GO

DROP TABLE IF EXISTS Test
GO

CREATE TABLE Test (t VARCHAR(100))
GO

INSERT INTO Test (t)
SELECT TOP (10000)
    x = ROW_NUMBER() OVER (ORDER BY s1.[object_id])
FROM       sys.columns AS s1
CROSS JOIN sys.columns AS s2

--10000 Fully Logged Rows
SELECT 
    [Fully Logged Rows] = COUNT_BIG(*) 
FROM sys.fn_dblog(NULL, NULL) AS FD
WHERE
    FD.Operation = N'LOP_INSERT_ROWS'
    AND FD.Context = N'LCX_HEAP'
    AND FD.AllocUnitName = N'dbo.Test';

DBCC TRACEOFF(715)
GO
DBCC TRACEOFF(715, -1)
GO
DBCC TRACESTATUS
GO

我在看什么？我不需要重新启动 SQL Server 来通过 DBCC 启用 TF，对吗？

先感谢您。

我有以下变量和 CASE 语句：

DECLARE @DATE_VALUE VARCHAR(20) = '4/29/2021'
DECLARE @CONVERT_CODE VARCHAR(1) = 'I'

SELECT CASE @CONVERT_CODE WHEN 'I' THEN DATEDIFF(day, '04/21/2021', @DATE_VALUE)
       END AS ConvertedValue

它没有什么特别之处。它按照我的预期返回 8 (int)。

但是，只要我在 CASE 语句中添加一个返回日期时间数据类型的条件，我就不再得到 8 (int)，尽管继续使用“I”作为 @CONVERT_CODE 值。

DECLARE @DATE_VALUE VARCHAR(20) = '4/29/2021'
DECLARE @CONVERT_CODE VARCHAR(1) = 'I'

SELECT CASE @CONVERT_CODE WHEN 'I' THEN DATEDIFF(day, '04/21/2021', @DATE_VALUE)
                          WHEN 'D' THEN GETDATE()
       END AS ConvertedValue

相反，我得到“1900-01-09 00:00:00.000”的日期时间值

我认为“D”根本不应该被评估，因为在 SQL Server 中，一旦条件评估为真（在本例中为“I”），它应该脱离 CASE 语句——所以我不确定为什么“D”的数据类型会影响整体返回数据类型。此外，如果我颠倒条件的顺序并仍然使用“I”也没关系：

DECLARE @DATE_VALUE VARCHAR(20) = '4/29/2021'
DECLARE @CONVERT_CODE VARCHAR(1) = 'I'

SELECT CASE @CONVERT_CODE WHEN 'D' THEN GETDATE()
                          WHEN 'I' THEN DATEDIFF(day, '04/21/2021', @DATE_VALUE)
       END AS ConvertedValue

我错过了什么吗？

我在数据库中有数百个具有相同结构的表：

一些Id、Pos、 不同数量的其他字段

因此，例如，一个表可能如下所示：

PersonId, Pos, Hobby, Degree
12345, 1, Basketball, Bachelor of Science
12345, 2, Baseball, Master of Science
12345, 3, Boxing, NULL
12345, 4, Tennis, NULL
22222, 1, Golf, Bachelor of Science
22222, 2, NULL, Master of Science
22222, 3, NULL, Doctorate

我想汇总每列 3-N 的值。所以这会变成：

12345, "Basketball, Baseball, Boxing, Tennis",  "Bachelor of Science, Master of Science"
22222, "Golf", "Bachelor of Science, Master of Science, Doctorate"

另一个表可能如下所示：

ClientId, Pos, Location, Language, CommunicationType
33333, 1, North Carolina, English, Phone
33333, 2, New York, Spanish, Email
33333, 3, NULL, Portuguese, NULL
44444, 1, California, English, Phone
44444, 2, NULL, NULL, Email

变成这样：

33333, "North Carolina, New York", "English, Spanish, Portugeue", "Phone, Email"
44444, "California", "English", "Phone, Email"

我想做的是创建一个 TVF，我可以在其中指定表名并让函数返回其字段。理想情况下，就像我刚刚在上面演示的那样卷起来。

Solomon Rutzky 提供了一个解决方案（SQL Server：将表名作为参数传递给表值函数），他展示了如何使用 XML 和 CASE 语句在 TVF 中接受表名。

这是一个改编：

DECLARE @TableName sysname = 'objects'
/*
DECLARE @TableName sysname = 'columns'
DECLARE @TableName sysname = 'indexes'
*/
       
SELECT tab.BaseData.value(N'/row[1]/@name', N'VARCHAR(128)') AS [name],
       tab.BaseData.value(N'/row[1]/@object_id', N'BIGINT') AS [object_id],
       *
FROM (
    SELECT CASE @TableName
             WHEN N'objects' THEN (SELECT * FROM master.sys.tables FOR XML RAW, TYPE)
             WHEN N'indexes' THEN (SELECT * FROM master.sys.indexes FOR XML RAW, TYPE)
             WHEN N'columns' THEN (SELECT * FROM master.sys.columns FOR XML RAW, TYPE)
           END AS [BaseData]
     ) tab;

如果我要创建一个怪物 CASE 语句并考虑所有可能的传入表名，有没有办法按序号位置（而不是像我上面那样的名称）引用列？更好的是，也可以汇总并划定它们的值（这是我的最终目标）？

先感谢您！

此外，这里是创建我的两个示例表的 DDL：

CREATE TABLE [dbo].[Person](
[PersonId] [int] NULL,
[Pos] [int] NULL,
[Hobby] [varchar](100) NULL,
[Degree] [varchar](50) NULL
)
GO
INSERT [dbo].[Person] ([PersonId], [Pos], [Hobby], [Degree]) VALUES (12345, 1, N'Basketball', N'Bachelor of Science')
GO
INSERT [dbo].[Person] ([PersonId], [Pos], [Hobby], [Degree]) VALUES (12345, 2, N'Baseball', N'Master of Science')
GO
INSERT [dbo].[Person] ([PersonId], [Pos], [Hobby], [Degree]) VALUES (12345, 3, N'Boxing', NULL)
GO
INSERT [dbo].[Person] ([PersonId], [Pos], [Hobby], [Degree]) VALUES (12345, 4, N'Tennis', NULL)
GO
INSERT [dbo].[Person] ([PersonId], [Pos], [Hobby], [Degree]) VALUES (22222, 1, N'Golf', N'Bachelor of Science')
GO
INSERT [dbo].[Person] ([PersonId], [Pos], [Hobby], [Degree]) VALUES (22222, 2, NULL, N'Master of Science')
GO
INSERT [dbo].[Person] ([PersonId], [Pos], [Hobby], [Degree]) VALUES (22222, 3, NULL, N'Doctorate')
GO

CREATE TABLE [dbo].[Client](
[ClientId] [int] NULL,
[Pos] [int] NULL,
[Location] [varchar](100) NULL,
[Language] [varchar](50) NULL,
[CommunicationType] [varchar](50) NULL
)
GO
INSERT [dbo].[Client] ([ClientId], [Pos], [Location], [Language], [CommunicationType]) VALUES (33333, 1, N'North Carolina', N'English', N'Phone')
GO
INSERT [dbo].[Client] ([ClientId], [Pos], [Location], [Language], [CommunicationType]) VALUES (33333, 2, N'New York', N'Spanish', N'Email')
GO
INSERT [dbo].[Client] ([ClientId], [Pos], [Location], [Language], [CommunicationType]) VALUES (33333, 3, NULL, N'Portuguese', NULL)
GO
INSERT [dbo].[Client] ([ClientId], [Pos], [Location], [Language], [CommunicationType]) VALUES (44444, 1, N'California', N'English', N'Phone')
GO
INSERT [dbo].[Client] ([ClientId], [Pos], [Location], [Language], [CommunicationType]) VALUES (44444, 2, NULL, NULL, N'Email')
GO

SELECT * FROM Person;
SELECT * FROM Client;

我知道有很多关于如何在 SQL Server 中拆分字符串的示例。但是，它们通常用于单个分隔字符串。在不使用函数（SQL Server 2016）的情况下拆分多个分隔字符串的最佳方法是什么？

这是我开始的内容：

GroupId     Val1      Val2
----------- --------- ------
G1           1,2,3     a,b,c
G2           4,5,6     d,e,f
G3           1,2,3,4   w,x,y,z

这就是我想要的结果：

GroupId Val1    Val2
------- ------- -------
G1      1       a
G1      2       b
G1      3       c
G2      4       d
G2      5       e
G2      6       f
G3      1       w
G3      2       x
G3      3       y      
G3      4       z      

根据我读过的所有内容，我认为我可能需要使用 XML 方法，因为它允许我指定顺序，但我无法找到拆分多个列的这种方法的示例。

下面是创建源表的 T-SQL：

DROP TABLE IF EXISTS #TestData;

CREATE TABLE #TestData(GroupId VARCHAR(10),
                       Val1 VARCHAR(100),
                       Val2 VARCHAR(100)
                       );
INSERT INTO #TestData
VALUES ('G1', '1,2,3', 'a,b,c'),
       ('G2', '4,5,6', 'd,e,f'),
       ('G3', '1,2,3,4', 'w,x,y,z');

SELECT *
FROM #TestData;

先感谢您！

我有以下两个表，表 A 和表 B。

我想要做的是返回一个结果集，其中：

1. 如果表 B 中的开始位置（仅）在表 A 中任何行的开始和结束位置之间，那么我不希望表 B 中的该行出现在结果集中。

2. 如果表 A 中的任何开始和结束位置位于表 B 中任何行的任何开始和结束位置之间，那么我不希望表 A 中的该行。

这是表A：

这是表B：

这是我想要的结果集。您可以看到表 A 中的 197 和 212 位于表 B 中具有 195 和 214 的行之间，因此表 A 中的行不在结果集中。您还可以看到，表 B 中的起始位置 221 位于表 A 中的范围 (218, 235) 之间，因此表 B 中的行也不在结果集中。

总之，我想排除在哪里：

• 表 A 的开始位置和结束位置在表 B 的任何开始位置和结束位置之间。
• 表 B 的开始位置介于表 A 的任何开始位置和结束位置之间。

此外，表中有多个 RID... 我认为（并且仍然认为）我可以使用 FULL 连接（或者可能使用 APPLY）来完成这项工作，但我有点卡住了，这就是为什么我求大家帮忙！

这是生成两个表的 T-SQL：

CREATE TABLE #TableA(
    RID int NULL,
    StartPos int NULL,
    EndPos int NULL
)

INSERT #TableA (RID, StartPos, EndPos) VALUES (7, 45, 77)
INSERT #TableA (RID, StartPos, EndPos) VALUES (7, 118, 130)
INSERT #TableA (RID, StartPos, EndPos) VALUES (7, 197, 212)
INSERT #TableA (RID, StartPos, EndPos) VALUES (7, 218, 235)


CREATE TABLE #TableB(
    RID int NULL,
    StartPos int NULL,
    EndPos int NULL
)

INSERT #TableB (RID, StartPos, EndPos) VALUES (7, 83, 87)
INSERT #TableB (RID, StartPos, EndPos) VALUES (7, 121, 132)
INSERT #TableB (RID, StartPos, EndPos) VALUES (7, 175, 179)
INSERT #TableB (RID, StartPos, EndPos) VALUES (7, 183, 191)
INSERT #TableB (RID, StartPos, EndPos) VALUES (7, 195, 214)
INSERT #TableB (RID, StartPos, EndPos) VALUES (7, 221, 237)


SELECT *
FROM #TableA

SELECT *
FROM #TableB

我有一个如下所示的表：

PersonId   EyeColor    HairColor    FavoriteTVShow      FavoriteMovie    FavoriteFood    BirthInfo
---------- ----------- ------------ ------------------- ---------------- --------------- ------------------
A123       Brown       Black        The Price Is Right  NULL             Pizza           New York City
A444       NULL        Brown        Friends             Elf              Steak           Seattle

我想要的结果集是这样的：

PersonId ColorInfo FavoriteInfo       BirthInfo
-------- --------- ------------------ -------------
A123     Brown     The Price Is Right New York City
A123     Black     Pizza              NULL
A444     Brown     Friends            Seattle
A444     NULL      Elf                NULL
A444     NULL      Steak              NULL

因此，对于每个 PersonId，每组列（2 个 ColorInfo 列、3 个 FavoriteInfo 列和 1 个 BirthInfo 列）被转置为行。我尝试了 and 的变体CROSS APPLY，UNPIVOT但我似乎无法做到恰到好处。我知道这是一个奇怪的要求，但谁能想到一个好方法来做到这一点？

先感谢您！

这是 T-SQL：

--Create the table
CREATE TABLE #test (
PersonId VARCHAR(10),
EyeColor VARCHAR(20),
HairColor VARCHAR(20),
FavoriteTVShow VARCHAR(30),
FavoriteMovie VARCHAR(30),
FavoriteFood VARCHAR(30),
BirthInfo VARCHAR(30)
)

--Populate the table
INSERT INTO #test
VALUES
('A123', 'Brown', 'Black', 'The Price Is Right', NULL, 'Pizza', 'New York City'),
('A444', NULL, 'Brown', 'Friends', 'Elf', 'Steak', 'Seattle')

--See the original result set.
SELECT *
FROM #test

--My desired result set
SELECT *
FROM ( VALUES ('A123', 'Brown', 'The Price Is Right', 'New York City'),
              ('A123', 'Black', 'Pizza', NULL),
              ('A444', 'Brown', 'Friends', 'Seattle'),
              ('A444', NULL, 'Elf', NULL),
              ('A444', NULL, 'Steak', NULL) ) t (PersonId, ColorInfo, FavoriteInfo, BirthInfo)

我有一种情况，对于我的表中的每个 ID，都有多个字段，每个字段都包含一个逗号分隔的字符串。字符串不一定具有彼此相同数量的元素。数据的格式来自另一个团队，我无法更改。

如何使用 SQL Server 2016 将这些字符串拆分为行？如果可以避免，我宁愿不向数据库添加函数。

这是我开始的示例以及我希望实现的目标：

DECLARE @SourceTable TABLE (ID INT, String1 VARCHAR(100), 
  String2 VARCHAR(100), String3 VARCHAR(100))

INSERT INTO @SourceTable (ID, String1, String2, String3)
VALUES (1, 'a1,a2,a3', 'b1,b2,b3,b4,b5,b6', 'c1,c2'),
       (2, 'd1,d2', 'e1', 'f1,f2')

--Source
SELECT *
FROM @SourceTable

期望的输出：

ID  Position    S1_Transformed  S2_Transformed  S3_Transformed
--  --------    --------------  --------------  --------------
1   1           a1              b1              c1
1   2           a2              b2              c2
1   3           a3              b3  
1   4                           b4  
1   5                           b5  
1   6                           b6  
2   1           d1              e1              f1
2   2           d2                              f2

编辑：根据 Aaron 的评论更正了输出。