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
9
votes
3answers
1k views

Should I add an auto-increment / IDENTITY field to a cross-reference table just for PK purposes?

I'm adding the following cross-reference table to my SQL Server-hosted DB: company_id bigint not null (FK) org_path nvarchar (2048) not null The company_id field refers to the id field in another ...
1
vote
1answer
102 views

Should I remove this clustered index?

I have lots of tables designed similar as the following (slightly simplified) sample. The original table on different servers has between 50,000 and 200,000 rows (around 12,000 - 150,000 pages). ...
7
votes
3answers
1k views

Would a table benefit if it was a heap

I've a logging table with approx 1.500.000 rows, the primary key is an ascending identity and the clustered index is on the primary key. The identity value is auto-generated => records are always ...
2
votes
2answers
2k views

Does Clustered Index on GUID create more fragmentation than Non Clustered Index?

I have a simple table: CREATE TABLE [dbo].[UserTestGroups]( [UserTestGroupId] [int] IDENTITY(1,1) NOT NULL, [Token] [nvarchar](100) NOT NULL, [TestId] [bigint] NOT NULL, [Group] [...
3
votes
3answers
2k views

Why SQL Server uses Non-Clustered but not Clustered Index?

I have a table with 145 million rows CREATE TABLE [dbo].[RFTest]( [SnapshotKey] [int] NOT NULL, [SnapshotDt] [datetime] NOT NULL, [LoanNum] [int] NOT NULL, [GLSourceSystem] [varchar](...
0
votes
2answers
241 views

Clustered Index Misunderstanding

I've read in couple of articles that clustered index is as the same as table, or if you read using clustered index, you have the whole record. Some say, clustered index stores the whole records in ...
2
votes
0answers
179 views

Clustered index on a fact table

I'm trying to work out what field(s) to use for the clustered index of my fact tables (which all have a similar structure). I'll use FactSales as an example: CREATE TABLE dbo.FactSales ( ...
1
vote
0answers
55 views

Varchar PrimaryKey. Clustered or nonclustered? [duplicate]

I am working on a legacy database. The primary key is clustered varchar(128) and the table contains a high volume of data. Does it necessarily help performance if I change the clustered PK to ...
8
votes
3answers
1k views

What does having the primary key as the last column in a composite secondary index in an InnoDB table do?

Say I have a 1-to-N relationship (person_id, pet_id). I have a table where pet_id is the primary key. I understand that an InnoDB secondary index is essentially a B-tree where the values are the ...
7
votes
4answers
35k views

How to create Clustered and non clustered index in Oracle?

I dug couple of hours regarding my question and didn't get satisfactory answer. Still I have doubt. I have found the following about Clustered Index: Data is stored in the order of the clustered ...
1
vote
0answers
109 views

Table clustered-index and partitioning in innoDB

I have a table with 45 columns, 3 of which are relevant: table Transactions(tr_id : int primary key, client : int, tr_date : datetime); The inserts to the table are done client-by-client The selects ...
2
votes
1answer
126 views

Why does SQL Server require non-clustered index to use unique keys?

According to https://stackoverflow.com/questions/4332982/do-clustered-indexes-have-to-be-unique The answer is yes. In the sense that SQL Server will make it unique by appending 4-bytes to key. This ...
0
votes
1answer
33 views

Splitting the table to get more clusterization options - will it help? [closed]

Complete theory. Let's assume we have a table with a few millions rows. The table has clustered index over PK, and it is important for quick fetching of particular rows. However, the table also has a ...
11
votes
2answers
3k views

SUM of DATALENGTHs not matching table size from sys.allocation_units

I was under the impression that if I were to sum the DATALENGTH() of all fields for all records in a table that I would get the total size of the table. Am I mistaken? SELECT SUM(DATALENGTH(Field1)) ...
6
votes
2answers
5k views

Why SQL Server has 900 byte index size limit

Why can't we create an index having size greater than 900 bytes? This question was raised in a presentation to which no one had an answer. I can think of this way to answer this question. Page size ...
3
votes
1answer
867 views

Datetime Clustered Index Keeps getting fragmented

I have a large table (4 million Records) named CDR, which is used for storing CDRs (Call Detail Record)s from a Cisco router in a VoIP system, the table is constantly growing and will never have a ...
3
votes
2answers
516 views

Does my table need a primary key and clustered index change?

I have a table holding 1.7 Milion rows. Definition: CREATE TABLE T( [ID] [uniqueidentifier] NOT NULL, [AID] [uniqueidentifier] NOT NULL, [BID] [uniqueidentifier] NOT NULL, [iType] [int] NOT NULL, [...
5
votes
2answers
815 views

Howto Identify Clustered Index Candidates

I find myself in a situation where I have inherited the db and people complain about database performance. I identified about a dozend of tables not having a clustered index or a not carefully planned ...
6
votes
1answer
11k views

Change clustered index without dropping the primary key

In the past, I have chosen the datetime column created as inappropriate clustered index in the table. Now I concluded (base on the execution plan) that it would be better to choose the ID identity ...
5
votes
1answer
246 views

Online clustered index rebuild using more log space than expected

I have a table TableA ID PK BIGINT TrxID BIGINT CardBalance INT AccountBalance INT SP_SPACEUSED 'TableA' output: name rows reserved data index_size unused TableA 904054184 ...
1
vote
1answer
118 views

Single Clustered Index Concept

Lets suppose we create a table called "SampleTable" and made "ID" column a primary key. SQL Server by default creates a clustered index on the table. This physically sorts the data on the disk. Now ...
1
vote
1answer
78 views

Can't eliminate index scan

I have a query, and even using SQL Sentry, I can't eliminate the index scan. This is the query: SELECT TOP 30 codCliente FROM ( SELECT t1.CodCliente, codcampo, valor, t1.chavealeat ...
3
votes
0answers
492 views

Clustered index on a join table

I have a table defined as follows, but I'm not sure how to cluster the table: CREATE TABLE [Security].[UserGroups] ( [UserId] INT NOT NULL, [GroupId] INT NOT NULL, [IsAdmin] BIT NOT NULL, ...
4
votes
2answers
542 views

Wide clustered index vs multiple narrow nonclustered indexes?

Say I have a contrived Student table like so: CREATE TABLE Student ( Id IDENTITY INT, SchoolId INT NOT NULL, FirstName VARCHAR(20) NOT NULL, LastName VARCHAR(20) NOT NULL ) ...
7
votes
1answer
142 views

Clustered Index Help, where did i go wrong?

I was asked to review a very busy table and identify any areas of improvement. I have very limited power to change the table just the indexes. Table information 240 columns ~5 million rows Read and ...
0
votes
0answers
54 views

Refactoring 80 GB table that stores massive amount of calculated results

Trying to evaluate architectural changes to improve the performance of an app that calculates a lot of individual values and stores each of these results as a row. The storage size for one of the ...
1
vote
0answers
694 views

Dropping old and creating a new clustered index

I am wondering what is the best approach for dropping and creating a new clustered index on a table within SQL Server 2008. My primary concern is the length of time it will take to perform this ...
5
votes
1answer
716 views

Column Store Index over an clustered index (created when primary key is declared) - SQL Server

When a clustered index is created, the table itself becomes the index structure sorted by the index key. Imagine now that we have 5 columns in a table XPTO (a,b,c,d,e) and "a" is the primary key and ...
0
votes
1answer
214 views

Which columns (and order) to choose for a clustered index to maximize query performance

In our database we have a table that looks more or less like the example below. In the past we always created the clustered index on the ID column on the table. CREATE TABLE [Measurement] ( [ID] ...
2
votes
1answer
2k views

MySQL/InnoDB - Clustering Index with UUID

In MS SQL Server it is possible to cluster an index on a non-primary, non-unique key. For example, if I wanted to create a clustered index on the insert date to prevent page splits and fragmentation ...
2
votes
1answer
3k views

Fastest way to disable indices and drop constraints and rebuild SQL Server table

Below are some T-SQL commands that we're running on a table with a few billion records. Most of the database's size is taken up by 5 tables like this. What is the quickest way to perform these steps ...
1
vote
1answer
53 views

Need advice on Primary and Clustering Keys of a table

I need advise on a table which is part of a development project I'm involved. A brief summary on the use of the table: Data from multiple databases will be consolidated in this table for the purpose ...
1
vote
2answers
136 views

Maintain Up-To-Date Copy of Very Large Table if Clustering/Partitioning/Moving Operation Needs to be Killed/Rollback

I've appended the following information in a previous post but I asked the 'why' instead of the 'how'. So here's the question... SQL Server 2014, CU6. 4.5 TB database, Simple recovery. The most ...
3
votes
1answer
2k views

Is a clustered index on column A the same as creating a table ordered by A?

Reading the official PostgreSQL documentation for version 9.0 I read an interesting escamotage that performs better than CLUSTER for big tables: The CLUSTER command reorders the original table by ...
4
votes
1answer
2k views

How to efficiently create a new PRIMARY KEY CLUSTERED definition online

Given a table with a PRIMARY KEY definition (acting as the clustered index), I want to efficiently change the definition of the PRIMARY KEY (or at a minimum, just the clustered index) online. I would ...
6
votes
1answer
2k views

Convert clustered primary key to non-clustered and use another column for the clustered index

I have a large table with a primary key (GUID) that is also the clustered index. There is already a integer sequence based field. So I want to leave the GUID as the PK and make the integer column the ...
4
votes
1answer
801 views

Is a clustered index on a child table in a parent/child relationship the most optimal index?

A clustered index will sort row data in a table according to the ordering of the fields in the index. If so, why would you (or would you not) want to create a clustered index on the child table in a ...
7
votes
1answer
3k views

Timestamp-based index

I have a big database (200GB+) that contains some log info. And I want to speed up SELECT queries and stored procedures. I have a table with a GeneratedOnUtc datetime column, and have a non-clustered ...
4
votes
3answers
2k views

Clustered index performance consideration in MySQL & PostgreSQL

In MySQL/InnoDB, the clustered index is synonymous with the primary key, so picking up a poor primary key affect your db performance, i.e. use of UUID as PK is a performance killer for database write. ...
2
votes
0answers
419 views

How to optimize clustered index rebuild?

I need to rebuild the clustered index of the table that has more then 1.6 millions rows, more then 20 non-clustered indexes and 80 columns in order to reclaim space after two NVARCHAR(MAX) columns ...
4
votes
1answer
476 views

Table with clustered Index consuming max memory causing low PLE alerts

For one of my server with below configuration i am continuously receiving alerts for low PLE which ranges between 20- 400 and an average of 90-100 in a day: Server: SQL server 2008R2 Databases : ...
0
votes
2answers
621 views

TokuDB + MariaDB 10: should I use a clustered index

I have a large table (200 Columns.. I know, but it wasn't my design) that will grow fast and I will have a lot of useless rows in it, identified by an IP (a varchar 32 column). The table will get ...
10
votes
2answers
326 views

Are “out of the row” fields read when a clustered index is used?

I know that when VARCHAR(MAX)/NVARCHAR(MAX) columns are used the data is stored out of the row - the data row will have a pointer to another location where the 'large value' is stored. I have the ...
3
votes
2answers
358 views

Why do nonclustered Indexes use clustered indexes instead of the table data pages directly?

From a Microsoft Course (presented by a third party) I learned that when a table has a clustered index even non-clustered indexes hit the clustered index. When there is not a clustered index (Heap) ...
1
vote
1answer
90 views

Skewed Clustered Index

I have a SQL Server database that has a pretty important table (join wise) that contains just over 2.1 million rows. For the VARCHAR column that defines the clustered index, 17.5k rows contain unique ...
7
votes
3answers
1k views

SQL Server clustered index, index balancing and insert performance using NewID

I have a large (6db) trace table. It has a clustered key (DateTime) which is created through GETDATE(). The connection pool for connections to this database/table rises as high as 50 on average ...
-4
votes
2answers
62 views

How to List out all the tables on the SQL server which do not have a Clustered index? [closed]

SQL Query to retrieve the tables on the server which does not have the clustered index.
1
vote
1answer
334 views

What is fragmenting my index on a table with stable traffic?

I am using Ola Hallengren's solution for optimizing indexes. I run it on Sunday every week. The index has had low fragmentation for the last 6 months and it hasn't needed reorganization. The way it's ...
2
votes
1answer
596 views

Foreign Keys to tables where primary key is not clustered index

I'm focusing on SQL Server here, but I think this is a database agnostic question, really. I have a table whereby a Guid is logically the primary key. However, uniqueidentifiers apparently make ...
2
votes
2answers
756 views

Indexed views on double linked server

A server SERVER_A has some data. A server SERVER_B defines A as a linked server, and defines some views on the data. The queries are like this: CREATE view myview as select * from openquery ( ...