All Articles - SQL Authority with Pinal Dave

SQL SERVER – SELECT vs. SET Performance Comparison

April 27, 2007

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

SQL Download, SQL Scripts, SQL Server Security

Usage: SELECT : Designed to return data. SET : Designed to assign values to local variables. While testing the performance of the following two scripts in query analyzer, interesting results are discovered. SET @foo1 = 1; SET @foo2 = 2; SET @foo3 = 3; SELECT @foo1 = 1, @foo2 =…

SQL SERVER – Difference Between Unique Index vs Unique Constraint

April 26, 2007

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks

SQL Constraint and Keys, SQL Index, SQL Scripts

Unique Index and Unique Constraint are the same. They achieve same goal. SQL Performance is same for both. Add Unique Constraint ALTER TABLE dbo.<tablename> ADD CONSTRAINT <namingconventionconstraint> UNIQUE NONCLUSTERED ( <columnname> ) ON [PRIMARY] Add Unique Index CREATE UNIQUE NONCLUSTERED INDEX <namingconventionconstraint> ON dbo.<tablename> ( <columnname> ) ON [PRIMARY] There…

SQL SERVER – Enable xp_cmdshell using sp_configure

April 26, 2007

Pinal Dave

SQL

Powershell, SQL Advanced Option, SQL Scripts, SQL Server, SQL Server Security, SQL Stored Procedure, SQL Utility

The xp_cmdshell option is a server configuration option that enables system administrators to control whether the xp_cmdshell extended stored procedure can be executed on a system.

SQL SERVER – 2005 – DBCC ROWLOCK – Deprecated

April 26, 2007

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks

SQL Deprecated Feature, SQL Server DBCC

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…