Function Split – TSQL

Aqui um código muito útil  em desenvolvimento, função SPLIT em SQL

Codigo

 

CREATE Schema Helper
go
CREATE FUNCTION Helper.SplitValues
(@texto VARCHAR(MAX),
@delimiter NVARCHAR(5)
)
RETURNS @Dados TABLE(DATA VARCHAR(MAX))
AS
BEGIN
DECLARE @valor VARCHAR(MAX)= LTRIM(RTRIM(@texto));
DECLARE @prefixos NVARCHAR(MAX), @textXML XML;
SELECT @prefixos = @valor;
SELECT @textXML = CAST(‘<d>’+REPLACE(@prefixos, @delimiter, ‘</d><d>’)+'</d>’ AS XML);
INSERT INTO @Dados
SELECT X.DATA
FROM
(
SELECT T.split.value(‘.’, ‘nvarchar(max)’) AS DATA
FROM @textXML.nodes(‘/d’) T(SPLIT)
) AS X;
RETURN;
END;

 

Para teste

 

 

por enquanto e isso

Conhecendo Views Parte II (Views particionadas)

Hoje vamos ver uma funcionalidade interessante das Views,e esse post aqui por si foi muito além do que eu imaginava.

Nossa missão hoje e otimizar acesso a uma tabela com 1 milhão de registros,

Vamos criar uma partitioned-views e alem disso ela deve ser MODIFICÁVEL

 ou seja deve receber Inserts , deletes , Updates.

Views particionadas surgiu no sql server 2000, alguns gostam outros não , outras funcionalidades foram criadas  como por exemplo partition table ,mas como views particionadas e uma funcionalidade a ser entendida , então entender-la:

Conceito: As views  particionadas permitem que os dados de uma tabela grande sejam divididos em tabelas membro menores. Os dados são particionados entre as tabelas membro com base nos intervalos dos valores de dados de uma das colunas. Os intervalos de dados para cada tabela membro estão definidos em uma restrição CHECK especificada na coluna de particionamento

Não se trata de partição de tabelas, o nosso caso de uso e ser resolvido trata-se de problemas de performance, processos de IO,

A sintaxe e bem simples.

CREATE VIEW dbo.VwMyView

AS

SELECT as colunas …

from tabela1

UNION ALL

SELECT as colunas …

from tabela 2
UNION ALL

SELECT as colunas …

from tabela 3
SELECT * FROM dbo.VwMyView

Cada tabela em uma visão particionada é sua própria pequena (ou grande) ilha de dados. Ao contrário do particionamento, onde as partições se juntam para formar uma unidade lógica. Por exemplo, se você executar o ALTER TABLE em uma tabela particionada, você altera a tabela inteira

Caso de uso:

Nosso cliente da XPTO tem uma tabela com 1 milhão de registros que são lançamentos bancários para dois grandes bancos, poderia ser 2 , 10 ,100 milhões mas vamos começar com 1 milhão, e diversas procedures fazem referencias a ela fazendo scan na tabela inteira, então decidimos ajudar.

vamos a demo:

O código abaixo apenas cria um banco com duas tabelas , uma para Conta bancaria e  outra de  Lançamentos


-- ==================================================================

–Observação:Cria o banco de dados com as tabelas para exemplo

— ==================================================================

CREATE DATABASE ExemploViewParticionada;

GO

USE ExemploViewParticionada;

IF ( OBJECT_ID(‘ContaBancaria’, ‘U’) IS NULL )

BEGIN

CREATE TABLE ContaBancaria

(

idContaBancaria UNIQUEIDENTIFIER NOT NULL PRIMARY KEY ROWGUIDCOL DEFAULT ( NEWSEQUENTIALID() ) ,

NomeConta VARCHAR(30)

);

INSERT dbo.ContaBancaria( NomeConta )VALUES ( ‘CEF’ ),( ‘BB’ );

END;

IF ( OBJECT_ID(‘Lancamentos’, ‘U’) IS NULL )

BEGIN

CREATE TABLE Lancamentos

(

idLancamento UNIQUEIDENTIFIER NOT NULL ROWGUIDCOL DEFAULT ( NEWSEQUENTIALID() ) ,

idContaBancaria UNIQUEIDENTIFIER NOT NULL FOREIGN KEY ( idContaBancaria ) REFERENCES dbo.ContaBancaria ( idContaBancaria ) ,

Historico VARCHAR(100) NOT NULL DEFAULT ( ‘Histórico padrão para lançamentos bancários.’ ) ,

NumeroLancamento INT NOT NULL ,

Data DATETIME ,

Valor DECIMAL(18, 2) ,

Credito BIT NOT NULL,

CONSTRAINT PKLancamentos PRIMARY KEY(idLancamento,Data)

);

END;

Agora vamos gerar um insert para a tabela de lançamentos gerando 1 milhão de registros balanceando entre os anos de 2011 até 2017, esse script demora cerca de 1 minuto.

— ==================================================================

–Observação:faz insert dos dados com valores aleatórios

— ==================================================================

DECLARE @dataInicio DATETIME = ‘2011-01-01’;

DECLARE @IsCredito BIT = 1;

DECLARE @IdContaBancariaCEF UNIQUEIDENTIFIER = ( SELECT TOP 1

CB.idContaBancaria

FROM dbo.ContaBancaria

AS CB

WHERE CB.NomeConta = ‘CEF’

);

DECLARE @IdContaBancariaBB UNIQUEIDENTIFIER = ( SELECT TOP 1

CB.idContaBancaria

FROM dbo.ContaBancaria AS CB

WHERE CB.NomeConta = ‘BB’

);

WITH CTE

AS ( SELECT 1 AS NumeroLancamento

UNION ALL

SELECT CTE.NumeroLancamento + 1

FROM CTE

WHERE CTE.NumeroLancamento < 1000000

),

Query

AS ( SELECT CTE.NumeroLancamento ,

TA.IdConta ,

TA.Data ,

TA.Valor ,

TA.Credito

FROM CTE

CROSS APPLY ( SELECT CASE WHEN ( CTE.NumeroLancamento

% 2 = 0 )

THEN @IdContaBancariaCEF

ELSE @IdContaBancariaBB

END AS IdConta ,

CAST(DATEADD(DAY,

ABS(CHECKSUM(NEWID())

% IIF(( CTE.NumeroLancamento <= 800000 ), 2555, 730)),

IIF(( CTE.NumeroLancamento <= 800000 ), ‘2011-01-01’, ‘2016-01-01’)) AS DATE) AS Data ,

CAST(ABS(CAST(( CAST(CHECKSUM(NEWID()) AS DECIMAL(18,

2)) / 1000000 ) AS DECIMAL(18,

2))) AS DECIMAL(18,

2)) AS Valor ,

IIF(( CTE.NumeroLancamento <= 600000 ), 0, 1) AS Credito

) AS TA

)

INSERT INTO dbo.Lancamentos WITH ( TABLOCK )

( idContaBancaria ,

NumeroLancamento ,

Data ,

Valor ,

Credito

)

SELECT Q.IdConta ,

Q.NumeroLancamento ,

Q.Data ,

Q.Valor ,

Q.Credito

FROM Query Q

OPTION ( MAXRECURSION 0 );

vamos analisar a distribuição de dados

— ==================================================================

SELECT Ano = YEAR(L.Data) ,

QuantidadeLancamento = FORMAT(COUNT(*), ‘N’, ‘pt-Br’)

FROM dbo.Lancamentos AS L

GROUP BY YEAR(L.Data)

ORDER BY YEAR(L.Data);

Nosso desafio e fazer uma forma de acesso para quando for buscado os registros de 2011 só leia as paginas referentes a registros de 2011 caso for buscado os registros de 2012 as rotinas só leia as paginas de 2012, e assim por diante.

habilitando SET STATISTICS IO ON  temos os seguintes dados

-- ==================================================================

— Vamos ver o tamanho da tabela em paginas

— ==================================================================

SELECT * FROM dbo.Lancamentos AS L –logical reads 14011,

SELECT ‘Tamanho em MB’, (14011 * 8) / 1024

Agora vamos criar as tabelas referentes a cada ano, de 2011 até  2016, o que vai mudar é a CONSTRAINT CHECK  que terá a regra de particionamento.

— ==================================================================

— Cria as tabelas para cada ano

— ==================================================================

CREATE TABLE LancamentosAno2011

(

idLancamento UNIQUEIDENTIFIER NOT NULL ROWGUIDCOL ,

idContaBancaria UNIQUEIDENTIFIER NOT NULL

FOREIGN KEY ( idContaBancaria ) REFERENCES dbo.ContaBancaria ( idContaBancaria ) ,

Historico VARCHAR(100) NOT NULL ,

NumeroLancamento INT NOT NULL ,

Data DATETIME NOT NULL ,

Valor DECIMAL(18, 2) ,

Credito BIT NOT NULL ,

CONSTRAINT PKidLancamento2011 PRIMARY KEY CLUSTERED ( idLancamento ,Data),

CONSTRAINT CkDataAno2011 CHECK ( Data >=‘2011-01-01 00:00:00’ AND Data <=‘2011-12-31 23:59:57’)

);

CREATE TABLE LancamentosAno2012

(

idLancamento UNIQUEIDENTIFIER NOT NULL

ROWGUIDCOL ,

idContaBancaria UNIQUEIDENTIFIER

NOT NULL

FOREIGN KEY ( idContaBancaria ) REFERENCES dbo.ContaBancaria ( idContaBancaria ) ,

Historico VARCHAR(100) NOT NULL ,

NumeroLancamento INT NOT NULL ,

Data DATETIME NOT NULL,

Valor DECIMAL(18, 2) ,

Credito BIT NOT NULL ,

CONSTRAINT PKidLancamento2012 PRIMARY KEY CLUSTERED ( idLancamento,Data),

CONSTRAINT CkDataAno2012 CHECK ( Data >=‘2012-01-01 00:00:00’ AND Data <=‘2012-12-31 23:59:57’)

);

CREATE TABLE LancamentosAno2013

(

idLancamento UNIQUEIDENTIFIER NOT NULL

ROWGUIDCOL ,

idContaBancaria UNIQUEIDENTIFIER

NOT NULL

FOREIGN KEY ( idContaBancaria ) REFERENCES dbo.ContaBancaria ( idContaBancaria ) ,

Historico VARCHAR(100) NOT NULL ,

NumeroLancamento INT NOT NULL ,

Data DATETIME NOT NULL ,

Valor DECIMAL(18, 2) ,

Credito BIT NOT NULL ,

CONSTRAINT PKidLancamento2013 PRIMARY KEY CLUSTERED ( idLancamento,Data),

CONSTRAINT CkDataAno2013 CHECK ( Data >=‘2013-01-01 00:00:00’ AND Data <=‘2013-12-31 23:59:57’)

);

CREATE TABLE LancamentosAno2014

(

idLancamento UNIQUEIDENTIFIER NOT NULL

ROWGUIDCOL ,

idContaBancaria UNIQUEIDENTIFIER

NOT NULL

FOREIGN KEY ( idContaBancaria ) REFERENCES dbo.ContaBancaria ( idContaBancaria ) ,

Historico VARCHAR(100) NOT NULL ,

NumeroLancamento INT NOT NULL ,

Data DATETIME NOT NULL ,

Valor DECIMAL(18, 2) ,

Credito BIT NOT NULL ,

CONSTRAINT PKidLancamento2014 PRIMARY KEY CLUSTERED ( idLancamento,Data ),

CONSTRAINT CkDataAno2014 CHECK ( Data >=‘2014-01-01 00:00:00’ AND Data <=‘2014-12-31 23:59:57’)

);

CREATE TABLE LancamentosAno2015

(

idLancamento UNIQUEIDENTIFIER NOT NULL

ROWGUIDCOL ,

idContaBancaria UNIQUEIDENTIFIER

NOT NULL

FOREIGN KEY ( idContaBancaria ) REFERENCES dbo.ContaBancaria ( idContaBancaria ) ,

Historico VARCHAR(100) NOT NULL ,

NumeroLancamento INT NOT NULL ,

Data DATETIME ,

Valor DECIMAL(18, 2) ,

Credito BIT NOT NULL ,

CONSTRAINT PKidLancamento2015 PRIMARY KEY CLUSTERED ( idLancamento ,Data),

CONSTRAINT CkDataAno2015 CHECK ( Data >=‘2015-01-01 00:00:00’ AND Data <=‘2015-12-31 23:59:57’)

);

CREATE TABLE LancamentosAno2016

(

idLancamento UNIQUEIDENTIFIER NOT NULL

ROWGUIDCOL ,

idContaBancaria UNIQUEIDENTIFIER

NOT NULL

FOREIGN KEY ( idContaBancaria ) REFERENCES dbo.ContaBancaria ( idContaBancaria ) ,

Historico VARCHAR(100) NOT NULL ,

NumeroLancamento INT NOT NULL ,

Data DATETIME NOT NULL,

Valor DECIMAL(18, 2) ,

Credito BIT NOT NULL ,

CONSTRAINT PKidLancamento2016 PRIMARY KEY CLUSTERED ( idLancamento,Data),

CONSTRAINT CkDataAno2016 CHECK ( Data >=‘2016-01-01 00:00:00’ AND Data <=‘2016-12-31 23:59:57’)

);

Agora para cada ano vamos inserir os registros na tabela respectiva.

#Code  faz os inserts para cada ano.

— ==================================================================

–Agora vamos executar o insert nessas tabelas, cada tabela criada

— terá os lançamentos do seu ano

— ==================================================================

INSERT INTO dbo.LancamentosAno2011

( idLancamento ,

idContaBancaria ,

Historico ,

NumeroLancamento,

Data ,

Valor ,

Credito

)

SELECT L.idLancamento ,

L.idContaBancaria ,

L.Historico ,

L.NumeroLancamento,

L.Data ,

L.Valor ,

L.Credito

FROM dbo.Lancamentos AS L

WHERE YEAR(L.Data) = 2011;

INSERT INTO dbo.LancamentosAno2012

( idLancamento ,

idContaBancaria ,

Historico ,

NumeroLancamento,

Data ,

Valor ,

Credito

)

SELECT L.idLancamento ,

L.idContaBancaria ,

L.Historico ,

L.NumeroLancamento,

L.Data ,

L.Valor ,

L.Credito

FROM dbo.Lancamentos AS L

WHERE YEAR(L.Data) = 2012;

INSERT INTO dbo.LancamentosAno2013

( idLancamento ,

idContaBancaria ,

Historico ,

NumeroLancamento,

Data ,

Valor ,

Credito

)

SELECT L.idLancamento ,

L.idContaBancaria ,

L.Historico ,

L.NumeroLancamento,

L.Data ,

L.Valor ,

L.Credito

FROM dbo.Lancamentos AS L

WHERE YEAR(L.Data) = 2013;

INSERT INTO dbo.LancamentosAno2014

( idLancamento ,

idContaBancaria ,

Historico ,

NumeroLancamento,

Data ,

Valor ,

Credito

)

SELECT L.idLancamento ,

L.idContaBancaria ,

L.Historico ,

L.NumeroLancamento,

L.Data ,

L.Valor ,

L.Credito

FROM dbo.Lancamentos AS L

WHERE YEAR(L.Data) = 2014;

INSERT INTO dbo.LancamentosAno2015

( idLancamento ,

idContaBancaria ,

Historico ,

NumeroLancamento,

Data ,

Valor ,

Credito

)

SELECT L.idLancamento ,

L.idContaBancaria ,

L.Historico ,

L.NumeroLancamento,

L.Data ,

L.Valor ,

L.Credito

FROM dbo.Lancamentos AS L

WHERE YEAR(L.Data) = 2015;

INSERT INTO dbo.LancamentosAno2016

( idLancamento ,

idContaBancaria ,

Historico ,

NumeroLancamento,

Data ,

Valor ,

Credito

)

SELECT L.idLancamento ,

L.idContaBancaria ,

L.Historico ,

L.NumeroLancamento,

L.Data ,

L.Valor ,

L.Credito

FROM dbo.Lancamentos AS L

WHERE YEAR(L.Data) = 2016;

 — ==================================================================
— Deleta os lançamentos que foram migrados para as outras tabelas
— ==================================================================

DELETE L

FROM dbo.Lancamentos AS L

JOIN ( SELECT LA.idLancamento

FROM dbo.LancamentosAno2011 AS LA

UNION ALL

SELECT LA.idLancamento

FROM dbo.LancamentosAno2012 AS LA

UNION ALL

SELECT LA.idLancamento

FROM dbo.LancamentosAno2013 AS LA

UNION ALL

SELECT LA.idLancamento

FROM dbo.LancamentosAno2014 AS LA

UNION ALL

SELECT LA.idLancamento

FROM dbo.LancamentosAno2015 AS LA

UNION ALL

SELECT LA.idLancamento

FROM dbo.LancamentosAno2016 AS LA

) AS LANAntigo ON LANAntigo.idLancamento = L.idLancamento

— ==================================================================
–Aqui temos um ponto de atenção,
–acabamos de rodar um DELETE em mais ou menos (750000 mil linhas)
–e mesmo assim temos logical reads 14011,
— ==================================================================

SET STATISTICS IO ON;
SELECT *
FROM dbo.Lancamentos AS L;
SET STATISTICS IO OFF;

Isso e motivado pela fragmentação do índice ,para isso vamos fazer um REBUILD

ALTER INDEX PKLancamentos ON dbo.Lancamentos REBUILD

rode novamente para ver a quantidade de paginas agora

SET STATISTICS IO ON;
SELECT *
FROM dbo.Lancamentos AS L;
SET STATISTICS IO OFF;

— ==================================================================
–Agora vamos criar a chave de particionamento da tabela
— ==================================================================
ALTER TABLE dbo.Lancamentos ADD CONSTRAINT CkDataLancamento2017 CHECK ( Data >= ‘2017-01-01 00:00:00’);

— ==================================================================
–Agora vamos criar a View Particionada, como o nosso objetivo e ter uma view que receba comandos DML precisamos retornar todas as colunas.
— ==================================================================

CREATE VIEW VwBuscaLancamentos AS

SELECT LA.idLancamento ,

LA.idContaBancaria ,

LA.Historico ,

La.NumeroLancamento,

LA.Data ,

LA.Valor ,

LA.Credito

FROM dbo.LancamentosAno2011 AS LA — logical reads 1606,

UNION ALL

SELECT LA.idLancamento ,

LA.idContaBancaria ,

LA.Historico ,

La.NumeroLancamento,

LA.Data ,

LA.Valor ,

LA.Credito

FROM dbo.LancamentosAno2012 AS LA –logical reads 1615

UNION ALL

SELECT LA.idLancamento ,

LA.idContaBancaria ,

LA.Historico ,

La.NumeroLancamento,

LA.Data ,

LA.Valor ,

LA.Credito

FROM dbo.LancamentosAno2013 AS LA — logical reads 1599

UNION ALL

SELECT LA.idLancamento ,

LA.idContaBancaria ,

LA.Historico ,

La.NumeroLancamento,

LA.Data ,

LA.Valor ,

LA.Credito

FROM dbo.LancamentosAno2014 AS LA –logical reads 1601

UNION ALL

SELECT LA.idLancamento ,

LA.idContaBancaria ,

LA.Historico ,

La.NumeroLancamento,

LA.Data ,

LA.Valor ,

LA.Credito

FROM dbo.LancamentosAno2015 AS LA –logical reads 1611

UNION ALL

SELECT LA.idLancamento ,

LA.idContaBancaria ,

LA.Historico ,

La.NumeroLancamento,

LA.Data ,

LA.Valor ,

LA.Credito

FROM dbo.LancamentosAno2016 AS LA –logical reads 3001

UNION ALL

SELECT LA.idLancamento ,

LA.idContaBancaria ,

LA.Historico ,

La.NumeroLancamento,

LA.Data ,

LA.Valor ,

LA.Credito

FROM dbo.Lancamentos AS LA; — logical reads 2977

GO

–Pronto temos nossa View particionada , agora vamos a alguns testes.

SET STATISTICS IO ON;

–Registros de 2011

SELECT COUNT(*)

FROM dbo.VwBuscaLancamentos AS VBL

WHERE VBL.Data >=‘2011-01-01’ AND Data <=‘2011-12-31’

(1 row(s) affected)
Table ‘LancamentosAno2011’. Scan count 1, logical reads 1606,

–Registros de 2012

SELECT COUNT(*)

FROM dbo.VwBuscaLancamentos AS VBL

WHERE VBL.Data >=‘2012-01-01’ AND Data <=‘2012-12-31’

(1 row(s) affected)
Table ‘LancamentosAno2012’. Scan count 1, logical reads 1615,

–Registros de 2013

SELECT COUNT(*)

FROM dbo.VwBuscaLancamentos AS VBL

WHERE VBL.Data >=‘2013-01-01’ AND Data <=‘2013-12-31’

(1 row(s) affected)
Table ‘LancamentosAno2013’. Scan count 1, logical reads 1599,

–Registros entre 2015-01-01 a 2016-12-31

SELECT COUNT(*)

FROM dbo.VwBuscaLancamentos AS VBL

WHERE VBL.Data >=‘2015-01-01’ AND Data <=‘2016-12-31’

Table ‘LancamentosAno2016’. Scan count 1, logical reads 3001,
Table ‘LancamentosAno2015’. Scan count 1, logical reads 1611,

–registros a partir de 2017

SELECT *

FROM dbo.VwBuscaLancamentos AS VBL

WHERE VBL.Data >=‘2017-01-01’

(213215 row(s) affected)
Table ‘Lancamentos’. Scan count 1, logical reads 2977,

 As consulta pode ser executada mais rapidamente porque o SQL Server está fazendo menos trabalho.

Agora Vamos Aos Inserts ,Deletes e Updates na View particionada.

TRUNCATE TABLE dbo.LancamentosAno2011
TRUNCATE TABLE dbo.LancamentosAno2012
TRUNCATE TABLE dbo.LancamentosAno2013
TRUNCATE TABLE dbo.LancamentosAno2014
TRUNCATE TABLE dbo.LancamentosAno2015
TRUNCATE TABLE dbo.LancamentosAno2016
TRUNCATE TABLE dbo.Lancamentos

— ==================================================================

–Com as tabelas vazias vamos fazer os Inserts

–Primeira observação , a view só podera responder por insert se todos os campos das tabelas forem retornada por ela

–ou seja se vc omitir um campo das tabelas a view não poderá sofrer inserts ,

— ==================================================================

SELECT * FROM dbo.VwBuscaLancamentos AS VBL

–Recuperar a chave gerada

DECLARE @idconta UNIQUEIDENTIFIER=(SELECT TOP 1 CB.idContaBancaria FROM dbo.ContaBancaria AS CB)

INSERT INTO dbo.VwBuscaLancamentos

( idLancamento ,idContaBancaria , Historico ,NumeroLancamento ,Data , Valor ,Credito)

VALUES ( NEWID(), @idconta ,‘Lancamento 2011’ ,1,‘2011-02-10’, 100,1 );

INSERT INTO dbo.VwBuscaLancamentos

( idLancamento ,idContaBancaria , Historico ,NumeroLancamento ,Data , Valor ,Credito)

VALUES ( NEWID(),@idconta,‘Lancamento 2012’ ,1,‘2012-05-10’, 100,1 );

INSERT INTO dbo.VwBuscaLancamentos

( idLancamento ,idContaBancaria , Historico ,NumeroLancamento ,Data , Valor ,Credito)

VALUES ( NEWID(),@idconta ,‘Lancamento 2013’ ,1,‘2013-05-10’, 100,1 );

INSERT INTO dbo.VwBuscaLancamentos

( idLancamento ,idContaBancaria , Historico ,NumeroLancamento ,Data , Valor ,Credito)

VALUES ( NEWID(),@idconta ,‘Lancamento 2014’ ,1,‘2014-05-10’, 100,1 );

INSERT INTO dbo.VwBuscaLancamentos

( idLancamento ,idContaBancaria , Historico ,NumeroLancamento ,Data , Valor ,Credito)

VALUES ( NEWID(),@idconta ,‘Lancamento 2015’ ,1,‘2015-05-10’, 100,1 );

INSERT INTO dbo.VwBuscaLancamentos

( idLancamento ,idContaBancaria , Historico ,NumeroLancamento ,Data , Valor ,Credito)

VALUES ( NEWID(),@idconta ,‘Lancamento 2016’ ,1,‘2016-10-10’, 100,1 );

INSERT INTO dbo.VwBuscaLancamentos

( idLancamento ,idContaBancaria , Historico ,NumeroLancamento ,Data , Valor ,Credito)

VALUES ( NEWID(),@idconta ,‘Lancamento 2019’ ,1,‘2019-10-10’, 100,1 );

vc já viu comando de UPDATE fazendo por trás dos panos insert e deletes ??? se não

veja só

— ==================================================================

— rode esses dois selects primeiro , apos rode o update

— ==================================================================

SELECT * FROM dbo.LancamentosAno2011 AS LA

SELECT * FROM dbo.Lancamentos AS L

UPDATE dbo.VwBuscaLancamentos SET Data =‘2019-10-10’ WHERE Historico =‘Lancamento 2011’

SELECT * FROM dbo.LancamentosAno2011 AS LA

SELECT * FROM dbo.Lancamentos AS L

–Agora o Delete

SET STATISTICS IO ON

DELETE VBL FROM dbo.VwBuscaLancamentos AS VBL

WHERE VBL.Data = ‘2014-05-10 00:00:00.000’

SET STATISTICS IO OFF

E isso aee, muito poderosa essa solução , até a proxima.

Referencias :

https://technet.microsoft.com/en-us/library/ms187067(v=sql.105).aspx

https://www.brentozar.com/archive/2016/09/partitioned-views-guide/

http://sqlmag.com/blog/partitioned-tables-v-partitioned-views-why-are-they-even-still-around

What is a Partitioned View?

Localizando Views Indexadas

Essa dica aqui é simples e direta, para localizar as views indexadas no banco de dados basta executar esse script.

/*Localizando Views Indexadas*/

SELECT

o.name AS view_name ,

i.name AS index_name

FROM sysobjects o

INNER JOIN sysindexes i ON o.id = i.id

WHERE o.xtype = ‘V’; — View

Index Scan , Index Seek e Key Lookup

vamos entender um pouco sobre Index Scan , Index Seek e Key Lookup.

Conheceremos de forma prática, alguns operadores e estruturas , então antes de começar rode esse script para criar duas tabelas uma chamada bancos com dois registros e outra de lançamentos com 1 milhão de registros dividido em 5 anos.

/**********************Script ******************************************// 

IF (OBJECT_ID(‘Lancamentos’, ‘U’) IS        NOT  NULL)

BEGIN

DROP TABLE [Lancamentos];

END;

CREATE TABLE [dbo].[Lancamentos]

(

[idLancamento] [UNIQUEIDENTIFIER] NOT NULL

ROWGUIDCOL

DEFAULT ( NEWSEQUENTIALID() ) ,

[idBanco] INT NOT NULL ,

[Historico] [VARCHAR](100)

COLLATE Latin1_General_CI_AI

NOT NULL

DEFAULT ( ‘Histórico padrão para lançamentos bancários.’ ) ,

[NumeroLancamento] [INT] NOT NULL ,

[Data] [DATETIME] NOT NULL ,

[Valor] [DECIMAL](18, 2) NULL ,

[Credito] [BIT] NOT NULL,

);

 

GO

IF (OBJECT_ID(‘Bancos’, ‘U’) IS        NOT  NULL)

BEGIN

DROP TABLE [Bancos];

END;

CREATE TABLE [dbo].[Bancos]

(

[idBanco] INT NOT NULL IDENTITY(1, 1),

[NomeBanco] [VARCHAR](30) COLLATE Latin1_General_CI_AI NULL

);

 

/*Adiciona um campo PK  */

ALTER TABLE dbo.Bancos ADD CONSTRAINT PKbancos PRIMARY KEY(idBanco)

 

INSERT INTO dbo.Bancos

(

[NomeBanco]

)

 

VALUES (‘Banco A’),

(‘Banco B’)

 

 

— ==================================================================

–Observação: cria tabela com 1 milhão de registros

— ==================================================================

DECLARE @dataInicio DATETIME = ‘2011-01-01’;

 

DECLARE @IsCredito BIT = 1;

 

DECLARE @IdBanco1 INT = (

SELECT TOP 1

CB.idBanco

FROM dbo.Bancos AS CB

WHERE CB.NomeBanco = ‘Banco A’

);

DECLARE @IdBanco2 INT = (

SELECT TOP 1

CB.idBanco

FROM dbo.Bancos AS CB

WHERE CB.NomeBanco = ‘Banco B’

);

 

WITH CTE

AS (SELECT 1 AS NumeroLancamento

UNION ALL

SELECT CTE.NumeroLancamento + 1

FROM CTE

WHERE CTE.NumeroLancamento < 1000000

),

Query

AS (SELECT CTE.NumeroLancamento,

TA.IdConta,

TA.Data,

TA.Valor,

TA.Credito

FROM CTE

CROSS APPLY

(

SELECT CASE

WHEN (CTE.NumeroLancamento % 2 = 0) THEN

@IdBanco1

ELSE

@IdBanco2

END AS IdConta,

CAST(DATEADD(

DAY,

ABS(CHECKSUM(NEWID()) % IIF((CTE.NumeroLancamento <= 800000), 2555, 730)),

IIF((CTE.NumeroLancamento <= 800000), ‘2011-01-01’, ‘2016-01-01’)

) AS DATE) AS Data,

CAST(ABS(CAST((CAST(CHECKSUM(NEWID()) AS DECIMAL(18, 2)) / 1000000) AS DECIMAL(18, 2))) AS DECIMAL(18, 2)) AS Valor,

IIF((CTE.NumeroLancamento <= 600000), 0, 1) AS Credito

) AS TA

)

INSERT INTO dbo.Lancamentos WITH (TABLOCK)

(

idBanco,

NumeroLancamento,

Data,

Valor,

Credito

)

SELECT Q.IdConta,

Q.NumeroLancamento,

Q.Data,

Q.Valor,

Q.Credito

FROM Query Q

OPTION (MAXRECURSION 0);

 

/*****************************Fim *************************/

 

No modo de armazenamento IN_ROW_DATA temos duas estruturas de armazenamento de dados,

Table Heap : é a estrutura de tabela quando ela não possuir Índice Clustered

Clustered Table, e a arquitetura da tabela quando ela possuir um Índice Clustered ,ou seja e como os dados são armazenados das páginas de dados.

Caracteristicas  da Clustered Table :

• Os dados são armazenados em ordem com base na chave de índice Clustered.
• Os dados podem ser recuperados rapidamente com base na chave de índice agrupada, se a consulta usar as colunas indexadas
• As páginas de dados estão vinculadas para um acesso sequencial mais rápido
• É necessário tempo adicional para manter o índice agrupado com base em INSERTS, UPDATES e DELETES
• É necessário espaço adicional para armazenar árvore de índice em cluster
• Essas tabelas têm um índice index_id de 1 na exibição do catálogo sys.indexes

Estrutura Cluster 

 

Index Scan  

todas as páginas do índice são digitalizadas, se for  um CLUSTERED INDEX ,toda a tabela e alocada em memória, pois o CLUSTERED INDEX é a própria tabela.

se for um nonclustered index os dados do índice são armazenados em memória

uma vez que scan toca cada linha da tabela, seja ou não qualificada, o custo é proporcional ao número total de linhas na tabela. Assim, um scan  é uma estratégia eficiente se a tabela for pequena ou se a maioria das linhas se qualificarem para o predicado.

Abaixo temos um exemplo simples da arquitetura B-tree.

 

Veja no plano de execução o  operador index scan:

 

SELECT * FROM  dbo.Bancos

E se ativamos o SET STATISTICS PROFILE, veremos os seguintes objetos

SET STATISTICS PROFILE ON

SELECT * FROM  dbo.Bancos

SET STATISTICS PROFILE OFF 

 

operador Clustered Index Seek:

Index Seek usa a capacidade de recuperar linhas de um índice clusterizado com base em um valor de uma chave.Assim ele navega na arvore usando a chave como referência

uma vez que uma busca só toca as linhas que se qualificam e as páginas que contêm as linhas qualificadas, o custo é proporcional ao número de linhas e páginas qualificadas em vez do número total de linhas na tabela.

 

 

Imagem do operador Index Seek.

 

Veja no plano de execução

SELECT * FROM  dbo.Bancos

WHERE idBanco =2

E Ativando o  SET STATISTICS PROFILE temos

SET STATISTICS PROFILE ON  

SELECT * FROM  dbo.Bancos

WHERE idBanco =2

SET STATISTICS PROFILE OFF 

A coluna Argument contém o nome do índice clusterizado que está sendo usado e o predicado SEEK:().

Nome do Objeto:

|–Clustered Index Seek(OBJECT:([TSQL2016].[dbo].[Bancos].[PKbancos]),

Predicado de pesquisa:

SEEK:([TSQL2016].[dbo].[Bancos].[idBanco]=CONVERT_IMPLICIT(int,[@1],0)) ORDERED FORWARD)

Qualquer acesso a dados que não são chaves que índice, serão feitos por Scan, agora o Seek e sempre feito pela chave de índice,e inicia-se na pagina root da b-tree

Abaixo temos a imagem que mostra a estrutura   de arvore B-tree,  o SQL Server  utiliza o algoritmo de índice B-tree +,  que fica para outro post. Vale a pena acessar esse link e brincar um pouco com o algoritmo.

 

Essa imagem monstra a diferença entre Scan e Seek ,o Scan pode ser feito no Índice (index Scan) ou pode ser na tabela (Table Scan), independentemente  o Scan digitaliza-rá todos os dados.

Os pontos em vermelhos são os operadores do plano de execução e seus paramentos.

 

A tabela Bancos tem como arquitetura de TABLE CLUSTERED, pois possui uma chave do tipo CLUSTERED , entretanto a de Lançamento e do tipo HEAP (sobre Heaps leia esse artigo)

Vamos converter a Heap em CLUSTERED

/*Converte a tabela de HEAP para Cluster*/

ALTER TABLE dbo.Lancamentos ADD CONSTRAINT PKLancamentos PRIMARY KEY(idLancamento)

Se rodarmos esse Select abaixo o SQL fará um SCAN na tabela lendo ela completa , veja com o plano de execução ativado e com o SET STATISTICS IO também ativado

SET STATISTICS IO ON

SELECT * FROM  dbo.Lancamentos

SET STATISTICS IO OFF

Consumo de I/O

(1000000 rows affected)

Tabela ‘Lancamentos’. Número de verificações 1, leituras lógicas 12390,

Agora, se tivermos um argumento de pesquisa em uma chave do índice , o SQL fará um Index SEEK

,ou seja o Sql com a chave do índice navegará pela arvore a procura do registro com valor da chave

 

Veja o exemplo, como os guids são aleatórios busque uma chave na tabela e faça o teste.

SET STATISTICS IO ON 

SELECT * FROM  dbo.Lancamentos

WHERE idLancamento =’4ABC902D-11AD-E711-8664-F48E38FE3F0D’

SET STATISTICS IO OFF 

Então temos @1 como argumento de pesquisa, assim Sql server navega pela estrutura do índice a procura da chave (Index SEEK)

 

Veja o Custo de I/0 agora.

1 row affected)

Tabela ‘Lancamentos’. Número de verificações 0, leituras lógicas 3, 

Isso é lindo quando o acesso a tabela e feito apenas pela PK , mas sabemos que não e assim que acontece.

Então em resumo, “geralmente” quando o predicado da query é uma chave do índice e feito um index Seek.

Geralmente ,pois existem diversos fatores para o sql usar ou não  um índice, não somente a coluna ser um critério do índice, mas por enquanto vamos ficar com isso , para entendemos o 3º ponto

Que o operador de KEY LOOKUP. 

Veja o exemplo , recupere qualquer valor de idLancamento  e cole na query , veja que o criterio do where  pede um valor que é chave de indice  , logo inicialmente temos um index seek

 WHERE l.idLancamento = ‘‘F0E8EFE1-25BE-E711-8670-F48E38FE3F0D‘

SET STATISTICS IO ON; 

SET STATISTICS TIME ON; 

SELECT *

FROM dbo.Lancamentos AS l

WHERE l.idLancamento =’F0E8EFE1-25BE-E711-8670-F48E38FE3F0D’

SET STATISTICS IO OFF; 

SET STATISTICS TIME ON; 

veja o consumo de I/O e CPU  

Agora vamos fazer o mesmo com a coluna NumeroLancamento

SET STATISTICS IO ON; 

SELECT * 

FROM dbo.Lancamentos AS l

WHERE l.NumeroLancamento = 999999

SET STATISTICS IO OFF; 

Sabemos que o retorno será apenas uma linha, entretanto  não temos um índice cobrindo o predicado, assim o Sql server não tem outra opção a não ser escanear os dados da tabela 

Vamos adicionar um indice pela coluna NumeroLancamento

CREATE NONCLUSTERED INDEX idxLancamentosNumeroLancamento ON dbo.Lancamentos(NumeroLancamento)

agora veja o  plano de execução :

SET STATISTICS IO ON
SELECT l.idLancamento,
l.idBanco,
l.NumeroLancamento,
l.Data,
l.Valor,
l.Credito FROM dbo.Lancamentos AS l
WHERE l.NumeroLancamento =999999
SET STATISTICS IO OFF;

Quando criamos o índice  na coluna NumeroLancamento o  Sql server automaticamente cria  uma STATISTICS e assim ele sabe estimar que agora essa consulta retorna poucos dados,   assim ele faz Seek na coluna  NumeroLancamento  igual  999999 . e usa o valor da PK da linha para chamar o  operador de KEY Lookup  buscando todos os dados da tabela Lancamentos que  são pedidos do select mas que não estão no indice

veja :

perceba que na área de output List não está a coluna IdLancamento, por que ?

nos acessamos o índice criado ,pela coluna NumeroLancamento e a query requita a coluna IdLancamento ,  os dados não  foram buscados pelo operador KEY LOOKUP pois

quando criamos um índice NONCLUSTERED a PK e levada junto para o arquivo de índice , veja:

 

se ficou alguma dúvida, ou alguma coisa que deveria complementar, fique a vontade para me dizer.

por hoje é isso.

 

https://stackoverflow.com/questions/40486539/index-seek-vs-index-scan-in-sql-server

https://msdn.microsoft.com/en-us/library/ms177443(v=sql.105).aspx

http://sqlity.net/en/2445/b-plus-tree/

https://stackoverflow.com/questions/40486539/index-seek-vs-index-scan-in-sql-server

O que é melhor? Um Index Seek ou Clustered Index Scan?

 

 

 

 

 

 

 

 

 

 

 

 

 

Heap tables ,Table Scan e RID LOOKUP

Hoje vamos falar sobre Heap tables ,Table Scan e RID LOOKUP

Antes de mais nada vamos entender algumas estruturas de tabelas.

1)Estruturas de Heap

• Em resumo Heap é uma tabela sem índice CLUSTERED.
• Se uma tabela for um heap e não tiver um índice , a tabela inteira deverá será examinada (Table Scan)
• Os dados são armazenados no Heap sem especificar uma ordem. Normalmente, os dados são armazenados inicialmente na ordem em que as linhas são inseridas na tabela,
• Podem ser criados um ou mais índices NONCLUSTERED em tabelas armazenadas como um Heap.
• Os dados específicos não podem ser recuperados rapidamente, a menos que também existam índices NONCLUSTERED 

As páginas de dados não estão vinculadas, portanto, o acesso seqüencial precisa se referir às páginas do mapa de alocação de índice (IAM)

• Heaps têm uma linha em sys.partitions, com index_id = 0

Quando uma tabela é armazenada como heap, linhas individuais são identificadas por referência a um RID (identificador de linha) que consiste em número do arquivo, número da página de dados e local na página. O ID de linha é uma estrutura pequena e eficiente. Às vezes, os arquitetos de dados usam heaps quando os dados são sempre acessados por índices não clusterizados e o RID é menor que uma chave de índice clusterizado.

Os dados reais em sua tabela são armazenados em Páginas, exceto os dados BLOB. Se uma coluna contiver dados BLOB, um ponteiro de 16 bytes é usado para fazer referência à página BLOB. A página é a menor unidade de armazenamento de dados no Microsoft SQL Server. Uma página contém os dados nas linhas.  Uma linha só pode residir em uma página. Cada Página pode conter 8KB de informação, devido a isso, o tamanho máximo de uma Linha é de 8KB. Um grupo de 8 páginas adjacentes é chamado de extensão.

A coluna sysindexes.FirstIAM aponta para a primeira página IAM na cadeia de páginas IAM que gerenciam o espaço alocado ao heap,o SQL Server usa as páginas IAM (Index Allocation Map) para navegar pelo Heap. As páginas de dados e as linhas dentro delas não estão em nenhuma ordem específica e não estão ligadas entre si . A única conexão lógica entre páginas de dados é a registrada nas páginas do IAM.

Que em resumo é a localização física da pagina.

Estrutura de uma HEAP 

Em uma tabela do tipo Heap ,a única forma de acessa-la e fazendo um SCAN , pois os dados não estão organizados, a não ser que você crie um INDEX NONCLUSTERED 

Assim em Tabelas do tipo  HEAP e muito comum dois operadores.

Table Scan e RID Lookup.

Table Scan:  O operador Table Scan recupera todas as linhas da tabela especificada na coluna Argument do plano de execução da consulta. Se um predicado WHERE:() aparecer na coluna Argument, somente as linhas que atenderem ao predicado serão retornadas. 

RID Lookup : é um indicador de pesquisa em um Heap que usa um identificador de linha fornecido (RID). A coluna Argumento contém o rótulo de indicador usado para pesquisar a linha na tabela e o nome da tabela em que a linha é pesquisada.

Vamos ver como isso funciona, vamos criar duas tabelas uma com PK ,e outra sem .

 

IF (OBJECT_ID(‘Bancos’, ‘U’) IS NOT NULL)
BEGIN

DROP TABLE dbo.Bancos;

END;

CREATE TABLE [dbo].[Bancos]
(
[idBanco] INT NOT NULL IDENTITY(1, 1),
[NomeBanco] [VARCHAR](30) COLLATE Latin1_General_CI_AI NULL
);

ALTER TABLE dbo.Bancos ADD CONSTRAINT PKbancos PRIMARY KEY(idBanco)




/*Insere dois bancos*/

INSERT  INTO dbo.Bancos

( [NomeBanco] )

VALUES  ( ‘Banco A’ ),

( ‘Banco B’ );

Vamos ver algumas características:

SELECT OBJECT_NAME(PAR.object_id) AS TableName,
PAR.index_id,
PAR.rows,
PAR.data_compression_desc ,
p.type_desc,
p.filegroup_id,
p.total_pages,
p.used_pages,
p.data_pages,
p.first_page,
p.root_page,
p.first_iam_page
FROM sys.partitions PAR
JOIN sys.system_internals_allocation_units p ON PAR.partition_id =p.container_id
WHERE PAR.object_id = OBJECT_ID(‘Bancos’);

Agora vamos criar uma tabela de lançamentos com um 1 milhão de registros analisarmos a diferença. 

CREATE TABLE [dbo].[Lancamentos]
(
[idLancamento] [UNIQUEIDENTIFIER] NOT NULL ROWGUIDCOL
DEFAULT (NEWSEQUENTIALID()),
[idBanco] INT NOT NULL,
[Historico] [VARCHAR](100) COLLATE Latin1_General_CI_AI NOT NULL
DEFAULT (‘Histórico padrão para lançamentos bancários.’),
[NumeroLancamento] [INT] NOT NULL,
[Data] [DATETIME] NOT NULL,
[Valor] [DECIMAL](18, 2) NULL,
[Credito] [BIT] NOT NULL,
);

/*Executa insert dinâmico*/

DECLARE @dataInicio DATETIME = ‘2014-01-01’;

DECLARE @IsCredito BIT = 1;

DECLARE @IdBanco1 INT = (
SELECT TOP 1
CB.idBanco
FROM dbo.Bancos AS CB
WHERE CB.NomeBanco = ‘Banco A’
);

DECLARE @IdBanco2 INT = (
SELECT TOP 1
CB.idBanco
FROM dbo.Bancos AS CB
WHERE CB.NomeBanco = ‘Banco B’
);

WITH CTE
AS (SELECT 1 AS NumeroLancamento
UNION ALL
SELECT CTE.NumeroLancamento + 1
FROM CTE
WHERE CTE.NumeroLancamento < 1000000
),
Query
AS (SELECT CTE.NumeroLancamento,
TA.IdConta,
TA.Data,
TA.Valor,
TA.Credito
FROM CTE
CROSS APPLY
(
SELECT CASE
WHEN (CTE.NumeroLancamento % 2 = 0) THEN
@IdBanco1
ELSE
@IdBanco2
END AS IdConta,
CAST(DATEADD(
DAY,
ABS(CHECKSUM(NEWID()) % IIF((CTE.NumeroLancamento <= 800000), 2555, 730)),
IIF((CTE.NumeroLancamento <= 800000), ‘2011-01-01’, ‘2016-01-01’)
) AS DATE) AS Data,
CAST(ABS(CAST((CAST(CHECKSUM(NEWID()) AS DECIMAL(18, 2)) / 1000000) AS DECIMAL(18, 2))) AS DECIMAL(18, 2)) AS Valor,
IIF((CTE.NumeroLancamento <= 600000), 0, 1) AS Credito
) AS TA
)
INSERT INTO dbo.Lancamentos WITH (TABLOCK)
(
idBanco,
NumeroLancamento,
Data,
Valor,
Credito
)
SELECT Q.IdConta,
Q.NumeroLancamento,
Q.Data,
Q.Valor,
Q.Credito
FROM Query Q
OPTION (MAXRECURSION 0);

Agora vamos ver as características da tabela de Lançamentos. 

SELECT OBJECT_NAME(PAR.object_id) AS TableName,
PAR.index_id,
PAR.rows,
PAR.data_compression_desc ,
p.type_desc,
p.filegroup_id,
p.total_pages,
p.used_pages,
p.data_pages,
p.first_page,
p.root_page,
p.first_iam_page
FROM sys.partitions PAR
JOIN sys.system_internals_allocation_units p ON PAR.partition_id =p.container_id
WHERE PAR.object_id = OBJECT_ID(‘lancamentos’);

Vamos fazer um Select na tabela de banco, com o plano de execução ativado

Assim temos, quando a tabela tem CLUSTERED INDEX e feito um Index Scan (tema do próximo artigo)

SELECT * FROM dbo.Bancos 

Quando e feito um select em tabela sem CLUSTERED INDEX e feito o table Scan

SELECT * FROM dbo.Lancamentos

Vamos ativar o  SET STATISTICS IO para verificar a quantidade de paginas acessada

SET STATISTICS IO  ON    

SELECT * FROM dbo.Lancamentos

SET STATISTICS IO  OFF ,

Veja o resultado:

(1000000 rows affected)

Table ‘Lancamentos’. Scan count 1, logical reads 12346, 

Agora vamos fazer o mesmo para retornar uma linha

SET STATISTICS IO  ON

SELECT * FROM dbo.Lancamentos

WHERE NumeroLancamento =55555

SET STATISTICS IO  OFF 

Agora veja o resultado;

(1 row affected)

Table ‘Lancamentos’. Scan count 5, logical reads 12346, 

Agora vamos fazer um select com JOIN  buscando da tabela de lancamentos apenas 1 registro

SET STATISTICS IO ON
SELECT B.idBanco,
B.NomeBanco,
L.idLancamento,
L.idBanco,
L.Historico,
L.NumeroLancamento,
L.Data,
L.Valor,
L.Credito FROM dbo.Bancos B
JOIN dbo.Lancamentos L ON L.idBanco = B.idBanco
WHERE L.NumeroLancamento =656564
SET STATISTICS IO OFF

 

Veja o custo do Table Scan com 97% da query

e a quantidade de I/O foi de :

 

Lembra que falamos que em uma tabela HEAP, existe um cara que é o identificador da linha (RID)
Agora vamos criar um NONCLUSTERED INDEX para ver o Operador RID

O RID entra em cena para buscar os dados que são pedidos na query mas não estão no índice.

vamos criar um índice na tabela lançamentos

CREATE NONCLUSTERED INDEX IdxLancamentos ON dbo.Lancamentos(NumeroLancamento) INCLUDE(idLancamento,idBanco,Data,Valor,Credito)

–vai ficar de fora o campo Histórico nos dados do índice

agora vamos rodar o mesmo select

SET STATISTICS IO ON
SELECT B.idBanco,
B.NomeBanco,
L.idLancamento,
L.idBanco,
L.Historico,
L.NumeroLancamento,
L.Data,
L.Valor,
L.Credito FROM dbo.Bancos B
JOIN dbo.Lancamentos L ON L.idBanco = B.idBanco
WHERE L.NumeroLancamento =656564
SET STATISTICS IO OFF

 

 

Agora veja a quantidade de IO

Por hoje  é isso ,aprendemos a diferença entre Table HEAP e Table CLUSTERED, e os operadores Table Scan e RID Lookup.

Abraço.

 

 

Usando profile para capturar Stored Procedures

Em algumas aplicações temos procedures com muitos parâmetros , 20, 30 50 ,  quando precisamos debugar essas procedures afim de descubrir algum erro ou entender o que está se passando no Sql server podemos usar o Porfile como abaixo

No exemplo vamos descobrir os paramentros  usado pela aplicação na procedure

HumanResources.uspUpdateEmployeeHireInfo do banco AdventureWorks2012

para simular aplicação usarei um aplicativo muito útil de test de performasse.

SQLQueryStress do  Adam Machanic

caso vc tenha uma aplicação esse passo não e necessário.

Executarei essa procedure com esses paramentos

EXEC HumanResources.uspUpdateEmployeeHireInfo @BusinessEntityID = 10, — int
@JobTitle = N’Engineering Manager’, — nvarchar(50)
@HireDate = ‘2017-09-27 11:32:22’, — datetime
@RateChangeDate = ‘2017-09-27 11:32:22’, — datetime
@Rate = NULL, — money
@PayFrequency = 23.72, — tinyint
@CurrentFlag = 1 — Flag

1. com o aplicativo aberto

 

clicamos em Database e configuramos a conexão com o banco. onde vc coloca o nome do seu servidor , o modo de autenticação e o nome do banco

 

Agora inicie o SQL Server Profiler, conecte no seu servidor

marque a opção Show all columns e marque a opção nome do banco

 

Na aba Events Selection marque o nome da aplicação  e o nome do banco que vc está conectado, se for em ambiente de produção , o nome da aplicação e importante para reduzir a quantidade de querys que vai ser capturada , mas se for em ambiente de teste basta o nome do banco .

 

pronto . basta executar a procedure na aplicação e capturar os paramentos no profile

 

 

 

por enquando e isso.

Conhecendo Views Parte III(VIEWS INDEXADAS)

A criação de instruções de consulta é na maioria dos casos simples, mas quando repetidos diversas vezes os mesmos códigos, pode-se começar a ficar cansativa a sua criação.
Para armazenar estas consultas no banco, utiliza-se um recurso já antigo de bancos de dados chamados View.
Uma View é uma representação virtual de uma tabela, e pode conter colunas de uma ou várias tabelas físicas ou até mesmo de outras Views. Na maioria das vezes, as Views não armazenam os dados em banco, elas consultam os dados que foram selecionados em suas tabelas de origem.

Exceto as VIEWS INDEXADAS

TABELAS CANDIDATAS A VIEWS INDEXADAS
1) Aplicações OLAP Datawerehouse

2)  Selects com muitos joins

3) tabelas de grande volume de dados repetidamente usados em várias consultas

3) Views que agregam dados

4) Tabelas com poucos inserts, deletes e updates

Premissas para criar um View Indexada

1.Verificar se as opções SET estão corretas para todas as tabelas existentes que serão referenciadas na exibição.

2.Verifique se as opções SET da sessão estão definidas corretamente antes de criar a view.

3.Verificar se a  view é determinística.

4.Verificar as regras aplicadas a construção de views.

5.Criar a view usando a opção WITH  SCHEMABINDING.

6.Crie o índice UNIQUE CLUSTERED  na view.

 

2.Opções SET necessárias para exibições indexadas. 

A avaliação da mesma expressão poderá produzir resultados diferentes no Mecanismo de Banco de Dados quando houver diferentes opções SET ativas durante a execução da consulta. Por exemplo, depois que a opção SET CONCAT_NULL_YIELDS_NULL for definida como ON, a expressão ‘abc‘ + NULL retornará o valor NULL. Entretanto, depois que CONCAT_NULL_YIEDS_NULL for definido como OFF, a mesma expressão produzirá ‘abc‘. 

Configurações adequadas.

 

 

opções SET	Valor Obrigatório	Valor do servidor padrão	Padrão Valor OLE DB e ODBC	Padrão Valor da DB-Library
ANSI_NULLS	ON	ON	ON	OFF
ANSI_PADDING	ON	ON	ON	OFF
ANSI_WARNINGS	ON	ON	ON	OFF
ARITHABORT	ON	ON	OFF	OFF
CONCAT_NULL_YIELDS_NULL	ON	ON	ON	OFF
NUMERIC_ROUNDABORT	OFF	OFF	OFF	OFF
QUOTED_IDENTIFIER	ON	ON	ON	OFF

 

Obs: Definir ANSI_WARNINGS como ON define implicitamente ARITHABORT como ON.

 

SET ANSI_NULLS ON; 

SET ANSI_PADDING ON; 

SET ANSI_WARNINGS ON; 

SET ARITHABORT ON; 

SET CONCAT_NULL_YIELDS_NULL ON; 

SET QUOTED_IDENTIFIER ON; 

SET NUMERIC_ROUNDABORT OFF; 

 

Nesse ponto cabe uma observação 

As views indexadas são exigentes em relação as configurações. Uma vez que uma view indexada seja criada, o SET ARITHABORT precisa estar ON sempre que um registro for inserido na mesma tabela em que a view está, do contrário o insert falha com o erro “Unable to create record. INSERT failed because the following SET options have incorrect settings: ‘ARITHABORT’.”

 

3. A definição de umaview indexada deve ser determinística

Para saber mais sobre determinismo  veja aqui : Funções determinísticas e não determinísticas

4. A instrução SELECT na definição da exibição não deve conter os seguintes elementos do Transact-SQL .

 

COUNT	Funções ROWSET (OPENDATASOURCE, OPENQUERY, OPENROWSET, AND OPENXML)	junções OUTER (LEFT, RIGHT ou FULL)
Tabela derivada (definida especificando uma instrução SELECT na cláusula FROM)	Auto junções	Especificando colunas usando SELECT * ou SELECT table_name.
DISTINCT	STDEV, STDEVP, VAR, VARP ou AVG	CTE (expressão de tabela comum)
float*, text, ntext, image, XMLou filestream	Subconsulta	Cláusula OVER, que inclui funções de classificação ou de janela de agregação
Predicados de texto completo (CONTAIN, FREETEXT).	Função SUM que referencia uma expressão que permite valor nulo	ORDER BY
Função de agregação CLR definida pelo usuário	INÍCIO	Operadores CUBE, ROLLUP ou GROUPING SETS
MIN, MAX	Operadores UNION, EXZip CodeT ou INTERSECT	TABLESAMPLE
Variáveis de tabela	OUTER APPLY ou CROSS APPLY	PIVOT, UNPIVOT
Conjuntos de colunas esparsas	Funções embutidas ou com valor de tabela de várias instruções	OFFSET
CHECKSUM_AGG

Se a view tiver CTE, na hora da criação do índice o erro e mostrado

/*

cannot create index on view “ViewName” because it references common table expression “Name CTE”. Views referencing common table expressions cannot be indexed. Consider not indexing the view, or removing the common table expression from the view definition. 

*/

 

Obs 1:

• A exibição indexada pode conter colunas float ; porém, essas colunas não podem ser incluídas na chave de índice clusterizado.
• Se GROUP BY estiver presente, a definição VIEW deverá conter COUNT_BIG() .

• E não deverá conter HAVING. Essas restrições GROUP BY são aplicáveis apenas à definição de exibição indexada.

 

Obs 2: Se a definição de exibição contiver uma cláusula GROUP BY, a chave de índice clusterizado exclusivo poderá referenciar somente as colunas especificadas na cláusula GROUP BY.

 

Segurança

Permissões

Requer a permissão CREATE VIEW no banco de dados e a permissão ALTER no esquema no qual a exibição está sendo criada

 

Uma view indexada, deve ter obrigatoriamente um índice UNIQUE CLUSTERED

 

Exemplo de uma View Simples

 

CREATE VIEW VwRelacaoClientesIndexada WITH SCHEMABINDING

 

AS

SELECT   [Cliente] = CONCAT(P.FirstName, ‘ -‘, P.LastName) ,

SOD.SalesOrderID ,

SOD.SalesOrderDetailID ,

SOH.SalesOrderNumber ,

SOH.OrderDate ,

[Produto] = P2.Name ,

SOD.UnitPrice ,

SOD.UnitPriceDiscount ,

SOH.SalesPersonID ,

ST.Name ,

ST.CountryRegionCode

FROM     Sales.SalesOrderDetail AS SOD

JOIN Sales.SalesOrderHeader AS SOH ON SOH.SalesOrderID = SOD.SalesOrderID

JOIN Sales.Customer AS C ON C.CustomerID = SOH.CustomerID

JOIN Person.Person AS P ON P.BusinessEntityID = C.PersonID

JOIN Production.Product AS P2 ON P2.ProductID = SOD.ProductID

JOIN Sales.SalesTerritory AS ST ON ST.TerritoryID = C.TerritoryID

 

Antes de criar o índice vamos fazer um select na view como  o plano de execução

Nos podemos fazer assim para ver as leituras e paginas ,

Vamos limpar o cache:

CHECKPOINT ;
GO
— Limpa buffers (comandos add-hoc)
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS ; — deleta o cache da query
GO
DBCC FREEPROCCACHE WITH NO_INFOMSGS; –deleta o plano execução ja feito
GO
DBCC FREESESSIONCACHE WITH NO_INFOMSGS
GO

SET STATISTICS IO ON

SELECT * FROM dbo.VwRelacaoClientesIndexada AS VRCI

SET STATISTICS IO OFF

Mas eu estou testando o sentryone Plan explorer que e FREE   ,baixado aqui  plan-explorer

veja o plano de execução

 

E aqui esta a quantidade de I/O da query

 
 Agora sim, podemos criar nossos índices  NONCLUSTERED INDEX

CREATE UNIQUE CLUSTERED INDEX [IdxViewIndexada] ON [dbo].[VwRelacaoClientesIndexada] ([SalesOrderID], [SalesOrderDetailID]) WITH (STATISTICS_NORECOMPUTE=ON) ON [PRIMARY] 

 

vamos analisar um select com a clausula where encima da coluna não chave que é a chave do índice, e como resultado esperado  um operador de index scan , para a leitura dos dados do índice

Agora vamos fazer um teste

SELECT

VRCI.Cliente ,

VRCI.SalesOrderID ,

VRCI.SalesOrderDetailID ,

VRCI.SalesOrderNumber ,

VRCI.OrderDate ,

VRCI.Produto ,

VRCI.UnitPrice ,

VRCI.UnitPriceDiscount ,

VRCI.SalesPersonID ,

VRCI.Name ,

VRCI.CountryRegionCode

 

FROM dbo.VwRelacaoClientesIndexada AS VRCI

WHERE VRCI.Name= ‘Germany‘

 

veja o plano de execução

veja o custo de I/0

 

Agora surge a pergunta?

Por que o índice não foi usado???

O certo seria  um Index Scan 

Essa resposta foi obter lendo o livro  70 -762  pag 93

he first benefit of using an indexed view is that when you use it Enterprise Edition of SQL Server, it uses the stored data in the index structure. For Standard Edition, it uses the code of the query unless you use a NOEXPAND table hint

Como a minha versão do SQL server e a Standard Edition (64-bit)

Vamos fazer assim

 

SELECT
VRCI.Cliente ,
VRCI.SalesOrderID ,
VRCI.SalesOrderDetailID ,
VRCI.SalesOrderNumber ,
VRCI.OrderDate ,
VRCI.Produto ,
VRCI.UnitPrice ,
VRCI.UnitPriceDiscount ,
VRCI.SalesPersonID ,
VRCI.Name ,
VRCI.CountryRegionCode

FROM dbo.VwRelacaoClientesIndexada AS VRCI WITH(NOEXPAND)
WHERE VRCI.Name= ‘Germany’

veja agora o operador de index scan entrando em ação

e se na sua clausula where for uma chave do índice temos o operador index seek

SELECT * FROM dbo.VwRelacaoClientesIndexada AS VRAPDP WITH (NOEXPAND)

WHERE    VRAPDP.SalesOrderID =43659

e o custo de IO

Nesse artigo demostramos de forma pratica como usar views indexadas ,até mais.

Referencias:

https://docs.microsoft.com/pt-br/sql/relational-databases/views/create-indexed-views

http://www.linhadecodigo.com.br/artigo/602/sql-server-views-indexadas.aspx#ixzz4qU9ZaJSI

http://www.linhadecodigo.com.br/artigo/602/sql-server-views-indexadas.aspx

 

 

 

Criando Sequências com ROW_NUMBER

olá pessoal esse post vem do blog
https://social.technet.microsoft.com/Forums/sqlserver/pt-BR/508a2593-f521-437b-98e7-8c8fa7c2acff/criar-sequncia?forum=transactsqlpt

temos a seguinte entrada

2017-07-19 16:27:58
2017-07-19 16:27:58
2017-07-19 16:27:58
2017-07-20 16:39:54
2017-07-20 16:39:54
2017-07-20 16:39:54
2017-07-20 16:39:54
2017-07-20 16:39:54

enquanto for o mesmo dia executa uma sequencia , e quando mudar a sequencia e reiniciada;

seja a saída

Data Sequencia
2017-07-19 16:27:58.8700000 1
2017-07-19 16:27:58.9070000 2
2017-07-19 16:27:58.9430000 3
2017-07-20 16:39:54.5500000 1
2017-07-20 16:39:54.5630000 2
2017-07-20 16:39:54.5800000 3
2017-07-20 16:39:54.5970000 4
2017-07-20 16:39:54.6170000 5

#Code
Usando Windows Functions fica muito fácil, como e por dia , precisamos desprezar as horas e minutos com CAST

 

DECLARE @tabela TABLE ( Data DATETIME );

INSERT INTO @tabela

( Data )

VALUES ( ‘2017-07-19 16:27:58’ ),

( ‘2017-07-19 16:27:58’ ),

( ‘2017-07-19 16:27:59’ ),

( ‘2017-07-20 16:39:54’ ),

( ‘2017-07-20 16:39:54’ ),

( ‘2017-07-20 16:39:54’ ),

( ‘2017-07-20 16:39:54’ ),

( ‘2017-07-20 16:39:54’ );

SELECT T.Data ,

Sequencia = ROW_NUMBER() OVER ( PARTITION BY CAST(T.Data AS DATE) ORDER BY CAST(T.Data AS DATE) )

FROM @tabela AS T;

 

 

Identificando as configurações SET ativas da sessão

Para você que precisa antes de criar uma VIEW indexada ou mesmo identificar as configurações SET da sessão aqui um script.

DECLARE @options INT

SELECT @options = @@OPTIONS

PRINT @options

IF ( (1 & @options) = 1 ) PRINT ‘DISABLE_DEF_CNST_CHK’

IF ( (2 & @options) = 2 ) PRINT ‘IMPLICIT_TRANSACTIONS’

IF ( (4 & @options) = 4 ) PRINT ‘CURSOR_CLOSE_ON_COMMIT’

IF ( (8 & @options) = 8 ) PRINT ‘ANSI_WARNINGS’

IF ( (16 & @options) = 16 ) PRINT ‘ANSI_PADDING’

IF ( (32 & @options) = 32 ) PRINT ‘ANSI_NULLS’

IF ( (64 & @options) = 64 ) PRINT ‘ARITHABORT’

IF ( (128 & @options) = 128 ) PRINT ‘ARITHIGNORE’

IF ( (256 & @options) = 256 ) PRINT ‘QUOTED_IDENTIFIER’

IF ( (512 & @options) = 512 ) PRINT ‘NOCOUNT’

IF ( (1024 & @options) = 1024 ) PRINT ‘ANSI_NULL_DFLT_ON’

IF ( (2048 & @options) = 2048 ) PRINT ‘ANSI_NULL_DFLT_OFF’

IF ( (4096 & @options) = 4096 ) PRINT ‘CONCAT_NULL_YIELDS_NULL’

IF ( (8192 & @options) = 8192 ) PRINT ‘NUMERIC_ROUNDABORT’

IF ( (16384 & @options) = 16384 ) PRINT ‘XACT_ABORT’

Conhecendo Views parte I

Esse post, e um resumo do livro  70-762 Developed Sql Databases,

Algumas características: A View pode ser definida como uma tabela virtual composta por linhas e colunas de dados vindos de tabelas relacionadas em uma query (um agrupamento de SELECT’s, por exemplo).

As linhas e colunas da View são geradas dinamicamente no momento em que é feita uma referência a ela.

Uma View é uma única instrução SELECT que é compilada em um objeto reutilizável.

Têm as mesmas características de segurança que as tabelas.

 

Quando usar

1) esconder dados para um propósito específico.

2) unificar lógicas complexas de selects

3) Criar querys de relátorios

 

Quando uma exibição é criada, as informações sobre a exibição são armazenadas nas seguintes views de sistema:

 sys.views,  sys.columns,  e sys.sql_expression_dependencies .

 

 O texto da instrução CREATE VIEW é armazenado na view  sys.sql_modules.

A indicação SELECT pode ser tão complexa quanto você deseja, e pode usar CTEs, definir

Operadores como UNION e EXCEPT, e quaisquer outras construções que você possa usar em uma única declaração.

Sintaxe:

CREATE VIEW SchemaName.ViewName  

[WITH OPTIONS]   AS SELECT statement  

[WITH CHECK OPTION]  

Existe três opções

[ SCHEMABINDING ] [ VIEW_METADATA ] [ ENCRYPTION ]

SCHEMABINDING:

Vincula a view ao esquema da tabela ou tabelas subjacentes. Quando SCHEMABINDING é especificado, a tabela base ou as tabelas não podem ser modificadas de forma a afetar a definição de exibição.

VIEW_METADATA:

Especifica que a instância do SQL Server  retornará às API DB-Library, ODBC e OLE DB   as informações de metadados sobre a exibição, em vez da tabela base ou tabelas

ENCRYPTION:

Criptografa a entrada em sys.syscomments que contém o texto do

Exibir declaração de criação.

Vamos usar o banco

Que pode der baixado aqui AdventureWorks2012:

Vamos a um caso de estudo:

Nosso gerente da empresa XPTO nos pediu um relatório de vendas

Usando o Banco AdventureWorks2012 vamos criar a seguinte view

CREATE VIEW vwRelacaoVendas  

AS  

    SELECT  SO.SalesOrderID ,  

            [Data Venda] = CONVERT(VARCHAR(10), SOH.OrderDate, 103) ,  

            [Cliente] = CONCAT(P.FirstName, ‘ ‘, P.LastName) ,  

            [Quantidade] = SO.OrderQty ,  

            [Produto] = Pro.Name ,  

            [PrecoUnitario] = SO.UnitPrice ,  

            [Desconto] = SO.UnitPriceDiscount ,  

            [TotalItem] = ( SO.OrderQty * SO.UnitPrice )  

            – ( SO.UnitPriceDiscount * SO.OrderQty )  

    FROM    Sales.SalesOrderDetail AS SO  

            JOIN Sales.SalesOrderHeader AS SOH ON SOH.SalesOrderID = SO.SalesOrderID  

            JOIN Sales.Customer AS C ON C.CustomerID = SOH.CustomerID  

            JOIN Person.Person AS P ON C.PersonID = P.BusinessEntityID  

            JOIN Production.Product AS Pro ON Pro.ProductID = SO.ProductID;   

Agora é só fazer um select na view

SELECT * FROM vwRelacaoVendas  

 — ================================================================== 

–Observação: rode os seguintes scripts para ver o que foi criado 

— ================================================================== 

SELECT S.id,S.text,S.ctext FROM  sys.syscomments AS S  

JOIN sys.views AS V ON S.id = V.object_id 

WHERE V.name =‘vwRelacaoVendas’ 

 

SELECT V.name , V.object_id , V.schema_id ,

V.type_desc , V.create_date , V.modify_date , V.with_check_option

FROM  sys.views AS V 

WHERE V.name =‘vwRelacaoVendas’ 

SELECT V.name,C.object_id , 

              C.name , 

              C.column_id , 

              T.name, 

              C.max_length , 

              C.collation_name , 

              C.is_nullable , 

              C.is_identity , 

              C.is_computed 

               FROM sys.columns AS C 

JOIN sys.views AS V ON V.object_id = C.object_id 

JOIN sys.types AS T ON T.user_type_id = C.user_type_id 

WHERE V.name =‘vwRelacaoVendas’ 

SELECT  

       OBJECT_NAME(SED.referencing_id ), 

        [Tabela Dependente] = CONCAT(SED.referenced_schema_name,‘.’,SED.referenced_entity_name) , 

       V.create_date , 

       V.modify_date , 

       V.is_published , 

       V.is_schema_published , 

       V.is_replicated , 

       V.with_check_option  

        FROM sys.sql_expression_dependencies AS SED 

JOIN sys.views AS V ON V.object_id =SED.referencing_id 

WHERE v.name =‘vwRelacaoVendas’ 

SELECT V.name,  

               M.definition , 

               M.uses_ansi_nulls , 

               M.uses_quoted_identifier , 

               M.is_recompiled 

                FROM sys.sql_modules M  

JOIN sys.views AS V ON V.object_id = M.object_id  

WHERE v.name =‘vwRelacaoVendas’ 

Agora vamos adicionar a opção ENCRYPTION

 

ALTER VIEW dbo.vwRelacaoVendas  WITH ENCRYPTION 

AS 

    SELECT  SO.SalesOrderID , 

            [Data Venda] = CONVERT(VARCHAR(10), SOH.OrderDate, 103) , 

            [Cliente] = CONCAT(P.FirstName, ‘ ‘, P.LastName) , 

            [Quantidade] = SO.OrderQty , 

            [Produto] = Pro.Name , 

            [PrecoUnitario] = SO.UnitPrice , 

            [Desconto] = SO.UnitPriceDiscount , 

            [TotalItem] = ( SO.OrderQty * SO.UnitPrice ) – ( SO.UnitPriceDiscount * SO.OrderQty ) 

    FROM    Sales.SalesOrderDetail AS SO 

            JOIN Sales.SalesOrderHeader AS SOH ON SOH.SalesOrderID = SO.SalesOrderID 

            JOIN Sales.Customer AS C ON C.CustomerID = SOH.CustomerID 

            JOIN Person.Person AS P ON C.PersonID = P.BusinessEntityID 

            JOIN Production.Product AS Pro ON Pro.ProductID = SO.ProductID

GO

Veja o que aconteceu com o texto da view

SELECT S.* FROM  sys.syscomments AS S  

JOIN sys.views AS V ON S.id = V.object_id 

WHERE V.name =‘vwRelacaoVendas’ 

Tanto o campo Ctext e text ficaram com valores null

 

INSERT ,UPDATE E DELETE EM VIEWS 

 

Você pode modificar os dados de uma tabela base subjacente através de uma visualização, desde que as seguintes condições sejam verdadeiras:

Quaisquer modificações, incluindo instruções UPDATE, INSERT e DELETE, devem referenciar colunas de uma única tabela base.

As colunas que estão sendo modificadas na view devem fazer referência direta aos dados subjacentes nas colunas da tabela. As colunas não podem ser derivadas de nenhuma outra maneira, como por meio das seguintes funções:

AVG, COUNT, SUM, MIN, MAX, GROUPING, STDEV, STDEVP, VAR e VARP.

A coluna não pode ser calculada a partir de uma expressão que usa outras colunas.

As colunas que são formadas usando os operadores UNION, UNION ALL, CROSS JOIN, EXCEPT e INTERSECT também não são atualizáveis. 

Exemplo :

SET QUOTED_IDENTIFIER ON 

SET ANSI_NULLS ON 

GO 

IF (EXISTS (SELECT * FROM sys.views AS V WHERE V.name =‘vwRelacaoVendas’)) 

BEGIN 

        DROP VIEW dbo.vwRelacaoVendas 

          END 

     

CREATE VIEW dbo.vwRelacaoVendas  

AS 

    SELECT  SO.SalesOrderID , 

            SO.SalesOrderDetailID , 

            SO.ProductID , 

            SO.OrderQty , 

            SO.UnitPrice , 

            SO.UnitPriceDiscount  

    FROM    Sales.SalesOrderDetail AS SO 

GO 

  

SELECT * FROM dbo.vwRelacaoVendas AS VRV 

    

UPDATE VRV SET VRV.OrderQty = 10  FROM dbo.vwRelacaoVendas AS VRV 

WHERE SalesOrderDetailID =1 

  

UPDATE VRV SET VRV.OrderQty = 9  FROM dbo.vwRelacaoVendas AS VRV 

WHERE SalesOrderID =43659 

 

ALTER VIEW dbo.vwRelacaoVendas 

AS 

    SELECT  SO.SalesOrderID , 

            SO.SalesOrderDetailID , 

            SO.ProductID , 

            SO.OrderQty , 

            SO.UnitPrice , 

            SO.UnitPriceDiscount 

    FROM    Sales.SalesOrderDetail AS SO 

    EXCEPT 

    ( SELECT    SO.SalesOrderID , 

                SO.SalesOrderDetailID , 

                SO.ProductID , 

                SO.OrderQty , 

                SO.UnitPrice , 

                SO.UnitPriceDiscount 

      FROM      Sales.SalesOrderDetail AS SO 

      WHERE     SO.SalesOrderID = 43659 

    ); 

 

UPDATE VRV SET VRV.UnitPrice =100 FROM dbo.vwRelacaoVendas AS VRV 

WHERE VRV.SalesOrderID =‘43660’ 

/*

Msg 4403, Level 16, State 1, Line 1

Cannot update the view or function ‘VRV’ because it contains aggregates, or a DISTINCT or GROUP BY clause, or PIVOT or UNPIVOT operator.

*/

— ==================================================================

–segundo exemplo

— ==================================================================

ALTER VIEW dbo.vwRelacaoVendas 

AS 

    SELECT  SO.SalesOrderID , 

            SO.SalesOrderDetailID , 

            SO.ProductID , 

            SO.OrderQty , 

            SO.UnitPrice , 

            SO.UnitPriceDiscount 

    FROM    Sales.SalesOrderDetail AS SO 

    WHERE    SO.SalesOrderID =43661 

    UNION 

    ( SELECT    SO.SalesOrderID , 

                SO.SalesOrderDetailID , 

                SO.ProductID , 

                SO.OrderQty , 

                SO.UnitPrice , 

                SO.UnitPriceDiscount 

      FROM      Sales.SalesOrderDetail AS SO 

      WHERE     SO.SalesOrderID = 43659 

    ); 

   

UPDATE dbo.vwRelacaoVendas SET UnitPrice =100 

WHERE SalesOrderID =‘43659’ 

 /*

Msg 4403, Level 16, State 1, Line 1

Cannot update the view or function ‘dbo.vwRelacaoVendas’ because it contains aggregates, or a DISTINCT or GROUP BY clause, or PIVOT or UNPIVOT operator.

 */

WITH CHECK OPTION 

Quando queremos limitar as atualizações de dados em uma View, utilizamos a propriedade

WITH CHECK OPTION.

Ao adicionarmos essa propriedade na definição de uma view, estamos dizendo que todas as operações de Insert e Update nesta View devem obedecer os critérios da consulta.

Exemplo

CREATE TABLE SaldoInicial 

( 

Numero INT IDENTITY(1,1)    PRIMARY KEY, 

NomeConta VARCHAR(20), 

Saldo DECIMAL(18,2)  

  ) 

  CREATE VIEW VwSaldoInicial AS  

SELECT * FROM dbo.SaldoInicial AS SI 

  INSERT INTO dbo.VwSaldoInicial 

        ( NomeConta, Saldo ) 

VALUES  ( ‘Conta A’,  100    ) 

 

Agora vamos alterar a view colocando WITH CHECK OPTION;  

ALTER VIEW dbo.VwSaldoInicial 

AS 

    SELECT  * 

    FROM    dbo.SaldoInicial AS SI 

    WHERE   SI.Saldo >= 101 

WITH CHECK OPTION; 

 

 

 INSERT INTO dbo.VwSaldoInicial 

        ( NomeConta, Saldo ) 

VALUES  ( ‘Conta B’,  100 ) 

 

  /*

Msg 550, Level 16, State 1, Line 1

The attempted insert or update failed because the target view either specifies WITH CHECK OPTION or spans a view that specifies WITH CHECK OPTION and one or more rows resulting from the operation did not qualify under the CHECK OPTION constraint.

The statement has been terminated.

*/

Por enquanto e isso ,

Saiba Mais: Documentação

Wesley Neves – Brasilia-DF

MTA-SQL Server
MTA- Web Development
Analista Desenvolvedor.NET
Pós-Graduando em Banco de Dados