4 ms·

SQL Server Enterprise will also allow you to create a view with somewhat complex joins and conditions, index it, and then other queries can benefit from the ind

SQL Server Enterprise will also allow you to create a view with somewhat complex joins and conditions, index it, and then other queries can benefit from the indexed joins. It’s things like this that keep us paying $millions for SQL Server Enterpise vs. Postgres.

Pxtl 7y ago
I think in all the dozens of times I've tried it I've never had a view pass their draconian requirements for indexing. There's always a self-join or a left join or a sqlclr function or a format or an unsupported aggregate or something else that was a critical thing that I really needed in the index. If the Microsoft SQL Server team really thinks this feature is a critical selling point, maybe they should work more on it since they were introduced back in like SQL Server 2005 and still have the same miserable whips-and-chains-bondage level masochism experience.
hotsauceror 7y ago
Actually the last time I checked, materialized views had a ton of restrictions on them, including things like not being able to use joins. Has that changed in recent versions?
- BrentOzar 7y ago
  You've been able to do joins in them for a decade. You just can't do self-joins. (But there are indeed a ton of restrictions.)
  - hotsauceror 7y ago
    According to this here web site, it also forbids outer joins, which is probably what I was remembering. https://docs.microsoft.com/en-us/sql/relational-databases/views/create-indexed-views?view=sql-server-ver15 https://docs.microsoft.com/en-us/sql/relational-databases/vi...
  - Pxtl 7y ago
    They're basically only usable on a super blue moon lunar eclipse.
jwcacces 7y ago
In postgres you could just make an index on the RIGHT(SomeColumn,3) expression. See https://www.postgresql.org/docs/current/sql-createindex.html https://www.postgresql.org/docs/current/sql-createindex.html > An index field can be an expression computed from the values of one or more columns of the table row. This feature can be used to obtain fast access to data based on some transformation of the basic data. For example, an index computed on upper(col) would allow the clause WHERE upper(col) = 'JIM' to use an index. No need to maintain a separate view or its index
- hotsauceror 7y ago
  That’s actually pretty slick. I’d pay a fair bit of money for SQL Server to have computed indexes like that.
  - marzell 7y ago
    I mean, you could add a new computed column to a table and index that... It's really effectively the same thing, just more visible. Still requires a table scan and storage/maintenance of the index/column. I guess technically it may require additional storage but it's not radically different. Either way, still requires some foresight to implement it before your queries require it so neither is a magic bullet.
    - hotsauceror 7y ago
      Right. What you’d really need would be for the query optimizer to recognize your non-sargable expression and be able to look up a corresponding function index, and then have a mechanism for seeking on that. Indexing a computed column, like you said, wouldn’t actually solve the problem of having to evaluate each row to begin with.
      - marzell 7y ago
        Does a function index somehow eliminate the need for RBAR evaluation? I don't see how it's functionally any different in this context from a computed column
shawnz 7y ago
Isn't an mssql indexed view essentially a materialized view? Postgres also supports indexes on materialized views.
- dragonwriter 7y ago
  An MSSQL indexed view is like a materialized view, except it's not actually materialized beyond the index, as I understand it, and there are restrictions on the view definition that mat views don't have, and it updates automatically.
grumpydba 7y ago
> It’s things like this that keep us paying $millions for SQL Server Enterpise vs. Postgres. Using these millions to better design your applications is better in the long run. You can totally live without those with a good schema and better queries. I think features like column store and always on are worth more that those band-aid features aimed at pleasing ugly ERPs.

Pxtl 7y ago

I think in all the dozens of times I've tried it I've never had a view pass their draconian requirements for indexing. There's always a self-join or a left join or a sqlclr function or a format or an unsupported aggregate or something else that was a critical thing that I really needed in the index. If the Microsoft SQL Server team really thinks this feature is a critical selling point, maybe they should work more on it since they were introduced back in like SQL Server 2005 and still have the same miserable whips-and-chains-bondage level masochism experience.

hotsauceror 7y ago

Actually the last time I checked, materialized views had a ton of restrictions on them, including things like not being able to use joins. Has that changed in recent versions?

BrentOzar 7y ago
You've been able to do joins in them for a decade. You just can't do self-joins. (But there are indeed a ton of restrictions.)
- hotsauceror 7y ago
  According to this here web site, it also forbids outer joins, which is probably what I was remembering. https://docs.microsoft.com/en-us/sql/relational-databases/views/create-indexed-views?view=sql-server-ver15 https://docs.microsoft.com/en-us/sql/relational-databases/vi...
- Pxtl 7y ago
  They're basically only usable on a super blue moon lunar eclipse.

jwcacces 7y ago

In postgres you could just make an index on the RIGHT(SomeColumn,3) expression. See https://www.postgresql.org/docs/current/sql-createindex.html https://www.postgresql.org/docs/current/sql-createindex.html > An index field can be an expression computed from the values of one or more columns of the table row. This feature can be used to obtain fast access to data based on some transformation of the basic data. For example, an index computed on upper(col) would allow the clause WHERE upper(col) = 'JIM' to use an index. No need to maintain a separate view or its index

hotsauceror 7y ago
That’s actually pretty slick. I’d pay a fair bit of money for SQL Server to have computed indexes like that.
- marzell 7y ago
  I mean, you could add a new computed column to a table and index that... It's really effectively the same thing, just more visible. Still requires a table scan and storage/maintenance of the index/column. I guess technically it may require additional storage but it's not radically different. Either way, still requires some foresight to implement it before your queries require it so neither is a magic bullet.
  - hotsauceror 7y ago
    Right. What you’d really need would be for the query optimizer to recognize your non-sargable expression and be able to look up a corresponding function index, and then have a mechanism for seeking on that. Indexing a computed column, like you said, wouldn’t actually solve the problem of having to evaluate each row to begin with.
    - marzell 7y ago
      Does a function index somehow eliminate the need for RBAR evaluation? I don't see how it's functionally any different in this context from a computed column

shawnz 7y ago

Isn't an mssql indexed view essentially a materialized view? Postgres also supports indexes on materialized views.

dragonwriter 7y ago
An MSSQL indexed view is like a materialized view, except it's not actually materialized beyond the index, as I understand it, and there are restrictions on the view definition that mat views don't have, and it updates automatically.

grumpydba 7y ago

> It’s things like this that keep us paying $millions for SQL Server Enterpise vs. Postgres. Using these millions to better design your applications is better in the long run. You can totally live without those with a good schema and better queries. I think features like column store and always on are worth more that those band-aid features aimed at pleasing ugly ERPs.