Pinal Dave - SQL Server Performance Tuning Expert

SQL SERVER – Definition, Comparison and Difference between HAVING and WHERE Clause

July 4, 2007

SQL, SQL Interview Questions and Answers, SQL Server, SQL Tips and Tricks

In recent interview sessions in hiring process I asked this question to every prospect who said they know basic SQL. Surprisingly, none answered me correct. They knew lots of things in details but not this simple one. One prospect said he does not know cause it is not on this…

SQL SERVER – Comparison : Similarity and Difference #TempTable vs @TempVariable

July 3, 2007

Pinal Dave

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

SQL Scripts, SQL TempDB

#TempTable and @TempVariable are different things with different scope. Their purpose is different but highly overlapping. TempTables are originated for the storage and & storage & manipulation of temporal data. TempVariables are originated (SQL Server 2000 and onwards only) for returning date-sets from table-valued functions. Common properties of #TempTable and…

SQL SERVER – 2005 Comparison SP_EXECUTESQL vs EXECUTE/EXEC

July 2, 2007

Pinal Dave

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

SQL Cursor, SQL Download, SQL Scripts, SQL Stored Procedure

Common Properties of SP_EXECUTESQL and EXECUTE/EXEC The Transact-SQL statements in the sp_executesql or EXECUTE string are not compiled into an execution plan until sp_executesql or the EXECUTE statement are executed. The strings are not parsed or checked for errors until they are executed. The names referenced in the strings are…

SQL SERVER – Explanation of WITH ENCRYPTION clause for Stored Procedure and User Defined Functions

July 1, 2007

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks

SQL Function, SQL Server Encryption, SQL Stored Procedure

This article is written to answer following two questions I have received in last one week. Questions 1) How to hide code of my Stored Procedure that no one can see it? 2) Our DBA has left the job and one of the function which retrieves important information is encrypted,…

SQL SERVER – Fix : Error : Server: Msg 131, Level 15, State 3, Line 1 The size () given to the type ‘varchar’ exceeds the maximum allowed for any data type (8000)

June 30, 2007

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks

SQL Error Messages, SQL String

Error: Server: Msg 131, Level 15, State 3, Line 1 The size () given to the type ‘varchar’ exceeds the maximum allowed for any data type (8000) When the the length is specified in declaring a VARCHAR variable or column, the maximum length allowed is still 8000. Fix/WorkAround/Solution: Use either…

SQL SERVER – Recompile All The Stored Procedure on Specific Table

June 29, 2007

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks

Best Practices, SQL Stored Procedure

I have noticed that after inserting many rows in one table many times the stored procedure on that table executes slower or degrades. This happens quite often after BCP or DTS. I prefer to recompile all the stored procedure on the table, which has faced mass insert or update. sp_recompiles…

SQL SERVER – 2005 Improvements in TempDB

June 28, 2007

Pinal Dave

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

Best Practices, SQL Download, SQL TempDB

Following are some important improvements in tempdb in SQL Server 2005 over SQL Server 2000 Input/Output traffic to TempDB is reduced as logging is improved. In SQL Server 2005 TempDB does not log “after value” everytime. E.g. For INSERT it does not log after value on log as that will…