[dba-SQLServer]

Thanks for everyone's responses.

I thought that nolock will show uncommitted transactions which includes 
inserts and updates.  Before transaction is rolled back, the select will 
display the inserts in the transactions.

I leaning towards option #1 because it is 6 times faster than option #2 (we 
can put index on ID but not on flagged field because there are only two 
possible values).

Billy


>From: "Djabarov, Robert" <Robert.Djabarov at usaa.com>
>Reply-To: dba-sqlserver at databaseadvisors.com
>To: <dba-sqlserver at databaseadvisors.com>
>Subject: RE: [dba-SQLServer]
>Date: Tue, 14 Oct 2003 13:40:58 -0500
>
>Actually, uncommitted insert will not show up in the select with nolock.
>Uncommitted update will be viewed with old data and uncommitted delete
>will still show the record that would be deleted.  Since the situation
>here concerns only inserts then new records will not show up in the
>select until they are committed.
>
>I would recommend to go with a modified version of #2, - identify the
>first and the last id's of records that need to be exported prior to
>exporting them and updating the flag to "exported".
>
>-----Original Message-----
>From: dba-sqlserver-bounces at databaseadvisors.com
>[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of
>Francisco H Tapia
>Sent: Tuesday, October 14, 2003 1:24 PM
>To: dba-sqlserver at databaseadvisors.com
>Subject: Re: [dba-SQLServer]
>
>
>if you SELECT * FROM TableName WITH(NOLOCK) you won't have the locking
>issues as you are providing the hint not to lock the table.  Some
>side-effects that any uncommitted insert will show up with this type of
>select.
>
>additionally a quick select every 5minutes should suffice and be quick
>enough even w/o the hint (depending on your rate of input).
>
>lastly you can also look at replication to get the records out of there
>or even logshipping.
>
>
>
>
>Billy Pang wrote:
>
> > Hello:
> >
> > Our IIS Server is logging website hits to SQL Server.  We are trying
> > to
> > figure out best way to export certain records from this table into
> > another table every 5 minutes.  So far, we came up with two options:
> >
> > 1) in a separate table, store the ID of the last record in the IIS Log
> > table that was exported.  Every 5 minutes, export the records that are
>
> > greater than the last exported ID.
> >
> > 2) pick one of the columns in the log table to be the "Exported" flag
> > column.  After exporting a record from the log table, update that
>record
> > as exported.
> >
> > We are leaning towards #2 but am unsure about any locking issues.
> > AKAIK, IIS only inserts records into that table, never update.
> >
> > Any ideas or thoughts?
> >
> > Thanks in advance,
> > Billy
>
>
>
>--
>-Francisco
>
>
>_______________________________________________
>dba-SQLServer mailing list
>dba-SQLServer at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
>http://www.databaseadvisors.com
>
>_______________________________________________
>dba-SQLServer mailing list
>dba-SQLServer at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
>http://www.databaseadvisors.com
>

_________________________________________________________________
MSN 8 helps eliminate e-mail viruses. Get 2 months FREE*.  
http://join.msn.com/?page=features/virus