SQL Index Archives - Page 38 of 47 - SQL Authority with Pinal Dave

SQL SERVER – Observation – Effect of Clustered Index over Nonclustered Index

February 4, 2009

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

SQL Constraint and Keys, SQL Index, SQL Scripts

Today I came across very interesting observation while I was working on query optimization. Let us run the example first. Make sure to to enable Execution Plan (Using CTRL + M) before running comparison queries. USE [AdventureWorks] GO /* */ CREATE TABLE [dbo].[MyTable]( [ID] [int] NOT NULL, [First] [nchar](10) NULL,…

SQL SERVER – FIX : ERROR : Msg 5834, Level 16, State 1, Line 1 The affinity mask specified conflicts with the IO affinity mask specified. Use the override option to force this configuration

January 31, 2009

Pinal Dave

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

SQL Error Messages, SQL Index, SQL Scripts, SQL Stored Procedure

Yesterday I came across following error while enabling fill factor for my database server, when I was trying to write article SQL SERVER – 2008 – 2005 – Rebuild Every Index of All Tables of Database – Rebuild Index with FillFactor. I ran following T-SQL script and it gave me…

SQL SERVER – 2008 – 2005 – Rebuild Every Index of All Tables of Database – Rebuild Index with FillFactor

January 30, 2009

Pinal Dave

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

SQL Cursor, SQL Index, SQL Scripts

I just wrote down following script very quickly for one of the project which I am working on. The requirement of the project was that every index existed in database should be rebuilt with fillfactor of 80. One common question I receive why fillfactor 80, answer is I just think…

SQL SERVER – Difference Between Index Scan and Index Seek

January 18, 2009

Pinal Dave

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

SQL Index, SQL Scripts

I have explained the concept of Index Scan and Index Seek earlier but I keep on receiving the same question again and again. Let us today look into it with little more depth. Before we go over the concept of scan and seek we need to understand what SQL Server…

SQL SERVER – Find Row Count in Table – Find Largest Table in Database – T-SQL

January 13, 2009

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks

SQL Index, SQL Scripts, SQL System Table, SQL Utility

I have written following script every time when I am asked by our team leaders or managers that how many rows are there in any particular table or sometime I am even asked which table has highest number of rows. Being Sr. Project Manager, sometime I just write down following…

SQL SERVER – Interesting Observation – Use of Index and Execution Plan

November 30, 2008

Pinal Dave

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

Best Practices, SQL Index, SQL Scripts

Previously I wrote article about SQL SERVER – Interesting Observation about Order of Resultset without ORDER BY and I have received tremendous response from my readers by emails and comments. Readers demanded that I should have written little more for the same subject. As I really liked the subject myself…

SQL SERVER – 2008 – Find If Index is Being Used in Database

October 3, 2008

Pinal Dave

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

SQL DMV, SQL Index, SQL Scripts

It is very often I get query that how to find if any index is being used in database or not. If any database has many indexes and not all indexes are used it can adversely affect performance. If number of index is higher it reduces the INSERT / UPDATE…