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
5
votes
1 answer

Where clustered and unclustered index of B+ tree are saved?

Currently I am reading about B+ Tree basics, and got confused regarding space allocation for clustered and unclustered index. When we create clustered index on B+ tree, the index get stored in the main memory and the leaves contain the data…
python
  • 4,403
  • 13
  • 56
  • 103
5
votes
1 answer

Sequential GUID generation in Java with SQL Server uniqueidentifier

The problem that I'm trying to solve is: 1 - In our DB we have all the tables (also tables with millions of records) with a PK id column declared as VARCHAR(36). There is also a clustered index on it This of course as I read online it's an awful…
frankieta
  • 962
  • 1
  • 9
  • 31
5
votes
1 answer

Apache Ignite indexing performance

I have a cache with string as a key and TileKey (class below) as a value, I've noticed that when I execute a query (below) the performance is affected almost linearly by the cache size even though all the fields that are used in the query are…
Black0ut
  • 1,642
  • 14
  • 28
5
votes
2 answers

SQL Server database with clustered GUID PKs - switch clustered index or switch to sequential (comb) GUIDs?

We have a database in which all the PKs are GUIDs, and most of the PKs are also the clustered index for the table. We know that this is bad (due to the random nature of GUIDs). So, it seems there are basically two options here (short of throwing out…
Eyvind
  • 5,221
  • 5
  • 40
  • 59
5
votes
1 answer

Why clustered index is update on update of field which not included in that index (Ms SQL)?

We are using MS SQL Server 2005. Hi, i am performing UPDATE statement on a database table. Lets say this table has next colums: int Id PK int Column1 int Column2 It also has several Index: Unique Clustered (Id) Non-Unique Non-Clustered…
y.selivonchyk
  • 8,987
  • 8
  • 54
  • 77
5
votes
3 answers

how clustered index implemented on view

I can create clustered or non-clustered index on view and SQL SERVER says, we can have multiple non-clustered index (max 249), but only one clustered index exist on table or view. Because, records are sorted, where they physically stored and we…
Ravi
  • 30,829
  • 42
  • 119
  • 173
5
votes
2 answers

How can I observe the performance overhead while inserting into a uniqueidentifier primary key column?

As has been discussed numerous times before and is a known fact - having a clustered index on a uniqueidentifier primary key column, in sql server,will affect performance. What I would like to do is observe this issue by issuing inserts and…
alwayslearning
  • 4,493
  • 6
  • 35
  • 47
5
votes
1 answer

Insert into table with clustered index

If I am not mistaken clustered index sorts the rows of the table physically on disk depending on some custom order. I assume tables are stored in files (maybe one file per table?). Well but then, assume that somebody inserts a "new row" into the…
Cartesius00
  • 23,584
  • 43
  • 124
  • 195
4
votes
5 answers

Performance of non-numeric indexes

If I use a nvarchar(n) column as a clustered index on a SQL Server database, am I going to suffer a significant performance hit compared to a numeric (int) index? Also how does the performance of compound indexes compare?
Colin Desmond
  • 4,824
  • 4
  • 46
  • 67
4
votes
1 answer

Index is not getting applied on Indexed View

I have an indexed view but when I run queries on that view the index which is built on View is not applied and the query runs without index. Below is my dummy script: Tables + View+ Index on View CREATE TABLE P_Test ( [PID] INT…
Rocky Singh
  • 15,128
  • 29
  • 99
  • 146
4
votes
2 answers

When is a clustered index absolutely necessary?

AFAIK SQL table indices are to improve queries speed, so are not absolutely necessary. However SQL Azure for whatever reason demands that each table has a clustered index, so I guess there're cases when an index is necessary. What can be a case when…
sharptooth
  • 167,383
  • 100
  • 513
  • 979
4
votes
2 answers

SQL Azure not recognizing my clustered Index

I get the following error when I try to insert a row into a SQL Azure table. Tables without a clustered index are not supported in this version of SQL Server. Please create a clustered index and try again. My problem is I do have a clustered…
Nate
  • 2,316
  • 4
  • 35
  • 53
4
votes
1 answer

How can I improve the amount of data queried with a partitioned+clustered table?

I have a BigQuery table - day partitioned, and clustered. However, it still uses a lot of data when I run queries over it. How is this possible?
Felipe Hoffa
  • 54,922
  • 16
  • 151
  • 325
4
votes
2 answers

SQL Server - Deadlock on clustered index

i have a table A with a clustered index on the primary key and a non-clustered index on a second column (act_id) (foreign Key to table B) Two update statements are generating following deadlock: Deadlock 1 This deadlock seems not to be an bookmark…
Littlefield
  • 51
  • 6
4
votes
1 answer

BigQuery: "Clustering encountered a key that is longer than"

When clustering my Wikipedia pageviews tables I got the error: Clustering encountered a key that is longer than the maximum allowed limit of 1024 bytes. Context:…
Felipe Hoffa
  • 54,922
  • 16
  • 151
  • 325
1 2 3
…
33 34