A Fine Slice Of SQL Server
Tomaz Kastrun explains how to combine data in two languages:
Merge statement in R language is a powerful, simple, straightforward method for joining data frames. Nevertheless, it also serves with some neat features that give R users fast data wrangling.
I will be comparing this feature with T-SQL language, to show the simplicity of the merge method.
Click through for the comparison.
Comments closedArun Sirpal looks at some of the command language for Azure Redis:
Now that we have created our Redis Cache lets connect to it. You can use the most common tool redis cli.exe https://redis.io/download or as I am going to do, use the Azure Portal to use the console directly, this isn’t probably the best way but it’s the easiest for this blog. 2 key points here:
Read on for those points, as well as examples of commands you can run.
Comments closedReitse Eskens looks at a couple of useful window operators:
I was teaching a class today on more advanced SQL queries and we were discussing if you could use preceding and following in a single windowing function.
Windowing Functions
If you’re not familiar with windowing functions, they’re really cool. You can perform all kinds of trickery and magic with them. Let’s start with a simple example.
The dataset I’m using has flight data from the early 2000’s.
Read on for the example and for the answer to Reitse’s question.
Comments closedChad Callihan looks at synonyms in SQL Server:
Are you familiar with synonyms as they relate to SQL Server? I haven’t seen them used too much out in the wild but understand they can have benefits. Let’s take a look at what synonyms are in SQL Server and some common reasons for implementing them.
My recollection is that synonyms have some limitations which make them not as useful as they’d first appear. But the bigger reason I think we don’t see synonyms used very often is that they obscure information and make it tougher to understand what’s really happening. In that respect, it’s a bit similar to a trigger: useful but sometimes painful to debug because it obscures relevant information.
Comments closedRonen Ariely provides a solution:
However, the answer in this specific case was not optimal. Unfortunately in most cases in the forums, most people that come to ask a question, do not care about learning but only about the solution, even so in my opinion the road is just as important as the end point. The road (the learning) is what will help the person to solve the next issue and not just the current one – teach a man to fish and you feed him for a lifetime…
The op marked the answer he got and I assume that from his point of view the discussion ended, but I wanted to present the solution which might be tens time better in some cases, which is what I will do in this post…. so let’s start
I won’t dive too deeply into Ronen’s philosophical argument—you can definitely read about that in the post. I will say I am sympathetic to the argument at the margin and believe it’s worthwhile to know the superior solution.
Comments closedHamish Watson does a document dump:
So what did we do here?
It searched our stored security events in the SecurityEvent table for all Accounts that had a successful login in the last 3 hours and we chose to display only the Account and number of log off events per Account in numerical order with the highest at the top.
So far I’ve introduced some new operators and things – but what is a really quick way to learn KQL?
Start with this post and just keep navigating forward. Hamish has ten posts in total.
Comments closedKevin Wilkie has fun with grouping sets:
Let’s look at our dbo.Person1 table that we worked with earlier. Today, I want to find a count of all of the persons in each of the following categories: ZipCode, Gender, and Email Domains. And just for fun, let’s add in there where each of those categories cross – for example, Zipcode and Gender, ZipCode and Email Domain, etc…
Most people would think all kinds of awful thoughts at this point about all of the GROUP BY statements you’ll have to write. For anyone wondering – this is one way to do it. Notice all kinds of UNION statements and I’m sure someone is wondering if that’s truly all of the combinations. And we don’t want to go into the maintenance on this if things do happen to change…
And don’t forget about the GROUPING() function:
Let’s say our business partner asks us to determine which fields are aggregated together. Since we only have 2 fields and a grand total of 15 rows, we could determine this by eye. But, like all good developers, we want to do this programmatically.
Here’s where our friend – the GROUPING() function – comes into play.
GROUPING SETS is an extremely useful operator in the ANSI SQL standard. Definitely worth learning how to use.
Robert Sheldon continues a series on getting started with MySQL:
Like other database management systems, MySQL lets you create views that enable users and applications to retrieve data without providing them direct access to the underlying tables. You can think of a view as a predefined query that MySQL runs when the view is invoked. MySQL stores the view definition as a database object, similar to a table object.
Read on for plenty of detail around views. Even if you know how views work in another RDBMS, there are nuances to each of them you’ll want to understand.
Comments closedKevin Wilkie unlocks the power of QUOTENAME():
When I first heard about QUOTENAME, I was like “This is rather useless. It just puts brackets around whatever. I can do it just as easily hard-coding the strings.”
Truly, I’m not completely wrong, but it’s a heck of a lot more fun to knock things out with the QUOTENAME function!
But there’s more that you can do with this function, as Kevin notes.
Comments closedAaron Bertrand builds a report:
Without comprehensive reporting tools (or Excel), it can be cumbersome and frustrating to produce perfect report output from SQL Server SELECT statement or stored procedures. In modern versions, we have access to T-SQL functionality that far exceeds old-school ROLLUP and CUBE, like PIVOT, UNPIVOT, and GROUPING SETS. Let’s look at how to produce output we can easily plug into a simple front end and produce great-looking reports.
GROUPING SETS is one of my favorite under-utilized operators.