Does SQL Server move records between partitions when updating the partition key column?

If I run an UPDATE query on a table which is partitioned by the column I am updating - will the records be moved to another partition?

ie. I have a table where Historical bit column marks whether a particular record should go to Partition1 (=0) or Partition2 (=1). Now, I update a record in that table and change the Historical column value from 0 to 1. What happens with that record?

It will move to the other partition.

Thanks,|||Thank you. That's the kind of behavior I was looking for.

Does a database need contiguous space?

Hello,
We had a problem that we're trying to figure out. We have a database that's
about 3.5GB in size on a partition with about 10GB of free space. The
database appeared normal, however when the client tried to write data to it
we'd receive a write error message.
We recreated the database on a different partition with 40GB free and it
works fine now. However we're still trying to figure out why the original
database failed and there comes the question-does SQL need contiguous free
space to work? The drive may be fairly fragmented and while there is ~10GB
free there may not be much contiguous space.
Everything else with SQL appears normal.
Thanks!Although a DB doesn't "need" contiguous space, it generally performs better
if it does have it.
What specifically is the error you get? Does the SQL Server service account
have write permission on that partition?
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Mike Chiodo" <MikeChiodo@.discussions.microsoft.com> wrote in message
news:B860E70E-2CC7-4644-A624-C21EB0373BCF@.microsoft.com...
Hello,
We had a problem that we're trying to figure out. We have a database that's
about 3.5GB in size on a partition with about 10GB of free space. The
database appeared normal, however when the client tried to write data to it
we'd receive a write error message.
We recreated the database on a different partition with 40GB free and it
works fine now. However we're still trying to figure out why the original
database failed and there comes the question-does SQL need contiguous free
space to work? The drive may be fairly fragmented and while there is ~10GB
free there may not be much contiguous space.
Everything else with SQL appears normal.
Thanks!|||Yes, the SQL server account has permission to write to that partition. As
far as the error message-it was an error generated by the program not a SQL
error.
I suspect the database was simply corrupt but don't know for certain.
Thanks for your answer-it helped clear up a piece of the puzzle.
Mike
"Tom Moreau" wrote:
> Although a DB doesn't "need" contiguous space, it generally performs better
> if it does have it.
> What specifically is the error you get? Does the SQL Server service account
> have write permission on that partition?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> ..
> "Mike Chiodo" <MikeChiodo@.discussions.microsoft.com> wrote in message
> news:B860E70E-2CC7-4644-A624-C21EB0373BCF@.microsoft.com...
> Hello,
> We had a problem that we're trying to figure out. We have a database that's
> about 3.5GB in size on a partition with about 10GB of free space. The
> database appeared normal, however when the client tried to write data to it
> we'd receive a write error message.
> We recreated the database on a different partition with 40GB free and it
> works fine now. However we're still trying to figure out why the original
> database failed and there comes the question-does SQL need contiguous free
> space to work? The drive may be fairly fragmented and while there is ~10GB
> free there may not be much contiguous space.
> Everything else with SQL appears normal.
> Thanks!
>