cocogorilla's questions

O tipo de agrupamento padrão no SQL Server permite a indexação em cadeias de caracteres que não diferenciam maiúsculas de minúsculas, mas as maiúsculas e minúsculas dos dados são mantidas. Como isso realmente funciona? Estou procurando as porcas e parafusos reais, bits e bytes, ou um bom recurso que explica isso em detalhes.

create table casetest (fruitnames nvarchar(50) not null);
create unique index IX_fruitnames on casetest(fruitnames);

insert into casetest values ('apples');
insert into casetest values ('Pears');
-- this insert fails
insert into casetest values ('pears');

-- this yields 'Pears' as a result
select * from casetest (forceseek) where fruitnames = 'PEARS'

update casetest set fruitnames = 'pears' where fruitnames = 'pEArs'

-- this yields 'pears' as a result
select * from casetest (forceseek) where fruitnames = 'PEARS'

Perguntas sobre agrupamentos do SQL Server que você era muito tímido para perguntar por Robert Sheldon aborda como usar o agrupamento. Ele não cobre como funciona o agrupamento. Estou interessado em como um índice pode ser criado/consultado com eficiência, sem se importar com o caso, enquanto armazena simultaneamente os dados do caso.

O que há nos Phonevalores inseridos abaixo que o SQL Server os trata como idênticos em um índice exclusivo?

CREATE TABLE Phone
(
  Id int identity(1, 1) primary key,
  Phone nvarchar(448) not null
)
go

create unique index IX_Phone on Phone(Phone)
with (data_compression = page);
go

insert into Phone Values ('?281/?263-?8400');
insert into Phone Values ('‎281/‎263-‎8400');

select * from Phone;

drop table Phone;

Recebo uma mensagem de erro:

Msg 2601, Level 14, State 1, Line 13 Não é possível inserir linha de chave duplicada no objeto 'dbo.Phone' com índice exclusivo 'IX_Phone'. O valor da chave duplicada é (?281/?263-?8400).

Estou revisando scripts recém-gerados de ferramentas de publicação SSDT. Esses projetos foram construídos a partir de um modelo padrão, assim como os perfis de publicação. Eles definiram page_verify nonepara o banco de dados.

Eu estava com uma impressão nebulosa de que a melhor prática seria defini-lo como checksum.

A única coisa em que consigo pensar é que a plataforma de destino do SSDT é o Azure. Alguém pode esclarecer isso com algo autoritário?

Obrigado

Alguém sabe uma solução alternativa para isso? Essencialmente, o procedimento armazenado força um operador de inserção na exibição indexada, mesmo que as linhas não sejam qualificadas. Como resultado, há um erro de conversão. No entanto, para ad hocs, o sql elimina corretamente a exibição da consideração.

Considere o seguinte esquema:

create table testdata (
    testid int identity(1,1) primary key
  , kind varchar(50)
  , data nvarchar(4000))
go
create view integer_testdata with schemabinding
as
select cast(a.data as int) data, a.kind, a.testid
  from dbo.testdata a
 where a.kind = 'integer'
go
create unique clustered index cl_intdata on integer_testdata(data)
go
create procedure insert_testdata
(
    @kind varchar(50)
  , @data nvarchar(4000)
)
as
begin
  insert into testdata (kind, data) values (@kind, @data)
end
go

Todos estes funcionam:

insert into testdata (kind, data) values ('integer', '1234');
insert into testdata (kind, data) values ('integer', 12345);
insert into testdata (kind, data) values ('noninteger', 'noninteger');
exec insert_testdata @kind = 'integer', @data = '123456';
exec insert_testdata @kind = 'integer', @data = 1234567;

Isso falha:

exec insert_testdata @kind = 'noninteger', @data = 'noninteger';

Uma comparação dos "planos de execução estimados":

insert into testdata (kind, data) values ('noninteger', 'noninteger'):

exec insert_testdata @kind = 'noninteger', @data = 'noninteger':

Estou obtendo um comportamento em um plano de consulta que não consigo explicar. A diferença está entre os dois índices filtrados com os quais estou testando. Um usa a where id is not nulle o outro usa where id > 0. Em meus dados reais, obtenho um tempo de execução favorável de 95% usando o > 0índice. Não consigo ver por que eles seriam diferentes ... minhas chaves são inteiros de incremento automático começando em 1 referenciado pela tabela adjacente em uma coluna anulável. Abaixo está um script que irá gerar estruturas e dados análogos aos meus dados de produção.

Observe o seguinte... se você executar esses scripts e, em seguida, comparar as duas instruções de seleção, provavelmente obterá cinquenta e cinquenta desempenhos como eu. No entanto , em meus dados de produção, a consulta que utiliza o > 0índice filtrado escolhe uma varredura de índice em vez de buscar. Essa verificação é executada muito mais rapidamente. Abaixo está uma captura de tela da minha comparação real do plano de consulta. Em segundo lugar, reconstruí esses índices, a fragmentação não é um problema.

Perguntas: Por que a disparidade? De onde vem a varredura versus busca? Não seria is not nulle > 0seria equivalente em uma junção quando o tipo de dados é identidade int(1,1)?

Esquema + Dados:

if exists (select * from sys.tables where name = 'sometable')
begin drop table sometable end
go

create table sometable (id int not null identity(1,1) primary key
                      , value nvarchar(50));
go

insert into sometable values ('a test value');
insert into sometable values ('a test value');
insert into sometable values ('a test value');
insert into sometable values ('a test value');
insert into sometable values ('a test value');
go

if exists (select * from sys.tables where name = 'audit')
begin drop table audit end
go

create table audit (id int not null identity(1,1) primary key
                  , sometable_id int null
                  , someothertable_id int null
                  , auditvalue nvarchar(50));
go

declare @count int = 0;
while (@count < 40000)
begin
insert into audit (sometable_id,someothertable_id,auditvalue) values (floor(rand()*5+1),null,'a sometable audit');
insert into audit (sometable_id,someothertable_id,auditvalue) values (floor(rand()*5+1),null,'another sometable audit');
insert into audit (sometable_id,someothertable_id,auditvalue) values (null,1,'irrelevant other table audit');
insert into audit (sometable_id,someothertable_id,auditvalue) values (floor(rand()*5+1),null,'another audit for record one sometable');
insert into audit (sometable_id,someothertable_id,auditvalue) values (floor(rand()*5+1),null,'record three audit');
insert into audit (sometable_id,someothertable_id,auditvalue) values (floor(rand()*5+1),null,'another record 3 audit');
insert into audit (sometable_id,someothertable_id,auditvalue) values (null,50,'irrelevant1');
insert into audit (sometable_id,someothertable_id,auditvalue) values (null,51,'irrelevant2');
insert into audit (sometable_id,someothertable_id,auditvalue) values (null,52,'irrelevant3');
insert into audit (sometable_id,someothertable_id,auditvalue) values (floor(rand()*5+1),null,'relevant fourth record');
insert into audit (sometable_id,someothertable_id,auditvalue) values (floor(rand()*5+1),null,'back to one record');
insert into audit (sometable_id,someothertable_id,auditvalue) values (null,53,'irrelevant 4');
insert into audit (sometable_id,someothertable_id,auditvalue) values (null,54,'irrelevant fifth record');
insert into audit (sometable_id,someothertable_id,auditvalue) values (null,55,'irrelevant sixth record');
insert into audit (sometable_id,someothertable_id,auditvalue) values (null,56,'irrelevant seventh record');
insert into audit (sometable_id,someothertable_id,auditvalue) values (floor(rand()*5+1),null,'a fifth record audit');
insert into audit (sometable_id,someothertable_id,auditvalue) values (floor(rand()*5+1),null,'another fourth record audit');
set @count = (@count + 1);
end
go

--drop index audit.filter_null_audits_for_sometable
create index filter_null_audits_for_sometable on audit(sometable_id,id) include(auditvalue) where sometable_id is not null;
go
--drop index audit.filter_upzero_audits_for_sometable
create index filter_upzero_audits_for_sometable on audit(sometable_id,id) include(auditvalue) where sometable_id > 0;
go

Duas consultas:

select top 50000 a.id sometableid,a.value,b.id auditid,b.auditvalue
  from sometable a
  join audit b with(index(filter_null_audits_for_sometable)) on a.id = b.sometable_id

select top 50000 a.id sometableid,a.value,b.id auditid,b.auditvalue
  from sometable a
  join audit b with(index(filter_upzero_audits_for_sometable)) on a.id = b.sometable_id and b.sometable_id > 0

Atualização1

Copiei dados de produção em minhas tabelas de teste. Em vez de cinquenta e cinquenta, os resultados corresponderam ao plano de consulta incluído e reproduziram a disparidade. O cenário de teste é estruturalmente análogo.

Atualização2

select a.id sometableid,a.value,b.id auditid,b.auditvalue
  from sometable a
 inner merge join audit b with(index(filter_null_audits_for_sometable)) 
    on a.id = b.sometable_id

select a.id sometableid,a.value,b.id auditid,b.auditvalue
  from sometable a
  join audit b with(index(filter_upzero_audits_for_sometable)) 
    on a.id = b.sometable_id

Esses planos de consulta não serão compilados. Por quê? Eles me forçam a usar > 0como condições de junção de consulta para obter o plano otimizado.