Will renaming a column rewrite the rows

[MCMartin]

When renaming a column will this rewrite the rows of a table?

Alter table X rename C1 to C2

[VolkerBarth]

Why would you expect a rewrite? IMHO, that should only change the system catalog and might force all stored procs etc. to be reloaded and parsed and all cached query plans to be dropped.

[MCMartin]

I don't expect it, but I want to be sure

[Breck_Carter]

@Volker: IMO one should always be afraid, very afraid, of ALTER TABLE. First of all, what it does and does not do and HOW FAST it does the things it will do, depends to a huge extent on what version of SQL Anywhere we are talking about. In fact, ALTER TABLE never ceases to surprise me... see my anecdotal answer for evidence of that 🙂

[VolkerBarth]

@Martin: I'm o glad that you have asked - particularly as I wasn't aware of the "computed columns re-computation" side-effect (s. Mark's comment). @Breck: You are more right than you have thought, right?

[MarkCulp · Accepted solution]

Renaming a column is simply a matter of updating the catalog table SYSCOLUMN to have the new name. The row data in the table is not affected by the change.... unless you have computed columns in the table, in which case the table is scanned and each computed column value is recomputed and updated if its value has changed (i.e. if the computed column did not change then the row is not rewritten).

[VolkerBarth · Accepted solution]

@Mark: Are my assumptions about the performance issues (due to recompliation of stored procs and dropping of cached plans) correct? (Confine my comment on the question.)

[MarkCulp · Accepted solution]

Yes, renaming a column will cause triggers and stored procedures to be reloaded. I would also presume that the cache would be dropped in order to maintain correctness (but I did not confirm this).

[Breck_Carter]

No. Maybe. See Mark's expanded answer, re: computed columns.

That's the short answer, the long answer is "it might not rewrite the rows but it sure does something expensive".

The following example shows what happens with a large table that has computed columns that do not change in value.

---

Here's an example of renaming a column and renaming it back, in a fairly large table using 32-bit SQL Anywhere 11.0.1.2472 on a consumer-grade PC with a 2.66 GHz Core2 Quad Q9450 processor and a 500G drive running 64-bit Windows Vista Ultimate:

ALTER TABLE DBA.rroad_group_2_property_pivot RENAME blocker_row_identifier TO BlahBlahBlah;
Execution time: 293.281 seconds

ALTER TABLE DBA.rroad_group_2_property_pivot RENAME BlahBlahBlah TO blocker_row_identifier;
Execution time: 286.469 seconds

Here's what I mean by "fairly large"... trust me, SQL Anywhere takes a lot longer than 5 minutes to rewrite 22M rows in a 14G table...

-- DBA.rroad_group_2_property_pivot (table_id 735) in Foxhound on bcarter-pc - Oct 19 2010 7:58:23AM - Print - Foxhound © 2010 RisingRoad

CREATE TABLE DBA.rroad_group_2_property_pivot ( -- 22,967,920 rows, 14G total = 13.5G table + 56k ext + 542M index, 655 bytes per row
   ...
   blocker_row_identifier                        VARCHAR ( 32 ) NULL,
   ...

Here's some more evidence that something expensive is going on, but it's NOT rewriting the rows:

• The Windows Vista Task Manager showed dbsrv11.exe pegged one of the 4 processors at 100%.
• The Resource Monitor - Resource Overview - Disk graph went up to 50 MB/sec, and the Disk details pane showed the database I/O at 3,100,000,000 for Read (B/min) and 0 for Write (B/min)

How big was the cache? Only 2G, only 1/7th the size of the table, so that doesn't explain the "0 writes".

[VolkerBarth]

Any explanation of what has happened?

[MCMartin]

Sounds to me like a sequential table scan...

[Breck_Carter]

@Volker: I have no idea. @Martin: I have no idea.

[VolkerBarth]

@Breck: Well, if you had changed the column name to/from "C2" (as in Martin's sample), then I would suspect that a C2-compliant audit has taken place under cover:)

[Breck_Carter]

@Volker: Not in Version 10 or later... but you get points for humor 🙂

[MarkCulp]

@Breck: Do you have any computed columns in your table? If you do then renaming a column will cause all computed columns to be recomputed.

[Breck_Carter]

@Mark: Yes.....