Mike S's questions

Tenho um procedimento armazenado que contém uma única consulta select. Dentro da consulta, há alguns aliases que representam uma fórmula ou expressão.

É possível extrair o cálculo/fórmula/expressão para quem tem um?

No exemplo abaixo, estou tentando extrair algo como:

"s * 2" para ScoreTimesTwo

"s * 5" para ScoreTimesFive

"CASE..." (a declaração completa do caso) para ConditionalScore

Além disso, escrevi a consulta de forma a cobrir o fato de que campos/expressões podem ser atribuídos a aliases de diferentes maneiras (equals ou "AS") e as expressões podem estar na mesma linha ou em várias linhas.

Tentei usar sys.dm_exec_describe_first_result_set, mas parece que ele só identifica colunas de origem referenciadas diretamente (ou seja, quando elas não fazem parte de uma fórmula) e nunca a fórmula em si, que é o que preciso.

/* 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

É meu entendimento, com base em várias fontes (incluindo esta: https://techcommunity.microsoft.com/t5/sql-server/migrating-sap-workloads-to-sql-server-just-got-2-5x-faster /ba-p/384910 ) que o SQL Server Trace Flag 715 deve ser equivalente à dica de consulta TABLOCK - com a diferença de que TF715 pode ser definido nos níveis de sessão e global, enquanto TABLOCK só pode ser usado como uma dica de consulta.

Meu problema é que testei no SQL Servers 2016 e 2019 Development Editions no modelo de recuperação simples e estou obtendo resultados diferentes entre o TABLOCK e o TF715, independentemente de qual servidor eu uso. O TABLOCK está funcionando conforme o esperado e o TF715 não. Especificamente, TABLOCK diz ao SQL Server para remover um bloqueio de tabela inteiro para operações em massa e registrar minimamente a operação .

Considere o código a seguir. Isso insere 10.000 linhas em um heap.

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

Agora, se você executar este próximo bloco de código, poderá ver que ele registrou 10.000 linhas.

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';

Ok, agora se fizermos tudo de novo usando a dica TABLOCK, você verá que se torna uma operação minimamente registrada (ou seja, a contagem de linhas totalmente registrada = 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';

No entanto, quando tento habilitar o TF715 (e retirar a dica TABLOCK), ainda recebo 10.000 linhas totalmente registradas quando esperaria uma operação minimamente registrada.

--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

O que estou negligenciando? Eu não deveria precisar reiniciar o SQL Server para habilitar TFs por meio de DBCC, correto...?

Agradeço antecipadamente.

Eu tenho as seguintes variáveis ​​e instrução 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

Não há nada de especial nisso. Ele retorna 8 (int) como eu esperava.

No entanto, assim que adiciono à minha instrução CASE uma condicional que retorna um tipo de dados datetime, não recebo mais 8 (int), apesar de continuar usando "I" como o valor @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

Em vez disso, recebo o valor de data e hora de "1900-01-09 00:00:00.000"

Eu acho que "D" não deve ser avaliado porque, no SQL Server, uma vez que uma condição é avaliada como verdadeira ("I" neste caso), ela deve sair da instrução CASE - então não tenho certeza por que o tipo de dados "D" está afetando o tipo de dados de retorno geral. Além disso, não importa se eu inverter a ordem das condições e ainda usar "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

Estou esquecendo de algo?

Eu tenho várias centenas de tabelas em um banco de dados que têm a mesma estrutura:

Alguns Id, Pos, Número Variado de Outros Campos

Assim, por exemplo, uma tabela pode ficar assim:

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

Eu quero acumular os valores para cada coluna 3-N. Então isso se tornaria:

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

Outra tabela pode ficar assim:

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

e torne-se isso:

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

O que eu gostaria de fazer é criar um TVF onde eu possa especificar o nome da tabela e fazer com que a função retorne seus campos. Idealmente, enrolado como acabei de demonstrar acima.

Solomon Rutzky forneceu uma solução ( SQL Server: Pass table name into table value function as a parameter ) onde mostrou como usar instruções XML e CASE para aceitar nomes de tabelas em um TVF.

Segue uma adaptação:

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;

Se eu criasse uma instrução CASE monstro e contasse todos os nomes de tabela de entrada possíveis, existe uma maneira de referenciar as colunas por posição ordinal (em vez de nome como estou fazendo acima)? Melhor ainda, arregaçar e delimitar seus valores também (que é meu objetivo final)?

Agradeço antecipadamente!

Além disso, aqui está o DDL para criar minhas duas tabelas de amostra:

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;

Eu sei que existem vários exemplos de como dividir strings no SQL Server. No entanto, eles geralmente são para uma única string delimitada. Qual é a melhor maneira de dividir várias strings delimitadas sem usar uma função (SQL Server 2016)?

Aqui está o que eu estou começando:

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

Aqui está o que eu gostaria de terminar com:

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      

Com base em tudo o que li, acho que posso precisar usar o método XML porque ele me permite especificar uma ordem, mas não consegui encontrar um exemplo desse método em que várias colunas sejam divididas.

Aqui está o T-SQL para criar a tabela de origem:

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;

Agradeço antecipadamente!

Eu tenho as duas tabelas a seguir, Tabela A e Tabela B.

O que estou tentando fazer é retornar um conjunto de resultados onde:

1. Se a posição inicial (somente) na Tabela B estiver entre a posição inicial e final de qualquer linha da Tabela A, não quero que essa linha da Tabela B esteja no conjunto de resultados.

2. Se qualquer uma das posições inicial e final da Tabela A estiver entre qualquer uma das posições inicial e final de qualquer linha da Tabela B, não quero essa linha da Tabela A.

Aqui está a Tabela A:

Aqui está a Tabela B:

Aqui está o meu conjunto de resultados desejado. Você pode ver que 197 e 212 da Tabela A estão entre a linha da Tabela B que tem 195 e 214, de modo que a linha da Tabela A não está no conjunto de resultados. Você também pode ver que a posição inicial 221 na Tabela B está entre um intervalo na Tabela A (218, 235), de modo que a linha da Tabela B também não está no conjunto de resultados.

Em suma, quero excluir onde:

• As posições inicial e final da Tabela A estão entre qualquer uma das posições inicial e final da tabela B.
• A posição inicial da Tabela B está entre qualquer uma das posições inicial e final da tabela A.

Além disso, há mais de um RIDs na tabela... Eu pensei (e ainda acho) que poderia fazer isso usando uma junção FULL (ou talvez usando APPLY), mas estou ficando um pouco preso, e é por isso que eu estou pedindo a todos vocês alguma ajuda sobre isso!

Aqui está o T-SQL para gerar as duas tabelas:

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

Tenho uma tabela que se parece com a seguinte:

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

Meu conjunto de resultados desejado é este:

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

Assim, para cada PersonId, cada grupo de colunas (2 colunas ColorInfo, 3 colunas FavoriteInfo e 1 coluna BirthInfo) é transposto em linhas. Eu tentei variações de CROSS APPLYe UNPIVOT, mas não consigo acertar. Eu sei que é um pedido estranho, mas alguém pode pensar em uma boa maneira de fazer isso?

Agradeço antecipadamente!

Aqui está o 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)

Eu tenho uma situação em que, para cada ID na minha tabela, existem vários campos, cada um contendo uma string delimitada por vírgulas. As strings não têm necessariamente o mesmo número de elementos umas das outras. O formato dos dados vem de outra equipe e não posso alterá-lo.

Como posso dividir essas strings em linhas com o SQL Server 2016? Prefiro não adicionar uma função ao banco de dados se isso puder ser evitado.

Aqui está uma amostra do que estou começando e o que estou procurando alcançar:

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

Saída Desejada:

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

EDIT: saída corrigida com base no comentário de Aaron.