Olá, muito boa tarde….
Este é mais um post da nova Sessão É Pá e Bola com SQL Server.
Aqui o papo vai ser simples e direto, sem formalidade, com dicas rápidas para se fazer algo relacionado com Microsoft SQL Server de forma geral.
Você pode estar se perguntando o porque deste nome, eu já te respondo.
O porquê é que todas as dicas a serem compartilhadas podem estar envolvidas com esportes praticados com bola, ou simplesmente por sua facilidade na construção e execução.
Buenos dias, bom dia, comunidade….
E ai como passaram os últimos meses?
Pergunto isso, devido ao post anterior desta sessão ter sido publicado no mês de maio, posso imaginar que neste intervalo de tempo nossas vidas apresentaram inúmeras situações, espero que todas possam ter sido dentro do possível boas, assim como a minha, e se caso venha a existir algo mais espinhoso, que você possa ter superado.
Seguindo em frente, que alegria poder te encontrar em mais um post da sessão Short Scripts, uma das sessões mais recentes do meu blog que esta alçando a marca de 34 posts publicados trimestralmente.
Mantendo a tradição estou retornando com mais um conjunto de “pequenos” scripts catalogados e armazenados em minha biblioteca pessoal de códigos relacionados ao Microsoft SQL Server e sua fantástica linguagem de desenvolvimento Transact-SQL.
Como promessa é dívida e deve ser cumprida “ou melhor” compartilhada, estou compartilhando a minha feita a alguns meses no final do último post desta sessão, publicando mais um conjunto de scripts adicionados atualmente na minha biblioteca particular de códigos e exemplos.
Normalmente compartilho os principais scripts armazenados nos últimos meses, no post de hoje vou fazer um pouco diferente, recentemente tive a necessidade de realizar alguns atividades relacionadas as Split de dados e Collation, sendo assim, decide então compartilhar alguns códigos que estejam envolvidos com estes assuntos e que também se vinculem com outros, dentre os quais destaco:
Chegou a hora, mãos nos teclados, a seguir apresento os códigos e exemplos selecionados para o Short Script – Setembro 2018 . Vale ressaltar que todos os scripts publicados nesta sessão foram devidamente testados, mas isso não significa que você pode fazer uso dos mesmo em seu ambiente de produção, vale sim todo cuidado possível para evitar maiores problemas.
Fique a vontade para compartilhar, comentar e melhorar cada um destes códigos.
— Short Script 1 – Identificando Page Split – Método 1 —
SELECT cntr_value
FROM sys.sysperfinfo
WHERE counter_name =’Page Splits/sec’ AND
OBJECT_NAME LIKE’%Access methods%’
Go
— Short Script 2 – Identificando Page Split – Método 2 —
SELECT object_name,
counter_name,
instance_name,
cntr_value, cntr_type
FROM sys.dm_os_performance_counters
WHERE counter_name =’Page Splits/sec’ AND
OBJECT_NAME LIKE’%Access methods%’
Go
— Short Script 3 – Criando um User Defined Function para separação de Strings por vírgula – Método 1 —
CREATE FUNCTION dbo.UFN_SEPARATES_COLUMNS(@TEXT1 varchar(8000), @COLUMN tinyint, @SEPARATOR char(1))
RETURNS varchar(8000)
AS
BEGIN
DECLARE @POS_START int = 1
DECLARE @POS_END int = CHARINDEX(@SEPARATOR, @TEXT, @POS_START)
WHILE (@COLUMN >1 AND @POS_END> 0)
BEGIN
SET @POS_START = @POS_END + 1
SET @POS_END = CHARINDEX(@SEPARATOR, @TEXT, @POS_START)
SET @COLUMN = @COLUMN – 1
END
IF @COLUMN > 1 SET @POS_START = LEN(@TEXT) + 1
IF @POS_END = 0 SET @POS_END = LEN(@TEXT) + 1
RETURN SUBSTRING (@TEXT, @POS_START, @POS_END – @POS_START)
END
Go
SELECT
dbo.UFN_SEPARATES_COLUMNS(@ISBN, 1, ‘-‘) AS PREFIX,
dbo.UFN_SEPARATES_COLUMNS(@ISBN, 2, ‘-‘) AS REGISTRATION_GROUP,
dbo.UFN_SEPARATES_COLUMNS(@ISBN, 3, ‘-‘) AS REGISTRANT,
dbo.UFN_SEPARATES_COLUMNS(@ISBN, 4, ‘-‘) AS PUBLICATION,
dbo.UFN_SEPARATES_COLUMNS(@ISBN, 5, ‘-‘) AS [CHECK]
Go
— Short Script 4 – Criando um User Defined Function para separação de Strings por vírgula – Método 2 —
Create FUNCTION [dbo].[fnSplitString]
(
@string NVARCHAR(MAX),
@delimiter CHAR(1)
)
RETURNS @output TABLE(splitdata NVARCHAR(MAX)
)
BEGIN
DECLARE @start INT, @end INT
SELECT @start = 1, @end = CHARINDEX(@delimiter, @string)
WHILE @start < LEN(@string) + 1 BEGIN
IF @end = 0
SET @end = LEN(@string) + 1
INSERT INTO @output (splitdata)
VALUES(SUBSTRING(@string, @start, @end – @start))
SET @start = @end + 1
SET @end = CHARINDEX(@delimiter, @string, @start)
END
RETURN
END
Select * from dbo.fnSplitString(‘Querying SQL Server’,”)
Go
— Short Script 5 – Informando o Collate de uma coluna na cláusula Where —
Select campo
From tabela
Where campo collate Latin_General_CI_AS like ‘%á%’
Go
— Short Script 6 – Informando o Collate na declaração de uma coluna e na cláusula Where —
Select Campo collate Latin_General_CI_AS
From tabela
Where Campo collate Latin_General_CI_AS like ‘%á%’
Go
— Short Script 7 – Alterando o Collate de um Banco de Dados —
Alter Database MosaicoClient
Collate SQL_Latin1_General_CP1_CI_AI
Go
— Short Script 8 – Alterando o Collate de uma Coluna —
Alter Table Users
Alter Column [Password] Varchar(12) Collate SQL_Latin1_General_CP1_CS_AS
Go
— Short Script 9 – Identificando o Server Collation – Método 1 —
Select SERVERPROPERTY (‘collation’)
Go
— Short Script 10 – Identificando o Server Collation – Método 2 —
Exec sp_helpsort
Go
— Short Script 11 – Identificando o Database Collation —
Select DatabasePropertyEx(‘MRP’,’collation’)
Go
— Short Script 12 – Identificando o Column Collation – Método 1 —
Exec sp_help ‘Produtos’
Go
— Short Script 13 – Identificando o Column Collation – Método 2 —
Select * from Sys.columns
Where Name = ‘Produtos’
Go
— Short Script 14 – Identificando o Column Collation – Método 3 —
Select * from Information_schema.columns
Where Table_Name = ‘Produtos’
Go
Muito bem, missão mais que cumprida! Uma nova relação de short scripts acaba de ser compartilhada, mesmo sendo denominados short entre aspas “pequenos”, posso garantir que todos estes exemplos são de grande importância, apresentam um valor e conhecimento do mais alto nível.
Chegamos ao final de mais um Short Scripts, espero que este material possa lhe ajudar, ilustrando o uso de alguns recursos e funcionalidades do Microsoft SQL Server.
Acredito que você tenha observado que estes códigos são conhecidos em meu blog, todos estão relacionados aos posts dedicados ao Microsoft SQL Server publicados no decorrer dos últimos anos.
Boa parte deste material é fruto de um trabalho dedicado exclusivamente a colaboração com a comunidade, visando sempre encontrar algo que possa ser a solução de um determinado problema, bem como, a demonstração de como se pode fazer uso de um determinado recurso.
Caso você queira acessar os últimos posts desta sessão, não perca tempo acesse os links listados abaixo:
https://pedrogalvaojunior.wordpress.com/2018/05/10/short-scripts-maio-2018/
https://pedrogalvaojunior.wordpress.com/2018/02/19/short-scripts-fevereiro-2018-transaction-log/
https://pedrogalvaojunior.wordpress.com/2017/12/09/short-scripts-dezembro-2017/
https://pedrogalvaojunior.wordpress.com/2017/09/16/short-scripts-setembro-2017/
Agradecimento
Obrigado mais uma vez por sua visita, fico honrado com sua ilustre presença ao meu blog, desejo e espero que você possa ter encontrado algo que lhe ajudou.
Volte sempre, nos encontraremos mais uma vez na sessão Short Scripts no post do mês de novembro de 2018.
Abraços…
Muito boa tarde!!! Olá galera….
Após mais um sábado de aulas na Fatec São Roque, chegou a hora de se dedicar a comunidade de tecnologia, mais especificamente aos amantes de bancos de dados.
Como promessa é dívida e deve ser cumprida “ou melhor” paga, estou pagando a minha feita a exatos três meses no final do último post da sessão Short Scripts, hoje publicando mais um conjunto de scripts adicionados a minha biblioteca particular de códigos e exemplos nos últimos meses.
Como de costume selecionei os principais scripts armazenados recentemente na minha biblioteca de códigos, que apresentam os seguintes assuntos:
Chegou a hora, mãos nos teclados, a seguir apresento os códigos e exemplos selecionados para o Short Script – Setembro 2017. Vale ressaltar que todos os scripts publicados nesta sessão foram devidamente testados, mas isso não significa que você pode fazer uso dos mesmo em seu ambiente de produção, vale sim todo cuidado possível para evitar maiores problemas.
Fique a vontade para compartilhar, comentar e melhorar cada um destes códigos.
— Short Script 1 – Calculando o Ano e Mês posterior através do ano e mês —
Declare @AnoMes int = 201712
Declare @AnoMesPosteriorDate date = DATEADD(MONTH, @AnoMes % 100, DATEADD(YEAR, @AnoMes / 100 – 1900, 0))
Declare @AnoMesPosterior int = YEAR(@AnoMesPosteriorDate) * 100 + Month(@AnoMesPosteriorDate)
Select @AnoMes, @AnoMesPosterior
Go
— Short Script 2 – Comando Select especificando o nome da coluna e o valor através do sinal de igual —
Select ‘1/0’ = ‘Hello’, ‘2…’ = ‘teste’
Go
— Short Script 3 – Formatando a apresentação de um Plano de Contas —
DECLARE @tableDados TABLE ( Codigo VARCHAR(100) )
INSERT INTO @tableDados
SELECT ‘1.0.0.0.0.00.00’
UNION ALL
SELECT ‘1.1.0.0.0.00.00’
UNION ALL
SELECT ‘1.1.1.1.2.00.00’
UNION ALL
SELECT ‘2.3.7.1.1.01.00’
UNION ALL
SELECT ‘2.3.2.0.5.00.00’
SELECT Codigo FROM @tableDados
SELECT TD.Codigo, LEFT(TD.CODIGO, LEN(TD.CODIGO) – PATINDEX(‘%[1-9]%’, REVERSE(TD.CODIGO)) + 1)
FROM @tableDados AS TD
Go
— Short Script 4 – Formatando a apresentação de dados através das funções Parse() e Format() —
Declare @Valor Varchar(10)
Set @Valor=’1,540.20′
Select @Valor As Antes, PARSE(@Valor As money Using ‘en-US’) As Depois
Select FORMAT(Cast(@Valor As Money),’C’,’pt-BR’)
Go
— Short Script 5 – Analisando as diferentes de comportamente entre os operadores Cross Apply e Outer Apply —
Declare @Tabela1 Table
(Codigo Int,
Valor Int)
Declare @Tabela2 Table
(Codigo Int,
Valor Int)
Insert Into @Tabela1 Values (1,1),(2,2),(Null, Null)
Insert Into @Tabela2 Values (1,1),(2,2),(3,3),(4,4),(5,5), (Null, Null)
— Utilizando operador Outer Apply —
Select T.Codigo,
T.Valor
From @Tabela1 T Outer Apply (Select Codigo From @Tabela2
Where Codigo = T.Codigo) As T2
— Utilizando operador Cross Apply —
Select T.Codigo,
T.Valor
From @Tabela1 T Cross Apply (Select Codigo From @Tabela2
Where Codigo = T.Codigo) As T2
— Short Script 6 – Criando a sequência Fibonacci de valores —
— Criando a Tabela —
Create Table NumerosFibonacci
(Codigo Int Primary Key Identity(0,1),
Numeros BigInt Not Null)
Go
— Declarando as Variáveis de Controle —
Declare @Contador TinyInt = 0, @SequenciaFinobacciConcatenada Varchar(Max)
While @Contador <50
Begin
If @Contador = 0
Begin
Insert Into NumerosFibonacci (Numeros) Values (0)
Set @SequenciaFinobacciConcatenada = ‘0’
End
Else
Begin
Insert Into NumerosFibonacci (Numeros)
Select IsNull(Sum(Numeros)+1,1) from NumerosFibonacci
Where Codigo < @Contador – 1
Set @SequenciaFinobacciConcatenada = (Select Concat(@SequenciaFinobacciConcatenada,’,’,Numeros) from NumerosFibonacci Where Codigo = @Contador)
End
Set @Contador +=1
End
— Apresentando a Lista de Números no formato de Tabela —
Select Numeros ‘Numeros List’ From NumerosFibonacci
— Apresentando a Lista de Números no Formato Concatenado —
Select @SequenciaFinobacciConcatenada As ‘NumerosFibonacci Fibonacci’
Go
— Limpando a Tabela —
Truncate Table NumerosFibonacci
Go
— Short Script 7 – Criando uma Computed Column para uso de uma Scalar User Defined Function —
— Criando a Function F_CalcularDiferencaAnos —
Create Function F_CalcularDiferencaAnos (@DataNascimento Date)
Returns Int
As
Begin
Return (Select DATEDIFF(Year, @DataNascimento, GetDate()))
End
Go
— Criando a Tabela1 para Teste —
Create Table Tabela1
(Codigo Int,
DataNascimento Date,
DiferencaComputada As (dbo.F_CalcularDiferencaAnos(DataNascimento))) — Criando uma coluna computada com a function —
Go
— Inserindo os dados —
Insert Into Tabela1 (Codigo, DataNascimento)
Values (1,’1980-04-28′), (2,’1981-01-28′)
Go
— Validando o resultado —
Select * from Tabela1
Go
— Short Script 8 – Criando uma sequência de valores de CEPs através de uma CTE Recursiva —
Declare @Tabela table
(Codigo int, Cidade varchar(40), Inicio char(9), Fim char(9));
insert into @Tabela values (1, ‘Belo Horizonte’, ‘30000-000’, ‘35000-000’)
;With CTE_Rec as
(
Select Cidade,
Cast(left(Fim, 5) + right(Fim, 3) as int) as Fim,
Cast(left(Inicio, 5) + right(Inicio, 3) as int) as Cep
From @Tabela
Union all
Select Cidade, Fim, Cep + 1
from CTE_rec
where Cep < Fim
)
Select Cidade, cast(Cep / 1000 as char(5)) + ‘-‘ + right(’00’ + cast(Cep % 1000 as varchar), 3) as Cep,
(Cep / 1000) as Div,
(Cep % 1000) as Div2
from CTE_Rec
OPTION (MAXRECURSION 0)
Go
Ufa, conseguimos! Mais uma relação de short scripts acaba de ser compartilhada, mesmo sendo denominados short entre aspas “pequenos”, posso garantir que todos estes exemplos são de grande importância, apresentam um valor e conhecimento do mais alto nível.
Chegamos ao final de mais um Short Scripts, espero que este material possa lhe ajudar, ilustrando o uso de alguns recursos e funcionalidades do Microsoft SQL Server.
Acredito que você tenha observado que estes códigos são conhecidos em meu blog, todos estão relacionados aos posts dedicados ao Microsoft SQL Server publicados no decorrer dos últimos anos.
Boa parte deste material é fruto de um trabalho dedicado exclusivamente a colaboração com a comunidade, visando sempre encontrar algo que possa ser a solução de um determinado problema, bem como, a demonstração de como se pode fazer uso de um determinado recurso.
Caso você queira acessar os últimos posts desta sessão, não perca tempo acesse os links listados abaixo:
Agradecimento
Obrigado mais uma vez por sua visita, fico honrado com sua ilustre presença ao meu blog, desejo e espero que você possa ter encontrado algo que lhe ajudou.
Volte sempre, nos encontraremos mais uma vez na sessão Short Scripts no post do mês de dezembro.
Um forte abraço, até mais.
Olá pessoal, boa tarde.
Estou retornando com mais um post da sessão Dica do Mês, onde hoje vou compartilhar algo que poderá lhe ajudar de maneira simples e direta em suas atividades relacionadas a simulação de manipulação de dados no Microsoft SQL Server.
Acredito que em algum momento você já teve a necessidade de inserir dezenas, centenas ou até mesmo milhares de linhas em uma determinada tabela em seus bancos de dados, se você ainda não passou por esta necessidade pode ter a certeza que em algum momento isso poderá lhe acontecer.
Pensando justamente nisso, vou compartilhar no post de hoje um script de minha autoria que utilizo justamente para realizar esta inserção de dados aleatórios em uma tabela específica sem levar em consideração a qualidade e veracidade dos dados, como também regras de integridade referencial ou normalização.
Sendo assim, seja bem vindo ao post – Dica do Mês – Simulando a inserção de uma massa de dados aleatória.
Bom divertimento……
A necessidade de se trabalhar com um volume considerável de dados pode ser algo bastante comum para muitos profissionais de bancos de dados, para outros nem tanto.
Este pode parecer um cenário bastante complexo, que venha a exigir um grande conhecimento técnico ou até mesmo o uso de ferramentas de terceiros para tal finalidade. Mas na sua grande maioria os recursos existentes no próprio Microsoft SQL Server nos permite criar scripts ou blocos de códigos capazes de realizar tão procedimento sem requerer a obtenção ou exigência de um alto nível de conhecimento.
Nosso Cenário
Afim de tentar ilustrar como podemos realizar este tipo de procedimento em um ambiente de banco de dados, vamos trabalhar com um ambiente de banco de dados bastante simples e que apresenta baixa complexidade.
Nosso ambiente será forma por uma única tabela denominada MassaDeDados, composta pela seguinte estrutura de colunas:
A coluna denominada Codigo será definida como nossa coluna chave primária artificial numerada de forma sequência próprio SQL Server, tento como finalidade evitar a duplicação de registros e principalmente ser utilizada como atributo de busca e pesquisa de nossos dados. As demais colunas estarão sendo utilizadas como atributos complementares para ilustrar a capacidade de geração de dados aleatórios e posteriormente inseridos em nossa tabela MassaDeDados.
A seguir apresento o Bloco de Código 1, utilizado para criação da tabela MassaDeDados:
— Bloco de Código 1 —
Create Table MassaDeDados
(Codigo int IDENTITY(1,1) NOT NULL Primary Key,
ClienteId int NOT NULL,
VendedorId varchar(10) NOT NULL,
Quantidade smallint NOT NULL,
Valor numeric(18, 2) NOT NULL,
Data date NOT NULL)
Go
Perfeito, note que este bloco de código é bastante simples, nosso próximo passo será justamente trabalhar com o bloco de código que nos permitirá gerar o volume de dados aleatórios inseridos na sequência dentro da tabela MassaDeDados, para tal procedimentos vamos utilizar o Bloco de Código 2 apresentado abaixo:
— Bloco de Código 2 —
Declare @Texto Char(130), @Posicao TinyInt, @ContadorLinhas Int
Set @Texto = ‘0123456789@ABCDEFGHIJKLMNOPQRSTUVWXYZ\_abcdefghijklmnopqrstuvwxyzŽŸ¡ÀÁÂÃÄÅÆÇÈÉÊËÌÍÎÏÐÑÒÓÔÕÖÙÚÛÜÝàáâãäåæçèéêëìíîïðñòóôõöùúûüýÿ’ — Existem 130 caracteres neste texto —
Set @ContadorLinhas = Rand()*100000 — Definir a quantidade de linhas para serem inseridas —
While (@ContadorLinhas >=1)
Begin
Set @Posicao=Rand()*130
If @Posicao <=125
Begin
Insert Into MassaDeDados (ClienteId, VendedorId, Quantidade, Valor, Data)
Values(@ContadorLinhas,
Concat(SubString(@Texto,@Posicao+2,2),SubString(@Texto,@Posicao-4,4),SubString(@Texto,@Posicao+2,4)),
Rand()*1000,
Rand()*100+5,
DATEADD(d, 1000*Rand() ,GetDate()))
End
Else
Begin
Insert Into MassaDeDados (ClienteID, VendedorID, Quantidade, Valor, Data)
Values(@ContadorLinhas,
Concat(SubString(@Texto,@Posicao-10,1),SubString (@Texto,@Posicao+4,6),SubString(@Texto,@Posicao-12,3)),
Rand()*1000,
Rand()*100+5,
DATEADD(d, 1000*Rand() ,GetDate()))
End
Set @ContadorLinhas = @ContadorLinhas – 1
End
Observação: Note que estou utilizando a função Concat() para realizar a concatenação dos dados sem a necessidade de conversão implícita, sendo esta função recurso adicionado a partir da versão 2012 do SQL Server, caso você esteja utilizando uma versão anterior remova a função Concat() e aplique a concatenação utilizando o operador de soma.
Agora basta executar o bloco de código e verificar o resultado obtido, no meu caso após a execução deste mesmo exemplo obtive o resultado apresentado pela Figura 1 apresentada abaixo:
Figura 1 – Massa de Dados aleatórios inseridos na tabela MassaDeDados.
Observando a Figura 1, torna-se fácil analisar o conjunto de dados aleatórios inseridos em cada coluna de nossa tabela, como também, a quantidade de linhas inseridas sendo um total de 41.857 linhas de registros.
Inicialmente o tamanho do bloco de código poderá parecer complexo ou até mesmo confuso, mas na verdade não é bem assim, o grande segredo esta justamente no uso da variável @Texto que apresenta um conjunto de 130 caracteres que podemos utilizar no processo de geração do nosso volume aleatório de dados.
Outro fator muito importante é o uso da função RAND() que nos possibilidade o sorteio de números decimais, algo que ajuda bastante quando queremos trabalhar com faixas de valores distintos.
Vale ressaltar que a função RAND trabalhar com valores decimais, para que seja possível realizar o sorteio aleatório de números e depois transformar em números inteiros temos a necessidade de utilizar variáveis que trabalhem com tipos de dados inteiros, neste caso: TinyInt, SmallInt, Int ou BigInt, caso contrário os valores retornados por esta função será sempre tratados e apresentados no formato de números decimais.
Com isso chegamos ao final de mais um post da sessão Dica do Mês, espero que você tenha gostado.
Referências
https://technet.microsoft.com/pt-br/library/ms177610(v=sql.105).aspx
https://docs.microsoft.com/en-us/sql/t-sql/data-types/data-types-transact-sql
https://docs.microsoft.com/en-us/sql/t-sql/language-elements/declare-local-variable-transact-sql
https://technet.microsoft.com/pt-br/library/hh231515(v=sql.110).aspx
Posts Anteriores
Conclusão
Em diversos cenários temos a necessidade de imaginar diversas possibilidades para se obter a solução de um problema que inicialmente se apresenta com algo de outro mundo, por outro lado realizando uma análise com calma e simulando diversos cenários esta possível solução tão “misteriosa” e “complexa” pode estar na frente de nossos olhos.
No post de hoje isso não foi diferente, realizar a inserção de um volume aleatório e fictício de dados dentro do Microsoft SQL Server pode ser feito de forma bastante simples e prática, sem requerer qualquer tipo ferramentas adicionais ou conhecimento de alto nível.
Agradecimentos
Mais uma vez obrigado por sua visita, agradeço sua atenção, fique a vontade para enviar suas críticas, sugestões, observações e comentários.
Um forte abraço, nos encontramos no próximo post a ser publicado no mês de outubro.
Valeu….
Junior Galvão - MVP - Data Platform 