1Use the Table Variable in SQL 2000 instead of a temporary table. This is done all in memory than having to switch out to disk in the TempDB

2 Consider using a forward-only recordset the next time you need an updatable recordset. Many people think of forward-only recordsets as read-only, but you can actually update them. They can also provide better performance than dynamic or keyset recordsets.

3 Use the GRANT statement in SQL to give a user the ability to grant privileges to other users. The Enterprise Manager doesn't provide this feature.

4 You can modify the contents of the sysmessages system table to include custom messages. Adding custom messages can improve the quality of your error messages and make the return values of your stored procedures easier to understand.

5 Use the IN keyword in your WHERE clause to specify several conditionals instead of using several OR statements. For example, use this:

WHERE cCustomerState IN ('CT', 'NY', 'NJ')

instead of this:

WHERE cCustomerState = 'CT' OR cCustomerState = 'NY' OR cCustomerState = 'NJ'

6 The SQL Optimizer usually does a good job of determining your query's execution plan. However, if the SQL Optimizer doesn't provide optimal performance, you can provide it with performance hints when you create your SQL statement.

7 Use RELATIVE or ABSOLUTE keywords if you're traversing through a scrollable cursor. These two keywords can help you navigate directly to the desired location within the cursor.

8 Use the ROLLUP or CUBE keywords the next time you want to generate a report using SQL. They allow you to define queries that return not only individual line items, but summary and total data as well.

9 Try setting different values for the Lock Type property for the recordset object when you use ActiveX Data Objects (ADO) to modify your data. Use the Lock property to indicate which levels of locking recordsets you should use.

10 Indexing views is a new feature introduced in SQL Server 2000. Try adding indexes to your views to improve performance.