The Complete Weekly Roundup of SQL Server News

Hand-picked content to sharpen your professional edge

Why don't database developers like regular expressions?

• Firstly, there are countless flavors of it, from PCRE, Vim, Java to POSIX ERE. Microsoft still have three distinct, basic flavors, in many varieties. You never can be quite sure how your favorite RegExes will work if you change your application, or work in two different versions of one application (as with SSMS).
• Secondly, Regexes aren't intuitive. Even if you understand concepts such as 'greedy' 'possessive' and 'lazy' and you think you've wrestled with the complexities of matching line ends, you are confronted with a metacharacter syntax that may have made sense to the legendary mathematical geeks who devised it, such as Stephen Cole and Ken Thompson, but is hardly destined to appeal to the legendary, ordinary application developer on the legendary Clapham Omnibus.
• Thirdly, when they are used as predicates in filters, such as WHERE clauses, it is very difficult to reduce the number of searches to the possible candidates. Unless the Regex makes a certainty of the start of the string, it is hopeless.

Instead, in SQL Server, we cling to the LIKE/PATINDEX wildcard convention. It is a Sybase invention, a sort of poor-man's RegEx, so Microsoft have never bothered to enhance the syntax. In a sense this is a joy. The LIKE wildcards that you write now will work in any version of SQL Server. However, they are so deficient in the basic anchors, quantifiers, character classes and alternators that any useful wildcards become very complicated even to look at, let alone understand. Frustratingly, Regexes are there in SQL Server, but out of reach, only for XQuery/XPath expressions.

With MySQL, MongoDB or PostgreSQL, Regular expressions are at the heart of searches, and they aren't causing the sort of performance problems that Microsoft engineers seem to conjure up in their fevered imagination. Microsoft can't really afford to dither any longer with facilitating better string pattern search. As I see it there are two options: either enhance the LIKE/PATINDEX wildcard conventions or provide a good Regex search that is compatible with .NET Core. I'd be happy with the former, just to allow easier constraints for complicated string-based datatypes, such as sort codes or postal codes. We must retain backward compatibility anyway, and it would be great to have something that prevents us having to learn two different conventions for pattern search.

Curiously, even PowerShell has a '-like' comparison operator. It is even more of a poor, shriveled thing than the SQL Server one. The documentation for it consists mostly of stern instructions to use the -match Regex instead. This seems a bit rich, coming from the company that popularized the wildcard convention for searching the filesystem. Come on, SQL Server team. Wildcards need to be improved!

Phil Factor

Phil Factor from SQLServerCentral.com

» Join the debate, and respond to today's editorial on the forums

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. These headlines are gathered throughout the week and are posted in real time at the website. Check there for information throughout the week or enjoy this weekly summary of the SQL Server world.

Webinars

Year in review with Kendra, Steve, Grant and Kathi: The best of 2018 and our predictions for 2019 - Join Microsoft Data Platform MVPs Kendra Little, Steve Jones, Kathi Kellenberger and Grant Fritchey live to discuss the highlights they’ve seen in 2018 and what cool things they hope to be surprised with in 2019. Along the way they'll share their own personal moments of glory, and favorite goofy memories as well....(more)

Virtualization and Containers

How many Cores per Socket do I need? - Klaus Aschenbrenner tackles an interesting topic in VMware vSphere: the Cores per Socket option, and how you should configure it for your SQL Server based Virtual Machine....(more)

Microsoft to stop supporting its Azure Container Service in January 2020 - Microsoft will no longer support its Azure Container Service (ACS) as of January 31, 2020. For Kubernetes users, the Microsoft way forward is AKS, its dedicated Kubernetes Container Service....(more)

Attaching databases via a dockerfile – UPDATE - Andrew Pruski devises a better way to attach databases to SQL Server running in a Linux container....(more)

Setting up SQL Server replication in containers - Andrew Pruski figures out how to set up replication for SQL Server running in docker containers....(more)

Vendors/3rd Party Products

Database Continuous Integration with SQL Clone and SQL Change Automation - When you are working as part of an application development team, it is worth settling down into a routine of doing a daily build from source control, and then provisioning test and development instances of the database....(more)

T-SQL

Synonyms in SQL Server – Good and Bad - Despite being around since SQL Server 2005, Jason Brimhall thinks synonyms are an often under-utilized feature. More importantly, they are often implemented in a very bad way....(more)

MERGE: Updating Source and Target Tables Located on Separate Servers - Using the MERGE statement, we can change data in a target table based on data in a source table. Using it, we can execute INSERT, UPDATE and DELETE on the target tables within a single query block. ...(more)

T-SQL Tuesday 109: To influence and to be influenced - Jason Brimhall encourages us to call out times when something you did influenced someone , and you were in turn influenced by something that person said or did. ...(more)

Technique and Simple Utility to Determine the Datatype of a Scalar T-SQL Expression - You find some SQL that is using CAST to convert an expression of one data type to another. But what type will it be? Louis Davidson just has to know......(more)

Naming Convention Hall Of Shame - Tim Mitchell explains the value in establishing a set pattern for naming and formatting....(more)

How to stop the SQL Scheduler with T-SQL - Create a list of currently enabled jobs, store this list in a physical table, execute a loop or cursor on it that executes a procedure that disables those jobs. Once the maintenance is complete, run another script that goes back through that list and re-enables them....(more)

The Default Frame for Window Functions - When you write a Window function, there is an implicit default frame for the windows that you might not be aware of....(more)

Closest Match, Part 1 - Itzik Ben-Gan sets a T-SQL Challenge to match to each row from Table 1 (T1) the row from table 2 (T2) where the absolute difference between T2.val and T1.val is the lowest....(more)

Visualizing Nested Loops Joins And Understanding Their Implications - Everyone has their own method of reading an execution plan when performance tuning a slow SQL query. Burt Wagner explains why it's worth taking a look at the kind of join operators that are being used....(more)

SQL Server News

Cumulative Update #15 for SQL Server 2014 SP2 - The 15th cumulative update release for SQL Server 2014 SP2 is now available for download at the Microsoft Downloads site....(more)

Announcing SQL Server 2019 community technology preview 2.2 - With this preview, Customers can now use SparkR from Azure Data Studio on a big data cluster. They can also use UTF-8 character encoding with SQL Server Replication....(more)

Cumulative Update #1 for SQL Server 2014 SP3 - The 1st cumulative update release for SQL Server 2014 SP3 is now available for download at the Microsoft Downloads site. ...(more)

Product Reviews and Articles

Book Review: Learn Amazon Web Services in a Month of Lunches - This book aims to teach you Amazon Web Services (AWS) in around 20 hours, how does it fare? Businesses are increasingly moving applications to cloud infrastructure, owing to its many advantages (e.g. elastic scalability and cost). Amazon is the biggest player in cloud provisioning - so it makes sense for IT professionals to learn something about the technology underlying this change....(more)

PowerShell

Revised Everything PowerShell Prompt - Jeffrey Hicks has the King of PowerShell Prompts....(more)

PowerPivot/PowerQuery/PowerBI

Clean Data = Happy Analytics - Power BI is incredibly easy to use and a robust analytics tool, but if your data is rubbish, well, even its going to fail to give you the results you hoped for. Lucky for all of us, even when working with large data sets, it can help you identify problems in the data quality....(more)

Multiple Layers of Aggregations in Power BI; Model Responds Even Faster - Aggregations are speeding up the model. However, the aggregated table is not just one table, It can be multiple layers of aggregations. Aggregation by Date, aggregation by Date and Product, aggregation by Date and Product and Customer. Having multiple layers ensures that you always have the best performance result possible, and you only query the DirectQuery data source for the most atomic requests....(more)

Data Model Options for Power BI Solutions - At the heart of every a Business Intelligence reporting solution is a data model, to optimize queries and enable ad hoc report interactions. ...(more)

Custom Power BI Themes: Page Background Colors - There are two types of backgrounds in Power BI reports. The first is the Page Background, which is the background of the report itself. The second is the Wallpaper, which is the outer color surrounding the report....(more)

Credentials, Data Privacy Settings And Data Sources In Power Query/Power BI - Chris Webb tries to shed light onto how the process of setting data privacy levels works end-to-end....(more)

Financial Times Visual Vocabulary: Power BI Edition - The Financial Times Graphics team created the Visual Vocabulary poster to help all of us make better chart choices. Sal Jason was so inspired by Andy Kriebel's created interactive Tableau Edition of it that he decided to create the Power BI Edition....(more)

Performance Tuning SQL Server

Never Judge A Query By Its Cost - When tuning queries, or even finding queries to tune, there’s a rather misguided desire to look for queries with a high cost, or judge improvement by lowering query cost. The problem is that no matter what you’re looking at, costs are estimates, and often don’t reflect how long a query runs for or the actual work involved in processing the query....(more)

Don’t ignore the warning signs (in execution plans) - When you’re performance tuning, you can’t afford to ignore the warning signs. Andrew Daniels has lost count of the number of times he's found the issue with a query by looking at the warnings....(more)

Finding the Slowest Query in a Stored Procedure - Figuring out exactly what causes slow performance for a stored procedure can sometimes feel like trying to unravel a ball of Clark Griswold’s Christmas lights. Erin Stellato explains how Query Store can help....(more)

Does low fill factor affect SELECT performance? - Sometimes good intentions lead to big problems. Page splits seem to be causing a performance problem so you change the fill factor setting of a lot of indexes. Now imagine you thought the fill factor setting was for how much free space you wanted when creating or rebuilding an index, instead of how full....(more)

Graph Databases

SQL Server Graph Database of US Capitals - US Capitals is a popular data set for working with graphs. Nodes identify a state capital. An edge connects a capital in one state with the capital of a neighboring state....(more)

Graph Edge Constraints and a Crystal Ball - SQL Server 2019 introduces edge constraints for graph databases, which solves a couple of the key problems with the SQL 2017 implementation. Dennes Torres explains....(more)

ETL/SSIS/Azure Data Factory

Moving SSISDB is not as easy as it sounds - We’ve been doing a lot of upgrading recently and at one point had to move an instance from one 2016 server to another. In the process, we found out (the hard way) that it’s not that easy to move SSISDB....(more)

DevOps and Continuous Delivery (CI/CD)

Adopting Compliant Database DevOps at PASS - To help meet the requirements of the GDPR, PASS teamed up with Redgate to implement Compliant Database DevOps. This benchmark process has streamlined PASS’ database development pipeline, and strengthened their ability to uphold data privacy regulations such as the GDPR. ...(more)

Data Transfer (XML, JSON)

SQL Server 2019 Extensibility Framework and Java - Passing Data - Having looked at how to install and enable the Java language extensions, and write some basic Java code to ensure it all worked, Niels Berglund shows how we can pass data back and forth between SQL Server and Java....(more)

Computing in the Cloud (Azure, Google , AWS)

Azure Backup Storage Options - With SQL Server 2016 or higher, you can now backup directly to a URL, which is an endpoint into a cloud storage account, such as Microsoft Azure. ...(more)

How to Reference Azure Storage Files from Cloud Shell - How to reference the file share in Azure Storage to communicate with Azure Cloud Shell....(more)

Some differences with SQL Server when running on AWS RDS - Amazon RDS (Relational Database Service) is a managed service where Amazon looks after some aspects of your database server for you. In return you give up some of the control you would have with your own server or VM. So, what do you give up and what do you gain? ...(more)

Azure SQL Managed Instance

What Azure SQL DB Managed Instances Don’t Support (Yet) - Brent Ozar trawls though Azure SQL DB’s Managed Instance feedback forum, and finds reassuringly few challenging issues that users are still facing....(more)

Administration of SQL Server

Preparation for SQL Server installation - This series is for professionals who starts their journey with SQL Server administration and also for those who want to extend and structure their knowledge on SQL Server administration....(more)