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
8
votes
6 answers

Clustered indexes on non-identity columns to speed up bulk inserts?

My two questions are: Can I use clustered indexes to speed up bulk inserts in big tables? Can I then still efficiently use foreign key relationships if my IDENTITY column is not the clustered index anymore? To elaborate, I have a database with a…
littlegreen
  • 7,290
  • 9
  • 45
  • 51
8
votes
3 answers

Clustered Indexes without Primary key

A clustered index stores the actual data rows at the leaf level of the index. Returning to the example above, that would mean that the entire row of data associated with the primary key value of 123 would be stored in that leaf node. Question - in…
Nilish
  • 1,066
  • 3
  • 12
  • 26
8
votes
2 answers

primary key is always indexed in sql server?

You can create a clustered index on a column other than primary key column if a nonclustered primary key constraint was specified. http://msdn.microsoft.com/en-us/library/ms186342.aspx So the above told me: I can create a clustered index on…
ZZZ
  • 3,574
  • 10
  • 34
  • 37
7
votes
3 answers

B-trees, databases, sequential vs. random inserts, and speed. Random is winning

EDIT @Remus corrected my test pattern. You can see the corrected version on his answer below. I took the suggestion of replacing the INT with DECIMAL(29,0) and the results were: Decimal: 2133 GUID: 1836 Random inserts are still winning, even…
IamIC
  • 17,747
  • 20
  • 91
  • 154
7
votes
1 answer

Clustered index in django

I have two tables in my models: 1)Owner: OwnerName 2)Car OwnerKey CarName CarPrice Here while creating a row in Owner table, I also add the Cars for that owner Car table. So all the cars for a particular owner are stored sequentially in the Car…
user5042031
7
votes
3 answers

Clustered index - multi-part vs single-part index and effects of inserts/deletes

This question is about what happens with the reorganizing of data in a clustered index when an insert is done. I assume that it should be more expensive to do inserts on a table which has a clustered index than one that does not because reorganizing…
Anssssss
  • 3,087
  • 31
  • 40
7
votes
4 answers

Is it bad to have a non-clustered index that contains the primary key from the clustered index?

If you have a table with a clustered index on the Primary Key (int), is it redundant and bad to have one (ore more) non-clustered indexes that include that primary key column as one of the columns in the non-clustered index?
Don
  • 1,060
  • 1
  • 21
  • 27
7
votes
4 answers

Primary Key / Clustered key for Junction Tables

Let's say we have a Product table, and Order table and a (junction table) ProductOrder. ProductOrder will have an ProductID and an OrderID. In most of our systems these tables also have an autonumber column called ID. What is the best practice for…
Yvo
  • 18,681
  • 11
  • 71
  • 90
7
votes
1 answer

How to convert clustered primary key to non-clustered without dropping referring foreign keys in SQL Server 2005

I've made mistake creating clustered primary key on GUID column. There are many tables that reference that table with defined foreign keys. Table size is not significant. I would like to convert it from clustered to non-clustered without manually…
alpav
  • 2,972
  • 3
  • 37
  • 47
7
votes
2 answers

Difference between primary key (cluster) and clustered unique index in SQL Server

I understand what is primary key and unique clustered index my question is why primary key is required when we define unique clustered index. Just considering performance good database design. As per my understanding when we define a clustered…
user2903263
  • 71
  • 1
  • 2
6
votes
3 answers

Should Join Tables typically be created as Index Organized Tables (Clustered Indexes)?

Generally speaking ... should join tables (i.e. associative tables) be created as Index Organized Tables (Oracle) , Clustered Indexes (SQL Server) .... or plain old heap tables (with separate indexes on the 2 columns). The way I see if, the…
vicsz
  • 9,552
  • 16
  • 69
  • 101
6
votes
4 answers

Best way to change clustered index (PK) in SQL 2005

I have a table which has a clustered index on two columns - the primary key for the table. It is defined as follows: ALTER TABLE Table ADD CONSTRAINT [PK_Table] PRIMARY KEY CLUSTERED ( [ColA] ASC, [ColB] ASC )WITH (SORT_IN_TEMPDB = OFF,…
Mr. Flibble
  • 26,564
  • 23
  • 69
  • 100
6
votes
1 answer

NewSequentialId on UniqueIdentifier Clustered Index

I am working on database standards for a new database my company is starting. One of the things we are trying to define is Primary Key and Clustered Index rules in relation to UniqueIdentifiers. (NOTE: I do not want a discussion on the pros and…
Vaccano
  • 78,325
  • 149
  • 468
  • 850
6
votes
1 answer

Using GUID as PK in big partitioned MySQL table

We have a huge InnoDB table with hundreds of millions of rows and only 3 columns: GUID, enum, smallint. All lookups are done by GUID. We're considering making GUID the PK and partitioning it BY KEY. We've heard that using GUID as PK is bad because…
Philopator
  • 131
  • 1
  • 8
6
votes
2 answers

MySQL compound index not being used

I have a large table from which I must select large amounts of rows. The table stores call detail records (CDR's). Example: +-------------+--------------+------+-----+---------------------+----------------+ | Field | Type | Null | Key…
Alex Recarey
  • 20,178
  • 4
  • 25
  • 22
1 2 3
…
33 34