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

SQL SERVER – Unique Nonclustered Index Creation with IGNORE_DUP_KEY = ON – A Transactional Behavior

January 6, 2010

SQL Index, SQL Scripts, SQL Transactions

Earlier, I had written on SQL SERVER – Unique Nonclustered Index Creation with IGNORE_DUP_KEY = ON, and I received a comment regarding when this option can be useful. On the same day, I met Jacob Sebastian—my close friend and SQL Server MVP, I discussed this question with him. During our…

SQL SERVER – Unique Nonclustered Index Creation with IGNORE_DUP_KEY = ON

January 4, 2010

Pinal Dave

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

SQL Error Messages, SQL Index, SQL Scripts

In one of my recent training course, I was asked question regarding what is the importance of setting IGNORE_DUP_KEY = ON when creating unique nonclustered index. Here is the short answer: When nonclustered index is created without any option the default option is IGNORE_DUP_KEY = OFF, which means when duplicate…

SQL SERVER – Fillfactor, Index and In-depth Look at Effect on Performance

December 16, 2009

Pinal Dave

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

SQL Index, SQL Scripts

I would like to start this post with an interesting question: Where in MS SQL Server is “100” equals to “0”? And I am not talking about data types now.. Today I will be presenting the answer to this question and some topics related to it. Creating Indices in SQL…

SQL SERVER – White Paper – Partitioned Table and Index Strategies Using SQL Server 2008

December 13, 2009

Pinal Dave

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

Best Practices, SQL Index, SQL White Papers

Partitioned Table and Index Strategies Using SQL Server 2008 Writer: Ron Talmage, Solid Quality Mentors Technical Reviewer: Denny Lee, Wey Guy, Kevin Cox, Lubor Kollar, Susan Price – Microsoft Greg Low, Herbert Albert – Solid Quality Mentors When a database table grows in size to the hundreds of gigabytes or…

SQL SERVER – Size of Index Table for Each Index – Solution

November 30, 2009

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks

SQL Index, SQL Scripts

Earlier I have posted small question on this blog and requested help from readers to participate here and provide solution. Please read the original Puzzle here. SQL SERVER – Size of Index Table – A Puzzle to Find Index Size for Each Index on Table The puzzle was to write…

SQL SERVER – Remove Bookmark Key Lookup – 4 Different Ideas

November 22, 2009

Pinal Dave

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

SQL Index

I quite often get request to summarized my ideas about Removing bookmark lookup on this blog post. Bookmark lookup or key lookup are bad for any query as they force query engine to lookpup corresponding row in the table or index as it does not find required data from just…

SQL SERVER – Size of Index Table – A Puzzle to Find Index Size for Each Index on Table

November 18, 2009

Pinal Dave

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

SQL Index, SQL Scripts, SQL Stored Procedure

It is very easy to find out some basic details of any table using the following Stored Procedure. USE AdventureWorks GO EXEC sp_spaceused [HumanResources.Shift] GO Above query will return following resultset The above SP provides basic details such as rows, data size in table, and Index size of all the…