June 9, 2010 at 3:58 pm
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
June 9, 2010 at 4:10 pm
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
June 9, 2010 at 4:13 pm
Yes i know all those column which needs to be converted to Not Null
June 9, 2010 at 4:33 pm
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
June 9, 2010 at 4:43 pm
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.
June 9, 2010 at 4:46 pm
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
June 9, 2010 at 10:43 pm
CirquedeSQLeil (6/9/2010)
Two optionsCreate 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
Change is inevitable... Change for the better is not.
June 9, 2010 at 10:50 pm
Jeff Moden (6/9/2010)
CirquedeSQLeil (6/9/2010)
Two optionsCreate 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
June 9, 2010 at 10:57 pm
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
Change is inevitable... Change for the better is not.
June 9, 2010 at 11:29 pm
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;-)
June 10, 2010 at 7:30 am
Bhuvnesh (6/9/2010)
@JeffWhich 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
Change is inevitable... Change for the better is not.
June 11, 2010 at 7:32 am
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;-)
June 14, 2010 at 1:46 am
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.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply