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
11
votes
3 answers

How to choose the clustered index in SQL Server?

Usually the clustered index is created in SQL Server Management Studio by setting the primary key, however my recent question about PK <-> clustered index (Meaning of Primary Key to Microsoft SQL Server 2008) has shown that it is not necessary to…
usr
  • 168,620
  • 35
  • 240
  • 369
11
votes
2 answers

Does SQL Server creates Non clustered index by default

Ya, it is a duplicate of this. But I just needs a clarification on this article by Pinal Dave, which says the following: Scenario 4: Primary Key defaults to Clustered Index with other index defaults to Non-clustered index In this case we will…
Sharun
  • 3,022
  • 6
  • 30
  • 59
11
votes
1 answer

How to create a clustered index when using code-first migration with Entity Framework and SQL Server

I have a MessageModel with a timestamp attribute of when the message was created. I would like to make a clustered index on this long attribute. I hope to achieve a query speed-up when doing a query to get all messages newer than a certain…
Sindre
  • 3,880
  • 2
  • 26
  • 39
10
votes
3 answers

DELETE performance in SQL Server on clustered index, large table

I have a table with more than 20 million rows, and when i do: DELETE [Table] WHERE ID = ? It takes over 40 seconds. The ID column is clustered. Is this what you could expect? or is it possible to optimize this?
Erik Sundström
  • 997
  • 1
  • 12
  • 29
10
votes
6 answers

Improving performance of cluster index GUID primary key

I've a table with large number of rows (10K+) and it primary key is GUID. The primary key is clustered. The query performance is quite low on this table. Please provide suggestions to make it efficient.
TheVillageIdiot
  • 40,053
  • 20
  • 133
  • 188
10
votes
1 answer

SQL Server : ADO.Net GetUpdateCommand ok with PK, fails after adding another clustered unique index

I have such a table with a non-clustered primary key: CREATE TABLE [dbo].[StudentGrade]( [EnrollmentID] [int] IDENTITY(1,1) NOT NULL, [CourseID] [nvarchar](10) NOT NULL, [StudentID] [int] NOT NULL, [Grade] [decimal](3, 2) NOT…
Alain Tésio
  • 489
  • 4
  • 8
10
votes
3 answers

Clustered vs NonClustered Primary Key

begin transaction; create table person_id(person_id integer primary key); insert into person_id values(1); ... snip ... insert into person_id values(50000); commit; This code takes about 0.9 seconds on my machine and creates a db file taking up…
Elite Mx
  • 141
  • 1
  • 1
  • 4
9
votes
1 answer

Clustered and nonclustered indexes performance

I have a huge table (~ 10 million rows) with clustered PK on a random uniqueidentifier column. The most operations I do with this table is inserting a new row if there is not yet a row with the same pk. (To improve performance of it I use…
irriss
  • 742
  • 2
  • 11
  • 22
9
votes
2 answers

How to Query to Find out if a Table has a CLUSTERED Primary Key

I found this question, but it doesn't appear to answer the question... SQL Server - How to find if clustered index exists How can I write an IF T-SQL statement to say: IF NOT ([TableName] has a CLUSTERED PK) ALTER TABLE to add the CLUSTERED PK
Will Strohl
  • 1,646
  • 2
  • 15
  • 32
9
votes
3 answers

SQL Server query to find clustered indexes

Is it possible to write a query that returns all tables that have clustered indexes that are not based on an identity key?
DevilDog
  • 413
  • 2
  • 7
  • 16
9
votes
2 answers

What should I do to get an Clustered Index Seek instead of Clustered Index Scan?

I've got a Stored Procedure in SQL Server 2005 and when I run it and I look at its Execution Plan I notice it's doing a Clustered Index Scan, and this is costing it the 84%. I've read that I've got to modify some things to get a Clustered Index Seek…
Brian Roisentul
  • 4,590
  • 8
  • 53
  • 81
8
votes
3 answers

Clustered index on temp table

I'm trying to optimize a procedure that has code like the following: CREATE TABLE #t1 (c1 int, c2 varchar(20), c3(varchar(50)...) CREATE CLUSTERED INDEX ix_t1 ON #t1(c3) ON [PRIMARY] I wanted to improve that by moving the CLUSTERED index into the…
David Mathis
  • 247
  • 1
  • 2
  • 8
8
votes
1 answer

How does PostgreSQL's CLUSTER differ from a clustered index in SQL Server?

Many posts like this stackoverflow link claim that there is no concept of a clustered index in PostgreSQL. However, the PostgreSQL documentation contains something similar. A few people claim it is similar to a clustered index in SQL Server. Do you…
Mahesh V S
  • 552
  • 1
  • 8
  • 23
8
votes
2 answers

Why isn't index likely to do much if it's DATETIME or DATETIME2 because they include the time portion?

Comments to question "How to decrease response time of a simple select query?" tell: "What is the data type on LaunchDate? An index isn't likely to do much if it's DATETIME or DATETIME2 because they include the time portion – OMG Ponies" "@OMG -…
Gennady Vanin Геннадий Ванин
  • 10,136
  • 11
  • 75
  • 106
8
votes
5 answers

Reasons not to have a clustered index in SQL Server 2005

I've inherited some database creation scripts for a SQL SERVER 2005 database. One thing I've noticed is that all primary keys are created as NON CLUSTERED indexes as opposed to clustered. I know that you can only have one clustered index per table…
AJM
  • 32,054
  • 48
  • 155
  • 243
1 2
3
…
33 34