SQL Server Performance Forum – Threads Archive

partitioning the existing large table

Hi ,
I have a table which is having 130Million records(Not partitioned) based on every week. This is what i did ..
1. Created PF(Function)
2. Created PS(Schema) ON FILE GROUP ( consisits of 10 physical files)
3. Created CLUSTERED index on PS . When I did SelecT * From sys.partitions It is showing rows in each partition. I have indexid=1 for all partitions . that means is my index is not partioned ?
How do I achive the partitioned index on each of partition??

Indexid = 1 is clustered index, cluster index is nothing but the table… Microsoft SQL Server 9.0 Technical Articles
Partitioned Tables and Indexes in SQL Server 2005
http://msdn2.microsoft.com/en-us/library/ms345146.aspx#sql2k5parti_topic27
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

… 2. Created PS(Schema) ON FILE GROUP ( consisits of 10 physical files) … If you only have one filegroup, it will spread the data over the physical files within the filegroup. (cfr striping) If you want control over what data gets into a single file, you should create a number of filegroups (with at least one file) and alter your PS to route to its wanted filegroup. have al look athttp://www.sqlskills.com/resources/Whitepapers/Partitioning in SQL Server 2005 Beta II.htm (is the workdoc for the bol ref ! you can download the scripts overthere)

http://www.sqlskills.com/blogs/kimberly/PermaLink.aspx?guid=cf223632-c93b-4242-b0f2-af493e051266 &http://www.davidemauri.it/DasBlog/PermaLink,guid,c9d877ed-3c22-4ff9-87ce-3bf693ada233.aspx this blog too Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.

Thank you so much!

One more quick question. does IDentity columns gives any negative impact on partitions?

I don’t think so, as I haven’t come across any issues so far, for instance to partition a table, you must identify which column will be used for partitioning and the ranges assigned to each partition. In this case the values of an identity column can define partition ranges; values one through one million could reside on one partition, one million through two million on the next partition, and so forth. I can see the int based partition will perform better than non-number based column. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

SQL Server 2005 Books Online
Transferring Data Efficiently by Using Partition Switching
http://msdn2.microsoft.com/en-us/library/ms191160.aspx Performing partition switching can introduce duplicate values in IDENTITY columns of the target table, and gaps in the values of IDENTITY columns in the source table. Use DBCC CHECKIDENT to check the identity values of your tables and correct the values if you want. MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

AS always .. Thank you so much!
CAn’t we restrict insert the data into the partition which does not exist.
Eg: my partition Key is 200711 .. I haven’t created the partition for this. When I try to insert the record it is inserting into last partition. how can I restrict this.? In oracle it will not allow until you create that partition..

It is by default and when you haven’t created any partition it will see for available partition, BTW have you enabled AUTOGROW for that specified filegroup/http://msdn2.microsoft.com/en-us/library/ms345146.aspx fyi and further read. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

]]>