Alter Column
-
The unfortunate answer to this question has always bugged me. - It just doesn't make sense that ALTER COLUMN is not allowed on a computed column...
The DROP COLUMN + ADD COLUMN work-around to alter the calculation for a computed column has the unfortunate side-effect of altering the column order in the table (unless it's already the last column).
I can ALTER COLUMN on a non-computed column and completely change the data-type if I want, but I can't alter the calculation for a computed column - weird... :w00t:
-
Good one, thank you for the post.
ww; Raghu
--
The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart. -
Koen Verbeeck (4/30/2014)
Interesting question, thanks.For people who like an official reference:
ALTER COLUMN
Specifies that the named column is to be changed or altered.
The modified column cannot be any one of the following:
* ...
* A computed column or used in a computed column.
* ...
That statement on that page will lead people straignt to the incorrect answer. It is an error on the BOL page. Not all that rare a phenomenon.
This is a bad question, because a computed column can be changed, using alter column as is quite clear to anyone who reads the syntax definition carefully, or even just reads the explanation text further down the page that describes altering a computed column using the alter column clause:
[ {ADD | DROP} PERSISTED ]
Specifies that the PERSISTED property is added to or dropped from the specified column. The column must be a computed column that is defined with a deterministic expression.
This code works perfectly well:-
create table testalter (x int primary key, comp as -x)-- so now I have a table with a computed column named comp
alter table testalter alter column comp add persisted
-- that statement works. The column comp is altered to be persistent. So I can alter a computed column with alter column.
drop table testalter -- remove clutter
So the correct answer to this question is the last option, "none of the above".
The first option (just run the alter column statement) doesn't work becase (a) most properties of a computed column can't be altered that way (and some computed columns can't even have their persisted property altered that way) and (b) pedantically, alter column is not a statement, it's a clause within an alter table statement.
Tom
-
Good question. It was one of those that I was pretty sure I knew the answer to, but the fact that it was here made me second-guess myself, which is sometimes a good thing. So, I looked it up anyway. Thanks.
-
This was removed by the editor as SPAM
-
BWFC (4/30/2014)
It certainly has its uses but I'm always careful what I post because unscrupulous types could use it to bypass the hours of research that I may have been through. As I said, if the meaning of the question isn't always clear it's a useful place to get hints as to what the intention may have been. To be honest I don't think a lot of people know it's there though.
Or, unscrupulous types could just wait until tomorrow's newsletter, look at the answer there, and then answer yesterday's question. You can actually go back years and answer old questions if you just want to rack up points.
As always, let your conscience be your guide.
[font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
Connect to me on LinkedIn -
Thomas Abraham (4/30/2014)
BWFC (4/30/2014)
It certainly has its uses but I'm always careful what I post because unscrupulous types could use it to bypass the hours of research that I may have been through. As I said, if the meaning of the question isn't always clear it's a useful place to get hints as to what the intention may have been. To be honest I don't think a lot of people know it's there though.
Or, unscrupulous types could just wait until tomorrow's newsletter, look at the answer there, and then answer yesterday's question. You can actually go back years and answer old questions if you just want to rack up points.
As always, let your conscience be your guide.
this thread is loaded with new stuffs (learned more than one today)
first, the alter column thing
then, the discuss link on the home page
and, this word "unscrupulous" - really seeing this word for the first time (and this word is heavy)
now "let your conscience be your guide"... this is awesome +(infinite)
ww; Raghu
--
The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart. -
Knew this one from personal experience. I use computed columns all the time, particularly for things like derived file paths and other things that might change after implementation. Much easier to drop & recreate a computed column than to update a column for millions of rows with a newly computed value.
Tony
------------------------------------
Are you suggesting coconuts migrate? -
Thomas Abraham (4/30/2014)
BWFC (4/30/2014)
It certainly has its uses but I'm always careful what I post because unscrupulous types could use it to bypass the hours of research that I may have been through. As I said, if the meaning of the question isn't always clear it's a useful place to get hints as to what the intention may have been. To be honest I don't think a lot of people know it's there though.
Or, unscrupulous types could just wait until tomorrow's newsletter, look at the answer there, and then answer yesterday's question. You can actually go back years and answer old questions if you just want to rack up points.
As always, let your conscience be your guide.
I'm still waiting for the place where I can spend my points for valuable prizes. :rolleyes:
-
OCTom (4/30/2014)
Thomas Abraham (4/30/2014)
BWFC (4/30/2014)
It certainly has its uses but I'm always careful what I post because unscrupulous types could use it to bypass the hours of research that I may have been through. As I said, if the meaning of the question isn't always clear it's a useful place to get hints as to what the intention may have been. To be honest I don't think a lot of people know it's there though.
Or, unscrupulous types could just wait until tomorrow's newsletter, look at the answer there, and then answer yesterday's question. You can actually go back years and answer old questions if you just want to rack up points.
As always, let your conscience be your guide.
I'm still waiting for the place where I can spend my points for valuable prizes. :rolleyes:
I'm not spending them. I have them roll over into my IRA. Given the sad state of social security, the points on SSC will be a substantial portion of my retirement income.
[font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
Connect to me on LinkedIn -
BWFC (4/30/2014)
Koen, that really gives the answer away, you don't even need Google :ermm:BWFC - This could be said about every discussion post for every QOTD ever...
It could be said that you have shown everyone that reads your post how to find this cheat.
If they are going to cheat, they will. They can also wait till tomorrow, read the answer that gets emailed to everybody and still get points for answering.
Let's discuss the questions and peoples responses to it.
The cheaters and trolls will get enough attention without us helping them out. 😎
-
OCTom (4/30/2014)
Thomas Abraham (4/30/2014)
BWFC (4/30/2014)
It certainly has its uses but I'm always careful what I post because unscrupulous types could use it to bypass the hours of research that I may have been through. As I said, if the meaning of the question isn't always clear it's a useful place to get hints as to what the intention may have been. To be honest I don't think a lot of people know it's there though.
Or, unscrupulous types could just wait until tomorrow's newsletter, look at the answer there, and then answer yesterday's question. You can actually go back years and answer old questions if you just want to rack up points.
As always, let your conscience be your guide.
I'm still waiting for the place where I can spend my points for valuable prizes. :rolleyes:
+100 :smooooth:
-
nice and easy one..
thanks Shanjan.
-
Thanks for the QotD
Hope this helps...
Ford Fairlane
Rock and Roll Detective
-
Thomas Abraham (4/30/2014)
BWFC (4/30/2014)
You can actually go back years and answer old questions if you just want to rack up points.
Some people do. They add extra points by adding a vitally important comment to the end of the discussion - usually something like "Easy" - so those of us who've subscribed to the thread suddenly get an email alert about something that was over months or even years before!
Viewing 15 posts - 16 through 30 (of 31 total)
You must be logged in to reply to this topic. Login to reply