MCTS/MCITP-DBA SQLServer 2008

Ufa.... mais uma jornada de muito estudo deu resultados. Ainda faltam os outros 2
MCITPs de SQLServer 2008.

Uma alternativa ao @@Identity

Hoje o site SQL Server Central publicou uma dica simples, mas muito legal do Steve McRoberts: Retrieving Identities on set-based inserts.

Esta matéria mostra uma alternativa ao @@Identity. Claro que deve ser estudado e ponderado quando usar um e quando usar outro. Mas em resumo o código é este:

DECLARE @parent TABLE (myID int IDENTITY, myCol int)
DECLARE @newID TABLE (new_ID int)

-- Generate 100 rows of test data
;WITH mycte
AS ( SELECT 100 AS seedValue
UNION ALL
SELECT seedValue + 1
FROM mycte
WHERE seedValue + 1 < 200 )
INSERT INTO @parent (myCol)
OUTPUT INSERTED.myId INTO @newID
SELECT * FROM mycte

SELECT * FROM @newID

Alterar o Collation do SERVIDOR / Instancia SQLServer

Para alterar o Collation Default de um Servidor/Instancia SQLServer é um pouco mais complicado e não confundir com trocar o Collation Default de um Banco de Dados SQLServer.

1) Pare o serviço MSSQLServer da Instancia do SQLServer que você quer trocar o Collation.

2) Na pasta BINN da instalação do seu servidor SQLServer (exemplo: C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn) inicie o serviço SQLServer da seguinte forma:

sqlservr -m -sInstancia_SQLSERVER -T4022 -T3659 -q"COLLATE_ESCOLHIDO"

3) Pressione Ctrl+C para parar o serviço atual

4) Inicie novamente o MSSQLServer da Instancia do SQLServer que você parou para trocar o Collation.

Collate (Collation) no SQLServer

Aqui vou passar alguns exemplos e explicações bem básicas e diretas do que é um Collate (Collation) no SQLServer.

O Collate é definição da cadeia de caracteres utilizados como padrão em um banco, colunas de uma tabela ou alguma consulta.

Vários alfabetos de outros paises utilizam caracteres bem diferentes do nosso, e isso é definido no Collate, por exemplo, o alfabeto utilizado no Ingles não usa acentuação, já o Latino (utilizado aqui no Brasil) sim, isso faz total diferença em uma pesquisa ou no armazenamento da informação no banco.

/*

Para saber quais são os Collates disponíveis no SQLServer do padrão Latin1_General:

*/

Select *
from ::fn_helpcollations()
Where Name like 'Latin1_General%'



/*
Notem que no final do nome do Collate tem algumas siglas, essas siglas fazem toda diferença na escolha, pois influenciam diretamente em como as querys e algumas funcionalidades do Banco tratam os dados.

CI = Case Insensitive
AI = Accents Insensitive
CS = Case Sensitive
AS = Accents Sensitive
WS = Width Sensitive (*1)
KS = Kanatype Sensitive (*2)

(*1) Kana-sensitive - Especifica que o SQL Server deve distinguir entre os dois tipos de caracteres kana japoneses: hiragana e katakana.
Se não for selecionada o SQL Server considera caracteres hiragana e katakana iguais.

(*2) Width-sensitive - Especifica que o SQL Server deve distinguir entre um caractere de byte único (meia largura) e o mesmo caractere quando representado como um caractere de byte duplo (largura total).
Se não for selecionada o SQL Server considera o byte único e byte duplo igualmente.

Ambos os casos acimas são praticamente não utilizados.

*/


/*

Para saber qual o Collation configurado no seu servidor

Alterar o Collation do Servidor é um pouco mais complicado. Como fazer isso está neste post: Alterar o Collation do SERVIDOR SQLServer.

*/

Select ServerProperty('CollationId') CollationId,
SERVERPROPERTY('Collation') Collation


/*

Para saber qual o Collation configurado no seu Banco de Dados

*/

Select DatabasePropertyEx('Nome_Banco','Collation')
--Ou na coluna Status da
sp_helpdb

--Para Alterar o Collate do Banco:
Alter database Nome_Banco collate Latin1_General_CI_AS


/*

Para saber qual o Collation configurado na Tabela

*/

Select name, collation
from syscolumns
Where [id]=object_id('Nome_Tabela')

--Se a coluna Collation estiver com Null significa que está assumindo o Collate do Banco



Agora vamos ao exemplo bem simples de busca para mostrar como isso funciona:
Estou partindo do principio que o Collation default do banco é o Latin1_General_CI_AI.
Em uma tabela temporária vou inserir o nome 'Conceição' exatamente por causa do Ç e do à para fazer as buscas utilizando acentuação e maiuscula/minuscula.

--Criar uma tabela temporária para fazer o exemplo com as buscas:

Create Table #tbTesteCollate (id Int, Nome VarChar(50))
GO
Insert Into #tbTesteCollate Values (1, 'Conceição')
Insert Into #tbTesteCollate Values (2, 'CONCEIÇÃO')
Insert Into #tbTesteCollate Values (3, 'Conceicao')
Insert Into #tbTesteCollate Values (4, 'CONCEICAO')


--Agora vamos executar uma busca utilizando os 4 Collates disponíveis com o critério like '%cao%' e ver o que retorna com cada um dos Collates

Select *
from #tbTesteCollate
Where Nome collate Latin1_General_CS_AS like '%cao%' --Case Sensitive / Accents Sensitive
go
Select *
from #tbTesteCollate
Where Nome collate Latin1_General_CI_AS like '%cao%' --Case Insensitive / Accents Sensitive
GO
Select *
from #tbTesteCollate
Where Nome collate Latin1_General_CI_AI like '%cao%' --Case Insensitive / Accents Insensitive
go
Select *
from #tbTesteCollate
Where Nome collate Latin1_General_CS_AI like '%cao%' --Case Sensitive / Accents Insensitive




/*

Mais Exemplos de busca

*/


Select *
from #tbTesteCollate
Where Nome collate Latin1_General_CS_AS like '%ÇÃO%'
go
Select *
from #tbTesteCollate
Where Nome collate Latin1_General_CI_AS like '%ÇÃO%'
GO
Select *
from #tbTesteCollate
Where Nome collate Latin1_General_CI_AI like '%ÇÃO%'
go
Select *
from #tbTesteCollate
Where Nome collate Latin1_General_CS_AI like '%ÇÃO%'

Somar o valor de uma coluna com o registro anterior (subtotal).

Aqui vou passar mais um exemplo de como utilizar CTEs - Common Table Expressions (Disponivel no SQLServer 2005 e 2008).

Nesse exemplo vou passar a dica de como somar o valor de uma coluna do registro atual com o registro anterior, como um subtotal.

Primeiro vou criar a tabela para os teste:

Create Table #tbValoresPagos (id int, dtPagamento DateTime, tpPagamento VarChar(1), vlPagamento Decimal(10,2))

E Inserir alguns valores de exemplos:

insert into #tbValoresPagos values(1, '2010-01-10 1:00', 'A', 125.30)
insert into #tbValoresPagos values(2, '2010-01-21 1:00', 'B', 15.00)
insert into #tbValoresPagos values(3, '2010-01-22 1:00', 'B', 1964.10)
insert into #tbValoresPagos values(4, '2010-03-20 2:00', 'C', 213.47)
insert into #tbValoresPagos values(5, '2010-03-20 2:30', 'A', 46.63)
insert into #tbValoresPagos values(6, '2010-04-01 3:00', 'B', 4899.21)
insert into #tbValoresPagos values(7, '2010-04-10 3:30', 'C', 3876.22)
insert into #tbValoresPagos values(8, '2010-05-01 3:40', 'C', 25.11)
insert into #tbValoresPagos values(9, '2010-05-05 3:40', 'B', 647.14)
insert into #tbValoresPagos values(10,'2010-05-05 4:40', 'B', 659.33)
insert into #tbValoresPagos values(12,'2010-05-12 5:40', 'C', 12.11)
insert into #tbValoresPagos values(13,'2010-05-20 5:42', 'A', 50.00)
insert into #tbValoresPagos values(14,'2010-06-20 5:45', 'A', 60.00)

/*

Por garantia, é melhor criar uma coluna Row_Number Particionado e Ordenado pelos critérios necessários antes de fazer a soma. Esta coluna Row_Number vai ser responsável pela identificação dos registros para sabermos qual é o atual e buscarmos o anterior.

A CTE cte_Recursivo seleciona o primeiro registro e juntamente com o Union All é utilizado a recursividade para que a segunda parte do Select traga sempre o registro atual + 1 (Próximo registro) respeitando os critérios utilizados no Inner Join.

*/

/*

Exemplo 1:
O exemplo abaixo é um mais simples, ele simplesmente soma a coluna vlPagamento registro a registro ordenado simplesmente pelo critério indicado no select da cte_ok, no caso o dtPagamento.

*/

;with cte_ok
as( select row_number() over (order by dtPagamento) as idRowNumber, dtPagamento, tpPagamento, vlPagamento
from #tbValoresPagos),
cte_Recursivo as
( Select idRowNumber, dtPagamento, tpPagamento, vlPagamento, vlPagamento as vlSoma
From cte_ok
Where idRowNumber = 1
Union All
Select A.idRowNumber, A.dtPagamento, A.tpPagamento, A.vlPagamento, Convert(Decimal(10,2), A.vlPagamento + B.vlSoma)
From cte_ok A
Inner Join cte_Recursivo B On A.idRowNumber = B.idRowNumber +1
)
Select * from cte_Recursivo



/*

Exemplo 2:
Agora o exemplo inclui um particionamento no Row_Number, o tpPagamento. Então o Row_Number é agrupado e reiniciado a cada tipo de tpPagamento diferente e ordenado por id.
Isso faz com que a soma da coluna vlPagamento seja reiniciado quando o tpPagamento mudar.

*/

;with cte_ok
as( select row_number() over (Partition by tpPagamento order by id) as idRowNumber, dtPagamento, tpPagamento, vlPagamento
from #tbValoresPagos),
cte_Recursivo as
( Select idRowNumber, dtPagamento, tpPagamento, vlPagamento, vlPagamento as vlSoma
From cte_ok
Where idRowNumber = 1
Union All
Select A.idRowNumber, A.dtPagamento, A.tpPagamento, A.vlPagamento, Convert(Decimal(10,2), A.vlPagamento + B.vlSoma)
From cte_ok A
Inner Join cte_Recursivo B On A.idRowNumber = B.idRowNumber +1
And A.tpPagamento = B.tpPagamento
)
Select *
from cte_Recursivo
Order by tpPagamento, idRowNumber




/*

Exemplo 3:
Um exemplo um pouco mais complexo inclui um particionamento de Ano/Mes no Row_Number. Então o Row_Number é agrupado e reiniciado a cada Mes/Ano e ordenado por id.

*/


;with cte_ok
as( select row_number() over (Partition by DatePart(year, dtPagamento), DatePart(Month, dtPagamento) order by id) as idRowNumber, Cast(DatePart(year, dtPagamento) as Varchar) + '/' + Cast(DatePart(Month, dtPagamento) as VarChar) Exercicio, dtPagamento, tpPagamento, vlPagamento
from #tbValoresPagos),
cte_Recursivo as
( Select idRowNumber, Exercicio, dtPagamento, tpPagamento, vlPagamento, vlPagamento as vlSoma
From cte_ok
Where idRowNumber = 1
Union All
Select A.idRowNumber, A.Exercicio, A.dtPagamento, A.tpPagamento, A.vlPagamento, Convert(Decimal(10,2), A.vlPagamento + B.vlSoma)
From cte_ok A
Inner Join cte_Recursivo B On A.idRowNumber = B.idRowNumber +1
And A.Exercicio = B.Exercicio
)
Select *
from cte_Recursivo
Order by Exercicio, idRowNumber