Mitos e Verdades – ORDER BY + TOP/OFFSET/FOR XML dentro de Table Expressions

Fui questionado recentemente por um dos alunos de um curso que ministrei de TSQL sobre o porque de ser necessário o uso da cláusula TOP (n), OFFSET ou FOR XML PATH() dentro das expressões de tabela (VIEWs, TVFs, Derived Tables e CTEs) para que seja possível que o resultado execute com sucesso e não retorne erros.

Acredito que esse é um assunto interessante e decidi realizar alguns testes para entender melhor o funcionamento dessa issue dentro do SQL Server.


O TSQL é uma linguagem SET BASED (baseada em conjuntos – teoria Matemática dos Conjuntos). É importante entender que um conjunto, por sua natureza, não possui retorno ordenado.

Quando executado um comando de SELECT fazendo o uso da cláusula ORDER BY você demanda ao SQL Server uma das duas opções abaixo:

  1. Quero definir a ordenação das linhas que serão devolvidas através do comando de SELECT;
  2. Quero definir quais linhas serão devolvidas a partir de uma expressão TOP ou OFFSET;

É necessário entender também que as Expressões de Tabela (Table Expressions) nada mais fazem além de dar a uma consulta uma tabela virtual como origem (ou uma das origens). Essa tabela virtual será criada em tempo de execução através do comando de SELECT localizado dentro da Expressão de Tabela.

Isso quer dizer que o SQL Server irá primeiro realizar a consulta que está dentro da Query localizada na Table Expression e, a partir dessa tabela virtual que será gerada, iniciará a execução da query de fora da Table Expression (Outer query).

Dessa forma é possível entender o porquê de não podermos usar uma Cláusula ORDER BY dentro de uma Table Expression: O Retorno se torna não determinístico. O SQL Server não pode passar por cima da regra que define que um conjunto de OUTPUT não pode possuir ordenação.

Muitas pessoas acreditam que é possível “burlar” essa funcionalidade by design do SQL Server usando a cláusula TOP. Será possível que você veja abaixo quais as verdades e mentiras por detrás disso.


Use para os testes o banco de dados AdventureWorks2014.

Primeiramente veja o plano de execução estimado da query abaixo:

USE AdventureWorks2014
GO

SELECT TOP 10 
 BusinessEntityID,
 FirstName,
 MiddleName,
 LastName 
FROM Person.Person
ORDER BY FirstName DESC

01

Observe que o operador SORT foi inserido no plano de execução. Isso quer dizer que o SQL Server está realizando a ordenação do retorno da consulta que fora executada.

Após isso, crie uma VIEW denominada [vw_Person_Names] utilizando a query abaixo e crie um plano de execução estimado para o comando SELECT após sua criação.

USE AdventureWorks2014
GO

CREATE VIEW vw_Person_Names
AS
SELECT TOP 10 
 BusinessEntityID,
 FirstName,
 MiddleName,
 LastName 
FROM Person.Person
ORDER BY FirstName DESC

GO

SELECT *
FROM vw_Person_Names

01

Observe que o plano de execução estimado será idêntico ao execution plan da query anterior. Isso ocorre porque o retorno do SELECT é determinístico graças ao comando TOP (n) na query. Dessa maneira, estamos usando a cláusula ORDER BY em uma de suas funções no TSQL: definir quais linhas serão devolvidas a partir de uma expressão TOP, OFFSET ou FOR XML PATH().

Neste caso, é possível usar corretamente o comando ORDER BY dentro de uma Table Expression – Seja ela uma VIEW, Table-Valued User-Definid Function (TVF), Common Table Expression (CTE) ou Derived Table.

É importante observar um outro ponto. Algumas vezes, quando o Query Optimizer decide usar um índice em cujo os registros já estão ordenados, o operador SORT não é usado na consulta, porém, se você selecionar o operador de Index Scan com o botão direito e for até a opção Properties, será possível observar que a opção Ordered será TRUE, ou seja, o retorno do operador de busca no index já está ordenado.

É o que será demonstrado no caso abaixo:

USE AdventureWorks2014
GO

SELECT TOP 10 
 BusinessEntityID,
 FirstName,
 MiddleName,
 LastName 
FROM Person.Person
ORDER BY BusinessEntityID DESC

0001

Observe que o operador SORT não foi usado. Agora veja a aba de propriedades do operador Clustered Index Scan:

001


Agora você irá descobrir o outro lado da moeda acerca do ORDER BY sendo usado dentro de uma Expressão de Tabela que é quando você tentar usá-lo para definir qual a ordenação das linhas que serão devolvidas.

Use a query a baixo para realizar a análise do seu plano de execução:

USE AdventureWorks2014
GO

SELECT BusinessEntityID,
 FirstName,
 MiddleName,
 LastName 
FROM Person.Person
ORDER BY FirstName DESC

02

Observe que o operador SORT foi novamente utilizado para realizar a ordenação do retorno da consulta acima.

Como já fora visto no início do post, se tentarmos usar essa mesma query dentro de uma Table Expression (uma view, por exemplo) um erro será retornado pois isso faria com que o SQL Server “fraudasse” uma das regras da teoria matemática dos conjuntos: Não há ordenação dos dados dentro de um SET.

Mas, é possível “burlar” este erro do SQL Server adicionando ao comando localizado dentro da VIEW a cláusula TOP 100 PERCENT. Veja:

USE AdventureWorks2014
GO

CREATE VIEW vw_Person_Names2
AS 
SELECT TOP 100 PERCENT
 BusinessEntityID,
 FirstName,
 MiddleName,
 LastName 
FROM Person.Person
ORDER BY FirstName DESC

GO

SELECT *
FROM vw_Person_Names2

A VIEW denominada [vw_Person_names2] foi criada com sucesso. Porém, observe um detalhe ao visualizar o plano de execução criado a partir do seu SELECT:

03

O operador SORT não é apresentado. Observe também abaixo na opção Ordered dentro das propriedades do operador Index Scan e veja que realmente a ordenação não ocorreu. O valor é FALSE:

04

Isso ocorre porque o SQL Server percebe que está ocorrendo através do comando TOP 100 PERCENT o retorno de todos os dados do conjunto e, por ser um retorno não determinístico, o Query Optmizer não pode desrespeitar a regra de não ordenação do conjunto.

Logo, ao fazer isso que foi demonstrado o retorno de sua Table Expression (neste caso da VIEW) não é ordenado mesmo com o comando ORDER BY estando presente na query. Veja abaixo:

06
Comando SELECT executado na VIEW.
05
Comando SELECT executado na Query.

Para contornar isso, algumas pessoas tentam novamente enganar o Query Optmizer usando o operador TOP (n) PERCENT onde (n) possui valor de 99.99. Isso quer dizer que você está pedindo ao SQL Server 99,99% dos dados do retorno.

Isso funcionará para forçar o SQL Server a trazer o resultado da consulta a partir de uma Table Expression ordenado, entretanto, isso pode gerar problemas.

USE AdventureWorks2014
GO

CREATE VIEW vw_Person_Names3
AS 

SELECT TOP 99.99 PERCENT
 BusinessEntityID,
 FirstName,
 MiddleName,
 LastName 
FROM Person.Person
ORDER BY FirstName DESC

GO

SELECT *
FROM vw_Person_Names3

Ao criar uma nova VIEW denominada [vw_Person_names3] através do comando acima fora executado um COUNT em ambos objetos (tabela [Person].[Person] e view [dbo].[vw_Person_names3]). Veja o resultado abaixo:

07

Ocorreu perda de registros (neste caso um somente) para o retorno em cuja query fora usado o TOP 99.99 PERCENT.


Por esses motivos, sempre que o resultado de uma Table Expression for não determinístico é recomentado a utilização da cláusula ORDER BY na query de fora da Table Expression (Outer query) evitando, dessa forma, resultados errôneos.

Anúncios

Capturando a Collation de Todas as bases e Colunas da Instância

Recentemente fui incumbido de realizar um levantamento das collations das bases de dados e colunas do sistema transacional da ANEEL para que pudéssemos mensurar a complexidade de uma migração de Collation.

Com base nisso, desenvolvi uma query usando as DMVs do SQL Server que busca em cada um dos bancos de dados de uma instância sua collation e também quantidade de colunas que cada base possui com collation divergente da collation da base de dados.


DECLARE @informations TABLE (
 
 database_name VARCHAR (500),
 db_collation_name VARCHAR(500),
 
 type_desc VARCHAR(500),
 
 tb_name VARCHAR(500),
 tb_columns_name VARCHAR(500),
 tb_column_collation_name VARCHAR(500),

 vw_name VARCHAR(500),
 vw_columns_name VARCHAR(500),
 vw_column_collation_name VARCHAR(500),

 tvf_name VARCHAR(500),
 tvf_columns_name VARCHAR(500),
 tvf_column_collation_name VARCHAR(500),

 itf_name VARCHAR(500),
 itf_columns_name VARCHAR(500),
 itf_column_collation_name VARCHAR(500),

 tt_name VARCHAR(500),
 tt_columns_name VARCHAR(500),
 tt_column_collation_name VARCHAR(500)
)

INSERT INTO @informations 

EXEC sp_msforeachdb '
 SELECT (SELECT DISTINCT name FROM sys.databases WHERE name = ''?'') AS database_name,
 (SELECT DISTINCT collation_name FROM sys.databases WHERE name = ''?'') AS db_collation_name,
 
 ob.type_desc,
 
 tb.name AS tb_name,
 co_tb.name AS tb_columns_name,
 co_tb.collation_name AS tb_column_collation_name, 
 
 vw.name AS vw_name,
 co_vw.name AS vw_columns_name,
 co_vw.collation_name AS vw_column_collation_name, 
 
 CASE WHEN ob.type = ''TF'' THEN ob.name ELSE NULL END AS tvf_name,
 co_tvf.name AS tvf_columns_name,
 co_tvf.collation_name AS tvf_column_collation_name, 
 
 CASE WHEN ob.type = ''IF'' THEN ob.name ELSE NULL END AS itf_name,
 co_itf.name AS itf_columns_name,
 co_itf.collation_name AS itf_column_collation_name,

 CASE WHEN ob.type = ''TT'' THEN ob.name ELSE NULL END AS tt_name,
 co_tt.name AS tt_columns_name,
 co_tt.collation_name AS tt_column_collation_name

 FROM [?].sys.all_objects ob
 LEFT JOIN [?].sys.tables tb ON tb.object_id = ob.object_id
 LEFT JOIN [?].sys.all_views vw ON vw.object_id = ob.object_id
 LEFT JOIN [?].sys.all_columns co_tb ON co_tb.object_id = tb.object_id AND co_tb.collation_name IS NOT NULL
 LEFT JOIN [?].sys.all_columns co_vw ON co_vw.object_id = vw.object_id AND co_vw.collation_name IS NOT NULL
 LEFT JOIN [?].sys.all_columns co_tvf ON ob.type = ''TF'' and co_tvf.object_id = ob.object_id AND co_tvf.collation_name IS NOT NULL
 LEFT JOIN [?].sys.all_columns co_itf ON ob.type = ''IF'' and co_itf.object_id = ob.object_id AND co_itf.collation_name IS NOT NULL
 LEFT JOIN [?].sys.all_columns co_tt ON ob.type = ''TT'' and co_tt.object_id = ob.object_id AND co_tt.collation_name IS NOT NULL
 WHERE ob.type in (''U'',''V'',''TF'',''IF'',''TT'')'

SELECT --database name and collation
 database_name,
 db_collation_name,
 
 --verify amount of tables, table text columns and table text columns with collation difference of database
 COUNT( DISTINCT tb_name) AS qtd_tables,
 COUNT(tb_columns_name) AS qtd_table_columns,
 SUM(CASE WHEN db_collation_name <> tb_column_collation_name THEN 1 ELSE 0 END) AS qtd_table_columns_with_different_collation,
 
 --verify amount of views, view text columns and view text columns with collation difference of database
 COUNT(DISTINCT vw_name) AS qtd_views,
 COUNT(vw_columns_name) AS qtd_view_columns,
 SUM(CASE WHEN db_collation_name <> vw_column_collation_name THEN 1 ELSE 0 END) AS qtd_view_columns_with_different_collation,

 --verify amount of table valued functions, table valued function text columns and table valued function text columns with collation difference of database
 COUNT(tvf_name) AS qtd_tvf,
 COUNT(tvf_columns_name) AS qtd_tvf_columns,
 SUM(CASE WHEN db_collation_name <> tvf_column_collation_name THEN 1 ELSE 0 END) AS qtd_tvf_columns_with_different_collation,

 --verify amount of inline table valued functions, inline table valued function text columns and inline table valued function text columns with collation difference of database
 COUNT(itf_name) AS qtd_itf,
 COUNT(itf_columns_name) AS qtd_itf_columns,
 SUM(CASE WHEN db_collation_name <> itf_column_collation_name THEN 1 ELSE 0 END) AS qtd_itf_columns_with_different_collation,
 
 --verify amount of table types, table type text columns and table type text columns with collation difference of database
 COUNT(tt_name) AS qtd_tt,
 COUNT(tt_columns_name) AS qtd_tt_columns,
 SUM(CASE WHEN db_collation_name <> tt_column_collation_name THEN 1 ELSE 0 END) AS qtd_tt_columns_with_different_collation

FROM @informations
WHERE database_name NOT IN ('master','tempdb','model','msdb')
GROUP BY database_name,
 db_collation_name

Caso você queira saber quais os nomes das colunas cujo Collation diverge da collation da base de dados para cada objeto, basta usar a query abaixo:

DECLARE @informations TABLE (
 
 database_name VARCHAR (500),
 db_collation_name VARCHAR(500),
 
 type_desc VARCHAR(500),
 
 tb_name VARCHAR(500),
 tb_columns_name VARCHAR(500),
 tb_column_collation_name VARCHAR(500),

 vw_name VARCHAR(500),
 vw_columns_name VARCHAR(500),
 vw_column_collation_name VARCHAR(500),

 tvf_name VARCHAR(500),
 tvf_columns_name VARCHAR(500),
 tvf_column_collation_name VARCHAR(500),

 itf_name VARCHAR(500),
 itf_columns_name VARCHAR(500),
 itf_column_collation_name VARCHAR(500),

 tt_name VARCHAR(500),
 tt_columns_name VARCHAR(500),
 tt_column_collation_name VARCHAR(500)
)

INSERT INTO @informations 

EXEC sp_msforeachdb '
 SELECT (SELECT DISTINCT name FROM sys.databases WHERE name = ''?'') AS database_name,
 (SELECT DISTINCT collation_name FROM sys.databases WHERE name = ''?'') AS db_collation_name,
 
 ob.type_desc,
 
 tb.name AS tb_name,
 co_tb.name AS tb_columns_name,
 co_tb.collation_name AS tb_column_collation_name, 
 
 vw.name AS vw_name,
 co_vw.name AS vw_columns_name,
 co_vw.collation_name AS vw_column_collation_name, 
 
 CASE WHEN ob.type = ''TF'' THEN ob.name ELSE NULL END AS tvf_name,
 co_tvf.name AS tvf_columns_name,
 co_tvf.collation_name AS tvf_column_collation_name, 
 
 CASE WHEN ob.type = ''IF'' THEN ob.name ELSE NULL END AS itf_name,
 co_itf.name AS itf_columns_name,
 co_itf.collation_name AS itf_column_collation_name,

 CASE WHEN ob.type = ''TT'' THEN ob.name ELSE NULL END AS tt_name,
 co_tt.name AS tt_columns_name,
 co_tt.collation_name AS tt_column_collation_name

 FROM [?].sys.all_objects ob
 LEFT JOIN [?].sys.tables tb ON tb.object_id = ob.object_id
 LEFT JOIN [?].sys.all_views vw ON vw.object_id = ob.object_id
 LEFT JOIN [?].sys.all_columns co_tb ON co_tb.object_id = tb.object_id AND co_tb.collation_name IS NOT NULL
 LEFT JOIN [?].sys.all_columns co_vw ON co_vw.object_id = vw.object_id AND co_vw.collation_name IS NOT NULL
 LEFT JOIN [?].sys.all_columns co_tvf ON ob.type = ''TF'' and co_tvf.object_id = ob.object_id AND co_tvf.collation_name IS NOT NULL
 LEFT JOIN [?].sys.all_columns co_itf ON ob.type = ''IF'' and co_itf.object_id = ob.object_id AND co_itf.collation_name IS NOT NULL
 LEFT JOIN [?].sys.all_columns co_tt ON ob.type = ''TT'' and co_tt.object_id = ob.object_id AND co_tt.collation_name IS NOT NULL
 WHERE ob.type in (''U'',''V'',''TF'',''IF'',''TT'')'


SELECT * 
FROM (
 SELECT --database name and collation
 database_name,
 db_collation_name,
 
 --verify name of tables, table text columns and table text columns with collation difference of database
 tb_name AS table_name,
 tb_columns_name AS table_column_name,
 CASE WHEN db_collation_name <> tb_column_collation_name THEN tb_column_collation_name ELSE NULL END AS table_column_collation,
 
 --verify name of views, view text columns and view text columns with collation difference of database
 vw_name AS view_name,
 vw_columns_name AS view_column_name,
 CASE WHEN db_collation_name <> vw_column_collation_name THEN vw_column_collation_name ELSE NULL END AS view_column_collation,

 --verify name of table valued functions, table valued function text columns and table valued function text columns with collation difference of database
 tvf_name AS tvf_name,
 tvf_columns_name AS tvf_column_name,
 CASE WHEN db_collation_name <> tvf_column_collation_name THEN tvf_column_collation_name ELSE NULL END AS tvf_column_collation,

 --verify name of inline table valued functions, inline table valued function text columns and inline table valued function text columns with collation difference of database
 itf_name AS itf_name,
 itf_columns_name AS itf_column_name,
 CASE WHEN db_collation_name <> itf_column_collation_name THEN itf_column_collation_name ELSE NULL END AS itf_column_collation,
 
 --verify name of table types, table type text columns and table type text columns with collation difference of database
 tt_name AS tt_name,
 tt_columns_name AS tt_column_name,
 CASE WHEN db_collation_name <> tt_column_collation_name THEN tt_column_collation_name ELSE NULL END AS tt_column_collation

 FROM @informations
) AS detail_infornmations
WHERE database_name NOT IN ('master','tempdb','model','msdb')
 AND (table_column_collation IS NOT NULL 
 OR view_column_collation IS NOT NULL
 OR tvf_column_collation IS NOT NULL
 OR itf_column_collation IS NOT NULL
 OR tt_column_collation IS NOT NULL)