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
0answers
58 views

Is making the primary key also the clustered index a mistake/pitfall/trap?

For the tables in our schema, we've so far just been creating them with the primary key defaulting to being the clustered index. Our assumption was that we would be able to change the clustered index ...
-1
votes
0answers
80 views

How to replace clustered index with non-clustered fast? [closed]

Can clustered index be effectively used in this case? We have a large table that keeps logs of users’ actions. 189-190GB clustered index. We want to analyze data there daily and run more complex ...
1
vote
1answer
22 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 ...
0
votes
1answer
46 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 ...
-1
votes
1answer
42 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
1answer
38 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, ...
1
vote
0answers
14 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 ...
1
vote
0answers
61 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 ...
16
votes
4answers
2k 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 ...
2
votes
2answers
77 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 , ...
0
votes
0answers
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 ...
0
votes
2answers
40 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 ...
5
votes
2answers
155 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 ...
0
votes
0answers
49 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 ...
1
vote
2answers
124 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 ...
-2
votes
1answer
46 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 ...
0
votes
1answer
75 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 ...
0
votes
1answer
77 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 ...
0
votes
2answers
46 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 (...
5
votes
2answers
487 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 ...
0
votes
1answer
68 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 ...
0
votes
1answer
36 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 ...
0
votes
2answers
42 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 ...
0
votes
1answer
38 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 ...
0
votes
4answers
85 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 ...
2
votes
1answer
123 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 ...
0
votes
3answers
128 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 ...
1
vote
1answer
52 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 ...
1
vote
2answers
45 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 ...
1
vote
1answer
153 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 ...
7
votes
2answers
221 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 (...
0
votes
2answers
71 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 ...
0
votes
1answer
54 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 ...
0
votes
2answers
188 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 ...
1
vote
4answers
370 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 ...
0
votes
4answers
182 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 ...
0
votes
2answers
98 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 ...
0
votes
2answers
82 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 ...
1
vote
1answer
54 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, [...
1
vote
2answers
191 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 ...
6
votes
3answers
485 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 ...
0
votes
2answers
86 views

What does it mean that a "clustered index does not store pointers to the data"? [duplicate]

I am a beginner in SQL and Database Administration and I am trying to undestand the difference between clustered and non-clustered indexes. More or less I understood what the different tasks are but I ...
1
vote
1answer
66 views

Clustered and columnstore indexes

I just started a new job where we have many tables from ETL processes. I was told not to put any columnstore or clustered indexes on any tables since the server does not have the resources (CPU). So ...
1
vote
0answers
78 views

service broker queue index growing beyond control

We have a queue that was set up about ten years ago in 2008. We have been running it on an Azure VM Standard edition sql 2016 for about 3 years no with no problems. In the last 3 months, with no ...
7
votes
2answers
1k views

Does it make sense to add indexes to a clustered columnstore index table?

Question is similar to this one, but the answer doesn't seem to answer this question. My understanding of a clustered columnstore table (and please correct me if I am wrong) is that each column is ...
0
votes
1answer
97 views

MySQL Clustered Index or Non Clustered index? (Database Design)

I know this might be a basic thing for you guys in this corner of SE but I'm having some trouble figuring out the best option for designing my DB. I am creating a custom app where the user will be ...
0
votes
0answers
31 views

MySQL InnoDB Weird Query Performance

I designed two tables, both using InnoDB. The first one has columns "Offset, Size, and ColumnToBeIndexed" with BIGINT "Offset" being the primary key, and the second one has columns "OffsetAndSize, ...
0
votes
3answers
38 views

Index of which columns are NOT empty

I am trying to enhance the search performance of a table based on whether certain columns contain values. I have the following columns : id | link | text | date | lat | long | type If a record ...
0
votes
0answers
15 views

Is 'data corruption occurs in the clustered index' still open?

Based on sp_Blitz Result: DBCC CHECKDB Not Run Recently, there is a reported bug in SQL Server 2012 & 2014, Data corruption occurs in the clustered index when you run online index rebuild in SQL ...
3
votes
2answers
149 views

SQL Server - creating a clustered index in a large heap - should I add a column?

I'm going to add a clustered index to a fairly large table: over 7 million records 106 columns no PK no other indexes. There is an Id column which could potentially be used for the index ...

1
2 3 4 5 6