McNets's questions

Eu preciso cavar em uma tabela de logs com um esquema semelhante a este:

CREATE TABLE t (
  id int PRIMARY KEY,
  data varchar(max)
);

A coluna dataarmazena um texto XML recebido de um serviço da web neste formato:

_{Esta é uma versão reduzida}

<?xml version="1.0" encoding="UTF-8"?>
<PARAM>
  <TAB DIM="30" ID="ZC3D2_1" SIZE="5">
    <LIN NUM = "1">
      <FLD NAME = "ZDOC" TYPE = "Char">Ferran López</FLD>
    </LIN>
  </TAB>
</PARAM>

Quando tento converter este texto para XML, recebo o próximo erro:

Análise XML: linha xx, caractere 48, caractere xml ilegal

Isso pode ser resolvido removendo a <xml>tag, ou pelo menos, o encodingatributo.

NOTA: Funciona bem se não houver caracteres especiais como ó, mesmo se eu não remover a <xml>tag.

Pergunta

Existe uma maneira de convertê-lo em XML sem substituir ou remover a <xml>tag?

CAST(REPLACE(data, 'encoding="UTF-8"', '') as XML)

db<>fique aqui

ATUALIZAR

O agrupamento do servidor é: Latin1_General_BIN

Mas mesmo se eu tentar alterar o agrupamento para o agrupamento usual de servidores, ele não funcionará.

SELECT
  id, 
  CAST((data COLLATE Latin1_General_CI_AS) as XML)
FROM
  t;

Estou desenvolvendo um .Net App que chama um procedimento armazenado que tem um parâmetro com valor de tabela declarado como TYPE READONLY.

A primeira vez que tentei ligar para o SP recebi o seguinte erro:

Msg 229, Level 14, State 5, Line 1
A permissão EXECUTE foi negada no objeto 'TYPE_OBJ', banco de dados 'MY_DB', esquema 'dbo'.

Depois de conceder a permissão EXECUTE ao usuário, ele funciona bem.

Mas não consigo encontrar no MS-DOCS nem em CREATE TYPE nem em Usar parâmetros com valor de tabela nenhuma referência às permissões necessárias para usá-lo como parâmetro.

Onde posso encontrar informações sobre as permissões necessárias para usar um TYPE como parâmetro?

Estou tentando (aprender) publicar uma função SQLCLR usando o Visual Studio 2017. (É uma função simples que envia um email.)

Como referência usei estes artigos sobre CodeProject e MSSQLTips :

Criar, executar, depurar e implantar a função SQL CLR com o projeto de banco de dados do Visual Studio 2013

Enviar email do SQL Server Express usando um procedimento armazenado CLR

Nas propriedades do projeto->SQLCLR eu configurei:

Permission level : UNSAFE

E de acordo com o artigo, atualizei os bancos de dados de destino:

sp_configure 'clr enabled', 1;  
GO  
RECONFIGURE;  
GO  

E depois que recebi o erro, segui este conselho no artigo MSSQLTips:

Se você receber mensagens de erro ao tentar compilar o código, talvez seja necessário alterar o banco de dados usando o comando a seguir e tente novamente criar o assembly e o procedimento armazenado.

ALTER DATABASE msdb SET trustworthy ON

Eu tentei usar dois bancos de dados de destino diferentes:

• SQL Server 2017 LocalDB
• SQL Server 2017 Express

Consigo construir o projeto sem erros, mas quando publico o projeto recebo um erro ao executar o próximo comando:

CREATE ASSEMBLY [dbSysmac]
    AUTHORIZATION [dbo]
    FROM 0x5F8A900003000000...
    WITH PERMISSION_SET = UNSAFE;

(47,1): SQL72014: .Net SqlClient Data Provider: Msg 10327, Level 14, State 1, Line 1 CREATE ASSEMBLY para assembly 'dbSysmac' falhou porque assembly 'dbSysmac' não é confiável. O assembly é confiável quando uma das seguintes condições for verdadeira: o assembly é assinado com um certificado ou uma chave assimétrica que tem um logon correspondente com a permissão UNSAFE ASSEMBLY ou o assembly é confiável usando sp_add_trusted_assembly.

O que estou fazendo de errado?

Dado o seguinte exemplo:

IF OBJECT_ID('dbo.my_table') IS NOT NULL
    DROP TABLE [dbo].[my_table];
GO

CREATE TABLE [dbo].[my_table]
(
    [id]    int IDENTITY (1,1)  NOT NULL PRIMARY KEY,
    [foo]   int                 NULL,
    [bar]   int                 NULL,
    [nki]   int                 NOT NULL
);
GO

/* Insert some random data */
INSERT INTO [dbo].[my_table] (foo, bar, nki)
SELECT TOP (100000)
    ABS(CHECKSUM(NewId())) % 14,
    ABS(CHECKSUM(NewId())) % 20,
    n = CONVERT(INT, ROW_NUMBER() OVER (ORDER BY s1.[object_id]))
FROM 
    sys.all_objects AS s1 
CROSS JOIN 
    sys.all_objects AS s2
GO

CREATE UNIQUE NONCLUSTERED INDEX [IX_my_table]
    ON [dbo].[my_table] ([nki] ASC);
GO

Se eu buscar todos os registros ordenados por [nki](índice não clusterizado):

SET STATISTICS TIME ON;
SELECT id, foo, bar, nki FROM my_table ORDER BY nki;
SET STATISTICS TIME OFF;

SQL Server Execution Times: CPU time = 266 ms, elapsed time = 493 ms

O otimizador escolhe o índice clusterizado e, em seguida, aplica um algoritmo de classificação.

Execution plan

Mas se eu forçá-lo a usar o índice não clusterizado:

SET STATISTICS TIME ON;
SELECT id, foo, bar, nki FROM my_table WITH(INDEX(IX_my_TABLE));
SET STATISTICS TIME OFF;

SQL Server Execution Times: CPU time = 311 ms, elapsed time = 188 ms

Em seguida, ele usa o índice não clusterizado com uma pesquisa de chave:

Execution plan

Obviamente, se o índice não clusterizado for transformado em um índice de cobertura:

CREATE UNIQUE NONCLUSTERED INDEX [IX_my_table]
    ON [dbo].[my_table] ([nki] ASC)
    INCLUDE (id, foo, bar);
GO

Em seguida, ele usa apenas este índice:

SET STATISTICS TIME ON;
SELECT id, foo, bar, nki FROM my_table ORDER BY nki;
SET STATISTICS TIME OFF;

SQL Server Execution Times: CPU time = 32 ms, elapsed time = 106 ms

Execution plan

Pergunta

• Por que o SQL Server usa o índice clusterizado mais um algoritmo de classificação em vez de usar um índice não clusterizado, mesmo que o tempo de execução seja 38% mais rápido no último caso?

De acordo com o MS-DOCS sobre exibições de esquema de informações do sistema , a definição de colunas de esquema tem uma nota de aviso que diz:

** Importante ** Não use as visualizações INFORMATION_SCHEMA para determinar o esquema de um objeto. A única maneira confiável de localizar o esquema de um objeto é consultar a exibição do catálogo sys.objects.

Por que as visualizações INFORMATION_SCHEMA não podem ser usadas para determinar o esquema de um objeto?

Esta informação está errada?

Nossa empresa recebe um arquivo diário de um de nossos clientes. É um arquivo EDIFACT traduzido para XML e baixado de um servidor SFTP.

Desenvolvemos um aplicativo (console c#) que:

1. Baixe o arquivo.
2. Carregue-o em um SQL Server (2005) usando um procedimento armazenado.
3. Exclui este arquivo do compartilhamento de rede.
4. Executa outro procedimento armazenado para integrá-lo às tabelas do nosso pedido de venda.

O serviço de agente SQL-Server e SQL-Server é iniciado como LocalSystemconta. (Ele será substituído em breve e prefiro deixá-lo como está, sei que não está configurado corretamente.)

O procedimento para armazenar o arquivo em nosso SQL-Server é semelhante a este:

CREATE PROCEDURE [dbo].[sp_ProviderName_Download]
(
    -- FQN of XML file
    @file_name nvarchar(260)
)
AS
BEGIN

    DECLARE @xml_data XML,
            @cmd nvarchar(max);

    BEGIN TRY

        SET @cmd = 'SELECT @xmlText = BulkColumn FROM OPENROWSET(BULK '
                    + '''' + @file_name + ''''
                    + ', SINGLE_BLOB) x;';

        EXEC sp_executesql @cmd, N'@xmlText XML OUTPUT', @xmlText = @xml_data OUTPUT;

        INSERT INTO [dbo].[ProviderXML_Register] (..., xml_data)
        VALUES (..., @xml_data);

        RETURN 0;

    END TRY
    BEGIN CATCH

        EXEC [dbo].[vsp_error_handler];
        RETURN -1;

    END CATCH

END

A questão :

O procedimento armazenado funciona dependendo de como definimos o ConnectionString em nosso aplicativo C#.

Se usar IntegratedSecurity=false:

this.scsb = new SqlConnectionStringBuilder();
this.scsb.ApplicationName = "ProviderXML_Download";
this.scsb.WorkstationID = Environment.MachineName;
this.scsb.DataSource = "xxx.xxx.xxx.xxx";
this.scsb.InitialCatalog = "my_db";
this.scsb.IntegratedSecurity = false;
this.scsb.Password = "*******";
this.scsb.UserID = "SqlServerLogin";

Funciona corretamente sem erros.

Mas se conectar usando IntegratedSecurity=true (usuário AD)

this.scsb = new SqlConnectionStringBuilder();
this.scsb.ApplicationName = "ProviderXML_Download";
this.scsb.WorkstationID = Environment.MachineName;
this.scsb.DataSource = "SQL_INSTANCE_NAME";
this.scsb.InitialCatalog = "my_db";
this.scsb.IntegratedSecurity = true;

ele falha com erro:

Erro do sistema operacional 5 Acesso negado.

O usuário do AD tem direitos MODIFICAR no compartilhamento de rede. E até onde eu sei, a conta 'LocalSystem' não deve poder acessar o compartilhamento de rede.

• Por que estou recebendo este erro quando IntegratedSecurity=true?

Às vezes, armazeno nomes de objetos (identificadores) em alguns de nossos bancos de dados, por exemplo, em algumas tabelas de parâmetros. Como seleciono registros dessas tabelas usando os operadores de comparação '=' ou 'LIKE', devo ter o cuidado de armazenar esses nomes sempre com ou sem colchetes .

IF EXISTS (SELECT 1 FROM MYTABLE WHERE OBJ_NAME = '[TABLE_NAME]';

ou

IF EXISTS (SELECT 1 FROM MYTABLE WHERE OBJ_NAME = 'TABLE_NAME';

No entanto, o MS-SQL tem algumas funções nas quais você pode usar nomes de objetos com ou sem colchetes, por exemplo, a função OBJECT_ID(). Eu configurei um exemplo mínimo em dbfiddle.uk .

CREATE TABLE TEST
(
    ID     INT IDENTITY(1,1) PRIMARY KEY,
    OBJECT sysname NOT NULL
);
GO

INSERT INTO TEST VALUES ('[obj1]'),('obj2'),('obj3'),('[obj4]');
GO

Agora posso usar OBJECT_ID() para verificar se a tabela TEST existe desta forma:

IF OBJECT_ID('TEST') IS NOT NULL
BEGIN
    SELECT 'TEST EXISTS.' OBJECT_ID;
END
GO

| OBJECT_ID    |
| :----------- |
| TEST EXISTS. |

IF OBJECT_ID('[TEST]') IS NOT NULL
BEGIN
    SELECT '[TEST] EXISTS.' OBJECT_ID;
END
GO

| OBJECT_ID      |
| :------------- |
| [TEST] EXISTS. |

Não importa se eu passo o identificador TEST com ou sem colchetes, o analisador é inteligente o suficiente para remover os colchetes.

Bem, posso simular isso adicionando uma função escalar que remove colchetes de uma string:

CREATE FUNCTION UNQUOTENAME(@TXT NVARCHAR(MAX)) 
RETURNS NVARCHAR(MAX)
AS
    BEGIN
        RETURN IIF(LEFT(@TXT, 1) = N'[' AND RIGHT(@TXT, 1) = N']', 
                   SUBSTRING(@TXT, 2, LEN(@TXT) -  2), 
                   @TXT);
    END;
GO

E então use-o desta forma:

SELECT dbo.UNQUOTENAME (N'[FIELD]') NAME1, N'FIELD' NAME2;
GO

NAME1 | NAME2
:---- | :----
FIELD | FIELD

SELECT ID, OBJECT 
FROM   TEST 
WHERE OBJECT LIKE 'obj%';
GO

ID | OBJECT
-: | :-----
 2 | obj2  
 3 | obj3  

SELECT ID, dbo.UNQUOTENAME(OBJECT) 
FROM   TEST 
WHERE  dbo.UNQUOTENAME(OBJECT) LIKE 'obj%';
GO

ID | (No column name)
-: | :---------------
 1 | obj1
 2 | obj2
 3 | obj3
 4 | obj4  

Mas minha pergunta é:

• Existe alguma função interna oculta que remove colchetes usando T-SQL?

dbfiddle aqui

Eu costumo projetar meus bancos de dados seguindo as próximas regras:

• Ninguém além de db_owner e sysadmin tem acesso às tabelas do banco de dados.
• As funções do usuário são controladas na camada do aplicativo. Eu costumo usar uma função de banco de dados para conceder acesso às visualizações, procedimentos armazenados e funções, mas em alguns casos, adiciono uma segunda regra para proteger alguns procedimentos armazenados.
• Eu uso TRIGGERS para validar inicialmente informações críticas.

CREATE TRIGGER <TriggerName>
ON <MyTable>
[BEFORE | AFTER] INSERT
AS
    IF EXISTS (SELECT 1 
               FROM   inserted
               WHERE  Field1 <> <some_initial_value>
               OR     Field2 <> <other_initial_value>)
    BEGIN
        UPDATE MyTable
        SET    Field1 = <some_initial_value>,  
               Field2 = <other_initial_value>  
        ...  
    END

• DML é executado usando procedimentos armazenados:

sp_MyTable_Insert(@Field1, @Field2, @Field3, ...);
sp_MyTable_Delete(@Key1, @Key2, ...);
sp_MyTable_Update(@Key1, @Key2, @Field3, ...);

Você acha que, nesse cenário, vale a pena usar DEFAULT CONSTRAINTs, ou estou adicionando um trabalho extra e desnecessário ao servidor DB?

Atualizar

Eu entendo que usando a restrição DEFAULT estou dando mais informações para outra pessoa que deve administrar o banco de dados. Mas estou mais interessado no desempenho.

Presumo que o banco de dados esteja sempre verificando os valores padrão, mesmo que eu forneça o valor correto, portanto, estou fazendo o mesmo trabalho duas vezes.

Por exemplo, existe uma maneira de evitar a restrição DEFAULT em uma execução de gatilho?

Preciso integrar um arquivo XML diário ao meu sistema.

O arquivo XML tem um esquema semelhante a este:

create table txml(id int identity, data xml);
insert into txml (data) values ('
<order>
    <trans DTrans="20170102" HTrans="10:52"></trans>
    <head Id="552665566"></head>
    <lin headId="552665566">
        <dLin Id="00001" CoArt="1111111" CoArtCust="05220001" NameArt="Product1" dateVal="20161115" />
        <acum DCalAcm="20170101" DIniAcm="20161115"><qtyAcm Qty="1101163.00" /></acum>
        <lastOrd Id="95767" QtyLastOrd="12000.00" DLastOrd="20170101" />
        <pLin LType="4"><uni Qty="24000.00"/><dIni Date="20170104"/><dEnd Date="20170108"/></pLin>
        <pLin LType="4"><uni Qty="20000.00"/><dIni Date="20170109"/><dEnd Date="20170112"/></pLin>
        <pLin LType="4"><uni Qty="24000.00"/><dIni Date="20170113"/><dEnd Date="20170116"/></pLin>
    </lin>
    <lin headId="552665566">
        <dLin Id="00002" CoArt="2222222" CoArtCust="05269958" NameArt="Product2" dateVal="20161101" />
        <acum DCalAcm="20170101" DIniAcm="20161101"><qtyAcm Qty="552652.00" /></acum>
       <lastOrd Id="49956" QtyLastOrd="5000.00" DLastOrd="20170101" />
        <pLin LType="4"><uni Qty="2200.00"/><dIni Date="20170104"/><dEnd Date="20170108"/></pLin>
        <pLin LType="4"><uni Qty="3000.00"/><dIni Date="20170109"/><dEnd Date="20170116"/></pLin>
    </lin>
</order>');

Como você pode ver para cada <lin>seção, você pode encontrar um número desconhecido de <pLin>linhas. Até agora estou fazendo este trabalho usando C# e um aplicativo de console .Net, mas gostaria de fazê-lo usando SQL.

Até agora, posso obter um conjunto de linhas de <lin>tags usando a próxima consulta:

SELECT  T2.lin.value('(./dLin/@Id)[1]', 'int') Id,
        T2.lin.value('(./dLin/@CoArt)[1]', 'varchar(20)') CoArt,
        T2.lin.value('(./dLin/@CoArtCust)[1]', 'varchar(20)') CoArtCust,
        T2.lin.value('(./lastOrd/@Id)[1]',  'varchar(20)') lastOrderId,
        T2.lin.value('(./lastOrd/@QtyLastOrd)[1]',  'decimal(18,2)') QtyLastOrd
FROM    txml
CROSS APPLY data.nodes('/order/lin') as T2(lin);

Isso produz o próximo resultado:

Id | CoArt   | CoArtCust | lastOrderId | QtyLastOrd
-: | :------ | :-------- | :---------- | :---------
 1 | 1111111 | 05220001  | 95767       | 12000.00  
 2 | 2222222 | 05269958  | 49956       | 5000.00   

Mas devo adicionar linhas aninhadas correspondentes a <pLin>tags e produzir o próximo resultado:

Id | CoArt   | CoArtCust | lastOrderId | QtyLastOrd | DIni       | DEnd       | NextQty  
-: | :------ | :-------- | :---------- | :--------- | ---------- | ---------- | ---------
 1 | 1111111 | 05220001  | 95767       | 12000.00   | 04/01/2017 | 08/01/2017 | 24000.00  
 1 | 1111111 | 05220001  | 95767       | 12000.00   | 09/01/2017 | 12/01/2017 | 20000.00  
 1 | 1111111 | 05220001  | 95767       | 12000.00   | 13/01/2017 | 16/01/2017 | 24000.00  
 2 | 2222222 | 05269958  | 49956       | 5000.00    | 04/01/2017 | 08/01/2017 | 2200.00  
 2 | 2222222 | 05269958  | 49956       | 5000.00    | 09/01/2017 | 16/01/2017 | 3000.00  

Eu configurei um dbfiddle aqui

Dado um esquema como este:

CREATE TABLE Foo
(
    Id int PRIMARY KEY,
    Position int NOT NULL,
    Title varchar(10) NOT NULL
);

INSERT INTO Foo VALUES
(1, 3,  'Title3'),
(2, 10, 'Title10'),
(3, 1,  'Title1'),
(4, 12, 'Title12'),
(5, 2,  'Title2');

Eu preciso gerar uma string separada por vírgulas ordenada por Position:

'M' + Id + ' AS [' + Title + ']'

Resultado desejado:

M1 AS [Title1], M2 AS [Title10], M3 AS [Title3], M4 AS [Title10], M5 AS [Title12]

Eu tentei:

DECLARE @rows nvarchar(max);
SET @rows = STUFF((SELECT DISTINCT ', ' + ('M' + CAST(Id as varchar(10)) + ' AS ' + QUOTENAME(Title)) 
                   FROM Foo
            FOR XML PATH(''), TYPE
            ).value('.', 'nvarchar(MAX)') 
           ,1,1,'');

SELECT @rows;

Mas ele constrói o resultado ordenado por Id:

M1 AS [Title3], M2 AS [Title10], M3 AS [Title1], M4 AS [Title12], M5 AS [Title2]

Se eu adicionar ORDER BY Idà STUFFexpressão:

DECLARE @rows nvarchar(max);
SET @rows = STUFF((SELECT DISTINCT ', ' + ('M' + CAST(Id as varchar(10)) + ' AS ' + QUOTENAME(Title)) 
                   FROM Foo
                   ORDER BY Position
            FOR XML PATH(''), TYPE
            ).value('.', 'nvarchar(MAX)') 
           ,1,1,'');

SELECT @rows;

Produz o próximo erro:

Msg 145 Nível 15 Estado 1 Linha 2 Os
itens ORDER BY devem aparecer na lista de seleção se SELECT DISTINCT for especificado. +

Eu posso usar uma subconsulta ordenada por Position:

DECLARE @rows nvarchar(max);
SET @rows = STUFF((SELECT DISTINCT ', ' + ('M' + CAST(Id as varchar(10)) + ' AS ' + QUOTENAME(Title)) 
                   FROM (SELECT TOP 100 PERCENT Id, Position, Title FROM Foo ORDER BY Position) X
            FOR XML PATH(''), TYPE
            ).value('.', 'nvarchar(MAX)') 
           ,1,1,'');

SELECT @rows;

M1 AS [Title3], M2 AS [Title10], M3 AS [Title1], M4 AS [Title12], M5 AS [Title2]

Mas gostaria de saber se existe outra maneira de ordenar o resultado sem usar uma subconsulta. Não há títulos duplicados.

dbfiddle aqui

Dado este esquema:

CREATE TABLE #TEST_COALESCE
(
    Id int NOT NULL,
    DateTest datetime NOT NULL,
    PRIMARY KEY (Id, DateTest)
);

INSERT INTO #TEST_COALESCE VALUES
(1, '20170201'),
(1, '20170202'),
(1, '20170203'),
(2, '20170204'),
(2, '20170205'),
(2, '20170206');

Se eu usar COALESCE dentro de uma subconsulta, ela retornará NULL.

SELECT  t1.Id, t1.DateTest,
        (SELECT TOP 1 COALESCE(t2.DateTest, t1.DateTest)
         FROM         #TEST_COALESCE t2
         WHERE        t2.Id = t1.Id
         AND          t2.DateTest > t1.DateTest
         ORDER BY     t2.Id, t2.DateTest) NextDate
FROM    #TEST_COALESCE t1;

+----+---------------------+---------------------+
| Id | DateTest            | NextDate            |
+----+---------------------+---------------------+
| 1  | 01.02.2017 00:00:00 | 02.02.2017 00:00:00 |
| 1  | 02.02.2017 00:00:00 | 03.02.2017 00:00:00 |
| 1  | 03.02.2017 00:00:00 | NULL                |
| 2  | 04.02.2017 00:00:00 | 05.02.2017 00:00:00 |
| 2  | 05.02.2017 00:00:00 | 06.02.2017 00:00:00 |
| 2  | 06.02.2017 00:00:00 | NULL                |
+----+---------------------+---------------------+

No entanto, se for colocado fora da subconsulta:

SELECT  t1.Id, t1.DateTest,
        COALESCE((SELECT TOP 1 t2.DateTest
                 FROM         #TEST_COALESCE t2
                 WHERE        t2.Id = t1.Id
                 AND          t2.DateTest > t1.DateTest
                 ORDER BY     t2.Id, t2.DateTest), t1.DateTest) NextDate
FROM    #TEST_COALESCE t1;

+----+---------------------+---------------------+
| Id | DateTest            | NextDate            |
+----+---------------------+---------------------+
| 1  | 01.02.2017 00:00:00 | 02.02.2017 00:00:00 |
| 1  | 02.02.2017 00:00:00 | 03.02.2017 00:00:00 |
| 1  | 03.02.2017 00:00:00 | 03.02.2017 00:00:00 |
| 2  | 04.02.2017 00:00:00 | 05.02.2017 00:00:00 |
| 2  | 05.02.2017 00:00:00 | 06.02.2017 00:00:00 |
| 2  | 06.02.2017 00:00:00 | 06.02.2017 00:00:00 |
+----+---------------------+---------------------+

Por que a primeira subconsulta não retorna: t1.DateTest?

http://rextester.com/CNDOO40877

Configurar

Eu configurei um exemplo em rextester e dbfiddle .

Cenário

Lista de preços : É a lista de preços dos produtos, um produto pode ter mais de um preço ativo, mesmo um preço futuro.

+---------+-------+------------+--------+--------+
| product | price | date_price |  base  | active |
+---------+-------+------------+--------+--------+
|   0125  |    90 | 01.01.2017 |  1200  |    0   |
|   0125  |   100 | 25.01.2017 |  1000  |    1   |
|   0125  |   110 | 27.02.2017 |   500  |    1   |
+---------+-------+------------+--------+--------+
|   1200  |   140 | 01.01.2017 |  2000  |    0   |
|   1200  |   150 | 01.02.2017 |  1500  |    1   |
|   1200  |   160 | 27.02.2017 |  1000  |    1   |
+---------+-------+------------+--------+--------+

Encomendas As encomendas pendentes têm um preço e uma data de encomenda

+---------+------------+-------+--------+
| product | order_date | price |  base  |
+---------+------------+-------+--------+
|   0125  | 19.02.2017 |  100  |  1000  |
|   0125  | 20.02.2017 |  100  |  1000  |
|   0125  | 21.02.2017 |  100  |  1000  |
|   0125  | 22.02.2017 |  100  |  1000  |
|   0125  | 23.02.2017 |  100  |  1000  |
|   0125  | 28.02.2017 |  110  |   500  |
+---------+------------+-------+--------+
|   1200  | 19.02.2017 |  150  |  1500  |
|   1200  | 20.02.2017 |  150  |  1500  |
|   1200  | 21.02.2017 |  150  |  1500  |
|   1200  | 22.02.2017 |  150  |  1500  |
|   1200  | 23.02.2017 |  150  |  1500  |
|   1200  | 28.02.2017 |  160  |  1000  |
+---------+------------+-------+--------+

Sempre que adicionamos um novo preço na lista, devemos atualizar as linhas afetadas de pedidos pendentes.

Por exemplo, se adicionarmos:

+---------+-------+------------+--------+--------+
| product | price | date_price |  base  | active |
+---------+-------+------------+--------+--------+
|   0125  |   105 | 21.02.2017 |  1300  |    1   |
|   1200  |   155 | 21.02.2017 |  1400  |    1   |
+---------+-------+------------+--------+--------+

A nova lista de preços deve ser:

+---------+------------+-------+--------+
| product | order_date | price |  base  |
+---------+------------+-------+--------+
|   0125  | 19.02.2017 |  100  |  1000  |
|   0125  | 20.02.2017 |  100  |  1000  |
|   0125  | 21.02.2017 |  105  |  1300  | *
|   0125  | 22.02.2017 |  105  |  1300  | * Affected rows
|   0125  | 23.02.2017 |  105  |  1300  | *
|   0125  | 28.02.2017 |  110  |   500  | 
+---------+------------+-------+--------+
|   1200  | 19.02.2017 |  150  |  1500  |
|   1200  | 20.02.2017 |  150  |  1500  |
|   1200  | 21.02.2017 |  150  |  1500  | *
|   1200  | 22.02.2017 |  150  |  1500  | * Affectd rows between 21.02.2017 and 27.02.2017
|   1200  | 23.02.2017 |  150  |  1500  | *
|   1200  | 28.02.2017 |  160  |  1000  |
+---------+------------+-------+--------+

Gostaria de atualizar os registros afetados usando uma única consulta.

Por existir outro preço a partir de 27.02.2017, os pedidos de 28.02.2017 não são afetados pelo preço inserido.

Processo real

Até agora, uso uma subconsulta que busca a primeira data que corresponde na tabela da lista de preços, mas agora preciso atualizar o basecampo também. (E mais dois ou três campos) e gostaria de evitar usar duas ou mais subconsultas.

update @orders
set    price = (select   top 1 pl.price
                from     @price_list pl
                where    pl.product = o.product
                and      pl.date_price <= o.order_date
                and      active = 1
                order by pl.date_price desc),
       base  = (select   top 1 pl.base
                from     @price_list pl
                where    pl.product = o.product
                and      pl.date_price <= o.order_date
                and      active = 1
                order by pl.date_price desc)
from   @orders o
where  o.product in ('0125', '1200');  --<<< select distinct product from inserted

Por favor, sinta-se à vontade para corrigir meu texto, eu sei que minha gramática inglesa não é boa o suficiente.

Enquanto:

tipo de campo de data começando com SQL Server 2008

Dada uma tabela:

CREATE TABLE dbo.MyTable
(
    Code int NOT NULL,
    DateCode datetime NOT NULL
)
CONSTRAINT [PK_MyTable] PRIMARY KEY CLUSTERED 
(
[Code] ASC, [DateCode] DESC
);

Qual é a melhor maneira de RESTRINGIR que DateCode seja apenas uma data?

Deve permitir datas sem parte de tempo.

2017-01-02
2017-01-02 00:00:00.000
20170102

Até agora estou usando:

ALTER TABLE dbo.MyTable ADD CONSTRAINT [CHK_MyTable_DateCode]
    CHECK ([DateCode] = DATEADD(DAY, 0, DATEDIFF(DAY, 0, [DateCode])));

Mas, gostaria de saber se existe uma maneira melhor/curta de realizá-lo.

Para uma determinada estrutura como esta:

+----+----------+
| ID |   Name   |
+----+----------+
| a  | ZZZZZZZZ |
| b  | YYYYYYYY |
| c  | WWWWWWWW |
| d  | ZZZZZZZZ |
| e  | YYYYYYYY |
| f  | WWWWWWWW |
+----+----------+

Obtenha a lista de pares (ID, ID2) com mesmo Nome, excluindo todos os registros onde (ID2, ID) tenha o correspondente (ID, ID2).

+----+----------+-----+
| ID |   Name   | ID2 |
+----+----------+-----+
| a  | ZZZZZZZZ |  d  |
| b  | YYYYYYYY |  e  |
| c  | WWWWWWWW |  f  |
+----+----------+-----+

Encontrei esta solução, mas gostaria de saber se existe uma solução mais "acadêmica".

;WITH Pair AS
(
SELECT
    CASE WHEN p1.Id > p2.Id THEN p2.Id ELSE p1.Id END as ID,
    CASE WHEN p1.Id > p2.Id THEN p1.Id ELSE p2.Id END as ID2,
    p1.Name
FROM Pairs p1
     LEFT JOIN Pairs p2
     ON p1.Name = p2.Name
     AND p1.Id <> p2.Id
)
SELECT DISTINCT ID, ID2, Name
FROM Pair;

Rextester

CREATE TABLE Pairs(Id varchar(5), Name varchar(20));
INSERT INTO Pairs VALUES
('a','ZZZZZZZZ'),
('b','YYYYYYYY'),
('c','WWWWWWWW'),
('d','ZZZZZZZZ'),
('e','YYYYYYYY'),
('f','WWWWWWWW');

Dada uma tabela hierárquica como esta:

CREATE TABLE [dbo].[btree]
(
  id INT PRIMARY KEY
, parent_id INT REFERENCES [dbo].[btree] ([id])
, name NVARCHAR(20)
);

Eu gostaria de obter toda a estrutura da árvore.

Por exemplo, usando esses dados:

INSERT INTO [btree] VALUES (1, null, '1 Root');
INSERT INTO [btree] VALUES (2,    1, '1.1 Group');
INSERT INTO [btree] VALUES (3,    1, '1.2 Group');
INSERT INTO [btree] VALUES (4,    2, '1.1.1 Group');
INSERT INTO [btree] VALUES (5,    2, '1.1.2 Group');
INSERT INTO [btree] VALUES (6,    3, '1.2.1 Group');
INSERT INTO [btree] VALUES (7,    3, '1.2.2 Group');
INSERT INTO [btree] VALUES (8,    4, '1.1.1.1 Items');
INSERT INTO [btree] VALUES (9,    4, '1.1.1.2 Items');
INSERT INTO [btree] VALUES (10,   5, '1.1.2.1 Items');
INSERT INTO [btree] VALUES (11,   5, '1.1.1.2 Items');
INSERT INTO [btree] VALUES (12,   6, '1.2.1.1 Items');
INSERT INTO [btree] VALUES (13,   6, '1.2.1.2 Items');
INSERT INTO [btree] VALUES (14,   7, '1.2.2.1 Items');

gostaria de obter:

+----+-----------+---------------------+
| id | parent_id | description         |
+----+-----------+---------------------+
|  1 |    NULL   | 1 Root              |
|  2 |     1     |   1.1 Group         |
|  4 |     2     |     1.1.1 Group     |
|  8 |     4     |       1.1.1.1 Items |
|  9 |     4     |       1.1.1.2 Items |
|  5 |     2     |     1.1.2 Group     |
| 10 |     5     |       1.1.2.1 Items |
| 11 |     5     |       1.1.2.2 Items |
|  3 |     1     |   1.2 Group         |
|  6 |     3     |     1.2.1 Group     |
| 12 |     6     |       1.2.1.1 Items |
| 13 |     6     |       1.2.1.2 Items |
|  7 |     3     |     1.2.2 Group     |
| 14 |     7     |       1.2.2.1 Items |
+----+-----------+---------------------+

Estou buscando registros usando uma consulta recursiva como esta:

;WITH tree AS
(
    SELECT c1.id, c1.parent_id, c1.name, [level] = 1
    FROM dbo.[btree] c1
    WHERE c1.parent_id IS NULL
    UNION ALL
    SELECT c2.id, c2.parent_id, c2.name, [level] = tree.[level] + 1
    FROM dbo.[btree] c2 INNER JOIN tree ON tree.id = c2.parent_id
)
SELECT tree.level, tree.id, parent_id, REPLICATE('  ', tree.level - 1) + tree.name AS description
FROM tree
OPTION (MAXRECURSION 0)
;

E este é o resultado atual:

+----+-----------+---------------------+
| id | parent_id | description         |
|  1 |    NULL   | 1 Root              |
|  2 |     1     |   1.1 Group         |
|  3 |     1     |   1.2 Group         |
|  6 |     3     |     1.2.1 Group     |
|  7 |     3     |     1.2.2 Group     |
| 14 |     7     |       1.2.2.1 Items |
| 12 |     6     |       1.2.1.1 Items |
| 13 |     6     |       1.2.1.2 Items |
|  4 |     2     |     1.1.1 Group     |
|  5 |     2     |     1.1.2 Group     |
| 10 |     5     |       1.1.2.1 Items |
| 11 |     5     |       1.1.1.2 Items |
|  8 |     4     |       1.1.1.1 Items |
|  9 |     4     |       1.1.1.2 Items |
+----+-----------+---------------------+

Não consigo descobrir como ordená-lo por níveis.

Existe uma maneira de definir uma classificação para cada subnível?

Eu configurei umRextester

Dado o esquema:

create table containers (
    id int primary key, 
    name text, 
    product_id int, 
    lot int, 
    qty int, 
    parent_id int);

create table orders_items (
    id int,
    position int,
    container_id int);

Onde containersé uma tabela hierárquica com um número desconhecido de níveis.

containers.parent_id = containers.id OR null

Um contêiner pode conter outros contêineres, esta é uma estrutura de árvore de contêineres:

  23 Box 40 Parent  [ID: NULL, Lot: NULL, Qty: SUM(20+20)=40]
   |
   |--22 Bag 20     [Parent ID: 23, Lot: NULL, Qty: SUM(10+10)=20]
   |   |--21 Bag 10 [Parent ID: 22, Lot: 701, Qty: 10]
   |   |--22 Bag 10 [Parent ID: 22, Lot: 703, Qty: 10]
   | 
   |--19 Bag 20     [Parent ID: 23, Lot: NULL, Qty: SUM(10+10)=20]
       |--17 Bag 10 [Parent ID: 19, Lot: 700, Qty: 10]
       |--18 Bag 10 [Parent ID: 19, Lot: 701, Qty: 10] 

Para nós um contentor é considerado como uma unidade expedidora, dependendo dos requisitos do cliente um contentor pode ser um simples saco de plástico de 10 peças, ou uma palete completa com 10 caixas de 5000 peças cada uma. Apenas os contêineres de último nível têm um lote atribuído, os contêineres pais podem conter lotes diferentes, mas sempre pertencentes ao mesmo produto.

Precisamos listar todos os produtos incluídos em um único pedido, agrupados por lote.

• Produtos diferentes não podem ter o mesmo lote.
• Um contêiner só pode conter contêineres do mesmo produto.

Dados de amostra:

insert into containers values
  (23, 'Box 40',  2, null, 40,  null)
, (16, 'Pallet',  1, null, 120, null)
, (12, 'Bag 20',  1, null, 20,  14)
, (13, 'Bag 20',  1, null, 20,  14)
, (14, 'Box 40',  1, null, 40,  16)
, (19, 'Bag 20',  2, null, 20,  23)
, (22, 'Bag 20',  2, null, 20,  23)
, (5,  'Bag 20',  1, null, 20,  7)
, (6,  'Bag 20',  1, null, 20,  7)
, (7,  'Box 40',  1, null, 40,  16)
, (1,  'Bag 10',  1, 500,  10,  5)
, (2,  'Bag 10',  1, 501,  10,  5)
, (3,  'Bag 10',  1, 502,  10,  6)
, (4,  'Bag 10',  1, 500,  10,  6)
, (8,  'Bag 10',  1, 600,  10,  12)
, (9,  'Bag 10',  1, 601,  10,  12)
, (10, 'Bag 10',  1, 502,  10,  13)
, (11, 'Bag 10',  1, 501,  10,  13)
, (15, 'Box 40',  1, 600,  40,  16)
, (17, 'Bag 10',  2, 700,  10,  19)
, (18, 'Bag 10',  2, 701,  10,  19)
, (20, 'Bag 10',  2, 703,  10,  22)
, (21, 'Bag 10',  2, 701,  10,  22);

insert into orders_items values
(1, 1, 16),
(1, 2, 23);

O número do pedido 1tem duas linhas de detalhes, contêineres 16e 23, preciso incluir todos os lotes nesses contêineres.

Neste exemplo, o resultado deve mostrar estas linhas:

+----+------------+-------------+------+------+------------+
| id |    name    |  product_id |  lot |  qty |  parent_id |
+----+------------+-------------+------+------+------------+
|  1 |  'Bag 10'  |           1 |  500 |   10 |          5 |
|  2 |  'Bag 10'  |           1 |  501 |   10 |          5 |
|  3 |  'Bag 10'  |           1 |  502 |   10 |          6 |
|  4 |  'Bag 10'  |           1 |  500 |   10 |          6 |
|  8 |  'Bag 10'  |           1 |  600 |   10 |         12 |
|  9 |  'Bag 10'  |           1 |  601 |   10 |         12 |
| 10 |  'Bag 10'  |           1 |  502 |   10 |         13 |
| 11 |  'Bag 10'  |           1 |  501 |   10 |         13 |
| 15 |  'Box 40'  |           1 |  600 |   40 |         16 |
| 17 |  'Bag 10'  |           2 |  700 |   10 |         19 |
| 18 |  'Bag 10'  |           2 |  701 |   10 |         19 |
| 20 |  'Bag 10'  |           2 |  703 |   10 |         22 |
| 21 |  'Bag 10'  |           2 |  701 |   10 |         22 |
+----+------------+-------------+------+------+------------+

Agrupar por lote:

|----------|---------|-----|----------|
| Order ID | Product | Lot | Quantity |
|----------|---------|-----|----------|
|    1     |    1    | 500 |    20    |
|    1     |    1    | 501 |    20    |
|    1     |    1    | 502 |    20    |
|    1     |    1    | 600 |    50    |
|    1     |    1    | 601 |    10    |
|    1     |    2    | 700 |    10    |
|    1     |    2    | 701 |    20    |
|    1     |    2    | 703 |    10    |
|----------|---------|-----|----------|

Eu criei um exemplo de rextester com esses valores.

Se você obtiver a definição de algumas visualizações usando sys.sp_helptext:

exec sys.sp_helptext 'sys.columns'

CREATE VIEW sys.columns
AS
  SELECT c.id                                                             AS object_id,
         c.NAME,
         c.colid                                                          AS column_id,
         c.xtype                                                          AS system_type_id,
         c.utype                                                          AS user_type_id,
         c.length                                                         AS max_length,
         c.prec                                                           AS PRECISION,
         c.scale,
         CONVERT(SYSNAME, CollationPropertyFromId(c.collationid, 'name')) AS collation_name,
         sysconv(bit, 1 - ( c.status & 1 ))                               AS is_nullable,-- CPM_NOTNULL  
         sysconv(bit, c.status & 2)                                       AS is_ansi_padded,-- CPM_NOTRIM  
         sysconv(bit, c.status & 8)                                       AS is_rowguidcol,-- CPM_ROWGUIDCOL  
         sysconv(bit, c.status & 4)                                       AS is_identity,-- CPM_IDENTCOL  
         sysconv(bit, c.status & 16)                                      AS is_computed,-- CPM_COMPUTED  
         sysconv(bit, c.status & 32)                                      AS is_filestream,-- CPM_FILESTREAM  
         sysconv(bit, c.status & 0x020000)                                AS is_replicated,-- CPM_REPLICAT  
         sysconv(bit, c.status & 0x040000)                                AS is_non_sql_subscribed,-- CPM_NONSQSSUB  
         sysconv(bit, c.status & 0x080000)                                AS is_merge_published,-- CPM_MERGEREPL  
         sysconv(bit, c.status & 0x100000)                                AS is_dts_replicated,-- CPM_REPLDTS  
         sysconv(bit, c.status & 2048)                                    AS is_xml_document,-- CPM_XML_DOC   
         c.xmlns                                                          AS xml_collection_id,
         c.dflt                                                           AS default_object_id,
         c.chk                                                            AS rule_object_id,
         sysconv(bit, c.status & 0x1000000)                               AS is_sparse,-- CPM_SPARSE  
         sysconv(bit, c.status & 0x2000000)                               AS is_column_set -- CPM_SPARSECOLUMNSET  
  FROM   sys.syscolpars c
  WHERE  number = 0
         AND has_access('CO', c.id) = 1 

Existe uma função chamada sysconv.

A função 'sysconv' é usada por essas exibições e você pode selecionar valores dessa exibição sem obter nenhuma mensagem de erro.

select * from sys.columns;

Mas se eu tentar algo parecido com:

select sysconv(bit, 375 & 8);

Eu recebo o erro:

sysconv não é um nome de função integrado reconhecido

Como Martin Smith apontou, essa função pode ser substituída por convert (bit, 375 & 8), mas eu me pergunto por que posso selecionar valores dessa visão e não posso usá-la em minhas consultas, mesmo quando o SSMS a reconhece como comando, alterando a cor do primeiro plano?