MCTS: SQL Server 2008, Business Intelligence Development and Maintenance

Mais uma no dia 27/01...

Pretendo ainda para esse semestre:
MCTS SQL Server 2008 Database Development
MCITP Database Developer 2008
MCITP Business Intelligence Developer 2008
MCP Windows 2003 Server


As provas de Developer para mim serão muito mais faceis, assim como foram as de Administração, mas a MCITP de BI preciso estudar bem mais.

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

Data por extenso alterando o language

Esse é um jeito bem facil e rapido para ter uma data por extenso somente trocanto o Language do banco:

*** No exemplo eu uso por padrão o us_english no banco de dados. Verifique antes qual o default language do seu banco para alterar a última linha do código:


Declare @Data DateTime

set language brazilian

Declare @Retorno VarChar(200)

If @Data is Null
Begin
Set @Data = getdate()
End

Select DATENAME(weekday, @Data) + ', ' + convert(varchar(02),DatePart(day,@Data)) + ' De ' + DateName(Month,@Data) + ' de ' + convert(varchar(04),DatePart(YEar,@Data)) + '.'

SET LANGUAGE us_english