Stack Overflow
Stack Overflow

Questions tagged [clustered-index]

A clustered index determines the physical order of data in a table.

A clustered index determines the physical order of data in a table. As the clustered index dictates the physical storage order of the data in the table, a table can contain only one clustered index. However, the index can comprise multiple columns (a composite index), like the way a telephone directory is organized by last name and first name.

A clustered index is particularly efficient on columns that are often searched for ranges of values.

Source: MSDN (Using Clustered Indexes)

508 questions
6
votes
3 answers

What are the consequences of converting heap-indexes to clustered indexes on SQL Server?

I've recently got the advice, that I should convert all our tables from using heap indexes such that each table has a clustered index. What are the consequences of persuing this strategy? E.g. is it more important to regularly reorganize the…
Carlo V. Dango
  • 13,322
  • 16
  • 71
  • 114
6
votes
3 answers

How does the `primary key` keyword relate to clustered indexes in SQL Server?

How does the PRIMARY KEY keyword relate to clustered indexes in SQL Server? (Some people seem to want to answer this question instead of a different question I asked, so I am giving them a better place to do so.)
bbadour
  • 616
  • 1
  • 4
  • 19
6
votes
4 answers

Sql Server Legacy Database To Clustered index or not

We have a legacy database which is a sql server db (2005, and 2008). All of the primary keys in the tables are UniqueIdentifiers. The tables currently have no clustered index created on them and we are running into performance issues on tables…
Peter
  • 1,776
  • 13
  • 20
6
votes
3 answers

Add Primary Key to a table with existing clustered index

I have to work with a database to do reporting The DB is quite big : 416 055 104 rows Each row is very light though, just booleans and int ids. Each row is identify by 3 columns, but at my surprise, there is no Primary Key on it. Only a Clustered…
Stéphane
  • 11,755
  • 7
  • 49
  • 63
6
votes
5 answers

Does clustered index on foreign key column increase join performance vs non-clustered?

In many places it's recommended that clustered indexes are better utilized when used to select range of rows using BETWEEN statement. When I select joining by foreign key field in such a way that this clustered index is used, I guess, that…
alpav
  • 2,972
  • 3
  • 37
  • 47
6
votes
5 answers

Difference between Cluster and Non-cluster index in SQL

Just for knowledge in interview question, and my knowledge. SQL - Difference between Cluster and Non-cluster index?
AjmeraInfo
  • 506
  • 3
  • 10
  • 25
5
votes
4 answers

Changing newid() to newsequentialid() on an existing table

At the moment we have a number of tables that are using newid() on the primary key. This is causing large amounts of fragmentation. So I would like to change the column to use newsequentialid() instead. I imagine that the existing data will remain…
cbp
  • 25,252
  • 29
  • 125
  • 205
5
votes
4 answers

Removing a Primary Key (Clustered Index) to increase Insert performance

We've been experiencing SQL timeouts and have identified that bottleneck to be an audit table - all tables in our system contain insert, update and delete triggers which cause a new audit record. This means that the audit table is the largest and…
Kirk Broadhurst
  • 27,836
  • 16
  • 104
  • 169
5
votes
4 answers

Why does SQL Server use a non-clustered index over the clustered PK in a "select *" operation?

I've got a very simple table which stores Titles for people ("Mr", "Mrs", etc). Here's a brief version of what I'm doing (using a temporary table in this example, but the results are the same): create table #titles ( t_id tinyint not null…
gerrod
  • 6,119
  • 6
  • 33
  • 45
5
votes
1 answer

Solr approaches to re-indexing large document corpus

We are looking for some recommendations around systematically re-indexing in Solr an ever growing corpus of documents (tens of millions now, hundreds of millions in than a year) without taking the currently running index down. Re-indexing is needed…
gstathis
  • 4,753
  • 1
  • 17
  • 15
5
votes
1 answer

Best practice for indexing foreign keys on a large table

I have a large table (SQL Server 2008) with almost 3 million rows. It currently has a clustered index on it's primary key, and a non-clustered index on its foreign key. Almost all the queries on the table are reads based on the foreign key. Should…
cman77
  • 1,753
  • 1
  • 22
  • 48
5
votes
3 answers

ORDER and filter by add date while CLUSTERED IDENTITY PRIMARY KEY on row Id

I have a table (Orders) with CLUSTERED IDENTITY PRIMARY KEY (OrderId) and I am filtering and sorting the data by add date column (AddDate). Is there a way to tell the query optimizer that the AddDate is ordered the same way the OrderId is (so the…
Jozef Babinsky
  • 140
  • 9
5
votes
4 answers

Does SQL Server 2008 Non-Clustered Index Contain the Clustered Index Fields?

OK, I need this spelled out one more time. I've read the articles on-line, and I still haven't found a definitive answer. In SQL Server 2008, I have a "core" table with about 50k records and lots of read activity that is used in the same way in all…
Flipster
  • 4,373
  • 4
  • 28
  • 36
5
votes
2 answers

Does INDEX() create a clustered or non-clustered index in MySQL?

I am learning from a tutorial that uses INDEX() within a CREATE TABLE statement, but does not explain whether it is clustered or non-clustered. My question is: does INDEX() when used in a CREATE TABLE statement result in a clustered or non-clustered…
developer098
  • 773
  • 2
  • 10
  • 18
5
votes
3 answers

converting clustered index into non-clustered index?

Is it possible to convert a clustered index to non clustered index or non clustered index to clustered index in sql server 2005. Please convert this query into clustered index: create index index1 on mytable(firstcolumn) Please convert this query…
naveenkumar
  • 51
  • 1
  • 1
  • 2
1 2 3
…
33 34