tags:

views:

19

answers:

1
Q: 

Any issues with join hints in a SQL Server 2000 view?

I have some ad-hoc reporting users hitting some SQL Server views. Occasionally the read locks taken by these users for particularly lengthy queries causes trouble elsewhere in the system.

I am considering adding some strategic with(nolock) hints to the views but wanted to know if there are any gotchas associated with hints in views.

Please ignore the obvious issues with letting users run queries this close to the SQL metal :).

Also, I know that nolock hints are an advanced feature not to be used lightly and I am well aware that they introduce fun things like dirty reads. Finally, if you're thinking that read_committed_snapshot makes sense here, I must sadly say that it's not available for 2000.

+1  A: 

There is a potential for the report to show an inconsistent view of data. That kind of thing is rare, though.

Still, a better strategy is to use replication to create a completely separate reports database.

Joel Coehoorn  2009-10-15 18:39:44
@Joel, thanks for the input and confirming what I recommended to my customer (transactional replication or something similar)! I'm looking at this to use in the mean time. Dirty reads are acceptable. +1, thanks!
Michael Haren  2009-10-15 18:45:56

related questions

Is there a "poor man's" alternative to RedGate for scripting out entire database schema?
My SQL Server 2000 Transaction Log Size Seems Small
Is there a way to script diagrams in SQL 2000 (or save them another way)?
Programatically renaming a table in SQL Server 2000 - is sp_rename the only solution?
I am getting a "fatal error 7987" error from SQL Server 2000
SQL Server 2000 and System.Transactions.TransactionScope()
Why should I upgrade from SQL2000 to SQL2005?
Can I protect against SQL Injection by escaping single-quote and surrounding user input with single-quotes?
Passing timestamp parameters in SQLAnalyzer to debug stored procedures
Linked Server Performance and options
SQL Server 2005: Importing data from SQL Server 2000
How do I perform a case-sensitive search and replace in SQL 2000/2005?
Warm Backup
Benefits of SQL Server 2005 over 2000
How can I create a human-readable script for every DTS package on a SQL server?
Deleting Rows from a SQL Table marked for Replication
In what situations would you get different users seeing different rows in a table on SQL Server?
Date object last modified
Improving SQL Server performance on VMware under Linux
The log file for database is full.
SQL Server 2000 - Debugging Deadlocks
Strategy for identifying unused tables in SQL Server 2000?
In SQL Server 2000, is there a sysobjects query that will retrieve user views and not system views?
Upgrading Sharepoint 3.0 to SQL 2005 Backend?
How do you deal with transport-level errors in SqlConnection?