3 ms·

Anekdote: Recently fixed an issue for performance ( MS SQL ) by just using "With INDEX index-name". Very small PR actually. I couldn't write an blog post abou

• magicalhippo 5y ago
  I mentioned it in an earlier thread, but why can't we just select from indexes directly as if they were tables or views? I mean sure, most of the time the DB is clever. But sometimes the developer knows best, why not provide the tools? select i.OrderNumber, i.OrderDate, o.OrderShipped from ix_Orders_OrderDate i join Orders o on o.OrderNumber = i.OrderNumber where i.OrderDate > '2022-02-01'
  • electroly 5y ago
    Index names aren't global; they are table-scoped. So you have to specify the table name. If you squint your eyes, there's not much difference between typing "ix_Orders_OrderDate i" and typing "Orders i WITH (INDEX(ix_Orders_OrderDate))". SQL Server lets you do this.
    • jeltz 5y ago
      No, index named are global in PostgreSQL and the SQL standard.
      • electroly 5y ago
        The ANSI SQL standard doesn't talk about indexes at all. I'm sure you're right about PostgreSQL; I'm no expert on that implementation. I took his post to be asking about SQL Server since that's what we were talking about in the subthread (hence my SQL Server-specific response). In SQL Server indexes are table-scoped.