April 13, 2010 at 9:38 am
Hi,
I am newbie to SQL server and I would appreciate if you could assist me with the following:
I have a question regarding dynamic partition.
Let say I have the following:
I need to store sentences from books in the following table:
book_id (int) - name of the book
page_id (int) - the page number
sentence_id (int) - some temporary id (for example, the first sentence in each page will get id 1 and so on)
sentence (varchar)
primary key(book_id, page_id, sentence_id)As you can see the table can be divied to different partition based on the book_id
How do I create a new partiton dynamically in the table for each book_id.
I would apprecaite if you can also give me a code sample on how to do that.
Thanks,
Gady
April 13, 2010 at 9:52 am
I do not see any need for table partitioning... a 500 pages book with 25 sentences per page will be stored in 12,500 rows - ten thousand books will be stored in about 12.5M rows... that's a pretty small number of rows and I do not think there is any reason to have ten thousand partitions.
Any particular reason to look for partitioning?
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.April 13, 2010 at 9:56 am
This is a duplicate post. Let's continue here in this thread.
For reference, the other thread is here.
I'm in agreeance with Paul here, why do you think you need partitions?
April 13, 2010 at 9:56 am
I only gave the book scenario as an example, in my real problem I will have some thing like million records per one entity.
So, is there a way of creating dynamic partitons per each entity's id?
April 13, 2010 at 10:04 am
I just want to clarify my question - I am intersted to know if it is possible to create partitons on the fly.
I am facing an issue in which I need to divide my table to partition per specifc entity's ID. I just gave the book scenario as an example in order to make my question a bit clearer
April 13, 2010 at 10:16 am
gady.male (4/13/2010)...I just gave the book scenario as an example in order to make my question a bit clearer
it didn't work as expected, huh? 😀
Answering your question it all depends of how you define "on the fly".
If you mean a partition getting created just by new data being inserted into the target partitioned table - as far as I know SQL Server has nothing (*) like Oracle 11g "interval partitioning" which does exactly that, meaning when you insert a row in a "interval partitioned table" if the partition partition-key is pointing at does not exists then rdbms creates it for you "on the fly".
(*) Please folks correct me if I'm wrong
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.April 13, 2010 at 10:29 am
It is possible, but it could get ugly in this scenario. The way SQL Server partitioning works, you would have to create procedures that manage your partition function and schemes. You'd then have to tie those procedures into a trigger that fires on INSERT into the table in question. I designed something similar at my last job, but it was only expected that new partitions be created monthly.
Here's what I did in a nutshell:
1. Set up initial partitions (scheme, function, file/filegroups, tables).
2. Created a procedure to check partition scheme/function based on incoming value for partition key.
3. Created a procedure to create new partition in the event an existing partition does not exist to house the incomming partition key value. This proc had to check available storage areas (that we defined in a table that the proc would round-robin the filegroups on disk) for the next disk, create a filegroup on that disk, create a file in the filegroup, then alter the partition function/scheme. We also used check constraints on the table on the partitioning key column to ensure that only data that has a partition assigned to it can be input into the table so our procedure had to recreate those constraints as well.
This worked very well for what we needed it to work for, but we were partitioning by date so the function would only fire off when we needed a new month. I also did not want this to fire from an INSERT trigger so we set up a job that ran monthly to create the partitions for the next month's data.
Keep in mind that doing all of this dynamically was not easy to code and required a bunch of testing to ensure it worked correctly. If you mess this up, you'll end up rebuilding your table. I would avoid doing this 'on-the-fly' via a trigger and come up with some way to enforce a process that ran outisde of a transaction to build out the new partitions.
Again, you are a self-proclaimed SQL Server newbie so why do you think partitioning is the way to go? How many rows total do you forsee storing in this table? How will the table be used, queried, always by book id?
March 20, 2015 at 5:55 am
Hi,
Can you please send me the scripts that you tried for getting the monthwise dynamic partition in?.
Thanks in advance!.
Thanks,
Syam
March 20, 2015 at 5:57 am
Hello John Rowan,
Can you please send me the scripts that you have done for the below:?
Here's what I did in a nutshell:
1. Set up initial partitions (scheme, function, file/filegroups, tables).
2. Created a procedure to check partition scheme/function based on incoming value for partition key.
3. Created a procedure to create new partition in the event an existing partition does not exist to house the incomming partition key value.
Thanks,
Syam
March 20, 2015 at 6:21 am
Why are you considering partitioning?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply