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

Aerospike: How Primary & Secondary Index works internally

We are using Aerospike DB and was going through the documentation. I could not find good explanation of algorithm explaining how Primary & Secondary index works. The documentation says it uses some sort of distributed hash + B Tree. Could someone…
Tarun
  • 3,162
  • 3
  • 29
  • 45
4
votes
3 answers

SQL Server: ~2000 Heap Tables all using GUID Uniqueidentifier - Possible Clustered Indexing?

I have just taken over a database which has around 2200 tables. Over 2000 of these have no clustered index (some have no indexes at all). All of the tables have been configured to use a GUID as the uniqueidentifier. Just looking at the query plans,…
Stevie Gray
  • 75
  • 7
4
votes
1 answer

Clustered Index on Foreign Key VS Primary Key

Table Structure (SalesPersonProduct) SalesPersonProductId INT (PK, Clustered) SalesPersonId INT (FK - non-unique - Nullable) ProductId INT (FK - non-unique - Nullable) AreaId INT (Not Null) DistributionType …
AverageProgrammer
  • 41
  • 2
4
votes
1 answer

Why Len Function Can Use Index?

I have a table with 145000 rows. And have not any index on it. When I run below sql. I found a table scan on execute plan as expected. It generate six rows and 3481 logic read. SET STATISTICS IO ON SELECT columnA FROM table WHERE…
jojo
  • 285
  • 4
  • 14
4
votes
4 answers

SQL Server Indexed Views: Cannot create clustered index because the select list contains an expression on result of aggregate function

I am trying to create a simple indexed view on the query below. But when I try to create a unique clustered index on it, I get the following error: Cannot create the clustered index '..' on view '..' because the select list of the view contains…
user2673722
  • 295
  • 2
  • 6
  • 15
4
votes
1 answer

GUID primary key, separate clustered index column

I have a database application in production, and all the tables use GUID primary keys which are currently set as the clustered indexes. I understand that this is a poor design due to performance considerations. I have been reading much on the…
rory.ap
  • 34,009
  • 10
  • 83
  • 174
4
votes
4 answers

SQL Server: Clustering by timestamp; pros/cons

I have a table in SQL Server, where i want inserts to be added to the end of the table (as opposed to a clustering key that would cause them to be inserted in the middle). This means I want the table clustered by some column that will constantly…
Ian Boyd
  • 246,734
  • 253
  • 869
  • 1,219
4
votes
2 answers

SQL Server: Create Nonclustered Index without giving a name to it

I use SQL Server 2008. I am trying to create a nonclustered index on my table. I want to check if there exists a way to create this without giving a name to the index. For e.g. CREATE TABLE #mytable (Date_ datetime NOT NULL, ID_ varchar(10) NOT…
Maddy
  • 2,520
  • 14
  • 44
  • 64
4
votes
2 answers

Optimizing queries based on clustered and non-clustered indexes in SQL?

I have been reading lately about how clustered index and non-clustered index works. My understanding in simple terms (correct me if wrong): The data structure that backs clustered and non-clustered index is B-Tree Clustered Index: physically sorts…
brain storm
  • 30,124
  • 69
  • 225
  • 393
4
votes
7 answers

Should searchable date fields in a database table always be indexed?

If I have a field in a table of some date type and I know that I will always be searching it using comparisons like between, > or < and never = could there be a good reason not to add an index for it?
Spencer Ruport
  • 34,865
  • 12
  • 85
  • 147
4
votes
1 answer

How to define if table is a good candidate for a clustered columnstore index?

I have read (here,here and here) about clustered columnstore indexes introduced in SQL Server 2014. Basically, now: Column store indexes can be updatable Table schema can be modified (without drop column store indexes) Structure of the base table…
gotqn
  • 42,737
  • 46
  • 157
  • 243
4
votes
2 answers

Delete big amount of data and primary index

I'm trying to delete big amount of rows (>10 million, it about 1/3 of all records in a table) from InnoDB MySQL table with primary/clustered index. Field id is primary/clustered index and it's continuous without gaps. At least it should be, I do not…
andrey123
  • 41
  • 2
4
votes
2 answers

Composite clustered index vs non-unique clustered index. Which is better/worse in this case?

I have a database where all tables include a Site column (char(4)) and a PrimaryId column (int). Currently the clustered index on all tables is the combination of these two columns. Many customers only have one site so in those cases I think it…
BVernon
  • 3,205
  • 5
  • 28
  • 64
4
votes
1 answer

Why SELECT COUNT(*) performs clustered index scan?

I have the following table: CREATE TABLE [dbo].[Addr]( [Address] [char](34) NOT NULL, CONSTRAINT [PK_Addr] PRIMARY KEY CLUSTERED ( [Address] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS …
Zergatul
  • 1,957
  • 1
  • 18
  • 28
4
votes
1 answer

Postgres ignoring clustered index on date query

I have a large table that I run queries like select date_att > date '2001-01-01' on regularly. I'm trying to increase the speed of these queries by clustering the table on date_att, but when I run those queries through explain analyze it still…
abu
  • 948
  • 1
  • 7
  • 14
1 2 3
…
33 34