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
1answer
43 views

Does using PK and FK effect performance in negative way?

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
39 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
382 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
64 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
29 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
0answers
21 views

Non-unique clustering vs partitioning [closed]

I have large table with billions of rows. It has no DATE column, its kind of like a dimension table (very poor design i know). The table is clustered on a non-unique decimal(18,0) column called TaskId,...
0
votes
2answers
39 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
79 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
107 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
96 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
50 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
28 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
87 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
208 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
38 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
34 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
95 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
127 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
111 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
55 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
71 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
51 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
134 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
2answers
414 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
56 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
63 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
58 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
708 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
47 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
22 views

Primary Key and Unique Constraints in Partitioned Table

I am currently creating a project that applies to a table that contains about 40 columns (including varchar (max)) and will be partitioned. About 200-250 records per second will be inserted into the ...
0
votes
0answers
23 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
35 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
13 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
134 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
vote
0answers
20 views

How to create Non-clustered Covering index on PK while creating a table

I am trying to create a table where PK will not be a clustered index. PK will be non clustered with INCLUDE clause to add cover. CREATE TABLE [Ref].[User]( [UserRegisteredId] [varchar](100) NOT ...
0
votes
1answer
43 views

difference between create constraint non clustered and create index non clustered

I am new to sql server. i was trying to learn creating index. i tried to create a table with below query: CREATE TABLE [Ref].[Country]( [CountryId] [uniqueidentifier] NOT NULL, [CountryName] ...
0
votes
1answer
48 views

Why scan clustered index instead seek nonclustered index?

There is SQL script: CREATE TABLE users ( id INT, firstname VARCHAR(50), surname VARCHAR(50) ); CREATE CLUSTERED INDEX ix_users_id ON users (id); CREATE ...
0
votes
0answers
18 views

Would a set of non-clustered index work better than a clustered index for my large table?

I'm trying to create an index over a table with potentially 100 million rows. Right now I have the following columns: name="date", type ="DATE" name="account_id", type ="INT64" name="group_id", type =...
0
votes
1answer
31 views

Does the order for clustered index have the same constraints as non-clustered index on search?

I have a table filled with a bunch of data, and I want to use the following clustered index: [account_id, group_id, run_id, page_id, date] For a non-clustered index, if I were to to use WHERE ...
0
votes
1answer
44 views

If PK is Non-Clustered index, how to identify clustered index candidate?

I'm designing a database for insurance company. I want to make Primary key as GUID-uniqueidentifier. I want to create Index for each table in database for performance optimization. I did research ...
0
votes
1answer
70 views

How does an index speed up insertion in a table

How does index speed up insertion in a table? I have an idea how it happens for retrieving the rows and selection of data, but how does it helps in insertion? CREATE TABLE persons( id NUMBER , ...
-1
votes
1answer
31 views

Order of a 2-level b+tree with n-distinct (assume e.g. n=10) entries

Given a two-level clustered b+tree on a non-key attribute foodCategory (of which 10 distinct values exist), is it possible to calculate the order of the b+tree? Assuming an average 69% occupancy of ...
2
votes
3answers
272 views

Is it possible to track the progress of when I drop a the primary key clustered index on a table?

I have a large primary key clustered index I need to drop. I believe dropping it will also trigger index rebuilds on the nonclustered indexes (which are also pretty big). Is there a way to determine ...
0
votes
0answers
71 views

Explaining about SQL Server query Execution Plans and understanding Clustered and Nonclustered Indexes

I'm Christine and I'm a newbie. I would like to know if someone could help me clarify what I'm drilling down this execution plan as below : Here's query generated from SQL server that is accompanied ...
0
votes
1answer
50 views

Does a unique clustered index give array-like lookup performance?

Suppose I have a MySQL table with a unique integer index id that is auto-incremented for each row. id | PlanetName ---+----------- 0 | Mercury 1 | Venus 2 | Neptune Suppose that I am primarily ...
0
votes
1answer
75 views

Non-clustered index covering a clustered index

I have a table that has the following indexes: IXC clustered index that includes a single column : A asc. IX1 that includes two columns : A asc, B asc. In this case ICX seems like a duplicate index ...
1
vote
2answers
117 views

How to compare write speed before and after adding index?

I am planning to add a non-clustered index (composite) to my table with around 5 million records and 7 columns. Table has only clustered index on id column. The first thing I am going to do is test on ...
0
votes
1answer
40 views

How to create a clustered index on a schema bound view with the least amount of contention caused?

I have a table with 100 billion rows in it, with an identity BIGINT column that is the primary key (and clustered index). I have a schema bound view on that table that is filtered down to the last 500 ...
1
vote
1answer
50 views

Do filtered indexed views cause contention on the underlying table when rows are added to the table that fall outside the filter?

If I create an indexed view on a table and the query on that table includes a filter, when new records are added to that table that don't fall into that filter (and therefor wouldn't show in the ...

1
2 3 4 5 6