15.03.2009

Horizontal Partitioning on SQL Server 2000

Couple of days ago somebody ask me how to configure partitioned tables on SQL Server 2000. SQL Server 2000 does not support this feature but on the other hand you can use horizontal partitioned views.

In this post i’ll tell you how to configure horizontal partitioning on SQL Serer.

  1. configure your tables without identity column
  2. you can not create clustered indexes on views
  3. configure one column for check constraint
  4. Create clustered indexes on Identifier of tables and columns that you put check constrained
if exists(select * from master.dbo.sysdatabases where name = 'BlogDB')
drop database BlogDB
go
create database BlogDb
go
use BlogDB
go
if exists(select * from sysobjects where name = 'vw_Documents')
drop view vw_Documents
go
if exists(select * from sysobjects where name = 'tbl_pdf_documents')
drop table tbl_pdf_documents
go
if exists(select * from sysobjects where name = 'tbl_word_documents')
drop table tbl_word_documents
go
create table tbl_pdf_documents
(
DocumentId uniqueidentifier not null
,
Document varchar(8000) not null ,
Title varchar(255) not null ,
Type varchar(5) not null check(Type = '.pdf')

constraint PK_Pdf_Documents primary key (DocumentId,Type)
)
go
create table tbl_word_documents
(
DocumentId uniqueidentifier not null ,
Document varchar(8000) not null,
Title varchar(255) not null,
Type varchar(5) not null check(Type = '.doc')

constraint PK_WordDocuments primary key (DocumentId,Type)
)
go


go
create view vw_Documents
with schemabinding
as
select DocumentId ,Document,Title,Type
from dbo.tbl_pdf_documents
union all
select DocumentId,Document,Title,Type
from dbo.tbl_word_documents
go




Sample Insertion Code 


declare @i int
set @i = 1000

declare @t varchar(4)


while @i > 0
begin

if @i % 2 = 0
set @t = '.pdf'
else
set @t = '.doc'
insert into
vw_Documents(DocumentId,Document,Title,Type)
values(newid(),'Test Document ' + cast(@i as varchar(4)),'Test Title ' + cast(@i as varchar(4)),@t)
set @i = @i - 1;
end

select top 2 * from tbl_pdf_documents
select top 2* from tbl_word_documents



















image



And the Query Plan is



image



Hiç yorum yok: