Alguma vez você deve ter se perguntado: “Como eu sei em qual filegroup está essa tabela?”
Existe uma ideia de que a hierarquia se dá da seguinte forma:
Instância -> Database -> Filegroup -> Tabela -> Dados
Porém está faltando um item aí que acaba passando despercebido muitas vezes… Os índices! Ele é quem define onde os dados de uma tabela serão armazenados.
O índice cluster é o responsável por organizar a tabela fisicamente. Seria conveniente dizer que ele “É” os dados da sua tabela (falar que os dados estão dentro do índice cluster me soa errado, então deixemos a frase estranha mesmo 🙂 ). Então, na prática, ao criar o seu índice cluster, você estará especificando o filegroup onde os dados serão armazenados.
O índice não cluster organiza os teus dados de uma forma lógica, mas ele também ocupa espaço, uma vez que ele possui os campos que foram especificados na criação do índice (mais o teu índice cluster).
“Ok Logan, mas a minha tabela é uma HEAP, não tem índice cluster… Como fica?”
Bom, se você parar pra pensar, uma HEAP table possui a ordem física baseada na ordem de inserção do registro… Então imagine que existe um índice, mas com ordenação baseada na ordem de chegada no momento da inserção (rowid).
Vendo isso na prática, vamos criar um banco com 2 filegroups (Primary e Secondary), criar duas tabelas, uma com índice cluster e outra sem (uma heap table) e vamos inserir 1 milhão de registros nela.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 |
USE master GO IF (DB_ID('TesteFilegroups') IS NOT NULL) BEGIN USE TesteFilegroups ALTER DATABASE TesteFilegroups SET SINGLE_USER WITH ROLLBACK IMMEDIATE USE master drop database [TesteFilegroups] END go CREATE DATABASE [TesteFilegroups] ON PRIMARY ( NAME = N'TesteFilegroups', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\DATA\TesteFilegroups_Data.mdf' , SIZE = 5120KB , FILEGROWTH = 1024KB ), FILEGROUP [SECONDARY] ( NAME = N'TesteFilegroups_1', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\DATA\TesteFilegroups_Data2.ndf' , SIZE = 5120KB , FILEGROWTH = 1024KB ) LOG ON ( NAME = N'TesteFilegroups_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\DATA\TesteFilegroups_Log.ldf' , SIZE = 2048KB , FILEGROWTH = 10%) GO USE TesteFilegroups GO CREATE TABLE ClustFGPRIMARY (id int identity(1,1), nome varchar(10),nascimento datetime, cpf varchar(11), sexo char(1)) ON [PRIMARY] GO create unique clustered index pk_fgprimary on ClustFGPRIMARY(id) on [primary] go CREATE TABLE HeapFGPRIMARY (id int identity(1,1), nome varchar(10),nascimento datetime, cpf varchar(11), sexo char(1)) ON [PRIMARY] GO CREATE TABLE HeapFGSECONDARY (id int identity(1,1), nome varchar(10),nascimento datetime, cpf varchar(11), sexo char(1)) ON [Secondary] go insert into ClustFGPRIMARY values ('Logan','1981-05-25','99999999999','M') insert into HEAPFGPRIMARY values ('Logan','1981-05-25','99999999999','M') insert into HeapFGSECONDARY values ('Logan','1981-05-25','99999999999','M') go 1000000 |
Ok, feito isso, vamos ver a tabela sys.indexes e a sys.tables :
|
1 2 3 4 |
SELECT tbl.name, idx.name, idx.type_desc, idx.type FROM sys.tables tbl JOIN sys.indexes idx ON tbl.object_id = idx.object_id |
Notem que mesmo não criando um índice, um join com a sys.indexes retorna a tabela HEAP, por isso acho que a analogia acima é válida. Vejam também que, a não ser pelo nome da tabela, eu não consigo ver em qual filegroup o registro foi inserido. Para isso, vamos mudar a consulta, fazendo mais um join, desta vez com a sys.filegroups
|
1 2 3 4 5 6 |
SELECT tbl.name, idx.name, idx.type_desc, idx.type, fg.name FROM sys.tables tbl JOIN sys.indexes idx ON tbl.object_id = idx.object_id JOIN sys.filegroups fg ON idx.data_space_id = fg.data_space_id |
Pronto… Desta forma, conseguimos descobrir “em quais filegroups estão as nossas tabelas”.
E agora… Se quisermos mudar os dados entre filegroups, como devemos fazer?
Bom, isso é assunto para um próximo post… Ou vocês acharam que os registros tinham sido inseridos para te fazer perder 30 minutos (aqui pelo menos) inserindo registros à toa? 🙂
Espero que tenham gostado… Dúvidas? Críticas? Sugestões de melhorias? Comentem… vamos agregar conteúdo!
[]’s!
Oi Logan, tudo bem?
Um pequeno ponto na parte da composição do seu INC:
(mais o teu índice cluster).
Na verdade, seria a chave do índice cluster (as colunas que o compõem) e não o índice inteiro.
Cya!
Oi Logan,
Só uma pequena consideração referente a ‘HEAP table possui a ordem física baseada na ordem de inserção do registro…’
Na heap, o dado é inserido em qualquer página que possuia espaço disponível, logo não há ordem de inserção do registro. Além disso, a heap é composta por páginas desordenadas semligação entre si. Logo, heap e ordenação não combinam =)
É verdade Socorro!
Quando eu falei em ordem física eu estava pensando em algo do tipo “entrou 2 ou mais linhas, elas são armazenadas conforme chegar no banco”, por isso comentei pra imaginar a ordem por rowid…
Valeu o comentário! 🙂