A Fine Slice Of SQL Server
You should never embed passwords or other “secrets” – sensitive data – in code. A better way is to put sensitive data into configuration, and load configuration from your code. Read on to find out how to do this in R Markdown (and Shiny).
Click through for one way to do this. Just make sure you .gitignore excluded .env files.
Paul White continues a series on lock escalation:
When SQL Server reads data under locking read committed isolation (the default for non-cloud offerings) and chooses row-level locking granularity, it normally only locks one row at a time. The engine processes the row through parent operators before releasing the shared lock prior to locking and reading the next row.
When the engine chooses page-level locking granularity, it takes and holds a shared page lock while processing rows on the current page and then releases the lock right before acquiring a lock on the next page.
In general, SQL Server is careful to take and hold locks only when needed and only long enough to guarantee correct results, taking account of the current isolation level. When locking at row granularity, the engine can skip row locks entirely when it’s safe to do.
Read on to understand when SQL Server either gets it wrong or when exigent factors alter the story.
Comments closedIf you have ever used Azure Synapse Analytics dedicated SQL pool you would know there are multiple table types to choose from, for your workload. You might ask yourself, “when can I use Replicated table type and how I can efficiently use them”?
This blog is going to talk in detail about replicated table type, when to use and what are best practices for its usage. But before that, let’s start by understanding the different table types:
I’ve seen replicated tables get overused, so check out Pedro’s advice on how not to get burned with them.
Comments closedKate Smith points out a new feature in SQL Server 2022:
In SQL Server 2022, we introduced a new feature called DOP feedback. This feature will look at any parallel query and determine if it might perform better with a lower degree of parallelism than currently being used. For example, perhaps 16 threads will perform better than 20 if there are a lot of waits on other threads. It will test out the new degree of parallelism and, either decide that this was a good change and keep the 16 threads, or it will revert to previous levels of parallelism and go back to 20 threads. If the new degree of parallelism is good, then this optimization is persisted inside the query store and will be applied appropriately to a query for future executions.
Read on for an overview of how it works and what protections are in place to keep it from going completely bonkers. Well, more completely bonkers than what you already have.
Comments closedKevin Chant gives us the whole story:
In this post I want to show you a complete CI/CD experience for Azure Synapse Link for SQL Server 2022 tables. Which uses a YAML Pipeline in Azure DevOps. Including how to automatically stop and start it in the pipeline.
In a previous post I showed how an easier way to perform CI/CD for Azure Synapse Link for SQL Server 2022. Where you only need to stop the link, update the SQL Server database and afterwards start the link again.
However, the best CI/CD solutions are the ones where you do not do any manual work at all. This includes stopping and starting the link.
And that’s just what Kevin gives us.
Comments closedOlivier van Steenlandt helps you get database code into source control:
In this blog post, we will focus on how to get started with Database Projects and how to get this into Source Control (Azure Repos). So together we will create our first Database Project, import our database into the project and push it to the Azure Repository.
Before we can start, we need to make sure that we have the required tools installed, in this blog post I will focus on Visual Studio. In order to create your first Database Project, you need to ensure that the SQL Server Data Tools extension for Visual Studio is installed.
This one I intended to post earlier in the week but it got away from me for a little bit. Do check it out.
Comments closedChad Callihan emulates George Jetson:
I recently saw a comment about building SQL scripts with Excel. While I haven’t gone that route before, the comment did remind me of a quick and dirty way of generating SQL statements with Notepad++ and macros. It can be helpful when you have the right set of data and repetitive steps to take.
Let’s look at an example.
Read on to save time.
Comments closedJeffrey Hicks keeps track of time:
Last year I released a PowerShell module called PSClock. The module contains a command to create a transparent WPF form displaying a clock. Shortly after, someone posted a request for a countdown timer. Not an unreasonable request and one I finally got around to implementing. However, I already had a module with a number of timer and countdown-related commands called PSTimers I had been thinking of a WPF-based timer, so this solved two problems.
Read on to see how it works.
Comments closedThis is more of a heads up for me / reminder regarding some of the caveats to using ONLINE / RESUMABLE index operations with SQL Server.
Don’t get me wrong, there’s a ton of advantages to using ONLINE and RESUMABLE which I will highlight below but here is the link to the Microsoft Learn page if you want more in depth information
Read on for the pros and cons. I do like the idea, though I personally haven’t used the feature.
Comments closedPrathy Kamasani gives us options:
A colleague of mine came to me with an interesting use case, “ Switch between a summarized value or selected value with multiple legends”. For example, I have five countries and their GDP values. When the end user goes to the report, the user would like to see the average GDP of all countries, but when the user selects single or multiple countries on the slicers, the line chart should show only selected values.
Click through for the guide.
Comments closed