December 17, 2015 at 8:04 pm
Comments posted to this topic are about the item Changing a computed column
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
December 18, 2015 at 12:37 am
Nice question Steve, thanks, a timely reminder about indexing.
...
December 18, 2015 at 1:19 am
In case of shortening or if the type is CHAR/NCHAR the following error is raised:
Msg 5074, Level 16, State 1, Line 17
The index 'IX_MySales_CustomerReport' is dependent on column 'OrderStatus'.
Msg 4922, Level 16, State 9, Line 17
ALTER TABLE ALTER COLUMN OrderStatus failed because one or more objects access this column.
December 18, 2015 at 2:07 am
...makes sense.
Thanks Steve.
December 18, 2015 at 5:17 am
I was looking for the trick I was missing, but never found it. 😉 Thanks, Steve.
December 18, 2015 at 5:22 am
Carlo Romagnano (12/18/2015)
In case of shortening or if the type is CHAR/NCHAR the following error is raised:Msg 5074, Level 16, State 1, Line 17
The index 'IX_MySales_CustomerReport' is dependent on column 'OrderStatus'.
Msg 4922, Level 16, State 9, Line 17
ALTER TABLE ALTER COLUMN OrderStatus failed because one or more objects access this column.
That's correct, but I don't recall seeing an answer that shortened the width of the column. I can't go back and look at them again and I'm not completely caffeinated yet, so I might be remembering incorrectly, so I could be wrong in my recollection.
December 18, 2015 at 6:08 am
Ed Wagner (12/18/2015)
Carlo Romagnano (12/18/2015)
In case of shortening or if the type is CHAR/NCHAR the following error is raised:Msg 5074, Level 16, State 1, Line 17
The index 'IX_MySales_CustomerReport' is dependent on column 'OrderStatus'.
Msg 4922, Level 16, State 9, Line 17
ALTER TABLE ALTER COLUMN OrderStatus failed because one or more objects access this column.
That's correct, but I don't recall seeing an answer that shortened the width of the column. I can't go back and look at them again and I'm not completely caffeinated yet, so I might be remembering incorrectly, so I could be wrong in my recollection.
It's just a remark! 😀
December 18, 2015 at 6:21 am
Carlo Romagnano (12/18/2015)
Ed Wagner (12/18/2015)
Carlo Romagnano (12/18/2015)
In case of shortening or if the type is CHAR/NCHAR the following error is raised:Msg 5074, Level 16, State 1, Line 17
The index 'IX_MySales_CustomerReport' is dependent on column 'OrderStatus'.
Msg 4922, Level 16, State 9, Line 17
ALTER TABLE ALTER COLUMN OrderStatus failed because one or more objects access this column.
That's correct, but I don't recall seeing an answer that shortened the width of the column. I can't go back and look at them again and I'm not completely caffeinated yet, so I might be remembering incorrectly, so I could be wrong in my recollection.
It's just a remark! 😀
Ah - got it.
December 18, 2015 at 6:33 am
Thanks Steve, may could help also this reference: https://msdn.microsoft.com/en-us/library/ms190806.aspx
December 18, 2015 at 6:52 am
Trying to figure out what the question had to do with a computed column had me stymied for a few minutes. I kept going back over it, trying to figure out what I was missing. I guess the answer is nothing, the question title was just a red herring.
December 18, 2015 at 8:29 am
Xavon (12/18/2015)
Trying to figure out what the question had to do with a computed column had me stymied for a few minutes. I kept going back over it, trying to figure out what I was missing. I guess the answer is nothing, the question title was just a red herring.
Same here. There is no computed column so the question title really had me scratching my head.
Good question.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 18, 2015 at 9:34 am
Xavon (12/18/2015)
Trying to figure out what the question had to do with a computed column had me stymied for a few minutes. I kept going back over it, trying to figure out what I was missing. I guess the answer is nothing, the question title was just a red herring.
Ahh, sorry. I had a computed column in there earlier and changed the code. I must have mistitled this one without realizing it during testing.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
December 18, 2015 at 11:50 am
Nice Question. Thnx Steve!
Thanks.
December 19, 2015 at 12:02 am
Thanks for this easy question ( not surprising as it is the week-end ).
Just a little remark : this question was treated 3 years ago in a session by an escalation engineer who did a remark maybe important : it is possible to decrease the size of a column ( varchar/nvarchar ) if for all the rows , the new size is bigger than the maximum size of the column in all rows.
As I am not sure to be clear ( I am admitting that I have a poor written English language and the spoken one is worst ) : I have 4 rows with the values
value1 value01 value001 for a varchar(20) no problem to decrease the size to varchar(10) , but it is impossible to change to a varchar(7) as the size of value001 is 8.
If I am going wrong , please , could you provide a link giving a full ( and easy ) explanation for my error ? Thanks beforehand.
Have a nice day/week-end.
December 19, 2015 at 7:58 am
Carlo Romagnano (12/18/2015)
Ed Wagner (12/18/2015)
Carlo Romagnano (12/18/2015)
In case of shortening or if the type is CHAR/NCHAR the following error is raised:Msg 5074, Level 16, State 1, Line 17
The index 'IX_MySales_CustomerReport' is dependent on column 'OrderStatus'.
Msg 4922, Level 16, State 9, Line 17
ALTER TABLE ALTER COLUMN OrderStatus failed because one or more objects access this column.
That's correct, but I don't recall seeing an answer that shortened the width of the column. I can't go back and look at them again and I'm not completely caffeinated yet, so I might be remembering incorrectly, so I could be wrong in my recollection.
It's just a remark! 😀
The interesting thing about that remark is that it makes it clear that a constant length colum can't have it's length changed while included in an index - so Steve's explanation "If you are lengthening a column, you can just alter it, even if the column is included in an index" isn't quite correct.
Nice easy Friday question anyway.
Tom
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply