A Fine Slice Of SQL Server
Patrick LeBlanc shows us how to incorporate images into Power BI data sets:
Are you curious about how images can be used in Power BI? Patrick looks at some different ways to incorporate images into your data model and Power BI Report!
Click through for a video. All of the techniques here use an image URL which gets pulled in rather than storing the binaries in Power BI itself.
Comments closedAaron Nelson has a pair of new Powershell cmdlets:
I built two new PowerShell commands to deploy SSRS projects, and they have finally been merged into the ReportingServicesTools module. The commands are Get-RsDeploymentConfig & Publish-RsProject. While the Write-RsFolderContent command did already exist, and is very useful, it does not support deploying the objects in your SSRS Project to multiple different folders in your report server. These two new commands can handle deployment to multiple folders.
Click through for details on each.
Comments closedErik Darling reminds me why I hate user-defined functions in SQL Server:
At 23 seconds, this is probably unacceptable. And this is on SQL Server 2019, too. The function inlining thing doesn’t quite help us, here.
One feature restriction is this, so we uh… Yeah.
The UDF does not contain aggregate functions being passed as parameters to a scalar UDF
But we’re probably good query tuners, and we know we can write inline functions.
Read the whole thing, as this is not always straightforward.
Comments closedWe have an application that uses
datetimecolumns in a number of places. All of the users have always been in Eastern Time, but now we have a request to introduce users from a different time zone (Central Time) into the system. The lack of time zone information in our dates and times now presents a problem.The system needs to communicate to users how long ago something occurred, or a time in the future that something needs to be done. If an Eastern Time user enters in a “follow up time” of today at 2:00 pm, a Central Time user could log in, see that, and end up being an hour late following up with their customer.
Click through for the process and several bugaboos you might run into. What we’ve done was to force all application times in UTC in DATETIME or DATETIME2 format and then store user preferences on time zone in the application, translating from UTC to the relevant time zone at that level.
Comments closedItzik Ben-Gan continues a series on table expressions:
Last month I explained and demonstrated that CTEs get unnested, whereas temporary tables and table variables actually persist data. I provided recommendations in terms of when it makes sense to use CTEs versus when it makes sense to use temporary objects from a query performance standpoint. But there’s another important aspect of CTE optimization, or physical processing, to consider beyond the solution’s performance—how multiple references to the CTE from an outer query are handled. It’s important to realize that if you have an outer query with multiple references to the same CTE, each gets unnested separately. If you have nondeterministic calculations in the CTE’s inner query, those calculations can have different results in the different references.
Say for instance that you invoke the SYSDATETIME function in a CTE’s inner query, creating a result column called dt. Generally, assuming no change in the inputs, a built-in function is evaluated once per query and reference, irrespective of the number of rows involved. If you refer to the CTE only once from an outer query, but interact with the dt column multiple times, all references are supposed to represent the same function evaluation and return the same values. However, if you refer to the CTE multiple times in the outer query, be it with multiple subqueries referring to the CTE or a join between multiple instances of the same CTE (say aliased as C1 and C2), the references to C1.dt and C2.dt represent different evaluations of the underlying expression and could result in different values.
Definitely worth the read.
Comments closedWarwick Rudd takes us through database projects in Azure Data Studio:
For a long time source control for Database Code, has been difficult or costly to implement and use.
With the ever expanding list of resources available for Azure Data Studio, we can now do even more while staying inside of a single tool allow us to be more productive and take advantage of implementing and using source control in our environments.
In the September 2020, release we have a new extension – Database Projects that I recommend you install and have approved if needed in your environment making your life easier with your database development being incorporated into source control.
I’ll stick to the ones in Visual Studio for now, but will check in on this in a couple releases to see if there’s enough value in here to make the switch.
1 CommentGuy Glantser hits on a pain point in SQL Server when dealing with long strings:
Now, let’s talk about concatenation. What do you think would be the data type of the following expression?
N’ABCD’ + N’EFG’
Correct! It’s NVARCHAR(7). Everything is making sense. Isn’t it great?
Now, let’s complicate things just a little bit. Suppose you have an expression that is a concatenation of two string literals – one of them contains 3,000 characters and the other contains 2,000 characters.
Guy also has a function to print beyond 4000 Unicode characters:
Sometimes, you want to print a long string. For example, you might want to print the definition of a long stored procedure. Or you might have a very long dynamic batch that you are going to execute, but you want to print it first for debug purposes.
The problem with the PRINT statement is not only that it prints up to the first 8,000 bytes. It also truncates your text without even generating a warning.
This is a long-running frustration of mine, especially when writing out complicated dynamic SQL. I think PRINT should have been changed 15 years ago to handle MAX types.
Comments closedThe truth is, its often a combination of database and infrastructure issues that are the cause. Although many of you may want me to dig into database performance data, I’m actually going to first focus on infrastructure, as it’s the area that most aren’t privy to for Oracle, or for that matter, any database on Azure IaaS.
The topic of infrastructure is an essential one for any database running in IaaS and even more so VMs on Linux, which can be a bit foreign for the Microsoft data specialist. Yes, this may be intimidating when doing the shift to Linux and understanding some of the nuances to running a database on Linux, but understanding the infrastructure is a key to removing it from the scenario. Hopefully these tips will assist you, no matter if you’re running Oracle, (MySQL, PostgreSQL or SQL Server) on Linux VMs on Azure IaaS.
Click through for some guidance on the topic.
Comments closedEd Hansberry shows how to deal with renaming columns whose names regularly change:
The easiest way to rename a column in Power Query is to do it the same way you do in Excel – just double-click it and rename it. Sometimes though the column you are renaming can have different names with each refresh, so this method won’t work. We can use a List in Power Query to make this dynamic. Consider this very simple example:
You receive this file every day, and the column name starting in Row 6, Column B, constantly changes. We are going to extract that date and make it a column, as it should be, and then rename both columns regardless of what they are called.
Read on for the process.
Comments closedGarry Bargsley shows how you can export configuration settings for your SQL Server instances:
Have you ever deleted a login by mistake from a hastily typed TSQL script or dropped a list of logins because the “Business” said they are not used anymore? Have you ever made a change to a SQL Server Agent job and then it failed on the next execution. What about that time you changed the Database Mail profile on all of your servers and left your personal account in the script instead of the DBA distribution list.
While each of these examples is not life-threatening, they will strike fear in you depending on how prepared you are to recover the items in question.
This is the type of thing you’d want to store in source control, too. That way, you have a record of changes over time.
Comments closed