Engenho &amp; Engenhocas

Consultar colunas XML com XQuery em T-SQL

Para ler

Como redefinir um novo valor para a coluna identity em T-SQL

Para ler

IDENT_CURRENT (Transact-SQL)

Antes de mais nada, não importa qual é o valor da coluna identity. Está sempre bem, desde que seja único. Afinal, as pessoas não precisam de ver o valor.

No entanto, se é realmente necessário redefinir os valores da identidade (por exemplo, para um teste ou para um projecto), isso pode fazer-se no SQL Server.

Suponha-se o seguinte cenário, uma série de registos apagados de uma tabela com uma coluna identity. Como definir manualmente um novo valor de identidade para a coluna de identidade?

A função IDENT_CURRENT(<table_or_view>)retorna o último valor identity gerado para a tabela. O valor da última identidade gerado pode ser para qualquer sessão e para qualquer âmbito.

DBCC CHECKIDENT
(    <table_name>          
[, { NORESEED | { RESEED [, <new_reseed_value> ] } } ])

pode usar-se para definir manualmente um novo valor identity actual para a coluna de identidade.

Como funciona:

CREATE TABLE Product (
ProductID INT IDENTITY,
ProductName VARCHAR(200),
Price INT);

INSERT INTO Product (ProductName, Price)
VALUES ('Produto1', 100);

INSERT INTO Product (ProductName, Price)
VALUES ('Produto2', 250);

INSERT INTO Product (ProductName, Price)
VALUES ('Produto3', 300);

INSERT INTO Product (ProductName, Price)
VALUES ('Produto4', 340);

INSERT INTO Product (ProductName, Price)
VALUES ('Produto5', 500);

SELECT * FROM Product;

DELETE FROM Product WHERE ProductID >= 2;

INSERT INTO Product (ProductName, Price)
VALUES ('Produto1', 100);

INSERT INTO Product (ProductName, Price)
VALUES ('Produto2', 250);

INSERT INTO Product (ProductName, Price)
VALUES ('Produto3', 300);

INSERT INTO Product (ProductName, Price)
VALUES ('Produto4', 340);

INSERT INTO Product (ProductName, Price)
VALUES ('Produto5', 500);

O último valor identity retorna 10.SELECT IDENT_CURRENT ('dbo.Product') AS Current_Identity;

Agora, aplicando o CHECKIDENT com RESEED

DELETE FROM Product WHERE ProductID >= 2;
DECLARE @MaxProductID AS INT;
SELECT @MaxProductID = MAX(ProductID) FROM dbo.Product;

DBCC CHECKIDENT ('dbo.Product', RESEED, @MaxProductID);

O último valor identity retorna 1.

SELECT IDENT_CURRENT ('dbo.Product') AS Current_Identity;

INSERT INTO Product (ProductName, Price)
VALUES ('Produto2', 250);

INSERT INTO Product (ProductName, Price)
VALUES ('Produto3', 300);

INSERT INTO Product (ProductName, Price)
VALUES ('Produto4', 340);

INSERT INTO Product (ProductName, Price)
VALUES ('Produto5', 500);

SELECT * FROM Product;

A execução pode ser analisada aqui.

Para aplicar a verificação do último valor identity gerado para todas as tabelas de bases de dados, utilizar o script explanado aqui.

Documentação:

DBCC CHECKIDENT (Transact-SQL)

T-SQL para limpeza de caracteres não legíveis

Para ler

DECLARE @strClean as VARCHAR(1000) = 'atuastringaqui';

SELECT LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(REPLACE(@strClean, CHAR(10), CHAR(32)), CHAR(13), CHAR(32)), CHAR(160), CHAR(32)), CHAR(10), CHAR(32))))

Cláusulas Over e Partition by em SQL Server

Para ler

A cláusula OVER define uma janela ou conjunto de registos especificado pelo utilizador nos resultados da consulta. Uma função de janela calcula um valor para cada registo na janela. A cláusula OVER pode ser usada com funções para calcular valores agregados.

A cláusula PARTITION BY é uma sub-cláusula da cláusula OVER. A cláusula PARTITION BY divide os resultados de uma consulta em partições. A função da janela é executada em cada partição separadamente.

A cláusula PARTITION BY divide os resultados em partições e altera como a função da janela é calculada. A cláusula PARTITION BY não reduz o número de linhas retornadas.

O exemplo seguinte serve apenas para ilustrar a aplicação combinada destas cláusulas.

Exemplo para uma factura

CREATE TABLE Factura
  (
     ID INT,Numero VARCHAR(9),Montantetotal DECIMAL
  );

CREATE TABLE Facturalinha
  (
     ID INT,FacturaID INT,Descricao NVARCHAR(50),Montante DECIMAL
  );

INSERT INTO Factura
VALUES      (1,'002-00509',300.00);

INSERT INTO Facturalinha
VALUES      (1,1,'produto 01',50.00);

INSERT INTO Facturalinha
VALUES      (2,1,'produto 02',30.00);

INSERT INTO Facturalinha
VALUES      (3,1,'produto 03',20.00);

INSERT INTO Facturalinha
VALUES      (4,1,'produto descrição a definir',200.00);

INSERT INTO Factura
VALUES      (2,'003-00595',400.00);

INSERT INTO Facturalinha
VALUES      (1,2,'produto 01',70.00);

INSERT INTO Facturalinha
VALUES      (2,2,'produto 02',30.00);

INSERT INTO Facturalinha
VALUES      (3,2,'produto 03',100.00);

INSERT INTO Facturalinha
VALUES      (4,2,'produto 04',50.00);

INSERT INTO Facturalinha
VALUES      (5,2,'produto 05',150.00);

SELECT f.Numero AS "Factura número"
, ROW_NUMBER() OVER(PARTITION BY fl.FacturaID ORDER BY fl.ID ASC) AS "Linha nº"  
, fl.Descricao AS "Descrição"
, fl.Montante
, SUM(fl.Montante) OVER(PARTITION BY fl.FacturaID ORDER BY fl.ID ASC ROWS UNBOUNDED PRECEDING) AS "Montante acumulado"
, IIF(fl.ID = MAX(fl.ID) OVER (PARTITION BY fl.FacturaID), SUM(fl.Montante) OVER(PARTITION BY fl.FacturaID), 0)  AS "Montante total factura"
, f.MontanteTotal AS "Factura montante total"
FROM   Factura f
       INNER JOIN Facturalinha fl
               ON f.Id = fl.Facturaid

O SELECT apresenta o seguinte resultado

Relacionado: SQL PARTITION BY Clause overview

Fonte: Microsoft Docs

Script para manutenção de índices em SQL Server

Para ler

Encontrei o seguinte script para manutenção de índices neste sítio web. Rapidamente se reorganiza ou reconstroi os índices de uma base de dados dependendo da fragmentação, conforme os valores aconselhados pela Microsoft.

-- http://www.sqlmusings.com
USE SUSDB  -- <databasename> statement has been executed first.
GO

SET NOCOUNT ON

-- adapted from "Rebuild or reorganize indexes (with configuration)" from MSDN Books Online 
-- (http://msdn.microsoft.com/en-us/library/ms188917.aspx)
 
-- =======================================================
-- || Configuration variables:
-- || - 10 is an arbitrary decision point at which to
-- || reorganize indexes.
-- || - 30 is an arbitrary decision point at which to
-- || switch from reorganizing, to rebuilding.
-- || - 0 is the default fill factor. Set this to a
-- || a value from 1 to 99, if needed.
-- =======================================================
DECLARE @reorg_frag_thresh   float  SET @reorg_frag_thresh   = 10.0
DECLARE @rebuild_frag_thresh float  SET @rebuild_frag_thresh = 30.0
DECLARE @fill_factor         tinyint SET @fill_factor         = 80
DECLARE @report_only         bit  SET @report_only         = 0

-- added (DS) : page_count_thresh is used to check how many pages the current table uses
DECLARE @page_count_thresh  smallint SET @page_count_thresh   = 1000
 
-- Variables required for processing.
DECLARE @objectid       int
DECLARE @indexid        int
DECLARE @partitioncount bigint
DECLARE @schemaname     nvarchar(130) 
DECLARE @objectname     nvarchar(130) 
DECLARE @indexname      nvarchar(130) 
DECLARE @partitionnum   bigint
DECLARE @partitions     bigint
DECLARE @frag           float
DECLARE @page_count     int
DECLARE @command        nvarchar(4000)
DECLARE @intentions     nvarchar(4000)
DECLARE @table_var      TABLE(
                          objectid     int,
                          indexid      int,
                          partitionnum int,
                          frag         float,
        page_count   int
                        )
 
-- Conditionally select tables and indexes from the
-- sys.dm_db_index_physical_stats function and
-- convert object and index IDs to names.
INSERT INTO
    @table_var
SELECT
    [object_id]                    AS objectid,
    [index_id]                     AS indexid,
    [partition_number]             AS partitionnum,
    [avg_fragmentation_in_percent] AS frag,
 [page_count]       AS page_count
FROM
    sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED')
WHERE
    [avg_fragmentation_in_percent] > @reorg_frag_thresh 
 AND
 page_count > @page_count_thresh
 AND
    index_id > 0
 
 
-- Declare the cursor for the list of partitions to be processed.
DECLARE partitions CURSOR FOR
    SELECT * FROM @table_var
 
-- Open the cursor.
OPEN partitions
 
-- Loop through the partitions.
WHILE (1=1) BEGIN
    FETCH NEXT
        FROM partitions
        INTO @objectid, @indexid, @partitionnum, @frag, @page_count
 
    IF @@FETCH_STATUS < 0 BREAK
 
    SELECT
        @objectname = QUOTENAME(o.[name]),
        @schemaname = QUOTENAME(s.[name])
    FROM
        sys.objects AS o WITH (NOLOCK)
        JOIN sys.schemas as s WITH (NOLOCK)
        ON s.[schema_id] = o.[schema_id]
    WHERE
        o.[object_id] = @objectid
 
    SELECT
        @indexname = QUOTENAME([name])
    FROM
        sys.indexes WITH (NOLOCK)
    WHERE
        [object_id] = @objectid AND
        [index_id] = @indexid
 
    SELECT
        @partitioncount = count (*)
    FROM
        sys.partitions WITH (NOLOCK)
    WHERE
        [object_id] = @objectid AND
        [index_id] = @indexid
 
    -- Build the required statement dynamically based on options and index stats.
    SET @intentions =
        @schemaname + N'.' +
        @objectname + N'.' +
        @indexname + N':' + CHAR(13) + CHAR(10)
    SET @intentions =
        REPLACE(SPACE(LEN(@intentions)), ' ', '=') + CHAR(13) + CHAR(10) +
        @intentions
    SET @intentions = @intentions +
        N' FRAGMENTATION: ' + CAST(@frag AS nvarchar) + N'%' + CHAR(13) + CHAR(10) +
        N' PAGE COUNT: '    + CAST(@page_count AS nvarchar) + CHAR(13) + CHAR(10)
 
    IF @frag < @rebuild_frag_thresh BEGIN
        SET @intentions = @intentions +
            N' OPERATION: REORGANIZE' + CHAR(13) + CHAR(10)
        SET @command =
            N'ALTER INDEX ' + @indexname +
            N' ON ' + @schemaname + N'.' + @objectname +
            N' REORGANIZE; ' + 
            N' UPDATE STATISTICS ' + @schemaname + N'.' + @objectname + 
            N' ' + @indexname + ';'

    END
    IF @frag >= @rebuild_frag_thresh BEGIN
        SET @intentions = @intentions +
            N' OPERATION: REBUILD' + CHAR(13) + CHAR(10)
        SET @command =
            N'ALTER INDEX ' + @indexname +
            N' ON ' + @schemaname + N'.' +     @objectname +
            N' REBUILD'
    END
    IF @partitioncount > 1 BEGIN
        SET @intentions = @intentions +
            N' PARTITION: ' + CAST(@partitionnum AS nvarchar(10)) + CHAR(13) + CHAR(10)
        SET @command = @command +
            N' PARTITION=' + CAST(@partitionnum AS nvarchar(10))
    END
    IF @frag >= @rebuild_frag_thresh AND @fill_factor > 0 AND @fill_factor < 100 BEGIN
        SET @intentions = @intentions +
            N' FILL FACTOR: ' + CAST(@fill_factor AS nvarchar) + CHAR(13) + CHAR(10)
        SET @command = @command +
            N' WITH (FILLFACTOR = ' + CAST(@fill_factor AS nvarchar) + ')'
    END
 
    -- Execute determined operation, or report intentions
    IF @report_only = 0 BEGIN
        SET @intentions = @intentions + N' EXECUTING: ' + @command
        PRINT @intentions     
        EXEC (@command)
    END ELSE BEGIN
        PRINT @intentions
    END
 PRINT @command

END
 
-- Close and deallocate the cursor.
CLOSE partitions
DEALLOCATE partitions
 
GO

Para problemas de desempenho verificar primeiro os recursos como explicado aqui (com script). O Engenho & engenhocas tem aqui a sua versão. A afinação dos índices e actualização das estatísticas pode não resolver o problema por si só, mas apenas adiá-lo. Dicas sobre fragmentação de índices aqui.

Aqui estão os melhores scripts para manutenção de base de dados que jamais encontrei, os scripts de Ola Hallengren. Esta solução está no Github.

Com obter o nª de registos de cada tabela para todas as bases de dados de um servidor

Para ler

SET NOCOUNT ON;

CREATE TABLE #Databases
(DatabaseID   INT, 
 DatabaseName SYSNAME
);

CREATE TABLE #TableRows
(TableCatalog  SYSNAME, 
 TableSchema   SYSNAME, 
 TableName     SYSNAME,
 NrRows     Int
);

INSERT INTO #Databases
(DatabaseID, 
 DatabaseName
)
       SELECT database_id, 
              [name]
       FROM sys.databases
       WHERE [state] <> 6 /* ignore offline DBs */
             AND database_id > 4 /* ignore system DBs */


DECLARE @databaseID INT, @databaseName SYSNAME, @sql VARCHAR(MAX);

WHILE
(
    SELECT COUNT(*)
    FROM #Databases
) > 0
    BEGIN
        SELECT TOP 1 @databaseID = databaseID, 
                     @databaseName = databaseName
        FROM #Databases;

        SET @sql = 'INSERT INTO #TableRows SELECT DB_NAME(' + CONVERT(VARCHAR, @databaseID) + '), ISNULL(OBJECT_SCHEMA_NAME(TBL.object_id), ''dbo'') ''Schema'', TBL.name ''Table'', SUM(PART.rows) ''NrRows''
FROM ' + QUOTENAME(@databaseName) + '.sys.tables TBL
INNER JOIN ' + QUOTENAME(@databaseName) + '.sys.partitions PART ON TBL.object_id = PART.object_id
INNER JOIN ' + QUOTENAME(@databaseName) + '.sys.indexes IDX ON PART.object_id = IDX.object_id
AND PART.index_id = IDX.index_id
WHERE IDX.index_id < 2
GROUP BY TBL.object_id, TBL.name';

        EXEC (@sql);

        DELETE FROM #Databases
        WHERE databaseID = @databaseID;

    END;

SET NOCOUNT OFF;

SELECT *
FROM #TableRows A
ORDER BY A.TableCatalog, 
         A.TableSchema, 
         A.TableName;

DROP TABLE #TablesRows;
DROP TABLE #Databases;

Referência: https://www.brentozar.com/archive/2014/02/count-number-rows-table-sql-server/, https://stackoverflow.com/questions/13757387/getting-sql-server-cross-database-dependencies

SQL Server SQLCMD

Para ler

O SQLCMD é uma ferramenta de linha de comando que serve para executar comandos e scripts Transact-SQL em instâncias do SQL Server 2008 ou mais recente, podendo utilizar variáveis de ambiente e exportar os resultados para ficheiro. O SSMS também tem um modo SQLCMD que simula a capacidade do SQLCMD e aceita comandos que não fazem parte do T-SQL.

Ferramenta SQLCMD

Modo SQLCMD em SSMS

Exemplo de SQLCMD

:setvar DataDB Kairos
:setvar routineName spChronos
:setvar baseSchema dbo

USE $(DataDB);
GO

IF ('$(dbName)' NOT LIKE DB_NAME())
BEGIN
SET @sql = N'$(dbName) is not a valid database! Aborting definition of ' + QUOTENAME('$(procName)');
RAISERROR(@sql, 16, 127);
END

PRINT 'Creating the ' + QUOTENAME('$(routineName)') + ' Stored Procedure'

IF EXISTS(SELECT 1 FROM [INFORMATION_SCHEMA].[ROUTINES] WHERE [ROUTINE_SCHEMA] = '$(baseSchema)' AND [ROUTINE_NAME] = '$(routineName)' AND [ROUTINE_TYPE] = 'PROCEDURE')
BEGIN

-- Remove the old procedure so we can replace it
DROP PROCEDURE $(baseSchema).$(routineName);

END
GO

CREATE PROCEDURE $(baseSchema).$(routineName)
AS
DECLARE @GO BIT

SET @GO = 1

GO

PRINT '$(routineName) Stored Procedure created'
GO

Referência: Overview of the SQLCMD utility in SQL Server, Working with the SQL Server command line (sqlcmd)

Agregação: Grouping with cube

Para ler

Exemplo de utilização de agregação GROUP BY CUBE em T-SQL

drop table #AggregationTable

SELECT [IntegradoCRM], [AgenteAlteracao], [NomeEntidade], [Operacao], COUNT(*) AS NumeroRegistos
, GROUPING([IntegradoCRM]) 'isIntegradoCRMGroup'
, GROUPING([AgenteAlteracao]) 'isAgenteAlteracaoGroup'
, GROUPING([NomeEntidade]) 'isNomeEntidadeGroup'
, GROUPING([Operacao]) 'isOperacaoGroup'
, Grouping_ID ([IntegradoCRM], [AgenteAlteracao], [NomeEntidade], [Operacao]) 'isGroupingRow'
INTO #AggregationTable
  FROM [Pec_Stagingarea_to_CRM_Online].[pec].[Integracao]
  WHERE  IntegradoCRM = 0
  GROUP BY [IntegradoCRM], [AgenteAlteracao], [Operacao], [NomeEntidade]
  WITH CUBE;

select ISNULL([NomeEntidade], 'Total registos por processar'), SUM(NumeroRegistos) AS Numeroregistos
FROM #AggregationTable
WHERE isIntegradoCRMGroup = 1 AND isOperacaoGroup = 1 AND isAgenteAlteracaoGroup = 1
GROUP BY [NomeEntidade]
order by [NomeEntidade]

select ISNULL([NomeEntidade], 'Total'), AgenteAlteracao, SUM(NumeroRegistos) AS Numeroregistos
FROM #AggregationTable
WHERE isIntegradoCRMGroup = 1 AND isOperacaoGroup = 1 AND isAgenteAlteracaoGroup = 0
GROUP BY AgenteAlteracao, [NomeEntidade]
order by AgenteAlteracao, [NomeEntidade]

select ISNULL([NomeEntidade], 'Total'), AgenteAlteracao, [Operacao], SUM(NumeroRegistos) AS Numeroregistos
FROM #AggregationTable
WHERE isOperacaoGroup = 0 AND isAgenteAlteracaoGroup = 0 AND isIntegradoCRMGroup = 0
GROUP BY AgenteAlteracao, [NomeEntidade], [Operacao]
order by AgenteAlteracao, [NomeEntidade], [Operacao]


select [Operacao], SUM(NumeroRegistos) AS Numeroregistos
FROM #AggregationTable
WHERE isOperacaoGroup = 0 AND isAgenteAlteracaoGroup = 1 AND isNomeEntidadeGroup = 0 AND isIntegradoCRMGroup = 0
GROUP BY [Operacao]
GO

Referência: SELECT - GROUP BY- Transact-SQL

SQL Server -Contadores de desempenho

Para ler

select distinct counter_name from sys.dm_os_performance_counters order by counter_name

Select * from sys.dm_os_performance_counters
where counter_name = 'Lock Timeouts/sec'   --and instance_name = 'Extent'

select * from sys.dm_os_performance_counters
where counter_name in ('Target Server Memory (KB)', 'Total Server Memory (KB)', 'Batch Requests/sec')

SELECT request_session_id, COUNT (*) num_locks
FROM sys.dm_tran_locks
GROUP BY request_session_id
ORDER BY count (*) DESC

SELECT * FROM sys.configurations
GO

fonte: Simple SQL Server

CTEs e queries recursivas em SQL Server

Para ler

CTE é o acrónimo para Common Table Expressions.

As CTEs podem ser usadas para construir queries recursivas, na medida em que as CTEs podem ter referências para elas próprias. Apresenta-se, agora, a sintaxe básica de uma CTE para queries recursivas:

WITH cte_name (column_list) AS
(anchor_member
UNION ALL
recursive_member)
outer_query

A cláusula WITH é composta por duas queries ligadas pelo operador UNION ALL.
O primeiro operando do UNION ALL não faz referência à CTE (anchor_member). O segundo faz referência à CTE e representa a recursão (recursive_member).

Na primeira invocação da recursão, a referência à CTE representa o resultado da primeira query (anchor_member) e a segunda query (recursive_member) usa o resultado da primeira. Nas invocações seguintes, o sistema executa a segunda query repetidamente (recursive_member). A execução da segunda query termina quando o resultado da invocação anterior da query é vazio.
O operador UNION ALL junta os registos acumulados e os registos obtidos na última invocação. (UNION ALL significa que registos duplicados não são eliminados)

A outer_query especifica a query que usa a CTE para obter o resultado da união de ambos os membros do operador UNION ALL.

Exemplo para o Menu do Dia

CREATE TABLE [dbo].[MNU_tblMenuDia](
[ParagrafoID] [int] NOT NULL,
[ParagrafoPaiID] [int] NULL,
[Texto] [varchar](1024) NOT NULL,
[Ambito] [varchar](8) NOT NULL
)
GO

INSERT [dbo].[MNU_tblMenuDia] ([ParagrafoID],[ParagrafoPaiID],[Texto],[Ambito])VALUES(1,NULL,'Menu do Dia','All')
INSERT [dbo].[MNU_tblMenuDia] ([ParagrafoID],[ParagrafoPaiID],[Texto],[Ambito])VALUES(2,1,'Pratos de Carne','All')
INSERT [dbo].[MNU_tblMenuDia] ([ParagrafoID],[ParagrafoPaiID],[Texto],[Ambito])VALUES(7,4,'Pratos de Peixe','All')
INSERT [dbo].[MNU_tblMenuDia] ([ParagrafoID],[ParagrafoPaiID],[Texto],[Ambito])VALUES(3,2,'Almondegas com molho de tomate','Seg')
INSERT [dbo].[MNU_tblMenuDia] ([ParagrafoID],[ParagrafoPaiID],[Texto],[Ambito])VALUES(4,2,'Peru recheado','Ter')
INSERT [dbo].[MNU_tblMenuDia] ([ParagrafoID],[ParagrafoPaiID],[Texto],[Ambito])VALUES(8,7,'Solha gratinada','Seg')
INSERT [dbo].[MNU_tblMenuDia] ([ParagrafoID],[ParagrafoPaiID],[Texto],[Ambito])VALUES(9,7,'Bacalhau à senhor prior','Ter')
INSERT [dbo].[MNU_tblMenuDia] ([ParagrafoID],[ParagrafoPaiID],[Texto],[Ambito])VALUES(12,9,'Restaurante Nicolao','All')

A tabela MNU_tblMenuDia é composta por quatro colunas. A coluna Texto especifica o texto do menu; a coluna Ambito especifica a qualidade o texto, se específico de um dia da semana ou se geral.

Conteudo da tabela MNU_tblMenuDia
1 NULL Menu do Dia All
2 1 Pratos de Carne All
7 4 Pratos de Peixe All
3 2 Almondegas com molho de tomate Seg
4 2 Peru recheado Ter
8 7 Solha gratinada Seg
9 7 Bacalhau à senhor prior Ter
12 9 Restaurante Nicolao All

Apresenta-se, agora, a cláusula WITH que especifica a query para apresentação do Menu de Terça-feira:

WITH Menu(ParagrafoID, ParagrafoPaiID, Texto, Ambito, nivel) AS
(
SELECT ParagrafoID,
ParagrafoPaiID, Texto, Ambito,
0 as nivel
FROM
sin.[MNU_tblMenuDia]
WHERE ParagrafoPaiID IS NULL
UNION ALL
SELECT sin.[MNU_tblMenuDia].ParagrafoID,
sin.[MNU_tblMenuDia].ParagrafoPaiID, sin.[MNU_tblMenuDia].Texto, sin.[MNU_tblMenuDia].Ambito,
nivel + 1
FROM
sin.[MNU_tblMenuDia] INNER JOIN Menu ON sin.[MNU_tblMenuDia].ParagrafoPaiID = Menu.ParagrafoID
)
select distinct ParagrafoID, ParagrafoPaiID, Texto, nivel, Ambito
from Menu
where Ambito in ('All', 'Ter')
GO

O SELECT apresenta o seguinte resultado
1 NULL Menu do Dia 0 All
2 1 Pratos de Carne 1 All
4 2 Peru recheado 2 Ter
7 4 Pratos de Peixe 3 All
9 7 Bacalhau à senhor prior 4 Ter
12 9 Restaurante Nicolao 5 All

Apresenta-se, de seguida, a cláusula WITH que especifica a query para apresentação do Menu de Segunda-feira:

WITH Menu(ParagrafoID, ParagrafoPaiID, Texto, Ambito, nivel) AS
(
SELECT ParagrafoID,
ParagrafoPaiID, Texto, Ambito,
0 as nivel
FROM
sin.[MNU_tblMenuDia]
WHERE ParagrafoPaiID IS NULL
UNION ALL
SELECT sin.[MNU_tblMenuDia].ParagrafoID,
sin.[MNU_tblMenuDia].ParagrafoPaiID, sin.[MNU_tblMenuDia].Texto, sin.[MNU_tblMenuDia].Ambito,
nivel + 1
FROM
sin.[MNU_tblMenuDia] INNER JOIN Menu ON sin.[MNU_tblMenuDia].ParagrafoPaiID = Menu.ParagrafoID
)
select distinct Texto, nivel
from Menu
where Ambito in ('All', 'Seg') order by nivel
GO

O SELECT apresenta o seguinte resultado
Menu do Dia 0
Pratos de Carne 1
Almondegas com molho de tomate 2
Pratos de Peixe 3
Solha gratinada 4
Restaurante Nicolao 5

Restrições a uma CTE para queries recursivas:
• A definição da CTE deve ter pelo menos dois SELECTs (anchor_member & recursive_member) combinados pelo operador UNION ALL.
• Ambos os membros têm de ter o mesmo número de colunas (consequência directa do uso do UNION ALL).
• As colunas de ambos os membros têm de ser dos mesmos tipos de dados.
• A cláusula FROM do membro recursivo só pode referir uma vez o nome da CTE.
• Não são permitidas as opções seguintes no membro recursivo:
* SELECT DISTINCT
* GROUP BY
* HAVING
* Aggregation Functions
* TOP
* Subquerires (Salientar que a única operação de JOIN permitida neste membro é INNER JOIN.)

SQL Server Object - MySQL Linked Server

Para ler

Pré-requisitos
Instalação do MySQL Connector/ODBC

Linked Server

O objecto Linked Server permite vincular o SQL Server a um servidor de dados externo. Este objecto do SQL Server permite fazer queries distribuídas e heterogéneas sobre fontes de dados OLE DB. Depois do Linked Server ser criado pela store procedure de sistema sp_addlinkedserver, as queries distribuídas podem ser executadas nesse servidor.

/****** Object: LinkedServer [MYSQL-DEV-ENV] ******/
EXEC master.dbo.sp_addlinkedserver @server = N'MYSQL-DEV-ENV', @srvproduct=N'MySQL', @provider=N'MSDASQL', @provstr=N'Driver={MySQL ODBC 5.3 ANSI Driver};database=;option=134217728;server=;port=3306;uid='

/* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'MYSQL-DEV-ENV',@useself=N'False',@locallogin=NULL,@rmtuser=N'',@rmtpassword='########'

GO

Consulta com a função OpenQuery

SELECT * FROM OPENQUERY(MYSQL-DEV-ENV,'SELECT * FROM test_mysql.Profile WHERE Name = '''profilename''' LIMIT 0, 1000')
GO

No caso da função OpenQuery, o motor de SQL não vai tentar classificar a query nem verificar o que vai fazer, a query é pura e simplesmente enviada para o servidor de MySQL. O Parsing e a geração do plano de execução, bem como a aplicação de filtros, são executados no servidor remoto.

De uma forma geral, o OpenQuery é mais rápido que o Linked Server porque o motor de SQL não vai partir a query antes de a enviar para o MySQL, mas é mais indicado para invocações feitas com menos frequência.

Mais informação sobre acesso a fontes de dados remotas aqui.

Invocação de store procedure MySQL

exec ('CALL test_mysql.usp_Conference()') at [MYSQL-DEV-ENV]
GO

Referências: Create a linked server to MySQL from SQL Server, Access MySQL data from SQL Server via a Linked Server

SQL Server - Fusos horários e funções datetime

Para ler

Diferenças e Conversão de fusos horários

SELECT SYSDATETIME()
,SYSDATETIMEOFFSET()
,SYSUTCDATETIME()
,CURRENT_TIMESTAMP
,GETDATE()
,GETUTCDATE()
,GETDATE() AT TIME ZONE 'UTC' AT TIME ZONE 'Greenwich Standard Time' AS Date_TimeZoneGMT
GO

Fusos Horários

SELECT * FROM sys.time_zone_info
GO

Referência: AT TIME ZONE (Transact-SQL)

Geração de SQL dinâmico com o comando EXEC

Para ler

O comando EXEC[UTE] executa uma string T-SQL ou um módulo. Entende-se aqui por módulo:

system stored procedure,
user-defined stored procedure,
scalar-valued user-defined function,
extended stored procedure.

Tem privilégios para a execução de um módulo a conta de utilizador que o criou (owner) ou aquela que detém permissão de execução (EXECUTE) sobre o módulo.

Sintaxe para execução de módulos

[EXEC[UTE]] [@return_status =] {module_name | @module_name_var} {[[@parameter1 =] value | [@parameter1=] @variable [OUTPUT]] | DEFAULT}.. [WITH RECOMPILE]

Sintaxe para execução de string T-SQL

EXEC[UTE] ({<string variable> |'<literal command string>'})

O comando EXEC permite construir ou invocar comandos dinamicamente em tempo de execução.

Há, no entanto, alguns factores a ter em consideração:

O comando EXEC corre num contexto diferente do código que o invoca com excepção do @@ROWCOUNT (i.e., o código que invoca o EXEC não pode referenciar variáveis instanciadas no contexto do EXEC e da mesma maneira o EXEC não pode referenciar variáveis do código que o invoca depois de já incorporadas na string do EXEC)
O EXEC corre no contexto de segurança do utilizador corrente e não no contexto de segurança do objecto que o invoca.
O EXEC corre no contexto da transacção do objecto que o invoca.
Uma concatenação que implique a chamada a uma função tem de ser executada sobre a string T-SQL antes de invocar o EXEC propriamente dito
O EXEC não pode ser usado numa user-defined function.

Exemplos

Exemplo de execução de string T-SQL

DECLARE @InVar varchar(200)

SET @InVar = 'DECLARE @OutVar varchar(50) SELECT @OutVar = Nome FROM PessoaSegura WHERE PessoaSeguraID = 1 SELECT ''Nome obtido: '' + @OutVar'

EXEC (@Invar)

Exemplo de execução de string T-SQL e @@ROWCOUNT

EXEC('SELECT * FROM Cliente')
SELECT 'O Rowcount é ' + CAST(@@ROWCOUNT as varchar)

Exemplo de execução de string T-SQL com chamada a função

DECLARE @NumberOfLetters AS int

SET @NumberOfLetters = 3

DECLARE @str AS varchar(255)

SET @str = 'SELECT LEFT(''Ola mundo!'',' + CAST(@NumberOfLetters AS varchar) + ')'

EXEC(@str)

Exemplo de execução de módulos

SET NOCOUNT ON

CREATE TABLE #CurrentHistory (
RowID int IDENTITY(1, 1), 
ProcedureToRun varchar(64)
)


DECLARE @NumberRecords int, @RowCount int, @return_status int
DECLARE @ProcedureToRun varchar(64), @ErrorMsg varchar(104)

INSERT INTO #CurrentHistory (ProcedureToRun)
select [name] from .sys.all_objects
where type = 'P'
and name like 'Procedures a encontrar'


SELECT @NumberRecords = @@ROWCOUNT, @RowCount = 1

WHILE @RowCount <= @NumberRecords
BEGIN

SELECT @ProcedureToRun = ProcedureToRun 
FROM #CurrentHistory
WHERE RowID = @RowCount

EXEC @return_status = @ProcedureToRun

IF @return_status = 1
BEGIN

SELECT @ErrorMsg = 'Error on ' + @ProcedureToRun + ' execution. Its work was not completed. Please verify.'

RAISERROR (@ErrorMsg,16, 1)

END

SET @RowCount = @RowCount + 1

END

DROP TABLE #CurrentHistory

Referência: EXECUTE (Transact-SQL)

Relacionado: Difference between sys.sp_execute sys.sp_executesql