Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««12345»»

ALTER Expand / Collapse
Author
Message
Posted Friday, July 16, 2010 1:29 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, September 15, 2014 6:42 AM
Points: 365, Visits: 940
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.

Post #953645
Posted Friday, July 16, 2010 5:47 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, September 12, 2014 5:33 AM
Points: 900, Visits: 1,489
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
Post #953777
Posted Friday, July 16, 2010 3:19 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 1:36 PM
Points: 6,002, Visits: 8,267
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
Post #954213
Posted Monday, August 23, 2010 8:15 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, August 25, 2014 3:46 PM
Points: 22, Visits: 45
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.
Post #973812
Posted Tuesday, August 24, 2010 12:13 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 1:36 PM
Points: 6,002, Visits: 8,267
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
Post #973871
Posted Friday, August 27, 2010 10:23 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, August 25, 2014 3:46 PM
Points: 22, Visits: 45
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 . 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.
Post #976887
Posted Friday, August 27, 2010 11:14 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 10:47 AM
Points: 382, Visits: 1,159
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/
Post #976893
Posted Saturday, August 28, 2010 6:57 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 1:36 PM
Points: 6,002, Visits: 8,267
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
Post #976924
Posted Monday, September 27, 2010 5:11 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Sunday, November 17, 2013 11:53 AM
Points: 623, Visits: 237
Learn about Null bit map thanks
Post #994115
Posted Saturday, October 2, 2010 6:33 PM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 8:33 AM
Points: 703, Visits: 326
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.
Post #997256
« Prev Topic | Next Topic »

Add to briefcase «««12345»»

Permissions Expand / Collapse