Hot answers tagged

84

The question is not 'when should the PK be NC', but instead you should ask 'what is the proper key for the clustered index'? And the answer really depends on how do you query the data. The clustered index has an advantage over all other indexes: since it always includes all columns, is always covering. Therefore queries that can leverage the clustered index ...


51

This has been asked in SO here and here. Jeff's post explains a lot about pros and cons of using GUID. GUID Pros Unique across every table, every database and every server Allows easy merging of records from different databases Allows easy distribution of databases across multiple servers You can generate IDs anywhere, instead of ...


41

Here are the results of querying a table on the second column of a multicolumn index. The effects are easy to reproduce for anybody. Just try it at home. I tested with PostgreSQL 9.0.5 on Debian using a medium sized table of a real-life database with 23322 rows. It implements the n:m relationship between the tables adr (address) and att (attribute), but ...


33

1 - It's faster. A JOIN on an integer is much quicker than a JOIN on a string field or combination of fields. It's more efficient to compare integers than strings. 2 - It's simpler. It's much easier to map relations based on a single numeric field than on a combination of other fields of varying data types. 3 - It's data-independent. If you match on ...


31

Does the order of columns in a PK index matter? Yes it does. By default, the primary key constraint is enforced in SQL Server by a unique clustered index. The clustered index defines the logical order of rows in the table. There may be a number of extra index pages added to represent the upper levels of the b-tree index, but the lowest (leaf) level of ...


31

You are obviously suggesting that CONSTRAINTs in a database should be enforced by the application(s) that/which access that database? There are many reasons why this is a bad (bad, bad...) idea. 1) If you are building a "roll-your-own" constraint "engine" (i.e. within your application code), then you are merely emulating what Oracle/SQL ...


28

Note: as of Azure SQL Database v12, these restrictions no longer apply. The is no such thing as a 'primary index'. There is such a thing as a 'primary key' and also there is such a thing as a 'clustered index'. Distinct concepts, often confused. With this distinction in mind, lets revisit the question: Q1) Can the clustered index in a SQL Azure table be ...


25

I'm going to say no, not always, but most of the time yes.. These are some circumstances in which you don't need a surrogate or artificial key: Pure intersection tables. If there is no risk of the intersection being the target of a foreign key and if there is little or no risk of the intersection attracting independent attributes (i.e. something other ...


23

It depends on your generation function and size of the final tables GUIDs are intended to be globally unique identifiers. As discussed in the Postgres 8.3 documentation there are no methodologies that are universally appropriate to generate these identifiers, but postgreSQL does ship with a few more useful candidates. From the scope of your problem, and ...


20

re 1) Yes and no. For a query that uses both columns e.g. where (user_id1, user_id2) = (1,2) it doesn't matter which index is created. For a query that has a condition on only one of the columns e.g. where user_id1 = 1 it does matter because usually only the "leading" columns can be used for a comparison by the optimizer. So where user_id1 = 1 would be ...


20

Like you said. A FOREIGN KEY constraint referencing the same table is typically for a hierarchy structure and it would use another column to reference the primary key. A good example is a table of employees: EmployeeId Int Primary Key EmployeeName String ManagerId Int Foreign key going back to the EmployeeId So in this case there is a ...


17

It Depends on your engine. Common wisdom is that reads are cheap, a few bytes here and there will not significantly impact the performance of a small to medium size database. More importantly, it depends on the uses to which you will put the primary key. Integer serials have the advantage of being simple to use and implement. They also, depending on the ...


17

Because people have learned from experience that using such fields leads to problems. I've developed database applications for 20 years. Most critically I spent five years working with data warehouses. In the early days choosing another field seemed ok. Then we found duplicate records, sometimes unique validations were missing, sometimes (frequently) ...


17

One more advice - never use GUIDs as part of clustered index. GUIDs are not sequential, thus if they are part of clustered index, every time you insert new record, database would need to rearrange all its memory pages to find the right place for insertion, in case with int(bigint) auto-increment, it would be just last page. Now if we look to some db ...


16

If you're synchronizing your data with an external source, a persistent GUID can be much better. A quick example of where we're using a GUIDs is a tool that is sent to the customer to crawl their network and do certain classes of auto-discovery, store the records found, and then all the customer records are integrated into a central database back on our end. ...


16

A Super Key is simply a non-minimal Candidate Key, that is to say one with additional columns not strictly required to ensure uniqueness of the row. A Primary Key is a minimal Candidate Key, which is to say all constituent columns are strictly required in order to ensure uniqueness. As a database developer/designer of 30 years experience, I had never even ...


15

Primary keys (and other unique constraints) are implemented as indexes, and are dealt with in exactly the same way - it doesn't make sense from the programmer's point of view to have separate code paths for PKs and indexes (it would double up the potential for bugs). Other than being referred to by foreign keys, a PK is just a unique constraint which is in ...


15

The answer to your question is logical, not physical - the value you look up might change for business reasons. For example, if you index your customers by email address, what happens when an email address changes? Obviously this won't apply to all your lookup tables, but the benefits of doing it the same way across the entire application is that it makes ...


13

One main difference is that the unique index can have a NULL value that is not allowed in the primary key. Clustered or not, this is the main difference between the practical implementation of a Primary Key versus a Unique Key. Oh, and the fact that a table can have one PK and many UK :-). These are both differences in INTENT not in PERFORMANCE. Otherwise, ...


12

A table can have at most one PRIMARY KEY constraint but it can have as many as you want UNIQUE KEY constraints. Columns that are part of the PRIMARY KEY must be defined as NOT NULL. That is not required for columns that are part of UNIQUE KEY constraints. If the columns are not Nullable, then there is no difference between Unique and Primary Keys. Another ...


11

"it depends" Yes: Surrogate IDENTITY/AUTONUMBER fields are good when the natural key is wide and non-numeric. Note: this assumes the conflation of "PK" and clustered index that occurs by default in SQL Server and Sybase etc No: many/many tables when the 2 parent keys suffice. Or when the natural key is short and fixed length eg currency code Of course, a ...


11

The basic reason to use Clustered indexes is stated on Wikipedia: Clustering alters the data block into a certain distinct order to match the index, resulting in the row data being stored in order. Therefore, only one clustered index can be created on a given database table. Clustered indices can greatly increase overall speed of retrieval, but usually ...


11

The composite primary index will always ensure that the combination of col1, col2, col3 values are unique. If the purpose of the index is to ensure that the values are unique then you will have achieved that A composite index also provides for index matches on any combination of the columns col1, col2, and col3 You would create separate indexes on col2, ...


11

how can i use multiple primary keys in postgres ? You can't. It's an oxymoron - the definition of a primary key is that it's the primary key, singular. You can't have more than one. You can have multiple unique constraints. You can have a primary key that contains multiple columns (a composite primary key). But you can't have more than one primary key ...


11

Email is a particularly bad choice for any PK whether composite or single. See my answer on this question on Stack Overflow for why: http://stackoverflow.com/questions/3804108/is-email-address-a-bad-primary-key/3804174#3804174


11

You have the hobt_id so the following query will identify the table:- SELECT o.name FROM sys.partitions p INNER JOIN sys.objects o ON p.object_id = o.object_id WHERE p.hobt_id = 72057632651542528 From that you can then run the following statement to identify the row in the table (if it still exists):- SELECT %%LOCKRES%%, * FROM [TABLE NAME] ...


10

The distinction is mainly historical. The relational model was developed in part in response to the way IMS handled data. The presently released version of IMS provides the user with a choice for each file: a choice between no indexing at all (the hierarchic sequential organization) or indexing on the primary key only . . . Source: A Relational Model ...


10

Acceptable? Sure. Common? No. Beneficial? Doubtful. At my old job we inherited a system where they had a central sequence generator (this was a SQL Server system long before SEQUENCE was introduced in SQL Server 2012). It wasn't really a performance bottleneck and shouldn't be unless you're generating hundreds of thousands of values per second. But it made ...


10

You'd use ALTER TABLE to add the primary key constraint. In Postgres you can "promote" an index using the "ALTER TABLE .. ADD table_constraint_using_index" form Note, the index need to be unique of course for a primary key ALTER TABLE my_table ADD CONSTRAINT PK_my_table PRIMARY KEY USING INDEX my_index;


10

For a non partitioned table I get the following plan There is a single seek predicate on Seek Keys[1]: Prefix: DeviceId, SensorId = (3819, 53), Start: Date < 1339225010. Meaning that SQL Server can perform an equality seek on the first two columns and then begin a range seek starting at 1339225010 and ordered FORWARD (as the index is defined with ...



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