Alter command

Question

Alter command

pujain

SSCrazy

Points: 2903
More actions
June 9, 2010 at 3:58 pm

#143578

Hi All,
i need to alter multiple column in from null to not null in production server.
table have millions of records, and taking each alter is taking hours to run. can anybody suggest me better option...
ALTER TABLE Panelist ALTER Column "RegSourceID" int not null ALTER TABLE Panelist ALTER Column "CultureID" int not null ALTER TABLE Panelist ALTER Column "EmailStatusTypeID" int not null
thanks in advance
Puneet

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

Do you know which columns need to be altered? Or are you changing every single column to Not NULL?

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

pujain SSCrazy Points: 2903 More actions · Answer 2

Yes i know all those column which needs to be converted to Not Null

SQLRNNR SSC Guru Points: 281334 More actions · Answer 3

Here is a quick and dirty that you could run to create all of your statements.

With MassAlter as (

Select Table_Schema,Table_Name,Column_Name,Case

When IsNull(Character_Maximum_length,0) = 0

Then Data_Type

Else Convert(Varchar(40),Data_Type + '(' + Convert(Varchar(20),Character_Maximum_Length) + ')')

End as Data_Type

,IS_NULLABLE

From INFORMATION_SCHEMA.Columns

Where Is_Nullable = 'Yes'

)

Select 'Alter Table ' + TABLE_SCHEMA + '.'+ Table_Name + ' Alter Column ' + Column_Name + ' ' +

Data_Type + ' Not Null'

From MassAlter

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

pujain SSCrazy Points: 2903 More actions · Answer 4

Thanks Alot for this,

can you please tell me a another way through which i can alter all this column as this will take hours to complete.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 5

Two options

Create a cursor that does essentially the same thing I just showed you.

or

Run that script, review the commands, and then set it up in a job to execute in batches. You will need to add ';' or go statements in between the statements.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 6

CirquedeSQLeil (6/9/2010)
Two options
Create a cursor that does essentially the same thing I just showed you.
or
Run that script, review the commands, and then set it up in a job to execute in batches. You will need to add ';' or go statements in between the statements.

Ummmm.... I don't believe that's what the OP means, Jason. I believe the OP wants to know if all the columns can be altered by a single command so only one pass needs to be made on the table.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SQLRNNR SSC Guru Points: 281334 More actions · Answer 7

Jeff Moden (6/9/2010)
CirquedeSQLeil (6/9/2010)
Two options
Create a cursor that does essentially the same thing I just showed you.
or
Run that script, review the commands, and then set it up in a job to execute in batches. You will need to add ';' or go statements in between the statements.
Ummmm.... I don't believe that's what the OP means, Jason. I believe the OP wants to know if all the columns can be altered by a single command so only one pass needs to be made on the table.

You're probably right.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 8

pujain (6/9/2010)
Hi All,
i need to alter multiple column in from null to not null in production server.
table have millions of records, and taking each alter is taking hours to run. can anybody suggest me better option...
ALTER TABLE Panelist ALTER Column "RegSourceID" int not null ALTER TABLE Panelist ALTER Column "CultureID" int not null ALTER TABLE Panelist ALTER Column "EmailStatusTypeID" int not null
thanks in advance
Puneet

"It depends"...

If you have the ENTERPRISE edition, you could build a new table and use SPLIT to move the data almost instantaneously based on a trick I saw Paul White use recently.

If you don't have the ENTERPRISE edition, you can use SELECT/INTO to both create and populate a new table and then rename the tables. The keys for this method are...

1. If you are using the FULL recovery model, take a full backup (including a transaction log backup, all just to be safe) and temporarily change to the BULK-LOGGED model.

2. Do NOT using SELECT * to do the SELECT/INTO. Instead, list every column name and for the columns you want to have as NOT NULL, use ISNULL() around each of those columns with the appropriate second operand for whatever datatype the column is.

Of course, since it's a new table either way, you'll need to make sure that the correct permissions are granted and indexes, keys, and foreign keys are created as well as going through the table renaming process.

Or... you can use the table designer and it'll actually do all of that for you although it will be a logged process (including the table rename) and take just a little longer.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 9

@jeff

Which would be the better option ?

1. USE ALTER TABLE ....ALTER COLUMN approach

2. Create new table and then push the exisitng table's data into it.

and does sql server use same locking mechanism in both cases ?

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 10

Bhuvnesh (6/9/2010)
@Jeff
Which would be the better option ?
1. USE ALTER TABLE ....ALTER COLUMN approach
2. Create new table and then push the exisitng table's data into it.
and does sql server use same locking mechanism in both cases ?

They're both going to render the table pretty much useless for the duration. However, in light of the fact that the OP wants to change multiple columns, the "Create and Push" method would likely be the fastest even in the STANDARD edition. Using the SPLIT command, it would likely be nearly instantaneous (except for reindexing and rekeying) in the ENTERPRISE edition.

To know for sure, though, you'd have to do an insitu test using a small table of, say, a million or two rows.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 11

@pujain

from below link, i learnt that first we need to update concerned (NULLABLE) columns ( with any dummy values) then we can change them to NOT NULL.

though i am not too sure about that additionally i didnt tested it.

http://sqlserverplanet.com/sql/alter-table-alter-column/

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

pujain SSCrazy Points: 2903 More actions · Answer 12

Hey,

i tried both the method but creating a table and inserting it seems a little better but still not an easy one as i am having a table with 15 million records and 85 columns, creating index to the new one looks a difficult task.

do any body have any suggestion to reduce my headache?

thanks in advance.