Microsoft 20461 – Post Extra

Bom dia pessoal, gostaria de fazer um post extra pra resumir alguns assuntos que mereçem destaque e de quebra podemos treinar o inglês!

  • Funções
    • Scalar user-defined function
      • Scalar UDFs are called scalar because they return a single value. Scalar UDFs can appear anywhere in the query where an expression that returns a single value can appear (for example, in the SELECT column list). All the code within the scalar UDF must be enclosed
        in a BEGIN/END block.
    • Inline user-defined function
      • An inline table-valued UDF contains a single SELECT statement that returns a table. To see how an inline table-valued UDF works, insert the following SSMS snippet for an inline tablevalued function that cannot by itself be executed.
        CREATE FUNCTION dbo.FunctionName
        (
        @param1 int,
        @param2 char(5)
        )
        RETURNS TABLE AS RETURN
        (
        SELECT @param1 AS c1,
        @param2 AS c2
        )
        
        IF OBJECT_ID('Sales.fn_FilteredExtension', 'IF') IS NOT NULL
        DROP FUNCTION Sales.fn_FilteredExtension;
        GO
        CREATE FUNCTION Sales.fn_FilteredExtension
        (
        @lowqty AS SMALLINT,
        @highqty AS SMALLINT
        )
        RETURNS TABLE AS RETURN
        (
        SELECT orderid, unitprice, qty
        FROM Sales.OrderDetails
        WHERE qty BETWEEN @lowqty AND @highqty
        );
        GO
        
        SELECT orderid, unitprice, qty
        FROM Sales.fn_FilteredExtension (10,20);
        
    • UDF Options
      You can specify five options with UDFs:

      • ENCRYPTION: As with stored procedures and triggers, this is really an obfuscation of the source code and not a complete encryption.
      • SCHEMABINDING: This binds the schemas of all referenced objects.
      • RETURNS NULL ON NULL INPUT: If this is set, any NULL parameters cause a scalar UDF to return NULL without executing the body of the function.
      • CALLED ON NULL INPUT: This is the default, and it implies that a scalar function body will execute even if NULL is passed as a parameter.
      • EXECUTE AS: This executes under various contexts.

 

  • Estados de Transação
    • @@TRANCOUNT can be queried to find the level of transaction.
      • A level of 0 indicates that at this point, the code is not within a transaction.
      • A level > 0 indicates that there is an active transaction, and a number > 1 indicates the nesting level of nested transactions.
        Trancount

        USE TSQL2012;
        SELECT @@TRANCOUNT; -- = 0
        BEGIN TRAN;
        SELECT @@TRANCOUNT; -- = 1
        BEGIN TRAN;
        SELECT @@TRANCOUNT; -- = 2
        -- Issue data modification or DDL command here
        ROLLBACK; -- rolls back the entire transaction at this point
        SELECT @@TRANCOUNT; -- = 0
        
    • XACT_STATE() can be queried to find the state of the transaction.
      • The XACT_STATE() values are:
      • 1 An open transaction exists that can be either committed or rolled back.
      • 0 There is no open transaction; it is equivalent to @@TRANCOUNT = 0.
      • -1 An open transaction exists, but it is not in a committable state. The transaction can
        only be rolled back.

 

  • TRY_PARSE x TRY_CONVERT
    • TRY_CONVERT attempts to cast a value as a target data type, and if it succeeds, returns the value, returning NULL if the test fails. The following example tests two values of the datetime data type, which does not accept dates earlier than 1753-01-01 as valid dates.
      SELECT TRY_CONVERT(DATETIME, '1752-12-31');
      SELECT TRY_CONVERT(DATETIME, '1753-01-01');
      
    • TRY_PARSE, you can take an input string containing data of an indeterminate data type and convert it to a specific data type if possible, and return NULL if it is not. The following example attempts to parse two strings.
      SELECT TRY_PARSE('1' AS INTEGER);
      SELECT TRY_PARSE('B' AS INTEGER);
      

      The first string converts to an integer, so the TRY_PARSE function returns the value as an~integer. The second string, ‘B’, will not convert to an integer, so the function returns NULL.

  • Table Hints
    • HOLDLOCK
      Is equivalent to SERIALIZABLE. For more information, see SERIALIZABLE later in this topic. HOLDLOCK applies only to the table or view for which it is specified and only for the duration of the transaction defined by the statement that it is used in. HOLDLOCK cannot be used in a SELECT statement that includes the FOR BROWSE option.
    • ROWLOCK
      Specifies that row locks are taken when page or table locks are ordinarily taken. When specified in transactions operating at the SNAPSHOT isolation level, row locks are not taken unless ROWLOCK is combined with other table hints that require locks, such as UPDLOCK and HOLDLOCK.
    • XLOCK
      Specifies that exclusive locks are to be taken and held until the transaction completes. If specified with ROWLOCK, PAGLOCK, or TABLOCK, the exclusive locks apply to the appropriate level of granularity.
    • UPDLOCK
      Specifies that update locks are to be taken and held until the transaction completes. UPDLOCK takes update locks for read operations only at the row-level or page-level. If UPDLOCK is combined with TABLOCK, or a table-level lock is taken for some other reason, an exclusive (X) lock will be taken instead.
    • TABLOCK
      Specifies that the acquired lock is applied at the table level. The type of lock that is acquired depends on the statement being executed. For example, a SELECT statement may acquire a shared lock. By specifying TABLOCK, the shared lock is applied to the entire table instead of at the row or page level. If HOLDLOCK is also specified, the table lock is held until the end of the transaction.When importing data into a heap by using the INSERT INTO <target_table> SELECT <columns> FROM <source_table> statement, you can enable optimized logging and locking for the statement by specifying the TABLOCK hint for the target table. In addition, the recovery model of the database must be set to simple or bulk-logged. For more information, see INSERT (Transact-SQL).When used with the OPENROWSET bulk rowset provider to import data into a table, TABLOCK enables multiple clients to concurrently load data into the target table with optimized logging and locking. For more information, see Prerequisites for Minimal Logging in Bulk Import.
    • FORCESCAN
      Introduced in SQL Server 2008 R2 SP1, this hint specifies that the query optimizer use only an index scan operation as the access path to the referenced table or view. The FORCESCAN hint can be useful for queries in which the optimizer underestimates the number of affected rows and chooses a seek operation rather than a scan operation. When this occurs, the amount of memory granted for the operation is too small and query performance is impacted.FORCESCAN can be specified with or without an INDEX hint. When combined with an index hint, (INDEX = index_name, FORCESCAN), the query optimizer considers only scan access paths through the specified index when accessing the referenced table. FORCESCAN can be specified with the index hint INDEX(0) to force a table scan operation on the base table.
    • FORCESEEK [ (index_value(index_column_name [ ,n ] )) ]
      Specifies that the query optimizer use only an index seek operation as the access path to the data in the table or view. Starting with SQL Server 2008 R2 SP1, index parameters can also be specified. In that case, the query optimizer considers only index seek operations through the specified index using at least the specified index columns.index_value
      Is the index name or index ID value. The index ID 0 (heap) cannot be specified. To return the index name or ID, query the sys.indexes catalog view.
    • Referência: https://msdn.microsoft.com/en-us/library/ms187373.aspx
      Em PT/BR: https://msdn.microsoft.com/pt-br/library/ms187373.aspx
      http://www.devmedia.com.br/tipos-de-sql-server-table-hints/32489
      http://technet.microsoft.com/en-us/library/ms189857.aspx
      http://msdn.microsoft.com/en-us/library/ms187373.aspx
  • DML TRIGGERS
    • AFTER :This trigger fires after the event it is associated with finishes and can only be defined on permanent tables.
    • INSTEAD OF: This trigger fires instead of the event it is associated with and can be defined on permanent tables and views.

 

Confira meu perfil profissional no LinkedIn e faça parte da minha rede.https://lnkd.in/w9kyFR

Abs,
Bruno F. Antognolli

 

Microsoft 20461 – Módulos 18/19/20

Módulo 18 - Implementando Transações

Objetivos:
+ Descrever transações e batch de transações
+ Entender como o SQL Server trata batch
+ Gerenciar transações
+ Utilizar XACT_ABORT e TRY/CATCH


  • SP_WHO2 -> exibe dados das transações abertas no banco
    • KILL XX (mata o número da seção)
    • SYS.dm_exec_connections
    • TRY/CATCH
  • XACT_ABORT
    • Por padrão XACT_ABORT é OFF. 
    • When SET XACT_ABORT is ON, the entire transaction is terminated and rolled back on error, unless the error occurs in a TRY block. An error in a TRY blockleaves the transaction open but not committable, despite the setting of XACT_ABORT.
    • http://go.microsoft.com/fwlink/?LinkID=402858

Resultados XACT_STATE

XACT_STATE Results Description
0 There is no active user transaction.
1 The current request has an active, committable, user transaction.
-1 The current request has an active user transaction, but an error has occurred. The transaction can only be rolled back.

 

Módulo 19 - Improving Query Performance

Objetivos:
+ Entender Index Clustered/NonClustered
+ Entender estatísticas
+ Comparar uso de cursores


  • Dicas para melhorar performance da query
    • Retornar somente as colunas que serão usadas no SELECT
    • Uma tabela pode ter até 1024 colunas, evitar utilizar o ” * ” para retornar dados
    • Usar sempre where para restringir a massa de dados
    • Evitar uso de tabelas temporárias
    • Usar windows functions quando possível ao invés de tabela temporária
    • Evite uso de cursores, cada vez que ele é criado fica na memória, se não destruir pode sobrecarregar
    • Refatorar código
    • Link: http://go.microsoft.com/fwlink/?LinkID=402860
  • Index
    • Ver tamanho dos índices: Botão direito -> Standards Reports -> X
  • Índice Clusterizado
    • Movimenta fisicamente os dados,
    • ex: Mudar os alunos de lugar por ordem alfabética.
    • Só pode existir um índice clusterizado por tabela
    • Pode conter várias colunas da tabela mas só um índice
    • Quando usar? Deve ser utilizado em colunas do tipos numéricos e data.
      • É utilizado para buscar resultados “entre valores”
    • http://go.microsoft.com/fwlink/?LinkID=402869
  • Índice NonClusterizado
    • É usado com consultas com baixa cardinalidade
    • Ex: As pessoas da sala de aula estão sentadas em ordem alfabética, mas o professor tem 2 listas: 1 ordenada por idade e outra por cidade. A lista do professor é uma lista “NonClusterizada” embora a posição dos alunos seja “clusterizado”
    • http://go.microsoft.com/fwlink/?LinkID=402869
  • Estatísticas
    • Computa os registros mais utilizados e vai guardando os resultados, quando a consulta for utilizar esses registros a consulta será mais performática

      /*Estatistiticas*/
      set statistics IO on --LEITURA DE DISCO
      SELECT * FROM SALES.Orders
      set statistics TIME on --TEMPO
      SELECT * FROM SALES.Orders
  • Cursor
    • Alternativas para cursos: Funções tipo janela e funções tipo agregação
    • Plano de execução
      • Para salvar o modelo gráfico do plano de execução é criado um arquivo .sqlplan
  • SET SHOWPLAN_XML
 USE TSQL;
GO
SET SHOWPLAN_XML ON;
GO
-- Execute a query.
SELECT custid, ordermonth, qty FROM Sales.CustOrders WHERE custid =4;
GO
SET SHOWPLAN_XML OFF; 
Módulo 20 - SQL Server Metadata
  • Views de sistema -> acessar dados do sistema
  • Schema Views
    • Retorna mesmos dados da view de sistema, a diferença é que os registros retornam em padrão internacional
  • DMV Dinamic Manegement View
    • db -> banco
    • exec -> execução
    • io -> disco
    • os -> sistema
    • tran -> transações

Confira meu perfil profissional no LinkedIn e faça parte da minha rede.https://lnkd.in/w9kyFR

Abs,
Bruno F. Antognolli

Microsoft 20461 – Módulos 16/17

Modulo 16 - Programming with T-SQL

Objetivos:
+ Descrever batches em SQL Server
+ Decharar variáveis
+ Utilizar IF e WHILE


  • Diretiva GO
    • Sempre que for criar objetos é necessário separar em batch (diretivas)
    • Não é permitido criar mais de 1 objeto por diretiva
      SQL20461 M16.1
  • Variáveis
    SQL20461 M16.2

      --DECLARACAO DE VARIAVEIS
    --SOMENTE A PARTIR DO 2012
    DECLARE @NOME VARCHAR(55) = 'ABC'
    DECLARE @VALOR INT,
    @TESTE VARCHAR(10)
    --QUALQUER VERSAO
    SET @TESTE = 'ABC'
    SELECT @TESTE = 'ABC' 
  • Sinônimos
    SQL20461 M16.3
Modulo 17 - Implementing Error Handling
  • XACT_ABORT OFF = Padrão, não faz rollback se uma transação der erro
  • XACT_ABORT ON =  FAZ ROLLBACK de tudo (até que não deu erro)
  • RAISEERROR = Força erro, ex, para regra de negócios, mas é necessário saber os códigos de erro (coluna message_id) da tabela sys.messages.
    begin
     raiserror(N'%s %d', 10, 1, N'TESTE BFA', 2)
    end
    

 

Confira meu perfil profissional no LinkedIn e faça parte da minha rede.https://lnkd.in/w9kyFR

Abs,
Bruno F. Antognolli

Microsoft 20461 – Módulos 14/15

Módulo 14 - PIVOT

Objetivos:
+ Escrever queries com PIVOT e UNPIVOT
+ Escrever queries com groupings / grouping sets

  • Troca linha por colunas
    SQL20461 M14.1

SQL20461 M14.2

  • Group set -> Cria matriz de dados
    SQL20461 M15.1
  • Exemplos
      --=============EXEMPLO DE GROUPING SETS
    CREATE VIEW SALES.CATEGORYSALES
    AS
    SELECT C.categoryname,
    O.empid,
    O.custid,
    OD.qty
    FROM Production.Categories C
    INNER JOIN Production.Products P ON C.categoryid = P.categoryid
    INNER JOIN SALES.OrderDetails OD ON OD.productid = P.productid
    INNER JOIN SALES.Orders O ON O.orderid = OD.orderid
    WHERE C.categoryid IN (1,2,3) AND O.custid BETWEEN 1 AND 5
    --EXEMPLO SEM O GROUPPING SETS
    SELECT categoryname, NULL AS CUST, SUM(QTY) AS TOTAL FROM SALES.CATEGORYSALES GROUP BY categoryname
    UNION ALL
    SELECT NULL categoryname, CUSTID AS CUST, SUM(QTY) AS TOTAL FROM SALES.CATEGORYSALES GROUP BY CUSTID
    UNION ALL
    SELECT NULL categoryname, NULL AS CUST, SUM(QTY) AS TOTAL FROM SALES.CATEGORYSALES GROUP BY CUSTID
    --EXEMPLO COM O GROUP SET
    SELECT categoryname,
    custid,
    SUM(QTY) TOTAL
    FROM SALES.CATEGORYSALES
    GROUP BY
    GROUPING SETS((categoryname),(custid), ())
    ORDER BY categoryname, custid
    
    --=============EXEMPLO DE GROUPING SETS
    CREATE VIEW SALES.CATEGORYSALES
    AS
    SELECT C.categoryname,
    O.empid,
    O.custid,
    OD.qty
    FROM Production.Categories C
    INNER JOIN Production.Products P ON C.categoryid = P.categoryid
    INNER JOIN SALES.OrderDetails OD ON OD.productid = P.productid
    INNER JOIN SALES.Orders O ON O.orderid = OD.orderid
    WHERE C.categoryid IN (1,2,3) AND O.custid BETWEEN 1 AND 5
    --EXEMPLO SEM O GROUPPING SETS
    SELECT categoryname, NULL AS CUST, SUM(QTY) AS TOTAL FROM SALES.CATEGORYSALES GROUP BY categoryname
    UNION ALL
    SELECT NULL categoryname, CUSTID AS CUST, SUM(QTY) AS TOTAL FROM SALES.CATEGORYSALES GROUP BY CUSTID
    UNION ALL
    SELECT NULL categoryname, NULL AS CUST, SUM(QTY) AS TOTAL FROM SALES.CATEGORYSALES GROUP BY CUSTID
    --EXEMPLO COM O GROUP SET
    SELECT categoryname,
    custid,
    SUM(QTY) TOTAL
    FROM SALES.CATEGORYSALES
    GROUP BY
    GROUPING SETS((categoryname),(custid), ())
    ORDER BY categoryname, custid 
  • CUBE e ROLLUP -> extensão group by
    SQL20461 M14.4
  • Exemplos
      --=============EXEMPLO ROLLUP e CUBE
    SELECT categoryname,
    custid,
    SUM(QTY) TOTAL
    FROM SALES.CATEGORYSALES
    GROUP BY ROLLUP (categoryname, custid)
    SELECT categoryname,
    custid,
    SUM(QTY) TOTAL
    FROM SALES.CATEGORYSALES
    GROUP BY CUBE (categoryname, custid)
    --DUVIDA
    SELECT categoryname,
    custid,
    SUM(QTY) TOTAL
    FROM SALES.CATEGORYSALES
    GROUP BY ROLLUP (custid, categoryname)
    --EXEMPLO GROUPING_ID
    SELECT D.NAME,
    CASE WHEN GROUPING_ID(D.NAME,E.JOBTITLE) = 0 THEN E.JOBTITLE
    WHEN GROUPING_ID(D.NAME,E.JOBTITLE) = 1 THEN N'SUBCATEGORIA CATEGORIA'
    WHEN GROUPING_ID(D.NAME,E.JOBTITLE) = 3 THEN N'***TOTAL GERAL***'
    ELSE 'DESCONHECIDO'
    END as AGRUPAMENTO,
    COUNT(E.BUSINESSENTITYID) AS 'QTD EMP'
    FROM HumanResources.Employee E
    INNER JOIN HumanResources.EmployeeDepartmentHistory DH on E.BUSINESSENTITYID = DH.BUSINESSENTITYID
    INNER JOIN HumanResources.DEPARTMENT D ON D.DEPARTMENTID = DH.DEPARTMENTID
    WHERE DH.ENDDATE IS NULL AND D.DEPARTMENTID IN (1,2)
    GROUP BY ROLLUP(D.NAME, E.JOBTITLE); 
Módulo 15 - Executando Store Procedures

Objetivos
+ Retornar resultados através de Store Procedures
+ Passar parametros para procedures
+ Criar stored procedures simples para encapsular SELECT
+ Construir e exectuar sql dinânicos através do SP_EXECUTESQL

  • Procedures

     USE TSQL;
    --EXEMPLO CRIAÇÃO PROCEDURE
    CREATE PROC CALCULA_PRECO (@VALOR AS NUMERIC(10,2), @PRODID AS INT)
    AS
    DECLARE @IDPROD AS INT = @PRODID;
    SELECT PRODUCTID,
    productname,
    UNITPRICE,
    (UNITPRICE * @VALOR) CAMPO_CALCULADO
    FROM Production.PRODUCTS
    WHERE PRODUCTID = @IDPROD;
    EXEC CALCULA_PRECO 0.5,10;
    EXEC CALCULA_PRECO @VALOR = 0.5, @PRODID = 10;
    
    --EXEMPLO PROCEDURE COM VALOR OUTPUT
    CREATE PROC TESTE_OUTPUT (@VALOR INT, @CODIGO INT OUTPUT)
    AS
    SELECT @CODIGO = PRODUCTID FROM Production.PRODUCTS
    WHERE productid = @VALOR+1;
    GO
    DECLARE @RESULTADO INT
    EXEC TESTE_OUTPUT 4, @RESULTADO OUTPUT;
    SELECT @RESULTADO AS MEURESULTADO  
  • Como evitar SQL Injection usando query dinamica? *PROVA*
    • sys.sp_executesql

Confira meu perfil profissional no LinkedIn e faça parte da minha rede.https://lnkd.in/w9kyFR

Abs,
Bruno F. Antognolli

 

Microsoft 20461 – Módulos 12/13

Módulo 12 - Using set operators

Objetivos:
+ Utilizar operador UNION
+ Comparar INTERSECT e EXCEPT
+ Manipular registros com APPLY

  • UNION
    • Une tabelas
    • Elimina duplicidade
    • mesma quantidade de colunas
    • colunas de tipo compatível
  • UNION ALL
    • Une tabelas
    • NÃO Elimina duplicidade
    • mesma quantidade de colunas
    • colunas de tipo compatível

       --EXEMPLO DE UNION
      --Cria table exemplo
      SELECT top (5) BusinessEntityID, LoginID, JobTitle INTO CLIENTE_COPIA FROM HumanResources.Employee
      --Exclui duplicidades com UNION
      SELECT TOP(6) BusinessEntityID, LoginID, JobTitle FROM HumanResources.Employee
      UNION
      SELECT * FROM CLIENTE_COPIA
      --UNION ALL traz as duplicidades tb pq ele "une tudo"
      SELECT TOP(6) BusinessEntityID, LoginID, JobTitle FROM HumanResources.Employee
      UNION ALL
      SELECT * FROM CLIENTE_COPIA
      
  • INTERSECT
    • Traz resultado que está na intersecção
  • EXCEPT
    • Avalia quem está do lado esquerdo e retorna quem não está contido no lado direitoSQL20461 M12.1
  • APPLY
    • Funciona com uma tabela e uma função do tipo tabela.
    • Retorna os valores da primeira consulta passando como parâmetro para a função
      SQL20461 M12.2
    • SCRIPTS
      --===========================================
      --==========EXEMPLO DE APPLY=================
      SELECT TOP(5) DepartmentID, NAME INTO DEPARTAMENTO
      FROM HumanResources.Department
      ORDER BY DepartmentID
      CREATE TABLE EMPREGADO (EMP_ID_PK INT,
      EMP_NOME VARCHAR(50),
      DEPID INT)
      INSERT INTO EMPREGADO (EMP_ID_PK, EMP_NOME, DEPID)
      VALUES (1, 'MAX', 1)
      INSERT INTO EMPREGADO (EMP_ID_PK, EMP_NOME, DEPID)
      VALUES (2, 'ORLANDO', 2)
      INSERT INTO EMPREGADO (EMP_ID_PK, EMP_NOME, DEPID)
      VALUES (3, 'MARIA', 3)
      INSERT INTO EMPREGADO (EMP_ID_PK, EMP_NOME, DEPID)
      VALUES (4, 'BATMAN', 3)
      INSERT INTO EMPREGADO (EMP_ID_PK, EMP_NOME, DEPID)
      VALUES (5, 'SPIDER MAN', NULL)
      --CRIANDO FUNCAO QUE RETORNA TABELA
      CREATE FUNCTION FN_CARREGA_DEPARTAMENTO(@DEPID AS INT)
      RETURNS TABLE
      AS RETURN (SELECT * FROM DEPARTAMENTO WHERE DepartmentID = @DEPID)
      --TESTE DA FUNCAO
      SELECT * FROM FN_CARREGA_DEPARTAMENTO(1)
      SELECT * FROM FN_CARREGA_DEPARTAMENTO(2)
      --EXEMPLO CROSS APPLY
      SELECT * FROM EMPREGADO E
      CROSS APPLY FN_CARREGA_DEPARTAMENTO(E.DEPID)
      --EXEMPLO OUTER APPLY
      SELECT * FROM EMPREGADO E
      OUTER APPLY FN_CARREGA_DEPARTAMENTO(E.DEPID)
      --EXEMPLO CROSS APPPLY SEM FUNCAO, MAS COM TABELA TEMP
      SELECT * FROM EMPREGADO E
      CROSS APPLY
      (SELECT * FROM DEPARTAMENTO D
      WHERE DepartmentID = E.DEPID) A
      --EXEMPLO OUTER APPPLY SEM FUNCAO, MAS COM TABELA TEMP
      SELECT * FROM EMPREGADO E
      OUTER APPLY
      (SELECT * FROM DEPARTAMENTO D
      WHERE DepartmentID = E.DEPID) A
      
      
    • Mais exemplos?

 

Módulo 13 - Funções de janela, Offset e Funções de Agregação

Objetivos:
+ Descrever o uso de funções de janela (window functions)
+ Manipular resultados da window function com o operador OVER
+ Escrever queries com window functions com ranking, aggregation e offset comparasion


SQL20461 M13.1

Exemplos

 USE TSQL
--EXEMPLO OVER
CREATE VIEW Production.CategorizedProducts AS
SELECT P.categoryid AS CategoriaID,
C.categoryname AS Categoria,
P.productname AS Nome_Produto,
P.unitprice as Preco_Unitario
FROM Production.Products P
INNER JOIN Production.Categories C ON P.categoryid = C.categoryid;
GO
SELECT * FROM Production.CategorizedProducts
CREATE VIEW Sales.CategoryQtdyYear AS
SELECT C.categoryname AS CATEGORIA_PRODUTO,
SUM(OD.QTY) AS QTY,
YEAR(O.orderdate) AS ORDERDATE
FROM Production.Categories C
INNER JOIN Production.Products P ON C.categoryid = P.categoryid
INNER JOIN SALES.OrderDetails OD ON OD.productid = P.productid
INNER JOIN Sales.Orders O ON O.orderid = OD.orderid
GROUP BY C.categoryname, YEAR(O.ORDERDATE)

SELECT * FROM Sales.CategoryQtdyYear

--EXEMPLO OVER COM LINHA ANTERIOR E ATUAL
SELECT CATEGORIA_PRODUTO,
QTY,
ORDERDATE,
SUM(QTY) OVER (PARTITION BY CATEGORIA_PRODUTO
ORDER BY ORDERDATE
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS TOTALACUMULADO
FROM Sales.CategoryQtdyYear

--EXEMPLO OVER COM PROXIMA LINHA
SELECT CATEGORIA_PRODUTO,
QTY,
ORDERDATE,
SUM(QTY) OVER (PARTITION BY CATEGORIA_PRODUTO
ORDER BY ORDERDATE
ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING) AS TOTALACUMULADO
FROM Sales.CategoryQtdyYear
--EXEMPLO RANK
SELECT CATEGORIA_PRODUTO,
QTY,
ORDERDATE,
RANK() OVER (ORDER BY QTY) AS RANK_DADOS, --SEM CRITERIO DE DESEMPATE
DENSE_RANK() OVER (ORDER BY QTY) AS RANK_DADOS2 --COM CRITERIO DE DESEMPATE
FROM Sales.CategoryQtdyYear


--EXEMPLO ROW_NUMBER
SELECT CATEGORIA_PRODUTO,
QTY,
ORDERDATE,
ROW_NUMBER() OVER (PARTITION BY CATEGORIA_PRODUTO ORDER BY QTY) AS LINHA_NUMEROS
FROM Sales.CategoryQtdyYear

--EXEMPLO NTILE (DIVIDE EM GRUPOS)
SELECT CATEGORIA_PRODUTO,
QTY,
ORDERDATE,
NTILE(5) OVER (ORDER BY QTY) AS LINHA_NUMEROS
FROM Sales.CategoryQtdyYear
--EXEMPLO LEAD E LEAD
CREATE VIEW SALES.ORDERBYEMPLOYE AS
SELECT E.empid AS ID,
YEAR(O.ORDERDATE) DATA,
SUM(OD.QTY * OD.UNITPRICE) AS TOTAL_VENDAS
FROM SALES.ORDERS O
INNER JOIN SALES.OrderDetails OD ON O.orderid = OD.orderid
INNER JOIN HR.Employees E ON E.empid = O.empid
GROUP BY E.EMPID, YEAR(O.ORDERDATE)
--LAG
SELECT ID,
DATA,
TOTAL_VENDAS AS TOTAL_CORRENTE,
LAG(TOTAL_VENDAS,1,0) OVER(PARTITION BY ID ORDER BY DATA) AS PREVIOUS
FROM SALES.ORDERBYEMPLOYE 

Confira meu perfil profissional no LinkedIn e faça parte da minha rede.https://lnkd.in/w9kyFR

Abs,
Bruno F. Antognolli

 

Microsoft 20461 – Módulos 10/11

Módulo 10 - USING SubQueries

Objetivos:
+ Compreender o uso de queries aninhadas
+ Escrever queries “self-contained”
+ Usar o predicato Exists para escrever subquery


  • Correlated Subqueries
  • Múltiplas linhas usa-se in
  • Única linha (scalar) subqueries com funções scalares no where.
  • Correlated Subqueries
    SQL20461 M10.1
  • Exists: Não reconhece valores unknown

     /*Exists*/
    SELECT *
    FROM HumanResources.Cliente
    WHERE NOT EXISTS (SELECT MAX(CLIENTE_ID_PK)
    FROM HumanResources.Cliente
    WHERE CLIENTE_NOME = 'ROBSON') 
Módulo 11 - USING TABLE EXPRESSIONS
  • VIEW
    • É uma tabela virtual.
    • Order by fora da view.
    • Boa prática usar o TOP 100%.
      • O TOP utiliza as paginações em memória, o que torna-o mais rápido.
      • Utilizar FETCH para ler views paginadas

         /*CREATE VIEW*/
        CREATE VIEW V_PESSOA AS
        SELECT CLIENTE_NOME AS PESSOA,
        IDADE AS MAIOR
        FROM HumanResources.Cliente
        WHERE EXISTS (SELECT MAX(CLIENTE_ID_PK)
        FROM HumanResources.Cliente
        WHERE CLIENTE_NOME = 'ROBSON')
        
        <span style="font-weight: 400;">SELECT</span> <span style="font-weight: 400;">*</span> <span style="font-weight: 400;">FROM</span><span style="font-weight: 400;"> V_PESSOA</span>
        
  • DERIVED TABLES

      /*TABELA DERIVADA*/
    SELECT * FROM (SELECT CLIENTE_NOME FROM HumanResources.Cliente) AS DERIVADA 
  • CTE (Commom table expression)

      WITH CT_TESTE AS
    (SELECT CLIENTE_NOME FROM HumanResources.Cliente)
    SELECT * FROM CT_TESTE;
    

Confira meu perfil profissional no LinkedIn e faça parte da minha rede.https://lnkd.in/w9kyFR

Abs,
Bruno F. Antognolli

Microsoft 20461 – Módulos 8/9

 

  • FUNÇÕES ESCALAR

 

    • Retorna valor único
      SQL20461 M8.1
    • FUNÇÕES AGREGAÇÃO
      SQL20461 M8.2
    • FUNÇÕES WINDOW (NOVA Sql Server 2008r2)
      SQL20461 M8.3

       --WINDOWS FUNCTION
      SELECT PRI.ProductID,
      PROD.Name,
      PRI.LocationID,
      PRI.Quantity,
      RANK() OVER(PARTITION BY PRI.LOCATIONID ORDER BY PRI.QUANTITY) AS RANK_DADOS
      FROM Production.Product AS PROD
      INNER JOIN Production.ProductInventory PRI
      ON PROD.ProductID = PRI.ProductID
      WHERE PROD.ProductID BETWEEN 3 AND 4
      ORDER BY PRI.LocationID
      

 

  • FUNÇÕES ROWSET (Select em arquivos txt como tabela virtual, por ex)

 

  • FUNÇÕES DE CONVERSÃO
    /*CAST X CONVERT*/
    DECLARE @DATA DATETIME = '01/02/2016'
    SELECT CAST(@DATA AS DATE)
    SELECT CONVERT(CHAR(10),@DATA,101) --PADRAO AMERICANO
    SELECT CONVERT(CHAR(10),@DATA,103) --PADRAO BR
    /*PARSE*/
    SELECT PARSE('02/01/2016' AS DATETIME2 USING 'EN-US') AS PARSE_CONVERT
    SELECT PARSE('02/01/2016' AS DATETIME2 USING 'PT-BR') AS PARSE_CONVERT
    /*TRY PARSE*/
    SELECT TRY_PARSE('02/31/2016' AS DATETIME2 USING 'EN-US') AS TRY_PARSE_CONVERT
    SELECT TRY_PARSE('02/31/2016' AS DATETIME2 USING 'PT-BR') AS TRY_PARSE_CONVERT
    
    /*TRY CONVERT*/
    SELECT TRY_CONVERT(DATETIME2, '02/31/2016') AS TRY_CONVERT_
    SELECT TRY_CONVERT(DATETIME2, '02/31/2016') AS TRY_CONVERT_
    
    /*IS NUMERIC: Retorna booleano 1/0 se é numerico ou nao*/
    SELECT ISNUMERIC('1')
    SELECT ISNUMERIC('A')
    
    /*IIF*/
    SELECT IIF(LISTPRICE > 0, 'MAIOR QUE ZERO', 'MENOR QUE ZERO')
    FROM Production.Product
    
    
  • Outras funções de conversão
    • ISNULL (CONVERSÃO PARA NUMERO, ISNULL(NUMERO, 0)
    • COALESCE (CONVERSÃO EM TEXTO, ISNULL(NUMERO, ‘0’)) DEPOIS ROLA CONVERSÃO IMPLÍCITA
    • NULLIF: SE OS VALORES FOREM IGUAIS ELE RETORNA NULO.

TABELA DE CONVERSÃO IMPLÍCITA

SQL20461 M8.4

 

Módulo 9 – Funções de agregação

Tabela de funções de agregação:SQL20461 M9.1

 

Confira meu perfil profissional no LinkedIn e faça parte da minha rede.https://lnkd.in/w9kyFR

Abs,
Bruno F. Antognolli