Pinal Dave - SQL Server Performance Tuning Expert

SQL SERVER – Union vs. Union All – Which is better for performance?

March 10, 2007

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

This article is completely re-written with better example SQL SERVER – Difference Between Union vs. Union All – Optimal Performance Comparison. I suggest all of my readers to go here for update article. UNION The UNION command is used to select related information from two tables, much like the JOIN…

SQL SERVER – Download 2005 SP2a

March 7, 2007

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks, SQLAuthority News

DBA, SQL Download

Microsoft released an updated SQL Server 2005 SP2 on March 5th, 2007. The build number is 9.00.3042.01. The previous build number was 9.00.3042.00.Microsoft released a SP2a patch for the second service pack for SQL Server 2005 to fix the issues with the maintenance plans.If you have upgraded to SP2, use…

SQL SERVER – Script to Determine Which Version of SQL Server 2000-2005 is Running

March 7, 2007

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks

SQL Function, SQL Scripts

To determine which version of SQL Server 2000/2005 is running, connect to SQL Server 2000/2005 by using Query Analyzer, and then run the following code: SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition') The results are: The product version (for example, 8.00.534). The product level (for example, “RTM” or “SP2”). The edition…

SQL SERVER – QUOTED_IDENTIFIER ON/OFF and ANSI_NULL ON/OFF Explanation

March 5, 2007

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks

SQL Scripts

When create or alter SQL object like Stored Procedure, User Defined Function in Query Analyzer, it is created with following SQL commands prefixed and suffixed. What are these – QUOTED_IDENTIFIER ON/OFF and ANSI_NULL ON/OFF? SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO--SQL PROCEDURE, SQL FUNCTIONS, SQL OBJECTGO SET QUOTED_IDENTIFIER OFF…

SQL SERVER – Delete Duplicate Records – Rows

March 1, 2007

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks

Duplicate Records, SQL Scripts

Following code is useful to delete duplicate records. The table must have identity column, which will be used to identify the duplicate records. Table in example is has ID as Identity Column and Columns which have duplicate data are DuplicateColumn1, DuplicateColumn2 and DuplicateColumn3. DELETE FROM MyTable WHERE ID NOT IN…

SQL SERVER – T-SQL Script to find the CD key from Registry

February 28, 2007

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks

SQL Scripts, SQL Stored Procedure, SQL Utility

Here is the way to find SQL Server CD key, which was used to install it on machine. If user do not have permission on the SP, please login using SA username. Expended stored procedure xp_regread can read any registry values. I have used this XP to read CD_KEY. This…

SQL SERVER – What is New in SQL Server Agent for Microsoft SQL Server 2005

February 26, 2007

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks

SQL Download, SQL Error Messages, SQL Server Agent, SQL Utility

I came across this interesting and detailed article ‘What’s New in SQL Server Agent for Microsoft SQL Server 2005‘ on Microsoft TechNet. This article describes Security Improvements, New Roles in the msdb Database, Multiple Proxy Accounts, Performance Improvements, Performance Counters, New SQL Server Agent Subsystems, Shared Schedules, WMI Event Alerts,…