x

Problems on filegroup file sizes

consider FG_january_2011 file group contains 100000 records. and FG_january_2012 file group contains 2000 records.

update Table_Name set date=dateadd(yyyy,+1,date) where Year(date)=2011

if i execute this query, what will happen.?? my thought is FG_january_2011 file group 100000 records will move to FG_january_2012 file group. and records too incremented as 102000.

Is it right things?? else what???
more ▼

asked Aug 30, 2012 at 08:55 AM in Default

sabari gravatar image

sabari
40 6 7 9

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first
If your table is partitioned to different filegroups, on the date-column, the data will move to pages in the FG_january_2012 filegroup when you change the date-column.
more ▼

answered Aug 30, 2012 at 12:33 PM

Magnus Ahlkvist gravatar image

Magnus Ahlkvist
16.4k 16 19 33

actually i'm the fresher for partitioning. i donno well about partitioning. if i partition the table., which will be partitioned, Table?? or table of pages??. if there is datas, these are where will be stored. i partitioned table month-wise. and 12 month ranges assigned to 12 file groups. i think data's will go to file groups?? rite?? if it is wrong, clear me... plz
Aug 30, 2012 at 12:54 PM sabari
The data goes into the file groups, but it's stored on pages there, not as indvidual rows. That's why you need to understand the difference.
Aug 30, 2012 at 12:56 PM Grant Fritchey ♦♦

Please share create scripts for your partition scheme, partition function and table.

In short: You create a partition function and a partition scheme for the function. The partition scheme partitions your data to different filegroups (or to the same filegroup which might also make a lot of sense if you're not putting the partitions on different disks anyway). When creating a table, you create it on a partition scheme rather than on a filegroup, and when doing so, you specify which column to partition the table over. When you have partitioned your table, your indexes will automagically be created on the partition scheme (meaning the index will also be partitioned), unless you specify to place it on its own filegroup. If you already have a table with data that you want to partition, you can create a clustered index on your partition scheme (since the clustered index contains the data, all rows will be partitioned when doing so).

For further reading on partitioning in SQL Server 2005, read Kimberly Tripp's article on MSDN, "Partitioned Tables and Indexes in SQL Server 2005": http://msdn.microsoft.com/en-us/library/ms345146(v=sql.90).aspx
Aug 30, 2012 at 01:03 PM Magnus Ahlkvist
You already allocated the space for the storage, so it doesn't have to allocate more.
Aug 30, 2012 at 01:15 PM Grant Fritchey ♦♦

Glad to hear it helped.

Please do me a favor, while working with us on this site, respond to comments & answers by clicking on the Add Comment button, not typing into the Your Answer box. That's for answers, not comments.
Aug 30, 2012 at 01:50 PM Grant Fritchey ♦♦
(comments are locked)
10|1200 characters needed characters left
Filegroups don't really count storage based on rows of data. Instead, data is allocated by pages. You assigned tables to particular filegroups and as data is added to the tables, when more space is needed, pages are consumed. The rows are stored on pages based on your default free space options and the size of the data in the rows.
more ▼

answered Aug 30, 2012 at 10:55 AM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
98.8k 19 21 74

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1945

asked: Aug 30, 2012 at 08:55 AM

Seen: 755 times

Last Updated: Aug 30, 2012 at 01:50 PM