Question about efficient table dump/reload

We have a large table with one, compound (two columns), clustered index. We
want to recover space from the table (it is 50% empty space). DBREINDEX coul
d
do this, because DBREINDEX would copy the table (since the index is
clustered). But there is not enough free space on the disk partition for us
to do this on this table.
So we need to dump the table to another disk partition, and then reload the
data. How could this be done efficiently? I'm particularly concerned about
the reload phase, since the index needs to be rebuilt and I'm guessing this
will have to be done row by row. However I'd like advice on the dump phase
as well, since I suspect the type of dump and dump row order will also
greatly affect the subsequent reload.
I'm open to all suggestions: how you'd do it; how you'd *not* do it;
documentation or web references you'd suggest I look at.
(SQL Server 2000, by the way.)
Thanks!You might consider using DBCC INDEXDEFRAG if you don't have sufficient
space. If you export to a file, you can import using BCP or BULK INSERT
with the clustered index in place and specify that data are ordered using
the ORDER hint. See BCP and BULK INSERT in the Books Online for details.
In addition to data file space requirements, be mindful of the log space
needed. You might consider importing with a batch size specification and
using the SIMPLE or BULK_LOGGED recovery model in order to to reduce log
space requirements.
Hope this helps.
Dan Guzman
SQL Server MVP
"Scott" <Scott@.discussions.microsoft.com> wrote in message
news:3544030B-92C7-40F7-9F44-03125557D27D@.microsoft.com...
> We have a large table with one, compound (two columns), clustered index.
> We
> want to recover space from the table (it is 50% empty space). DBREINDEX
> could
> do this, because DBREINDEX would copy the table (since the index is
> clustered). But there is not enough free space on the disk partition for
> us
> to do this on this table.
> So we need to dump the table to another disk partition, and then reload
> the
> data. How could this be done efficiently? I'm particularly concerned
> about
> the reload phase, since the index needs to be rebuilt and I'm guessing
> this
> will have to be done row by row. However I'd like advice on the dump
> phase
> as well, since I suspect the type of dump and dump row order will also
> greatly affect the subsequent reload.
> I'm open to all suggestions: how you'd do it; how you'd *not* do it;
> documentation or web references you'd suggest I look at.
> (SQL Server 2000, by the way.)
> Thanks!
>|||Very good, thanks.
I've used INDEXDEFRAG a lot. Except for an early test case (small table),
we have totally batted zero on it ever recovering space. I know it can do s
o
in theory; the the theory has not materialized in practice for us. It does
a
good job of improving performance (index optimizing) for us, though.
I found this on the web: "Bulk Copy Optimization Tips" -
http://www.mssqlcity.com/Tips/bulk_...ptimization.htm ; this very much
matches your suggestions. An additional tip it has (not sure this is in
Books Online) is that BULK INSERT will be more efficient on the import than
BCP would be. Also regarding the transaction log issue you bring up: *if*
the TABLOCK hint is specified and the table starts out empty at the beginnin
g
of the import (plus: select into/bulkcopy option enabled and no replication)
,
*then* no transaction logging is performed. So that combination not only
would solve the potential transaction log space problem you raise, it sounds
like it even speeds things further due to no transaction logging at all.
Thank you very much for your advice.
--Scott
"Dan Guzman" wrote:

> You might consider using DBCC INDEXDEFRAG if you don't have sufficient
> space. If you export to a file, you can import using BCP or BULK INSERT
> with the clustered index in place and specify that data are ordered using
> the ORDER hint. See BCP and BULK INSERT in the Books Online for details.
> In addition to data file space requirements, be mindful of the log space
> needed. You might consider importing with a batch size specification and
> using the SIMPLE or BULK_LOGGED recovery model in order to to reduce log
> space requirements.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Scott" <Scott@.discussions.microsoft.com> wrote in message
> news:3544030B-92C7-40F7-9F44-03125557D27D@.microsoft.com...
>|||Hi, Scott.
Also be aware that if you perform a minimally logged bulk operation without
indexes, you might still paint yourself into a corner if you don't have
enough space to create the clustered index.
It seems to me that your main problem is insufficient disk space. Unless
you can add more space, you might need to load with the clustered index in
place so that you don't need to create the clustered index after the load.
Personally, I try to reserve enough freespace to allow rebuilding the
clustered index on the largest table. Although you can get by with less, it
increases manageability costs..
Hope this helps.
Dan Guzman
SQL Server MVP
"Scott" <Scott@.discussions.microsoft.com> wrote in message
news:FA01EF9E-15B1-4511-A2B2-BAFE7F86E1C4@.microsoft.com...[vbcol=seagreen]
> Very good, thanks.
> I've used INDEXDEFRAG a lot. Except for an early test case (small table),
> we have totally batted zero on it ever recovering space. I know it can do
> so
> in theory; the the theory has not materialized in practice for us. It
> does a
> good job of improving performance (index optimizing) for us, though.
> I found this on the web: "Bulk Copy Optimization Tips" -
> http://www.mssqlcity.com/Tips/bulk_...ptimization.htm ; this very much
> matches your suggestions. An additional tip it has (not sure this is in
> Books Online) is that BULK INSERT will be more efficient on the import
> than
> BCP would be. Also regarding the transaction log issue you bring up:
> *if*
> the TABLOCK hint is specified and the table starts out empty at the
> beginning
> of the import (plus: select into/bulkcopy option enabled and no
> replication),
> *then* no transaction logging is performed. So that combination not only
> would solve the potential transaction log space problem you raise, it
> sounds
> like it even speeds things further due to no transaction logging at all.
> Thank you very much for your advice.
> --Scott
>
> "Dan Guzman" wrote:
>

Question about efficient table dump/reload

We have a large table with one, compound (two columns), clustered index. We
want to recover space from the table (it is 50% empty space). DBREINDEX could
do this, because DBREINDEX would copy the table (since the index is
clustered). But there is not enough free space on the disk partition for us
to do this on this table.
So we need to dump the table to another disk partition, and then reload the
data. How could this be done efficiently? I'm particularly concerned about
the reload phase, since the index needs to be rebuilt and I'm guessing this
will have to be done row by row. However I'd like advice on the dump phase
as well, since I suspect the type of dump and dump row order will also
greatly affect the subsequent reload.
I'm open to all suggestions: how you'd do it; how you'd *not* do it;
documentation or web references you'd suggest I look at.
(SQL Server 2000, by the way.)
Thanks!
You might consider using DBCC INDEXDEFRAG if you don't have sufficient
space. If you export to a file, you can import using BCP or BULK INSERT
with the clustered index in place and specify that data are ordered using
the ORDER hint. See BCP and BULK INSERT in the Books Online for details.
In addition to data file space requirements, be mindful of the log space
needed. You might consider importing with a batch size specification and
using the SIMPLE or BULK_LOGGED recovery model in order to to reduce log
space requirements.
Hope this helps.
Dan Guzman
SQL Server MVP
"Scott" <Scott@.discussions.microsoft.com> wrote in message
news:3544030B-92C7-40F7-9F44-03125557D27D@.microsoft.com...
> We have a large table with one, compound (two columns), clustered index.
> We
> want to recover space from the table (it is 50% empty space). DBREINDEX
> could
> do this, because DBREINDEX would copy the table (since the index is
> clustered). But there is not enough free space on the disk partition for
> us
> to do this on this table.
> So we need to dump the table to another disk partition, and then reload
> the
> data. How could this be done efficiently? I'm particularly concerned
> about
> the reload phase, since the index needs to be rebuilt and I'm guessing
> this
> will have to be done row by row. However I'd like advice on the dump
> phase
> as well, since I suspect the type of dump and dump row order will also
> greatly affect the subsequent reload.
> I'm open to all suggestions: how you'd do it; how you'd *not* do it;
> documentation or web references you'd suggest I look at.
> (SQL Server 2000, by the way.)
> Thanks!
>
|||Very good, thanks.
I've used INDEXDEFRAG a lot. Except for an early test case (small table),
we have totally batted zero on it ever recovering space. I know it can do so
in theory; the the theory has not materialized in practice for us. It does a
good job of improving performance (index optimizing) for us, though.
I found this on the web: "Bulk Copy Optimization Tips" -
http://www.mssqlcity.com/Tips/bulk_copy_optimization.htm ; this very much
matches your suggestions. An additional tip it has (not sure this is in
Books Online) is that BULK INSERT will be more efficient on the import than
BCP would be. Also regarding the transaction log issue you bring up: *if*
the TABLOCK hint is specified and the table starts out empty at the beginning
of the import (plus: select into/bulkcopy option enabled and no replication),
*then* no transaction logging is performed. So that combination not only
would solve the potential transaction log space problem you raise, it sounds
like it even speeds things further due to no transaction logging at all.
Thank you very much for your advice.
--Scott
"Dan Guzman" wrote:

> You might consider using DBCC INDEXDEFRAG if you don't have sufficient
> space. If you export to a file, you can import using BCP or BULK INSERT
> with the clustered index in place and specify that data are ordered using
> the ORDER hint. See BCP and BULK INSERT in the Books Online for details.
> In addition to data file space requirements, be mindful of the log space
> needed. You might consider importing with a batch size specification and
> using the SIMPLE or BULK_LOGGED recovery model in order to to reduce log
> space requirements.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Scott" <Scott@.discussions.microsoft.com> wrote in message
> news:3544030B-92C7-40F7-9F44-03125557D27D@.microsoft.com...
>
|||Hi, Scott.
Also be aware that if you perform a minimally logged bulk operation without
indexes, you might still paint yourself into a corner if you don't have
enough space to create the clustered index.
It seems to me that your main problem is insufficient disk space. Unless
you can add more space, you might need to load with the clustered index in
place so that you don't need to create the clustered index after the load.
Personally, I try to reserve enough freespace to allow rebuilding the
clustered index on the largest table. Although you can get by with less, it
increases manageability costs..
Hope this helps.
Dan Guzman
SQL Server MVP
"Scott" <Scott@.discussions.microsoft.com> wrote in message
news:FA01EF9E-15B1-4511-A2B2-BAFE7F86E1C4@.microsoft.com...[vbcol=seagreen]
> Very good, thanks.
> I've used INDEXDEFRAG a lot. Except for an early test case (small table),
> we have totally batted zero on it ever recovering space. I know it can do
> so
> in theory; the the theory has not materialized in practice for us. It
> does a
> good job of improving performance (index optimizing) for us, though.
> I found this on the web: "Bulk Copy Optimization Tips" -
> http://www.mssqlcity.com/Tips/bulk_copy_optimization.htm ; this very much
> matches your suggestions. An additional tip it has (not sure this is in
> Books Online) is that BULK INSERT will be more efficient on the import
> than
> BCP would be. Also regarding the transaction log issue you bring up:
> *if*
> the TABLOCK hint is specified and the table starts out empty at the
> beginning
> of the import (plus: select into/bulkcopy option enabled and no
> replication),
> *then* no transaction logging is performed. So that combination not only
> would solve the potential transaction log space problem you raise, it
> sounds
> like it even speeds things further due to no transaction logging at all.
> Thank you very much for your advice.
> --Scott
>
> "Dan Guzman" wrote:

Question about data import in MS-SQL server

Dear All,

I am performing a data import on the SQL server. Due to fact
that I use the excel file as a source. Some of cells in excel are
actually empty, they become NULL fields after importing into the SQL
server. Actually I want these fields are empty string instead of NULL.
Does SQL server has any approach to make these fields to be empty
string instead of NULL when importing?? Or is there any store
procedure exist for converting the fields to empty string?

Thanks for your kind attention.

BennyBenny (cs_benny@.hotmail.com) writes:
> I am performing a data import on the SQL server. Due to fact
> that I use the excel file as a source. Some of cells in excel are
> actually empty, they become NULL fields after importing into the SQL
> server. Actually I want these fields are empty string instead of NULL.
> Does SQL server has any approach to make these fields to be empty
> string instead of NULL when importing?? Or is there any store
> procedure exist for converting the fields to empty string?

Since I don't know how you import the data, I can't really say what
you could do in that end.

Once the data is in SQL Server, you can say:

UPDATE tbl
SET col = ''
WHERE col IS NULL

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||I use DTS import/export wizard to import the data from the Excel file.
So is there any ways to replace the NULL fields with empty string
beside using query to update the fields to empty string?

Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns93C0EFC135FA3Yazorman@.127.0.0.1>...
> Benny (cs_benny@.hotmail.com) writes:
> > I am performing a data import on the SQL server. Due to fact
> > that I use the excel file as a source. Some of cells in excel are
> > actually empty, they become NULL fields after importing into the SQL
> > server. Actually I want these fields are empty string instead of NULL.
> > Does SQL server has any approach to make these fields to be empty
> > string instead of NULL when importing?? Or is there any store
> > procedure exist for converting the fields to empty string?
> Since I don't know how you import the data, I can't really say what
> you could do in that end.
> Once the data is in SQL Server, you can say:
> UPDATE tbl
> SET col = ''
> WHERE col IS NULL|||Benny (cs_benny@.hotmail.com) writes:
> I use DTS import/export wizard to import the data from the Excel file.
> So is there any ways to replace the NULL fields with empty string
> beside using query to update the fields to empty string?

Sorry, I don't use DTS so I don't know. But I want to point out the
necessity of providing people full information about what you are
doing.

If you don't get any replies, consider asking in
microsoft.public.sqlserver.dts. (Available on msnews.microsoft.com if your
ISP does not have it.)

Also check out the FAQ on http://www.sqldts.com.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||With DTS, you can transform the data before it gets imported. That's the
'T' in 'DTS'. When moving an Excel spreadsheet to SQL Server, there is a
place in DTS where you select the worksheet from the Excel file. At this
location, there is a Transform button. You can do simple
transformations, or you can use VB code to do more complex
transformations.

HTH,
Brain

Benny wrote:
> I use DTS import/export wizard to import the data from the Excel file.
> So is there any ways to replace the NULL fields with empty string
> beside using query to update the fields to empty string?
> Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns93C0EFC135FA3Yazorman@.127.0.0.1>...
> > Benny (cs_benny@.hotmail.com) writes:
> > > I am performing a data import on the SQL server. Due to fact
> > > that I use the excel file as a source. Some of cells in excel are
> > > actually empty, they become NULL fields after importing into the SQL
> > > server. Actually I want these fields are empty string instead of NULL.
> > > Does SQL server has any approach to make these fields to be empty
> > > string instead of NULL when importing?? Or is there any store
> > > procedure exist for converting the fields to empty string?
> > Since I don't know how you import the data, I can't really say what
> > you could do in that end.
> > Once the data is in SQL Server, you can say:
> > UPDATE tbl
> > SET col = ''
> > WHERE col IS NULL

--
================================================== =================

Brian Peasland
dba@.remove_spam.peasland.com

Remove the "remove_spam." from the email address to email me.

"I can give it to you cheap, quick, and good. Now pick two out of
the three"