SQL Standard Confusion, the Cost of Complexity in SQL Server and more in Database Weekly (08/13/22)

Database Weekly for August 13, 2022 Problems displaying this newsletter? [View online](. [Database Weekly]( The Complete Weekly Roundup of SQL Server News by SQLServerCentral.com Hand-picked content to sharpen your professional edge Editorial  SQL Standard Confusion Perhaps Yahweh was a bit harsh in his reaction to seeing the Tower of Babel. He looked with trepidation at the power that a single intelligible language, spoken by all, would give mankind. That tower was, alarmingly, being delivered on-time and to specification. 'Nothing that they propose to do will now be impossible for them. Come, let us go down and confuse their language there', he said and scattered them abroad. This legend, one of the oldest that survive, will always resonate with developers just as it did for the Sumerians. Computer languages achieve their power by their consistency. SQL, sadly, has had the 'confusion' treatment. There is a reasonably consistent declarative syntax for SQL-92 queries but, beyond that, it is tough writing SQL-based applications that are interoperable. Instead, you are obliged to stick with a particular brand of Relational Database Management System. You may experiment on the nursery slopes of a standard SQL, but the moment you need even just string concatenation or date handling, you see inconsistencies. OK, SQL is a declarative language, not a procedural language, but then even some of the declarative syntax is different. You'll even find implementations that are case-sensitive, completely contrary to the SQL standard. If the Sumerian Lord of Aratta (ca. 21st century BC) were alive today and developing SQL, he'd shake his head sadly and sagely wish "the whole universe, the well-guarded people—may they all query databases together in a single language." There are several matters standing in the way of any move towards a shared syntax. One problem is the complexity of the SQL standard, now with JSON, time-intervals, pattern-matching and multi-dimensional arrays. No vendor is likely to add them all into their next release, especially if it requires a breaking change for existing users. Another is the fact that the Standards people were very late in deciding that SQL needed procedural extensions, and by then there were several incompatible and competing standards for procedural code for such database objects as functions and batches. This has resulted in database developers being faced with T-SQL, PL-SQL, PSQL, SQL-PSM, PL/pgSQL, SPL and ABAP, all of which established themselves before the SQL standard appeared. To make matters worse, the SQL standard tied down the syntax firmly without so much emphasis on the semantics. We can parse stuff without being clear what it means, and there are implementation aspects such as indexes which are very specific to a particular implementation. You can't just delegate processes to middleware. The problem is that some processes, such as those within table-valued functions, can provide table-sources, so they are intrinsic to the data layer. The attempt, by ODBC, to provide a consistent syntax for every data source was noble, and worked remarkably well for simple usage, but has had to implement just a very simple subset of SQL. There are no obvious solutions to this problem; we remain boxed into whatever RDBMS we grew up with, suffering acute vendor lock-in. Phil Factor Tony Davis [Join the debate, and respond to the editorial on the forums](  The Weekly News All the headlines and interesting SQL Server information that we've collected over the past week, and sometimes even a few repeats if we think they fit. Vendors/3rd Party Products [Introduction to Testing a Flyway Development]( How to use Flyway Teams to run basic tests whenever it successfully executes a migration, checking that all the business processes supported by our database always produce the expected results. AI/Machine Learning/Cognitive Services [Near Real-Time Anomaly Detection with Delta Live Tables and Databricks Machine Learning]( Spotting anomalous behavior as soon as it happens is vital for preventing lost revenue, fines from regulators, and violation of customer privacy and trust due to security breaches. Administration of SQL Server [Formatting Binary(10) LSN Values For Use In sys.fn_dblog()]( Michael J Swart comes up with a function to convert the binary(10) LSN values from system procedures like sp_replincrementlsninto the format expected by sys.fn_dblog, for those times you need to delve deep into the transaction log. Azure SQL [Resolve tempdb-related errors in Azure SQL Database]( The most common tempdb issue is running out of space, either regarding tempdb's overall size quota or the transaction log. Two factors to investigate are the service tier (pricing tier) that the database is configured with, and the type of workload that is executed against the database. Azure Synapse (SQL Data Warehouse and Data Lake) [Identity Columns to Generate Surrogate Keys Are Now Available in a Lakehouse Near You!]( How to create a surrogate key with an identity column using SQL and Delta Lake [Alternative pipeline parametrization for Azure Synapse Analytics]( Parametrization was always a key aspect in ETL development to be able to move scripts, packages, jobs or whatever artifact you use into another environment. Unfortunately, Azure Synapse or Data Factory lack a configuration database, file or similar mechanism to help with this. One way to overcome this issue is by creating a parameters table in a database. Computing in the Cloud (Azure, Google, AWS) [Securing Web APIs with Azure AD: Enabling Local Development]( In the last post, we looked at creating the server application and corresponding Azure resources to secure it. If we try to test our application locally, we'll quickly run into issues - our API requires a valid JWT with appropriate roles. [Learning Azure – Delving into Databases]( Which databases are on Azure? How do I choose the right one? How do I migrate to an Azure database? [Serverless hosting with Azure Functions]( How to get started with Azure Functions, a serverless solution used for doing things in the back-end and the middle tier, like running scheduled tasks or processing data. DMO/SMO/Powershell [Use a PowerShell Substring to Search Inside a String]( Need to search for a string inside a string? Never fear, PowerShell substring is here! Data Mining / Data Analysis [Part 2 : Natural Language Processing- Key Word Analysis]( Part 2 of a 2-part series on web scraping and natural language processing (NLP), providing details on what NLP is at a high level, and an application of NLP called key word analysis (KWA). Data Privacy, Compliance, and Governance [Microsoft Purview access policies for SQL Server 2022]( Microsoft Purview is a family of data governance, risk, and compliance solutions that help organizations. [Defining Data Retention]( Data retention is a key component of your data governance programs. Policies must be defined early, shared via data literacy programs and technical controls built to automate the retention, protection and purging of data per policies. Database Design, Theory and Development [Managing Datasets for Database Development Work using Flyway]( A generic way of exporting, deleting and loading data, for database development work. It uses Flyway Teams, a PowerShell framework, JSON files for storage and a table manifest to define the correct order of dependency for each task. It should help a team maintain datasets between database versions, as well as to switch between the datasets required to support different types of testing. DocumentDB/Key-Value/Graph/other NoSQL Databases [Working with Neo4j Imports–I HATE CASE SENSITIVITY]( Steve Jones bangs his head against case sensitivity problems when trying to learn more about graph databases. HA/DR/Always On/Clustering [Configure cross-platform SQL Server Always On Availability Groups]( How to configure SQL Server Always On Availability Groups between Windows and Linux SQL instances. Hardware [Virtual Memory & x64 Long Mode]( Having explained how you can read and execute the Second Stage Boot Loader of your own Operating System, Klaus Aschenbrenner moves on to how to remove the dependency from floppy disks and then switch our CPU into the x64 Long Mode, which is necessary to be able to execute our x64-based OS Kernel. MDX/DAX [Calculate working days in DAX]( A quick look at a new DAX function to calculate working days, meaning days between 2 dates, ignoring weekends and (optionally) holidays. Performance Tuning SQL Server [Estimated and Actual Plans Can Have Different Shapes]( Brent Ozar offers an example where estimated and actual plans don’t have the same shape, due to parallelism. [Even If SQL Server Table Variables Were Always In Memory, It Wouldn’t Make Them Better Than Temp Tables]( SQL Server Table Variables have a couple of good uses but, generally, there are so many downsides for most query patterns that they could all happen in quantum space and performance would still stink. [The Cost Of Complexity In SQL Server]( SQL Server can do a whole lot of things, aside from return the results of your queries. The thing is, the more of those you add into the mix the tougher it becomes to scale, and maintain reliable performance. [Using DBCC CLONEDATABASE to create a schema only copy of your database.]( This DBCC command was originally created to "investigate performance issues related to the query optimizer“. How would a schema only copy of a database help with that? Well, it also copies the statistics and query store information. [Are Forced Plans Always Forced?]( Query Store is useful for forcing a beneficial plan but what if something changes that makes the forced plan impossible to use? PowerPivot/PowerQuery/PowerBI [Load API Data to SQL Server Using Python and Generate Report with Power BI]( In this tutorial, we will look at how to load data to a SQL Server database using Python and an API. Then we will show how to build a simple Power BI report using this imported data. [Power BI Datamart Vs. Dataflow Vs. Dataset]( Explaining the differences between these three components, when and where you use each, and how they work alongside other components of Power BI. [Power BI Desktop August 2022 Features I Love]( As Power BI matures, there is less and less to get excited about with a new release of Desktop, but a couple of things that caught Matt Allington's eye in the latest release. [Calling The Power BI Enhanced Refresh API From Power Automate, Part 1: Creating A Basic Custom Connector]( The new Power BI Enhanced Refresh API lets you refresh individual tables in your dataset, override incremental refresh policies, control the amount of parallelism, and a lot more, but one problem remains: how can you schedule a dataset refresh using it? [Power BI Error – Culture Name ‘en-XX” is not valid or is not supported]( How to troubleshoot language errors such as "Culture Name...is not valid or is not supported"when trying to open a PBIX file. [Power BI B2B - Sharing your content outside of your organisation]( Mara Pereira describes how to share content outside your organization, such as for reporting purposes, through a Power BI Service. Professional Development [Some Requests For Authors Of SQL Server Books]( Erik Darling offers some sound advice for any would-be SQL Server book authors out there. R Language [Sonnet to infix function]( Custom infix functions are one of my favorite features in R. This article is my love letter to them. But first, a quick recap. Security News and Issues [How To Install Active Directory Users And Computers: A Step-by-Step Guide]( How to install Active Directory Users and Computers and the basics of working with it so you can manage Active Directory. T-SQL and Query Languages [Learn how to concatenate data in SQL Server]( There is often the need to concatenate data in Microsoft SQL Server in SQL queries or stored procedures to make one long string instead of having separate columns. In this SQL tutorial, we will show several ways that this can be done with T-SQL. [SQL Server 2022: Introduces the DATETRUNC Function, So You Can Truncate Your Dates And Stuff]( What does it do and how does it perform? Erik Darling investigates.  [RSS Feed]([Twitter]( This email has been sent to {EMAIL}. To be removed from this list, please click [here](. If you have any problems leaving the list, please contact the webmaster@sqlservercentral.com. This newsletter was sent to you because you signed up at SQLServerCentral.com. Note: This is not the SQLServerCentral.com daily newsletter list, and unsubscribing to this newsletter will not stop you receiving the SQL Server Central daily newsletters. If you want to be removed from that list, you can follow the instructions on the daily newsletter. ©2019 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved. webmaster@sqlservercentral.com  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -