New answers tagged

2

If there is any possibilty of avoiding GUID as Primary Key / Unique Clustered Index, then take this option! A good (surrogate-) key should be: Narrow (NO) Unique (GUID is) Static (GUID is) Ever Increasing (NO) GUID (uniqueidentifier) needs 16 bytes - int (numbers up to 2^31 - 1) needs 4 bytes, bigint (numbers up ot 2^63 -1 which is far more than you ...


2

It depends. Using a normal UUID as your clustering key will result in greater fragmentation in the table and so waste disk space and memory. You can mitigate this mostly with NEWSEQUENTIALID if that fits your use of the table, or of course you could use some other key as the clustering key (specify NONCLUSTERED when defining the primary key and create ...


3

It will slow down inserts rather than queries. That's because it is essentially inserting a record somewhere randomly inside the existing records, instead of just whacking it on the end (as it would if you were using an identity or sequential dates). You can somewhat reduce this by using NEWSEQUENTIALID instead of NEWID (assuming the GUID is generated by ...


2

I always take careful consideration when creating indexes on production systems, always try to create in DEV first. There are several reasons, but the top three for me are. First, there is a cost associated to indexes, essentially more overhead, so depending on your table the cost can be quite high. Second, I am never always privy to all the functionality ...


-1

AFAIK, this will (possibly) only affect INSERT/UPDATE tasks on the table, and rebuilding of indexes. I would confirm that any INSERT/UPDATE Tasks (Procedures) still run efficiently.


13

Unless you explicitly state a desired order using an ORDER BY clause you can not guarantee the order that data will be presented in response to a query. Without an ORDER BY clause the engine is free to present data to you in any order it finds most convenient at the time, which can mean a different order for the same query you ran earlier. If there is a ...


1

How indexes work in InnoDB. This discussion is limited to InnoDB. (Let me establish some ground work, then I will get to your question.) The PRIMARY KEY is a BTree where the leaf nodes contain all the columns of the row. That is, the PK and the data coexist in the same BTree. Each secondary key is a BTree where the leaf nodes contain a copy of the ...


5

For a start unless this was an absolute emergency you have done everything backwards: you should not make changes to production that have not been made in dev/test first to make sure they have the desired effect. Develop, test, then release the changes to production. Setting that aside: There are a number of tools out there that claim to compare schema, and ...


2

The following is written for SQL Server, but should be quite the same with other RDBMS... The clustered index is physically sorted on the storage media and will cover all columns of your table - as if they were include columns. A good clustered index is bound to a column with an implicit sort quality, such as insertDateTime or a running number (e.g. with ...


1

If a table is large and growing fast, clustered index might become too expensive to maintain since the DB server has to reshuffle all the data while rebalancing the tree, not only nodes with key values. It might significantly affect speed of data modification queries. Clustered index stores table data right in the index (a tree node contains the whole ...



Top 50 recent answers are included