6 ms·
From a development perspective clustered indexes and SSDT are the best features. Clustered indexes vastly improve query performance on tables where you often wa
by sitharus 10y ago
From a development perspective clustered indexes and SSDT are the best features. Clustered indexes vastly improve query performance on tables where you often want the data by something other than insert order. SSDT lets you version your database in a really easy way and generates change scripts on demand.
- snuxoll 10y agoYou can optionally cluster a table based on index order with PostgreSQL, simply create the index and then `ALTER TABLE foo CLUSTER ON bar_ix`. Clustered indexes are actually an issue I run into with SQL server, because, like MySQL, the table data is always indirectly referenced by the clustered index, so any query that uses a secondary index requires a second pointer deref to find the data in the table heap. This has a benefit to insert/update performance, but I'd argue that more tables are higher on reads than mutations.
- dx034 10y agoYou can use INCLUDE to put some columns in the index. Doesn't always help but speeds up many queries. AFAIK Postgres doesn't have that feature, not sure about MySQL.
- snuxoll 10y agoUsing INCLUDE comes at the cost of bloating your indexes making scans take a considerable longer time, not having to chase that extra pointer works well enough in most cases (and if you are able to benefit from Heap-Only Tuples you can even have in-place UPDATES without needing to rewrite the pointer in every index).
- dhd415 10y agoSQL Server supports heaps (tables w/o clustered indexes) on which indexes point directly to the referenced row. It's nice that they allow you to choose between those and tables with clustered indexes where any secondary index references the primary key. See https://msdn.microsoft.com/en-us/library/hh213609.aspx https://msdn.microsoft.com/en-us/library/hh213609.aspx