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

SQL Server creating table with clustered index without a primary key

Is it possible to create a clustered index from a create table statement in SQL Server 2008 that is not a primary key? The purpose of this is for a table in SQL Azure, so it is not an option for me to first create the table, and then create the…
Chris Marisic
  • 32,487
  • 24
  • 164
  • 258
20
votes
4 answers

Sort order of an SQL Server 2008+ clustered index

Does the sort order of a SQL Server 2008+ clustered index impact the insert performance? The datatype in the specific case is integer and the inserted values are ascending (Identity). Therefore, the sort order of the index would be opposite to the…
HCL
  • 36,053
  • 27
  • 163
  • 213
19
votes
1 answer

Sql Server Indexes Include Primary Key?

One of my co workers is under the impression that when adding an index to a table in SQL Server 2008 that the PK's index is added to that index as well. Therefore if you are using a wider primary key then that key will also be included in the new…
Grummle
  • 1,364
  • 1
  • 12
  • 21
19
votes
6 answers

What does this sentence mean: Clustered indexes are stored physically on the table?

How are clustered indexes stored on a hard disk? What is the logical order? How do non-clustered indexes work?
masoud ramezani
  • 22,228
  • 29
  • 98
  • 151
19
votes
4 answers

Why is there a scan on my clustered index?

SQL 2000 The NED table has a foreign key to the SIGN table NED.RowID to SIGN.RowID The SIGN table has a foreign key to the NED table SIGN.SignID to NED.SignID The RowID and SignID are clustered primary keys that are GUIDs (not my choice) The WHERE…
user142253
  • 494
  • 1
  • 6
  • 16
18
votes
4 answers

Mysql How do you create a clustered index?

I'm reading all about how clustered indexes work, and think they would be beneficial to my app. I understand that primary keys are automatically clustered indexes, but how would you add a clustered index to a non-primary key column? I.e. a…
Wiz
  • 4,595
  • 9
  • 34
  • 51
17
votes
2 answers

How to drop clustered property but retain primary key in a table. SQL Server 2005

i have the following key: ALTER TABLE dbo.Table ADD CONSTRAINT PK_ID PRIMARY KEY CLUSTERED ( ID ASC ) so i have clustered index and primary key on ID column. Now i need to drop clustered index (i want to create new clustered index on another…
skaeff
  • 753
  • 2
  • 13
  • 25
15
votes
3 answers

Update ANSI_NULLS option in an existing table

In our database there is a table which is created with ANSI_NULLS OFF. Now we have created a view using this table. And we want to add a clustered index for this view. While creating the clustered index it is showing an error like can't create an…
Mahesh KP
  • 6,248
  • 13
  • 50
  • 71
14
votes
1 answer

Clustered index on two columns

I've a many-to-many table, let's say: PersonJob(personId,jobId) with clustered index (personId,jobId). The question is: If somewhere in SQL I'll make a query like: SELECT * FROM PersonJob JOIN Job ON PersonJob.jobId = Job.jobId ....... will it…
dragonfly
  • 17,407
  • 30
  • 110
  • 219
14
votes
2 answers

Why can MySQL not use a partial primary key index?

The MySQL documentation describing the use of index extensions, gives the following table as an example, followed by the query below: CREATE TABLE t1 ( i1 INT NOT NULL DEFAULT 0, i2 INT NOT NULL DEFAULT 0, d DATE DEFAULT NULL, …
Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
13
votes
3 answers

Does the SQL Server clustered index replace the RID lookup "index"

When a table has a clustered index in SQL Server does that mean that all indexed queries will go via the clustered index? For example if I have a table with a single non-clustered index (indexing one column) and search for a row via that column it…
Mike Q
  • 22,839
  • 20
  • 87
  • 129
12
votes
3 answers

Make existing bigquery table clustered

I have a quite huge existing partitioned table in bigquery. I want to make the table clustered, at least for the new partition. From the documentation: https://cloud.google.com/bigquery/docs/creating-clustered-tables, it is said that we are able to…
Yosua Michael
  • 165
  • 1
  • 2
  • 7
12
votes
3 answers

Is Unique key Clustered or Non-Clustered Index in SQL Server?

I am new to SQL Server and while learning about clustered index, I got confused! Is unique key clustered or a non-clustered index? Unique key holds only unique values in the column including null, so according to this concept, unique key should be a…
Lijin Durairaj
  • 4,910
  • 15
  • 52
  • 85
11
votes
1 answer

How to create nonclustered index with online if available

I'm adding a new index to a SQL Azure database as recommended by the query insights blade in the Azure portal, which uses the ONLINE=ON flag. The SQL looks something like this: CREATE NONCLUSTERED INDEX [IX_MyIndex] ON …
Mark Heath
  • 48,273
  • 29
  • 137
  • 194
11
votes
6 answers

Should primary keys be always assigned as clustered index

I have a SQLServer table that stores employee details, the column ID is of GUID type while the column EmployeeNumber of INT type. Most of the time I will be dealing with EmployeeNumber while doing joins and select criteria's. My question is, whether…
AbrahamJP
  • 3,425
  • 7
  • 30
  • 39
1
2
3
…
33 34