A Fine Slice Of SQL Server
Tom Collins has a filter for filtered indexes:
Question: How can I find SQL Server Filtered Indexes ? We are woking on some migrations from SQL Server to other engine platform and part of the process includes locating a range of database objects . The identified objects will be recoded into the target engine plaform
Click through for a query which provides this answer. Tom is even kind enough to include the filter definition.
Comments closedGilbert Quevauvilliers answers a question:
Recently I got a question on the Power BI Community forum which was “Is it possible to allow multiple users to use the same data source for their report”?
I thought it would be a great blog post so that other users could also benefit from this knowledge, as once you know it is quite a simple process to complete
I’m mildly surprised by the question itself, as one of the core reasons for having datasets be top-level entities in Power BI is to allow people to re-use them.
Comments closedHi everyone! Brandon Wilson (Cloud Solution Architect/Engineer) here to follow up on the post I authored previously for the Well-Architected Cost Optimization Assessment offering, with another customer offering we have known as the Well-Architected Cost Optimization Implementation. This offering can be considered as a continuation/”part 2” of sorts for the Well-Architected Cost Optimization Assessment, where the goal is to help you implement some of the findings relating to Azure Reservations, Azure Savings Plans, Azure Hybrid Benefits, along with cleaning up some of that cloud waste sitting around.
Just as before (and in case you are a new reader), we’ll touch a little bit on the Azure Well-Architected Framework (WAF), along with the Cloud Adoption Framework (CAF), and then go over what is covered in the Well-Architected Cost Optimization Implementation offering itself.
Some of this is Microsoft-internal tooling, though the WAF assessments themselves are available to the general public and well worth going through.
Comments closedRyan Lambert creates a trigger:
I rarely use triggers. I don’t hate triggers, I just think they should be used sparingly. Like any specialized tool, you should not expect to use triggers for every occasion where they could be used. However… there is one notable use where case I really like triggers: audit tables. Part of the magic of using triggers for auditing data changes in Postgres is the
JSON/JSONBsupport available.
I think this attitude toward triggers is correct. Triggers are really useful, but they come with a few drawbacks, such as tools not making it readily obvious which tables have triggers associated with them, the effects of triggers being “hidden” until execution, and execution problems (e.g., performance issues, potentially modifying data incorrectly, assuming triggers will only work with one row at a time, etc.).
Comments closedRobert Haas points the finger:
If an error message shows up in the PostgreSQL log, what program is malfunctioning? It’s easy to conclude that the answer is PostgreSQL, but that’s too simplistic.
For example, suppose these messages show up in my log file:
ERROR: new row for relation “orders” violates check constraint “orders_d_check”
DETAIL: Failing row contains (1975-03-16, 42).
STATEMENT: insert into orders values (‘1975-03-16’, 42);
Error logs are always a “reading is fundamental” scenario and an exercise in applied logic. Sometimes the exercise is harder than others, such as the notorious “String or binary data would be truncated” in SQL Server or “Object reference not set to an instance of an object” in .NET. But in many cases, there’s enough information at least to get an idea of culprit and cause.
Comments closedAllison Kennedy shares a variant of DimTime:
Today’s post is going to be short and sweet. I’m simply publishing my version of a DimTime table for Power BI.
Thanks to Radacad for posting a great article on why we need a DimTime table, along with their script for creating one in Power BI.
If you don’t already have a DimDate table, you can find my version in my DimDate: What, Why and How blog article. I update the script occasionally with new requests.
Click through for the script.
Comments closedErik Darling is always optimal:
Using the OPTIMIZE FOR UNKNOWN hint, or declaring variables inside of a code block to be used in a where clause have the same issue, though: they make SQL Server’s query optimizer make bad guesses, which often lead to bad execution plans.
You can read great detail about that here.
Read on for a bit of a deserved rant and an example to show why OPTIMIZE FOR UNKNOWN often doesn’t solve the problem.
Andy Leonard takes us through building an Azure Key Vault:
One way to keep confidential information confidential is to store confidential values in Azure Key Vault.
This post describes one way to provision an Azure Key Vault.
In addition to other values, I use key vault to store login usernames – as well as passwords – in key vault. Why? I don’t like storing half of the login information – the username – in plain text. In case I haven’t shared this with you, you should know I use a password generator to create usernames and passwords. In Azure, it’s common to use the same username and password in multiple locations, so when I change access credentials (You are regularly changing passwords, at least, right?), I can update both values in a central location.
One nice thing about most Azure services is that they make Key Vault access fairly easy, especially if you use the managed identity account to grant vault access.
Comments closedKevin Chant diagnoses an issue:
In this post I want to cover common Azure Synapse Link for SQL storage permission issues. Since I helped a fellow MVP out with this recently.
To be more precise, I want to show how you can fix one of the most common issues I tend to encounter with Azure Synapse Link for SQL. Which is access to the Data Lake Storage Gen2 account.
I have encountered issues like this a few times now. For example, when I was performing my file tests for Azure Synapse Link for SQL Server 2022.
Click through to learn more about a couple of common issues, their causes, and resolutions.
Comments closedRyan Majidimehr has an update for us:
We are excited to announce that Multi-Column Distribution (MCD) for Azure Synapse Dedicated SQL pools is now Generally Available! MCD is highly desirable for easing migrations, promotes faster query performance, and reduces data skew.
You can choose to distribute data on multiple columns to balance the data distribution in your tables and reduce data movement during query execution. Multi-Column distribution will allow you to choose up to eight columns for distribution.
Click through for the full list of changes. It seems like there’s a decent spread between the four major pool types, with the emphasis on Data Explorer pools this month.
Comments closed