Utilizar funções de classificação e de conjuntos de linhas

As funções de classificação e conjunto de linhas não são funções escalares porque não retornam um único valor. Essas funções aceitam um conjunto de linhas como entrada e retornam um conjunto de linhas como saída.

Funções de classificação

As funções de classificação permitem que você execute cálculos em um conjunto de linhas definido pelo usuário. Essas funções incluem funções de classificação, deslocamento, agregação e distribuição.

Este exemplo usa a função RANK para calcular uma classificação com base no ListPrice, com o preço mais alto classificado como 1:

SELECT TOP 100 ProductID, Name, ListPrice,
RANK() OVER(ORDER BY ListPrice DESC) AS RankByPrice
FROM Production.Product AS p
ORDER BY RankByPrice;

Os resultados da consulta podem ser semelhantes a:

OVER

Você pode usar a cláusula OVER para definir partições ou agrupamentos dentro dos dados. Por exemplo, a consulta a seguir estende o exemplo anterior para calcular classificações baseadas em preços para produtos dentro de cada categoria.

SELECT c.Name AS Category, p.Name AS Product, ListPrice,
  RANK() OVER(PARTITION BY c.Name ORDER BY ListPrice DESC) AS RankByPrice
FROM Production.Product AS p
JOIN Production.ProductCategory AS c
ON p.ProductCategoryID = c.ProductcategoryID
ORDER BY Category, RankByPrice;

Os resultados dessa consulta podem ter esta aparência:

Funções de Rowset

As funções de conjunto de linhas retornam uma tabela virtual que pode ser usada na cláusula FROM como uma fonte de dados. Essas funções assumem parâmetros específicos para a própria função de conjuntos de linhas. Eles incluem OPENDATASOURCE, OPENQUERY, OPENROWSET, OPENXML e OPENJSON.

As funções OPENDATASOURCE, OPENQUERY e OPENROWSET permitem que você passe uma consulta para um servidor de banco de dados remoto. Em seguida, o servidor remoto retornará um conjunto de linhas de resultados. Por exemplo, a consulta a seguir usa OPENROWSET para obter os resultados de uma consulta de uma instância do SQL Server chamada SalesDB.

SELECT a.*
FROM OPENROWSET('SQLNCLI', 'Server=SalesDB;Trusted_Connection=yes;',
    'SELECT Name, ListPrice
    FROM AdventureWorks.Production.Product') AS a;

Para usar servidores remotos, você deve habilitar algumas opções avançadas na instância do SQL Server em que você está executando a consulta.

As funções OPENXML e OPENJSON permitem consultar dados estruturados no formato XML ou JSON e extrair valores em um conjunto de linhas tabular.

Uma exploração detalhada das funções de conjunto de linhas está além do escopo deste módulo. Para obter mais informações, consulte a documentação de referência doTransact-SQL.