Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

query from a specific partition

Status
Not open for further replies.

mflancour

MIS
Apr 23, 2002
379
US
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)
 
What is the sql for querying from a specific partion of a table?
 
hahah, figures that you'd answer this one too :p

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

 
yes, here is a whole script you can run

Code:
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:
 
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.
 
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.
 
take a look at sys.partition_functions, sys.partition_parameters and sys.partition_range_values

example
Code:
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:
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top