SET ANSI_PADDING ON
GO
CREATE TABLE Test1
(Age INT CONSTRAINT CK_Age1 CHECK(Age BETWEEN 1 AND 50)
,Name VARCHAR(50)
,CONSTRAINT PK_Test1 PRIMARY KEY CLUSTERED (Age)
)
GO
CREATE TABLE Test2
(Age INT CONSTRAINT CK_Age2 CHECK(Age BETWEEN 51 AND 100)
,Name VARCHAR(50)
,CONSTRAINT PK_Test2 PRIMARY KEY CLUSTERED (Age)
)
GO
CREATE VIEW Test
AS
SELECT * FROM Test1
UNION ALL
SELECT * FROM Test2
GO
INSERT Test1 VALUES(37,'Jerry')
INSERT Test1 VALUES(48,'Bill')
INSERT Test1 VALUES(22,'Sue')
INSERT Test1 VALUES(11,'Angie')
INSERT Test1 VALUES(15,'Kim')
INSERT Test1 VALUES(32,'Steve')
INSERT Test2 VALUES(82,'Bertha')
INSERT Test2 VALUES(59,'Ethel')
INSERT Test2 VALUES(99,'Rudolph')
INSERT Test2 VALUES(73,'Zelda')
INSERT Test2 VALUES(68,'Britney')
INSERT Test2 VALUES(51,'Rob')
INSERT Test2 VALUES(88,'Jane')
GO

SELECT *
FROM Test
WHERE Age = 32

SELECT *
FROM Test
WHERE Age > 60

SELECT *
FROM Test
WHERE Age > CAST(60 AS INT)

DECLARE @Age INT
SET @Age = 88

SELECT *
FROM Test
WHERE Age = @Age

quote:

---

DECLARE @Age INT
SET @Age = 88

SELECT *
FROM Test
WHERE Age = @Age

The plan shows it hitting both Test1 and Test2. A previous thread on this subject states the reason being the compiler knows the value of @Age could change and therefore the plan has to cover the whole possible range. Ok, I understand. But my question then is, what the heck is the use of partitioned views? How many people run their queries with literal constants instead of parameters/variables on the partitioned column? Unless you set it up as dynamic SQL, I guess. But that's a royal pain in the butt, considering the hundreds of sprocs we already have built hitting these tables.

---

quote:

---

Originally posted by mr_mist

I found that the constraints had to be re-run after data loads for them to be effective, and you'd need to use the WITH CHECK option.

---