Hacker News Reader

Comment by taspeotis | Hacker News Reader

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.

View on HN

In postgres you could just make an index on the RIGHT(SomeColumn,3) expression.

See 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

That’s actually pretty slick. I’d pay a fair bit of money for SQL Server to have computed indexes like that.

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.

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.

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

I don’t know how postgresql does it, but I’m interpreting it as yes, their engine has a way to actually replace nonsargable expressions in the query plan itself, with some other operation that performs a seek against the index on the filter. Otherwise you’re right, you’d still need an RBAR evaluation, which is really the crux of the issue. I wish they’d reframe this discussion as RBAR instead of sargability, because it covers so many more sins...

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.

> 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.

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

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.

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?

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.)

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/vi...

They're basically only usable on a super blue moon lunar eclipse.

Reply on news.ycombinator.com