All Questions
Tagged with primary-key clustered-index
30 questions
0
votes
5
answers
81
views
Log table without primary key
I have a SQL Server log table that includes a column "Id"
This is an identity column, but not a primary key, it is not even indexed.
This would have just been set up from some tutorial for ...
- 1
-1
votes
1
answer
673
views
Best indexing strategy for composite primary key with frequently queried columns and occasional additional column [closed]
I have a large table with the following composite primary key:
PRIMARY KEY(col1, col2, col3)
col1, col2, col3 are queried all the time.
Some times (maybe 10% of the time) I get queries for col1, col2,...
- 99
1
vote
1
answer
426
views
How to find out if a row with a specific primary key exists in a table in SQL [closed]
I have created a table in SQL which is named testTable like this where I have defined the:
PRIMARY KEY (FeatureNbr, DateTime)
CREATE TABLE testTable
(
[DateTime] SMALLDATETIME NOT NULL,
[...
- 35
0
votes
1
answer
182
views
If PK is Non-Clustered index, how to identify clustered index candidate?
I'm designing a database for insurance company. I want to make Primary key as GUID-uniqueidentifier. I want to create Index for each table in database for performance optimization.
I did research ...
- 21
2
votes
3
answers
2k
views
Is it possible to track the progress of when I drop the primary key clustered index on a table?
I have a large primary key clustered index I need to drop. I believe dropping it will also trigger index rebuilds on the nonclustered indexes (which are also pretty big). Is there a way to determine ...
- 39.5k
1
vote
0
answers
53
views
Changing clustered key on a big table
I have a big fact table in a datawarehouse (40 Gb worth of data in dev, 120gb in production ) which was created in a very classic way
CREATE TABLE BigFactTable
( BigFactTableId bigint IDENTITY ...
- 163
4
votes
2
answers
3k
views
Clustered vs Non Clustered Indexes for a Primary Key
I have a large table with around 19 million records in it.
The primary key is a uniqueidentifier column called ID and there is a nonclustered index on this column. An application generates these ...
- 157
2
votes
1
answer
1k
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 ...
1
vote
2
answers
316
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 ...
- 235
4
votes
1
answer
2k
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 ...
- 183
4
votes
4
answers
6k
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 ...
- 187
3
votes
1
answer
3k
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 ...
- 77
3
votes
1
answer
89
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 ...
- 2,210
0
votes
3
answers
82
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 ...
- 2,012
10
votes
3
answers
2k
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 ...
- 213
1
vote
0
answers
99
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 ...
- 163
8
votes
3
answers
3k
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 ...
- 215
3
votes
2
answers
2k
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,
[...
- 4,807
8
votes
1
answer
25k
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 ...
- 533
3
votes
0
answers
886
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,
...
- 41
1
vote
1
answer
62
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,550
5
votes
1
answer
5k
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 ...
- 532
5
votes
3
answers
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.
...
- 549
4
votes
1
answer
1k
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 ...
- 275
2
votes
1
answer
1k
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 then....
- 259
38
votes
2
answers
15k
views
Does the order of columns in a PK index matter?
I have a few very large tables with the same basic strucure. Each one has a RowNumber (bigint) and DataDate (date) column. Data is loaded using SQLBulkImport every night, and no "new" data is ever ...
- 867
0
votes
2
answers
6k
views
Use timestamp(or datetime) as part of primary key (or part of clustered index)
I use following query frequently:
SELECT * FROM table WHERE Timestamp > [SomeTime] AND Timestamp < [SomeOtherTime] and publish = 1 and type = 2 order by Timestamp
I would like to optimize this ...
- 663
2
votes
1
answer
1k
views
Would Mongo's ObjectID work well in InnoDB's clustered index?
Mongo's ObjectID is defined like this:
ObjectId is a 12-byte BSON type, constructed using:
a 4-byte value representing the seconds since the Unix epoch,
a 3-byte machine identifier,
...
- 123
5
votes
3
answers
1k
views
Can I have create an InnoDB table without a PK? If so, would a secondary index be used as the clustered index?
Is it possible to create an InnoDB table without a Primary Key?
Since InnoDB tables are structured as clustered index around the PK, what would be the structure of a table without PK? Would that ...
- 151
17
votes
2
answers
2k
views
Is there any tangible difference between a unique clustered index and a clustered primary key?
I understand that there may be a difference in meaning or intent between the two, but are there any behavioral or performance differences between a clustered primary key and a clustered unique index?
- 1,636