Create partitions

Question

Create partitions

sql_novice_2007

Hall of Fame

Points: 3307
More actions
December 13, 2011 at 7:47 am

#249600

Hi,
I need to create partitions for a table with 6 million records.
I am going to create partition each with one million records.
How can I change my SP to query these partitions?
Thanks.

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

okbangas SSChampion Points: 11773 More actions · Answer 1

For the partitioning to work as intended, you'll have to ensure that the partitioning key is included in the where clause of your queries.

Ole Kristian Velstadbråten Bangås - Virinco - Facebook - Twitter

Concatenating Row Values in Transact-SQL[/url]

sql_novice_2007 Hall of Fame Points: 3307 More actions · Answer 2

And where can I see the script of the created partitions.

When I queried SELECT * FROM sys.partitions it shows 6 partitions on this table. But where I can see the script of these partitions and how can I edit these partitions.

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 3

okbangas (12/13/2011)
For the partitioning to work as intended, you'll have to ensure that the partitioning key is included in the where clause of your queries.

I would recommend it otherwise. The column we used most in our queries that is the most suited candidate for Partition Key (there are exceptions as well).

@OP: Another thought, we just don’t partition a table because it has N rows. There are some good reasons behind this option. Apart from 6M rows do you have another though in your mind?

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 4

sql_novice_2007 (12/13/2011)
And where can I see the script of the created partitions.
When I queried SELECT * FROM sys.partitions it shows 6 partitions on this table. But where I can see the script of these partitions and how can I edit these partitions.

Excellent. So you created the partitions :-). I believe we can’t UNDO this operation. You might have to create another table & load it with data. Hope you are not playing in PROD environment.

You can optionally edit Partition Functions / Scheme but populating a new table is good approach IMO.

sql_novice_2007 Hall of Fame Points: 3307 More actions · Answer 5

No,it's not on production.

The reason why I am asking this is because after testing I need to deploy these partitions on production.

I haven't created these partitions on staging.

So I am wondering if I can generate a script for the partitions on staging?

Thanks.

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 6

sql_novice_2007 (12/13/2011)
No,it's not on production.
The reason why I am asking this is because after testing I need to deploy these partitions on production.
I haven't created these partitions on staging.
So I am wondering if I can generate a script for the partitions on staging?
Thanks.

I guess you created the partitions with SSMS GUI. In the wizard there is an option to script the partition definitions.

http://msdn.microsoft.com/en-us/library/cc280640.aspx

http://msdn.microsoft.com/en-us/library/cc280599.aspx

sql_novice_2007 Hall of Fame Points: 3307 More actions · Answer 7

When I did the following

In SSMS Object Explorer, selected database, right-clicked on the table and right clicked Storage and then I see only two options enables.

Create partition and manage compression.

I dont see an option to generate the script.

Is this the correct way?

Thanks.

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 8

sql_novice_2007 (12/13/2011)
When I did the following
In SSMS Object Explorer, selected database, right-clicked on the table and right clicked Storage and then I see only two options enables.
Create partition and manage compression.
I dont see an option to generate the script.
Is this the correct way?
Thanks.

Create partitions option and follow the wizard steps. At last script it all.

sql_novice_2007 Hall of Fame Points: 3307 More actions · Answer 9

I need to create a partion on user table for username column.

Can I write the partition function like this?

CREATE PARTITION FUNCTION [pf_Product_Partition](VARCHAR(10)) AS RANGE LEFT

FOR VALUES (N’g’, N’l’, N'r',N’s’)

Thanks.

sql_novice_2007 Hall of Fame Points: 3307 More actions · Answer 10

Do we definitely need a secondary file group to create partition?