I compared a SQL Server 2005 paging query using ROW_NUMBER() with the SQL 2000 way of paging through temp or derived tables, both run on AdventureWorks.
SQL SERVER – 2005 – Performance Dashboard Reports
SQL Server 2005 Performance Dashboard Reports show CPU, IO, blocking and missing index problems as they happen. How to set them up and read them well.
SQL SERVER – TempDB is Full. Move TempDB from one drive to another drive.
TempDB is full and you see Event ID 17052 for the tempdb log? Here is what to check first and how to move TempDB from one drive to another.
SQL SERVER – 2005 Best Practices Analyzer (February 2007 CTP)
What the SQL Server 2005 Best Practices Analyzer did, how to act on its report, and why checking your servers on a regular schedule still matters.
SQL SERVER – Index Seek Vs. Index Scan (Table Scan)
Index Scan retrieves all the rows from the table. Index Seek retrieves selective rows from the table.
SQL SERVER – Difference between DISTINCT and GROUP BY – Distinct vs Group By
What is the difference between DISTINCT and GROUP BY? They usually produce the same plan. Use GROUP BY for aggregates and DISTINCT to remove duplicates.
SQL SERVER – Fix : Error 8101 An explicit value for the identity column in table can only be specified when a column list is used and IDENTITY_INSERT is ON
Error 8101 means you gave a value for an identity column. What IDENTITY_INSERT does, why there are two different errors, and why TRY CATCH misses it.
SQL SERVER – Fix : Error 701 There is insufficient system memory to run this query
For Error 701, insufficient system memory, compare max server memory with min server memory, check the paging file size, and free caches with DBCC commands.
SQL SERVER – @@IDENTITY vs SCOPE_IDENTITY() vs IDENT_CURRENT – Retrieve Last Inserted Identity of Record
@@IDENTITY vs SCOPE_IDENTITY() vs IDENT_CURRENT: how each one returns the last identity value, and why a trigger can make @@IDENTITY give the wrong one.
SQL SERVER – Stored Procedure – Clean Cache and Clean Buffer
DBCC FREEPROCCACHE will invalidate all stored procedure plans that the optimizer has cached in memory. Let us learn how to clean cache.