So for starters as it the SQL doesn't support all the cool things that SQL Server can do, partly since this is a service focused on a different approach, and has only been around for a few years, so the level of maturity isn't quite there for some of the functions you would expect basic SQL to do. However you can create stored procedures and user defined functions that can fill some of that gap.
The main wall I hit was that it can't use and alias a SELECT statement as a join. Which would have come in handy. So for example the sub-query such as the below is valid.
It will return just the 'TotalSaleRevenue' result and not the 'NumberOfSales' item. Performance wise on the first run there was a small delay, I suspect it's not really optimised well for that sort of thing. But as the equivalent Query Execution Plan in SQL Server and how it will logically process the execution isn't available.
But not the following isn't
Which is a bit of a bugger as it would have worked well to join some results together. I've tried every trick in my bag (and it's a big bag) but no, it will not support it.
So currently where your items that could be thematically the same, but have slightly different predicates in the 'WHERE' clause you'll need to use a separate query, which isn't to back as Cosmos DB is very fast.
As I've been hanging out with the cool applications development team, (Increasing the average age, and hopefully IQ too), I feel the need to Instagram my lunch.
