Poor Man's Partitioned Tables - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Poor Man's Partitioned Tables

Author

Topic

drsloat
Starting Member

45 Posts

Posted - 2009-04-23 : 15:09:19

I'm working on Sql 2005 Standard Edition.

I have set of tables with the data partitioned on an int column (SiteID) and a view (SubscriberDetail) that union all's these tables together.

The SiteID in each table for the view has a constraint. Since i have the constraint, if i select from the view and include the value for the partition column (siteID), the query plan will only scan 1 table from the view.

I was thinking that if i joined to the view from another table, and included the partition column this, query would also only scan 1 table. I can't seem to get this to work, however, unless my query explicity states the value for the partition column.

I'm trying to write a query like the one that follows that does not scan all the indexes for the view tables

select *
from subscriber s, subscriberdetail d
where s.subscriberid = d.subscriberID
and s.siteid = d.siteid
and s.Subscriberid = 2

CREATE TABLE [dbo].[Subscriber](
[SubscriberID] [int] NOT NULL,
[SiteID] [int] NOT NULL,
[Name] [varchar](50) NULL,
CONSTRAINT [PK_Subscriber] PRIMARY KEY CLUSTERED
(
[SubscriberID] ASC
) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[SubscriberDetail_100](
[SubscriberID] [int] NOT NULL,
[SiteID] [int] NOT NULL,
[Detail] [int] NULL,
CONSTRAINT [PK_SubscriberDetail_100] PRIMARY KEY CLUSTERED
(
[SubscriberID] ASC
) ON [PRIMARY]
) ON [PRIMARY]

ALTER TABLE [dbo].[SubscriberDetail_100] WITH CHECK ADD CONSTRAINT [CK_SubscriberDetail_100] CHECK (([SiteID]=(100)))

ALTER TABLE [dbo].[SubscriberDetail_100] CHECK CONSTRAINT [CK_SubscriberDetail_100]

CREATE TABLE [dbo].[SubscriberDetail_200](
[SubscriberID] [int] NOT NULL,
[SiteID] [int] NOT NULL,
[Detail] [int] NULL,
CONSTRAINT [PK_SubscriberDetail_200] PRIMARY KEY CLUSTERED
(
[SubscriberID] ASC
) ON [PRIMARY]
) ON [PRIMARY]

ALTER TABLE [dbo].[SubscriberDetail_200] WITH CHECK ADD CONSTRAINT [CK_SubscriberDetail_200] CHECK (([SiteID]=(200)))

ALTER TABLE [dbo].[SubscriberDetail_200] CHECK CONSTRAINT [CK_SubscriberDetail_200]

Go

CREATE VIEW [dbo].[SubscriberDetail]
AS
SELECT SubscriberID, SiteID, Detail
FROM dbo.SubscriberDetail_100
UNION ALL
SELECT SubscriberID, SiteID, Detail
FROM dbo.SubscriberDetail_200

Go

insert into Subscriber
Select 1, 100, 'test1'

insert into Subscriber
Select 2, 100, 'test2'

insert into Subscriber
select 3, 200, 'test3'

insert into SubscriberDetail_100
select 1, 100, 1

insert into SubscriberDetail_100
select 2, 100, 2

insert into subscriberdetail_200
select 3, 200, 3

select * from subscriber
select * from subscriberdetail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-23 : 15:36:22

See http://weblogs.sqlteam.com/peterl/archive/2007/09/24/Horizontal-partitioning.aspx
You can see the tables are included in the execution plan, but scan count is 0.

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources