Utilizar funções escalares

3 minutos

As funções escalares retornam um único valor e geralmente funcionam em uma única linha de dados. O número de valores de entrada que elas assumem podem ser zero (por exemplo, GETDATE), um (por exemplo, UPPER) ou vários (por exemplo, ROUND). Como as funções escalares sempre retornam um único valor, elas podem ser usadas em qualquer lugar e um único valor (o resultado) é necessário. Elas são usadas com mais frequência em cláusulas SELECT e predicados de cláusula WHERE. Elas também podem ser usadas na cláusula SET de uma instrução UPDATE.

As funções escalares internas podem ser organizadas em várias categorias, como cadeia de caracteres, conversão, lógica, matemática e outras. Este módulo vai ver algumas funções escalares comuns.

Dentre as considerações ao usar funções escalares estão:

Determinismo:se a função retornar o mesmo valor para o mesmo estado de entrada e banco de dados sempre que for chamada, dizemos que ela é determinística. Por exemplo, ROUND(1.1, 0) sempre retorna o valor 1.0. Muitas funções internas são não determinísticas. Por exemplo, GETDATE() retorna a data e hora atuais. Os resultados de funções não determinísticas não podem ser indexados, o que afeta a capacidade do processador de consultas de criar um bom plano para executar a consulta.
Ordenação:ao usar funções que manipulam dados de caracteres, qual ordenação será usada? Algumas funções usam a ordenação (ordem de classificação) do valor de entrada, outras usam a colagem do banco de dados se não for fornecida nenhuma ordenação de entrada.

Exemplos de função escalar

No momento da redação deste manual, a SQL Server Technical Documentation listava mais de 200 funções escalares que abrangem várias categorias, incluindo:

Funções de configuração
Funções de conversão
Funções de cursor
Funções de data e hora
Funções matemáticas
Funções de metadados
Funções de segurança
Funções de cadeia de caracteres
Funções do sistema
Funções estatísticas de sistema
Funções de texto e imagem

Não há tempo suficiente neste curso para descrever cada função, mas os exemplos a seguir mostram algumas funções comumente usadas.

O exemplo hipotético a seguir usa várias funções de data e hora:

SELECT  SalesOrderID,
    OrderDate,
        YEAR(OrderDate) AS OrderYear,
        DATENAME(mm, OrderDate) AS OrderMonth,
        DAY(OrderDate) AS OrderDay,
        DATENAME(dw, OrderDate) AS OrderWeekDay,
        DATEDIFF(yy,OrderDate, GETDATE()) AS YearsSinceOrder
FROM Sales.SalesOrderHeader;

Abaixo são mostrados resultados parciais:

SalesOrderID

OrderDate

OrderYear

OrderMonth

OrderDay

OrderWeekDay

YearsSinceOrder

71774

2008-06-01T00:00:00

2008

Junho

Sunday

...

O exemplo a seguir inclui algumas funções matemáticas:

SELECT TaxAmt,
       ROUND(TaxAmt, 0) AS Rounded,
       FLOOR(TaxAmt) AS Floor,
       CEILING(TaxAmt) AS Ceiling,
       SQUARE(TaxAmt) AS Squared,
       SQRT(TaxAmt) AS Root,
       LOG(TaxAmt) AS Log,
       TaxAmt * RAND() AS Randomized
FROM Sales.SalesOrderHeader;

Resultados parciais:

TaxAmt

Arredondado

Piso

Ceiling

Quadrado

Root

Registro

Aleatória

70.4279

70.0000

71.0000

4960.089098

8.392133221

4.254589491

28.64120429

...

O exemplo a seguir usa algumas funções de cadeia de caracteres:

SELECT  CompanyName,
        UPPER(CompanyName) AS UpperCase,
        LOWER(CompanyName) AS LowerCase,
        LEN(CompanyName) AS Length,
        REVERSE(CompanyName) AS Reversed,
        CHARINDEX(' ', CompanyName) AS FirstSpace,
        LEFT(CompanyName, CHARINDEX(' ', CompanyName)) AS FirstWord,
        SUBSTRING(CompanyName, CHARINDEX(' ', CompanyName) + 1, LEN(CompanyName)) AS RestOfName
FROM Sales.Customer;

Resultados parciais:

CompanyName

UpperCase

LowerCase

Comprimento

Reversed

FirstSpace

FirstWord

RestOfName

Uma Loja de Bicicletas

UMA LOJA DE BICICLETAS

uma loja de bicicletas

erotS ekiB A

Loja de bicicletas

Progressive Sports

ESPORTES PROGRESSIVOS

stropS evissergorP

Progressivo

Esportes

Advanced Bike Components

COMPONENTES DE BICICLETAS AVANÇADOS

componentes de bicicletas avançados

stnenopmoC ekiB decnavdA

Avançado

Componentes de bicicleta

...

Funções lógicas

Outra categoria de funções permite determinar qual dos vários valores deve ser retornado. As funções lógicas avaliam uma expressão de entrada e retornam um valor apropriado com base no resultado.

IIF

A função IIF avalia uma expressão de entrada booleana e retorna um valor especificado se a expressão for avaliada como Truee um valor alternativo se a expressão for avaliada como False.

Por exemplo, considere a consulta a seguir, que avalia o tipo de endereço de um cliente. Se o valor for "Escritório Principal", a expressão retornará "Cobrança". Para todos os outros valores de tipo “endereço”, a expressão retorna "Mailing".

SELECT AddressType,
      IIF(AddressType = 'Main Office', 'Billing', 'Mailing') AS UseAddressFor
FROM Sales.CustomerAddress;

Os resultados parciais dessa consulta podem ter a seguinte aparência:

AddressType

UseAddressFor

Escritório Principal

Cobrança

Remessa

Postagem

...

CHOOSE

A função CHOOSE avalia uma expressão integral e retorna o valor correspondente de uma lista com base em sua posição ordinal (baseada em 1).

SELECT SalesOrderID, Status,
CHOOSE(Status, 'Ordered', 'Shipped', 'Delivered') AS OrderStatus
FROM Sales.SalesOrderHeader;

Os resultados dessa consulta podem ter a seguinte aparência:

SalesOrderID

Status

OrderStatus

1234

Entregue

1235

Enviado

1236

Enviado

1237

Encomendado

...

Utilizar funções escalares

Exemplos de função escalar

Funções lógicas

IIF

CHOOSE

Comentários