App mobile CASA

Publicado em 28 de dezembro de 2016 por Capitão Caverna

Bom dia pessoal, tudo bem?

Boa notícia na minha vida e gostaria de compartilhar com vocês! Agora sou voluntário de uma ONG Canadense chamada CASA Latinoamericaine ela é sediada em Québec/Québec. Na verdade nós começamos a trocar informações em junho/2016 e agora em dezembro fico contente em anunciar o primeiro protótipo do APP CASA desenvolvido por mim! =)

Segue uma pequena amostra do projeto!

A propósito, segue o link da ONG:
http://casalatinoamericaine.org/

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

Abs,
Bruno F. Antognolli

Microsoft 20462 – Módulo 12 – Error Logs

Publicado em 18 de novembro de 2016 por Capitão Caverna

Resposta

Bom dia pessoal,
Vamos estudar um pouco sobre error logs!

Tipos de erros:

SP_CYCLE_ERRORLOG
- Criar um novo arquivo de log

DELETAR EMAILS
- EXECUTE DBO.SYSMAIL_DELETE_MAILITEMS_SP
  @SENT_BEFORE = @CUTOFFDATE;
DELETAR LOGS
- EXECUTE DBO.SYSMAIL_DELETE_LOG_SP
  @LOGGED_BEFORE = @CUTOFFDATE;

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

Abs,
Bruno F. Antognolli

Microsoft 20462 – Módulo 12 – Jobs

Publicado em 15 de outubro de 2016 por Capitão Caverna

Resposta

Bom dia pessoal,
Hoje vamos falar um pouco sobre Agent e Jobs, vamos lá.

Sql Server Agent
- Serviço responsável por startar tarefas
Jobs?
- Adicionar Jobs com a SP_ADD_JOB
  [CODE=SQL]
  USE msdb; GO EXEC sp_add_job @job_name = ‘HR database backup’, @enabled = 1, @description = ‘Backup the HR database’, GO
  [/CODE]
- Funcionamento JOB:

Argumentos da JOB

Argument	Description
@job_id	Unique identification number of job to which to add the step (only specify this or @job_name, not both).
@job_name	User-friendly name of job to which to add the step (only specify this or @job_id, not both).
@step_id	Unique identification number of the job step, starting at 1 and incrementing by 1.
@step_name	User-friendly name of job step.
@subsystem	Subsytem for SQL Server Agent to use to execute the command (for example, CMDEXEC or PowerShell).
@command	Command to execute.
@on_success_action	Action to perform if the step succeeds (for example, quit or go to the next step).
@on_fail_action	Action to perform if the step fails (for example, quit or go to the next step).
@retry_attempts	Number of retry attempts if the step fails.
@retry_interval	Time to wait between retry attempts.

Visualizar histórico das JOBS

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

Abs,
Bruno F. Antognolli

Microsoft 20462 – Módulo 11 – Integridade, Recuperação, Índices e Fragmentação

Publicado em 23 de setembro de 2016 por Capitão Caverna

Resposta

Bom dia pessoal,
Hoje vamos verificar algumas lições da prova 20462. Vamos começar sobre como chegar a integridade do banco.

DBCC CheckAlloc()
- Verifica integridade de alocação do banco, se há alguma coisa corrompida
DBCC CheckCatalog()
- Verifica integridade de catalogo do banco (tabelas, views, procedures, etc)
DBCC CheckDB()
- Verifica integridade banco de dados
DBCC CheckTable()
- Verifica integridade das tabelas do banco

Reparar objetos banco

REPAIR_REBUILD
- Checagem de consistência com reparação SEM perda de dados
REPAIR_ALLOW_DATA_LOSS
- Checagem de consistência com reparação COM perda de dados

[CODE=SQL]

select * from [dbo].[orders]

–analise de alocacao
DBCC CHECKALLOC([CorruptDB])
DBCC CHECKCATALOG([CorruptDB])
DBCC CHECKDB([CorruptDB])

ALTER DATABASE [CorruptDB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
DBCC CHECKDB([CorruptDB], REPAIR_ALLOW_DATA_LOSS);
ALTER DATABASE [CorruptDB] SET MULTI_USER WITH ROLLBACK IMMEDIATE;
GO

[/CODE]

Resultado

Msg 8945, Level 16, State 1, Line 13
Table error: Object ID 149575571, index ID 1 will be rebuilt.
The error has been repaired.
Msg 8928, Level 16, State 1, Line 13
Object ID 149575571, index ID 1, partition ID 72057594039697408, alloc unit ID 72057594040942592 (type In-row data): Page (1:283) could not be processed. See other errors for details.
The error has been repaired.
Msg 8939, Level 16, State 98, Line 13
Table error: Object ID 149575571, index ID 1, partition ID 72057594039697408, alloc unit ID 72057594040942592 (type In-row data), page (1:283). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 2057 and -4.
The error has been repaired.
Msg 8980, Level 16, State 1, Line 13
Table error: Object ID 149575571, index ID 1, partition ID 72057594039697408, alloc unit ID 72057594040942592 (type In-row data). Index node page (1:309), slot 0 refers to child page (1:283) and previous child (0:0), but they were not encountered.
The error has been repaired.
Msg 8978, Level 16, State 1, Line 13
Table error: Object ID 149575571, index ID 1, partition ID 72057594039697408, alloc unit ID 72057594040942592 (type In-row data). Page (1:310) is missing a reference from previous page (1:283). Possible chain linkage problem.
The error has been repaired.
Msg 8945, Level 16, State 1, Line 13
Table error: Object ID 149575571, index ID 2 will be rebuilt.
The error has been repaired.
Msg 8945, Level 16, State 1, Line 13
Table error: Object ID 149575571, index ID 3 will be rebuilt.
The error has been repaired.
Msg 8945, Level 16, State 1, Line 13
Table error: Object ID 149575571, index ID 4 will be rebuilt.
The error has been repaired.
Msg 8945, Level 16, State 1, Line 13
Table error: Object ID 149575571, index ID 5 will be rebuilt.
The error has been repaired.
Msg 8945, Level 16, State 1, Line 13
Table error: Object ID 149575571, index ID 6 will be rebuilt.
The error has been repaired.
Msg 8945, Level 16, State 1, Line 13
Table error: Object ID 149575571, index ID 7 will be rebuilt.
The error has been repaired.
Msg 8945, Level 16, State 1, Line 13
Table error: Object ID 149575571, index ID 8 will be rebuilt.
The error has been repaired.
Msg 8945, Level 16, State 1, Line 13
Table error: Object ID 149575571, index ID 9 will be rebuilt.
The error has been repaired.
There are 788 rows in 19 pages for object "Orders".

Conferindo quais pedidos ficaram sem detalhes

Restaurando paginações corrompidas a partir de um backup.


--Restaurando Paginações Corrompidas
USE MASTER
GO
RESTORE DATABASE CORRUPTDB
PAGE='1:283,1:309'
FROM DISK = 'C:\TESTE.BAK'

Índices e Fragmentação

Fragmentação interna
- Ocorre no comando update.
  Quando há um update (internamente ocorre um insert e um delete no banco), dividindo as paginações do DB. Ex: Um registro possui dados em diversas paginações:
  10% do registro na paginação1
  50% do registro na paginação2
  40% do registro na paginação3
Fragmentação externa
- Ocorre naturalmente, quando registros são inseridos aleatoriamente. Ex: Cadastrar clientes:
  Fragmentado -> Bruno, José, Adão, Eva, Mauricio, Carlos, Robson.

Fillfactor e PadIndex
- Fillfactor: Minimiza fragmentação: Informa quantos por cento da paginação é necessário para gravar um dados. Ex: Se um dado ocupar até 70% da paginação ele deve ser gravado numa paginação nova e não quebrado em 20% em uma paginação e outros 50% em outra paginação. Assim contribui para diminuir a fragmentação
- PadIndex: Além do Root Index também é criado no nível Intermediate Level. (3 imagens atrás). Obs: Por default é apenas no nível root

Rebuild Online
O Comando ONLINE = ON é responsável por refazer o rebuild em modo online.

Updating Statistics
- AUTO_UPDATE_STATISTICS
  - Parâmetro a nível de banco de dados que atualiza as estatísticas baseados em um algoritmo interno
- UPDATE STATISTICS
  - Atualiza só as estatísticas que o DBA definir.
- sp_update
  - Procedure que atualiza as estatísticas do banco.

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

Abs,
Bruno F. Antognolli

Microsoft 20461 – Post Extra

Publicado em 4 de agosto de 2016 por Capitão Caverna

Resposta

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.
```
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

Publicado em 25 de julho de 2016 por Capitão Caverna

Resposta

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

TRANSACTION
- BEGIN
- COMMIT
- ROLLBACK
Links
http://go.microsoft.com/fwlink/?LinkID=402857
http://go.microsoft.com/fwlink/?LinkID=402802

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
- sys.tables, sys.objects
- http://go.microsoft.com/fwlink/?LinkID=402873
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

Publicado em 15 de julho de 2016 por Capitão Caverna

Resposta

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

Variáveis

  --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

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

Publicado em 12 de julho de 2016 por Capitão Caverna

Resposta

Módulo 14 - PIVOT

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

Troca linha por colunas

Group set -> Cria matriz de dados

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

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

Publicado em 8 de julho de 2016 por Capitão Caverna

Resposta

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 direito

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

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?
- https://technet.microsoft.com/en-us/library/ms175156(v=sql.105).aspx

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

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

Publicado em 8 de julho de 2016 por Capitão Caverna

Resposta

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

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

Bruno Fernando Antognolli

Meu blog de desenvolvimento

App mobile CASA

Microsoft 20462 – Módulo 12 – Error Logs

Microsoft 20462 – Módulo 12 – Jobs

Microsoft 20462 – Módulo 11 – Integridade, Recuperação, Índices e Fragmentação

Microsoft 20461 – Post Extra

Microsoft 20461 – Módulos 18/19/20

Microsoft 20461 – Módulos 16/17

Microsoft 20461 – Módulos 14/15

Microsoft 20461 – Módulos 12/13

Microsoft 20461 – Módulos 10/11