The tag has no usage guidance.

learn more… | top users | synonyms

0
votes
0answers
47 views

PK Guid, Clustered Indexes and Fragmentation

I need your help solving a db headache. I'm using Entity Framework as a ORM to access my database (SQL Server Azure V12), the entity model uses Guid as PK and by default SQL Server has clustered ...
0
votes
1answer
53 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
70 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
82 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
73 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] ...
3
votes
3answers
250 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
20 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
215 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
1k 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 ...
4
votes
2answers
225 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 ...
1
vote
1answer
111 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 ...
2
votes
2answers
186 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, ...
6
votes
2answers
191 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 ...
0
votes
1answer
190 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 ...
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 ...
2
votes
2answers
526 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 ...