SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


ALTER


ALTER

Author
Message
Fozzie
Fozzie
Right there with Babe
Right there with Babe (719 reputation)Right there with Babe (719 reputation)Right there with Babe (719 reputation)Right there with Babe (719 reputation)Right there with Babe (719 reputation)Right there with Babe (719 reputation)Right there with Babe (719 reputation)Right there with Babe (719 reputation)

Group: General Forum Members
Points: 719 Visits: 1172
Very good question and a clear explanation. Specification of NOT NULL is obvious, I just hadn't woken up yet and didn't read the table schema properly... must drink my coffee first.

Thanks Sankar et Hugo.
Andre Guerreiro
Andre Guerreiro
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2263 Visits: 1515
For a second I read this:

Option 1 runs faster but Option 2 is the right choice.


instead of this:

Option 1 runs faster than Option 2 and is the right choice.


If I had read it correctly I would have answered it right. :/

Anyways, very nice and detailed question with a very good explanation.

Best regards,

Andre Guerreiro Neto

Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA
Hugo Kornelis
Hugo Kornelis
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18735 Visits: 12426
Sankar Reddy (7/15/2010)
I am glad this QoD has helped people learn new things. The credit should directly go to Hugo Kornelis for his excellent feedback otherwise this would have been an incorrect/ordinary question.

Thanks for the kind words, Sankar. But you are giving me too much credit. The idea of the question was yours. I merely helped you to improve the options and the explanation a bit.


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
taylor_benjamin
taylor_benjamin
SSC Journeyman
SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)

Group: General Forum Members
Points: 80 Visits: 51
I disagree that option 1 is the right choice. It stated that Option 1 was faster and must therefore be the correct choice. However, option 1 lost a constraint that was part of the table in the first place. How could this be the correct choice?

There was nothing in the question the had any performance requirements for getting the fastest update with no concern for the integrity of future data. There was a notion of a large number of records. So, the last answer must be the correct one that either one is acceptable without further qualification.

My 2 Cents.
Hugo Kornelis
Hugo Kornelis
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18735 Visits: 12426
taylor_benjamin (8/23/2010)
I disagree that option 1 is the right choice. It stated that Option 1 was faster and must therefore be the correct choice. However, option 1 lost a constraint that was part of the table in the first place. How could this be the correct choice?

Hi Taylor,

I'm afraid I don't understand your concern. You write that option 1 loses a constraint and hence can never be the correct choice. But this is not correct - it is not option 1, but option 2 that loses a constraint.

I do of course agree that performance comes after correctness, so that the "correct" option always has to be picked over the incorrect option regardless of which one is faster. But in this particular case, option 1 is the correct one (and it being faster is an additional bonus).


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
taylor_benjamin
taylor_benjamin
SSC Journeyman
SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)

Group: General Forum Members
Points: 80 Visits: 51
Sadly, receiving the response through comments I cannot see the original question. From my unreliable memory I remember parts of the question as follows: :-)

The table had a column width that was being increased in size.

The original column definition was VARCHAR(?) NOT NULL.

The Update statements that were there were
1) Alter Table x Alter Column VARCHAR(Bigger Amount) NULL
2) Alter Table x Alter Column VARCHAR(Bigger Amount) NULL

From memory the options were
1) Statement 1 was fastest and therefore the best
2) Statement 2 was fastest and therefore the best.
3)...
4)...
5) Either was a good choice.

The author expected the correct answer to be answer 1.

In my opinion this is not a good choice w00t. The result of running the answer that is supposed to be correct is the dropping of the NOT NULL constraint in order to alter the table quickly.

None of the answers were really good...so I picked what I thought was the best answer from the list available...option 5. I chose this answer because the speed of execution was not established as the only/sole requirement of the modification. Option 5 simply stated that either one would work and could be a good choice.

If the table was originally defined as not allowing nulls, how can an alter allowing nulls be a good choice?

I humbly disagree.

Cheers,

Ben

Thanks for taking the time to reply to my response.
Sankar Reddy
Sankar Reddy
SSC Eights!
SSC Eights! (850 reputation)SSC Eights! (850 reputation)SSC Eights! (850 reputation)SSC Eights! (850 reputation)SSC Eights! (850 reputation)SSC Eights! (850 reputation)SSC Eights! (850 reputation)SSC Eights! (850 reputation)

Group: General Forum Members
Points: 850 Visits: 1250
Taylor,

I agree with you that relying on memory and commenting on the post is NOT only difficult but extremely prone to errors. To go back to the actual question, all you have to do is, click "QotD" on the left side of the page, just below "Blogs" and go back few pages to get to July 14 2010 and the question title is "ALTER".

Please re-read the question without any prior bias/assumptions/thoughts word by word and ask yourself which might be correct. Also test the theory with some sample data involving large volumes of data.

In the requirements, it is explicitly stated that increasing the length of the column is the "ONLY change requested" and another tell is to look for the "right and optimal solution".

>>The result of running the answer that is supposed to be correct is the dropping of the NOT NULL constraint in order to alter the table quickly.
>>If the table was originally defined as not allowing nulls, how can an alter allowing nulls be a good choice?

If you look at the question again, I am sure you will come back and might say that your memory was fuzzy and didn't remember the answer options correctly.

The correct answer is quite the opposite of what you are referring above and I will request you to please read the question and the answer options again.



Sankar Reddy | http://SankarReddy.com/
Hugo Kornelis
Hugo Kornelis
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18735 Visits: 12426
taylor_benjamin (8/27/2010)
Sadly, receiving the response through comments I cannot see the original question. From my unreliable memory I remember parts of the question as follows: :-)
It's very easy to check the question. Just go to page 1 of this discussion, then clck the link in the first message (the one that reads "Comments posted to this topic are about the item ALTER" - the word ALTER is a hyperlink that will take you directly to the question).

You'll probably find that there is a small, yet significant difference between your memory and the actual question.


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Dhruvesh Shah
Dhruvesh Shah
Right there with Babe
Right there with Babe (799 reputation)Right there with Babe (799 reputation)Right there with Babe (799 reputation)Right there with Babe (799 reputation)Right there with Babe (799 reputation)Right there with Babe (799 reputation)Right there with Babe (799 reputation)Right there with Babe (799 reputation)

Group: General Forum Members
Points: 799 Visits: 237
Learn about Null bit map thanks
rtelgenhoff
rtelgenhoff
Right there with Babe
Right there with Babe (748 reputation)Right there with Babe (748 reputation)Right there with Babe (748 reputation)Right there with Babe (748 reputation)Right there with Babe (748 reputation)Right there with Babe (748 reputation)Right there with Babe (748 reputation)Right there with Babe (748 reputation)

Group: General Forum Members
Points: 748 Visits: 366
Thanks Sankar and Hugo for a good question. The performance and NULL handling are interesting and the wording really allowed only 1 realistic answer, knowing that UNIQUE constraint still allows multiple NULL entries. Option 2 might work, albeit slower, but violates existing business rules.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search