Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Alter A Partitioned Table Column DataType. Expand / Collapse
Author
Message
Posted Wednesday, February 3, 2010 8:38 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, October 2, 2013 9:39 AM
Points: 329, Visits: 195
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
Post #858750
Posted Friday, February 12, 2010 6:44 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, May 10, 2012 9:49 AM
Points: 800, Visits: 1,759
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
Post #864587
Posted Friday, February 12, 2010 12:41 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, October 2, 2013 9:39 AM
Points: 329, Visits: 195
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
Post #864875
Posted Sunday, February 21, 2010 9:55 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:32 PM
Points: 11,194, Visits: 11,112
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

I think you originally misunderstood the Books Online entry, since everything works 'as advertised' and I know of no corruption issue.

Paul




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #869857
Posted Sunday, February 21, 2010 11:41 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, October 2, 2013 9:39 AM
Points: 329, Visits: 195
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
Post #869883
Posted Sunday, February 21, 2010 11:41 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, October 2, 2013 9:39 AM
Points: 329, Visits: 195
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
Post #869885
Posted Sunday, February 21, 2010 8:16 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:32 PM
Points: 11,194, Visits: 11,112
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #870015
Posted Sunday, February 21, 2010 11:12 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:32 PM
Points: 11,194, Visits: 11,112
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #870079
Posted Monday, February 22, 2010 7:03 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, October 2, 2013 9:39 AM
Points: 329, Visits: 195
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


  Post Attachments 
afterUpdate.txt (18 views, 2.25 KB)
beforeUpdate.txt (17 views, 2.21 KB)
SqlServer2005Sp2_updateTableScript.sql.txt (20 views, 1.28 KB)
Post #870322
Posted Monday, February 22, 2010 7:44 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:32 PM
Points: 11,194, Visits: 11,112
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #870360
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse