A Fine Slice Of SQL Server
Aaron Bertrand is not amused with feats of logic:
When I see a bit column allowing NULLs, I always ask:
Is the intention here to have 3-valued logic?
Does NULL mean something different from 0, or will all future code use COALESCE to make them the same?
Excuse me as I look for the modus tollens operator in SQL.
Comments closedKenneth Fisher shows us how Powershell implements subexpressions:
I’m working on a project right now where I want to add the date/time to the end of a filename. It didn’t take me long before I found a solution, and that solution lead me to subexpressions.
Click through for an example. This is indeed quite useful.
Comments closedErik Svensen shaves off a step:
Here is a tip that reduces your Power Query steps with 1 step less.
When I build date tables I typically use the function List.Dates to create a list of dates.
Read on to see how to do this, and then how to do it slightly quicker.
Comments closedErik Darling has two posts of a similar theme. First up is that you might not need to offload reads:
Duplicating data for reporting, outside of moving it to a data warehouse where there’s some transformations involved, can be an expensive and perilous task.
Your options come down to a native solution like AGs, Replication, or Log Shipping. You can brew something up yourself that relies on native stuff too, like Change Data Capture, Change Tracking, Temporal Tables, or triggers.
Erik’s suggestion here is that appropriate query tuning (and I’ll add proper database design!) does more for you than scaling out.
Then, Erik takes it one step further and recommends against certain features in SQL Server:
Consulting gives you a lot of opportunities to talk to a lot of people and deal with interesting issues.
Recently it occurred to me that a lot of people seem to confer magic button status to a lot of things that always seem to be If-I-Could-Only-Do-This features that would solve all their problems, and similarly a Thing-That-Solved-One-Problem-Once turned into something that got used everywhere.
I do agree with Erik on partitioning (which makes administration easier but usually only helps with read queries on columnstore indexes with huge numbers of rows), In-Memory OLTP (which could have been an incredible feature if it worked as I’d hoped), dirty reads, and over-use of recompilation. For sufficiently busy environments, I disagree with Erik’s take on fill factor, having been convinced by Jeff Moden that there’s a lot of value in well-thought-out fill factor settings, but to make the most of it requires more knowledge of the data than DBAs typically take the time to learn.
Comments closedShivani Sarthi clues us in on Grafana alerts:
Grafana alerts are useful since they tell you about the problems in your system moments after they occur. Which in turn results in quickly resolving the issue. Also reducing the disruption time of the service.
Click through for an overview of functionality, where you can post alerts, and how to configure them.
Comments closedAlex Woodie discusses a new storage solution:
In the hunt for the optimal long-term storage, media comes and media goes. From cave paintings and daguerreotypes to tape and optical disk, humans continue to search for new ways to preserve information for use by subsequent generations. Now an Upstate New York company is touting a new type of archive that leverages an element with the ultimate in longevity: gold.
Totenpass today announced the launch of its novel storage solution that’s made of gold. Data is written directly to the surface of the gold cartridge (which features some nickel) using a laser etching process. The data takes the form of shrunken down human-readable images or documents, or machine-readable data that can be encrypted and read with a smartphone app (which is still in development).
This makes perfect sense to me. If you write it in metal, that means Ruin can’t read or modify it.
Comments closedManvendra Singh does the math:
Transparent Data Encryption (TDE) encrypts database files to secure your data. It also encrypts the tempdb database to secure your data in a temporary space. The process of encryption and decryption adds additional overhead to the database system. Even non-encrypted databases hosted on the same SQL Server instance would have some performance degradation because of tempdb encryption. Today I will show you performance impact analysis using few simple T-SQL statements by comparing their stats gathered before and after enabling TDE.
I will execute T-SQL statements (INSERT, UPDATE, SELECT, BACKUP DATABASE) before and after enabling encryption (Transparent Data Encryption) and gather their performance statistics during each execution. Finally, once we will have performance stats taken before and after enabling encryption then we will compare them to understand the performance impact analysis. I have used DBCC DROPCLEANBUFFERS before executing each query to clean the buffer cache.
Click through for the results. My rule of thumb is about a 5% performance overhead, but Manvendra shows us some of the particulars of what that means.
Comments closedJess Pomfret has a Powershell snippet for us:
This morning I was working on pulling together some information which included whether certain accounts were in the local administrator’s group on some remote servers. I had the perfect snippet saved in my code repo so I was quickly able to answer that question – and then I realised I should share that with you all.
Click through for the script.
Comments closedHow to organize workspaces in Power BI is one of those topics that comes up a lot. On one hand, it’s really easy to quickly create a workspace and keep moving. At the same time, it’s also really useful to have a strategy for how you scope your workspaces so they don’t get out of hand over time.
In this post & video we’re going to cover 4 sets of criteria to consider when planning for workspaces in the Power BI Service.
Click through for the video, as well as a post with the details.
Comments closedRobert Cain takes us through Markdown and snippets in Visual Studio Code:
Seriously though, I do find this documentation language very useful, and easy to use. With just a few commands I can produce a nicely formatted document that can be displayed in my code editor, as well as on platforms like GitHub. I’ve even begun authoring these blog posts in Markdown.
A big reason for me is the ability to integrate it into my projects. VSCode, as well as the full blow Visual Studio, support Markdown (with of course the proper extensions installed). When I create a new PowerShell project in VSCode, I can store the projects documentation in Markdown format right alongside the PowerShell code.
By the way, two VS Code extensions I can highly recommend for their Markdown support are Markdown All in One and markdownlint.
Comments closed