Pinal Dave - SQL Server Performance Tuning Expert

SQL SERVER – 2005 – DBCC ROWLOCK – Deprecated

April 26, 2007

Title says all. My search engine log says many web users are looking for DBCC ROWLOCK in SQL SERVER 2005. It is deprecated feature for SQL SERVER 2005. It is Automatically on for SQL SERVER 2005. More Deprecated Features of SQL SERVER 2005 Refer MSDN Discontinued Database Engine Functionality in…

SQL SERVER – Alternate Fix : ERROR 1222 : Lock request time out period exceeded

April 25, 2007

Pinal Dave

SQL

SQL Error Messages, SQL Lock, SQL Server, SQL Server Security

ERROR 1222 : Lock request time out period exceeded.

SQL SERVER – ERROR Messages – sysmessages error severity level

April 25, 2007

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks

SQL Error Messages, SQL Scripts, SQL Server Security

SQL ERROR Messages

Each error message displayed by SQL Server has an associated error message number that uniquely identifies the type of error. The error severity levels provide a quick reference for you about the nature of the error. The error state number is an integer value between 1 and 127; it represents information about the source that issued the error. The error message is a description of the error that occurred. The error messages are stored in the sysmessages system table.

SQL SERVER – 2005 Take Off Line or Detach Database

April 25, 2007

Pinal Dave

SQL, SQL Performance, SQL Server, SQL Tips and Tricks

SQL Scripts

EXEC sp_dboption N'mydb', N'offline', N'true' OR ALTER DATABASE [mydb] SET OFFLINE WITH ROLLBACK AFTER 30 SECONDS OR ALTER DATABASE [mydb] SET OFFLINE WITH ROLLBACK IMMEDIATE Using the alter database statement (SQL Server 2k and beyond) is the preferred method. The rollback after statement will force currently executing statements to rollback…

SQL SERVER – TRIM() Function – UDF TRIM()

April 24, 2007

Pinal Dave

SQL, SQL Performance, SQL Server, SQL Tips and Tricks

SQL Function, SQL Scripts

SQL Server does not have function which can trim leading or trailing spaces of any string. TRIM() is very popular function in many languages. SQL does have LTRIM() and RTRIM() which can trim leading and trailing spaces respectively. I was expecting SQL Server 2005 to have TRIM() function. Unfortunately, SQL…

SQL SERVER – Six Properties of Relational Tables

April 24, 2007

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks

Relational tables have six properties: Values Are Atomic This property implies that columns in a relational table are not repeating group or arrays. The key benefit of the one value property is that it simplifies data manipulation logic. Such tables are referred to as being in the “first normal form”…

SQL SERVER – 2005 Collation Explanation and Translation

April 24, 2007

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks

SQL Collation

Just a day before one of our SQL SERVER 2005 needed Case-Sensitive Binary Collation. When we install SQL SERVER 2005 it gives options to select one of the many collation. I says in words like ‘Dictionary order, case-insensitive, uppercase preference’. I was confused for little while as I am used…