Hot answers tagged clustered-primary-key
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
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 ...
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
