set statistics io off
go
set nocount on
go
drop table T_A2
go
drop table T_A1
go
create table T_A1 (
idT_A1 bigint identity,
info varchar(50)
constraint pk_idT_A1 primary key (idT_A1)
)
go
insert into T_A1 (info) values (cast(getdate() as varchar(50)))
go 10
go
create table T_A2 (
idT_A2 bigint identity,
idT_A1 bigint,
info varchar(50)
constraint pk_idT_A2 primary key (idT_A2)
constraint fk_idT_A1 foreign key (idT_A1) references T_A1(idT_A1)
)
create index idx_idT_A1 on T_A2 (idT_A1)
go
insert into T_A2 (idT_A1, info) values (cast(ceiling(rand() * 10) as int), cast(getdate() as varchar(50)))
go 100000
------------------------------------------------
drop table T_B2
go
drop table T_B1
go
create table T_B1 (
idT_B1 int identity,
info varchar(50)
constraint pk_idT_B1 primary key (idT_B1)
)
go
insert into T_B1 (info) SELECT info FROM T_A1
go
create table T_B2 (
idT_B2 int identity,
idT_B1 int,
info varchar(50)
constraint pk_idT_B2 primary key (idT_B2)
constraint fk_idT_B1 foreign key (idT_B1) references T_B1(idT_B1)
)
create index idx_idT_B1 on T_B2 (idT_B1)
go
insert into T_B2 (idT_B1, info) SELECT idT_A1, info FROM T_A2
go
-------------------------------------------------------------------------
set statistics io on
go
select count(*) from T_A2 where idT_A1 = 1 --option (maxdop 1)
select count(*), max(len(info)) from T_A2 where idT_A1 = 1 --option (maxdop 1)
go
select count(*) from T_B2 where idT_B1 = 1 --option (maxdop 1)
select count(*), max(len(info)) from T_B2 where idT_B1 = 1 --option (maxdop 1)La conclusion de cet exemple est que nous avons un gain de 20% de lecture de pages, si nous passons une clé primaire de ‘bigint’ à ‘tinyint’.
L’écart est moindre si nous transformons la clé en ‘int’ ou ‘smallint’
L’écart est plus important si nous travaillons avec des tables sans index cluster (implicitement, les clés primaires sont des index cluster, avec SQL Server)