Database Administrators Stack Exchange
Database Administrators Stack Exchange

Questions tagged [clustered-primary-key]

34 questions
8
votes
2 answers

Autoincrement vs composite primary key for a big table on innodb

I have a had a quite lengthy discussion with Rick James on this this we came out with idea of having composite key to replace the autoincrement pk where the int is limited close 2 billion. My table will reach this limit in few months easily as…
user8012596
  • 227
  • 1
  • 4
  • 9
7
votes
4 answers

Should I convert the frontend generated UUIDv6 to binary(16) for use in SQL Server as the clustered primary key?

Background As suggested by the front-end developer, I looked into using UUID as the primary key for a bunch of tables in our new system. From learning the pros and cons of random vs. sequential UUIDs, to the use of a non-clustered primary key in…
oopoopoop
  • 73
  • 5
7
votes
2 answers

In SQL Server, is it possible to have a PRIMARY KEY on a table without either CLUSTERED or NONCLUSTERED indexes on the same key?

In Sql Server (2008), is it possible to have a PRIMARY KEY on a set of columns without either CLUSTERED or NONCLUSTERED indexes on the same set of columns? I am aware of the fact that PRIMARY KEY and CLUSTERED INDEX key are separate concepts and…
UB01
  • 947
  • 2
  • 9
  • 18
7
votes
2 answers

How to convert primary keys into clustered primary keys for a great number of tables keeping referential identity

After discovering, that I better change the primary keys from non clustered to clustered. Cf this question about clustered indexes on identity columns, the next step is how to do this in a prcatical way. I was surprised that I didn't found this…
bernd_k
  • 12,369
  • 24
  • 79
  • 111
4
votes
2 answers

removal of GUIDS used for all PK's, FKs & Clustered indexes

Inherited control over a database that has this nasty config. It also has large sections of code generated by NHibernate, including the gneeration of GUIDs before they get to the db so no chance of using NEWSEQUENTIALID() either. Obviously changing…
DamagedGoods
  • 2,591
  • 6
  • 34
  • 48
4
votes
3 answers

Is Primary Key Non Clustered recommended?

I am designing a database table. Columns are RouteId ,Origin , Destination. RouteId is a string, it is always unique but length will be more than 60 characters. Since RouteId is unique, I am thinking to make it Primary key and since the size is…
Rupesh
  • 143
  • 1
  • 5
3
votes
2 answers

Reindexing Clustered Primary Key

We need to reindex a Clustered Primary Key on quite a large table. Here is the definition of the table: Data Size: 22GB Index Size: 34GB Total Non-Clustered Indexes: 3 Row Count: 54 Million Data File Free Space: Minimal Drive Free Space: 3GB The…
aaroncatlin
  • 457
  • 5
  • 15
3
votes
0 answers

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, CONSTRAINT PK_UserGroups PRIMARY KEY CLUSTERED…
Gareth Webb
  • 41
  • 4
2
votes
2 answers

Composite Primary Key column order

How would you do the ordering of the PK columns in this case (Oracle 11gR2 Standard Edition): I have a database where I offer the same application to many users. I want to be perfectly sure that all tenants have separated data and that under no…
Blama
  • 151
  • 8
2
votes
1 answer

Clustered monotonically increased index insert performance

I have a table with field Id (bigint, IDENTITY) as primary key and clustered index on it. I inserted 400 rows and saw execution plan. I got: the relative query cost for this insertion 36% and for this query "Clustered Index Insert"'s cost 97%.…
garik
  • 6,782
  • 10
  • 44
  • 56
2
votes
2 answers

What can I do to make mysql use the expected indices?

What can I do to make mysql use the expected indices? I've got 4 tables, two containing resources, and the others containing historical changes. One pair uses indexes correctly, the other doesn't, but both are structured almost identically. I've…
Jason Pascoe
  • 123
  • 3
2
votes
1 answer

Change Tracking and Primary keys

I have a SQL Server database with Change Tracking enabled on a hand full of tables and it's used by another application on the cloud to sync data between both databases. When an existing customer record in SQL Server needs to be synced to the cloud,…
MindDData
  • 123
  • 4
2
votes
5 answers

Is a unique index required for a sequential (autoincrementing) id?

If a sequential AUTO_INCREMENT column is not also the primary key of a MySQL table, does it have to have its own UNIQUE index to ensure long-term data integrity? I'm working on reindexing a particular MySQL table to speed up certain very common…
O. Jones
  • 583
  • 3
  • 15
2
votes
2 answers

Any benefit in running ANALYZE after bulk insert in table with only PRIMARY KEY index in SQLite?

If I create a table in SQLite that has only the PRIMARY KEY index, is there any role for running ANALYZE after a bulk insert, or will that key automatically have been kept up-to-date by necessity? In this case, I am inserting all data at once, and…
carbocation
  • 123
  • 4
2
votes
1 answer

Optimize a UNIQUE CONSTRAINT to make SELECT faster

I have a database on SQL Server 2012 SP3 with this table: CREATE TABLE [dbo].[Code] ( [CodeId] INT IDENTITY (1, 1) NOT NULL, [Serial] NVARCHAR (20) NOT NULL, [AggregationLevelId] TINYINT NOT NULL, …
VansFannel
  • 1,873
  • 5
  • 23
  • 36
1
2 3