Rename column

  • Lokesh Vij

    SSChampion

    Points: 10836

    Yes. That's true.

    As persisted columns are used to define computed column they cannot be changed all alone.

    ~ Lokesh Vij


    Guidelines for quicker answers on T-SQL question[/url]
    Guidelines for answers on Performance questions

    Link to my Blog Post --> www.SQLPathy.com[/url]

    Follow me @Twitter

  • Lokesh Vij

    SSChampion

    Points: 10836

    My question still remains unanswered, does someone has an answer to this please.

    - Lokesh

    ~ Lokesh Vij


    Guidelines for quicker answers on T-SQL question[/url]
    Guidelines for answers on Performance questions

    Link to my Blog Post --> www.SQLPathy.com[/url]

    Follow me @Twitter

  • OzYbOi d(-_-)b

    Hall of Fame

    Points: 3994

    great question - cheers

  • Miles Neale

    SSChampion

    Points: 13147

    Lokesh - I looked at this for a few minutes and come to this:

    1. The update is a delete and add, both could generate a message in a routine called by the rename function.

    2. The are at least two tables related to the column name. the column name is not in sysobjects but the table name is that row in sysobjects points to a column table or whatever it is called that holds the key for the column and its name. That key is related to a record in the syscomments table that holds the formula for computing of the value. To do an update of a column there are at least three tables open, sysobjects, syscolumns, and syscomments. At least two of those could change if the delete and add were to happen which might or might not generate a duplicate message.

    3. Or some other person may have an idea, or the real answer.

    M.

    Not all gray hairs are Dinosaurs!

  • Ken Wymore

    SSCoach

    Points: 16588

    sestell1 (7/25/2012)


    Thanks for the question!

    I don't use computed columns often, so this is very good to know!

    +1

  • Michael Poppers

    SSCrazy

    Points: 2131

    Thanks, PravS, for a good QotD. I didn't think we would be allowed to rename a computed column, but the MSDN page on sp_rename (e.g. http://msdn.microsoft.com/en-us/library/ms188351.aspx) didn't say a word about the issue. You mentioned ALTER TABLE as a reference for the answer to the QotD, but at least in the SQL Server version I most frequently use (SQL2005, 9.00.5057.00), sp_rename actually utilizes the undocumented DBCC RENAMECOLUMN command, not ALTER TABLE.

  • TomThomson

    SSC Guru

    Points: 104773

    Good question, nice and straightforward.

    But I don't have a clue why someone decided that a computed column can not have its name changed, it strikes me as a unnecessary restriction. It's not a restriction that's going to cause much pain though.

    Tom

  • PravS

    SSC Journeyman

    Points: 93

    Thanks everybody for all those nice & encouraging comments about the post.

    Michael & all,

    i am sorry for putting the wrong reference for the answer.......i realized it yesterday.

    It happened that while posting QotD I was looking at both ALTER and sp_rename command on MSDN....and i ended up pasting the link for ALTER command instead of sp_rename. But as you said, sp_rename MSDN page does not mention anything about this.:-P

  • Lokesh Vij

    SSChampion

    Points: 10836

    Miles Neale (7/25/2012)


    Lokesh - I looked at this for a few minutes and come to this:

    1. The update is a delete and add, both could generate a message in a routine called by the rename function.

    2. The are at least two tables related to the column name. the column name is not in sysobjects but the table name is that row in sysobjects points to a column table or whatever it is called that holds the key for the column and its name. That key is related to a record in the syscomments table that holds the formula for computing of the value. To do an update of a column there are at least three tables open, sysobjects, syscolumns, and syscomments. At least two of those could change if the delete and add were to happen which might or might not generate a duplicate message.

    3. Or some other person may have an idea, or the real answer.

    M.

    Thanks Miles:-)

    ~ Lokesh Vij


    Guidelines for quicker answers on T-SQL question[/url]
    Guidelines for answers on Performance questions

    Link to my Blog Post --> www.SQLPathy.com[/url]

    Follow me @Twitter

  • Koen Verbeeck

    SSC Guru

    Points: 258965

    Thanks for the question.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Andre Guerreiro

    SSCertifiable

    Points: 7319

    Got it wrong and I'm satisfied because it was a very interesting question.

    Microsoft always makes QotD harder than it should be because many things don't work the way we logically think they do. 😉

    Best regards,

    Andre Guerreiro Neto

    Database Analyst
    http://www.softplan.com.br
    MCITPx1/MCTSx2/MCSE/MCSA

  • tilew-948340

    Hall of Fame

    Points: 3431

    From the remarks in the sp_rename definition:

    Renaming a stored procedure, function, view, or trigger will not change the name of the corresponding object name in the definition column of the sys.sql_modules catalog view. Therefore, we recommend that sp_rename not be used to rename these object types. Instead, drop and re-create the object with its new name.

    A computed columns, from what I learned (maybe I am wrong), is kind of a function.

    The QotD was on "sp_rename", not "Alter" table and nothing in the definition talked about "error", so I got it wrong... 🙁

  • Vinay Kumar

    SSCertifiable

    Points: 6098

    Learn new thing

    Thanks

    Thanks
    Vinay Kumar
    -----------------------------------------------------------------
    Keep Learning - Keep Growing !!!

Viewing 13 posts - 16 through 28 (of 28 total)

You must be logged in to reply to this topic. Login to reply