Porting stored procedures means preserving error, transaction, temporary-table, sequence, and date behavior instead of translating tokens.
SQL SERVER – 2005 – Retrieve Any User Defined Object Details Using sys objects Database
The sys.objects catalog view has a row for every schema-scoped object, so you can retrieve any user defined object details, like foreign keys, by object type.
SQL SERVER – 2005 – Retrieve Processes Using Specified Database
Jim Sz shared a quick script to retrieve processes that use a specific database from sys.sysprocesses, with spid, status, host name and login.
SQL SERVER – 2005 – What is CLR?
CLR is the Common Language Runtime. A diagram of its architecture, and why CLR stored procedures in C# help with the loops and recursion that slow down T-SQL.
SQL SERVER – FIX : Error : 3702 Cannot drop database because it is currently in use – Part 2
Still see Error 3702, cannot drop database because it is currently in use? Why closing Management Studio works and how to find the connection.
SQL SERVER – 2005 – Find Nth Highest Record from Database Table – Using Ranking Function ROW_NUMBER
Nicholas Paldino shared a neat way to find the Nth Highest Record from a table in SQL Server 2005 using the ranking function ROW_NUMBER. Here is his query.
SQL SERVER – How to Retrieve TOP and BOTTOM Rows Together using T-SQL – Part 3
Part 3 on how to retrieve TOP and BOTTOM rows together: Dave Arthur’s solution uses UNION ALL instead of an OR clause, and it is quite good.
SQL SERVER – How to Retrieve TOP and BOTTOM Rows Together using T-SQL – Part 2 – CTE
Part 2 of retrieving TOP and BOTTOM rows together: a reader’s solution using a CTE, the temporary result set introduced in SQL Server 2005.
Finding the Most Frequently Run Queries
Most frequently run queries can consume large SQL Server resources even when each call is fast; use plan cache and Query Store to find them.
SQL SERVER – Find Nth Highest Record from Database Table
The fourth Highest Record depends on whether ties occupy separate places. I define the ranking rule before choosing a query.