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 closed