ALTER

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Sankar Reddy » ALTER

45 posts, Page 1 of 5

1 2 3 4 5 »»»

ALTER

Rate Topic

Display Mode

Topic Options

Author

Message

Sankar Reddy

Posted Tuesday, July 13, 2010 8:40 PM

Old Hand

Group: General Forum Members
Last Login: Friday, June 07, 2013 2:21 PM
Points: 382, Visits: 1,109

Comments posted to this topic are about the item ALTER

Sankar Reddy | http://SankarReddy.com/

Post #951965

Nakul Vachhrajani

Posted Tuesday, July 13, 2010 11:12 PM

Ten Centuries

Group: General Forum Members
Last Login: Monday, June 10, 2013 6:56 AM
Points: 1,282, Visits: 1,613

Really good question. Thank-you!

Thanks & Regards,
Nakul Vachhrajani.
http://beyondrelational.com/modules/2/blogs/77/nakuls-blog.aspx
Be courteous. Drive responsibly.

Follow me on
Twitter: @nakulv_sql
Google Plus: +Nakul

Post #952014

malleswarareddy_m

Posted Wednesday, July 14, 2010 12:01 AM

SSCommitted

Group: General Forum Members
Last Login: Today @ 7:53 AM
Points: 1,868, Visits: 1,044

good Question.Learned some new point about nulls

Malleswarareddy
I.T.Analyst
MCITP(70-451)

Post #952051

Carlo Romagnano

Posted Wednesday, July 14, 2010 2:57 AM

SSCommitted

Group: General Forum Members
Last Login: Today @ 7:18 AM
Points: 1,996, Visits: 1,864

Option 1 is the right and only choice, Option 2 runs fast same as Option 1.
The performance problem is done converting NULL to NOT NULL and not viceversa.

Try this script to verify:
create table zz(a varchar(20) not null)
insert into zz(a) select a.id from sysobjects,sysobjects a

-- same performance
SET IMPLICIT_TRANSACTIONS ON
go
alter table zz alter column a varchar(50) not null
alter table zz alter column a varchar(50)
go
--
rollback
go
-- converting null to not null may take a while
alter table zz alter column a varchar(50)
alter table zz alter column a varchar(50) not null
go
rollback

Post #952131

Hugo Kornelis

Posted Wednesday, July 14, 2010 3:06 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 2:44 PM
Points: 5,297, Visits: 7,239

Carlo, you are missing an impportant point in your repro.

The performance difference in the question is not caused by rebuilding or converting the table contents, but by rebuilding / converting the nonclustered index. Since your repro lacks a nonclustered index, it's not a surprise that you don't see a performance difference.

Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

Post #952133

Bhuvnesh

Posted Wednesday, July 14, 2010 4:26 AM

SSCrazy

Group: General Forum Members
Last Login: Thursday, June 13, 2013 7:03 AM
Points: 2,562, Visits: 3,453

Good question , i took some minutes to understand this

-------Bhuvnesh----------
While 1 = 1 (Learning SQL....)
Click to get fast response of your post

Post #952166

Sanjay-940444

Posted Wednesday, July 14, 2010 6:32 AM

SSC Rookie

Group: General Forum Members
Last Login: Wednesday, June 05, 2013 4:14 AM
Points: 26, Visits: 93

Here question is not about performance but WHAT change company wants.
There was a not null constraint already on this column means company didnt want to have null value for this columns and while increasing the size we have to keep this in mind.2nd option will make it nullable hence accepting null value (atleast one as it is a unique constraint) which will violate an already existing business rule.

rgds

Post #952261

Mike Dougherty

Posted Wednesday, July 14, 2010 7:03 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, July 22, 2010 8:59 AM
Points: 110, Visits: 952

how about:
Schema changes to a 50 million row table should not be done while the DBA is on vacation. Especially when the table in question potentially affects "login" of 50 million customers. :)

Post #952294

Carlo Romagnano

Posted Wednesday, July 14, 2010 7:10 AM

SSCommitted

Group: General Forum Members
Last Login: Today @ 7:18 AM
Points: 1,996, Visits: 1,864

Hugo Kornelis (7/14/2010)

OK, for performance question.
But the two options do very different things: option 2 changes the column from NOT NULLABLE TO NULLABLE.
The hint is to enlarge a column not to change attribute.

Post #952299

Rose Bud

Posted Wednesday, July 14, 2010 7:25 AM

Ten Centuries

Group: General Forum Members
Last Login: Thursday, May 16, 2013 9:42 AM
Points: 1,072, Visits: 1,026

I was amused to see Sanjay and Mike D had the same thought process I did: Should John the developer be willing to risk the DBA's post-vacation wrath by allowing nulls on that column? (even if we are only talking about one null, at most)

But, we digress from the main point about performance which was made quite well by this well-written question and thorough explanation of the correct answer. Thanks, Sankar.

Post #952318

« Prev Topic | Next Topic »

45 posts, Page 1 of 5

1 2 3 4 5 »»»

Permissions