February 8, 2010 at 2:55 am
Hi Everybody,
In 100000 [ one lac] records existing table,I partitioned 4 partiotion. Each partition has 25000 records
Partition1--- 1 to 25000
Partition2--- 25001 to 50000
Partition3--- 50001 to 75000
Partition4--- 75001 to 100000
1. Now i insert a new record in that table. Which partition take the new record.
2. How to insert the new records in existing partition table.
3. How to retrive the data,it means based on partition key or index key.
February 8, 2010 at 3:26 am
The questions that you ask, show that you didn’t understand how partitioned table works. Before you create the partitioned table you have to create partitioning function and partitioning schema. The partitioning function creates the partitioning boundaries. The partitioning schema defines which file group will be used by each partition. When you create the table, you create it on the partitioning schema and you specify which column in the table will be used to base the partitioning on. When you insert data into the table, you insert it the same way that you insert data to any other table. According to the value in the column that the partition is based on, it should get to the correct partition. I suggest that you’ll read a bit more about partitioned tables.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
February 8, 2010 at 9:45 pm
Ok Adi,
but new records means in my existing table 100000 records is there,in that i has divided into 4 partitiones. Now I insert 100001 record into the table, this new record where it is insert in the table.
February 8, 2010 at 10:17 pm
As I already wrote before – it depends on the partitioning function and the value that you inserted into the column that the partitioning is based on. Without knowing those factors no one can know on which partition the new record will be inserted.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
February 8, 2010 at 10:45 pm
That is ok Adi.
First i alter my existing database for adding the files in my file group. here i added 4 files i.e FG1,FG2,FG3,FG4
now i create partition function
Actually I partitioned based on Left Range values. I think Range partition working in below way
Here First Range is Left -Ve to 25000 this file name
Second Partition Is 25001 to 50000
Third Partition Is 50001 to 75000
4Th one is 75001 to 100000
now I create Partition Scheme
I think Partition steps is over here. Now My doubt is
we insert new record i.e 100001. This record where its stored.
1.Range partition I think it will allow upto I mentioned integer not more than that...........
February 8, 2010 at 11:30 pm
My guess is that you created this partitioning function:
CREATE PARTITION FUNCTION WhatEverNameYouSelected (int)
AS RANGE LEFT FOR VALUES (25000, 50000, 75000, 100000)
This function doesn’t create 4 partitions. It creates 5 partitions. The last partition is for all numbers that are greater then 100000 (just as the first partition is for all number that are less or equal to 25000). If the value in the column that the partitioning is based on is greater then 100000, then it will go into the fifth partition. You can also use the $partition function to check it (you can read about it in BOL).
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
February 9, 2010 at 1:39 am
Adi that is ok .
Next record is kept on 5th partition, it means the fifth partition how many record are kept. its upto next 25000 or remaining new records are inserted in the lats partition. This is my doubt and also
we are create only 4 files in that file group so which file has these 5 partion.
February 9, 2010 at 11:52 am
subbarayudu.net 37258 (2/9/2010)
Adi that is ok .Next record is kept on 5th partition, it means the fifth partition how many record are kept. its upto next 25000 or remaining new records are inserted in the lats partition. This is my doubt and also
we are create only 4 files in that file group so which file has these 5 partion.
The fifth partition will hold any records with value above 100000 in the partitioning column. I can’t tell where the last partition resides because I didn’t see the DDL for both partitioning function and partitioning schema.
In the first answer that I wrote in this thread, I suggested that you’ll read more about partitioned table. From your questions I can see, that you didn’t read more about it. Getting answers to your questions is one thing, but since you are using partitioned tables, you should read about it to get a better understanding about it so won’t have to rely on help that you get from strangers (like me) which no one can assure you there knowledge about the subject of your questions. I strongly recommend that you’ll look for a good article about partitioning and read it.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
February 10, 2010 at 3:28 am
Partition scheme always have one more File Group (say FG5 in this case for 4 partition values). Otherwise Partition Scheme creation would fail if extra file group is not mentioned.
Key points:
PF with n items creates n+1 partitions
Partiton Scheme : add one extra file group that is not allocated - "Next used".
To clarify about the record # 100001 , it should be placed in the last partition and it uses the NEXT USED file group ( This is smilar to the MAXVALUE in ORACLE). Hence , all the records values >100000 will be added into the last partition / PG.
Caution: This kind of data movement is not good when you want to Sliding window concepts in Partition.
Read more articles in this space...
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply