A Fine Slice Of SQL Server
Robert Cain needs more than two paths for branching logic:
In my previous post Fun With KQL – IIF, we saw how to use the Kusto
iiffunction to check for a condition then perform an action based on the result of a condition.What if you had multiple conditions you need to check? While you could string multiple
iiffunctions together there’s better solution: the KQLcasefunction.
Robert includes several examples, as well as a check of whether KQL does short circuiting or not.
Comments closedDaniel Hutmacher noticed a problem:
As I was performance tuning a query, I found that a number of date calculation functions in SQL Server appear to be forcing a conversion of their date parameters to a specific datatype, adding computational work to a query that uses them. In programming terms, it seems that these functions do not have “overloads”, i.e. different code paths depending on the incoming datatype.
So let’s take a closer look at how this manifests itself.
Some of these results are pretty annoying, especially because I like to use DATETIME2(0) for the large majority of development work
Robert Sheldon takes us through data insertion in MySQL:
In most cases, adding a single row of data to table is a reasonably straightforward process. You define the
INSERTclause andVALUESclause and usually specify the column list in between. The column list should include only those columns for which you provide values. The list can include the primary key column, generated columns, or columns defined with default constraints, but you must be careful how you handle them, as you’ll see later in the article.
This is definitely aimed at people new to MySQL and SQL in general.
Comments closedRob Farley does a double-check:
I’ve written before about what I consider the golden rule with SQL Injection. And that is, to understand that DATA should not be executed like CODE. A parameter is fine – that’s not part of what gets executed – but you do not embed user-entered values into queries. You never create a WHERE clause like “WHERE t.col1 = 3”, if that value 3 is being passed in. Instead, you use “WHERE t.col1 = @param”. Because you can’t assume that “3” is safe to run.
But there are things in queries that can’t be parameterised. Table names, linked server names, column names, for example.
Read on to learn what Rob does in those cases.
Comments closedMatt Allington made the Power BI world a slightly better place:
Have you ever loaded a calendar table with lots of numeric columns (such as Year, Month Number, etc), loaded it into Power BI, and then had to manually go an turn off the default aggregation on these columns? If you build Power BI reports, then I bet you have done this more than once. Jeffrey’s idea was that Power BI should be smart enough to detect a dimension table (one side of the relationship) and to disable these default aggregations. There’s nothing worse than adding a Year column to a table and having Power BI add the years together. If I add a year to a table, I want to slice and dice, not SUM the years.
Matt added a request and see where that gets you.
Comments closedErik Darling has some thoughts on batching data modifications:
The whole point is to go a little bit easier on your server:
– Smaller transactions sent to the log file
– Shorter locking durations
– Less build up of the version store (optimistic isolation levels, triggers, etc.)
But there are thing that make batching impossible, or downright tricky.
Erik provides more details after the jump. This is all “stuff you should think about” rather than “reasons why you shouldn’t batch.”
Comments closedI like Power BI scorecards a lot, as they give a really nice interface and look to your and your teams goals. However, entering all goals manually can become a bit tedious, especially if you have your goals already sitting in an Excel file. This is often the case for example with Budget figures. In this article I will share a method using Power Automate to create a scorecard automatically from an Excel file using standard Power Automate connectors. Please note that these connectors are still a bit limited, so connections to Power BI reports for example cannot be created currently.
Click through to see how.
Comments closedReitse Eskens doesn’t need a server:
In my previous blog I wrote about the premium tier, the one that can be compared with the business critical tier. Now we’re moving away from the DTU models and back to what we DBA’ers really understand, cores, memory and disks. Before I’m going to dive into the limitations, there’s one thing you need to understand. The serverless tier is made for intermittent use. If you’re using the tier for more than 25% of the time (or about 183 hours per month), you’re better of going provisioned. This has nothing to do with performance but everything with cost. The tipping point of provisioned being cheaper is around 25% of the time.
There’s some solid advice on how to get the service to go to sleep but the bulk of the article revolves around performance.
Comments closedChris Webb disambiguates a couple of terms:
It’s very easy to get confused between a Live connections and DirectQuery mode in Power BI: the customers I work with frequently get them mixed up, partly because they are similar concepts, partly because other BI tools use the term “live connection” to refer to what Power BI calls DirectQuery mode. In this post I will explain the differences between these two terms.
Read on for that explanation.
Comments closedRecently, I attempted to update the Networking for an Azure SQL Server I use when delivering training. Specifically, I was attempting to set “Public network access” to “Selected networks,” add my client IP, and check the “Allow Azure services and resources to access this server” checkbox – found on the Networking blade for my new Azure server:
Fortunately, this was a case of “read the actual error message, understand the actual error.” It doesn’t always work out that way but it’s nice when it does.
Comments closed