2. Vertical queries (in preview): A vertical elastic query is a query that is executed across databases that contain different schemas and different data sets. An elastic query can be executed across any two Azure SQL Database instances. This is actually really easy to set up and that what this blog post is about! The diagram below represents a query being issued against tables that exist in separate Azure SQL Database instances that contain different schemas.
Read on to learn how to implement vertical elastic queries today.
We’ve seen that we can easily solve the original problem with SQL only: Select some data from a table using predicate A, and if we don’t find any data for predicate A, then try finding data using predicate B from the same table.
Oracle and PostgreSQL can both optimise away the unnecessary query 2 by inserting a “probe” in their execution plans that knows whether the query 2 needs to be executed or not. In Oracle, we’ve even seen a situation where the combined query outperforms two individual queries. SQL Server 2014 surprisingly does not have such an optimisation.
Interesting totally-not-a-comparison between the three database products. There are some things I’d ideally like the SQL Server optimizer to do with common table expressions, but as Lukas notes, it doesn’t, so user beware.
The main steps that we will review are the following:
splitting paths depending on purchases counts
replacing some channels/touch points
a unique channel/touchpoint case
consequent duplicated channels in the path and higher order Markov chains
paths that haven’t led to a conversion
customer journey duration
attributing revenue and costs comparisons
There’s a lot there, and I like the practical explanations of issues when dealing with a real business problem.
In many demo cases, you will have an all-in-one server where you have installed Power BI Report Server, SSAS (tabular or multidimensional) and SQL Server. In those cases you don’t need any form for credentials delegation since the Report Server is on the same box than the data source.
But there are scenarios where you have a distributed environment like the one I have on my VMs demo domain and for jumping around servers and passing credentials around, you need to setup Kerberos Constrained Delegation. Furthermore you will need protocol transition for it to work in Power BI Report Server.
Read on for step-by-step instructions showing how to do this.
On the Distributed Replay Controller machine, permissions need to be set in Component Services to allow the Distributed Replay Client Service accounts Launch and Activate permissions remotely on the COM component. The service accounts also need to be in the Distributed COM Users group in Windows. So in Component Services, expand Computers > My Computer > DCOM Config > DReplayController and right-click and open the Properties for the COM Component.
A lot of this boils down to permissions, but the error messages are about as unhelpful as they get.
GOis a special little guy. It’s not exactly T-SQL. It’s a way of telling the SQL Server Management Studio (SSMS) to send everything before it, from the beginning of the script or the preceding
GO, to the SQL Server instance.
If you read the documents, the main point to take away is…
A Transact-SQL statement cannot occupy the same line as a GO command. However, the line can contain comments.
The problem turns out to be a little trickier than you’d first imagine. Also, after reading this post, I think it’s lunchtime…
2. Use the Hex Code
If you know the ASCII Code of your qualifier, you can use it starting with “_x” and ending with “_”.
A ” would be described by “_x0022_” for example.
Click through for the other two, as well as a bonus side-by-side German translation.
To spare all the Wows & how can’s, Microsoft was well aware of this problem and has delivered a solution with Cumulative Update 3 for SQL Server 2016 with Service Pack 1:
FIX: SQL Server 2016 consumes more memory when you reorganize a columnstore index, and here it is – a new trace flag 6404 (documented in the link and thus should be supported), that will allow you to lower the memory requirements for the ALTER INDEX … REORGANIZE command.
Let’s take it for the test, by once again running the setup workload for the FactOnlineSales_Reindex table and then executing the following command, enabling the Trace Flag 6404 and then reorganising our Clustered Columnstore Index:
This is a rather interesting post and once again makes me wish that clustered columnstore indexes could be rebuilt online.
If the database property Auto Update Statistics is set to True, when a query is being compiled and a necessary statistic is determined to be out-of-date, it will be automatically updated before optimization continues, thus changing the database. Your SELECT statement could cause this to happen. Additionally, if the Auto Update Statistics Asynchronously property is enabled, the statistic will be automatically updated, but after the optimization process (so the compiling query doesn’t have to wait).
Read on for a few more activities.
Microsoft maintains a list of supported trace flags and I noticed that there are two new ones related to Query Store: 7745 and 7752. The descriptions for these Query Store Trace Flags are pretty straight-forward, but for those of you not familiar with Query Store, I thought I’d provide some context and details.
Click through for the descriptions of these two trace flags.