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
2 votes
2 answers
176 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, ...
user avatar
  • 599
0 votes
1 answer
66 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. When I arrived I found other ETLs that also ...
user avatar
0 votes
2 answers
22 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 ...
user avatar
2 votes
2 answers
297 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 ...
user avatar
  • 123
0 votes
0 answers
25 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 ...
user avatar
  • 266
0 votes
1 answer
135 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"...
user avatar
0 votes
2 answers
78 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 ((...
user avatar
  • 173
0 votes
2 answers
55 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 ...
user avatar
  • 173
0 votes
0 answers
49 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 ...
user avatar
  • 137
-1 votes
3 answers
98 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
67 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 ...
user avatar
  • 2,223
0 votes
1 answer
64 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 ...
user avatar
-1 votes
1 answer
72 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 ...
user avatar
0 votes
1 answer
43 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, ...
user avatar
1 vote
0 answers
92 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 ...
user avatar
  • 11
1 vote
0 answers
122 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 ...
user avatar
  • 1,163
16 votes
4 answers
3k 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 avatar
  • 163
2 votes
2 answers
90 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 , ...
user avatar
0 votes
1 answer
397 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" ...
user avatar
0 votes
0 answers
40 views

What is the point of adding an unrelated index field to a heap? [duplicate]

I have the following heap [Suburb] [nvarchar](50) NOT NULL, [State] [nvarchar](3) NOT NULL, [PostCode] [nvarchar](4) NOT NULL, [NSC] [nvarchar](3) NOT NULL with a unique non-clustered ...
user avatar
  • 167
0 votes
2 answers
42 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 ...
user avatar
5 votes
2 answers
160 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 ...
user avatar
0 votes
0 answers
57 views

How does the SQL server decides the Max number of rows in a row_group for a CCI?

I have an application such that I have to load 3 sets of incremental data every day and the batch count is ~30 million records each run. So I am worried about my CCI compression quality. Note that my ...
user avatar
1 vote
2 answers
140 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 ...
user avatar
  • 820
-2 votes
1 answer
49 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 ...
user avatar
1 vote
1 answer
142 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 ...
user avatar
0 votes
1 answer
127 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 ...
user avatar
  • 11
0 votes
2 answers
63 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 (...
user avatar
5 votes
2 answers
580 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 ...
user avatar
0 votes
1 answer
74 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 ...
user avatar
  • 1,163
0 votes
1 answer
65 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 ...
user avatar
  • 24.2k
0 votes
2 answers
44 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 ...
user avatar
0 votes
1 answer
42 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 ...
user avatar
  • 111
0 votes
4 answers
102 views

Is a Non-Clustered index needed when referencing a small table with a Foreign Key

I'm not a DBA, excuse me if this question sounds dumb. Hope I can get help from an experienced DBA, because I'm questioning this for a long time. Assume there are 2 tables, one table with a lot of ...
user avatar
  • 175
2 votes
1 answer
227 views

Save performance with large update on Index with Included Column

Points ------------------ PK QuestionId int (+30.000.000 distinct values) PK EventId int (large batches where 80.000 rows have the same EventId) Value smallint The table is about 40 ...
user avatar
  • 333
0 votes
3 answers
236 views

How to fine tune a huge problematic Heap Table? [closed]

Problem: A huge table is causing to a critical performance issue on a very critically important production database (MS SQL Server 2016 SP1 Standard Edition). Facts about the problematic Table : Heap ...
user avatar
1 vote
1 answer
61 views

Are page numbers of a book a good analogy for a clustered index? [closed]

The title pretty much covers the question. I think that the index of a book is a good analogy for a non-clustered index, as it demonstrates the extra storage and physical separation properties of a ...
user avatar
  • 113
1 vote
2 answers
67 views

How do I add composite MySQL Indexes with denormalization and JOINS?

I have the following tables: CREATE TABLE base_event ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, created_by ... -- some columns ); CREATE TABLE transaction_events ( event_id ...
user avatar
1 vote
1 answer
267 views

How to prevent RID lookup?

I have a table that stores time-series data and has about 8 million rows. The table structure is as follows: Timestamp | ComponenentID | Parameter1 | Parameter2 | Parameter3 I have a clustered index ...
user avatar
7 votes
2 answers
250 views

Clustered Index fragmentation vs Index with Included columns fragmentation

I have a relative large table (for me) with 40 million rows that is expected to grow to 80 to 120 million rows in a period of two weeks (during a campaign). Tip -------------- Id int (...
user avatar
  • 333
0 votes
2 answers
150 views

How to deal with fragmentation in PostgreSQL?

What are best practices for dealing with fragmentation in PostgreSQL? Example: I have a multi-tenant database whose workload includes on-the-fly aggregation for reports. CREATE TABLE account ( id ...
user avatar
0 votes
1 answer
86 views

Sql Server Dynamic partition with page compressed index

I created a table with partitions by following this 2012 partitioned index document. I created partitions monthly based on Date_Id column; CREATE PARTITION FUNCTION Fnc_Prt_Fact_Sales (INT) AS RANGE ...
user avatar
0 votes
2 answers
297 views

Clustered index in ETL (drop - insert - create?)

I have a table which is 130 milion rows. Every day I extract data from a source system and insert into this table. The extract is incremental i.e. I only get the new data from the source. The table is ...
user avatar
  • 587
1 vote
4 answers
706 views

Reclaiming space after delete statement in SQL Server

I have some tables used in reporting and some of them grow huge with the daily ETL so I have implemented som jobs that delete rows more that x days old. E.g. everyday 10% new data gets added and I ...
user avatar
  • 587
0 votes
4 answers
292 views

Using an index when leading column is not in the predicate

Are there any circumstances when an index could be used when not all the index columns are included in the predicate? In my case the leading column in the index is unique and is part of the unique ...
user avatar
0 votes
2 answers
137 views

Tuning Clustered Indexes in case of Soft / Logical delete

I have a table (say dbo.AppUsers) with a clustered index on the always increasing, application generated column AppUserId. I have also a Bit column IsDisabled, that is used to logically delete/disable ...
user avatar
  • 105
0 votes
2 answers
99 views

When no index is faster than having a clustered index?

I ran the same query, which involves joining two tables A and B, when B had no index at all and then added a PK clustered index on the ID column used for joining to it. The time elapsed when there was ...
user avatar
1 vote
1 answer
60 views

How to find out if a row with a specific primary key exists in a table in SQL [closed]

I have created a table in SQL which is named testTable like this where I have defined the: PRIMARY KEY (FeatureNbr, DateTime) CREATE TABLE testTable ( [DateTime] SMALLDATETIME NOT NULL, [...
user avatar
  • 45
1 vote
2 answers
292 views

Benefits of not having a clustered index on tables (heaps)

What are the benefits of not having a clustered index on a table in SQL Server? Will: SELECT * INTO TABLE_A FROM TABLE_B Be faster if TABLE_A is a heap? Which operation(s) will benefit if the table ...
user avatar
  • 587
6 votes
3 answers
609 views

Online Index Rebuild of large table takes exclusive lock

I'm trying to rebuild the clustered index of a large table (77GB) on Azure SQL Database. There is high concurrent transactional activity on the table so I'm using the ONLINE=ON option. This has worked ...
user avatar
  • 446

1
2 3 4 5
7