Faster Query

Two quick questions,
1. Pleasae post a query for FASTER deleting rows ( millions ( 10s ) from a
table .
2. Does anyone knows what is the equivalent command for "Instant File
Initilization"
in SQL Server 2005 for faster database / file creation, in SQL Server
2000...'
Regards.
Piku.1. Do it in steps, say 1000 to 10000 rows at a time, so not all rows are del
eted in a single
transaction.
2. There is no such thing in 2000. Don't over-use autogrow and be prepared t
hat it takes time to
create database and expand database files.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Piku" <Piku@.discussions.microsoft.com> wrote in message
news:93258D03-424F-412E-9624-82864426F6A5@.microsoft.com...
> Two quick questions,
> 1. Pleasae post a query for FASTER deleting rows ( millions ( 10s ) from a
> table .
> 2. Does anyone knows what is the equivalent command for "Instant File
> Initilization"
> in SQL Server 2005 for faster database / file creation, in SQL Server
> 2000...'
> Regards.
> Piku.
>|||> 1. Pleasae post a query for FASTER deleting rows ( millions ( 10s ) from a
> table .
You'll usually get the best performance and keep the transaction log size
reasonable by deleting data in smaller batches. The details on how to do
this efficiently depend your actual situation (indexes, delete criteria) and
the version of SQL Server. A SQL 2005 example:
DECLARE @.RowsDeleted int
WHILE @.RowsDeleted IS NULL OR @.RowsDeleted > 0
BEGIN
DELETE TOP (1000000)
FROM dbo.MyTable
WHERE CreateDate < '20060101'
SET @.RowsDeleted = @.@.ROWCOUNT
END

> 2. Does anyone knows what is the equivalent command for "Instant File
> Initilization"
> in SQL Server 2005 for faster database / file creation, in SQL Server
> 2000...'
This is a new feature introduced in SQL 2005 and is therefore not available
in SQL 2000.
Hope this helps.
Dan Guzman
SQL Server MVP
"Piku" <Piku@.discussions.microsoft.com> wrote in message
news:93258D03-424F-412E-9624-82864426F6A5@.microsoft.com...
> Two quick questions,
> 1. Pleasae post a query for FASTER deleting rows ( millions ( 10s ) from a
> table .
> 2. Does anyone knows what is the equivalent command for "Instant File
> Initilization"
> in SQL Server 2005 for faster database / file creation, in SQL Server
> 2000...'
> Regards.
> Piku.
>|||Tibor, Thx for the quick reply.
1. I am deleting 5000 at a time , but for deleting 10 mil, rows it takes
about 5 hours.
here is the script I am using, please suggest me if you have something bette
r:
Use database
GO
while 1=1
begin
set rowcount 5000
DELETE from table_name
where DATE_TIME < '2006-03-01 00:00:00.000'
IF @.@.ROWCOUNT = 0
BREAK
end
set rowcount 0
2. Are you sure there is no FASTER way of creating a DB / File...' Because
it really matters, when you want to create a DB / File of about 200-300 gb.
"Tibor Karaszi" wrote:

> 1. Do it in steps, say 1000 to 10000 rows at a time, so not all rows are d
eleted in a single
> transaction.
> 2. There is no such thing in 2000. Don't over-use autogrow and be prepared
that it takes time to
> create database and expand database files.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Piku" <Piku@.discussions.microsoft.com> wrote in message
> news:93258D03-424F-412E-9624-82864426F6A5@.microsoft.com...
>|||> 2. Are you sure there is no FASTER way of creating a DB / File...' Because[vbcol=seagreen
]
> it really matters, when you want to create a DB / File of about 200-300 gb.[/vbcol
]
That would be better I/O throughput...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Piku" <Piku@.discussions.microsoft.com> wrote in message
news:57419BC5-2016-4C80-8BC8-0621B7FCD6D1@.microsoft.com...[vbcol=seagreen]
> Tibor, Thx for the quick reply.
> 1. I am deleting 5000 at a time , but for deleting 10 mil, rows it takes
> about 5 hours.
> here is the script I am using, please suggest me if you have something bet
ter:
> Use database
> GO
> while 1=1
> begin
> set rowcount 5000
> DELETE from table_name
> where DATE_TIME < '2006-03-01 00:00:00.000'
> IF @.@.ROWCOUNT = 0
> BREAK
> end
> set rowcount 0
> 2. Are you sure there is no FASTER way of creating a DB / File...' Becaus
e
> it really matters, when you want to create a DB / File of about 200-300 gb
.
>
>
> "Tibor Karaszi" wrote:
>|||"Piku" <Piku@.discussions.microsoft.com> wrote in message
news:57419BC5-2016-4C80-8BC8-0621B7FCD6D1@.microsoft.com...
> 2. Are you sure there is no FASTER way of creating a DB / File...'
Because
> it really matters, when you want to create a DB / File of about 200-300
gb.
>
Not within SQL Server.
However, some SAN solutions like I believe Left Hand Network's solution will
do this at a hardware level.|||"Piku" <Piku@.discussions.microsoft.com> wrote in message
news:93258D03-424F-412E-9624-82864426F6A5@.microsoft.com...
> Two quick questions,
> 1. Pleasae post a query for FASTER deleting rows ( millions ( 10s ) from a
> table .
Well if you want to delete ALL the rows, truncate table is the answer.
Another option if you want to keep some records and the number of records is
relatively small is to select them into a new table, drop the old table and
then rename the new table to the old table name.

> 2. Does anyone knows what is the equivalent command for "Instant File
> Initilization"
> in SQL Server 2005 for faster database / file creation, in SQL Server
> 2000...'
> Regards.
> Piku.
>