SQL: the Traditional Way, the Quick Way and the "Wait, What? Why?!" Way, in Database Weekly (06/27/2020)

The Appliance of Science

Nowadays, as an old fogey, young developers spot my grey muzzle and like to sit me down and explain in detail how Agile and development practices speed and empower development teams, and that DevOps processes are revolutionizing the speed and quality of application delivery.

"Not so fast..." I start. "Oh sorry. Do we need to explain it a bit more, slowly?", they ask, solicitously.

"Thanks, but no. I meant 'not so fast as it used to be'. Projects take longer than they did. The functionality is delivered more frequently, but in smaller packages."

This is a test to see whether the scientific approach has lapsed into dogma. Have I uttered a heresy? If so, are we, as software developers, scientists or priests? I could be entirely wrong in challenging the current orthodoxy, but that is not the point. My concern is that we've lost the science of software engineering, and for improving team productivity in development. Instead, it has become an act of faith that we have somehow, by evolving a particular 'modus operandi', broken through all the blocking factors in application development that have held up the delivery of applications.

That's not enough. Like any scientific, engineering, or technical idea, it needs to be tested against reality. It needs to be constantly challenged. It is not enough for marketing people to wave their arms around and insist that it's the ideal way. If that was the case, we'd see, across the whole industry, a noticeable fall in the number of humiliating failures in technology, or a rise in the delivery of innovation. More than that, we'd be somehow able to measure it objectively, and prove the point.

It is wrong to assume that all Software engineering used to be slow. In fact, some took very little time by our current standards. Curiously, some of the most rapid innovations Information Technology so far have been done by small core groups of very clever people, given unique opportunities at the perfect moment. All these factors must be in place. The results come fast. The Xerox Star, Unix, Lotus 123, Sybase, Wordstar, Microsoft Word, CP/M, Postscript, Pascal…the list is a long one, and I mention these only because I know about their history. They were all designed and written, originally, by fewer than a handful of very clever people very quickly and informally. Sure, they mostly support teams for specific tasks, but the core development team was small.

Although I love to examine the detail of IT initiatives that went well to find out why, my interest is in project autopsies. In engineering and construction, for example, failure is examined in forensic detail to determine the lessons to be learned, even if nobody dies. If we want to claim maturity as a profession, we need to be more assiduous in looking at IT disasters in detail, with the same objectivity, and build a bank of evidence-based best practices, learned in the school of hard knocks. In short, we need to act as scientists, not a priesthood.

Phil Factor

Join the debate, and respond to the editorial on the forums

Even though partitioning has been part of the SQL Server Engine for a number of years and versions, it’s not that common to see customers using it, so I decided to make a list with 5 cool things you can do when your data is partitioned.

Louis Davidson provides a pair of SQL Prompt snippets that will help you deal with dependencies, whenever you need to drop columns or tables.

Usage of Microsoft Azure is up by 15 percentage points compared to 2019 and it remains the most-used cloud platform. This is possibly as a result of increased remote working and the need to no longer rely on physical machines. For more insights into 2020 trends and challenges, download the State of Database Monitoring report here.

This July we're taking Redgate Streamed down under, bringing together expert industry speakers from Australia and New Zealand for a full day of online sessions, knowledge sharing and networking. Join us on Thursday July 2 8am - 2:30pm AEST / 4pm - 12am CDT

In this article, Diogo Souza explains how to use OAuth2 to authenticate users for GitHub in an ASP.NET Core application.

Six months ago today, you installed SQL Server Evaluation Edition. You told yourself you’d take care of that sooner or later, and…you forgot. Now, your SQL Server won’t start...

Is there any formula to calculate size of TempDB? It is not that simple...

I have always heard that if your database does have multiple log files, the first file is used in its entirety, then the second file will be used. And while that may have been the case in earlier versions of SQL Server, I stumbled upon something recently in SQL Server 2016 that makes wonder if that’s still how things work.

With modern server hardware, it is very easy to exceed SQL Server license limits on a two-socket server. It is also quite easy to exceed these limits on a VM. If you exceed the socket or core limits for a SQL Server Standard Edition instance, you will have several problems.

Last year when Windows Server 2019 was released I wanted to see which versions of SQL Server I could run on it, testing more the unwritten backward compatibility promise Microsoft has maintained over the last 45 years, rather than what the documentation says.

Keeping SQL Server instances patched can be a time-consuming task for DBAs. In this article, Alejandro Cobar explains how he created a service in Azure that anyone can use to retrieve the build information for SQL Server.

Azure DevOps doesn’t technically allow you to rename branches– but you can work around the issue by creating a new branch from master, setting the new branch as the default branch, and deleting the master branch.

Born out of Microsoft’s SQL Server Big Data Clusters investments, the Apache Spark Connector for SQL Server and Azure SQL is a high-performance connector that enables you to use transactional data in big data analytics and persists results for ad-hoc queries or reporting. The connector allows you to use any SQL database, on-premises or in the cloud, as an input data source or output data sink for Spark jobs.

SQL Server Big Data Clusters (BDC) is a new capability brought to market as part of the SQL Server 2019 release. BDC extends SQL Server’s analytical capabilities beyond in-database processing of transactional and analytical workloads by uniting the SQL engine with Apache Spark and Apache Hadoop to create a single, secure and unified data platform.

CU5 for SQL Server 2019 Big Data Clusters ushers in support for Red Hat OpenShift Container Platform, this is a big deal – but what exactly is OpenShift and more saliently; why does it matter ?.

Andy Mallon needs community feedback: What’s the purpose of a Code of Conduct? What do we want from a code of conduct?

A quick walk through on how you can backup your on premise SQL Servers to Azure BLOB storage.

Rob Sheldon explores the architecture of public cloud storage.

Throughout this series, I’ve discussed a range of storage-related topics, some of which I mentioned only briefly. One of those is cloud storage, which now plays a vital role in today’s data management strategies. Organizations of all types and sizes now employ cloud storage to varying degrees, either to supplement their on-premises systems or to handle the bulk of their data. Because cloud storage has become so pervasive, this article focuses exclusively on that topic, describing what it is, how it works, and its benefits and challenges.

James Phillips, VP of Corporate Technology at Mizuho, joins us to share their Database DevOps transformation story. Date and time: Wednesday July 1 & Friday July 3. Can't join us live, register to receive the recording.

Discover how Database DevOps and Octopus Deploy enable your organization to increase efficiency, reduce errors and get the most from your customer infrastructures. Date and time: Wednesday July 15 & Friday July 17. Can't join us live, register to receive the recording.

Discussing a few of the ways that PowerShell can better incorporate native executables into our object oriented world and how we can use these tools to better fit into our model of more discrete operations.

Having decided on Snowflake as your data warehouse of choice, how do you get one going? And how do you load data into it?

Grant Fritchey leans towards using artificial, or alternate, keys. Just make sure the natural key is enforced as well...

Struggling with ADF deployment? adf_publish branch doesn’t suit your purposes? Don’t have skills with PowerShell? I have good news for you. There is a new tool in the market. It’s a task for Azure DevOps Release Pipeline to deploy whole ADF from code (JSON files) to ADF instance in Azure.

In this article I use execution history data to measure the proportion of a data factory's activities (across all pipelines) executed during a full test run – the test suite's activity coverage.

This article describes the differences between HASONEVALUE and ISINSCOPE, which are two useful DAX functions to control the filters and the grouping that are active in a report.

DAX is simple, but it is not easy. The devil is in the details.

How to calculate the total business hours between a start date/time and an end date/time, taking into account the working days, public holidays and non-working weekends.

A great feature that has been continually improving is the Azure Data Studio notebooks. Not only can you run T-SQL notebooks now, but also PowerShell and python, using whatever kernel you desire.

Learn how to use the 'hybrid' approach in the Deployment Suite for Oracle to automate Oracle database deployments. In this approach, the team maintains the current state of the database in version control during development, then, at key stages, generates and tests the migrations script that will be used to deploy the new database changes safely.

Estimated plans can hide a lot of work from you. They’re sort of like government contracts, where the optimizer is the government: it picks the cheapest plan, but then…

Triggers use the version store in tempdb for access to the "special" trigger tables to access the previous and new versions of data being modified. Is this still true on databases using Accelerated Database Recovery (ADR) in SQL Server 2019?

Back with SQL Server 2019 CU2, I reported an error with PolyBase connecting to Excel when trying to select TOP(10) from the table. I’m using the Microsoft Access Database Engine 2016 Redistributable’s Excel driver. SQL Server 2019 CU5 fixes it.

If you’re struggling with data privacy errors in either the Power Query Editor in Power BI or Power Query/Get&Transform in Excel, then I hope this video will help you understand why you’re getting these errors and what you can do to avoid them.

Soheil Bakhshi shows how to produce a time dimension in seconds, and supporting time bands of 5 min, 15 min, 30 min, 45 min and 60 min.

List of improvements and fixes included in Cumulative Update package 5 (CU5) for SQL Server 2019.

This lesson will go through some of the fundamentals of querying XML values to extract information from the message body. when using Service Broker.

When you build a monitoring tool that sends advice via email, you discover two things: Some people have really, really big execution plans and Email servers have reasonable limits on file attachment sizes...

SQL Server 2019 tries to inline your functions using a technology they called Froid, but ever since Froid came out, it’s had one bug after another. And I don’t think it’s Microsoft’s fault. I think it’s your fault.

The "Traditional" way, the "Quick" way and the “Wait…what? Why!?” Way

Solving real-world problems is different than answering interview questions or twitter polls. The biggest difference is that real problems aren’t always fair. There’s not always a right answer.

Dear Vendors that encrypt stored procedures in SQL Server, Stop It!

Edward Pollack demonstrates some ways to design and populate a columnstore index to get even better performance.

Uwe Ricken discusses ways to affect the performance of queries involving heaps, including the TOP operator, compression, and partitioning.

Jess Pomfret needed to a list all the SQL Server instances on the server for which she was logging an issue, and find a cool T-SQL aggregate function to help.

I have a stored procedure. It’s a wonderful stored procedure. But something funny happens when a parameter gets sniffed...

I’ve heard many times incorrectly over the years that CTEs somehow materialize data. But a new one to me was that CTEs execute procedurally, and you could use that to influence plan shapes by always doing certain things first. Unfortunately, that’s not true of them either, even when you use TOP.

Since SQL Server is supported in containers, you can now run a SQL Server instance in your macOS without dual-booting or running a virtual machine. In this article of the series, Carlos Robles demonstrates how to connect to the SQL Server once it’s running.

How to create customized Docker container images, on Windows, for running SQL Server instances.