A Fine Slice Of SQL Server
Kevin Wilkie remains distinct:
Now, the more fun – “new-ish” – version of the DISTINCT keyword.
Let’s take two values – A and B. Let’s define A = 7 and B = 2.
Snowflake will allow you to ask if A IS DISTINCT FROM B. Thankfully, in this case, it is.
Click through to see how this works. Also note that this syntax is available in SQL Server 2022.
Comments closedGilbert Quevauvilliers builds a data validation tool in Excel:
I had a requirement where the customer wanted to make sure that the right user could only see the colour from the stock item assigned to them. Essentially using the Excel file for Row Level Security.
For the Row level security to work correctly it required the colour name to be an exact match.
For me to ensure that this would happen, I wanted to make sure I could get the data from my Power BI dataset, and then using the Excel data validation make sure that the user could either type in the value 100% correct or pick it from the drop-down list.
There are quite a few steps, but I can see business people being happy about the final outcome.
Comments closedRob Farley covers one of my favorite operators:
SQL Server 2005 was released, of course, in 2006, and I had been running the Adelaide SQL Server User Group since September 2005. Information about the new features had been coming through, and I’d been at TechEd Australia 2005 – my first since 1999. I was still an application developer at the time (well, a manager, but still getting my hands dirty), but saw the data as the most important part of my applications. When the T-SQL enhancements in SQL Server 2005 came through, there were two things that caught my eye (I know they were available in Oracle before SQL Server, but I was focusing more on the Microsoft platform by then). They were the OVER clause, and APPLY.
This is all part of a broader story about reading code to learn from it.
Comments closedI ran into a concern to quickly audit all current users and role members for a set of Azure SQL databases, spread across multiple resource groups. Without an easy CMS concept or a way to quickly loop through an unknown set of servers, resource groups, and databases, that can be a little challenging. I have an account to use that should have access to all databases (but doesn’t) so put together some PowerShell that I could run locally to get that information and send the results to Excel.
Click through for a SQL script to get the data and a Powershell script to run this for each database and export the results into different tabs in Excel.
Comments closedShane O’Neill has an experience:
You have code that keeps coming up as a high consumer of CPU on our systems? OK?
It had gotten to the stage where it kept appearing in
sp_WhoIsActive? What’s that? And should it not be “whom is active”? Well, agree to disagree.Let’s see the code so. Wow, that’s a small scroll bar! Yeah, that’s one of ours.
I should note that “Who is active?” is correct, as the sessions we are inquiring after are the subject of the question rather than the direct or indirect object, and there is no prepositional phrase which would affect the decision.
Comments closedReitse Eskens takes a shortcut:
A few days ago, I heard the term table clone for the first time, it’s preview release date was to be confirmed and I had no idea what it was about. Two days later, a video emerged where the table clone was explained on a high level. At that point, I started to wonder what the differences are between a table clone and a shortcut. So let’s dig a little into that question!
First I’m going to create a shortcut, then a clone and finally compare the two.
Read on for the comparison, as these are quite different things.
Comments closed