BCP out/in very large table

Could someone give me some guidance on the fastest way to unload and reload
data from very large tables. I am planning to drop about 30% of columns
(fixed length columns) from few of our largest tables (size ranges from 300
GB to 500 GB each). In order to reclaim space from dropped columns, looks
like only way is to unload the data, recreate the table (with less columns)
and reload the data. Dropping and recreating clustered index didn't reclaim
space ( I am running SQL 2000 sp4).
I am planning to perform following steps:
1. BCP out data
2. Drop/recreate table with only clustered index
3. BCP in data
4. Create non clustered index, foreign key, constraints etc
Do you see any potential problem with exporting such a big size to text
file? I am planning to export the data to multiple files so that when I BCP
in, I can load them concurrently and also planning to use ORDER and TABLOCK
hint. Can I use ORDER and TABLOCK hint when loading data to a table with
clustered index and loading multiple file simultaneously? I am guessing if I
use "queryout" option with "order by" clause on clustered column when bcp
out the data, I should be able to use ORDER hint when loading data in. Let
me know if I am wrong. Also, I am planning to use SQL server Native format.
If you have any experience in unloading/reloading very large table from sql
server, I would love to hear your comments/suggestion.
Instead of BCP in, consider BULK INSERT with parallel streams. See the
following excellent articles for some of the best rpactices recommendations:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/incbulkload.mspx
http://blogs.msdn.com/sqlcat/archive/2006/05/19/602142.aspx
http://www.microsoft.com/sql/techinfo/administration/2000/rosetta.doc
Linchi
"james" wrote:

> Could someone give me some guidance on the fastest way to unload and reload
> data from very large tables. I am planning to drop about 30% of columns
> (fixed length columns) from few of our largest tables (size ranges from 300
> GB to 500 GB each). In order to reclaim space from dropped columns, looks
> like only way is to unload the data, recreate the table (with less columns)
> and reload the data. Dropping and recreating clustered index didn't reclaim
> space ( I am running SQL 2000 sp4).
> I am planning to perform following steps:
> 1. BCP out data
> 2. Drop/recreate table with only clustered index
> 3. BCP in data
> 4. Create non clustered index, foreign key, constraints etc
> Do you see any potential problem with exporting such a big size to text
> file? I am planning to export the data to multiple files so that when I BCP
> in, I can load them concurrently and also planning to use ORDER and TABLOCK
> hint. Can I use ORDER and TABLOCK hint when loading data to a table with
> clustered index and loading multiple file simultaneously? I am guessing if I
> use "queryout" option with "order by" clause on clustered column when bcp
> out the data, I should be able to use ORDER hint when loading data in. Let
> me know if I am wrong. Also, I am planning to use SQL server Native format.
> If you have any experience in unloading/reloading very large table from sql
> server, I would love to hear your comments/suggestion.
>
>
sql

BCP out/in very large table

Could someone give me some guidance on the fastest way to unload and reload
data from very large tables. I am planning to drop about 30% of columns
(fixed length columns) from few of our largest tables (size ranges from 300
GB to 500 GB each). In order to reclaim space from dropped columns, looks
like only way is to unload the data, recreate the table (with less columns)
and reload the data. Dropping and recreating clustered index didn't reclaim
space ( I am running SQL 2000 sp4).
I am planning to perform following steps:
1. BCP out data
2. Drop/recreate table with only clustered index
3. BCP in data
4. Create non clustered index, foreign key, constraints etc
Do you see any potential problem with exporting such a big size to text
file? I am planning to export the data to multiple files so that when I BCP
in, I can load them concurrently and also planning to use ORDER and TABLOCK
hint. Can I use ORDER and TABLOCK hint when loading data to a table with
clustered index and loading multiple file simultaneously? I am guessing if I
use "queryout" option with "order by" clause on clustered column when bcp
out the data, I should be able to use ORDER hint when loading data in. Let
me know if I am wrong. Also, I am planning to use SQL server Native format.
If you have any experience in unloading/reloading very large table from sql
server, I would love to hear your comments/suggestion.Instead of BCP in, consider BULK INSERT with parallel streams. See the
following excellent articles for some of the best rpactices recommendations:
http://www.microsoft.com/technet/pr.../19/602142.aspx
http://www.microsoft.com/sql/techin...000/rosetta.doc
Linchi
"james" wrote:

> Could someone give me some guidance on the fastest way to unload and reloa
d
> data from very large tables. I am planning to drop about 30% of columns
> (fixed length columns) from few of our largest tables (size ranges from 30
0
> GB to 500 GB each). In order to reclaim space from dropped columns, looks
> like only way is to unload the data, recreate the table (with less columns
)
> and reload the data. Dropping and recreating clustered index didn't reclai
m
> space ( I am running SQL 2000 sp4).
> I am planning to perform following steps:
> 1. BCP out data
> 2. Drop/recreate table with only clustered index
> 3. BCP in data
> 4. Create non clustered index, foreign key, constraints etc
> Do you see any potential problem with exporting such a big size to text
> file? I am planning to export the data to multiple files so that when I BC
P
> in, I can load them concurrently and also planning to use ORDER and TABLO
CK
> hint. Can I use ORDER and TABLOCK hint when loading data to a table with
> clustered index and loading multiple file simultaneously? I am guessing if
I
> use "queryout" option with "order by" clause on clustered column when bcp
> out the data, I should be able to use ORDER hint when loading data in. Let
> me know if I am wrong. Also, I am planning to use SQL server Native format
.
> If you have any experience in unloading/reloading very large table from sq
l
> server, I would love to hear your comments/suggestion.
>
>

Basic Questions

Hi All,

I am used to other SQL engines, and have a few basic questions--

1)If I wanted to conditionally drop a table, does SQL Server have a
way to natively do this? Many SQL implementations will allow
something like:

CREATE OR REPLACE tablename AS
SELECT
x,y,z
FROM sourcetable
;

Does SQL Server have something like this? This syntax, both the
"create table as select" syntax and the "create or replace" syntax
seem to cause problems.

2) Some of our existing queries have a keyword, "GO" where I would
otherwise expect a semi-colon. Is there a functional difference
between the two? I seem to be able to replace the "GO" keywords with
semi-colons without any changes in how the script behaves, but I
thought I would check and see if anyone has advice about the
differences here.

Thanks in advance,
BenBJMurphy (murphy.ben@.gmail.com) writes:

Quote:

Originally Posted by

I am used to other SQL engines, and have a few basic questions--
>
1)If I wanted to conditionally drop a table, does SQL Server have a
way to natively do this? Many SQL implementations will allow
something like:
>
CREATE OR REPLACE tablename AS
SELECT
x,y,z
FROM sourcetable
;
>
Does SQL Server have something like this? This syntax, both the
"create table as select" syntax and the "create or replace" syntax
seem to cause problems.

You can create a table from a query this way:

SELET x, y, z
INTO tablename
FROM sourcetable

There is nothing resemblent of CREATE OR REPLACE for anything in SQL
Server. You need to have things like:

IF object_id('sometable') IS NOT NULL
DROP TABLE sometable

Quote:

Originally Posted by

2) Some of our existing queries have a keyword, "GO" where I would
otherwise expect a semi-colon. Is there a functional difference
between the two? I seem to be able to replace the "GO" keywords with
semi-colons without any changes in how the script behaves, but I
thought I would check and see if anyone has advice about the
differences here.

GO and ; are entirely unrelated. In T-SQL, GO is just another
identifier like MyTable, [Order Details] or whatever. GO is used by
many query tools as a batch separator, so that you can give the tool
something like:

CREATE something
go
-- Use this something
go
-- Drop this something

Each batch will be sent to SQL Server separately. A batch can consist
of many statements, separated or not by semicolons.

In application code you would typically never use GO, but each Execute
command is a batch.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||On Feb 13, 5:17 pm, Erland Sommarskog <esq...@.sommarskog.sewrote:

Quote:

Originally Posted by

BJMurphy (murphy...@.gmail.com) writes:

Quote:

Originally Posted by

I am used to other SQL engines, and have a few basic questions--

>

Quote:

Originally Posted by

1)If I wanted to conditionally drop a table, does SQL Server have a
way to natively do this? Many SQL implementations will allow
something like:

>

Quote:

Originally Posted by

CREATE OR REPLACE tablename AS
SELECT
x,y,z
FROM sourcetable
;

>

Quote:

Originally Posted by

Does SQL Server have something like this? This syntax, both the
"create table as select" syntax and the "create or replace" syntax
seem to cause problems.

>
You can create a table from a query this way:
>
SELET x, y, z
INTO tablename
FROM sourcetable
>
There is nothing resemblent of CREATE OR REPLACE for anything in SQL
Server. You need to have things like:
>
IF object_id('sometable') IS NOT NULL
DROP TABLE sometable
>

Quote:

Originally Posted by

2) Some of our existing queries have a keyword, "GO" where I would
otherwise expect a semi-colon. Is there a functional difference
between the two? I seem to be able to replace the "GO" keywords with
semi-colons without any changes in how the script behaves, but I
thought I would check and see if anyone has advice about the
differences here.

>
GO and ; are entirely unrelated. In T-SQL, GO is just another
identifier like MyTable, [Order Details] or whatever. GO is used by
many query tools as a batch separator, so that you can give the tool
something like:
>
CREATE something
go
-- Use this something
go
-- Drop this something
>
Each batch will be sent to SQL Server separately. A batch can consist
of many statements, separated or not by semicolons.
>
In application code you would typically never use GO, but each Execute
command is a batch.
>
--
Erland Sommarskog, SQL Server MVP, esq...@.sommarskog.se
>
Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx- Hide quoted text -
>
- Show quoted text -

Thanks, Erland, for your response.

To clarify further, if I use a semi-colon at the end of my query, and
put several queries in a row, SQL server will not execute them until
it reaches the end of the set of queries or a "GO" statement. Is that
correct?

Put another way, if I am creating table A in one query and then want
to use it in the next query, do I have any options other than using
"GO" after the first query to ensure that table A will be available in
the second query?

I understood the semi-colon to be analogous to "GO" in that both would
submit the query to the SQL server, acting as a separator, but it
seems that you are saying this is not the case. I apologize for my
confusion, I just want to make sure I have everything straight in my
head.|||I'm not the expert that Erland is, but I think I know.

GO and semicolon both serve to separate statements. But GO also
separates batches.

If you make 4 statements into one batch like this --

statement1; statement2; statement3; statement4 GO

...and statement4 fails with an error, the affected objects are
restored to the way they were before statement1.

If you make them 4 separate batches like this --

statement1 GO statement2 GO statement3 GO statement4 GO

... and statement4 fails with an error, the objects are restored to
the way they were after statement 3.

Jim|||On Feb 14, 4:54 pm, jim_geiss...@.countrywide.com wrote:

Quote:

Originally Posted by

I'm not the expert that Erland is, but I think I know.
>
GO and semicolon both serve to separate statements. But GO also
separates batches.
>
If you make 4 statements into one batch like this --
>
statement1; statement2; statement3; statement4 GO
>
...and statement4 fails with an error, the affected objects are
restored to the way they were before statement1.
>
If you make them 4 separate batches like this --
>
statement1 GO statement2 GO statement3 GO statement4 GO
>
... and statement4 fails with an error, the objects are restored to
the way they were after statement 3.
>
Jim

OK. It seems as though the variables that I had setup were only valid
and were local to the specific batch in which they were created. If I
have ten statements, some of which are codependent, but I know they
will run successfully, it seems like I would want to use semi-colons
because it will allow me to keep the values of the local variables
throughout the entire batch, since there is only one batch. Would the
codependent queries still work?

For example:

create table foo;
select * from bar into foo;
select * from foo where (true);

Is then equivalent to:
create table foo
go
select * from bar into foo
go
select * from foo where (true);

Again, assuming the queries run succesfully.

Is there a way to create global variables?

Thanks for your response, Jim. I appreciate the help. Also, don't
worry, a book is on the way (the highly recommended one that comes in
two parts, I forget the title and author, sorry), so I won't be
posting too many questions, hopefullly :-)|||BJMurphy (murphy.ben@.gmail.com) writes:

Quote:

Originally Posted by

To clarify further, if I use a semi-colon at the end of my query, and
put several queries in a row, SQL server will not execute them until
it reaches the end of the set of queries or a "GO" statement. Is that
correct?

Not really. SQL Server never reaches any GO statement. GO is not a statement
at all. GO carries no particular meaning for SQL Server at all. GO is a
separator that is intercepted by interactive query tools. You would
never have any GO in a program.

Keep in mind that SQL Server is a client-server application. Query
Analyzer or SQL Server Management Studio are not SQL Server. They
are client tools. When you select a couple of statements in a
query window, they will send these to SQL Server. But if there are
GO on some lines, they will send the batches one by one. And one
batch will not be sent, until the previous has executed.

Once in SQL Server, SQL Server will execute the statements, sequentially.
(Unless there is some control-of-flow languages.)

The semi-colons are statement terminators, but in difference to
other semicolon languages, they are optional in SQL Server. (Except in
a few situations where they are needed to disambiguiate the grammar.)

Quote:

Originally Posted by

Put another way, if I am creating table A in one query and then want
to use it in the next query, do I have any options other than using
"GO" after the first query to ensure that table A will be available in
the second query?

There are statements that must be alone in a batck, like CREATE PROCEDURE
and CREATE VIEW. So to refer to you newly created view, you need to
do this in a separate batch. There are also some commands that does not
take effect until the next batch. Some SET commands are like that.

However, CREATE TABLE does not have any of these quirks. You can say:

CREATE #mytable (a int NOT NULL
INSERT #mytable(a) VALUES (213)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||BJMurphy (murphy.ben@.gmail.com) writes:

Quote:

Originally Posted by

For example:
>
create table foo;
select * from bar into foo;
select * from foo where (true);
>
Is then equivalent to:
create table foo
go
select * from bar into foo
go
select * from foo where (true);
>
Again, assuming the queries run succesfully.

Well, since two of them has syntax errors, they will not. So
the first batch will fail. But if you run the second set of bathces,
the second statement will create the table "foo".

Quote:

Originally Posted by

Is there a way to create global variables?

No. The place to write data that needs to be persisted over batches
is in tables, typically temp tables.

(OK, there is a command SET CONTEXT_INFO which could be used for
this, but it's not anywhere close to practical for this purpose.)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx