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 Tuesday, July 13, 2010 8:40 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, March 24, 2014 5:16 PM
Points: 382, Visits: 1,136
Comments posted to this topic are about the item ALTER

Sankar Reddy | http://SankarReddy.com/
Post #951965
Posted Tuesday, July 13, 2010 11:12 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Sunday, April 20, 2014 11:47 PM
Points: 1,382, Visits: 1,775
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
Posted Wednesday, July 14, 2010 12:01 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, March 12, 2014 3:59 AM
Points: 1,938, Visits: 1,149
good Question.Learned some new point about nulls

Malleswarareddy
I.T.Analyst
MCITP(70-451)
Post #952051
Posted Wednesday, July 14, 2010 2:57 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 1:39 AM
Points: 2,397, Visits: 2,287
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
Posted Wednesday, July 14, 2010 3:06 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 10:59 AM
Points: 5,801, Visits: 8,018
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
Posted Wednesday, July 14, 2010 4:26 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 6:52 AM
Points: 2,820, Visits: 3,917
Good question , i took some minutes to understand this

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done
Post #952166
Posted Wednesday, July 14, 2010 6:32 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, October 22, 2013 1:40 AM
Points: 26, Visits: 104
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
Posted Wednesday, July 14, 2010 7:03 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-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
Posted Wednesday, July 14, 2010 7:10 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 1:39 AM
Points: 2,397, Visits: 2,287
Hugo Kornelis (7/14/2010)
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.

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
Posted Wednesday, July 14, 2010 7:25 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, April 07, 2014 1:38 PM
Points: 1,305, Visits: 1,054
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 »

Add to briefcase 12345»»»

Permissions Expand / Collapse