Adding Not Null with Default column to large Table

I am running an upgrade to an existing system which I have found is using the
Alter Table statement to add a Not Null with Default column to huge table
(100 million records). Not surprisingly it is taking rather a long time!
Can anyone give me any other options to perform this operation ?
I'm currently thinking about
1) Adding the column Nullable
2) Running multiple updates to the table
3) Altering the column to make it Not-Nullable.I don't think there's any way to get a performance boost, if that's what
you're after... Perhaps you could re-index the clustered index for the table
first with a large fillfactor. The ALTER may be going slowly because your
index pages are very full and adding the new column is forcing a lot of page
splits. But re-indexing the table may take just as long.
"Neil K" <Neil K@.discussions.microsoft.com> wrote in message
news:6037A939-4461-427E-8869-69B3171FA96F@.microsoft.com...
> I am running an upgrade to an existing system which I have found is using
the
> Alter Table statement to add a Not Null with Default column to huge table
> (100 million records). Not surprisingly it is taking rather a long time!
> Can anyone give me any other options to perform this operation ?
> I'm currently thinking about
> 1) Adding the column Nullable
> 2) Running multiple updates to the table
> 3) Altering the column to make it Not-Nullable.
>|||Not sure if you can set the constrainsts to nocheck, alter the column then
set the contraints to check
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:O2fPinbkEHA.1048@.tk2msftngp13.phx.gbl...
>I don't think there's any way to get a performance boost, if that's what
> you're after... Perhaps you could re-index the clustered index for the
> table
> first with a large fillfactor. The ALTER may be going slowly because your
> index pages are very full and adding the new column is forcing a lot of
> page
> splits. But re-indexing the table may take just as long.
>
> "Neil K" <Neil K@.discussions.microsoft.com> wrote in message
> news:6037A939-4461-427E-8869-69B3171FA96F@.microsoft.com...
>> I am running an upgrade to an existing system which I have found is using
> the
>> Alter Table statement to add a Not Null with Default column to huge table
>> (100 million records). Not surprisingly it is taking rather a long time!
>> Can anyone give me any other options to perform this operation ?
>> I'm currently thinking about
>> 1) Adding the column Nullable
>> 2) Running multiple updates to the table
>> 3) Altering the column to make it Not-Nullable.
>|||"Gene Black" <geblack@.hotmail.com> wrote in message
news:uN%23TUvdkEHA.1348@.TK2MSFTNGP15.phx.gbl...
> Not sure if you can set the constrainsts to nocheck, alter the column then
> set the contraints to check
Not a bad idea, but apparently constraints aren't checked when you do an
ALTER:
create table #a (id int)
insert #a values (2)
alter table #a with nocheck add constraint b check (id = 1)
alter table #a add b varchar(20) not null default('abc')|||Interesting suggestions ... thanks guys.
If I attempt my original plan of
1) Add column Nullable.
2) Run Batch updates for new column to Non-Null Value
3) Alter column to make non-null with default
Will the last step still take a long time to run , even though no data is
being added or amended, or will the Add Constraint Check be ignored ?|||"Neil K" <NeilK@.discussions.microsoft.com> wrote in message
news:3B4AF7FB-6138-4144-8B80-B014FCC18633@.microsoft.com...
> If I attempt my original plan of
> 1) Add column Nullable.
> 2) Run Batch updates for new column to Non-Null Value
> 3) Alter column to make non-null with default
> Will the last step still take a long time to run , even though no data is
> being added or amended, or will the Add Constraint Check be ignored ?
It should only take as long as a full table scan.
To simulate it, you could try:
SELECT COUNT(*)
FROM YourTable
WHERE SomeNonIndexedCol = SomeNonIndexedCol --Same column name
This will force a full table scan. You can add a NOLOCK hint or run it
with a READ UNCOMMITTED isolation level to make sure it doesn't interfere
with other processes.

Adding Not Null with Default column to large Table

I am running an upgrade to an existing system which I have found is using the
Alter Table statement to add a Not Null with Default column to huge table
(100 million records). Not surprisingly it is taking rather a long time!
Can anyone give me any other options to perform this operation ?
I'm currently thinking about
1) Adding the column Nullable
2) Running multiple updates to the table
3) Altering the column to make it Not-Nullable.
I don't think there's any way to get a performance boost, if that's what
you're after... Perhaps you could re-index the clustered index for the table
first with a large fillfactor. The ALTER may be going slowly because your
index pages are very full and adding the new column is forcing a lot of page
splits. But re-indexing the table may take just as long.
"Neil K" <Neil K@.discussions.microsoft.com> wrote in message
news:6037A939-4461-427E-8869-69B3171FA96F@.microsoft.com...
> I am running an upgrade to an existing system which I have found is using
the
> Alter Table statement to add a Not Null with Default column to huge table
> (100 million records). Not surprisingly it is taking rather a long time!
> Can anyone give me any other options to perform this operation ?
> I'm currently thinking about
> 1) Adding the column Nullable
> 2) Running multiple updates to the table
> 3) Altering the column to make it Not-Nullable.
>
|||Not sure if you can set the constrainsts to nocheck, alter the column then
set the contraints to check
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:O2fPinbkEHA.1048@.tk2msftngp13.phx.gbl...
>I don't think there's any way to get a performance boost, if that's what
> you're after... Perhaps you could re-index the clustered index for the
> table
> first with a large fillfactor. The ALTER may be going slowly because your
> index pages are very full and adding the new column is forcing a lot of
> page
> splits. But re-indexing the table may take just as long.
>
> "Neil K" <Neil K@.discussions.microsoft.com> wrote in message
> news:6037A939-4461-427E-8869-69B3171FA96F@.microsoft.com...
> the
>
|||"Gene Black" <geblack@.hotmail.com> wrote in message
news:uN%23TUvdkEHA.1348@.TK2MSFTNGP15.phx.gbl...
> Not sure if you can set the constrainsts to nocheck, alter the column then
> set the contraints to check
Not a bad idea, but apparently constraints aren't checked when you do an
ALTER:
create table #a (id int)
insert #a values (2)
alter table #a with nocheck add constraint b check (id = 1)
alter table #a add b varchar(20) not null default('abc')
|||Interesting suggestions ... thanks guys.
If I attempt my original plan of
1) Add column Nullable.
2) Run Batch updates for new column to Non-Null Value
3) Alter column to make non-null with default
Will the last step still take a long time to run , even though no data is
being added or amended, or will the Add Constraint Check be ignored ?
|||"Neil K" <NeilK@.discussions.microsoft.com> wrote in message
news:3B4AF7FB-6138-4144-8B80-B014FCC18633@.microsoft.com...
> If I attempt my original plan of
> 1) Add column Nullable.
> 2) Run Batch updates for new column to Non-Null Value
> 3) Alter column to make non-null with default
> Will the last step still take a long time to run , even though no data is
> being added or amended, or will the Add Constraint Check be ignored ?
It should only take as long as a full table scan.
To simulate it, you could try:
SELECT COUNT(*)
FROM YourTable
WHERE SomeNonIndexedCol = SomeNonIndexedCol --Same column name
This will force a full table scan. You can add a NOLOCK hint or run it
with a READ UNCOMMITTED isolation level to make sure it doesn't interfere
with other processes.

Adding Not Null with Default column to large Table

I am running an upgrade to an existing system which I have found is using th
e
Alter Table statement to add a Not Null with Default column to huge table
(100 million records). Not surprisingly it is taking rather a long time!
Can anyone give me any other options to perform this operation ?
I'm currently thinking about
1) Adding the column Nullable
2) Running multiple updates to the table
3) Altering the column to make it Not-Nullable.I don't think there's any way to get a performance boost, if that's what
you're after... Perhaps you could re-index the clustered index for the table
first with a large fillfactor. The ALTER may be going slowly because your
index pages are very full and adding the new column is forcing a lot of page
splits. But re-indexing the table may take just as long.
"Neil K" <Neil K@.discussions.microsoft.com> wrote in message
news:6037A939-4461-427E-8869-69B3171FA96F@.microsoft.com...
> I am running an upgrade to an existing system which I have found is using
the
> Alter Table statement to add a Not Null with Default column to huge table
> (100 million records). Not surprisingly it is taking rather a long time!
> Can anyone give me any other options to perform this operation ?
> I'm currently thinking about
> 1) Adding the column Nullable
> 2) Running multiple updates to the table
> 3) Altering the column to make it Not-Nullable.
>|||Not sure if you can set the constrainsts to nocheck, alter the column then
set the contraints to check
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:O2fPinbkEHA.1048@.tk2msftngp13.phx.gbl...
>I don't think there's any way to get a performance boost, if that's what
> you're after... Perhaps you could re-index the clustered index for the
> table
> first with a large fillfactor. The ALTER may be going slowly because your
> index pages are very full and adding the new column is forcing a lot of
> page
> splits. But re-indexing the table may take just as long.
>
> "Neil K" <Neil K@.discussions.microsoft.com> wrote in message
> news:6037A939-4461-427E-8869-69B3171FA96F@.microsoft.com...
> the
>|||"Gene Black" <geblack@.hotmail.com> wrote in message
news:uN%23TUvdkEHA.1348@.TK2MSFTNGP15.phx.gbl...
> Not sure if you can set the constrainsts to nocheck, alter the column then
> set the contraints to check
Not a bad idea, but apparently constraints aren't checked when you do an
ALTER:
create table #a (id int)
insert #a values (2)
alter table #a with nocheck add constraint b check (id = 1)
alter table #a add b varchar(20) not null default('abc')|||Interesting suggestions ... thanks guys.
If I attempt my original plan of
1) Add column Nullable.
2) Run Batch updates for new column to Non-Null Value
3) Alter column to make non-null with default
Will the last step still take a long time to run , even though no data is
being added or amended, or will the Add Constraint Check be ignored ?|||"Neil K" <NeilK@.discussions.microsoft.com> wrote in message
news:3B4AF7FB-6138-4144-8B80-B014FCC18633@.microsoft.com...
> If I attempt my original plan of
> 1) Add column Nullable.
> 2) Run Batch updates for new column to Non-Null Value
> 3) Alter column to make non-null with default
> Will the last step still take a long time to run , even though no data is
> being added or amended, or will the Add Constraint Check be ignored ?
It should only take as long as a full table scan.
To simulate it, you could try:
SELECT COUNT(*)
FROM YourTable
WHERE SomeNonIndexedCol = SomeNonIndexedCol --Same column name
This will force a full table scan. You can add a NOLOCK hint or run it
with a READ UNCOMMITTED isolation level to make sure it doesn't interfere
with other processes.

Adding default value to an already created table using query analyzer

Hello,

How can I give default value to a field in a table which is already created, i.e. there is a table test and it have field test1 which is int(4). Now, I want to give a default value 0 to this field. As I am not able to access Enterprise Manager, I want to do it using Query Analyzer. How can I do this using Query Analyzer?

Thanks in advance,
Uday.just run a query saying

 update yourtable set yourcol=0 where yourcol is null

hth|||Hi,

That is OK for the data that is entered but what about the new data that will get entered, i.e. I want to set default value as 0 for that particular field. So if someone enter new data and the value for that particular field is not entered, it should take that value as 0.

Best Regards,
Uday.|||ALTER TableName
ALTER COLUMN column_name
DEFAULT 0 WITH VALUES|||Hi,

I tried the above but it is giving me the following error:
Incorrect syntax near the keyword 'DEFAULT'

Best Regards,
Uday|||yea i thgt you already set the default value for the column as 0 and want to modify xisting rows with null values to default to 0. if you havent already you can do it now. so for any new records added if the value is not supplied it will default to 0.

hth|||ALTER TABLE MyTable
ADD AddDate smalldatetime NULL
CONSTRAINT AddDateDflt
DEFAULT getdate() WITH VALUES
-------------
That's books online says. Usually works fine with adding columns, but I don't see why it's not letting me alter. I guess you have to drop the current constraint, and recreate one.

Adding Default Dates within Reporting Services Admin

Is there any way, within the admin to specify that
yesterday and today for start date and end date. Someone
suggested =Today.AddDays(-1) but I don't think they meant
in the admin. Could you tell me where i am supposed to
apply this default.
Regards,
BryanIf by admin you mean through the report manager, then there is no way. The
report manager does not support entering expressions.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"bmurtha" <anonymous@.discussions.microsoft.com> wrote in message
news:9f5001c4791a$bf4e8040$a301280a@.phx.gbl...
> Is there any way, within the admin to specify that
> yesterday and today for start date and end date. Someone
> suggested =Today.AddDays(-1) but I don't think they meant
> in the admin. Could you tell me where i am supposed to
> apply this default.
> Regards,
> Bryan

Adding DEFAULT columns

Hi

I have a table that currently has 466 columns and about 700,000
records. Adding a new DEFAULT column to this table takes a long time.

It it a lot faster to recreate the table with the new columns and then
copy all of the data across.

As far as I am aware when you add a DEFAULT column the following
happens:

a) The column is added with a NULL property
b) Each row is updated to be set to the DEFAULT value
c) The column is changed to NOT NULL.

However, adding the column as NOT NULL with the DEFAULT seems to take a
lot longer than if I do steps a) - c) separately.

When I say a long time, adding just a single DEFAULT column takes
around 6 hours. Surely it should not take this long?

There is a trigger on this table but disabling this does not seem to
make much difference.

Can anybody give me any advice on the use of DEFAULT columns please?
When should they be used, benefits, disadvantages, alternatives etc.
Also should it really take as long as it is taking or is there a
problem with my setup?

If I am honest I can't see why DEFAULT columns should be used as the
values could always be inserted explicitly via the application
Thanks in Advance.

PaulI think you may be able to speed it up by using the NoCheck option, so it
doesn't look at the existing data.

I didn't know that specifying a default updates the null values in the
table - so you are either incorrect, or I have an incomplete understanding.

But I am pretty sure that having so many columns isn't helping you in the
least bit, and may be the real reason.

Splitting the tables apart and using a view to be backwards compatible (with
an 'instead of ' trigger for updates) - aught to make things go faster.
Especially if all or the most frequent searchable columns stay in one of the
new tables (a hub table as it were).

If the data values are ALWAYS explicitly added ALL the time, there is no
reason for a default.
You may need to beat up on some wayward programmer to guarantee that they
get populated each and every time with the correct values - But since I
don't like violence (nor the testing to find the problem), I add defaults
to guarantee that happens regardless. Also it is possible to do an insert
without a column list and specify defaults (so that all the columns get the
default values), which could be useful in some instances.

"Paul" <paulwragg2323@.hotmail.com> wrote in message
news:1103024817.227784.219090@.z14g2000cwz.googlegr oups.com...
> Hi
> I have a table that currently has 466 columns and about 700,000
> records. Adding a new DEFAULT column to this table takes a long time.
> It it a lot faster to recreate the table with the new columns and then
> copy all of the data across.
> As far as I am aware when you add a DEFAULT column the following
> happens:
> a) The column is added with a NULL property
> b) Each row is updated to be set to the DEFAULT value
> c) The column is changed to NOT NULL.
> However, adding the column as NOT NULL with the DEFAULT seems to take a
> lot longer than if I do steps a) - c) separately.
> When I say a long time, adding just a single DEFAULT column takes
> around 6 hours. Surely it should not take this long?
> There is a trigger on this table but disabling this does not seem to
> make much difference.
> Can anybody give me any advice on the use of DEFAULT columns please?
> When should they be used, benefits, disadvantages, alternatives etc.
> Also should it really take as long as it is taking or is there a
> problem with my setup?
> If I am honest I can't see why DEFAULT columns should be used as the
> values could always be inserted explicitly via the application
> Thanks in Advance.
> Paul|||Thanks for the response David.

I have suggested that we split this table up and I think this will be
eventually done (it's a case of having the time up front to do this).

I wasn't saying that adding a DEFAULT value to the column updates
existing data - rather that when a new DEFAULT column is added it
follows the steps a) - c) in order to add the new column.

As the table is so large I think I need to investigate the way the data
for this table is actually stored. At present we have no clustered
index on this table which is probably also contributing to the problem.
I don't know too much about the way the data is stored to be honest!|||OK, I get it now, you are adding a column and not just binding a new default
to an existing column.

Copy into the new table, drop the original, and do a sp_rename.
While you are at it break the table apart - If you can't find the time to do
it right, when will you find the time to do over and over incorrectly.

Get one of those MCSD prep books for the SQL Server Design Exam to find out
how stuff gets stored in a database. Their 1st or 2nd chapter normally goes
over devices, extents, pages and all that stuff.

I am sure there are a boat load of free sources on the Web on that as well.

"Paul" <paulwragg2323@.hotmail.com> wrote in message
news:1103033046.110249.69780@.f14g2000cwb.googlegro ups.com...
> Thanks for the response David.
> I have suggested that we split this table up and I think this will be
> eventually done (it's a case of having the time up front to do this).
> I wasn't saying that adding a DEFAULT value to the column updates
> existing data - rather that when a new DEFAULT column is added it
> follows the steps a) - c) in order to add the new column.
> As the table is so large I think I need to investigate the way the data
> for this table is actually stored. At present we have no clustered
> index on this table which is probably also contributing to the problem.
> I don't know too much about the way the data is stored to be honest!|||Paul (paulwragg2323@.hotmail.com) writes:
> It it a lot faster to recreate the table with the new columns and then
> copy all of the data across.

Is that a question or a statement? Which "It" is a typo for "is"?

> When I say a long time, adding just a single DEFAULT column takes
> around 6 hours. Surely it should not take this long?

Just because "ALTER TABLE tbl ADD col DEFAULT 0" is easy to type, that
does not mean that it executes equally fast. There is a lot of work to
be done - since all rows expand, basically all pages have to be written.

In our shop we do all table changes the long way - rename, create new,
insert over, move foreign keys, drop old. We have a build that generates
a skeleton for this manoeuvre. One reason we do this is that ALTER TABLE
only can handle some changes, and you can not insert columns in the
middle with. (And our scheme was established in 6.5 when you could do
even less with ALTER TABLE.)

Generally, I would not expect reload of a 700000 rows table, not even
that wide to take six hours. Also, when moving over, you can do that
in chunks.

> There is a trigger on this table but disabling this does not seem to
> make much difference.

The trigger is not fired when you to ALTER TABLE. Note that if you do
the long way, you will need to recreate the trigger. Whether you do
that before or after you reload the data depends on whether you want
the checks in the trigger to be performed (I usually want to). But for
performance, it's best to recreate the trigger after the data move.

> Can anybody give me any advice on the use of DEFAULT columns please?
> When should they be used, benefits, disadvantages, alternatives etc.
> Also should it really take as long as it is taking or is there a
> problem with my setup?

If you need to add to existing column to a database, and you don't want
NULL values in the column, the a default value is a good way to go, to
avoid problems with existing software that writes to this table. And,
even if existing software is rewritten - it may after all be a single
GUI form - existing data needs to be handled.

Sometimes NULL values can be feasible, but for instance a bit column
is typically NOT NULL. I think the choice should be made from the
anticpated use in the future, and not what is the most convenient
right now.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||A technique that I sometimes use with large tables is SELECT ... INTO
followed by a drop and a rename. This is minimally logged in the SIMPLE or
BULK_LOGGED recovery model.

Whether or not this is faster depends on the particulars of the changes made
and the indexes that need to be rebuilt.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Paul" <paulwragg2323@.hotmail.com> wrote in message
news:1103024817.227784.219090@.z14g2000cwz.googlegr oups.com...
> Hi
> I have a table that currently has 466 columns and about 700,000
> records. Adding a new DEFAULT column to this table takes a long time.
> It it a lot faster to recreate the table with the new columns and then
> copy all of the data across.
> As far as I am aware when you add a DEFAULT column the following
> happens:
> a) The column is added with a NULL property
> b) Each row is updated to be set to the DEFAULT value
> c) The column is changed to NOT NULL.
> However, adding the column as NOT NULL with the DEFAULT seems to take a
> lot longer than if I do steps a) - c) separately.
> When I say a long time, adding just a single DEFAULT column takes
> around 6 hours. Surely it should not take this long?
> There is a trigger on this table but disabling this does not seem to
> make much difference.
> Can anybody give me any advice on the use of DEFAULT columns please?
> When should they be used, benefits, disadvantages, alternatives etc.
> Also should it really take as long as it is taking or is there a
> problem with my setup?
> If I am honest I can't see why DEFAULT columns should be used as the
> values could always be inserted explicitly via the application
> Thanks in Advance.
> Paul

adding dbo to db_owner

Im duplicating a database by running the script below. This works fine. My only problem is that the dbo user does not by default have any role memberships in the new database hence no access. I have tried using sp_addrolemember but dbo is not a valid user for this procedure. Adding dbo to the db_owner role through the sql2005 MS works fine, but I would very much like to script this. Any suggestions?

--copy database
use master;
alter database polaris_regular set single_user with rollback immediate;
DROP DATABASE polaris_regular;
backup database polaris to disk = 'c:\tmp\polarisbak.bak' with INIT,format;
restore filelistonly from disk = 'c:\tmp\polarisbak.bak';
restore database polaris_regular from disk = 'c:\tmp\polarisbak.bak'
with move 'polaris' to 'C:\Data\polaris_regular.mdf',
move 'polarisLog' to 'C:\Data\polaris_regularLog.mdf';dbo is always a member of db_owner. You should never have to add it explicitly.|||you may want prefix your objects with [dbo], like [dbo].[polaris_regular]|||Thanks guys. Somehow I can't reproduce the situation, so the problem might have been something else.

Adding additional fields to ASPNETDB.mdf

I am attempting to add additional fields and data to the default users database that is created as a result of enabling roles on my website. Is it possible to add additional data fields to this file? Where can I find the commands to do this?

Hey,

Welcome to the forum. Sure, it's just a database; however, depending on the changes you make, you may break the existing SQL providers code, and then it won't work... You will have to provide customization to get the features you want out of it.

|||

You can store additional user data in your application using the ASP.NET profile object. If you've already enabled Roles, the basic process is...

1. Add Profile Properties to your Web.Config file.
<profile>
<properties>
<addname="FirstName"type="string" />
<addname="LastName"type="string" />
<addname="samAccountName"type="string" />
<addname="DisplayName"type="string" />
<addname="Email"type="string" />
</properties>
</profile>

2. Populate the user's Profile (you might do this when the user logs on to your app)
Profile.samAccountName = strSamAccountName
Profile.FirstName = strFirstName
Profile.LastName = strLastName
Profile.DisplayName = strFirstName &" " & strLastName
Profile.Email = strEmail

Scott Mitchell wrote a very useful 7 part article on the process athttp://aspnet.4guysfromrolla.com/articles/120705-1.aspx.
Microsoft's has a good starter article as wellhttp://msdn2.microsoft.com/en-us/library/ms379605(VS.80).aspx.