SELECTING on a Partitioned View - Different WHERE clauses

Question

SELECTING on a Partitioned View - Different WHERE clauses

davidandrews13

SSCertifiable

Points: 6792
More actions
April 10, 2014 at 5:25 am

#306538

i am experimenting with Partitioned Views again.
CREATE TABLE test_1
(TestID INTEGER PRIMARY KEY
CHECK (TestID BETWEEN 1 AND 10),
CustomerName varchar(50))
CREATE TABLE test_2
(TestID INTEGER PRIMARY KEY
CHECK (TestID BETWEEN 11 AND 20),
CustomerName VARCHAR(50))
CREATE TABLE test_3
(TestID INTEGER PRIMARY KEY
CHECK (TestID BETWEEN 21 AND 30),
CustomerName VARCHAR(50))
CREATE VIEW test_view AS
SELECT * FROM [dbo].[test_1]
UNION ALL
SELECT * FROM [dbo].[test_2]
UNION ALL
SELECT * FROM [dbo].[test_3]
INSERT INTO test_view
SELECT 5,'bob'
INSERT INTO test_view
SELECT 15,'john'
when i run the following statements, i can see the Execution Plan is correctly only hitting the Tables that it needs to
SELECT * FROM test_view WHERE testid in (5)
SELECT * FROM test_view WHERE testid in (15)
SELECT * FROM test_view WHERE testid in (5,15)
but what if my SELECT statement was
SELECT * FROM test_view WHERE CustomerName = 'john'
At the moment, it will check all tables, but i would really like it to know that it only needs to check 'test_2'.
could i have it allow me to filter by any Single Column and still have the Partitioned View know that it only needs to access specific tables, instead of all of them.
the desired end product is that, using the CREATE statements above, both the following statements will know that it only needs to look at 'test_2'
SELECT * FROM test_view WHERE testid in (15)
SELECT * FROM test_view WHERE CustomerName = 'john'
as a final point, the Tables in this example WILL be logically split by TestID.
You could have CustomerName begining with, for example, 'A' in any one of those tables.
i cannot find anything that suggests that this is possible.