Questions tagged [clustered-index]

A type of index mostly used in SQL-Server, which aligns the data of a table with the index.

Filter by
Sorted by
Tagged with
29
votes
2answers
31k views

Why index REBUILD does not reduce index fragmentatation?

I have used ALTER INDEX REBUILD to remove index fragmentation. In some cases REBUILD does not seem to remove this fragmentation. What are the reasons why REBUILD does not remove fragmentation? It ...
22
votes
4answers
4k views

Is 'Avoid creating a clustered index based on an incrementing key' a myth from SQL Server 2000 days?

Our databases consist of lots of tables, most of them using an integer surrogate key as a primary key. About half of these primary keys are on identity columns. The database development started in ...
8
votes
1answer
4k views

Storage order vs Result order

This is a spin-off question from Sort order specified in primary key, yet sorting is executed on SELECT. @Catcall says this on the subject of storage order (clustered index) and the output order A ...
19
votes
1answer
2k views

What factors go into an Indexed View's Clustered Index being selected?

Briefly What factors go into they query optimizer's selection of an indexed view's index? For me, indexed views seem to defy what I understand about how the Optimizer picks indexes. I've seen this ...
39
votes
3answers
5k views

Performance of Non Clustered Indexes on Heaps vs Clustered Indexes

This 2007 White Paper compares the performance for individual select/insert/delete/update and range select statements on a table organized as a clustered index vs that on a table organized as a heap ...
17
votes
2answers
1k views

Is there any tangible difference between a unique clustered index and a clustered primary key?

I understand that there may be a difference in meaning or intent between the two, but are there any behavioral or performance differences between a clustered primary key and a clustered unique index?
31
votes
3answers
8k views

What are valid usage scenarios for HEAP tables?

I am currently doing some data imports into a legacy system and discovered that this system does not use a single clustered index. A quick Google search introduced me to the concept of HEAP tables and ...
28
votes
3answers
57k views

Efficient INSERT INTO a Table With Clustered Index

I have a SQL statement that inserts rows into a table with a clustered index on the column TRACKING_NUMBER. E.G.: INSERT INTO TABL_NAME (TRACKING_NUMBER, COLB, COLC) SELECT TRACKING_NUMBER, COL_B, ...
15
votes
2answers
8k views

What is a Clustered Index?

I need a short explaining of clustered index. What is a clustered index? What are best practices for using the clustered index?
5
votes
2answers
808 views

Howto Identify Clustered Index Candidates

I find myself in a situation where I have inherited the db and people complain about database performance. I identified about a dozend of tables not having a clustered index or a not carefully planned ...
10
votes
2answers
11k views

Why disabling a clustered index makes the table inaccessible?

When an index is disabled, the definition remains in the system catalog but is no longer used. SQL Server does not maintain the index (as data in the table changes), and the index cannot be used to ...
1
vote
1answer
2k views

What happens when you delete a Clustered index and Non Clustered Index once created

What happens when we dropped a Cluster index and Non Clustered Index once they are created? What will happen to the data at the leaf level according to Clustered Index ?
11
votes
1answer
4k views

noexpand hint for non-enterpise edition and performance

I have to use Indexed views to reach performance. As I can see from this comparison table standard edition does not support indexed views. But BOL says: Indexed views can be created in any ...
3
votes
1answer
2k views

Is a clustered index on column A the same as creating a table ordered by A?

Reading the official PostgreSQL documentation for version 9.0 I read an interesting escamotage that performs better than CLUSTER for big tables: The CLUSTER command reorders the original table by ...
0
votes
2answers
199 views

Which should I use on my DB? - Clustered Index or Non Clustered Index or Both

I just need advice on how I am going to optimize my database. Date ---- Time ---- Area ---- Block ---- Data1 ---- Data2 ---- Data3 ---- DataN I have that format of tables on my database. Each tables ...
6
votes
2answers
5k views

Why SQL Server has 900 byte index size limit

Why can't we create an index having size greater than 900 bytes? This question was raised in a presentation to which no one had an answer. I can think of this way to answer this question. Page size ...
2
votes
3answers
169 views

Why does sql server prefer the nonclustered index over the clustered index?

I am trying to speed up a table and as I was experimenting I ran into this (what I think is) odd occurrence. I created a clustered index and a nonclustered index that should be the same thing. However,...
6
votes
2answers
570 views

Need for reaching data through clustered index with a non-clustered index

I have found that when a table has both clustered and non-clustered indexes (on different columns), the leaf level non-clustered pages, instead of pointing to the data row, point to the node of the ...
5
votes
1answer
701 views

Column Store Index over an clustered index (created when primary key is declared) - SQL Server

When a clustered index is created, the table itself becomes the index structure sorted by the index key. Imagine now that we have 5 columns in a table XPTO (a,b,c,d,e) and "a" is the primary key and ...
3
votes
1answer
811 views

NEWSEQUENTIALID() resets sequence on server restart

MSDN learns me that NEWSEQUENTIALID(): Creates a GUID that is greater than any GUID previously generated by this function on a specified computer since Windows was started. After restarting Windows,...
11
votes
2answers
3k views

SUM of DATALENGTHs not matching table size from sys.allocation_units

I was under the impression that if I were to sum the DATALENGTH() of all fields for all records in a table that I would get the total size of the table. Am I mistaken? SELECT SUM(DATALENGTH(Field1)) ...
6
votes
3answers
6k views

Cannot rebuild index, but there's no reason why not?

I've created a process whereby I am able to only rebuild indexes that need rebuilding(the process takes an hour and a half if I rebuild them all), and while it works beautifully, it gets stuck on one ...
4
votes
1answer
540 views

Does rebuilding a clustered index on an IDENTITY column do anything useful?

We have a COTS product that has created a very large transaction log table. It has a single clustered index which supports an IDENTITY column declared as a key. Last night the index got rebuilt and ...
3
votes
1answer
80 views

Moving Clustered Index to new filegroup results in excessive filegrowth

I've Moved the clustered index on large tables to new filegroups to defrag, compress, and to recover space more easily from the original "primary" filegroup. Problem: A 315GB compressed Clustered ...