Tag Info

Hot answers tagged

47

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


37

This has been asked in SO here and here Jeff's this post will explain alot about pros and cos of using GUID. GUID Pros Unique across every table,every database, 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 ...


28

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: event=# SELECT version(); version ------------------------------------------------------------------------------------------- ...


26

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


25

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


20

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 modified? A: Yes. Use WITH (DROP_EXISTING=ON): create table Friend ( ...


19

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


16

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


16

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


15

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


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


14

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

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


12

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


11

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


10

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


10

All a primary key is is a value that we have determined is the value that is of utmost importance in a record. Whether that key is a signed int, an unsigned int, a string, a blob (actually, there are limits) or a UUID (or whatever name it takes today), the fact still stands that it is a key, and that it is the thing of utmost importance. Since we're not ...


10

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


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


10

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


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


9

In your case these fields are natural key. Surrogate Key: Surrogate keys are keys that have no “business” meaning and are solely used to identify a record in the table. Such keys are either database generated (example: Identity in SQL Server, Sequence in Oracle, Sequence/Identity in DB2 UDB etc.) or system generated values (like ...


9

If you want to lookup your data, you really want to do this based on an integer field or fields. This is why many people use an ID field for this. But if you have a table you use for a many-to-many relation, it isn't really needed. Lets say you have the following two tables: Table news id integer title varchar item text Table tags id integer name varchar ...


9

You probably don't want to use rules and instead use constraints, in this case a check constraint. The reason that you don't want to use rules is that rules have been deprecated means that they will be removed from SQL Server at some point in the future so it would be better to use the check constraint instead. Something like this will do the trick. ...


9

You should never assume that a data point which is outside of the control of your system will never change. This means you shouldn't assume student names won't change. There are lots of reasons in the real world why names might change. Anything that is at reasonable risk of changing is a bad candidate for a primary key. Also, names are very unlikely to ...


8

There is no significant disadvantage using the natural key as the clustered index there are no non-clustered indexes no foreign keys referencing this table (it is a parent row) The downside would be increased page splits as data inserts would be distributed throughout the data, instead of at the end. Where you do have FKs or NC indexes, the using a ...


8

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;


8

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


8

Many people have heard guidance that "sequential scans are bad" and seek to eliminate them from their plans, but it isn't so simple. If a query is going to cover every row in a table, a sequential scan is the fastest way to get those rows. This is why your original join query used seq scan, because all rows in both tables were required. When planning a ...



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