Pinal Dave - SQL Server Performance Tuning Expert

SQL SERVER – Delay Function – WAITFOR clause – Delay Execution of Commands

June 18, 2007

Blocks the execution of a batch, stored procedure, or transaction until a specified time or time interval is reached, or a specified statement modifies or returns at least one row. This is very useful. Every day when I restore the database to backup server for reports post processing, I use…

SQL SERVER – De-fragmentation of Database at Operating System to Improve Performance

June 17, 2007

Pinal Dave

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

Computer Network, DBA, SQL Index, SQL Scripts

This issues was brought to me by our Sr. Network Engineer. While running operating system level de-fragmentation using either windows de-fragmentation or third party tool it always skip all the MDF file and never de-fragment them. He was wondering why this happens all the time. The reason MDF file are…

SQL SERVER – 2005 – UDF – User Defined Function to Strip HTML – Parse HTML – No Regular Expression

June 16, 2007

Pinal Dave

SQL Tips and Tricks

SQL Function, SQL Scripts, SQL Server, SQL String

One of the developers at my company asked is it possible to parse HTML and retrieve only TEXT from it without using regular expression. He wanted to remove everything between < and > and keep only Text. I found the question very interesting and quickly wrote UDF which does not use regular expression. Let us see how to parse HTML without regular expression.

SQL SERVER – sp_HelpText for sp_HelpText – Puzzle

June 15, 2007

Pinal Dave

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

SQL Scripts, SQL Stored Procedure

It was interesting to me. I was using sp_HelpText to see the text of the stored procedure. Stored Procedure were different so I had copied sp_HelpText on my clipboard and was pasting it in Query Editor of Management Studio. In rush I typed twice sp_HelpText and hit F5. Result was…

SQL SERVER – 2005 NorthWind Database or AdventureWorks Database – Samples Databases – Part 2

June 15, 2007

Pinal Dave

SQL

SQL Download, SQL Sample Database, SQL Scripts, SQL Server

I have mentioned the history of NorthWind, Pubs and AdventureWorks in my previous post SQL SERVER – 2005 NorthWind Database or AdventureWorks Database – Samples Databases. I have been receiving very frequent request for NorthWind Database for SQL Server 2005 and installation method.

SQL SERVER – Easy Sequence of SELECT FROM JOIN WHERE GROUP BY HAVING ORDER BY

June 14, 2007

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks

Database, DBA, SQL Scripts

I was called many times by Jr. Programmers in team to debug their SQL. I keep log of most of the problems and review them afterwards. This helps me to evaluate my team and identify most important next thing which I can do to improve the performance and productivity of…

SQL SERVER – Explanation SQL SERVER Hash Join

June 14, 2007

Pinal Dave

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

SQL Joins

Hash Join works with large data set. I have seen this join used many times in data warehouses applications as well as data mining algorithms. While its characteristics are similar to merge join it does not required ordered result set to join. Hash join requiresequijoin predicate to join tables. Equijoin…