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

SQL SERVER – Find Statistics Update Date – Update Statistics

January 25, 2010

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

Statistics are one of the most important factors of a database as it contains information about how data is distributed in the database objects (tables, indexes etc). It is quite common to listen people talking about not optimal plan and expired statistics. Quite often I have heard the suggestion to…

SQL SERVER – Fragmentation – Detect Fragmentation and Eliminate Fragmentation

January 12, 2010

Pinal Dave

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

SQL Index, SQL Scripts

Q. What is Fragmentation? How to detect fragmentation and how to eliminate it? A. Storing data non-contiguously on disk is known as fragmentation. Before learning to eliminate fragmentation, you should have a clear understanding of the types of fragmentation. We can classify fragmentation into two types: Internal Fragmentation: When records…

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

January 6, 2010

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks

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…