A Fine Slice Of SQL Server
I was trying to come up with a demo for something totally different. Don’t ask. Seriously. It’s top secret.
Okay, so it’s just embarrassing.
Anyway. I had these two queries. Which are actually the same query twice. The only difference is the table variable definition.
Click through for the demo and additional information.
Comments closedCallum Green notes the trade-offs between using a Header versus a Title in a Power BI slicer visual:
o Header can have an Outline, which includes the ability to underline text.
o Header is constrained to displaying the name of the attribute (“Product Category Name”), whereas a Title can be customised (“Select Category”). You can rename your source data attribute to get around this, however.
o Title enables you align the text, but this is not possible with a Header.
o Header contains the “Clear Selection” option.
It’s not usually great to have both, but there are definitely trade-offs.
Comments closedPatrick Keisler shows how to use Blob Auditing with Azure SQL Database to log database activity:
If you have multiple objects or actions to audit, then just separate them with a comma, just like the AuditActionGroups parameter. The one key piece to remember is you must specify all audit actions and action groups together with each execution of Set-AzureRmSqlDatabaseAuditingPolicy. There is no add or remove audit item. This means if you have 24 actions to audit and you need to add one more, then you have to specify all 25 in the same command.
Now let’s run a few queries to test the audit. First, we’ll run a simple select from the Salaries table.
Patrick shows off the UI (which is nice for one-off checking) and also the function sys.fn_get_audit_file(), which you’d probably want to use for automated audit checks.
Comments closedIn this video, Patrick answers your question about how to do this in Analysis Services Tabular and Multidimensional. Also, he adds a little bit of SQL to the mix.
Make sure to watch the previous dynamic filtering videos to understand the basics of how to do this.
To begin, you need to make sure to get the URL for your published report.
I completely agree with Patrick about doing as much as you can in the source, especially if there will be more than one potential consumer aside from Analysis Services.
Comments closedUpon completion of a job, the next run time is calculated based on the last scheduled time plus the schedule interval. However, allowance is made for the edge cases where the completed invocation overruns into the next start time. In such a situation, there isn’t a “catch-up” run; instead, the schedule is advanced iteratively until it reaches a future point in time.
However, 2016 introduces a new twist. When applying the “is the proposed next schedule time after Now()?” check, it adds five seconds to Now(). In other words, the question becomes “Is the proposed next schedule time more than five seconds in the future?”
Ewald jumps into the debugger to understand this better, so click through for that.
Comments closedKenneth Fisher shares his rules of thumb with regard to indexing:
Here are my general rules of thumb, although of course, you should always use your best judgment. Also, this is for OLTP systems (ex: data entry systems) not OLAP systems (ex: data warehouses).
No Clustered Index: You really should add a clustered index. Clustered indexes are important for a number of reasons, so if you don’t have one spend some time, figure one out and create it. When possible I like dates as a clustered index but of course, it’s highly specific to the table you are working on. There are a few types of tables, some load tables, for example, were not having a clustered index is appropriate but they are few and far between.
Indexes are like cookies: you can’t have too many. No, wait, that doesn’t sound right…
Comments closedChris Bell has announced a free e-mail course for learning the basics of SQL Server security:
Today I am very excited to announce that I have (finally!) launched my email course covering the basics of SQL Server Security.
This has been a lot of work to get a new system in place to make the learning experience a little different. It is like a normal email course, but at the same time it isn’t.
I have been waiting for this for months ever since hearing Chris first talk about it.
Comments closedDmitry Pilugin has a test of interleaved execution of a multi-statement table-valued function:
First of all, note that Estimated Number of Rows is 10 000 now, which is correct and equals Actual Number of Rows. Due to the correct estimate the optimizer decided that there are enough rows to benefit from a partial (local/global) aggregation and introduced a partial Hash aggregate before the join.
If you take a Profiler, enable events SP:StmtStarting, SP:StmtCompleted, SQL:StmtStarting, SQL:StmtCompleted and run the query without and with a TF, you’ll see what does it actually mean “interleaved” in terms of the execution sequence.
During the regular execution the query starts executing, then the function is executed, the query continues execution and finishes the execution. We see the following event sequence:
This is a very thorough post, but if you have multi-statement TVFs, you absolutely want to read it.
Comments closedJoey D’Antoni is shaking his head about a CLR announcement:
With this is mind, Microsoft has made some big changes to CLR in SQL Server 2017. SQL CLR has always been an interesting area of the engine—it allows for the use of .NET code in stored procedures and user defined types. For certain tasks , it’s an extremely powerful tool—things like RegEx and geo functions can be much faster in native CLR than trying to do the equivalent operation in T-SQL. It’s always been a little bit of a security risk, since under certain configurations, CLR had access to resources outside of the context of the database engine. This was protected by boundaries defined in the CLR host policy. We had SAFE, EXTERNAL_ACCESS, and UNSAFE levels that we could set. SAFE simply limited access of the assembly to internal computation and local data access. For the purposes of this post, we will skip UNSAFE and EXTERNAL_ACCESS, but it is sufficed to say, these levels allow much deeper access to the rest of the server.
Code Access Security in .NET (which is used to managed these levels) has been marked obsolete. What does this mean? The boundaries that are marked SAFE, may not be guaranteed to provide security. So “SAFE” CLR may be able to access external resources, call unmanaged code, and acquire sysadmin privileges. This is really bad.
It’s not the end of the world for CLR, but this is a breaking change. Read on for more details.
Comments closedDavid Smith gives us a nice roundup of feature improvements in Microsoft R Server 9.1:
Interoperability between Microsoft R Server and sparklyr. You can now use RStudio’s sparklyr package in tandem with Microsoft R Server in a single Spark session
New machine learning models in Hadoop and Spark. The new machine learning functions introduced with Version 9.0 (such as FastRank gradient-boosted trees and GPU-accelerated deep neural networks) are now available in the Hadoop and Spark contexts in addition to standalone servers and within SQL Server.
I have been looking forward to these.
Comments closed