4

The documentation claims

The data type of a column of a partitioned table can't be changed.

It repeats this claim elsewhere such as here.

Yet I have never seen this fail.

CREATE PARTITION FUNCTION pf(int)
AS RANGE RIGHT FOR VALUES(10, 20, 30)
GO

CREATE PARTITION SCHEME ps
AS PARTITION pf
ALL TO ([primary]);
GO


CREATE TABLE Part
(
    id INT IDENTITY PRIMARY KEY CLUSTERED,
    name VARCHAR(50)
) ON ps(id);
GO


INSERT INTO Part (name)
SELECT TOP(40) CONVERT(VARCHAR(50), [text])
FROM sys.messages;
GO


ALTER TABLE Part
ALTER COLUMN [name]
SQL_VARIANT;
GO

So what am I missing?

Peter Vandivier
  • 5,485
  • 1
  • 25
  • 49
J. Mini
  • 1,161
  • 8
  • 32

1 Answers1

0

That you can't change the data type of the column used for partitioning. It would be strange if partitioning a table prevented some completely separate column from being modified (although this is admittedly ambiguous in the docs).

We can't demonstrate this with the current setup since id is used in the primary key, and it's impossible to partition on name without making it part of the unique index key (though there may be some workarounds).

CREATE TABLE Part2
(
    id INT,
    name VARCHAR(50)
) ON ps(id); -- Look ma, no keys!

INSERT INTO Part2 (id, name)
SELECT TOP(40) message_id, CONVERT(VARCHAR(50), [text])
FROM sys.messages;
GO


ALTER TABLE dbo.Part2 ALTER COLUMN [name] SQL_VARIANT -- works

ALTER TABLE dbo.Part2 ALTER COLUMN [id] SQL_VARIANT -- does not work

And for the grand reveal:

Msg 5074, Level 16, State 1, Line XX
The object 'Part2' is dependent on column 'id'.
Msg 4922, Level 16, State 9, Line XX
ALTER TABLE ALTER COLUMN name failed because one or more objects access this column.
ImperviousInclemency
  • 105
  • 10