A Fine Slice Of SQL Server
Kevin Wilkie is on the top shelf:
In the last blog post, we went over the extreme basics of using the TOP operator in SQL. We showed how to grab things like the TOP 10 of a certain item.
That ability will get you through a number of criteria that you will be asked to perform. But what if you’re asked to grab the top five percent of performers in your company? Or in a region? It’s kinda hard to do that if you only have what we know so far, right?
Read on for the answer.
Comments closedAndrew Pruski troubleshoots a problem:
I was adding databases to an availability group (SQL Server 2017 CU20 instance) the other day and one database failed to automatically seed to the secondary.
When I looked in the SQL Server error log I saw this error message: –
BackupIoRequest::ReportIoError: write failure on backup device ‘{GUID}’. Operating system error 995(The I/O operation has been aborted because of either a thread exit or an application request.).
Read on to see how Andrew solved the problem.
Comments closedAndy Mallon splices together time zone information like some kind of time zone Dr. Frankenstein:
There was a recent post on Database Administrators, where there was some confusion over Daylight Saving Time(DST) in Australian timezones. Let’s be honest though–timezones are confusing, especially when DST comes into play. Arizona Time doesn’t observe Daylight Saving Time, so sometimes it’s the same as Mountain Time, and sometimes it’s Pacific Time.
Finding the right time zone can be hard. As of this writing, Windows recognizes 141 different timezones, many of which overlap.
In this case, the asker was running this query, and confused over why it wasn’t properly reflecting DST, and the other columns in the DMZ weren’t helpful either:
This is why everybody should just use Eastern Standard Time: it’s the standard—says so in the name, even.
Comments closedKevin Farlee surprises us all with some changes to replication:
Starting with CU13 of SQL Server 2019 , we are including new functionality which facilitates peer-to-peer replication with conflict detection & resolution using a last-writer-wins strategy. This enables many scenarios for multi-write configurations which may not have been possible or practical before.
Traditionally, customers configuring peer-to-peer replication are advised to implement a sharded solution which ensures that each row is updated on only one replica server (key values A-M are updated on server 1 while key values N-Z are updated on server 2, etc.). If it should happen that the same row is updated within a small time window by two servers in a replication environment, a conflict will be detected, which will either stop replication after raising an error, or it may optionally be resolved using the originator ID of the replica sending the update. (i.e. if you have replicas with Originator IDs 1 and 2, then updates coming from the replica with originator ID 2 would always win, and be persisted. See Conflict Detection in peer-to-peer replication – SQL Server | Microsoft Docs for a detailed explanation.
Read on for more details.
Comments closedAndrew Collier works with some wide data:
The concept of “wide data” is relative. In some domains 100 columns is considered “wide”, while in others that’s perfectly normal and you’d need to have thousands (or tens of thousands!) of columns for it to be considered even remotely “wide”. The data that we work with at Fathom Data generally lies in the first domain, but from time to time we do work on data that is considerably wider.
This post touches on a couple of approaches for dealing with that sort of data. We’ll be using some HCRIS (Healthcare Cost Report Information System) data, which are available for download here. Specifically, we’ll be working with an extract from the
hcris2552_10_2017.csvfile, which contains “select variables in flat shape”.
Click through for one example which has 1700 columns. H/T R-Bloggers.
Comments closedKristyna Hughes wants to update an existing schema:
Goal of this demo: Update a Power BI model schema by adding a column to the data model without opening a PBIX file and ensure the scheduled refresh still works.
Why would this be useful? Updating the schema in the desktop tool requires an entire refresh of the data model which can take a while if your model is large. Also, app developers could systematically add new elements to existing data models using a formulaic XMLA script through SSMS, saving your report designers time when new fields need to be added.
Read on for limitations, as well as the process.
Comments closedJack Vamvas does a thing I wish we could do innately in SQL Server Management Studio:
I am logged onto my desktop with my primary Active Directory ID. I need to log onto a SQL Server with SQL Server Management Studio (SSMS) with another Active Directory ID – the alternative ID has valid SQL Server security privileges on the target SQL Server.
How can I log onto the target SQL Server with the alternative login , remain on my desktop and use the alternative Active Directory ID ?
By the way, if you need to connect to a remote domain but your machine isn’t a member of that domain, add the /netonly flag to Jack’s answer. I’ve had to do this before when VPN’d into a network with a laptop not registered on that domain. Another tip is that, if you do this a lot, you might want to create a Windows shortcut which includes the full command.
Ed Pollack continues a series on extended events:
While using Extended Events is not overly complex, building a reliable system to collect, parse, and store events over time without any data loss can be challenging.
This article dives into one method for collecting and retaining all event data for a specific set of events. A system like this is highly customizable and provides a solid starting point for Extended Events development. This should be viewed as a collection of puzzle pieces; individual pieces can be adjusted as needed to produce a monitoring solution that fits the needs of a given situation, even if it is vastly different from what is demonstrated here.
Read on for the process. Shredding XML isn’t pretty, but the good news is that with a setup like this, you only need to do it once…unless you need to change it later, so get it right the first time and bam, problem solved.
Comments closedKendra Little is fairly pleased with the Azure SQL Database Quickstart guide:
I’m working through a series of Quickstart tutorials for Azure SQL Database and sharing bite-sized reviews.
In this first walkthrough, I’m stepping through the Create a Single Database tutorial.
Click through for Kendra’s take.
Comments closedJohn McCormack lays out the parameters for this T-SQL Tuesday:
T-SQL Tuesday this month is going back to basics and its all about code. I’d like to know “What are your go to handy short scripts”?
What are those little short bits of code that you can’t live without? I’m talking about little snippets that are only a few lines, that you may even have memorised. It could be T-SQL, PowerShell, Python or anything else you use day to day.
Click through for two of John’s.
Comments closed