Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I want to execute this statement in a production database to alter the length of a varchar column from 50 to 72:

ALTER TABLE Users ALTER COLUMN LoginID varchar(72) NOT NULL

The column is not null so I'm NOT altering column nullability from NULL to NOT NULL.

Database server is SQL Server 2008 R2; table has about 60,000 records.

Do I have to put the alter statment within begin tran commit tran?

share|improve this question
add comment

1 Answer

up vote 3 down vote accepted

The statement is atomic and is its own transaction, so no, there is no value in surrounding it with BEGIN TRANSACTION; / COMMIT TRANSACTION;. It will either succeed in whole or fail in whole.

share|improve this answer
    
Also it will try to take a full table lock for the duration, so other transactions will either block until it is finished or may block it from proceeding until they are finished with their locks on that resource. –  David Spillett Dec 9 '13 at 18:10
    
I would say there is some value putting in the begin and end tran in that it's explicit vs implied so that others reading your code aren't wondering if it's in a transaction or not. –  Bob Dec 9 '13 at 18:15
2  
@StarShip3000 any single statement is an atomic operation - no single statement can partially commit or rollback. I don't really comprehend what value you think it adds to wrap them; by extension do you think you should wrap every single statement not already involved in a larger transaction with BEGIN TRANSACTION / COMMIT TRANSACTION? Do you also name your tables tblWhatever so that nobody can get confused about them not being tables, or a column intCustomerID in case someone might assume that it's char or some other data type? I'm all for self-documenting code, within reason. –  Aaron Bertrand Dec 9 '13 at 18:18
    
I honestly don't see how you can't comprehend this. I work in a shop with a mix of DBA and developers of various database skill. We have found that having code templates that cover all cases to be the best. So in our case having BEGIN and END trans for all DML templates works best because if they add more than one DDL to the script it will all fail or succeed and the template keeps us from dealing with people coming up with clever solutions to a simple problem. –  Bob Dec 9 '13 at 18:21
1  
I can read rather well. "Should I use begin tran to do an alter table.." +1 to your answer as it's correct I was just trying to add a little side information, good day. –  Bob Dec 9 '13 at 18:28
show 5 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.