Post reply

Rename column

  • July 25, 2012 at 8:36 am

    #1517395

    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

  • July 25, 2012 at 9:23 am

    #1517429

    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

  • July 25, 2012 at 10:02 am

    #1517469

    great question - cheers

  • July 25, 2012 at 11:27 am

    #1517536

    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!

  • July 25, 2012 at 12:07 pm

    #1517557

    sestell1 (7/25/2012)

    Thanks for the question!

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

    +1

  • July 25, 2012 at 1:31 pm

    #1517606

    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.

  • July 25, 2012 at 5:49 pm

    #1517695

    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

  • July 25, 2012 at 9:06 pm

    #1517717

    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

  • July 26, 2012 at 6:09 am

    #1517866

    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

  • July 27, 2012 at 5:20 am

    #1518414

    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

  • July 27, 2012 at 5:21 pm

    #1518810

    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

  • July 30, 2012 at 7:19 am

    #1519131

    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... 🙁

  • July 31, 2012 at 7:14 am

    #1519703

    Learn new thing

    Thanks

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

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

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