Currently browsing tag

index

Copying Table with INDEX and CONSTRAINTS

Copying Table with INDEX and CONSTRAINTS Introduction When we are trying to copy a table (Structure only or with Data) we are unable to copy the Indexes, constraint with it. Can we copy Indexes and Constraint with Table structure in the same database? This article is related to it. General …

Foreign Key and Index Binding

Foreign Key and Index Binding Introductions In this article I am trying to discuss about the foreign key Index binding features. Hope it will be interesting and we can learn something new from it. Point in focus 1.    Foreign Key Definitions 2.    Why Index Binding is necessary 3.    What we …

Index of Materialized View Not Working

Index of Materialized View Not Working Introduction I got a mail from one of my closed friends related to “Materialized view Index not working”. I am little bit confused after getting this mail. When I am getting closer look at the problem that I understand what he mean by “Materialized …

Fill Factor

Fill Factor Introduction In my previous article, I am trying to discuss about the MS SQL Server Index and how the Index Boost up the performance of SQL Server Query. Fill Factors is directly related to Index and Index is related to performance. So without the proper knowledge of Fill …

Scenario based Index type selection

Scenario based Index type selection Introduction When we are thinking about index we have different options in mid to use them, such as Clustered / Non Clustered etc. It is very important to understand in which scenario what type of Index gives us the performance boost up. As we all …

Index and Statistics

Index and Statistics Introductions Microsoft SQL Server collects statistical information related to indexes and column data stored in the database. These statistics are used by the SQL Server query optimizer to choose the most efficient plan for retrieving or updating data. If we understand the index statistics in better way, …

Index Defragmentation

Index Defragmentation In flashback In my previous article, I am trying to discuss related to Index architecture, how clustered index works and the fragmentation of the index. If you needed any references, you can find it from Related to Clustered Index http://sqlknowledgebank.blogspot.in/2012/07/related-to-clustered-index.html Index fragmentation http://sqlknowledgebank.blogspot.in/2012/07/index-fragmentation.html Introductions In this article I …

Index and Disk Space

Index and Disk Space Introductions Disk space is a very important factor to Create, Rebuild or Drop Index. Disk space causes the performance degradation of an index or even fails. So the understanding about disk space is very important factors.  In this article I am trying to discuss related to …

Filtered Non Clustered Index

Filtered Non Clustered Index Index is a most important factor for boosting up database performance. Choosing the right index for right columns in the table objects is one of the challenge factors, where performance is the key factors. Here in this article I am trying to discuss related to FILTER …

Index and Sort Order

Index and Sort Order Introduction When we write the SQL Query we frequently use the ORDER BY clause to get the output in definite order (Ascending or descending). But ORDER BY clause has deep impacts in the performance of the query executions. In this article I am trying to discuss …

Related to Clustered Index

Related to Clustered Index When we think about the performance factors, the Index plays the main role. In this article I am trying to discuss some points related to CLUSTERD INDEX. The main tropics, that I am trying to highlight is mentioned bellow. 1.    Cluster index Structure 2.    How the …

Index Fragmentation

Index Fragmentation This article is related to Index Fragmentation. Basic of Index Fragmentation Index fragmentation is a phenomenon where index contents are no longer stored continuously in the storage. When index contents become scattered in the storage, fragmented, performance on index will degrade. There are 2 types of Index fragmentation …

PRIMARY KEY and INDEX

PRIMARY KEY and INDEX This article is related to PRIMARY KEY and INDEX. A common misconnects is that the PRIMARY KEY is always CUSTERED index.   Before going to the depth of this article I want to ask some common questions. My questions are mentioned bellow. 1.    Is PRIMARY KEY …

The general guidelines for Index

The general guidelines for Index We all know that the INDEX is a key factor of performance tuning. As proper index can increase the performance and bad implementation of index implementation can dramatically decreases the performance.  In this article I am trying to gather some facts from different sources, which …