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
1
vote
2answers
167 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
306 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 ...
5
votes
1answer
2k 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
2k 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 ...
0
votes
2answers
107 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
63 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
57 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 ...
4
votes
1answer
2k 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 ...
13
votes
2answers
5k 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
1k 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 ...
0
votes
0answers
762 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
477 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 ...
1
vote
2answers
572 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
107 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
390 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
411 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
2k 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
516 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]...
1
vote
1answer
2k 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
104 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
44 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 ...
2
votes
1answer
788 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
62 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
74 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
44 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 ...
7
votes
2answers
3k 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
217 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
239 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 ...
1
vote
1answer
2k views

Why does a rollback of a CREATE INDEX statement take longer than expected?

Recently, I needed to apply a clustered index to a table. I first performed the operation in a Staging environment. The operation completed in about 20 minutes, so we planned for PROD to take ...
2
votes
2answers
431 views

Clustered Index and Primary Key on table with unique identifier and date

I have table with Orders with unique incrementing ID and (order creation) Date. This table is quite large and wide (5mn rows, 50 columns, 10 columns of which are FK IDs). Example: CREATE TABLE Orders ...
2
votes
2answers
77 views

Is it needed to add additional column to make this clustered index unique?

I have a table as listed below in SQL Server 2012. There is a clustered index on RequisitionID – but this column is not unique. There can be many ProductID for one RequisitionID. CREATE TABLE [dbo].[...
3
votes
3answers
2k views

Difference between a unique clustered index and a clustered primary key?

I'd like to ask two supplementary/follow-on questions, further to this previous/existing question: Is there any tangible difference between a unique clustered index and a clustered primary key? That ...
2
votes
3answers
529 views

Clustered index on random order column

I have an existing table named “Orders” in our system. OrderID is the primary key in this table – it is a clustered index. I have a new table designed as shown below for “OrderCompanyDetails”. It has ...
3
votes
2answers
564 views

SQL Server heap tables fragmentation monitoring

As a SQL Server DBA I have maintenance jobs to regularly rebuild/reorganize indexes. Recently, I ask myself about table fragmentation in SQL Server. Then I have read article regarding heap tables and ...
7
votes
2answers
1k views

Is heap a table without indexes at all or without a clustered index?

BOL seems to define a heap as a table without a clustered index. But many online posts seem to equate a heap to a table without any kind of indexes. Is there any subtlety I am not aware of? Thanks
11
votes
0answers
658 views

Splitting Snapshot files with MaxBCPThreads for Transactional Replication

I've just set up a publication, and I'm attempting to get the snapshot to apply faster. So far the Distribution Agent is respecting the MaxBCPThreads settings, but the Snapshot Agent is not. I'm ...
0
votes
1answer
216 views

fragmented index not defragmenting after an index rebuild [closed]

I have a weird behavior that I'm trying to explain to a client with SQL express 2014 (12.0.2269.0). The table in question has 4.5 million rows and has over 60 thousand pages. The average ...
1
vote
2answers
131 views

Which columns to place the clustered index

I have a system that has been up-and-running for almost 3 years now. Previously (and currently) the front-end was using NHibernate for all of the database access. Right now, I'm in the process of ...
1
vote
3answers
578 views

Is GUID(uniqueidentifier) not suitable for a clustered index? [closed]

When i use uniqueidentifier datatype for primary key column then it will automatically create clustered index... So, will it slow down my queries or not ? I am explaining some description of my ...
1
vote
2answers
75 views

Is it good to create new indexes for better performance - SQL Server [duplicate]

Hi I have a SQL Stored Proc that used to take 45 seconds to execute. Then I run the Execution Plan under SQL and saw some high cost steps: Then I right click that and select: And finally I create ...
3
votes
1answer
1k views

Ordering of table is based on Clustered index or non clustered primary key?

I have created a table with primary key on name column, the ordering of the table is based on name column create table TestPrimaryKey (Name varchar(20) primary key,id int identity) insert into ...
3
votes
1answer
83 views

Copy primary keys/clustered indexes from Prod to DEv/Test env

We followed the best practices of creating Primary Keys/Clustered indexes on tables which are actively queried on prod. Now there is a scenario where we need to get the similar above changes done ...
0
votes
2answers
462 views

Use of Non clustered Indexes

I know the differences between clustered and non-clustered indexes. Can someone tell me the various scenarios where we actually prefer non clustered index over a clustered index ?
0
votes
1answer
239 views

Can InnoDB use a clustered index as a covering index?

The scenario I have in mind is a query like: SELECT id FROM table WHERE id = ? or equivalently, SELECT 1 FROM table WHERE id = ? So here's some background: In a B+ tree (which InnoDB uses as ...
13
votes
1answer
561 views

Clustered index not used in a delete statement

I have a SQL Server table defined as follow CREATE TABLE [dbo].[Production_Detail] ( [Id] [bigint] NOT NULL DEFAULT (NEXT VALUE FOR [dbo].[Production_Detail_Seq]), [Meta_Data_ID] INT NOT NULL ...
0
votes
3answers
76 views

Clustered Index and PK - Two different Objects?

I have a table which has a Clustered PK as follows: CREATE TABLE [dbo].[PanelCcmRawData] ADD CONSTRAINT [PK_CcmRawData] PRIMARY KEY CLUSTERED ( [CcmRawDataId] ASC, [LineageId] ASC ) I ...
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
101 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 ...