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

learn more… | top users | synonyms

10
votes
2answers
309 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)) ...
0
votes
1answer
28 views

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

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
1answer
2k views

What factors go into an Indexed View's Clustered Index being selected?

Breifly What factors go into they query optimizer's selection of an indexed view's index? For me, indexed views seem to defy what I understand about how the Optimizer picks indexes. I've seen this ...
14
votes
2answers
13k views

Why index REBUILD does not reduce index fragmentatation?

I have used ALTER INDEX REBUILD to remove index fragmentation. In some cases REBUILD does not seem to remove this fragmentation. What are the reasons why REBUILD does not remove fragmentation? It ...
2
votes
2answers
415 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 ...
4
votes
2answers
67 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 ) ...
2
votes
1answer
171 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 ...
0
votes
0answers
22 views

Composite Primary/Clustering Key vs GUID for Merge Replication scenario

I need to decide on a primary key for my tables in a merge replication scenario (currently one laptop taken into the field during the day while another user works on a desktop; both users need ...
5
votes
2answers
136 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 ...
2
votes
2answers
59 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, ...
3
votes
1answer
554 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 ...
2
votes
1answer
194 views

Change clustered index without dropping the primary key

In past, I have chosen date-time column created as inappropriate clustered index in the table. Now I guess (base on execution plan) that it would be better to choose ID identity primary key as ...
4
votes
1answer
57 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
223 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 ...
1
vote
1answer
79 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
47 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 ...
1
vote
1answer
53 views

SQL server UPDATE statement is processing records that should be eliminated by WHERE clause

I am getting a very odd error. Consider the table below. CREATE TABLE #MyTable ( Key1 INT , Key2 INT , x SMALLINT , y INT , z INT , a FLOAT , b FLOAT , c SMALLINT , s FLOAT ) -- insert ...
0
votes
0answers
36 views

How to improve the slow performance with Insert operations in SQL Server 2005?

Suddenly our server started to have a bad performance, after trace the error, I identified that the inserts are the problem, this seems a know bug: https://support.microsoft.com/en-us/kb/940545 (The ...
3
votes
0answers
66 views

Clustered index on a join table

Currently I have a table defined as follows, but I'm not sure what to cluster the table on: CREATE TABLE [Security].[UserGroups] ( [UserId] INT NOT NULL, [GroupId] INT NOT NULL, [IsAdmin] ...
7
votes
1answer
107 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 ...
1
vote
0answers
16 views

Table with composite unique cluserted key and non-clustered surrogate primary key [closed]

We are running a legacy application which is an OLTP SQL 2008 R2 database having all tables defined as heaps with non-clustered index on surrogate primary key. I am considering introducing ...
6
votes
1answer
423 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 ...
0
votes
0answers
43 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 ...
3
votes
2answers
288 views

Defrag a HEAP by creating clustered index and immediately dropping it

I'm writing a script which intends to defrag a HEAP based table by creating a dummy col with a clustered index and then immediately dropping it. (It's someone else's app and I don't want to make any ...
1
vote
1answer
144 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 ...
4
votes
1answer
163 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
71 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] ...
18
votes
3answers
3k views

What are valid usage scenarios for HEAP tables?

I am currently doing some data imports into a legacy system and discovered that this system does not use a single clustered index. A quick Google search introduced me to the concept of HEAP tables and ...
1
vote
1answer
636 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
39 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
93 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
296 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
3answers
1k 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 ...
3
votes
1answer
1k 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 ...
3
votes
1answer
301 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 ...
4
votes
3answers
621 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. ...
3
votes
1answer
225 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
0answers
77 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
223 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
242 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
246 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 ...
0
votes
2answers
68 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 ...
1
vote
1answer
75 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 ...
6
votes
3answers
528 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 ...
1
vote
1answer
239 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 ...
-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.
2
votes
1answer
176 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
285 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
56 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
946 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" ...