Questions tagged [clustered-primary-key]

The tag has no usage guidance.

0
votes
0answers
92 views

Create a clustered primary key on partitioned table

I have a table that has been partitioned recently based on a datetime column. The same table had a primary key (clustered) on the ID column. During the partitioning of the table, the primary key is ...
2
votes
2answers
51 views

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 ...
0
votes
1answer
43 views

Find minimum number of columns that identify uniqely all rows

I'm using SQL Server Management Studio. I've a table and I want to find a Primary Key for this table. Can I write a script in T-SQL that finds which columns (and I want that the number of columns is ...
1
vote
0answers
56 views

Any performance gains from having identical clustered and nonclustered indexes on table on different filegroups?

I am attempting to rearchitect our MSSQL database such that we have two filegroups, one for data, on a 15K HDD, primary, and one for indexes on a separate SSD, indexes, in order to realize some ...
1
vote
2answers
163 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 ...
4
votes
2answers
4k views

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 ...
1
vote
1answer
59 views

refreshing 500k rows per day, what primary key to use? [closed]

Usually I create a PK of type int/bigint for every table. But in this case I have some doubts. I have a table which is updated many times a day, the total number of rows is around 500k, but they get ...
2
votes
1answer
574 views

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, ...
2
votes
2answers
537 views

MySQL - Optimal indexing for a lookup table. HASH index, BTREE index or composite PK?

Let's say I have a very large lookup table that looks like this: CREATE TABLE `MyLookup` ( `FKToTableA` bigint(20) NOT NULL, `FKToTableB` bigint(20) NOT NULL, `Count` bigint(20) ...
1
vote
0answers
43 views

What type of primary key we need to use for specific length of bits in Cassandra?

I want to know in which situations which type of primary key I need to use. Situations: 1. If I need 64 bit key than is bigint is the best for it? If I will chose text type will performance will be ...
1
vote
1answer
857 views

SQL Server query problem when selecting data from child table based on column in parent table

I am using MS SQL Server 2008 using Management Studio. I am running a query(below) to extract dates and value based on the 'ticker' column from the parent table, the attribute column (child) , and ...
0
votes
1answer
422 views

How does InnoDB represent composite integer keys?

Let's say I have the following table: Create Table Orders(server : tiny int, ticket : integer, primary key (server, ticket)); How is the composite primary key representation when stored on disk? ...
0
votes
2answers
123 views

Deleting rows from table where values less than an aggregate value

I'm attempting to remove duplicates from a table. CREATE TABLE media_views ( `companyid` INT (11) NOT NULL, `channel` INT(11) NOT NULL, `date` DATE NOT NULL, `views` INT(11) NOT NULL DEFAULT ...
1
vote
1answer
118 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 ...
3
votes
0answers
456 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, ...
4
votes
3answers
785 views

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 ...
1
vote
0answers
22 views

Can I include extra columns in a m->m mapping table? [duplicate]

I guess this is sort of an odd question, but consider this scenario. table classes (id_pk) table students (id_pk) table students_classes_map (students_id_fk_pk, classes_id_fk_pk, finalExamScore ...
1
vote
2answers
795 views

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 ...
3
votes
2answers
3k views

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 ...
1
vote
1answer
289 views

Should I use a surrogate key in a mapping table?

I am deploying a mapping table which connects Items to ItemGroups Though an Item will only ever be in a single ItemGroup please accept a priori it is not appropriate in my use case to append a column ...
4
votes
2answers
268 views

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 ...
2
votes
2answers
228 views

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, ...
7
votes
2answers
206 views

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 ...
1
vote
1answer
197 views

Impact of miss-ordered fields in a composite, clustered primary key?

I have tables where the primary key is something like this: -StoreID -BusinessDate Typically, there is one row for every store (~100) for every single day. One row for each store is inserted on a ...
2
votes
1answer
565 views

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 ...
7
votes
2answers
2k views

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 ...