All Articles - SQL Authority with Pinal Dave

SQL SERVER – 2005 – Explanation of TRY…CATCH and ERROR Handling

April 11, 2007

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

SQL Error Messages, SQL Scripts, SQL Server Security

SQL Server 2005 offers a more robust set of tools for handling errors than in previous versions of SQL Server. Deadlocks, which are virtually impossible to handle at the database level in SQL Server 2000, can now be handled with ease. By taking advantage of these new features, you can…

SQL SERVER – 2005 – Silent Installation – Unattended Installation

April 10, 2007

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks

SQL Scripts, SQL Server Security

Silent SQL Server 2005 Installation is possible in two steps. 1) Creating an .ini file The SQL Server CD contains a template file called template.ini . Based on that create another required .ini file which includes a single [Options] section containing multiple parameters, each relating to a different feature or…

SQL SERVER – SP Performance Improvement without changing T-SQL

April 10, 2007

Pinal Dave

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

SQL Stored Procedure

There are two ways, which can be used to improve the performance of Stored Procedure (SP) without making T-SQL changes in SP. Do not prefix your Stored Procedure with sp_. In SQL Server, all system SPs are prefixed with sp_. When any SP is called which begins sp_ it is…

SQL SERVER – 2005 Reserved Keywords

April 9, 2007

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks

Microsoft SQL Server 2005 uses reserved keywords for defining, manipulating, and accessing databases. Reserved keywords are part of the grammar of the Transact-SQL language that is used by SQL Server to parse and understand Transact-SQL statements and batches. It is not legal to include the reserved keywords in a Transact-SQL…

SQL SERVER – Search Text Field – CHARINDEX vs PATINDEX

April 8, 2007

Pinal Dave

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

SQL Function, SQL Scripts, SQL Server, SQL Server Security, SQL Utility

We can use either CHARINDEX or PATINDEX to search in TEXT field in SQL SERVER. The CHARINDEX and PATINDEX functions return the starting position of a pattern you specify. Both functions take two arguments. With PATINDEX, you must include percent signs before and after the pattern, unless you are looking…

SQL SERVER – DBCC Commands Introduced in SQL Server 2005

April 7, 2007

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks

SQL Server DBCC

SQL Server 2005 has introduced following two documented and five undocumented DBCC Commands. I was able to find documentation for only first one online. If you find any documentation of any other DBCC Commands please add comments. It will be helpful to all of us. Documented: freesessioncache () — no…

SQL SERVER – Fix: Server: Msg 7391, Level 16, State 1, Line 1

April 6, 2007

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks

DBA, SQL Download, SQL Error Messages, SQL Server DBCC, SQL Server Security

I have received this error many times on different servers in my careers. There is no single fix for this Error. Server: Msg 7391, Level 16, State 1, Line 1 can happen due to many reasons. I have used various of this reasons with few of my servers. Please refer…

SQL SERVER – Performance Optimization of SQL Query and FileGroups

April 5, 2007

Pinal Dave

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

Best Practices, SQL Download, SQL Index

It is suggested to place transaction logs on separate physical hard drives. In this manner, data can be recovered up to the second in the event of a media failure. In SQL 2005 When database is created without specifying a transaction log size, the transaction log will be re-sized to…

SQL SERVER – Fix: HResult 0x274D, SQLCMD Level 16, State 1 Error: Microsoft SQL Native Client : Login timeout expired

April 4, 2007

Pinal Dave

SQL

SQL Error Messages, SQL Server, SQL Server Security, sqlcmd

While Working with SQLCMD in SQL Server 2005 I encountered following error. Let us learn in this blog post how we can solve Fix: HResult 0x274D, Level 16, State 1 Error: Microsoft SQL Native Client : Login timeout expired.

SQL SERVER – T-SQL Paging Query Technique Comparison – SQL 2000 vs SQL 2005

April 3, 2007

Pinal Dave

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

SQL Download, SQL Scripts, SQL Stored Procedure

I was doing paging in SQL Server 2000 using Temp Table or Derived Tables. I decided to checkout new function ROW_NUMBER() in SQL Server 2005. ROW_NUMBER() returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.…