>This is true, but I believe if you have an index-organised table then subsequent indexes would have to reference the primary key. With PostgreSQL, indexes can effectively point to the record in the heap by using the block number and item pointer in the block. This should mean, in theory, index lookups are faster in PostgreSQL than non-clustered index lookups in SQL Server.
Yes this is a tradeoff, if you primarily access by the clustered index its faster, if you access by secondary it slightly slower.
Clustered indexes work very well for tables that have a single access pattern, they can also save significant space and I/O since data is not copied in both heap and index.
Having the choice is great, Postgresql forces heaps and SQLite forces clustered, MSSQL lets you do either based on your design choice.
>I was wondering, is there a concept of Bitmap Index Scans in SQL Server?
Yes this is a tradeoff, if you primarily access by the clustered index its faster, if you access by secondary it slightly slower.
Clustered indexes work very well for tables that have a single access pattern, they can also save significant space and I/O since data is not copied in both heap and index.
Having the choice is great, Postgresql forces heaps and SQLite forces clustered, MSSQL lets you do either based on your design choice.
>I was wondering, is there a concept of Bitmap Index Scans in SQL Server?
Yes you can see the description here: https://learn.microsoft.com/en-us/sql/relational-databases/s...