SQL Performance Archives - Page 76 of 101 - SQL Authority with Pinal Dave

SQL SERVER – Query Optimization – Remove Bookmark Lookup – Remove RID Lookup – Remove Key Lookup – Part 2

October 8, 2009

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

This article is follow up of my previous article SQL SERVER – Query Optimization – Remove Bookmark Lookup – Remove RID Lookup – Remove Key Lookup. Please do read my previous article before continuing further. I have described there two different methods to reduce query execution cost. Let us compare…

SQL SERVER – Query Optimization – Remove Bookmark Lookup – Remove RID Lookup – Remove Key Lookup

October 7, 2009

Pinal Dave

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

SQL Index, SQL Scripts

Today, I would like to share one very quick tip about how to remove bookmark lookup or RID lookup. Let us first understand Bookmark lookup or RID lookup. Please note that from SQL Server 2005 SP1 onwards, Bookmark look up is known as Key look up. When a small number…

SQL SERVER – Outer Join in Indexed View – Question to Readers

September 25, 2009

Pinal Dave

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

SQL Index, SQL Joins, SQL View, SQL White Papers

Today I have question for you. Just a day ago I was reading whitepaper Improving Performance with SQL Server 2008 Indexed Views. Following is question and answer I read in the white paper. Q. Why can’t I use OUTER JOIN in an indexed view? A. Rows can logically disappear from…

SQL SERVER – Interesting Observation – Index on Index View Used in Similar Query

September 24, 2009

Pinal Dave

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

SQL Index, SQL Scripts, SQL White Papers

Recently, I was working on an optimization project for one of the large organizations. While working on one of the queries, we came across a very interesting observation. We found that there was a query on the base table and when the query was run, it used the index, which…

SQL SERVER – Execution Plan and Results of Aggregate Concatenation Queries Depend Upon Expression Location

September 20, 2009

Pinal Dave

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

SQL Documentation

I was reading the blog of Ward Pond, and I came across another note of Microsoft. I really found it very interesting. The given explanation was very simple; however, I would like to rewrite it again. Let us execute the following script. This script inserts two values ‘A’ and ‘B’…

SQL SERVER – Converting Stored Procedure into Table Valued Function

September 17, 2009

Pinal Dave

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

SQL Function, SQL Stored Procedure

In one of my recent articles, I mentioned the use of Table Valued Function (TVF) instead of Stored Procedure (SP). I received a follow up email asking what type of SP can be converted into a TVF. This is indeed a very interesting question! In fact, not all the SPs…

SQL SERVER – Index Seek vs. Index Scan – Diffefence and Usage – A Simple Note

August 24, 2009

Pinal Dave

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

SQL Index, SQL Server

In this article we shall examine the two modes of data search and retrieval using indexes- index seek and index scan, and the differences between the two.