query from a specific partition

[mflancour]

I have a table that is separated into filegourps by quarter and partitioned by quarter using insertion date.

I want to run some tests on this table and the partitions so I need a way to query a specific partition. ie

select * from table1.partition1 where dateinserted = getdate()


(just an example)

 

[PWise]

So what is the question

 

[mflancour]

What is the sql for querying from a specific partion of a table?

 

[SQLDenis]

lookup $PARTITION in BOL

http://msdn2.microsoft.com/en-us/library/ms188071.aspx

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

 

[mflancour]

hahah, figures that you'd answer this one too

I've created 2 different queries based on the info from that link and I want to make sure I understand what they are giving me...
1. All information from all partions ordered by partition
SELECT $PARTITION.pf_insertion_date(date_inserted) AS Partition,
* FROM table1
ORDER BY Partition

2. All data from partition 1
SELECT * FROM db1.dbo.table1
WHERE $PARTITION.pf_insertion_date(date_inserted) = 1

 

[SQLDenis]

yes, here is a whole script you can run

CREATE PARTITION FUNCTION pf_Range (int)
AS RANGE RIGHT FOR VALUES(5,10,15,20,25)
GO

CREATE TABLE TestPartition (id int not null)
INSERT TestPartition VALUES(1)
INSERT TestPartition VALUES(2)
INSERT TestPartition VALUES(3)
INSERT TestPartition VALUES(10)
INSERT TestPartition VALUES(11)
INSERT TestPartition VALUES(17)
INSERT TestPartition VALUES(18)
INSERT TestPartition VALUES(19)
INSERT TestPartition VALUES(21)
INSERT TestPartition VALUES(25)
INSERT TestPartition VALUES(22)
INSERT TestPartition VALUES(23)
GO

CREATE PARTITION SCHEME ps_Range
AS PARTITION pf_Range
ALL TO ([PRIMARY])
GO

CREATE CLUSTERED INDEX IX_TestPartition_ID 
ON TestPartition(ID) ON ps_Range(ID)


SELECT $PARTITION.pf_Range(ID) AS Partition,
* FROM TestPartition
ORDER BY Partition


SELECT 1,* FROM TestPartition
WHERE $PARTITION.pf_Range(ID) = 1 

SELECT 2,* FROM TestPartition
WHERE $PARTITION.pf_Range(ID) = 3

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

 

[mflancour]

Is there a way to get a partition name associated to the partition number....example I know the name of the partition I want to backup but I dont know it's partition number.

 

[mflancour]

better yet, do you know a site that will give me more in depth info on the different querying options pertaining to partitions in 2005? Sort of like a list of possible commands or maybe what table contains the metadata for partitions. Not even sure how I would use that, but as I'm sure you can tell I'm just starting to explore this and am not sure how to ask a more targeted question.

Or, in your experience, what are some handy things when administrating partitioned tables. any scripts you know of to make backups, move partitions to "read only warehouses", etc.

sorry, I realize my questions are getting a bit excessive, but I thank you for any feedback you can provide.

 

[SQLDenis]

take a look at sys.partition_functions, sys.partition_parameters and sys.partition_range_values

example

select name,boundary_id 
from sys.partition_functions pf 
join sys.partition_range_values  prv on pf.function_id =prv.function_id

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/