Adding a bit column to existing table.

How is storage allocated when an existing table is altered to add a bit
column? I think I read somewhere that the first bit column allocates a byte
and subsequent bit columns are included in that existing byte. Is that true?
Snake you must have one hellaciously big table! ;-)
"Snake" <Snake@.discussions.microsoft.com> wrote in message
news:AA6F5E58-5FB6-4E14-A58B-A9F5F648413F@.microsoft.com...
> How is storage allocated when an existing table is altered to add a bit
> column? I think I read somewhere that the first bit column allocates a
> byte
> and subsequent bit columns are included in that existing byte. Is that
> true?
|||I'm sorry, but I don't understand your response.
"Jerry Spivey" wrote:

> Snake you must have one hellaciously big table! ;-)
> "Snake" <Snake@.discussions.microsoft.com> wrote in message
> news:AA6F5E58-5FB6-4E14-A58B-A9F5F648413F@.microsoft.com...
>
>
|||The minimum allocation size used is a byte. A bit column requires one
bit for the NULL-bitmap, and one bit for the data storage. The 1st, 9th,
17th, etc. column that is added (regardless of data type) will allocate
a new byte for the NULL-bitmap. The 1st, 9th, 17th, etc. bit column will
allocate a new byte for the data storage.
Gert-Jan
Snake wrote:
> How is storage allocated when an existing table is altered to add a bit
> column? I think I read somewhere that the first bit column allocates a byte
> and subsequent bit columns are included in that existing byte. Is that true?

Adding a bit column to existing table.

How is storage allocated when an existing table is altered to add a bit
column? I think I read somewhere that the first bit column allocates a byte
and subsequent bit columns are included in that existing byte. Is that true?Snake you must have one hellaciously big table! ;-)
"Snake" <Snake@.discussions.microsoft.com> wrote in message
news:AA6F5E58-5FB6-4E14-A58B-A9F5F648413F@.microsoft.com...
> How is storage allocated when an existing table is altered to add a bit
> column? I think I read somewhere that the first bit column allocates a
> byte
> and subsequent bit columns are included in that existing byte. Is that
> true?|||I'm sorry, but I don't understand your response.
"Jerry Spivey" wrote:
> Snake you must have one hellaciously big table! ;-)
> "Snake" <Snake@.discussions.microsoft.com> wrote in message
> news:AA6F5E58-5FB6-4E14-A58B-A9F5F648413F@.microsoft.com...
> > How is storage allocated when an existing table is altered to add a bit
> > column? I think I read somewhere that the first bit column allocates a
> > byte
> > and subsequent bit columns are included in that existing byte. Is that
> > true?
>
>|||The minimum allocation size used is a byte. A bit column requires one
bit for the NULL-bitmap, and one bit for the data storage. The 1st, 9th,
17th, etc. column that is added (regardless of data type) will allocate
a new byte for the NULL-bitmap. The 1st, 9th, 17th, etc. bit column will
allocate a new byte for the data storage.
Gert-Jan
Snake wrote:
> How is storage allocated when an existing table is altered to add a bit
> column? I think I read somewhere that the first bit column allocates a byte
> and subsequent bit columns are included in that existing byte. Is that true?

Adding a bit column to existing table.

How is storage allocated when an existing table is altered to add a bit
column? I think I read somewhere that the first bit column allocates a byte
and subsequent bit columns are included in that existing byte. Is that true
?Snake you must have one hellaciously big table! ;-)
"Snake" <Snake@.discussions.microsoft.com> wrote in message
news:AA6F5E58-5FB6-4E14-A58B-A9F5F648413F@.microsoft.com...
> How is storage allocated when an existing table is altered to add a bit
> column? I think I read somewhere that the first bit column allocates a
> byte
> and subsequent bit columns are included in that existing byte. Is that
> true?|||I'm sorry, but I don't understand your response.
"Jerry Spivey" wrote:

> Snake you must have one hellaciously big table! ;-)
> "Snake" <Snake@.discussions.microsoft.com> wrote in message
> news:AA6F5E58-5FB6-4E14-A58B-A9F5F648413F@.microsoft.com...
>
>|||The minimum allocation size used is a byte. A bit column requires one
bit for the NULL-bitmap, and one bit for the data storage. The 1st, 9th,
17th, etc. column that is added (regardless of data type) will allocate
a new byte for the NULL-bitmap. The 1st, 9th, 17th, etc. bit column will
allocate a new byte for the data storage.
Gert-Jan
Snake wrote:[vbcol=seagreen]
> How is storage allocated when an existing table is altered to add a bit
> column? I think I read somewhere that the first bit column allocates a by
te
> and subsequent bit columns are included in that existing byte. Is that true?[/vbc
ol]

Adding a BIT column - 100+ hours!

I previously posted about a problem where I added a non-NULL DEFAULT 0 bit column to a table with 80 million records. It was taking a LONG time and we needed that database up fast. It ended up taking a total of 17 hours.

Now my coworker added the same non-NULL DEFAULT 0 bit column to another table on another important server. But this table has more like 400 million rows. It's been running for 100+ hours and is still going. We were hoping it would scale linearly (5*80 million records would hopefully take 5*17 hours) but that isn't happening. I have no idea how much longer it will take. I really need this to be done. I'm tempted to cancel but that will incur a potentially massive rollback, right? Any guestimate on how large that would be?

Any ideas?ALTER TABLE ADD...NOT NULL DEFAULT... is a fully logged operation. The logs that contain internal page updates are small but numerous (and I use this word loosely). There is nothing you can do at this point because as you mentioned correctly, - the rollback will take even longer. And as I stated in one of my replies to you previous similar post, - if you kill the service the database recovery will take forever (weeks possibly). There is however a rather drastic approach that may (!!!) yield a quick return your database to pre-ALTER state...I would not recommend you trying it simply because it may or may not succeed, and if it doesn't, - someone is gonna be fired...|||Why would someone get fired? I mean, of course they made a backup of the database before running such a drastic operation. Right?......|||I still don't know why you made a column NOT Nullable for 400 million rows...

And the last thread was filled with a lot of good advice...

why didn't you heed any of it?|||Why would someone get fired? I mean, of course they made a backup of the database before running such a drastic operation. Right?......
I'd hope so...Did they?|||I still don't know why you made a column NOT Nullable for 400 million rows...

And the last thread was filled with a lot of good advice...

why didn't you heed any of it?

From a design perspective, the column should be non-nullable. I'm sure we could deal with it as a NULLable column if absolutely necessary. That's a sacrifice I'd easily be willing to make but at this point it's too late.

As I said, I didn't do this and it's frustrating from my perspective. We really didn't need the column on this table in the first place and it was a without question a bad move. If he started both column add operations at the same time before he realized what a drastic operation this would be then I can understand his perspective. Believe me, I am using the info I learn here, and trying to convey what I learn to my team.

Thanks you both for the feedback! I will not cancel, let it run, just cross my fingers that it finishes in the next week, and I will try to convince my boss to give me yet another database server so that we can start over (not fun) as a parallel strategy. Thankfully, this was done on a secondary system where we are trying to reconstruct our primary database with a slightly improved process (bug fixes and better data gathering).|||Why would someone get fired? I mean, of course they made a backup of the database before running such a drastic operation. Right?......

LOL! No, no backups.

For some reason I feel way more stressed about this than either my boss or the worker who is assisting on this project.|||Then definitely I'm not telling anything about "the other" way...Does you DBA have any say in all this?|||Then definitely I'm not telling anything about "the other" way...Does you DBA have any say in all this?

"The other" way? :) Mysterious...

I just emailed our part time DBA but I'm not counting on much. Unfortunately, we are mostly programmers.|||Threads like this just make me speechless.

It's hard to talk when you're rolling on the floor laughing.sql

Add new column to a replicated table..

Hi,
I need to add a column to a table that is being replicated.
Can I just do the ALTER TABLE ADD xxxx bit then make a call out to
sp_replAddColumn?
When I do so, the sp fails saying the column exists. The manual says that it
adds the column to the publications, but does not suggest it does the
physical add tot the table..
Thanks in advance,
SPGYou use sp_replAddColumn INSTEAD of using alter table add column... the Sp
adds the column to the table, and fixes replication... since you manually
added the column the sp fails.... drop the column and use the SP only.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"SPG" <steve.nospoo.goodsell@.nopooblueyonder.co.nopoo.uk> wrote in message
news:r9IQd.16027$8B3.14345@.text.news.blueyonder.co.uk...
> Hi,
> I need to add a column to a table that is being replicated.
> Can I just do the ALTER TABLE ADD xxxx bit then make a call out to
> sp_replAddColumn?
> When I do so, the sp fails saying the column exists. The manual says that
> it adds the column to the publications, but does not suggest it does the
> physical add tot the table..
> Thanks in advance,
> SPG
>|||Thanks for that..
I sort of half guessed that when the sp failed, but the docs are not too
clear on this..
Steve
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:%23oEXG8CFFHA.2568@.TK2MSFTNGP10.phx.gbl...
> You use sp_replAddColumn INSTEAD of using alter table add column... the
> Sp adds the column to the table, and fixes replication... since you
> manually added the column the sp fails.... drop the column and use the SP
> only.
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "SPG" <steve.nospoo.goodsell@.nopooblueyonder.co.nopoo.uk> wrote in message
> news:r9IQd.16027$8B3.14345@.text.news.blueyonder.co.uk...
>

Add composite primary key

I'm a bit unclear on the syntax to add a composite (multiple columns)
primary key to a table. The columns already exist. Thanks.ALTER TABLE dbo.TableName ADD CONSTRAINT
PK_ConstraintName PRIMARY KEY NONCLUSTERED
(
Col1,
Col2
) ON [PRIMARY]
When in doubt, you can always perform the action in Enterprise Manager
and then look at the change script to help clear up basic syntax
things.
HTH,
Stu|||Do:
ALTER TABLE tbl ADD CONSTRAINT pk PRIMARY KEY ( col1, col2 )
Anith|||Roughly this at table creation:
CONSTRAINT PK_NameIt PRIMARY KEY CLUSTERED (Column1, Column2, Column3)
Obviously if you need a NONCLUSTERED PK, use that syntax instead.
Mark
"Rick Charnes" <rickxyz--nospam.zyxcharnes@.thehartford.com> wrote in message
news:MPG.1dc2cab9b0d541d8989902@.msnews.microsoft.com...
> I'm a bit unclear on the syntax to add a composite (multiple columns)
> primary key to a table. The columns already exist. Thanks.

Add Category to SQL Full-text

I'm moving my STS sites to WSS, and I'm running into a bit of a
problem. We've got everyone on our team adding a category to their
documents to help us search and organize them. However, seach under
WSS (w/ SQL and Free-Text search) doesn't pick up the category field
in my Word documents.
I saw some talk about adding it via SQL so it appears in searchs
through Sharepoint, but I've no idea how. Any suggestions or
instructions that could help me out here?
that's correct. SQL FTS only indexes documents contents when they are stored
in the image data type columns. It doesn't index properties. You would have
to extract the property and store it separately in a column in the table you
are full text indexing.
"Elroy" <derekvof@.yahoo.com> wrote in message
news:47f03009.0403231310.5344561b@.posting.google.c om...
> I'm moving my STS sites to WSS, and I'm running into a bit of a
> problem. We've got everyone on our team adding a category to their
> documents to help us search and organize them. However, seach under
> WSS (w/ SQL and Free-Text search) doesn't pick up the category field
> in my Word documents.
> I saw some talk about adding it via SQL so it appears in searchs
> through Sharepoint, but I've no idea how. Any suggestions or
> instructions that could help me out here?