Questions tagged [clustered-index]
A type of index mostly used in SQL-Server, which aligns the data of a table with the index.
323
questions
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 ...
- 1,159
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 ...
- 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 ...
- 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-...
- 21
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 ...
- 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 ...
- 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 ...
- 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 ...
-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,...
- 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 ...
- 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/...
- 61
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. ...
- 171
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
...
- 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 ...
- 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 ...
- 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" ...
- 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 ...
- 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) ...
- 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 ...
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, ...
- 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 ...
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 ...
- 1
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 ...
- 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 ...
- 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"...
- 195
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
((...
- 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 ...
- 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 ...
- 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 ...
mind_religion
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 ...
- 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 ...
- 5,472
-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 ...
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, ...
- 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 ...
- 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 ...
- 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 ...
- 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
, ...
- 125
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" ...
- 101
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 ...
- 147
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 ...
- 663
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 ...
- 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 ...
- 97
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 ...
- 21
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 ...
- 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 (...
- 532
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 ...
- 2,602
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 ...
- 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 ...
- 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 ...
- 334
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 ...
- 111