A Fine Slice Of SQL Server
Shane O’Neill has a bandolier of SQL tricks to show off:
Recently the DBA Team Lead and I were reviewing some SQL code, and we came across some SQL that neither of us had frequently encountered before. This led to a brief watercooler moment where we shared some uncommon SQL that we had seen. Perfect blog post material, I think.
I had previously learned about ODBC date functions from Shane and also learned about CURRENT in this post, so check it out.
Comments closedKevin Chant shows us how to use GitHub Actions to deploy updates to Azure SQL Database:
After my last post I wanted to test deploying to Azure SQL Database using GitHub Actions. To check that it all runs smoothly.
By the end of this post you will have some ideas about how you can deploy Azure SQL Databases using GitHub Actions. Both for a basic test and more complex deployments.
In my last post here I showed you how you can use GitHub Actions to deploy a free monitoring framework called SQLWATCH to on-premises versions of SQL Server. I thought I would test using the same Azure SQL Deploy Action for Azure SQL database deployments.
Click through for the process.
Comments closedDave Mason uses an external memory:
There are certain T-SQL statements whose syntax I have trouble remembering. When those situations arise, I might look up the syntax online; find the same type of object in SSMS, right-click it, script out the object, and use that as a starting point; or find one of my own scripts that has the syntax I’m looking for. Another option that I often overlook is T-SQL code snippets.
Click through to see how to use code snippets in SQL Server Management Studio. You can also create your own as well.
Comments closedTerry McCann posts Q&A from Simon Whiteley’s session on Lakehouse models in Spark 3.0:
“WHILE ALL THE HADOOP PROVIDERS PROMOTED THE DATALAKE PARADIGM BACK THEN, HOW THE INDUSTRY AND THE OTHER DATA LAKE PROVIDERS ARE SHIFTING TO/CONSIDERING THE LAKE HOUSE PARADIGM?“
It’s a direction that most providers are heading in, albeit under the “unified analytics” or “modern warehouse” name rather than the “lakehouse”. But most big relational engines are moving to bring in spark/big data capabilities, other lake providers are looking to expand their SQL coverage. It’s a bit of a race to who gets to the “can do both sides as well as a specialist tool” point first. Will we see other tools championing it as a “lakehouse”, or is that term now tied too closely as a “vendor-specific” term coming from Databricks? We’ll see…
Click through for some good questions and thoughtful answers.
Comments closedJeffrey Hicks takes us through color changes with Powershell:
I readily admit that I spend a great deal of my day at a PowerShell prompt. My day is very much run from the command-line, and has been for quite some time. This used to be a drab, gray existence. But I’ve been finding ways to liven things up. Here’s one way.
The PSScriptTools module includes a number of custom format files with alternate views. You need to make sure the module is imported before you can use any of them.
It’s interesting just how much of a quality of life improvement file type coloration is. I’ll go out of my way to use colorized shells with bash, as well as pretty much any IDE and even Notepad++.
Comments closedRajendra Gupta continues a series on Availability Groups:
In the previous article, Explore dynamic management views for monitoring SQL Server Always On Availability Groups, we explored the DMV’s for the Availability group for Windows Failover Clusters.
This article takes a further step and covers useful DMV related to availability replica and databases. Let’s start our journey with this article.
Click through for the article.
Comments closedHugo Cornelis continues a dive into execution plan operators:
The Filter operator is used to remove rows from a result set that do not satisfy a specified condition.
Its most common usage is to implement a WHERE clause, although many elements of a WHERE clause get implemented in other ways (e.g. by pushing them down into an Index Seek or Index Scan operator), and Filter operators can also be used when the optimizer transforms other query elements into any type of filtering.
The Filter operator can only verify conditions based on the data in the current row; if data from different rows needs to be compared for the filter, the optimizer generates an execution plan that first adds the data to be compared to the row, or it uses different operators.
Click through for Hugo’s normal in-depth look.
Comments closedI have a SQL Server Linked Server configured , pointing to an ODBC – accessing a MongoDB driver . The test connections all work OK – and no problems running an OPENQUERY select statement using the Linked Server.
But when I attempt to run an EXECUTE AT , and attempt to INSERT the data into a #temp table – I get an error message:
Msg 7390, Level 16, State 2, Line 6
The requested operation could not be performed because OLE DB provider “MSDASQL” for linked server “my_linked_server” does
not support the required transaction interface.
Read on to see what the problem is and how you can solve it.
Comments closedErik Darling shares some introductory thoughts on the filter operator:
When we write queries that need to filter data, we tend to want to have that filtering happen as far over to the right in a query plan as possible. Ideally, data is filtered when we access the index.
Whether it’s a seek or a scan, or if it has a residual predicate, and if that’s all appropriate isn’t really the question.
In general, those outcomes are preferable to what happens when SQL Server is unable to do any of them for various reasons. The further over to the right in a query plan we can reduce the number of rows we need to contend with, the better.
Read on to see where Erik takes this and stay tuned for part 2.
Comments closedAdam Saxton points us toward good resources for learning about Power BI:
Just starting with Power BI? Maybe you’ve been using Power BI for a while and have hit a wall. We wanted to call out some items that help with a lot of conversations we have over and over again. Take your skills to the next level!
Check out the video and click through for links to helpful resources.
Comments closed