Partitioned View.... Confused! (code provided)

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 2000 Forums

SQL Server Development (2000)

Partitioned View.... Confused! (code provided)

Author

Topic

uberman
Posting Yak Master

159 Posts

Posted - 2007-01-31 : 07:18:52

Sql Server 2000

I am trying to split out a large table and use a partioned view...

I have reduced the issue to the following example code

IF EXISTS(SELECT name 
	  FROM 	 sysobjects 
	  WHERE  name = N'TT1' 
	  AND 	 type = 'U')
    DROP TABLE TT1
GO

CREATE TABLE TT1 (
	uid INTEGER not null 
	,uidstatus   INT not null 
		CHECK (uidstatus = 1)
	,CONSTRAINT XX1 PRIMARY KEY(uid, uidstatus)
--	,CONSTRAINT XX1 PRIMARY KEY(uid)
)
GO

IF EXISTS(SELECT name 
	  FROM 	 sysobjects 
	  WHERE  name = N'TT2' 
	  AND 	 type = 'U')
    DROP TABLE TT2
GO

CREATE TABLE TT2 (
	uid INTEGER not null 
	,uidstatus   INT not null 
		CHECK (uidstatus  =2)
	,CONSTRAINT XX2 PRIMARY KEY(uid, uidstatus)
--	,CONSTRAINT XX2 PRIMARY KEY(uid)
)
GO

IF EXISTS (SELECT TABLE_NAME 
	   FROM   INFORMATION_SCHEMA.VIEWS 
	   WHERE  TABLE_NAME = N'vTT')
    DROP VIEW vTT
GO

CREATE VIEW vTT
AS 
SELECT
	uid
	,uidstatus
FROM
	TT1
UNION ALL
SELECT
	uid
	,uidstatus
FROM
	TT2

GO

SET STATISTICS IO ON
SET STATISTICS PROFILE ON


select * from vtt where uidstatus = 1 and uid = 1 --uses just table TT1 :-)

select * from vtt where uidstatus = 2 and uid = 1 --uses just table TT2 :-)
 
select * from vtt where uidstatus = 1 --uses both TT1 and TT2 :-(

The first select statement only uses table TT1

The second select statement used both TT1 and TT2, but I really just want it to use TT1 as TT1 will have the active status (e.g. when SELECT'ing a list of items where the status is constrained to 1, only use TT1, TT2 will not be touched)

I hope this makes sense and that what I want is possible

nr
SQLTeam MVY

12543 Posts

Posted - 2007-01-31 : 08:18:32

Can you change the order of the columns in the PK?

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

uberman
Posting Yak Master

159 Posts

Posted - 2007-01-31 : 08:47:04

If you mean

CONSTRAINT XX1 PRIMARY KEY(uidstatus, uid)

Then I tried that and have the same result...?

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-01-31 : 16:59:53

Not sure why that happens, but here's a possible fix - notice in the plan for the query that uses both tables that there is a convert operator being used. You can force that conversion by using a cast, so run this and you'll see that it hits only one table

select * from vtt where uidstatus = cast(1 as int) --uses only TT1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources