SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Alter A Partitioned Table Column DataType.


Alter A Partitioned Table Column DataType.

Author
Message
bhushanvinay
bhushanvinay
SSC Eights!
SSC Eights! (933 reputation)SSC Eights! (933 reputation)SSC Eights! (933 reputation)SSC Eights! (933 reputation)SSC Eights! (933 reputation)SSC Eights! (933 reputation)SSC Eights! (933 reputation)SSC Eights! (933 reputation)

Group: General Forum Members
Points: 933 Visits: 196
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
Piotr.Rodak
Piotr.Rodak
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4196 Visits: 1761
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:
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.


...and your only reply is slàinte mhath
bhushanvinay
bhushanvinay
SSC Eights!
SSC Eights! (933 reputation)SSC Eights! (933 reputation)SSC Eights! (933 reputation)SSC Eights! (933 reputation)SSC Eights! (933 reputation)SSC Eights! (933 reputation)SSC Eights! (933 reputation)SSC Eights! (933 reputation)

Group: General Forum Members
Points: 933 Visits: 196
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
Paul White
Paul White
SSC Guru
SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)

Group: General Forum Members
Points: 54670 Visits: 11391
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
SQLblog.com
@SQL_Kiwi
bhushanvinay
bhushanvinay
SSC Eights!
SSC Eights! (933 reputation)SSC Eights! (933 reputation)SSC Eights! (933 reputation)SSC Eights! (933 reputation)SSC Eights! (933 reputation)SSC Eights! (933 reputation)SSC Eights! (933 reputation)SSC Eights! (933 reputation)

Group: General Forum Members
Points: 933 Visits: 196
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
bhushanvinay
bhushanvinay
SSC Eights!
SSC Eights! (933 reputation)SSC Eights! (933 reputation)SSC Eights! (933 reputation)SSC Eights! (933 reputation)SSC Eights! (933 reputation)SSC Eights! (933 reputation)SSC Eights! (933 reputation)SSC Eights! (933 reputation)

Group: General Forum Members
Points: 933 Visits: 196
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
Paul White
Paul White
SSC Guru
SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)

Group: General Forum Members
Points: 54670 Visits: 11391
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
SQLblog.com
@SQL_Kiwi
Paul White
Paul White
SSC Guru
SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)

Group: General Forum Members
Points: 54670 Visits: 11391
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
SQLblog.com
@SQL_Kiwi
bhushanvinay
bhushanvinay
SSC Eights!
SSC Eights! (933 reputation)SSC Eights! (933 reputation)SSC Eights! (933 reputation)SSC Eights! (933 reputation)SSC Eights! (933 reputation)SSC Eights! (933 reputation)SSC Eights! (933 reputation)SSC Eights! (933 reputation)

Group: General Forum Members
Points: 933 Visits: 196
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
Attachments
afterUpdate.txt (53 views, 2.00 KB)
beforeUpdate.txt (58 views, 2.00 KB)
Paul White
Paul White
SSC Guru
SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)

Group: General Forum Members
Points: 54670 Visits: 11391
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
SQLblog.com
@SQL_Kiwi
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search