Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SELECTING on a Partitioned View - Different WHERE clauses Expand / Collapse
Author
Message
Posted Thursday, April 10, 2014 5:25 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 10:15 AM
Points: 575, Visits: 3,359
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.
Post #1560369
Posted Thursday, April 10, 2014 6:23 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: 2 days ago @ 3:37 PM
Points: 20, Visits: 44
You don't have any constraints on the CustomerName column, so SQL doesn't know where to look specifically so it has to look everywhere.

Also, this is really fake partitioning. Have you tried proper partitioning and building appropriate indices?
Post #1560387
Posted Friday, April 11, 2014 1:36 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 10:15 AM
Points: 575, Visits: 3,359
i see. thought so, but was still hoping!
do you have any good articles you can point me to, about proper partitioning?
Post #1560739
Posted Friday, April 11, 2014 6:00 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 12:24 PM
Points: 284, Visits: 620
Lots of good resources, starting MSDN:

[url=http://msdn.microsoft.com/en-us/library/ms188730.aspx][/url]

Brent Ozar:

http://www.brentozar.com/sql/table-partitioning-resources/

MS SQL Tips:

http://www.mssqltips.com/sqlservertip/2888/how-to-partition-an-existing-sql-server-table/

Mark Chapple:

http://databases.about.com/od/sqlserver/a/partitioning.htm

MSDN Blogs:

http://blogs.msdn.com/b/manisblog/archive/2009/01/18/easy-table-partitions-with-sql-server-2008.aspx

...and many, many more!
Post #1560840
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse