February 3, 2010 at 8:38 am
The BOL:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/5b21c53a-b4f4-4988-89a2-801f512126e4.htm
Partitioned Tables
In addition to performing SWITCH operations that involve partitioned tables, ALTER TABLE can be used to change the state of a partitioned table's columns, constraints, and triggers just like it is used for nonpartitioned tables. However, this statement cannot be used to change the way the table itself is partitioned. To repartition a partitioned table, use ALTER PARTITION SCHEME and ALTER PARTITION FUNCTION. Additionally, you cannot change the data type of a column of a partitioned table.
drop table test
go
create partition function fnTest ( int) as range right for values ( 10)
go
create partition scheme scTest as Partition fnTest to (Test1,Test2)
go
create table test
(
one int ,
two int,
three varchar(20)
) on scTest(one)
go
insert into test
select 1,1,'ABC'
union all
select 11,11,'ABC1'
go
select * from sys.partitions where object_id = object_id('Test')
go
alter table test alter column two varchar(20)
go
sp_help test
alter table test alter column two int
go
sp_help test
go
select * from test
The test worked with no difficulties has any one found issues with the mention of alter datatye in sqlserver 2005 sp2
Kind Regards
Vinay
Regards
Vinay
February 12, 2010 at 6:44 am
I think the last sentence pertains to the partitioning column.
If I issue this command
alter table test alter column one bigint
I get this:
[font="Courier New"]Msg 5074, Level 16, State 1, Line 1
The object 'test' is dependent on column 'one'.
Msg 4922, Level 16, State 9, Line 1
ALTER TABLE ALTER COLUMN one failed because one or more objects access this column.[/font]
...and your only reply is slàinte mhath
February 12, 2010 at 12:41 pm
Yes Thats right, That is not the complete story, Just saw on the internet that the Table Which is Partitioned should never be altered, there is the datapage corruption issue, WRT to it will shortly forward the link, in a different message, but if you [Bing/Google]it you will find out that there is a datapage corruption issue with respect to the Alter column on a Partitioned Table.
I know that you cannot alter the Partitioned Column. or its Datatype as the function relies on a datatype which is of the same datatype as the column a for example.
That is next to impossible, to alter the partionColumn, The only way out with that approach would be to rebuild your table with a new partition scheme and transfer the data "ALTER TAble on Partitioned Column" will not work.
Has any body found datapage corruption issue when you do a alter table on a partitoned Table.
Thank you.
Regards
Vinay
February 21, 2010 at 9:55 am
bhushanvinay (2/12/2010)
Yes Thats right, That is not the complete story, Just saw on the internet that the Table Which is Partitioned should never be altered, there is the datapage corruption issue, WRT to it will shortly forward the link, in a different message, but if you [Bing/Google]it you will find out that there is a datapage corruption issue with respect to the Alter column on a Partitioned Table.I know that you cannot alter the Partitioned Column. or its Datatype as the function relies on a datatype which is of the same datatype as the column a for example.
That is next to impossible, to alter the partionColumn, The only way out with that approach would be to rebuild your table with a new partition scheme and transfer the data "ALTER TAble on Partitioned Column" will not work.
Has any body found datapage corruption issue when you do a alter table on a partitoned Table.
We are still waiting for your link concerning the corruption issue :laugh:
I think you originally misunderstood the Books Online entry, since everything works 'as advertised' and I know of no corruption issue.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
February 21, 2010 at 11:41 am
Sorry for the Very Very Late Reply Paul.
Here we go.
https://connect.microsoft.com/SQLServer/feedback/details/531260/datapage-difference-in-2005-vs-2008
it has all the details on what i have given the SqlServer guys.
I have also attached the test scripts what i use on SqlServer 2005 SP2 and SqlServer 2008
both are different in the output.
Z=NULL and Z=DATA is what cought my eye.
Regards
Vinay
February 21, 2010 at 11:41 am
bhushanvinay (2/21/2010)
Sorry for the Very Very Late Reply Paul.Here we go.
https://connect.microsoft.com/SQLServer/feedback/details/531260/datapage-difference-in-2005-vs-2008
it has all the details on what i have given the SqlServer guys.
I have also attached the test scripts what i use on SqlServer 2005 SP2 and SqlServer 2008
both are different in the output.
Z=NULL and Z=DATA is what cought my eye.
One more thing to add This is not concerned to Partitioning but just with the tabe itself.
Regards
Vinay
February 21, 2010 at 8:16 pm
bhushanvinay (2/21/2010)
https://connect.microsoft.com/SQLServer/feedback/details/531260/datapage-difference-in-2005-vs-2008
That seems to be a connect item trying to report a display anomaly in the undocumented and unsupported DBCC PAGE command.
It doesn't show data corruption at all...?!
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
February 21, 2010 at 11:12 pm
On second look, it appears that you are mis-interpreting the output of DBCC PAGE.
As an optimization, when SQL Server changes the type of a column, it often just adds a new column and marks the old column as not used.
This is one of the reasons I try to avoid modifying table schemas.
If you are looking at page output in detail, you might be looking at the no-longer-referenced data, rather than the new column.
If you care to publish a reproduction script here (the connect item has limited visibility outside Microsoft and you) I would be happy to analyze it for you.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
February 22, 2010 at 7:03 am
Thans for the help with the "Alter Huge Table" It was sweet of you to drop a complete test script,
I will do the output for 2008 today night. but here is 2005 sp2 output.
Please find the same. i would be realy helpfull to know why it happens differently in 2008 compared to 2005.
And also i would realy like to locate where is 200.80 now gone.
Regards
Vinay
February 22, 2010 at 7:44 am
DBCC PAGE output after running the ALTER TABLE command:
Slot 0 Column 1 Offset 0x4 Length 4 Length (physical) 4
i = 1
Slot 0 Column 2 Offset 0x19 Length 8 Length (physical) 8
y = 200.00
Slot 0 Column 67108865 Offset 0x8 Length 0 Length (physical) 10
DROPPED = NULL
Slot 0 Column 3 Offset 0x8 Length 5 Length (physical) 5
z = 200.08
Column 2 is the CHAR(10) version of z - dropped.
Column 3 is the DECIMAL version of z.
Test ran on SQL Server 2008 10.0.2757
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
February 22, 2010 at 2:03 pm
Yes Paul this is what i was after,
2008 test page copied from your output.
Slot 0 Column 3 Offset 0x8 Length 5 Length (physical) 5
z = 200.08
2005 test dbcc page shows the following Where is the data?
DROPPED = È
z = [NULL]
you should also notice one more thing that the Column order in SqlServer 2005 is 0 based index and 2008 is 1 based index although this is not my question, In Sql Server 2005 where is the data gone?
Regards
Vinay
February 22, 2010 at 10:20 pm
I only have SQL Server 2005 SP3 CU7 version 9.0.4273 installed, I'll test it on that later.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
February 22, 2010 at 11:07 pm
Confirmed on the 2005 installation I referenced before. BUT this is just a decoding/display problem in DBCC PAGE with type 3 output - the data is there in the page record data, and appears in queries of course. Microsoft don't support or document DBCC PAGE, so you're just going to have to live with it, I'm afraid 😉
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 2, 2014 at 8:21 pm
So the verdict is no data corruption or significant performance issue?
I changed the data type from int to bigint and from image to varbinary(max) on a small partitioning table, it works, can I issue the altering column on a large partitioning table? I am working on sql server 2012 enterprise edition.
Thanks.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply