Funções De agregações - SQL SERVER

        As funções de agregação executam um cálculo em um conjunto de valores e retornam um único valor. Com exceção de COUNT, as funções de agregação ignoram valores nulos. As funções de agregação normalmente são usadas com a cláusula GROUP BY da instrução SELECT.

Todas as funções de agregação são determinísticas. Isso significa que as funções de agregação retornam o mesmo valor sempre que são chamadas, consulte  Funções determinísticas e não determinísticas.

Funções de agregação podem ser usadas como expressões apenas nos seguintes casos:

• A lista de seleção de uma instrução SELECT (uma subconsulta ou uma consulta externa).
• Uma cláusula HAVING.

AVG

Retorna a média dos valores em um grupo. Valores nulos são ignorados.

Ex: SELECT AVG(Salarios)FROM dbo.RecursosHumanos.Empregados

COUNT

Retorna o número de itens de um grupo. Isso inclui valores NULL e duplicados.

Ex: SELECT COUNT(*)FROM dbo.RecursosHumanos.Empregados

MAX

Retorna o valor máximo na expressão.
Ex: SELECT MAX(Salarios)FROM dbo.RecursosHumanos.Empregados

MIN

Retorna o valor minimo na expressão.
Ex: SELECT MIN(Salarios)FROM dbo.RecursosHumanos.Empregados

SUM

Retorna a soma de todos os valores ou somente os valores DISTINCT na expressão. SUM pode ser usado exclusivamente com colunas numéricas. 

Ex: SELECT SUM(Salarios)FROM dbo.RecursosHumanos.Empregados

Outras funções de agregações não comum. Para estudo de casa: CHECKSUM_AGG,COUNT_BIG,GROUPING,GROUPING_ID,STDEV,STDEVP,VARP

Funções De Classificação - SQL Server

As funções de classificação retornam um valor de classificação para cada linha em uma partição. Dependendo da função usada, algumas linhas podem receber o mesmo valor que outras. As funções de classificação são não determinísticas.

O transact-SQL fornece as seguintes funções de classificação:

RANK

Retorna a classificação de cada linha na partição de um conjunto de resultados. A classificação de uma linha é um mais o número de classificações que vêm antes da linha em questão.

SELECT

P.ID_Produto,P.ID_Categoria,P.Quantidade, 

RANK() OVER(PARTITION BY P.ID_Categoria ORDER BY P.Quantidade DESC) AS 'RANKING'

FROM

            Produtos.ProdutosInventario P

ORDER BY

            'RANKING'

*

Se duas ou mais linhas empatarem em uma classificação, cada linha empatada receberá a mesma classificação. Por exemplo, se os dois melhores vendedores tiverem o mesmo valor de SalesYTD, os dois serão classificados com o número um. O vendedor com o próximo SalesYTD maior será classificado com o número três, pois há duas linhas com classificação mais alta. Portanto, a função RANK nem sempre retorna inteiros consecutivos.

ROW_NUMBER

Este retorna o número sequencial de uma linha em uma instrução executado no conjunto de resultados, iniciando em 1 para a primeira linha de cada partição.

Observação que fique explicito o numero da linha, não tem relação com ID Da tabela em execução.

SELECT

           ROW_NUMBER OVER(ORDER BY ID_VENDA DESC) AS Row,

           Nome, SobreNome

FROM

           dbo.VENDAS

ROW Nome         SobreNome               
--- -----------  ---------------------- 
1   Linda        Mitchell               
2   Jae          Pak                    
3   Michael      Blythe                 
4   Jillian      Carson                 
5   Ranjit       Varkey Chudukatil      

Outras funções de classificação não muito comum, para complementar os estudos rs ..

NTILE & DENSE_RANK

Funções Escalares - SQL Server

O SQL fornece muitas funções internas, neste tópico detalhamos as funções escalares por categoria:

Categoria	Descrição
Funções de configuração	Retornam informações sobre a configuração atual.
Funções de conversão	Suporte para conversão de tipos de dados.
Funções do cursor	Retornam informações sobre cursores.
Tipos de dados e funções de data e hora	Executam operações em uma data e valores de entrada de hora e retornam valores de cadeia de caracteres, numéricos ou de data e hora.
Funções lógicas	Executam operações lógicas.
Funções matemáticas	Executam cálculos baseados em valores de entrada fornecidos como parâmetros às funções e retorna valores numéricos.
Funções de metadados	Retornam informações sobre o banco de dados e objetos de banco de dados.
Funções de segurança	Retornam informações sobre usuários e funções.
Funções de cadeia de caracteres	Executam operações em um valor de entrada de cadeia de caracteres (char ouvarchar) e retornam uma cadeia de caracteres ou valor numérico.
Funções de sistema	Executam operações e informações de retorno sobre valores, objetos e configurações em uma instância do SQL Server.
Funções estatísticas de sistema	Retornam informações estatísticas sobre o sistema.
Funções de texto e imagem	Executam operações em valores de entrada de texto ou imagem ou colunas e retornam informações sobre o valor.

Determinismo De Funções

As funções internas do SQL Server são determinísticas ou não determinísticas. As funções são determinísticas quando retornam sempre o mesmo resultado quando são chamadas com o uso de um conjunto específico de valores de entrada. As funções são não determinísticas quando podem retornar resultados diferentes sempre que são chamadas, mesmo com o mesmo conjunto específico de valores de entrada. Para obter mais informações, consulte Funções determinísticas e não determinísticas.

Agrupamento De Funções

As funções que usam uma entrada de cadeia de caracteres e retornam uma saída de cadeia de caracteres usam o agrupamento da cadeia de caracteres de entrada para a saída.

As funções que usam entradas de não caracteres e retornam uma cadeia de caracteres usam o agrupamento padrão do banco de dados atual para a saída.

As funções que usam várias entradas de cadeia de caracteres e retornam uma cadeia de caracteres usam as regras de precedência de agrupamento para definir o agrupamento da cadeia de caracteres de saída. Para obter mais informações, consulte Precedência de agrupamento (Transact-SQL).

SQLCMD – O utilitário de linha de comando do SQL Server

Neste topico irei detalhar um pouco mais sobre o SQLCMD, o utilitário de linha de comando do SQL Server. Super prático, leve, possibilidade de criar scripts altamente personalizáveis e de fácil execução (@wait, @locks, etc), uso de processador da máquina 0% e menos de 20 MB de memória.

Conheci o SQLCMD quando eu ia executar alguma atividade nos bancos SQL Server e achava que enviar evidência de execução tirando print de tela do SQL Management Studio era meio estranho. Estava acostumado com o SQL*Plus (da ORACLE), onde gerava um spool e toda a saída dos comandos executados e o retorno eram gravados em um arquivo texto de saída, que eu enviava para os solicitantes das atividades como evidência.

Realmente o SQL*Plus é bem mais completo que o SQLCMD, mas isso não tira o mérito do SQLCMD, que me foi de grande ajuda numa demanda onde precisei executar um script SQL de 500 MB para importação de dados numa base SQL Server. Tentei pelo Management Studio umas 10x, mas não carregava o arquivo nem por reza.. A solução foi recorrer ao já conhecido SQLCMD, que fez a atividade com 1 linha de comando, sem problema algum.


Não vou me estender muito, porque realmente existem vários e vários parâmetros do SQLCMD, além de comandos da própria ferramenta para “programar” nela, tipo umas macros, definição de variáveis, etc. Vou focar apenas nos principais recursos. Se você tem vontade de se aprofundar mais, sugiro visitar a página do SQLCMD da Microsoft ou conferir este link (em inglês)

Parâmetros de conexão

• -d <banco de dados>: Serve para especificar o nome do banco de dados que o comando será executado. Atualmente esse parâmetro está obsoleto e deve ser desativado em futuras versões, sendo substituído pelo comando USE [banco].
• -S <servidor>: Serve para especificar o servidor de conexão do SQL Server. Caso você queira se conectar em uma instância que não seja a padrão ou o servidor possui uma porta diferente da padrão (1433), utilize a sintaxe:
  sqlcmd -S <servidor>\<instancia>, <porta>.
  Ex: sqlcmd -S servidor_casa\instanciaTeste, 1453
• -A: Permite conectar no servidor com uma conexão dedicada para o administrador do banco de dados (Dedicated Administrator Connection – DAC)

 Parâmetros de login

• -E: Tenta se conectar ao servidor utilizando o modo de autenticação Windows Authentication. Não é necessário informar o usuário ou senha, uma vez que a conexão é feita utilizando o usuário logado na máquina, que está previamente cadastrado no Active Directory. Se os parâmetros -P ou -U não forem informados, esse parâmetro é utilizado automaticamente.
• -U <usuario> -P <senha>: Tenta se conectar ao servidor utilizando o modo de autenticação SQL Server Authentication, onde é necessário informar um usuário e senha e esse usuário deve ter sido criado previamente nas diretivas de usuários do servidor (No Management Studio, Security > Logins)

 Parâmetros de entrada e saída de arquivos

• -i <arquivo>: Define o arquivo de entrada a ser executado pelo SQLCMD. Caso parte do caminho tenha espaços, utiliza-se aspas (eu sempre utilizo, tendo espaço ou não).
  Ex: sqlcmd -S pc-casa -i “C:\Meus Arquivos\query.sql”
• -o <arquivo>: Define o arquivo de saída, onde serão gravadas as mensagens de saída retornadas pelo SQLCMD.
• -e: Define que as queries contidas no arquivo de entrada apareçam no arquivo de saída também.
• -u: Define que o arquivo de saída será gravado no formato Unicode (UTF-8).

 Parâmetros de query

• -q <query>: Executa a query passada por parâmetro (utilize aspas). Para executar mais de uma query, utilize o ponto e vírgula (;). Esse parâmetro não pode ser utilizado em conjunto com o -i, pois eles são mutuamente exclusivos.
• -Q <query>: Faz a mesma coisa do parâmetro -q, mas após a execução, fecha o SQLCMD.
• -t <segundos>: Define o tempo de timeout (em segundos) da query que será executada. Após ultrapassar o limite definido, a conexão será encerrada, mesmo que não tenha sido executada por completo ainda.
• -s <caractere>: Define o caractere separador de colunas.
  Ex: sqlcmd -S pc-casa -s ; -Q “select * from sys.sysobjects”
• -W: Remove os espaços em branco à direita.

 Parâmetros de erros

• -m<nivel de erro>: Define a partir de que nível de severidade de erro, as mensagens serão gravadas no arquivo de saída. Utilizando o parâmetro -1, todas as mensagens, até as informativas, serão gravadas. Esse parâmetro não aceita espaços. -m-1 é válido, mas -m -1 não.
• -V <nivel de erro>: Define a partir de qual nível de severidade as mensagens retornadas serão tratadas como erro.

 Parâmetros diversos

• -?: Exibe o HELP do SQLCMD

 Exemplos:

1 2 3 4 5 6 7 8 9 10 11

-- Conecta no servidor "servidor-casa", utilizando o usuário "damatto", senha "damatto", executa o script "teste.sql" e grava o resultado e o próprio arquivo de entrada no arquivo teste.log sqlcmd -S servidor-casa -U damatto -P damatto -i "C:\Meus Dados\SQL\teste.sql" -e -o "C:\Meus Dados\SQL\teste.log"   -- Conecta no servidor "servidor-casa" utilizando autenticação Windows e executa o script "teste.sql" sqlcmd -S servidor-casa -i "C:\Meus Dados\SQL\teste.sql"   -- Conecta no servidor "servidor-casa", instância "instanciaTeste", porta 1453. Executa uma query no servidor, onde o tempo de expiração é de 60 segundos. O resultado da query virá separado por ";" e não terá espaços em branco. sqlcmd -S servidor-casa\instanciaTeste,1453 -q "SELECT TOP 10 * FROM sys.sysobjects" -t 60 -s ; -W   -- Conecta no servidor "servidor-casa", executa uma query no servidor, onde o tempo de expiração é 30 segundos. O resultado da query virá separado por ";", não terá espaços em branco e será gravado no arquivo "saida.log" sqlcmd -S servidor-casa -q "SELECT * FROM sys.sysobjects" -t 30 -s ; -W -e -o "C:\saida.log"

 Também é possível executar queries normalmente no SQLCMD

acompanhei no proximo post um exemplo pratico em produçao, 

das funcionalidades mais uteis do SQLCMD ..

update:

Executar scripts em batch..

http://scriptsemsql.blogspot.com.br/2016/06/executar-em-batch-todos-os-scripts-de.html

Executar em batch todos os scripts de uma pasta SQLCMD - SQL SERVER

Neste post daremos continuidade as funcionalidades do SQLCMD e demonstraremos como executar vários scripts em lote que estão em uma determinada pasta, gerando log de execução, com apenas uma linha de código. Essa instrução é bem simples, e permite uma série de personalizações para atender à sua necessidade.

Acredito que todo DBA e boa parte dos desenvolvedores já precisaram executar vários scripts SQL de forma prática e rápida, não se esquecendo de nenhum e ainda logando as operações para eventuais questionamentos. Essa atividade é muito comum e diariamente precisamos executar uma serie de scripts em sequencia.
E é claro que eu não iria executá-los um por um manualmente..

Para essa solução, vou utilizar a linguagem do Prompt de comando do Windows (CMD.exe) e SQLCMD, o utilitário de linha de comando do SQL Server.

Lembre-se que como responsabilidade de todo DBA, você deve avaliar e validar todos os scripts que lhe são enviados para serem executados, preferencialmente em um ambiente de testes, desenvolvimento e/ou pré-produção. Não utilize a solução apresentada aqui para executar vários scripts em batch diretamente na produção sem ao menos tê-los testado em um ambiente de testes antes.

Exemplo:

Criaremos um arquivo .BAT no diretório dos seus scripts 
(Neste exemplo, vou usar o nome “Executa Scripts.bat”) com o seguinte comando:

2	for %%G in (*.sql) do sqlcmd /S localhost\sql2014 /d Testes -E -i"%%G" >> Teste.log pause

Detalhes, onde:

/S: define o servidor\instância onde os scripts serão executados
/d: define o database onde os scripts serão executados
-E: especifica que o modo de autenticação será o Trusted Connection (Autenticação Windows AD). Caso você queira utilizar autenticação SQL, utilize -Uusuario e -Psenha

-i: especifica o arquivo de entrada que contém os comandos que serão executados

Reparem que nessa solução, o log é gerado com o resultado da execução de cada script, sem informação de data e hora e sem separação entre os scripts. Com isso, os resultados não ficam muito organizados dentro do arquivo. Se você não se preocupa muito com a saída do log, pode utilizar essa solução tranquilamente.

Exemplo do log de execução: