Questions tagged [clustered-index]

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

Filter by
Sorted by
Tagged with
0 votes
1 answer
50 views

Understanding of the %%lockres%% and %%physloc%% pseudo columns with index hint

I am experimenting with clustered and non-clustered indexes in SQL Server and I have noticed something interesting. Here is a simplified version of my T-SQL code: IF OBJECT_ID('dbo.dept') IS NOT NULL ...
Fajela Tajkiya's user avatar
0 votes
2 answers
64 views

Partitioning a Table Vs Partitioning a clustered index

If I have a Table in SQL server, with Time series data for every business date (~100k rows a day) and only the last 45 days are frequently updated/deleted/inserted, it would be better to partition ...
Vid's user avatar
  • 1
0 votes
1 answer
61 views

Optimizing PostgreSQL Query with DISTINCT ON, Filtering, and Ordering

I'm working on optimizing a query for a PostgreSQL database used in an educational platform. The query retrieves distinct question_id values along with their is_seen status from the ...
sujeet's user avatar
  • 213
2 votes
1 answer
68 views

Pros/cons of two non-clustered indexes on Col1 and Col1+2

In relation to a similar question, please consider the following: Microsoft SQL Server Table T has at least 4 columns A, B, C and D PK is on A (i.e. non-null, unique, and the clustered index) Non-...
Paulie D's user avatar
1 vote
2 answers
230 views

How to choose a clustered index for a fact table

What is a good strategy for choosing a clustered index on a fact or transactional table? I am using SQL Server 2019. I have picked a generic sales table (FactSales) that has these properties: no ...
Craig's user avatar
  • 503
0 votes
1 answer
55 views

How can I make (game_id, user_id) unique, yet (game_id, score) indexed/clustered, in ScyllaDB?

See this in ScyllaDB: CREATE TABLE scores_leaderboards ( game_id int, score int, user_id bigint, PRIMARY KEY (game_id, score, user_id) ) WITH CLUSTERING ORDER BY (score DESC); The ...
Nuno's user avatar
  • 809
0 votes
1 answer
75 views

Clustered index on primary key vs on non-unique column

As a starting point, is it better to use the surrogate identity column (SaleItemID) as the clustered index. Or look for a column that I expect will be used often when reading data from the table but ...
Craig's user avatar
  • 503
1 vote
2 answers
56 views

Does clustered index fragmentation happen in relational DBMS?

Is it possible that after a certain pattern of random inserts and deletes leaf data nodes become fragmented with clustered index? I.e, that the physical order does not reflect the logical order (by ...
Borisav Živanović's user avatar
-1 votes
1 answer
148 views

Best indexing strategy for composite primary key with frequently queried columns and occasional additional column [closed]

I have a large table with the following composite primary key: PRIMARY KEY(col1, col2, col3) col1, col2, col3 are queried all the time. Some times (maybe 10% of the time) I get queries for col1, col2,...
Kevin's user avatar
  • 99
0 votes
1 answer
275 views

Adding a non-clustered, covering index, on a primary key, when clustered index exists

I went through the related articles, suggested by SO, but didn't really find all the answers, so I'll try to be specific here. Presume we have a (MSSQL) table, with a simple ID (int) primary key, with ...
userfuser's user avatar
  • 149
0 votes
0 answers
256 views

How to avoid cluster index scan?

I've a table with a few millions of records. Here's the table structure: Column_name Type Computed Length Prec Scale Nullable TrimTrailingBlanks FixedLenNullInSource Collation Id int no 4 10 0 no (n/...
Thomas Ayoub's user avatar
0 votes
0 answers
56 views

Index reorg question

Database table is defined using FillFactor=0 and that was not changed since database deployment several years ago. FillFactor=0 is also a server setting and maintenance job is running every night. ...
Mainecoon21's user avatar
0 votes
1 answer
76 views

Can a Clustered Index based on a key field be more performant than a non-clustered covering index for a big table

I am researching a DW reporting table that will grow very large. For simplicity, I will show the table as follows: BigTable -------- TableID INT IDENTITY NOT NULL, CompanyName NVARCHAR(100) NOT NULL ...
Ross Bush's user avatar
  • 505
2 votes
0 answers
70 views

Clustered Index being used over non-clustered with same key

I have a table that has a clustered index (CI) on the field filename. It also has a non-clustered index (NCI) - With the key column of filename and 5 other columns in the includes. (There are ...
Tom's user avatar
  • 29
4 votes
1 answer
50 views

Creating a new view or table from the last record

I have 2 tables DraftProducts and Products. The only difference between the 2 tables is the "phase" field. I have productCode varchar(20), phase(tinyint), Name, Price, SpecialCells.... etc ...
omerix's user avatar
  • 91
5 votes
1 answer
657 views

Index Design: clustered index using a DATE

Every once in a while, one needs a check. This is such a time. I have tables that are used for reporting. They are deleted/inserted once a day or multiple times a day to "refresh the data" ...
tbasallo's user avatar
  • 177
0 votes
1 answer
97 views

Working with varchar field in Clustered Index. (Performance Test)

I am using Microsoft SQL Server 2016. For my products table; I need to define a ClusteredIndex with 2 fields of Product Type and Product Code. ProductType(tinyint), ProductCode(varchar(32)). I know ...
omerix's user avatar
  • 91
3 votes
2 answers
570 views

Is a clustered index of 3 columns too big?

My goal is to design a table, that can be queried trough an external id(uniqueidentifier), an internal id(bigint), always in combination with companyId(bigint), userId(bigint) and dashboardId(bigint) ...
fmi21's user avatar
  • 133
0 votes
1 answer
71 views

Clustered Index Maintenance

I am running SQL Server 2012 and I am wondering if I create a clustered index if, as time goes on and users add records, if the RDBMS will maintain the clustered index on table change or have to do ...
Daniel L. VanDenBosch's user avatar
2 votes
2 answers
1k views

Non-clustered Primary Key and Clustered Index

It is my understanding that in SQL Server, you can have a Primary Key that is non-clustered, and have another index that is the clustered one. To me, this seems the same as just having a Primary Key, ...
Nuno's user avatar
  • 809
0 votes
1 answer
398 views

Index Optimization for tables that are truncated and charged everyday

I'm a recently recruited Data Engineer, I have made a bunch of ETL pipelines planned to run daily, the destination tables are truncated and charged again. The DBMS is on-prem SQL Server. When I ...
Hamza Hamdani's user avatar
0 votes
2 answers
209 views

InnoDB Clustered Index is stored under memory?

firstly i'm newbie about internals databases, sorry if my question is dumb. I have read in Stackoverflow(post link at final) that InnoDB clustered index is stored in disk and in memory. And i have ...
Vitor C's user avatar
2 votes
2 answers
597 views

MySQL Innodb, why does the size of primary index depend upon the order of insertion of records

I have a table with this schema CREATE TABLE `UserIdPhoneNo` ( `userId` int NOT NULL, `phoneNo` bigint NOT NULL, PRIMARY KEY (`userId`,`phoneNo`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 I am ...
Aryaveer's user avatar
  • 123
0 votes
0 answers
62 views

Adding new column to clustered column store indexed table

I designing clustered column store indexed fact tables. Later on many new columns might be added to these currently ~90 columns tables. How adding new nullable column is affected by the size (number ...
Avi's user avatar
  • 265
0 votes
1 answer
160 views

Ensure that SQL server "avoids" the clustered index when a non-clustered index can be used

I have a query, the short of which looks like the following. While this is not exact, it should show how the problematic behavior is introduced and what I am looking to prevent. And "prevent"...
user2864740's user avatar
0 votes
2 answers
294 views

Why Is There a Sort on this Column

This query has a plan which includes a SORT and the sort is taking up the majority (77%) of the cost: SELECT LFTYLM_STATUS FROM LFTYLM WHERE LFTYLM_LIS_ID = 138998 AND LFTYLM_LFTY_ID = 78 AND ((...
Nick's user avatar
  • 173
0 votes
2 answers
221 views

Order of Key Columns in Index

The execution plan for this query appears to be making a very bad choice (of course I know it is not): SELECT TOP 1 E_PER_ID FROM E WHERE E_CUS_ID = 1912 AND E_TYPE = 'R' ORDER BY E_ID DESC The two ...
Nick's user avatar
  • 173
0 votes
0 answers
128 views

Sudden performance degredation fixed with non-unique index property

We are using NServiceBus message queuing service backed by a SQL Server (2016 SP2-CU15) Database. Yesterday, after functioning without an issue for years, we started to notice server performance ...
sTTu's user avatar
  • 147
-1 votes
3 answers
766 views

Why MySQL clustered index page size is 16k by default?

If the whole idea of the B+-tree structure, that the MySQL clustered index uses, is to be efficient in accessing disk data. Then it's logical to make index page size equal to the disk block size which ...
user avatar
1 vote
1 answer
313 views

How does page split result in index fragmentation?

I am trying to understand the reason behind index fragmentation. Assuming fill factor is 100% and the clustered index is on an integer column. The values on each page are as follows and assuming that ...
variable's user avatar
  • 3,430
0 votes
1 answer
345 views

Clustered Column Store Index - does it have max row size limitation?

Updatable Clustered Column Store Index (SQL Server 2016+) - does it have max row size (8060) limitation, or you can put any row size you want in there ? In other words, is it possible to get below ...
Aleksey Vitsko's user avatar
-1 votes
1 answer
612 views

Clustered Index and Sorting [duplicate]

select * from [table_a]; A clustered index sorts the table based on whatever column(s) you choose. That being said, if I have a clustered index on [column_a], [column_b], and [column_c] and run the ...
Chicken Sandwich No Pickles's user avatar
0 votes
1 answer
90 views

Is this horrendously low row estimate causing a slow insert?

reference: https://www.brentozar.com/pastetheplan/?id=Bklc3fGNt I have a clustered index on both #LargerRange and #SmallerRange. These indexes contain 5 key columns: Name, InOrOut, LocCode, VisitID, ...
scarr030's user avatar
  • 115
1 vote
0 answers
534 views

DACPAC: Altering primary key results in rebuilding tables during deployment

Question: Is there any better way to avoid rebuilding of table when clustered Index column is changed Details: We have Database project with all default settings. There are tables with primary key ...
sanjay's user avatar
  • 11
2 votes
0 answers
624 views

How to estimate the nonclustered index size before its creation?

I created two scenarios and expected to see the same result but the results were noticeably different. I would like to know where I did wrong. Scenario 1 Create table mytable1 with clustered index and ...
Rauf Asadov's user avatar
  • 1,253
17 votes
4 answers
6k views

Is it a good idea to Partition a table (in MS SQL) based on date, when there's a clustered index present on ID (INT)

I have a table in MS SQL Server . Table Size: 806 GB Rows : 1.2 billion Index Space : 1.2 GB Table Usage: Logging from the Web Service calls 99.9% is usage is from the logging, developers look ...
User M's user avatar
  • 273
2 votes
2 answers
247 views

Is the order of columns in a clustered index important?

Does it matters which field I specify first in the index? would CREATE SET TABLE my_table ( validity_date DATE , branch_id SMALLINT , account_class_id BYTEINT , ...
Eliy Arlev's user avatar
0 votes
1 answer
909 views

High CPU usage in AWS RDS

When I visit the woocommerce orders page in WordPress, MySQL RDS CPU usage goes to 100% but the website is working perfectly fine. In 'active sessions' section, "wait/io/tables/sql/handler" ...
Anish Sapkota's user avatar
0 votes
2 answers
54 views

Index Drop for Partial Overlap?

I'm working with a 50mil row table with the following: Clustered Index keyed on [Col1] Nonclustered Index keyed on [Col1], [Col2] (no includes) It seems like the right call here would be to drop #2 ...
AccidentalDBA_CO's user avatar
5 votes
2 answers
174 views

Unexpected update results on heap using SET @Variable = Field= @Variable + 1, fixed with clustered index

I'm just looking to understand why this is happening, and my Google searches were failing me. We are on SQL Server 2016 SP1. This is the situation: Vendor table that manages IDs by keeping track of ...
IronicMuffin's user avatar
2 votes
2 answers
398 views

How is the clustered index different from the table itself?

If the SQL Server clustered index is the physical order of the table and all columns are included, is it the table itself? How is the clustered index stored physically? I have seen the Q & A What ...
Metaphor's user avatar
  • 890
-2 votes
1 answer
102 views

Will index on a partition that doesn't include partition column work fast?

Let's say I have a huge table with columns [CloseOfBusinessDate], [DataSourceID], etc. [CloseOfBusinessDate] is a partition column and I have a clustered index on that column (on ...
nightcoder's user avatar
1 vote
1 answer
581 views

Should I rebuild index in non clustered index?

I'll be rebuilding our index in SQL Server since our fragmentation rate on all indexes for all tables are so high. Should I include to rebuild my non-clustered index also? Or rebuilding of clustered ...
Dwightqwerty's user avatar
0 votes
1 answer
446 views

Does using PK and FK effect performance in negative way? [closed]

Few companies I noticed do not use PKs and FKs. I don't understand the logic of not using Clustered Indexes and I hear some say it can cause issues because FK couples records together and takes up ...
Habib's user avatar
  • 11
1 vote
2 answers
173 views

Online rebuild consuming lot of log space

why the online rebuild on a primary key clustered index in taking almost equal to the space of non clustered index size which is untouched. Details: We have a table with primary key clustered index (...
Siva Dasari's user avatar
5 votes
2 answers
1k views

What happens when you change a heap into a clustered table in SQL Server?

As I understand, a heap is an unordered object. When you insert a record, SQL Server use IAM page to get the pages that belong to this heap, and use PFS page to find a particular one which has enough ...
Just a learner's user avatar
0 votes
1 answer
134 views

What should be the size of nonclustered index?

I was experimenting with nonclustered indexes, especially their sizes. My aim was to understand how the amount of index keys in Clustered index affects the final size of Nonclustered index. I ...
Rauf Asadov's user avatar
  • 1,253
0 votes
1 answer
235 views

One-directional Transactional Replication is causing one table's Clustered Index to be copied over as a Nonclustered Index in the subscriber database?

This seems really odd to me, as I've used SQL Server's replication feature for a while now and have never seen this occur before. I'm using One-Directional Transactional Replication and I have ServerA ...
J.D.'s user avatar
  • 35.1k
0 votes
2 answers
52 views

Table design for efficiently retrieving "most recent row" per FK'd item whose PK is a GUID

Database is SQLAzure I've a recent requirement to store incremental updates in status against an Order item, whose primary key is a GUID (stored as an nvarchar(36) :/ ). An OrderStatusLog entry might ...
Caius Jard's user avatar
0 votes
1 answer
76 views

Does the primary key of a table help decide what block a row is written to in Oracle?

I'm trying to refactor a slow Oracle database, where the developers naively chose a VARCHAR2 UUD4 as the primary key for what is really an append only time series dataset. I've found that the ...
Rol's user avatar
  • 111

1
2 3 4 5
7