Tag Info

Hot answers tagged

12

In short, no. A primary key by definition requires uniqueness, an index on the primary key field is the database engines route to enforcing this constraint. From BOL: When you specify a PRIMARY KEY constraint for a table, the Database Engine enforces data uniqueness by creating a unique index for the primary key columns. This index also permits fast ...


8

No, a primary key constraint is always enforced in SQL-Server by a unique index. The index may be clustered or unclustered. If you don't specify which type, default is CLUSTERED for the primary key. From MSDN documentation, CREATE TABLE: CLUSTERED | NONCLUSTERED Indicate that a clustered or a nonclustered index is created for the PRIMARY KEY or ...


4

Your approach is correct and pretty much the only way, but I would consider using a tool like Red Gate to help me. drop all FKs without scripting them change the indexes (as above) generate an "FK only" script using the compare tool to re-apply the FKs The 3rd step saves writing something to script our your FK definitions, or fiddling with SSMS/SMO


3

(Moving discussion to answer) First of all, you have to ask yourself: Why go after GUID first (if at all)? You could end up spending a lot of time rearranging tables and gain no benefit at all. What do we know: You state that the queries are generated by NHibernate. This indicates that you are an OLTP system. Your major waits are CXPACKET and PAGEIOLATCH ...


3

Option 5: (ItemGroupId, ItemId) (note the order) as the primary key (clustered) Unique constraint (non-clustered) on ItemId While searching by ItemId is not conducive to range scans (yet?), searching by ItemGroupId is 1, which makes it a good candidate to be the leading column of the clustered index key. My normal design for this type of table is to ...


2

The relative cost of the components of an execution plan is not always reliable. The INSERTED scan or deleted scan shouts TRIGGER to me. This is your problem most likely. Are you looping over 400 rows and sending an email in an extreme example?


2

I'm not a SQL Server guy, but I know that in MySQL you can temporarily disable foreign key checks. I did a quick google on whether it was possible to do this in SQL Server, and came up with this link: http://stackoverflow.com/questions/159038/can-foreign-key-constraints-be-temporarily-disabled-using-t-sql Summary from the answer: -- disable all ...


2

I have had great success with overall perfomance gains by moving the clustered index to an identity, and leaving the GUID as the PK. If you have a GUID as a clustered PK, you are telling SQL to physically sort the data randomly, leading to page splits and fragmentation in the main table as well as in the non-clustered indexes. By getting your database on ...


1

Store the tenant_id first. When you do this you can enable index key compression. See http://docs.oracle.com/cd/B28359_01/server.111/b28310/indexes003.htm#i1106790 for the syntax and http://docs.oracle.com/cd/B28359_01/server.111/b28318/schema.htm#i14618 for the concepts. In your case, you can do it like this: create unique index mytable_idx on ...


1

One thing you need to keep in mind is what you are using PK for. At the logical level (sometimes called the conceptual level), the primary key is simply one of the candidate keys, chosen somewhat arbitrarily. The purpose is to guarantee that each row is unique, that each row has an identifier, and that no part of an identifier is left out (NULL). For ...


1

I know that reindexing the PK will lock the table, so I was wondering what best practice for this operation is? Well having some DB file size/growth and free disk space monitoring would be good so this situation never happens. As you are in this situation I think the plan you have outlined is your only choice until you get more disk space and you really ...


1

I think that better indexes for these queries are these (which you don't have on the tables now): (player_id, day_id) and (target_id, day_id) The optimizer choosing different indexes in the two situations has probably to due with table sizes and selectivity of the available indexes. I suggest you add the two indexes above and check (and compare) the new ...


1

The order of columns in a primary key does not affect the insert performance, since the combination of the values in the two columns of the primary key are meant to be unique. However the order of index impacts the performance of SELECT queries. In the new order you will have a greater and increasing range of values (dates) across which the index is ...



Only top voted, non community-wiki answers of a minimum length are eligible