0

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?

Improve this question

1 Answer 1

Reset to default
3

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.

Improve this answer
10
  • 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, 2013 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.
    – Kuberchaun
    Dec 9, 2013 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, 2013 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.
    – Kuberchaun
    Dec 9, 2013 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.
    – Kuberchaun
    Dec 9, 2013 at 18:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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