Determine the partition number

Question

Hi All,

I have created 6 partitions based on a key. My keys are from 100,101.. 105 and a separate partition is created for each key.

Can anyone tell me how to determine which partition(partition number) is created for each of the keys.

Following is the result

partition_id    object_id index_id partition_number hobt_id                 rows

72057594068860928 1474104292 1            1          72057594068860928         0          

72057594069123072 1474104292 1            2          72057594069123072         10575       

72057594069319680 1474104292 1            3          72057594069319680  0           

72057594069385216 1474104292 1            4          72057594069385216  5290        

72057594069450752 1474104292 1            5          72057594069450752  0       

72057594070237184 1474104292 1            6          72057594070237184         0

Regards,

Kailash

Answer 1

If you don't know the partition function name used for a table, you can run this query:

select pf.name 
  from sys.indexes ix
  join sys.partition_schemes ps
    on ix.data_space_id = ps.data_space_id
  join sys.partition_functions pf
    on ps.function_id = pf.function_id
 where object_id = object_id('PartitionTable')<br/>

Then, with the partition function name, you can do this:

SELECT $PARTITION.partitionfunctionname (100);

This will tell you which partition number the value 100 will go into.

---

Phil Brammer | http://www.ssistalk.com | Twitter: http://twitter.com/PhilBrammer

Answer 2

Thanks Phil !!! This is what I was looking for.

Regards,

Kailash

Answer 3

Hi Phil,

Another question... !! I have created a 10 partitions(partition no. 1,2.. 10) for a table based on a key. How do I drop a partitions(say partition number 7 and 8) without losing the data or merging it to another partition. Can I group all the dropped partition data into a single partition ??

Regards,

Kailash

Answer 4

Sure you can do that.  See the ALTER PARTITION FUNCTION BOL topic here, specifically the MERGE option: http://msdn.microsoft.com/en-us/library/ms186307.aspx

---

Phil Brammer | http://www.ssistalk.com | Twitter: http://twitter.com/PhilBrammer

Answer 5

Hi Phil,

I am creating the table table partitions with unique non-clustered indexes  and on the same file group. When I create a table on the partition scheme and create the non-clustered index on the partition scheme, 2 partitions are created with index_id 0 and 2(before loading data).

But when I create the Clustered index on the table, only 1 partition is created with the index_id 1.

Can you please tell me how to create the partition with unique non-clustered indexes ??

Regards,

Kailash

Answer 6

Hi Phil,

I am creating the table table partitions with unique non-clustered indexes  and on the same file group. When I create a table on the partition scheme and create the non-clustered index on the partition scheme, 2 partitions are created with index_id 0 and 2(before loading data).

But when I create the Clustered index on the table, only 1 partition is created with the index_id 1.

Can you please tell me how to create the partition with unique non-clustered indexes ??

Regards,

Kailash

Kailash,
When you create a table that DOES NOT have a clustered index, that table is a heap.  The data for the table is stored under index_id 0.  If you create a table that has a clustered index, the data for that table is stored as the clustered index, or index_id 1.

The index_id 2 is the non-clustered index itself.

---

Phil Brammer | http://www.ssistalk.com | Twitter: http://twitter.com/PhilBrammer

Answer 7

Thanks Phil. !!! I got to know it after some research. :)

Answer 8

Hi Phil,

I hav another question :) What is the maximun number of partitions that can be created on a table ??

Is there a limit ??

Regards,

Kailash

Answer 9

Hi Phil,

I hav another question :) What is the maximun number of partitions that can be created on a table ??

Is there a limit ??

Regards,

Kailash

Kailash,
A couple of things - 1) please start a new thread for your questions.  2) Please use Bing or Google for these types of questions first, as you should be able to find plenty of resources to answer your questions.  Books On-Line has this information.

To answer your question - 1,000 partitions is the max for a partitioned table.  http://msdn.microsoft.com/en-us/library/ms143432.aspx

---

Phil Brammer | http://www.ssistalk.com | Twitter: http://twitter.com/PhilBrammer