A type of index mostly used in SQL-Server, which aligns the data of a table with the index.

learn more… | top users | synonyms

0
votes
0answers
40 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
1answer
45 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 ...
3
votes
1answer
60 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
54 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] ...
1
vote
1answer
59 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 ...
0
votes
1answer
130 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
33 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
78 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 ...
2
votes
1answer
255 views

PostgreSQL 9.x: a clustered index on column A is 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 ...
2
votes
1answer
111 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 ...
3
votes
1answer
209 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 ...
6
votes
1answer
268 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 ...
3
votes
3answers
293 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. ...
1
vote
0answers
63 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
190 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
1answer
110 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 ...
8
votes
2answers
238 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
1answer
208 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) ...
0
votes
1answer
69 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 ...
5
votes
3answers
408 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
56 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.
0
votes
1answer
210 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
129 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 ...
1
vote
2answers
191 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 ( ...
1
vote
1answer
51 views

Choice of clustered key when almost all columns would be part of a unique index anyway

I am creating the following table CREATE TABLE dbo.COMM_SHELF_BLOCK_ACCESS ( KeyId uniqueidentifier NOT NULL ROWGUIDCOL CONSTRAINT DF_COMM_SHELF_BLOCK_ACCESS_KeyId default NEWSEQUENTIALID(), ...
6
votes
3answers
694 views

rebuild on clustered index, why does datasize shrink?

When we did a rebuild on a clustered index on a table that have about 15gb data in it and the datasize shrunk to 5gb, how can this be? What kind of "data" is removed? Data size i mean the "data" ...
0
votes
1answer
26 views

Does PostgreSQL `CLUSTER` command also cluster extended storage?

I'm trying to improve the locality of my data by using the CLUSTER command to periodically cluster records that share the same user_id. My most important data is in extended storage. Will extended ...
2
votes
0answers
68 views

Can there be any case where it would require additional indexes while all columns compose primary key

Ok i don't know whether this question is asked before or not Here my table SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[tblProductPagesFeatures]( ...
1
vote
2answers
263 views

Index optimization for table that is truncated every day

I have a bunch of tables in SQL Server 2012. These tables are fed by other tables. Every day there are routines that truncate (fully delete) them and run queries whose result are inserted into them. ...
1
vote
1answer
136 views

Query hint? for deduplicated CTE in Merge

Referring to this link: Optimizing MERGE Statement Performance The following criteria are given for ideal performance: Create an index on the join columns in the source table that is unique and ...
0
votes
2answers
64 views

Does it make sense to order condition in where query?

We have a clustered index in table Tbl with order of A,B,C. Does it make senses to write a query to have WHERE A = @a, B = @b, C = @c or it'll be as same as WHERE C = @c, B = @b, A = @a Does the ...
0
votes
1answer
66 views

Indexes and their uses [closed]

Index improve performance in select statement, but what is the difference between this create clustered index ix_index1 on table1(col1) create clustered index ix_index2 on table1(col2) and this ...
2
votes
3answers
2k views

Identity column as clustured index bad idea?

Its just been a few months of programming in SQL Server for me so my knowledge is not good in many regards. In an already existing project at work I came across many tables with large composite ...
8
votes
1answer
293 views

Why does an UPDATE against a table with an INSTEAD OF UPDATE trigger appear to do a clustered index insert, as well as a clustered index update?

I'll start with a very simple example: two tables, both with the same schema, clustered on PK, but one of which has an INSTEAD OF UPDATE trigger: CREATE TABLE Standard ( PK UNIQUEIDENTIFIER ...
1
vote
1answer
766 views

Script to automate converting a clustered index on primary key (with foreign keys to it) column to non clustered?

What's a good way to easily convert a clustered index on a primary key column to a nonclustered index? I know how to do this manually, but it's time consuming if there are several tables which have ...
2
votes
1answer
172 views

Almost sequential GUIDs vs random GUIDs?

I'm looking at using GUIDs (UUID) as primary key with clustered index in SQL Server. In my webb-app I have many clients who will create items client side in javascript and save them only now and ...
1
vote
1answer
209 views

Clustered Index with nightly ETL process

We have a nightly ETL process that involves data from many different sources being flattened, and loaded into a completely denormalized table on SQL Server 2008 for reporting. The clustered index on ...
6
votes
2answers
2k views

Why do Indexed Views not permit non-unique clustered indexes?

I've been looking into using Indexed Views to increase performance on a few of our most commonly used views. However Indexed Views do not support non-unique clustered indexes which goes a little ...
3
votes
3answers
978 views

PostgreSQL 9.0 how to reclaim disk space after delete without rebuilding table?

Our PostgreSQL 9.0 Windows production server is running low on space. In our 100GB database, we have a large table containing TOASTed binary data. We have deleted some rows and need to return the ...
0
votes
2answers
123 views

Which should I use on my DB? - Clustered Index or Non Clustered Index or Both

I just need advice on how I am going to optimize my database. Date ---- Time ---- Area ---- Block ---- Data1 ---- Data2 ---- Data3 ---- DataN I have that format of tables on my database. Each ...
2
votes
1answer
75 views

Clustered index additional storage

Reading some books I found that clustered index not require additional disk space for storing index data, because leaf level of clustered index are data itself. But, as clustered index is organized ...
2
votes
1answer
65 views

Moving Clustered Index to new filegroup results in excessive filegrowth

I've Moved the clustered index on large tables to new filegroups to defrag, compress, and to recover space more easily from the original "primary" filegroup. Problem: A 315GB compressed Clustered ...
2
votes
1answer
131 views

Clustered Index / Index Oriented Table performance in join, worse than nonclustered?

Can a clustered index (or IOT in Oracle) be detrimental, when to be used on a very "broad" table, but only few columns are used? In this case, the "Product" table is used only like a junction table ...
-1
votes
1answer
2k views

Difference between Clustered Index and Unique Clustered Index? [closed]

I got difference between clustered index and unique index here. But what is the exact difference between clustered index and unique-clustered index? AFAIK creating clustered index can be done for a ...
2
votes
1answer
595 views

Slow SELECT against clustered index [closed]

I have a table which will hold 10 million rows. I'm seing performance issues when quering it, and wonder if I can somehow improve the schema or query, or if I need to look into sharding. I've used SQL ...
2
votes
1answer
300 views

NEWSEQUENTIALID() resets sequence on server restart

MSDN learns me that NEWSEQUENTIALID(): Creates a GUID that is greater than any GUID previously generated by this function on a specified computer since Windows was started. After restarting ...
6
votes
3answers
5k views

Indexing a PK GUID in SQL Server 2012

My developers have setup their application to use GUID's as PK for pretty much all of their tables and by default SQL Server has setup the clustered index on these PK's. The system is relatively ...
1
vote
0answers
152 views

Why does removing a RID by creating a clustered index lose parallelism and slow down execution by 2X

If I remove a RID lookup by replacing 2 non-clustered indices (non unique) with 2 identical clustered indicies (non-unique) where there weren't any before, why does this query slow down by a factor ...
4
votes
2answers
191 views

Can a single SQL Server instance issue the same SYSDATETIME twice?

I am contemplating using a datetime2(7) as a clustered index. If I were to always populate that column using SYSDATETIME is it possible for the same instance to issue the same value twice (assuming ...
1
vote
0answers
4k views

How to speed up query on table with millions of rows

The Issue: I'm working on a big table that consists about 37mln rows. Data include measurements of many devices made in certain time e.g. '2013-09-24 10:45:50'. Each day all of those devices are ...