Uma constraint ajuda muita gente, duas constraints ajudam muito mais

Deparo-me com esta questão algumas vezes, por isso acho que é daquelas que merece uma entrada aqui.

Já criei esta demo há algum tempo para mostrar a um colega de trabalho o que acontece quando ajudamos o query optimizer, dizendo-lhe algumas coisas que nós já sabemos sobre os nossos dados.

Neste caso concreto, “particionar” tabelas manualmente, mas sem colocar nenhuma check constraint serve para… pouco. 🙂

Sim, podemos ter as partições em outros filegroups e em diferentes discos, mas em termos de performance é muito melhor ajudar o motor do SGBD (neste caso o meu SQL Server) o mais que pudermos.

E há algo simples que podemos fazer para ajudar.

Vejamos um caso simples

Criamos umas tabelas

CREATE TABLE [dbo].[Dates2015](
            [id] [bigint] primary key IDENTITY(1,1) NOT NULL,
            [Dt_ref] [date] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Dates2016](
            [id] [bigint] primary key IDENTITY(1,1) NOT NULL,
            [Dt_ref] [date] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Dates2017](
            [id] [bigint] primary key IDENTITY(1,1) NOT NULL,
            [Dt_ref] [date] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Dates2018](
            [id] [bigint] primary key IDENTITY(1,1) NOT NULL,
            [Dt_ref] [date] NOT NULL
) ON [PRIMARY]
GO

e uma vista apenas a juntar as 4

CREATE VIEW [dbo].[DatesAll]
AS
            SELECT        *
            FROM 
            dbo.Dates2015 
union all
            SELECT        *
            from 
            dbo.Dates2016 
union all
            SELECT        *
            from 
            dbo.Dates2017 
union all
            SELECT        *
            from 
            dbo.Dates2018 
GO

Vamos ligar o nosso “Actual Execution Plan” e ver o que acontece.

select top 1000 *
from [dbo].[DatesAll]

É justo… temos as tabelas vazias, mas como temos sempre um estimated rows de 1, vamos lá “tocar” e ver que não há nada.

Esta query também não deve ter truque…

select top 100 *
from [dbo].[DatesAll]
where dt_ref < '2015-01-06'

Vamos lá a ver se podemos ajudar aqui um pouco o nosso “amigo” SQL Server.

Como “sabemos” que dentro de cada tabela só vamos ter datas do ano correspondente, criamos umas check constraints

alter table [dbo].[Dates2015]
add constraint dt_2015
            check ( dt_ref >= '2015-01-01' 
                    and
                    dt_ref <= '2015-12-31' )


alter table [dbo].[Dates2016]
add constraint dt_2016
            check ( dt_ref >= '2016-01-01' 
					and
					dt_ref <= '2016-12-31' )

alter table [dbo].[Dates2017]
add constraint dt_2017
            check ( dt_ref >= '2017-01-01' 
					and
					dt_ref <= '2017-12-31' )

alter table [dbo].[Dates2018]
add constraint dt_2018
            check ( dt_ref >= '2018-01-01' 
					and
					dt_ref <= '2018-12-31' )

E agora?

select top 100 *
from [dbo].[DatesAll]
where dt_ref < '2015-01-06'

Agora o SQL Server “sabe” que a data só poderá estar numa das 4 tabelas da vista. E nem toca nas outras.

Não dói nada, pois não?