ALTER TABLE table ADD column_name

Hello,
I=B4m trying to add a new column in SQL Server 2000, but=20
in the specific table I don=B4t get, in other tables I get.=20
This table has 7000 rows and when I try, the server get=20
processing and dont=B4t finish. I already wait for 20=20
minutes and nothing.=20
Anyone can help me?
Thanks,
DanielaDaniela
I assume you did it by EM and not by QA
This is T-SQL example how to add a new column
CREATE TABLE Test
(
col INT
)
GO
INSERT INTO Test VALUES (1)
GO
ALTER TABLE Test ADD col1 CHAR(1) NOT NULL DEFAULT 'A'
GO
SELECT * FROM Test
go
DROP TABLE Test
"Daniela" <daniela@.perfilcs.com.br> wrote in message
news:113de01c41015$b05c1980$a501280a@.phx
.gbl...
Hello,
Im trying to add a new column in SQL Server 2000, but
in the specific table I dont get, in other tables I get.
This table has 7000 rows and when I try, the server get
processing and dontt finish. I already wait for 20
minutes and nothing.
Anyone can help me?
Thanks,
Daniela|||I did it by QA. In other tables I get, but only one I=20
don=B4t get. This table has many relations.=20
I don=B4t know what to do... Help.
Thanks,
Daniela

>--Original Message--
>Daniela
>I assume you did it by EM and not by QA
>This is T-SQL example how to add a new column
>CREATE TABLE Test
>(
> col INT
> )
>GO
>INSERT INTO Test VALUES (1)
>GO
>ALTER TABLE Test ADD col1 CHAR(1) NOT NULL DEFAULT 'A'
>GO
>SELECT * FROM Test
>go
>DROP TABLE Test
>
>"Daniela" <daniela@.perfilcs.com.br> wrote in message
> news:113de01c41015$b05c1980$a501280a@.phx
.gbl...
>Hello,
> I=B4m trying to add a new column in SQL Server 2000, but
>in the specific table I don=B4t get, in other tables I get.
>This table has 7000 rows and when I try, the server get
>processing and dont=B4t finish. I already wait for 20
>minutes and nothing.
> Anyone can help me?
> Thanks,
>Daniela
>
>.
>|||Perhaps you are bang blocked by another process. Check current activity
with sp_who.
Hope this helps.
Dan Guzman
SQL Server MVP
"Daniela" <daniela@.perfilcs.com.br> wrote in message
news:113de01c41015$b05c1980$a501280a@.phx
.gbl...
Hello,
Im trying to add a new column in SQL Server 2000, but
in the specific table I dont get, in other tables I get.
This table has 7000 rows and when I try, the server get
processing and dontt finish. I already wait for 20
minutes and nothing.
Anyone can help me?
Thanks,
Daniela|||Hi,
Uri can probably confirm this, but I've always understood that if you change
a table with Enterprise Manager, what it actually does is to copy the data
to a temp table, delete the original, then create a new table with the same
name as the old one, but with the changed properties, then copy all the data
back. This is why a) it can take so long, and b) when you later try to pick
up the table's properties in Query Analyzer it says that the object has been
dropped and re-created, so you have to refresh at the database level.
The most time-effective day I ever spent was learning how to use the ALTER
TABLE commands :-)
Richard
"Daniela" <daniela@.perfilcs.com.br> wrote in message
news:113de01c41015$b05c1980$a501280a@.phx
.gbl...
Hello,
Im trying to add a new column in SQL Server 2000, but
in the specific table I dont get, in other tables I get.
This table has 7000 rows and when I try, the server get
processing and dontt finish. I already wait for 20
minutes and nothing.
Anyone can help me?
Thanks,
Danielasql

Alter table problems (transaction log will growth to 60 Gb...)

I have a large table, and I try alter one column:
ALTER TABLE TABLE_NAME ALTER COLUMN COLUMN_NAME DECIMAL (15,9)
The transaction log will growth to 60 Gb, and then I haven't any more disk
space avalable.
Does the SQL server have any parameter like NOLOGGING
or does somebody have other solutions how I can solve this problem?You could change the database recovery model to simple, make the change, and
then revert the recovery model.
Alternatively, you could create a new table with the column change, and them
move the data to the new table -perhaps in batches, truncating the log in
between.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Major" <lievonen@.jyu.fi.HALOOOOOOOO> wrote in message
news:ODG1jGx0GHA.1252@.TK2MSFTNGP04.phx.gbl...
>I have a large table, and I try alter one column:
> ALTER TABLE TABLE_NAME ALTER COLUMN COLUMN_NAME DECIMAL (15,9)
> The transaction log will growth to 60 Gb, and then I haven't any more disk
> space avalable.
> Does the SQL server have any parameter like NOLOGGING
> or does somebody have other solutions how I can solve this problem?
>
>|||> You could change the database recovery model to simple, make the change,
> and then revert the recovery model.
> Alternatively, you could create a new table with the column change, and
> them move the data to the new table -perhaps in batches, truncating the
> log in between.
OK, thanks. I allready have simple recovery mode in use, so I should try
via new table...|||Also, consider creating the new table using SELECT INTO, which is
minimally-logged in the SIMPLE model.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Major" <lievonen@.jyu.fi.HALOOOOOOOO> wrote in message
news:e$O0zRx0GHA.4976@.TK2MSFTNGP02.phx.gbl...
>> You could change the database recovery model to simple, make the change,
>> and then revert the recovery model.
>> Alternatively, you could create a new table with the column change, and
>> them move the data to the new table -perhaps in batches, truncating the
>> log in between.
> OK, thanks. I allready have simple recovery mode in use, so I should try
> via new table...
>|||Hello,
Even Simple recovery mode will log the activities and will clear the log
once the task is completed. What you can do is
script entire the table including dependents then:-.
1. Using the script create the Table with new name. Do not create index and
triggers
2. Use DTS to load the data into new table. or use BCP OUT and BCP/IN or
BULK insert with Batch commit option to load data
3. Once the load is completed
4. Verify the count of records in both table
5. Truncate the old table
6. Rename the new table to existing using sp_rename
7. Create the indexes and triggers (Step 7 can be done along with step 1
also, but the load will be slightly slow)
Thanks
Hari
SQL Server MVP
"Major" <lievonen@.jyu.fi.HALOOOOOOOO> wrote in message
news:e$O0zRx0GHA.4976@.TK2MSFTNGP02.phx.gbl...
>> You could change the database recovery model to simple, make the change,
>> and then revert the recovery model.
>> Alternatively, you could create a new table with the column change, and
>> them move the data to the new table -perhaps in batches, truncating the
>> log in between.
> OK, thanks. I allready have simple recovery mode in use, so I should try
> via new table...
>|||Arnie Rowland wrote:
> You could change the database recovery model to simple, make the change, and
> then revert the recovery model.
> Alternatively, you could create a new table with the column change, and them
> move the data to the new table -perhaps in batches, truncating the log in
> between.
>
Simple mode alone won't resolve this, the ALTER will still take place as
a single, giant transaction. See
http://realsqlguy.com/serendipity/archives/14-When-Is-A-Transaction-Log-Not-A-Transaction-Log.html
(laugh, Arnie).
Batching is the way to avoid this...
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||"Arnie Rowland" <arnie@.1568.com> wrote in message
news:O7ngVKx0GHA.4044@.TK2MSFTNGP04.phx.gbl...
> You could change the database recovery model to simple, make the change,
> and then revert the recovery model.
I'm not sure that would do it. I'm under the impression that it's still
a logged operation -- still need to be able to rollback if it fails -- but
the log space is freed immediately after the statement completes.
I don't think that will work.
> Alternatively, you could create a new table with the column change, and
> them move the data to the new table -perhaps in batches, truncating the
> log in between.
That's what I'd recommend.

Alter table problems (transaction log will growth to 60 Gb...)

I have a large table, and I try alter one column:
ALTER TABLE TABLE_NAME ALTER COLUMN COLUMN_NAME DECIMAL (15,9)
The transaction log will growth to 60 Gb, and then I haven't any more disk
space avalable.
Does the SQL server have any parameter like NOLOGGING
or does somebody have other solutions how I can solve this problem?You could change the database recovery model to simple, make the change, and
then revert the recovery model.
Alternatively, you could create a new table with the column change, and them
move the data to the new table -perhaps in batches, truncating the log in
between.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Major" <lievonen@.jyu.fi.HALOOOOOOOO> wrote in message
news:ODG1jGx0GHA.1252@.TK2MSFTNGP04.phx.gbl...
>I have a large table, and I try alter one column:
> ALTER TABLE TABLE_NAME ALTER COLUMN COLUMN_NAME DECIMAL (15,9)
> The transaction log will growth to 60 Gb, and then I haven't any more disk
> space avalable.
> Does the SQL server have any parameter like NOLOGGING
> or does somebody have other solutions how I can solve this problem?
>
>|||> You could change the database recovery model to simple, make the change,
> and then revert the recovery model.
> Alternatively, you could create a new table with the column change, and
> them move the data to the new table -perhaps in batches, truncating the
> log in between.
OK, thanks. I allready have simple recovery mode in use, so I should try
via new table...|||Also, consider creating the new table using SELECT INTO, which is
minimally-logged in the SIMPLE model.
Hope this helps.
Dan Guzman
SQL Server MVP
"Major" <lievonen@.jyu.fi.HALOOOOOOOO> wrote in message
news:e$O0zRx0GHA.4976@.TK2MSFTNGP02.phx.gbl...
> OK, thanks. I allready have simple recovery mode in use, so I should try
> via new table...
>|||Hello,
Even Simple recovery mode will log the activities and will clear the log
once the task is completed. What you can do is
script entire the table including dependents then:-.
1. Using the script create the Table with new name. Do not create index and
triggers
2. Use DTS to load the data into new table. or use BCP OUT and BCP/IN or
BULK insert with Batch commit option to load data
3. Once the load is completed
4. Verify the count of records in both table
5. Truncate the old table
6. Rename the new table to existing using sp_rename
7. Create the indexes and triggers (Step 7 can be done along with step 1
also, but the load will be slightly slow)
Thanks
Hari
SQL Server MVP
"Major" <lievonen@.jyu.fi.HALOOOOOOOO> wrote in message
news:e$O0zRx0GHA.4976@.TK2MSFTNGP02.phx.gbl...
> OK, thanks. I allready have simple recovery mode in use, so I should try
> via new table...
>|||Arnie Rowland wrote:
> You could change the database recovery model to simple, make the change, a
nd
> then revert the recovery model.
> Alternatively, you could create a new table with the column change, and th
em
> move the data to the new table -perhaps in batches, truncating the log in
> between.
>
Simple mode alone won't resolve this, the ALTER will still take place as
a single, giant transaction. See
saction-Log.html" target="_blank">http://realsqlguy.com/serendipity/a...action-Log.html
(laugh, Arnie).
Batching is the way to avoid this...
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||"Arnie Rowland" <arnie@.1568.com> wrote in message
news:O7ngVKx0GHA.4044@.TK2MSFTNGP04.phx.gbl...
> You could change the database recovery model to simple, make the change,
> and then revert the recovery model.
I'm not sure that would do it. I'm under the impression that it's still
a logged operation -- still need to be able to rollback if it fails -- but
the log space is freed immediately after the statement completes.
I don't think that will work.

> Alternatively, you could create a new table with the column change, and
> them move the data to the new table -perhaps in batches, truncating the
> log in between.
That's what I'd recommend.

Alter Table - Add a field to a table in a specific location

I'm using the command to add a field to my table which has 20 fields.

ALTER TABLE table_name ADD column_name datatype

This adds the new field to the bottom of the table as the 21st first. How can I make it so it shows up as the 5th field in the table?

Wow you would have to do some work to get that to happen. It can be done but why?

The quick and dirty way is to copy all records into a temp table. DROP and reCREATE the table with the fields in the order of your preference. Then import the records from the temp table.

Adamus

|||The reason why I want to add it to a specific spot is to keep my table organized. The field that I am adding is a status field and I want it to be next to the other status fields and not just put it randomly at the bottom.

When you modify tables in Enterprise Manager it's real easy to change the location of fields...simply by drag and drop. This leads me to believe that there's a sql command that will do the same thing I'm just not sure what the command is.|||

Behind the 'scenes', Enterprise Manager does just like Adamus indicated. It creates a temp table, transfers the data, drops the old table, and renames the temp table.

There is no 'magic' to Enterprise Manager -it just writes the code for you -and sometimes not the best code either...

|||

Bank5,

this order is actually important only for the human user, as applications do not care so much about it (at least SHOULD NOT). Why don't you just create a view with correct column order which you can later use instead of table? Garnet Chaney posted an article about this, you can read it here

Also, "ALTER TABLE syntax for changing column order" feature is considered for the next release - if you think it would be useful you can vote here.

Cheers,

michalz