RowCount using Group BY and Having

Hi,
I have a query that uses Group By and Having to retrieve records.
The query is working fine and I want to get the Row Count of that Query.
How will I do that.
Thanks
Kiran
"Kiran" <Kiran@.nospam.net> wrote in message
news:eIhHM6EAFHA.3908@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I have a query that uses Group By and Having to retrieve records.
> The query is working fine and I want to get the Row Count of that Query.
> How will I do that.
> Thanks
> Kiran
>
SELECT ... Group By Query
SELECT @.@.ROWCOUNT
You could also save the rowcount into a variable like this:
DECLARE @.MyCount int
SELECT ... Group By Query
SELECT @.MyCount = @.@.RowCount
HTH
Rick Sawtell
MCT, MCSD, MCDBA

RowCount using Group BY and Having

Hi,
I have a query that uses Group By and Having to retrieve records.
The query is working fine and I want to get the Row Count of that Query.
How will I do that.
Thanks
Kiran"Kiran" <Kiran@.nospam.net> wrote in message
news:eIhHM6EAFHA.3908@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I have a query that uses Group By and Having to retrieve records.
> The query is working fine and I want to get the Row Count of that Query.
> How will I do that.
> Thanks
> Kiran
>
SELECT ... Group By Query
SELECT @.@.ROWCOUNT
You could also save the rowcount into a variable like this:
DECLARE @.MyCount int
SELECT ... Group By Query
SELECT @.MyCount = @.@.RowCount
HTH
Rick Sawtell
MCT, MCSD, MCDBA

RowCount using Group BY and Having

Hi,
I have a query that uses Group By and Having to retrieve records.
The query is working fine and I want to get the Row Count of that Query.
How will I do that.
Thanks
Kiran"Kiran" <Kiran@.nospam.net> wrote in message
news:eIhHM6EAFHA.3908@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I have a query that uses Group By and Having to retrieve records.
> The query is working fine and I want to get the Row Count of that Query.
> How will I do that.
> Thanks
> Kiran
>
SELECT ... Group By Query
SELECT @.@.ROWCOUNT
You could also save the rowcount into a variable like this:
DECLARE @.MyCount int
SELECT ... Group By Query
SELECT @.MyCount = @.@.RowCount
HTH
Rick Sawtell
MCT, MCSD, MCDBA

Row triggers equivalent in sql server 2000

Hello Guys!
i have been working with oracle with quite a time. No i migrated to sql
server 2000 and i want to create a trigger on a table.
the trigger function has to update the Modification field to getdate()
whenever a row is being updated.
i tried lots of things

if anyone can help i would appreciate a lot!

RegardsFad,

This should work for you:

create trigger MyTrigger on MyTable for insert, update
as
begin
update MyTable
set Modification = getdate()
from MyTable m
inner join inserted i on m.MyTableID = i.MyTableID
end

-- Bill

"Fad" <fadyay@.gmail.comwrote in message
news:1169163810.969473.16880@.m58g2000cwm.googlegro ups.com...

Quote:

Originally Posted by

Hello Guys!
i have been working with oracle with quite a time. No i migrated to sql
server 2000 and i want to create a trigger on a table.
the trigger function has to update the Modification field to getdate()
whenever a row is being updated.
i tried lots of things
>
if anyone can help i would appreciate a lot!
>
Regards
>

|||Thanks AlterEgo for the help!
now i get the functionality of inserted and deleted tablessql

Row filtering - Comparing HOST_NAME() with uniqueidentifier

Working with Merge Replication between SQL Server 2000 and SQL Server
CE 2.0.
I was doing some filtering on the Publication with "host_name()"
function. I was comparing host_name() function with a nvarchar column,
so all was working pretty well.
In order to filter all appointments for a specific patient, I was
trying to assign the HostName property of the replication object with
patientId. This patientId is a uniqueidentifier column, therefore, when
building the where clause condition in the Publication's properties
(patientId = HOST_NAME()), I got an error saying that cannot compare
varchar with uniqueidentifier.
Any solution to this? I think I should compare the uniqueidentifier
column rather than adding another nvarchar column
(patientId2)containing same value as patientId original column?
Besides: I need a patient only can see his/her appointments, but that a
doctor can see all appointments from all his patients. Appointment
table contains doctorId and a patientId fields, which both have
uniqueidentifier datatype.
What should be the best filter for this case? Would I need another
publication or a gool filter would done it?
Thanks in advance.
have a look at fn_varbintohexstr.
For the second one it sounds like you need a second publication where you
filter on the doctorID.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Lonifasiko" <mloichate@.gmail.com> wrote in message
news:1128333296.020256.272540@.g44g2000cwa.googlegr oups.com...
> Working with Merge Replication between SQL Server 2000 and SQL Server
> CE 2.0.
> I was doing some filtering on the Publication with "host_name()"
> function. I was comparing host_name() function with a nvarchar column,
> so all was working pretty well.
> In order to filter all appointments for a specific patient, I was
> trying to assign the HostName property of the replication object with
> patientId. This patientId is a uniqueidentifier column, therefore, when
> building the where clause condition in the Publication's properties
> (patientId = HOST_NAME()), I got an error saying that cannot compare
> varchar with uniqueidentifier.
> Any solution to this? I think I should compare the uniqueidentifier
> column rather than adding another nvarchar column
> (patientId2)containing same value as patientId original column?
> Besides: I need a patient only can see his/her appointments, but that a
> doctor can see all appointments from all his patients. Appointment
> table contains doctorId and a patientId fields, which both have
> uniqueidentifier datatype.
> What should be the best filter for this case? Would I need another
> publication or a gool filter would done it?
> Thanks in advance.
>
|||Thanks Hilary.
I'll take a look at that function.
Respecting to my second request, my initial idea was to only have one
publication for all the application instead of having several and
having to mantain them. Does it worth or is the normal approach to have
more than one publication? Isn't there another way of achieving what
I'm looking for? I would prefer to have only one publication, but if
there is not another way.......
Regards.
|||I would go for multiple publications. There is more administrative effort,
but slight.
You might be able to get away with a more complex filter, is something along
the line of
<> where PatientID in (select patientID from patients where something = case
when HOST_NAME() > 'M' then (select doctorID from doctors) else
host_name()end )
So you could build an elaborate case statement and do existence checks with
subqueries, or even use UDF's, but you are limited by what you can do on the
host end which is CE, and then there will be a performance hit while this
where condition is evaluated.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Lonifasiko" <mloichate@.gmail.com> wrote in message
news:1128341002.180658.194500@.g43g2000cwa.googlegr oups.com...
> Thanks Hilary.
> I'll take a look at that function.
> Respecting to my second request, my initial idea was to only have one
> publication for all the application instead of having several and
> having to mantain them. Does it worth or is the normal approach to have
> more than one publication? Isn't there another way of achieving what
> I'm looking for? I would prefer to have only one publication, but if
> there is not another way.......
> Regards.
>
|||I see your example too complex to build. Besides, I'm not sure I
understand your "where clause" example. What about when HOST_NAME() >
'M' ? Sorry but I don't understand it at all. Could you give me a
little more detailed example, with pseudo-code or something similar
please?
You mean you can elaborate case statements in the Publication's where
clause? And can I include subqueries and so on?
I understand UDF's are difficult when working against SQL Server CE,
aren't they?
I cannot achieve to convert host_name() to uniqueidentifier.
Publication's property page tells me cannot do that. Any example?
The alternative of RDA would be useful in this case?
Sorry to ask you for all kind of examples Hilary but I've never
experienced with Merge Replication before?
|||Hi again Hilary, I've achieved what I was looking for with the row
filtering I show you below. Don't know if it's good for performance or
not, but works great and I think meets my requirements. Anyway, any
opinion or new idea will be greatly appreciated:
SELECT <columnas_publicadas> FROM [dbo].[Appointment] WHERE
patientId in
(select patientId from Patient where patientFirstName = HOST_NAME())
or doctorId in
(select doctorId from Doctor where doctorFirstName = HOST_NAME())
This is a non-real "where clause", because it's not very useful to
filter rows based on the first name of the patient or doctor. Anyway,
in this case, I fill the Replication object's HostName property with
patient's or doctor's first name and the "OR" clause works great for
me! I understand that when patientId is not found, doctorId is checked.
If patientId is found, doctorId is not checked anymore.
I now would like to achieve the same with uniqueidentifiers but I'm not
able.
Is it better to have two publications for this case, or with this "OR
where clause" would be enough?
Thanks very much. I'm willing for any reply or advice.
|||basically its something bogus, but something I use every once and a while.
Host_name() >'m' compares this first value of host_name and checks to see if
its ascii value is greater than the ascii value of m.
so a host_name of 'alpha' would not meet this filtering criteria, whereas a
host_name of 'nancy' would. You can override the host_name() value by using
the -HostName parameter in your command string.
I can't really elaborate on the possible where clause or a case statement as
I don't know your schema. It looks like you are filtering on patients by
patientID. Note that you could issue a subquery if you have a doctor's table
of if the patients table is related to the doctor table via dri and you
extend the join filter it should work automatically.
I was hoping you would look at what I had written and go ah ha!
Can you post the schema of the doctors/patients tables?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Lonifasiko" <mloichate@.gmail.com> wrote in message
news:1128343662.941267.220180@.f14g2000cwb.googlegr oups.com...
> I see your example too complex to build. Besides, I'm not sure I
> understand your "where clause" example. What about when HOST_NAME() >
> 'M' ? Sorry but I don't understand it at all. Could you give me a
> little more detailed example, with pseudo-code or something similar
> please?
> You mean you can elaborate case statements in the Publication's where
> clause? And can I include subqueries and so on?
> I understand UDF's are difficult when working against SQL Server CE,
> aren't they?
> I cannot achieve to convert host_name() to uniqueidentifier.
> Publication's property page tells me cannot do that. Any example?
> The alternative of RDA would be useful in this case?
> Sorry to ask you for all kind of examples Hilary but I've never
> experienced with Merge Replication before?
>
|||I don't know if you've read my last post. I suppose yes. I've achieved
this way, with a where clause containing an OR:
SELECT <columnas_publicadas> FROM [dbo].[Appointment] WHERE
patientId in
(select patientId from Patient where patientFirstName = HOST_NAME())
or doctorId in
(select doctorId from Doctor where doctorFirstName = HOST_NAME())
I understand you don't agree with this approach at all but as I've
already told you, I want to avoid having more tha one publication. How
do you see it?
My database schema goes as follows:
Table Patient: patientId (uniqueidentifier -PK), patientFirstName, etc.
Table Doctor: doctorId (uniqueidentifier -PK), doctorFirstName, etc.
Relation table - Table DoctorPatient: patientId (part of PK), doctorId
(part of PK), diseaseId(part of PK), rowguidcol (generated by the
Publication) and so on.
Note that a patient can have same doctor for different diseases. For
example: Patient A is attended by Doctor B for disease C, and Patient A
is also attented by Doctor B, this time for disease D.
Anyway, I cannot filter directly by GUID, that is, I cannot convert
host_name() to GUID!
Help me please!
I hope to have explained myself correctly enough. Really thanks for
your time Hilary.Regards.
|||Hi again Hilary,
My "final" where clause would be as follows:
SELECT <columnas_publicadas> FROM [dbo].[Appointment] WHERE
HOST_NAME() = convert(nvarchar(100), patientId) or HOST_NAME()
= convert(nvarchar(100), doctorId) and isClosed = 0
You can notice that I am now able to do the comparison between
uniqueidentifier and nvarchar. I thougth this comparison had to be done
in the other direction, but found this and worked. Would be better to
convert host_name() to uniqueidentifier or left it this way?
I've also noticed that this where clause consumes additional time. I
didn't expect, but this way the first replication delays in some
seconds respect an initial replication wihout where clauses. Is this
normal? Could be because I now haven't got many rows while this
attempts are being done?
Give me some feedback please. Regards.
|||This looks ok. I think you would be better off adding an int column to the
doctor and patient tables. Add the identity property to these columns, put
an index on them and then use these in your join condition. It will be more
efficient this way.
Other than that try to put an index on the unique identifier column to speed
up the queries.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Lonifasiko" <mloichate@.gmail.com> wrote in message
news:1128433459.393363.244450@.g14g2000cwa.googlegr oups.com...
> Hi again Hilary,
> My "final" where clause would be as follows:
> SELECT <columnas_publicadas> FROM [dbo].[Appointment] WHERE
> HOST_NAME() = convert(nvarchar(100), patientId) or HOST_NAME()
> = convert(nvarchar(100), doctorId) and isClosed = 0
> You can notice that I am now able to do the comparison between
> uniqueidentifier and nvarchar. I thougth this comparison had to be done
> in the other direction, but found this and worked. Would be better to
> convert host_name() to uniqueidentifier or left it this way?
> I've also noticed that this where clause consumes additional time. I
> didn't expect, but this way the first replication delays in some
> seconds respect an initial replication wihout where clauses. Is this
> normal? Could be because I now haven't got many rows while this
> attempts are being done?
> Give me some feedback please. Regards.
>

Row Filter not working

I have a snapshot/transactional replication publication with the following
row filter:
SELECT <published_columns> FROM <<TABLE>> WHERE ItemName
not in ('dbsvrip','otfrconn','mapdir') and AppTypeID<>7
The snapshot generated just fine, but now none of the changes to the table
are getting replicated.
I'm using sql2000.
Any ideas?
I resolved this problem by changing the WHERE CLAUSE to:
Itemname<>'dbsvrip' and Itemname<>'otfrconn' and Itemname<>'mapdir' and
AppTypeID<>7.
"Need more Zzzz" wrote:

> I have a snapshot/transactional replication publication with the following
> row filter:
> SELECT <published_columns> FROM <<TABLE>> WHERE ItemName
> not in ('dbsvrip','otfrconn','mapdir') and AppTypeID<>7
> The snapshot generated just fine, but now none of the changes to the table
> are getting replicated.
> I'm using sql2000.
> Any ideas?
>

Row Count using Group By and Having

Hi,
I have a query that uses Group By and Having to retrieve records.
The query is working fine and I want to get the Row Count of that Query.
How will I do that.
Thanks
Kiran
select count(*) from
(<Your sql querey > ) dr
Here dr is name for derived table
Hth
"Kiran" <Kiran@.nospam.net> wrote in message
news:uGd396EAFHA.1296@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I have a query that uses Group By and Having to retrieve records.
> The query is working fine and I want to get the Row Count of that Query.
> How will I do that.
> Thanks
> Kiran
>
>|||Thanks a lot
That's exactly what I wanted
Kiran
"AM" <shahdharti@.gmail.com> wrote in message
news:ud6otJFAFHA.2552@.TK2MSFTNGP09.phx.gbl...
>
> select count(*) from
> (<Your sql querey > ) dr
> Here dr is name for derived table
> Hth
> "Kiran" <Kiran@.nospam.net> wrote in message
> news:uGd396EAFHA.1296@.TK2MSFTNGP10.phx.gbl...
>|||If you are already executing the base query in the same batch, there are a
couple of extra considerations:
1) you may be double running a potentially expensive query putting extra
load on the server.
2) unless you encapsulate the two queries in a REPEATABLE READ transaction,
you could get inconsistent results.
in this case you can do:
<your select>
SELECT @.@.ROWCOUNT
Mr Tea
http://mr-tea.blogspot.com
"Kiran" <Kiran@.nospam.net> wrote in message
news:uGd396EAFHA.1296@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I have a query that uses Group By and Having to retrieve records.
> The query is working fine and I want to get the Row Count of that Query.
> How will I do that.
> Thanks
> Kiran
>
>

Row and set based ?

Hey
I've been doing for SQL for a while but recently I've been trying to get
some whitepaper for it, I'm now working through some questions I obviously
first I google the questions before I came here to ask.
I ran into the questions.
Give an example of a rowbased statement, and a set based statement. I'm sure
know how to code it but I don't know what it is.
Another question I ran into was "what setting do you ALWAYS disable" in a
live environment. Now I don't know this either... it's not a registered
Microsoft exam so I don't know if the questions are dodgy.
Answers would be appreciated though.
THanksHi Mal
Row based statement:
SELECT * FROM <TABLE> WHERE NEWID() = '<some ID>'
SET based statement
SELECT * FROM <TABLE>
best Regards,
Chandra
http://chanduas.blogspot.com/
---
"Mal" wrote:

> Hey
> I've been doing for SQL for a while but recently I've been trying to get
> some whitepaper for it, I'm now working through some questions I obviously
> first I google the questions before I came here to ask.
> I ran into the questions.
> Give an example of a rowbased statement, and a set based statement. I'm su
re
> know how to code it but I don't know what it is.
> Another question I ran into was "what setting do you ALWAYS disable" in a
> live environment. Now I don't know this either... it's not a registered
> Microsoft exam so I don't know if the questions are dodgy.
> Answers would be appreciated though.
> THanks|||When multiple rows are effected by a query, then that query is a set based
query.
if only one row gets effected by a query, then the query is rowbased.
best Regards,
Chandra
http://chanduas.blogspot.com/
---
"Mal" wrote:

> Hey
> I've been doing for SQL for a while but recently I've been trying to get
> some whitepaper for it, I'm now working through some questions I obviously
> first I google the questions before I came here to ask.
> I ran into the questions.
> Give an example of a rowbased statement, and a set based statement. I'm su
re
> know how to code it but I don't know what it is.
> Another question I ran into was "what setting do you ALWAYS disable" in a
> live environment. Now I don't know this either... it's not a registered
> Microsoft exam so I don't know if the questions are dodgy.
> Answers would be appreciated though.
> THanks|||Hi Mal
For Row by Row operations look at
http://www.sql-server-performance.com/dp_no_cursors.asp
For things to switch off then looks at Brads article:
http://www.sql-server-performance.c...mance_audit.asp
John
"Mal" wrote:

> Hey
> I've been doing for SQL for a while but recently I've been trying to get
> some whitepaper for it, I'm now working through some questions I obviously
> first I google the questions before I came here to ask.
> I ran into the questions.
> Give an example of a rowbased statement, and a set based statement. I'm su
re
> know how to code it but I don't know what it is.
> Another question I ran into was "what setting do you ALWAYS disable" in a
> live environment. Now I don't know this either... it's not a registered
> Microsoft exam so I don't know if the questions are dodgy.
> Answers would be appreciated though.
> THanks|||Chandra,
That was Terrific :(
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Chandra" <Chandra@.discussions.microsoft.com> wrote in message
news:A9EB58C2-F6D2-4244-B324-CF9330F1572A@.microsoft.com...
> Hi Mal
> Row based statement:
> SELECT * FROM <TABLE> WHERE NEWID() = '<some ID>'
>
> SET based statement
> SELECT * FROM <TABLE>
>
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> ---
>
> "Mal" wrote:
>|||Chandra wrote:
> Hi Mal
> Row based statement:
> SELECT * FROM <TABLE> WHERE NEWID() = '<some ID>'
>
This is not quite correct. SELECT statements generate sets of records (which
is why the term used forthe result of a SELECT statement is "resultset"). A
set can contain a single member.
When we talk about doing a "row by row" operation, we are normally talking
about doing something with a cursor. Looping through a set of rows,
processing one row at a time, is a "row by row" operation. Most experienced
developers frown on row by row processing, due to its usual lack of
performance in comparison to set-based operations.
Here is a set-based operation:
UPDATE <TABLE>
SET <COLUMN> = <value>
WHERE <COLUMN1> = <value1>
Here is the equivalent operation done row by row:
DECLARE @.compare <datatype>
DECLARE cur scroll cursor FOR
SELECT <COLUMN1>, <COLUMN> FROM <TABLE>
FOR UPDATE
open cur
FETCH FIRST FROM cur INTO @.compare
WHILE (@.@.FETCH_STATUS <> -1)
BEGIN
if @.@.FETCH_STATUS <> -2
BEGIN
IF @.compare = <value1>
UPDATE <TABLE>
SET <COLUMN> = <value>
WHERE CURRENT OF cur
FETCH NEXT FROM cur INTO @.compare
END
END
Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

routine to archive records

Howdy,
I'm working on a routine to archive records. It is
the basic copy a massive amount of records from table
a to table b and then delete the records copied from
table a. I plan to do this using a trigger for delete
on table a. Neither table has a clustered index tho
they both have some non clustered indexes.
Please look at the outline and let me know if this is
a good plan. Is there anything that I might do to
improve? I know that the trigger will affect the
delete query performance ... but I'm not sure how best
to make sure that the deleted records are copied to
the archive database.
First I'm going to create a following trigger on
the source table Active_DB.dbo.Important_Records
CREATE TRIGGER ArchiveRecord ON [dbo].[Important_Records]
FOR DELETE
AS
INSERT INTO Archive_DB.dbo.Important_Records
(...columns...)
SELECT ...columns...
FROM deleted
The actual delete routine will be something like:
USE Archive_DB
ALTER DATABASE Archive_DB
SET RECOVERY SIMPLE
--DISABLE TRANSACTION LOG BACKUP JOB
USE Active_DB
ALTER DATABASE Active_DB
SET RECOVERY SIMPLE
--DISABLE TRANSACTION LOG BACKUP JOB
DECLARE @.DayCount int
SET @.DayCount = 576 --(for example)
DECLARE @.RowsDeleted int
SET @.RowsDeleted = -1
SET ROWCOUNT 1000
WHILE @.RowsDeleted <> 0
BEGIN
DELETE
FROM dbo.Important_Records
WHERE EXISTS (SELECT OCCURRED WITH (NOLOCK) FROM
dbo.Important_Records WHERE < (getdate() - @.DayCount))
SET @.RowsDeleted = @.@.ROWCOUNT
END
END
SET ROWCOUNT 0
--sp_updatestats @.resample = 'resample'
CHECKPOINT
--ENABLE TRANSACTION LOG BACKUP JOB
ALTER DATABASE Active_DB
SET RECOVERY BULK_LOGGED
USE Archive_DB
--sp_updatestats @.resample = 'resample'
CHECKPOINT
--ENABLE TRANSACTION LOG BACKUP JOB
ALTER DATABASE Archive_DB
SET RECOVERY BULK_LOGGED
--Full Backup of Active_DB
--Full Backup of Archive_DBA faster way would be:
1. bcp the data out to a flat file
2. delete the data in batches (10000 rows each would be fine)
3. bulk insert the data in in batches
-oj
<NTuser_Man@.msn.com> wrote in message
news:1125450831.524829.115380@.z14g2000cwz.googlegroups.com...
> Howdy,
> I'm working on a routine to archive records. It is
> the basic copy a massive amount of records from table
> a to table b and then delete the records copied from
> table a. I plan to do this using a trigger for delete
> on table a. Neither table has a clustered index tho
> they both have some non clustered indexes.
> Please look at the outline and let me know if this is
> a good plan. Is there anything that I might do to
> improve? I know that the trigger will affect the
> delete query performance ... but I'm not sure how best
> to make sure that the deleted records are copied to
> the archive database.
> First I'm going to create a following trigger on
> the source table Active_DB.dbo.Important_Records
> CREATE TRIGGER ArchiveRecord ON [dbo].[Important_Records]
> FOR DELETE
> AS
> INSERT INTO Archive_DB.dbo.Important_Records
> (...columns...)
> SELECT ...columns...
> FROM deleted
> The actual delete routine will be something like:
> USE Archive_DB
> ALTER DATABASE Archive_DB
> SET RECOVERY SIMPLE
> --DISABLE TRANSACTION LOG BACKUP JOB
> USE Active_DB
> ALTER DATABASE Active_DB
> SET RECOVERY SIMPLE
> --DISABLE TRANSACTION LOG BACKUP JOB
> DECLARE @.DayCount int
> SET @.DayCount = 576 --(for example)
> DECLARE @.RowsDeleted int
> SET @.RowsDeleted = -1
> SET ROWCOUNT 1000
> WHILE @.RowsDeleted <> 0
> BEGIN
> DELETE
> FROM dbo.Important_Records
> WHERE EXISTS (SELECT OCCURRED WITH (NOLOCK) FROM
> dbo.Important_Records WHERE < (getdate() - @.DayCount))
> SET @.RowsDeleted = @.@.ROWCOUNT
> END
> END
> SET ROWCOUNT 0
> --sp_updatestats @.resample = 'resample'
> CHECKPOINT
> --ENABLE TRANSACTION LOG BACKUP JOB
> ALTER DATABASE Active_DB
> SET RECOVERY BULK_LOGGED
> USE Archive_DB
> --sp_updatestats @.resample = 'resample'
> CHECKPOINT
> --ENABLE TRANSACTION LOG BACKUP JOB
> ALTER DATABASE Archive_DB
> SET RECOVERY BULK_LOGGED
> --Full Backup of Active_DB
> --Full Backup of Archive_DB
>|||oj, thanks for the advice.
I haven't used bcp. This morning I experimented with the northwind db
to get a feel for bcp. I would like to know how can I ensure that the
records I bcp from the dbo.Important_Records are the very same records
that I delete from dbo.Important_Records? That is the concern that
leads me to want to use a trigger for delete.
oj wrote:
> A faster way would be:
> 1. bcp the data out to a flat file
> 2. delete the data in batches (10000 rows each would be fine)
> 3. bulk insert the data in in batches
> --
> -oj
>
> <NTuser_Man@.msn.com> wrote in message
> news:1125450831.524829.115380@.z14g2000cwz.googlegroups.com...|||bcp can take a query. That would be the way to extract only the desired
rows.
e.g.
bcp "select * from Northwind..Orders where OrderID<10250" queryout
"c:\Orders.txt" -T -w
-oj
<NTuser_Man@.msn.com> wrote in message
news:1125500222.595819.253710@.z14g2000cwz.googlegroups.com...
> oj, thanks for the advice.
> I haven't used bcp. This morning I experimented with the northwind db
> to get a feel for bcp. I would like to know how can I ensure that the
> records I bcp from the dbo.Important_Records are the very same records
> that I delete from dbo.Important_Records? That is the concern that
> leads me to want to use a trigger for delete.
> oj wrote:
>|||Thanks again for the input.
I have the bcp part working ... it copies data to text files and then
uploads to the destination beautifully.
But I'm having a performance issue on the delete query ( appended below
). The select statement runs very quickly and uses a clustered index
s. But when the delete action is held up by sorts on three non
clustered indexes. The sorts account for 76% of the query cost.
I'm wondering if it would make sense to drop the non clustered indexes
before running the delete. The table itself has 28 million records so
dropping and rebuilding the non clustered indexes might be quite a
chore in itself. My other thought was to modify the non clustered
indexes to include the clustered index ...
Query:
CREATE PROCEDURE dbo.DeleteImportant_Record
@.DayCount int
AS
SET NOCOUNT ON
SET ROWCOUNT 1000
WHILE 1=1
BEGIN
DELETE
FROM dbo.Important_Record
WHERE EXISTS (SELECT OCCURRED FROM dbo.Important_Record WITH
(NOLOCK) WHERE RecordDate < (getdate() - @.DayCount))
IF @.@.ROWCOUNT = 0
BREAK
END
SET ROWCOUNT 0
SET NOCOUNT OFF
GO
oj wrote:
> bcp can take a query. That would be the way to extract only the desired
> rows.
> e.g.
> bcp "select * from Northwind..Orders where OrderID<10250" queryout
> "c:\Orders.txt" -T -w|||There is really no need for the subquery. Try:
declare @.dt datetime
set @.dt=(getdate() - @.DayCount)
WHILE 1=1
BEGIN
DELETE
FROM dbo.Important_Record
WHERE RecordDate < @.dt
IF @.@.ROWCOUNT = 0
BREAK
END
-oj
<NTuser_Man@.msn.com> wrote in message
news:1125524729.117694.268840@.g49g2000cwa.googlegroups.com...
> Thanks again for the input.
> I have the bcp part working ... it copies data to text files and then
> uploads to the destination beautifully.
> But I'm having a performance issue on the delete query ( appended below
> ). The select statement runs very quickly and uses a clustered index
> s. But when the delete action is held up by sorts on three non
> clustered indexes. The sorts account for 76% of the query cost.
> I'm wondering if it would make sense to drop the non clustered indexes
> before running the delete. The table itself has 28 million records so
> dropping and rebuilding the non clustered indexes might be quite a
> chore in itself. My other thought was to modify the non clustered
> indexes to include the clustered index ...
>
> Query:
> CREATE PROCEDURE dbo.DeleteImportant_Record
> @.DayCount int
> AS
> SET NOCOUNT ON
> SET ROWCOUNT 1000
> WHILE 1=1
> BEGIN
> DELETE
> FROM dbo.Important_Record
> WHERE EXISTS (SELECT OCCURRED FROM dbo.Important_Record WITH
> (NOLOCK) WHERE RecordDate < (getdate() - @.DayCount))
> IF @.@.ROWCOUNT = 0
> BREAK
> END
> SET ROWCOUNT 0
> SET NOCOUNT OFF
>
> GO
>
> oj wrote:
>|||Hey, thanks for all the help. Now the query deletes over 10,000
records/minute. Now I have only 27188456 recods to go.
oj wrote:
> There is really no need for the subquery. Try:
> declare @.dt datetime
> set @.dt=(getdate() - @.DayCount)
> WHILE 1=1
> BEGIN
> DELETE
> FROM dbo.Important_Record
> WHERE RecordDate < @.dt
> IF @.@.ROWCOUNT = 0
> BREAK
> END
>
>
> --
> -oj
>
> <NTuser_Man@.msn.com> wrote in message
> news:1125524729.117694.268840@.g49g2000cwa.googlegroups.com...