Database Administrators Stack Exchange
Database Administrators Stack Exchange

Questions tagged [clustered-index]

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

A CLUSTERED INDEX is used to organize an entire table in a specified order. The data pages in the clustered index are the pages of the table - the index itself contains all the data.

A helpful analogy is a phone book - the pages are in the order of LastName, Firstname but contain all the data about individual entries as well.

Similar concepts exist in other database systems, such as INDEX ORDERED TABLES in .

334 questions
44
votes
3 answers

Is the concept of a clustered index in a DB design sensical when using SSDs?

When designing a SQL server data schema and the subsequent queries, sprocs, views, etc. does the notion of a clustered index and order of data on disk make any sense to consider for DB designs made explicitly to be deployed on SSD…
Matthew
  • 1,693
  • 2
  • 17
  • 27
40
votes
3 answers

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 with a non clustered index on the same key columns…
Martin Smith
  • 87,941
  • 15
  • 255
  • 354
40
votes
6 answers

Why do sequential GUID keys perform faster than sequential INT keys in my test case?

After asking this question comparing sequential and non-sequential GUIDs, I tried to compare the INSERT performance on 1) a table with a GUID primary key initialized sequentially with newsequentialid(), and 2) a table with an INT primary key…
someName
  • 591
  • 1
  • 4
  • 5
38
votes
2 answers

Does the order of columns in a PK index matter?

I have a few very large tables with the same basic strucure. Each one has a RowNumber (bigint) and DataDate (date) column. Data is loaded using SQLBulkImport every night, and no "new" data is ever loaded - its a historical record (SQL Standard,…
ImmortalStrawberry
  • 867
  • 6
  • 12
  • 18
35
votes
3 answers

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 seems that this happens especially with small…
jrara
  • 5,393
  • 20
  • 58
  • 65
33
votes
3 answers

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, COL_C FROM STAGING_TABLE My question is - does it…
GWR
  • 2,847
  • 9
  • 35
  • 42
32
votes
3 answers

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 now I am curious in what usage scenarios a HEAP…
marc.d
  • 423
  • 1
  • 4
  • 5
27
votes
1 answer

Splitting Snapshot files with MaxBCPThreads for Transactional Replication

I've just set up a publication, and I'm attempting to get the snapshot to apply faster. So far the Distribution Agent is respecting the MaxBCPThreads settings, but the Snapshot Agent is not. I'm expecting it to split the files so the threads on the…
b1kjsh
  • 281
  • 2
  • 7
23
votes
5 answers

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 the days of SQL Server 6.0. One of the rules…
bernd_k
  • 12,369
  • 24
  • 79
  • 111
22
votes
1 answer

In SQL Server, why can a backward scan of clustered index cannot not use parallelism?

I've been reading about SQL Server internals and every book or blog mentions this about backward scans. A backward scan of a clustered index cannot use parallelism The only post that said something is this one below. The post says that the SQL…
Kishan Dasari
  • 329
  • 1
  • 4
22
votes
3 answers

What index to use with lots of duplicate values?

Let's make a few assumptions: I have table that looks like this: a | b ---+--- a | -1 a | 17 ... a | 21 c | 17 c | -3 ... c | 22 Facts about my set: Size of the whole table is ~ 1010 rows. I have ~ 100k rows with value a in column a,…
foo
  • 323
  • 1
  • 2
  • 6
20
votes
2 answers

PostgreSQL difference between VACUUM FULL and CLUSTER

I have a table with 200 GB of size occupied by data and 180 GB of size by the 6 indexes on it. It is 30% bloated, so I want to reclaim unwanted space occupied by it. It is clustered on job_id_idx index. So to reclaim the space do I need to use…
Arun P
  • 201
  • 1
  • 2
  • 3
20
votes
1 answer

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 asked before, but the OP wasn't too well received.…
EBarr
  • 690
  • 6
  • 13
18
votes
4 answers

Is it a good idea to Partition a table (in MS SQL) based on date, when there's a clustered index present on ID (INT)

I have a table in MS SQL Server . Table Size: 806 GB Rows : 1.2 billion Index Space : 1.2 GB Table Usage: Logging from the Web Service calls 99.9% is usage is from the logging, developers look rarely into this table in Prod(only when an issue is…
User M
  • 283
  • 2
  • 6
17
votes
2 answers

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?
goric
  • 1,646
  • 3
  • 18
  • 25
1
2 3
…
22 23