Jason's questions

Quero comparar dois conjuntos de valores e ver se há interseções. O primeiro conjunto de valores é especificado em tempo de execução e o segundo conjunto de valores é armazenado em uma linha de um banco de dados.

Aqui estão alguns códigos de exemplo:

CREATE TABLE #tab1 (var1 INT, var2 INT , var3 INT)

INSERT INTO #tab1 
VALUES (1,2,3),(0,0,0),(0,4,0)

Select
a = (case when 1 IN (var1,var2,var3) OR
               2 IN (var1,var2,var3) OR
               3 IN (var1,var2,var3)
    THEN 1 else 0 end) 
from #tab1

Eu sinto que o código é muito complicado e estou querendo saber se há alguma maneira simples de fazê-lo. Obrigado!

Estou usando o SQL Server Management Studio 2016 no Windows 10. Estou cansado de usar as teclas shift/ctrl+up/down/right/left para selecionar o código que quero executar. Eu estou querendo saber se existem atalhos/snippets para selecionar um bloco de código separado de outro código por linhas em branco?

Aqui está um exemplo de código:

select *
from tab1

select *
from tab2

select *
from tab3

Digamos, meu cursor está dentro do bloco do meio e qual é a melhor maneira de selecionar o bloco do meio?

Eu uso o SQL Server 2016 Developer Edition no Windows 10 de 64 bits. Todos os bancos de dados são locais no meu laptop.

Eu tenho um arquivo de script SQL e o Intellisense não funciona nele. Habilitei o Intellisense e tentei várias correções sugeridas neste post:

O IntelliSense não está funcionando, mas está ativado

Nenhuma dessas correções funciona para mim; no entanto, o Intellisense funciona em todos os outros arquivos. A única coisa especial deste arquivo é seu tamanho: tem mais de 600 linhas de código.

Parece que o problema é causado por um comando que cria o índice columnstore. Aqui está um código de exemplo:

SELECT TOP 100 *
INTO #test1
FROM tab1

CREATE CLUSTERED COLUMNSTORE INDEX idx ON #test1

SELECT TOP 100 *
INTO #test2
FROM tab1

Descobri que o Intellisense parou de funcionar depois de inserir o bloco de código no meio! O contorno também desaparecerá após a inserção do bloco de código do meio.

Depois de remover o bloco de código do meio, o contorno e o Intellisense funcionarão novamente.

Alguém pode me ajudar a testar este caso de exemplo?

--

Tentei adicionar ponto e vírgula um GO para separar o código, mas nenhum deles funciona para mim. Agora parece que a única solução viável é comentar esse bloco do meio.

Eu tenho uma tabela com dois campos: hora e valor, ambos são inteiros e quero encontrar o período contínuo com valores maiores que 0 e rotular o período "valor>0" com inteiros contínuos.

Por exemplo, se eu tiver a tabela de entrada assim:

Eu gostaria da tabela de saída assim:

Usando três funções CTE e duas funções row_number(), consegui fazer isso. Mas acho a consulta muito complicada. Alguém tem um código mais elegante para fazer isso?

Estou usando o SQL Server 2016 Developer Edition.

Aqui está o meu código:

CREATE TABLE #test1(
    [time] [int] NULL,
    [value] [int] NULL
)

insert into #test1
values(1,0),(2,0),(3,1),(4,0)
    ,(5,1),(6,2),(7,0),(8,0)
,(9,1),(10,2),(11,3),(12,0)
,(13,0),(14,0),(15,1),(16,0);

;with a1 as
(select *, [time] -  row_number() over (order by [time]) as group_num
    from #test1
where value>0),
a2 as 
(select distinct group_num from a1
),
a3 as
(select group_num, row_number() over (order by group_num) as group_id
from a2)
select a1.*, a3.group_id
    from a1 left join a3
on a1.group_num = a3.group_num

Estou tentando entender a ordem de precedência das operações lógicas e tenho o seguinte código:

declare @T bit ='TRUE'
declare @F bit ='False'

print @T and @F

e retorna um erro como

Sintaxe incorreta perto da palavra-chave 'and'.

Substituí 'e' por '&' e o código funciona novamente. Por que o código anterior não funcionou? Estou usando o servidor SQL.

Desejo converter uma tabela rowstore em uma tabela columnstore criando um índice columnstore clusterizado. Existem três colunas na tabela: id, hora e valor.

A tabela é ordenada por id e hora antes de criar o índice columnstore; no entanto, depois de criar o índice columnstore, a ordem das linhas fica confusa. Achei que poderia ser devido ao paralelismo e adicionei a maxdop = 1opção, mas isso não resolveu o problema. Alguém pode me ajudar com isso?

Aqui está o código criando tabelas e índices:

-- creating rowstore table
drop table if exists tab1_rstore
select id, time, value
into tab1_rstore
from tab0
order by id_loan, period
option(maxdop 1)

-- creating clustered index on rowstore table
create clustered index idx on tab1_rstore (id,time)

-- creating columnstore table 
select * 
into tab1_cstore
from tab1_rstore
    option(maxdop 1)

-- comparing the first two rows from these two tables
    select top 2 *
from tab1_rstore

    select top 2 *
from tab1_cstore

A captura de tela dos resultados da consulta:

-- creating clustered columnstore index
create clustered columnstore index idx on tab1_cstore 
with (maxdop = 1) 

-- comparing the top two rows again
select top 2 *
from tab1_rstore

select top 2 *
from tab1_cstore

A captura de tela dos resultados da consulta com o índice columnstore:

Meu entendimento é que a ordem das linhas é determinada pelo algoritmo de compressão e não há nada que possamos fazer a respeito, veja a limitação e restrição no documento aqui com a seguinte citação:

Não é possível incluir as palavras-chave ASC ou DESC para classificar o índice. Os índices Columnstore são ordenados de acordo com os algoritmos de compactação. A classificação eliminaria muitos dos benefícios de desempenho.

Estou usando o SQL Server 2016 Developer Edition no Windows 10 64 bits.

Eu tenho uma tabela com 20 milhões de linhas e cada linha tem 3 colunas: time, ide value. Para cada ide time, há um valuepara o status. Eu quero saber os valores de lead e lag de um certo timepara um específico id.

Eu usei dois métodos para conseguir isso. Um método está usando join e outro método está usando as funções de janela lead/lag com índice clusterizado ativado timee id.

Comparei o desempenho desses dois métodos pelo tempo de execução. O método join leva 16,3 segundos e o método window function leva 20 segundos, sem incluir o tempo para criar o índice. Isso me surpreendeu porque a função da janela parece ser avançada enquanto os métodos de junção são força bruta.

Aqui está o código para os dois métodos:

Criar índice

create clustered index id_time
 on tab1 (id,time)

Método de junção

select a1.id,a1.time
   a1.value as value, 
   b1.value as value_lag,
   c1.value as value_lead
into tab2
from tab1 a1
left join tab1 b1
on a1.id = b1.id
and a1.time-1= b1.time
left join tab1 c1
on a1.id = c1.id
and a1.time+1 = c1.time

Estatísticas de IO geradas usando SET STATISTICS TIME, IO ON:

Aqui está o plano de execução para o método join

Método de função de janela

select id, time, value, 
   lag(value,1) over(partition by id order by id,time) as value_lag,
   lead(value,1) over(partition by id order by id,time) as value_lead
into tab2
from tab1

(Ordenar apenas por timeeconomiza 0,5 segundos.)

Aqui está o plano de execução para o método de função Window

estatísticas de IO

[

Eu verifiquei os dados sample_orig_month_1999e parece que os dados brutos estão bem ordenados por ide time. Esta é a razão da diferença de desempenho?

Parece que o método join tem mais leituras lógicas do que o método window function, enquanto o tempo de execução para o primeiro é realmente menor. É porque o primeiro tem um paralelismo melhor?

Eu gosto do método de função de janela por causa do código conciso, existe alguma maneira de acelerá-lo para esse problema específico?

Estou usando o SQL Server 2016 no Windows 10 64 bits.

Eu tenho uma tabela com 20 milhões de linhas e cada linha tem 3 colunas: time, ide value. Para cada ide time, há um valuepara o status. Quero saber os valores do último e dos próximos períodos para um determinado timee id, e tenho a seguinte consulta para obter os valores:

update a1
set  a1.value_last = b1.value,   
     a1.value_next = c1.value
from tab1 a1
left join tab1 b1
on a1.id = b1.id
and a1.period = b1.period + 1
left join tab1 c1
on a1.id = c1.id
and a1.period = c1.period - 1

Parece que a consulta demora uma eternidade e o arquivo de log aumentou em mais de 10 GB. Eu estou querendo saber qual é a maneira mais eficiente de escrever esta consulta? Sei que usar o índice acelerará o processo de junção, mas como posso reduzir o registro?

Estou usando o SQL Server 2016 no Win10 64 bits.

Eu tenho um .txtarquivo com tamanho de armazenamento de 1253 MB. Após importá-la para o SQL Server 2016, a tabela ocupa 1680 MB em disco. Por que o espaço de armazenamento cresce tanto?

Não fiz nenhuma transformação de dados e usei o charformato puro para armazenar dados. Eu leio o dicionário de dados do txtarquivo e se o comprimento de uma variável for no máximo n, então eu uso varchar(3). Se o arquivo tiver comprimento fixo n, usei char(n).

Cerca de 4 a 5 colunas são densamente preenchidas e todas as outras colunas são muito esparsas. Para as colunas esparsas, apenas 1% das linhas não são nulas.

Aqui está o código criando a tabela e importando dados:

CREATE TABLE table1 (
var1 char(12),      var2 char(6),      var3 varchar(12),       var4 varchar(3),
var5 varchar(3),    var6 varchar(4),   var7 char(1),           var8 char(1),
var9 char(2),       var10 char(6),     var11     varchar(8),   var12 varchar(12),
var13 char(6),      var14 varchar(12), var15 varchar(14),var16 varchar(12),
var17 varchar(12),  var18 varchar(12), var19 varchar(12),var20 varchar(12),
var21 varchar(12),  var22 varchar(12));

Como posso reduzir o espaço de armazenamento?

Aqui estão as informações de armazenamento do comando sp_spaced:

tabela de nomes1
linhas 22260960
reservado 1721240 KB
dados 1721128 KB
index_size 24 KB
88 KB não utilizados

Tenho duas consultas pesadas para executar e ambas levam cerca de 1 minuto para serem executadas. Ambas as consultas estão usando o comando Bulk Insert para inserir dados de arquivos de texto em tabelas no banco de dados.

Para as duas tabelas que importam dados, não há índices/gatilhos/restrições, apenas duas tabelas vazias para carregar os dados.

Para carregar os dois arquivos de texto, cada um deles tem cerca de 20 milhões de linhas.

Consulta 1:

Bulk insert table1
FROM 'table1.txt'  WITH
        (FIELDTERMINATOR ='|',
         ROWTERMINATOR = '0x0a',
         TABLOCK)

Consulta 2:

Bulk insert table2
FROM 'table2.txt'  WITH
        (FIELDTERMINATOR ='|',
         ROWTERMINATOR = '0x0a',
         TABLOCK)

Eu quero comparar o tempo de execução em diferentes cenários.

Cenário 1: duas consultas sendo executadas em série em um editor:

• Consulta 1: 1 m 18 s
• Consulta 2: 1 m 2 s
• O tempo total de execução é de 2m 20s.

Cenário 2: cada consulta em um editor separado da mesma instância do Studio, executando simultaneamente:

• Consulta 1: 2 m 36 s
• Consulta 2: 2 m 09 d
• O tempo total de execução é de 2m 40s

Cenário 3: cada consulta em uma instância separada do Studio, executando simultaneamente:

• Consulta 1: 2 m 29 s
• Consulta 2: 2 m 19 s
• O tempo total de execução é de 2m 29s

Embora os cenários 2 e 3 pareçam executar consultas simultaneamente, por que o tempo de execução não mudou muito? Para todos os cenários, o uso da CPU está entre 17% e 21%, o uso do disco é de 50 MB a 70 MB por segundo. Não foram observadas diferenças significativas entre o uso da CPU e do disco.

Eu estou querendo saber o que está acontecendo sob o capô? Como o SQL Server executa essas consultas? Por que o tempo de execução é quase o mesmo para todos os três cenários? Existe alguma maneira de agilizar?

Estou usando o SQL Server 2016 Developer Edition no Windows 10 de 64 bits. Eu tenho quad core i7 e SSD no meu laptop.

Depois de testar mais cenários com 4 consultas rodando juntas, acho que a possível causa do baixo uso de CPU e disco é o hyperthreading com meu Core i7:

• Com dois editores executando duas consultas simultaneamente, o uso máximo da CPU é de 25%.

• Com 4 editores executando 4 consultas simultaneamente, o uso máximo da CPU é de 50%.

Vocês estão cientes de maneiras de permitir que uma consulta use exclusivamente um núcleo? Desativar o hyperthreading não é uma opção para mim, pois meu ThinkPad T460p não oferece suporte a essa opção no BIOS.

Tenho um arquivo de dados em formato txt e costumava carregá-lo com Bulk Insert no seguinte comando:

Bulk insert [table1]
FROM 'data.txt'  
WITH   
  ( 
     FIELDTERMINATOR ='|',
     ROWTERMINATOR ='0x0a',
  )

O código funciona bem. No entanto, enquanto tentava usar um arquivo de formato com a inserção em massa para carregar o arquivo, encontrei alguns problemas ao especificar o terminador de campo para a última linha.

Aqui está a especificação para a última linha no arquivo de formato:

21  SQLCHAR  0  20  "0x0a"  21  misc_costs       SQL_Latin1_General_CP1_CI_AS

Com "0x0a" como terminador, recebo o seguinte erro:

Erro de conversão de dados de carregamento em massa (truncamento) para linha 2, coluna 1 (var1).

Com "\n", os dados podem ser carregados, mas "|" também é carregado na última coluna. Por exemplo, recebo "0,5|" em vez de "0,5".

"|\n","0x0a","|0x0a",'\r\n' retorna o mesmo erro que "|"

Erro de conversão de dados de carregamento em massa (truncamento) para linha 2, coluna 1 (var1).

Estou usando o sql-server 2016, windows 10 64 bits.

Alguém pode ajudar com isso? Agradeço antecipadamente por sua ajuda!

jasão

Estou tentando criar um arquivo de formato usando o utilitário bcp seguindo as instruções nesta página:

Usando um arquivo de formato para importar dados em massa

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

No entanto, esta página discute apenas como criar arquivos de formato para tabelas nos bancos de dados. Meu destino é criar um arquivo de formato para um arquivo txt no meu disco e, em seguida, usar o arquivo de formato para importar os dados brutos no arquivo de texto para o banco de dados. Tentei o seguinte comando:

bcp C:\data.txt format nul -c -f txt1.fmt -T

e obter o seguinte erro:

Um nome de tabela válido é necessário para as opções de entrada, saída ou formato.

Estou usando o sql server 2016 e meu sistema é windows 10 64 bits.

Alguém pode me ajudar com isso, desde já agradeço a ajuda!

jasão

Tenho mais de 100 arquivos para importar para o sql server e a maioria deles são de 500 MB. Desejo aproveitar o utilitário de importação paralela do servidor SQL e li várias páginas da Web, como as seguintes:

Como carregar dados de 1 TB em 30 minutos

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

Importando dados em paralelo com bloqueio de nível de tabela

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

e as respostas no stackoverflow

Maneira mais rápida de inserir em paralelo a uma única tabela

No entanto, nenhum deles deu um exemplo simples com código. Sei como usar a inserção em massa/bcp, mas não sei por onde começar com a importação paralela. Alguém pode me ajudar com isso?

Meu sistema é Windows e estou usando o SQL Server 2016. O arquivo de dados de origem está no formato txt.

Agradeço antecipadamente por sua ajuda!

jasão

Eu tenho uma tabela tão grande quanto 8 GB e com 20 milhões de registros. Existe um campo int chamado mth. O campo mth armazena informações de data na forma de AAAAMM, desejo transformar o campo mth em inteiros consecutivos. Então eu uso uma fórmula para pegar o ano e o mês do campo mth e calcular a ordem do mês, especificamente, eu uso o seguinte código:

create function mth_to_num(@month int)
returns int
as 
begin
   return(round(@month/100,0)*12+@month-100*round(@month/100,0))
end

Então eu uso o seguinte código para atualizar o valor na tabela enorme

update full_orig_month_Q1_1999 
set mth_order = dbo.mth_to_num(period)
Go 

No entanto, o código demorou muito para ser executado, cerca de 2 a 3 minutos. Meu sistema é windows 10 64 bits com SQL Server 2016. Existe alguma maneira de acelerar?

Outro problema é que encontrei o SQL Server ocupando até 8 GB no banco de dados após executar a consulta acima. Precisa de tanta memória? Como posso libertá-los?

Obrigado por sua ajuda com antecedência!

jasão