A few days ago I did a presentation on SQL Server. This probably sounds a little strange given my status as an Oracle specialist – but the nice people at Microsoft asked me if I would contribute to one of their seminars so I downloaded and installed the 180 day free trial copy of the Enterprise version, then downloaded the “Books Online” manuals and started to play.
It was an interesting experience – and I think the audience (and organisers) found my presentation useful. The title was “What the Enterprise needs in an RDBMS” – and that’s something I do know about – and the presentation was about whether or not you could find everything you needed in SQL Server 2008, where you’d have to look in the manuals, and supplementary questions you’d have to ask.
The biggest problem I found was searching the manuals for things I knew would be there. For example, SQL Server 2008 does read-consistency so it has to generate the equivalent of undo – but could I find that as a keyword in the manuals ? (The correct search term is “version store”.)
Anyway – while working through SQL Server 2008 I came across a few really cute things that I’d like to see in Oracle, so I thought I’d list them here:
Filtered indexes – I know it’s something I can do with function-based indexes and decode() or case, but in SQL Server 2008 you can create an index with a predicate, e.g. create an index which references only the rows where status = ‘A’. Which means you can created an exquisitely tailored, minimalist index without changing the supplied code.
Filtered statistics – even if I don’t want to create an index there may be some very popular predicates that frequently appear along with a number of other predicates – rather than creating lots of “extended statistics” (11g – and limited to equality predicates anyway) it would be nice to be able to create statistics for just those rows where, say, status = ‘A’. This could produce a lot of extra stats entries for columns, of course, but perhaps these could be limited to the columns which appeared in sys.col_usage$.
“Included Columns” - you can add non-key columns indexes to allow the optimizer to resolve queries within the index. (If you have enough columns to do this, the index is often called a “covering” index. ) Of course, you can do this with Oracle – but there’s an important difference. In Oracle the extra columns are treated as part of the key and this is likely to affect the clustering_factor of the index, possibly making the index seem undesirable for other queries where the included columns were irrelevant or insufficient.
“Optimize for” hint – there is a hint that overrides bind peeking (or parameter sniffing, as I think SQL Server calls it). You can say option (optimize for (@bind1 = ‘LONDON’ @bind2 unknown)) to tell the optimizer to optimize as if bind variable (parameter) bind1 was set to ‘LONDON’ and bind2 was not peekable. What a great way to deal with the problems of instability caused by bind variable peeking.
sp_create_plan_guide – I know you can now create profiles by hand (and could hack stored outlines long ago) in Oracle, but in SQL Server 2008 it’s easy, definitely legal, and approved of. Update: I had forgotten that the Optimizer Development Group had published a note about SQL Plan Management in 11g showing how you could create an SQL Plan Baseline for one piece of SQL text by pulling the execution plan of another piece of SQL text from memory.
date_correlation_optimization – there’s an option to optimize for correlation between date columns in different tables. The tables have to be joined by a single column foreign key – and there are some other restrictions as well, but you can appreciate that there are some cases where assuming correlation between dates (joining orders to deliveries, for example) is a very good idea. It could be quite hard to implement this one, and could be quite costly to collect the statistics – but it’s a really nice idea. Update: I’ve had a look at how this is done in SQL Server and it’s not nice: essentially it uses something that an Oracle user would recognise as an ‘on commit refresh’ materialized view; the overheads are likely to be significant and there will be concurrency issues in busy systems.
I haven’t dumped my SQL Server virtual machine yet and I’ve already been asked by two different people if I’d be prepared to repeat the presentation, so there may be more wish-list items to come.