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
33 views

When all you indexes have included all the other columns, do you still have the penalty for a wide clustered index?

So I hear sometimes that I should not use a clustered index on a wide column, mainly because it is being referenced in all other indexes. When all my other indexes on this table include all other ...
9
votes
3answers
1k views

Does a non-clustered index make any guarantees about row order?

I have a developer that would like, when doing a select statement with no order by, the rows in a table to be in the order they were inserted. The developer suggested changing from a clustered to non-...
6
votes
2answers
118 views

When Azure recommends an index (part of natural key) with all other columns included, should I switch to a clustered index for this natural key?

The table does have an identity key (current CI), but it’s barely used to query. Because the natural key is not ever-increasing I’m afraid of insert performance, fragmentation or other problems that I ...
1
vote
0answers
63 views

What queries read my indexes and what queries apply inserts / updates to my indexes?

I'm looking indexes with 0 reads with SP_blitzindex, from there I am able to grab the indexes names that are probably not helping my system. Using the top query posted by Kendra: SELECT ...
2
votes
0answers
603 views

C# Windows Service task fails due to a SP timeout, but works fine after executing the SP in SSMS

I have a Windows Service app running and sometimes a task which has a method call to a stored procedure (with a Select operation) is keep failing due to a timeout. But when I execute that SP with ...
2
votes
0answers
65 views

How is page data rearranged after clustered index is created?

Can anyone please explain what happens at the page level when clustered indexes are created? How do pages rearrange data once clustered indexes are created?
0
votes
1answer
91 views

why is the Log File is 5 times the database overnight when deleting?

The log file alone is close to 500 GB while the database is 80 GB. This is not a production system so I tried to delete a table which had 780,000 records. this table has 5 individual indexes that ...
0
votes
1answer
66 views

InnoDB: Is a covering index in the form of (PK, col1, col2, col3) redundant?

Table structure for config: property (primary key) value description Query: SELECT property, value FROM config If I put a covering index on (property, value) the optimizer still chooses the ...
1
vote
1answer
537 views

PostgreSQL covering index on large table as alternative to CLUSTER command

I'm looking for an alternative to executing the CLUSTER command on large tables to keep data efficiently organized on disk, since it requires an ACCESS EXCLUSIVE lock on tables being clustered, making ...
1
vote
0answers
67 views

Any performance gains from having identical clustered and nonclustered indexes on table on different filegroups?

I am attempting to rearchitect our MSSQL database such that we have two filegroups, one for data, on a 15K HDD, primary, and one for indexes on a separate SSD, indexes, in order to realize some ...
3
votes
1answer
65 views

Add Primary Key column at the end of an Index

If I have a table with the following primary key: PRIMARY KEY (id) And I want to query documents written by someone (e.g. display a preview of the latest 20), sorted by their "id", so I created an ...
0
votes
1answer
820 views

Mariadb clustering Index design question

Here are the columns of concern for a table describing tasks: Org_ID Task_ID Task_property_columns The only requirement for Task_ID is that it is unique per every Org_ID, however I would prefer ...
1
vote
1answer
1k views

PostgreSQL: Running SELECT DISTINCT ON ~700m rows - how to optimize?

I have a table that, if we look at just the relevant parts, has two columns: id and raw_data. id is an integer, and raw_data is a text blob. At this point, the table has no constraints or indexes ...
1
vote
1answer
152 views

Clustered Index - Trade-off Queries VS Inserts? [closed]

I'm working on improving database performance of my application and now it comes to the part of clustered index. I don't have experience with it so I would like to ask. My application caveats In my ...
2
votes
1answer
618 views

Can I change which index a primary key uses?

I have a table with a non-clustered index on the id, and a clustered index on some other fields. The fields of the clustered index are non-sequential, and are frequently modified. I'd like to change ...
2
votes
1answer
184 views

How does clustering on a field other than autoincrement id affect inserts?

I have a table with an id column which is auto-incremented, and various other informational columns. Rows are inserted into this table very frequently. When the data is read back, the majority of ...
4
votes
1answer
1k views

Does rebuilding a clustered index on an IDENTITY column do anything useful?

We have a COTS product that has created a very large transaction log table. It has a single clustered index which supports an IDENTITY column declared as a key. Last night the index got rebuilt and ...
16
votes
6answers
12k views

Rebuild Very Large Primary Key Index

I have a SQL database that is hosted on Azure. The problem is that the size is getting out of control, I can see up to 99% fragmentation in the Primary Key clustered indexes. I'm able to rebuild all ...
11
votes
2answers
346 views

Clustered Index Selection - PK or FK?

I have a SQL Server 2014 table that looks like the following: OrderId int not null IDENTITY --this is the primary key column OrderDate datetime2 not null CustomerId int ...
1
vote
1answer
129 views

How to swap postgresql rows physical locations?

I'm looking for a way to reorganize my database tables rows incrementally based on their clustered index. I would like to avoid using the CLUSTER command since it requires an ACCESS EXCLUSIVE lock on ...
8
votes
3answers
2k views

How to create clustered index on 100 GB table

I have a heap table that takes about 104 GB of disk space with almost 3 billion rows. I am trying to create a clustered index on this table on the [WeekEndingDate] column. I have about 200 gb's free ...
1
vote
2answers
190 views

Proper table design for sparse primary key

In my system I have temporary entities that are created based on rules stored in my database, and the entities are not persisted. Now, I need is to store information about these entities, and because ...
-3
votes
1answer
471 views

Fix SQL Table with *BOTH* Heap AND Clustered Index

Somehow, we have a SQL Server Table with both a HEAP and a CLUSTERED index on a table. Is there a way to fix this? e.g. delete the HEAP index by its object ID? If we drop the Clustered Index, it will ...
6
votes
1answer
5k views

Insert much slower when target table has clustered index

I've just had to debug an issue which has me thoroughly confused. An ETL Transform process on our Development Data warehouse has just failed after working successfully every day for months. The same ...
5
votes
3answers
4k views

SQL Server: Clustered index, sorting and pagination

In my application, several times I have to show results that are paginated and sorted by some field. For example a simple user list sorted by last name. Because of that and because I also have logical ...
0
votes
2answers
167 views

SQL server 2012 reports "Missing Index" on column which is covered by "Clustered Index"

My table creation script is as follows SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[quotation_section]( [quotation_id] [varchar](24) NOT NULL, ...
5
votes
1answer
84 views

Drawbacks to add clustered keys

I have a database that has multiple tables without any clustered index. All that I have found are small (< 500 rows). Specifically, PRIMARY KEY NONCLUSTERED with no other indexes at all. At a very ...
3
votes
1answer
220 views

Adding an identity field to be the new cluster of a large table - how do I get the "right" order?

I have a very large table (88m rows, nearly 300GB in size) that has a poorly planned clustered index. I want to add a BIGINT identity field, and make it the new clustered index (having the desirable ...
5
votes
4answers
2k views

Can Fill Factor be changed on specific tables without index rebuild?

I've been digging around without any luck. SQL Server 2008: Is it possible to adjust the fill factor on a specific clustered index/table without having to rebuild the entire table? For example, if ...
5
votes
1answer
4k views

Recreating Clustered Index Efficiently

In a test database, I am looking to: Drop clustered index (they are Primary Key Clustered Constraints on a row that is super useless for us.) Create new Clustered index re-create Primary Key ...
15
votes
2answers
9k views

What index to use with lots of duplicate values?

Let's make a few assumptions: I have table that looks like this: a | b ---+--- a | -1 a | 17 ... a | 21 c | 17 c | -3 ... c | 22 Facts about my set: Size of the whole table is ~ 1010 ...
10
votes
2answers
2k views

Clustered index creation fail on table creation

We've got an error when running the following script; IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE' AND TABLE_NAME='Table_Name') BEGIN CREATE TABLE ...
1
vote
0answers
1k views

Unable to update table using Oracle linked server : table corrupted?

I want to synchronize a SQLServer table (version 2008) with data coming from Oracle (11g). The query is quite simple, uses openquery and looks like: update t set field2 = tt.field2 from ...
0
votes
1answer
698 views

Index and Heap table storage on disk

I have a few questions on tables and Indexed tables and its storage, Here is my first image where i have taken an example with Northwind database and Table name is Orders. Orders Table is with 9 ...
2
votes
2answers
1k views

How to index a composite primary key with non-sequential inserts?

I have a composite primary key, comprising of a datetime, a location id and a 2 character code. This is the key that uniquely identifies each row of my table. I know that the usage of this table will ...
2
votes
1answer
121 views

How to tell if `CLUSTER table USING index` finished?

I want to use an AWS scheduled Lambda function to update my PostgreSQL table indexes every month. I am not on psql bash and I am not leaving Lambda function up until CLUSTER finishes. How can I tell ...
0
votes
1answer
404 views

Aggressive index tuning

Background info - I have a table that stores Job information with a unique JobID (PK) and a ClientID (one client can have many jobs), as well as bunch of columns that are heaped data. I have ran ...
6
votes
1answer
950 views

Is pre-sorting valuable when using table value constructor to insert multiple values into a table with clustered index?

This is related to this question: Efficient INSERT INTO a Table With Clustered Index From that question it sounds like SQL Server will sometimes pre-sort the data when inserting multiple values into ...
3
votes
1answer
3k views

Compound index on inner join table?

I'm trying to create additional index or indexes to speed up the query below. All the of the keys are primary keys (except for id on Table A) so they already have a default btree index associated with ...
4
votes
2answers
808 views

Best indexing strategy for query that searches between two bigint columns

I have the following table in MS-SQL: CREATE TABLE [dbo].[dbip_locations]( [ip_from] [bigint] NOT NULL, [ip_to] [bigint] NOT NULL, [country_code] [nvarchar](64) NOT NULL, [region_name]...
3
votes
1answer
3k views

What happens when you delete a Clustered index and Non Clustered Index once created

What happens when we dropped a Cluster index and Non Clustered Index once they are created? What will happen to the data at the leaf level according to Clustered Index ?
3
votes
1answer
208 views

Is it possible to have partition in SQL Server 2016 with both clustered and non-clustered indexes on different partitions?

Suppose I have an equities pricing table with a 1000 million rows. I need to be able to maintain consistency, read and write to it at high speed with a large number of clients. What I'd like is a ...
2
votes
2answers
47 views

Returning value with Clustered index

I have emp table (empno, ename, job, mgr, hire_date, sal, deptno) with no index at all. If i run select * from emp i get the rows ordered by empno. Now i create a clustered index like this: Create ...
4
votes
1answer
1k views

Advantages/Disadvantages of using a multicolumn clustering index

What are the advantage/ disadvantages of using a primary key that is a combination of multiple columns? I have come across a table that has a unique non clustered index as a GUID but the clustering ...
2
votes
2answers
97 views

Why parameter for one query returning the same # of results as a different parameter, is extremely disproportionately slower

I am an application developer that has inherited a SQL Database from a previous developer. There is one view that is causing an issue. This view selects a bunch of different columns from different ...
2
votes
1answer
79 views

What exactly is SQL Server doing?

We have a control table called Generators which has a NextID and varchar label. I found some bad code in a stored procedure which assigns a value w/o a where clause - something like below. declare @...
1
vote
0answers
48 views

Clustered index when optimizing for "fetch all children of a single parent"

Say I have a table BlogPost and a table BlogComment. Obviously a BlogPost can have many BlogComments. The most important query of the BlogComment table would look something like this select * from ...
8
votes
2answers
4k views

Is it okay to have a fill factor of 100 on the clustered index that is on an ID column?

So I have a table with a clustered index on an ID column (which is an auto-increment column) in SQL Server. Is it okay to have a fill factor of 100 on this since the data will always get written with ...
4
votes
4answers
302 views

Considerations for Adding Indexes to a SQL Server Database

I'm a .NET developer. So, I know enough about databases to be dangerous, but do not have the expertise of a proper DBA. We have a slow running stored proc and one of the devs here ran a query ...
0
votes
2answers
352 views

clustered index got disabled suddenly

I have a large database with size of 40 GB and a one of the main tables which have row count of 3763071 rows and name of "XXX" and that table has a clustered index which is the primary key suddenly ...