Stack Exchange Network

Stack Exchange network consists of 175 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

Questions tagged [clustered-index]

A type of index mostly used in SQL-Server, which aligns the data of a table with the index.

2
votes
3answers
128 views

Why does sql server prefer the nonclustered index over the clustered index?

I am trying to speed up a table and as I was experimenting I ran into this (what I think is) odd occurrence. I created a clustered index and a nonclustered index that should be the same thing. However,...
2
votes
2answers
129 views

Postgres heap vs SQL Server clustered index

I am transitioning from SQL Server to Postgres, and one of the biggest things for me to digest is the non-existence of the "clustered key" that sorts the data in Postgres. Can someone share their ...
0
votes
0answers
64 views

Create a clustered primary key on partitioned table

I have a table that has been partitioned recently based on a datetime column. The same table had a primary key (clustered) on the ID column. During the partitioning of the table, the primary key is ...
2
votes
2answers
76 views

Second index on junction table with composite primary key: include both or one column

I'm working on a database that will be very read-intensive, and uses a junction table that's read often and in both directions. The junction table contains two columns, IDA and IDB The primary key ...
1
vote
2answers
422 views

Clustered Sequential GUID Primary Key vs Non-Clustered GUID and Clustered Sequential ID Primary Keys

I'm trying to figure out what the optimal solution is in regards to size and performance when using GUIDs as a primary key. The main reason for wanting GUIDs is that it's more appropriate in our micro-...
0
votes
1answer
28 views

Primary index is being used, not the one which is on the field present in the where condition

I have 2 tables, one is category table and has below coulmns and has a primary index on cat_id column. cat_id (primary key), acc_id, islive Another table is cat_brand table which has below fields ...
1
vote
0answers
51 views

SQL Server Execution Plan

In the following queries the Student table has Clustered Index on StudentID column, and the Enrollment Table also has a Clustered Index on EnrollmentID as well as Non-Clustered index on (SessionID, ...
1
vote
2answers
132 views

Will Re-Seeding an Identity column back to 0 cause page splits?

Simple table with an identity table as a clustered primary key. We unexpectedly hit the max value for the column, so I requested our DBA re-seed the table back to 0 to give us some time to examine ...
2
votes
0answers
35 views

To cluster or not to cluster? That is the question!

I have a table with just one (nvarchar) column for tracking IDs that have been used, to ensure they aren’t duplicated. Should I use Clustered or Non-clustered Index for this? The only two operations ...
0
votes
0answers
17 views

Does rebuilding and reorganizing indexes yield less fruitful results on tables that don't have clustered indexes?

I understand the operations of rebuilding and reorganizing indexes at a high level, but was wondering what they are actually doing on a table that doesn't have a clustered index (i.e. a table stored ...
2
votes
2answers
977 views

Performance differences between RID Lookup vs Key Lookup?

Are there any performance differences between when a non-clustered index uses the clustered index's key to locate the row vs when that table doesn't have a clustered index and the non-clustered index ...
1
vote
1answer
427 views

Are there any performance differences with a GUID vs INT clustered indexed column, especially with joins on those columns?

Basically what the title says: What are the differences in performance (if any) between using a clustered index on a GUID column vs a clustered index on an INT column, especially when those columns ...
3
votes
2answers
669 views

Clustered vs Non Clustered Indexes for a Primary Key

I have a large table with around 19 million records in it. The primary key is a uniqueidentifier column called ID and there is a nonclustered index on this column. An application generates these ...
3
votes
2answers
375 views

Can one make MySQL cluster on a unique index rather than a primary key?

The MySQL docs states that: When you define a PRIMARY KEY on your table, InnoDB uses it as the clustered index. But that's not the only possibility, you can cluster off of a unique index instead: ...
3
votes
3answers
82 views

Is there any way to test which clustered index is better for specific queries?

We've been querying and processing a growing table of events in our system, which was fine without specific indexes for now. However we've noticed a decrease in performance and are wondering what we ...
1
vote
1answer
1k views

Non-Clustered Index takes much longer to create than clustered Index

I have a procedure that creates a clustered and a non-clustered index on a table in order to do a partition swap. The problem I have is the clustered index takes about 2 minutes to create: CREATE ...
0
votes
2answers
32 views

Cannot Check Cluster Index Sql Server

I am just check if my table has Cluster Index or not by running this query Table Design CREATE TABLE [dbo].[postTB]( [postID] [bigint] IDENTITY(1,1) NOT NULL, [slug] [nvarchar](300) NULL, [title] [...
0
votes
1answer
179 views

Am I compromising effiency by using CTE instead of indexed tables? [closed]

Consider scenario where I am creating multiple intermediate tables in CTEs before joining the last intermediate result to some other table. The intermediate results can use joines as well. The ...
3
votes
3answers
365 views

Does Index Rebuild Reduces physical read from disk

Recently I have rebuild indexes uisng Ola Hallengreen script which were 99% fragmented. After rebuilt i have noticed the physical reads have reduced a lot. Does this has anything to do with Index ...
-1
votes
2answers
63 views

Index Seeks cost of OR operator

I have a query which is slow and it takes 15 seconds to execute. The query fetches data from a view and in the where clause, I have few conditions which includes OR and AND. exec sp_executesql N'...
0
votes
1answer
109 views

Does cost of an Operation matter.?

I have a query which is slow when i run from SSMS and from application.I am trying to tune it.I took the execution plan and on checking I can see cost of Clustured Index Seeks on a table is 85%.That ...
3
votes
1answer
117 views

Clustered Index Maintenance vs. Nonclustered Index Maintenance

There is a plethora of information available detailing why an IDENTITY field should be use as both the primary key and clustered index of a table for most situations; yet I'm having difficulty ...
0
votes
1answer
32 views

When all you indexes have included all the other columns, do you still have the penalty for a wide clustered index?

So I hear sometimes that I should not use a clustered index on a wide column, mainly because it is being referenced in all other indexes. When all my other indexes on this table include all other ...
9
votes
3answers
1k views

Does a non-clustered index make any guarantees about row order?

I have a developer that would like, when doing a select statement with no order by, the rows in a table to be in the order they were inserted. The developer suggested changing from a clustered to non-...
6
votes
2answers
108 views

When Azure recommends an index (part of natural key) with all other columns included, should I switch to a clustered index for this natural key?

The table does have an identity key (current CI), but it’s barely used to query. Because the natural key is not ever-increasing I’m afraid of insert performance, fragmentation or other problems that I ...
1
vote
0answers
50 views

What queries read my indexes and what queries apply inserts / updates to my indexes?

I'm looking indexes with 0 reads with SP_blitzindex, from there I am able to grab the indexes names that are probably not helping my system. Using the top query posted by Kendra: SELECT ...
1
vote
0answers
280 views

C# Windows Service task fails due to a SP timeout, but works fine after executing the SP in SSMS

I have a Windows Service app running and sometimes a task which has a method call to a stored procedure (with a Select operation) is keep failing due to a timeout. But when I execute that SP with ...
2
votes
0answers
55 views

How is page data rearranged after clustered index is created?

Can anyone please explain what happens at the page level when clustered indexes are created? How do pages rearrange data once clustered indexes are created?
0
votes
1answer
62 views

why is the Log File is 5 times the database overnight when deleting?

The log file alone is close to 500 GB while the database is 80 GB. This is not a production system so I tried to delete a table which had 780,000 records. this table has 5 individual indexes that ...
0
votes
1answer
33 views

InnoDB: Is a covering index in the form of (PK, col1, col2, col3) redundant?

Table structure for config: property (primary key) value description Query: SELECT property, value FROM config If I put a covering index on (property, value) the optimizer still chooses the ...
1
vote
1answer
209 views

PostgreSQL covering index on large table as alternative to CLUSTER command

I'm looking for an alternative to executing the CLUSTER command on large tables to keep data efficiently organized on disk, since it requires an ACCESS EXCLUSIVE lock on tables being clustered, making ...
1
vote
0answers
55 views

Any performance gains from having identical clustered and nonclustered indexes on table on different filegroups?

I am attempting to rearchitect our MSSQL database such that we have two filegroups, one for data, on a 15K HDD, primary, and one for indexes on a separate SSD, indexes, in order to realize some ...
4
votes
1answer
52 views

Add Primary Key column at the end of an Index

If I have a table with the following primary key: PRIMARY KEY (id) And I want to query documents written by someone (e.g. display a preview of the latest 20), sorted by their "id", so I created an ...
0
votes
1answer
212 views

Mariadb clustering Index design question

Here are the columns of concern for a table describing tasks: Org_ID Task_ID Task_property_columns The only requirement for Task_ID is that it is unique per every Org_ID, however I would prefer ...
1
vote
1answer
262 views

PostgreSQL: Running SELECT DISTINCT ON ~700m rows - how to optimize?

I have a table that, if we look at just the relevant parts, has two columns: id and raw_data. id is an integer, and raw_data is a text blob. At this point, the table has no constraints or indexes ...
1
vote
1answer
55 views

Clustered Index - Trade-off Queries VS Inserts? [closed]

I'm working on improving database performance of my application and now it comes to the part of clustered index. I don't have experience with it so I would like to ask. My application caveats In my ...
3
votes
1answer
113 views

Can I change which index a primary key uses?

I have a table with a non-clustered index on the id, and a clustered index on some other fields. The fields of the clustered index are non-sequential, and are frequently modified. I'd like to change ...
2
votes
1answer
106 views

How does clustering on a field other than autoincrement id affect inserts?

I have a table with an id column which is auto-incremented, and various other informational columns. Rows are inserted into this table very frequently. When the data is read back, the majority of ...
4
votes
1answer
434 views

Does rebuilding a clustered index on an IDENTITY column do anything useful?

We have a COTS product that has created a very large transaction log table. It has a single clustered index which supports an IDENTITY column declared as a key. Last night the index got rebuilt and ...
7
votes
6answers
6k views

Rebuild Very Large Primary Key Index

I have a SQL database that is hosted on Azure. The problem is that the size is getting out of control, I can see up to 99% fragmentation in the Primary Key clustered indexes. I'm able to rebuild all ...
11
votes
2answers
288 views

Clustered Index Selection - PK or FK?

I have a SQL Server 2014 table that looks like the following: OrderId int not null IDENTITY --this is the primary key column OrderDate datetime2 not null CustomerId int ...
1
vote
1answer
76 views

How to swap postgresql rows physical locations?

I'm looking for a way to reorganize my database tables rows incrementally based on their clustered index. I would like to avoid using the CLUSTER command since it requires an ACCESS EXCLUSIVE lock on ...
8
votes
3answers
853 views

How to create clustered index on 100 GB table

I have a heap table that takes about 104 GB of disk space with almost 3 billion rows. I am trying to create a clustered index on this table on the [WeekEndingDate] column. I have about 200 gb's free ...
1
vote
2answers
160 views

Proper table design for sparse primary key

In my system I have temporary entities that are created based on rules stored in my database, and the entities are not persisted. Now, I need is to store information about these entities, and because ...
-3
votes
1answer
277 views

Fix SQL Table with *BOTH* Heap AND Clustered Index

Somehow, we have a SQL Server Table with both a HEAP and a CLUSTERED index on a table. Is there a way to fix this? e.g. delete the HEAP index by its object ID? If we drop the Clustered Index, it will ...
5
votes
1answer
2k views

Insert much slower when target table has clustered index

I've just had to debug an issue which has me thoroughly confused. An ETL Transform process on our Development Data warehouse has just failed after working successfully every day for months. The same ...
5
votes
3answers
2k views

SQL Server: Clustered index, sorting and pagination

In my application, several times I have to show results that are paginated and sorted by some field. For example a simple user list sorted by last name. Because of that and because I also have ...
0
votes
2answers
98 views

SQL server 2012 reports “Missing Index” on column which is covered by “Clustered Index”

My table creation script is as follows SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[quotation_section]( [quotation_id] [varchar](24) NOT NULL, ...
5
votes
1answer
61 views

Drawbacks to add clustered keys

I have a database that has multiple tables without any clustered index. All that I have found are small (< 500 rows). Specifically, PRIMARY KEY NONCLUSTERED with no other indexes at all. At a very ...
3
votes
1answer
52 views

Adding an identity field to be the new cluster of a large table - how do I get the “right” order?

I have a very large table (88m rows, nearly 300GB in size) that has a poorly planned clustered index. I want to add a BIGINT identity field, and make it the new clustered index (having the desirable ...