The Sequel to SQL

SQL (originally SEQUEL) was envisioned as a fourth-generation declarative language that would require minimal training and that anyone could use to get information from databases. I remember reading the marketing froth when it was introduced. I was impressed. The concept was very much the zeitgeist, in the eighties and nineties. A Basic generator was released optimistically called 'The Last One' (it wasn't). Visual Basic was going to do away with all the labour of C or C++ programming (it didn't). 4GL languages (e.g. BusinessObjects) were developed that generated SQL under the covers. Breathlessly, the marketing men said it would put an end to SQL coding (it hasn't).

The industry is pock-marked with failed attempts to do away with the need for skilled professional developers. You'd have thought that anyone who popped their heads up above the parapet and declaimed loudly their brilliant idea that would do away with the need for skilled IT developers, they'd be generally considered an appropriate target. Bizarrely, it seems that many businesses are now betting on it happening. IDC's annual survey came up with results that enabled them to predict that within five years we will have a new 'developer class' producing 'code without custom scripting', and they will represent nearly a third of the developer population.

Apparently, this class of developer-less "code engineers" and "digital innovators"' will 'supplement traditional developers by leveraging visually guided development tools, low-code development platforms, no-code development platforms, and model-driven development tools to create and refine digital solutions.' Old grey-muzzled developers like me will wallow in the nostalgia. It will be like an 'eighties and nineties' revival party, but presumably without the mistakes. Remember the craze for outsourcing development work? Went well, didn't it? Err, no, quite the contrary in my experience.

Why doesn't the industry learn from their past mistakes, or even take an interest in what went wrong and why? Could it be that the wrong people in the industry learn from bitter experience? It is somewhat odd that so many of the opinion-formers on the technology of the IT industry have so little experience working in the industry as technologists. Few other professions are so clearly led by marketing people. The surgeon, for example, who eyes you up whilst fingering a scalpel doesn't often get his expertise and advice from non-medical marketing experts. The bridge you drive over during your commute was designed by engineers who were driven only by professional experience and technical knowledge, not soft-focus stock images in glossy brochures. The IT industry is odd in this way.

Clearly there is something wrong with the profession. We technologists are poor communicators and slow to point out the obvious technical issues with heavily marketed technologies and development practices. Instead, we occasionally look up from the terminal, shrug, do a little tinkering, and pop a new buzzword on our CV. It's easier.

Phil Factor

Phil Factor

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

Kenneth Igiri demonstrates a process you can use to ensure that data older than six months is moved out of a table in a clean fashion.

What’s using the most space, what’s getting used the most often, and what you might be able to clean out to make your job easier?

SQL Server caches object data in memory in the buffer pool. Understanding memory consumption by object can be crucial for performance.

Glenn Berry walks through the simple steps to upgrade from one SQL Server Edition to another, such as from Standard Edition to Enterprise Edition.

Hasan Savran shows how to use CosmosDB’s SQL API, which uses T-SQL like language to query Cosmos DB documents .

If you are, at minimum, running a Standard (S3) database, then you can now transform your row-store tables to the columnstore format without blocking incoming transactions

Just about every command you run against an Azure SQL Database requires you to supply the server name and the resource group name as parameters, so you need to get the list of server names for each resource group. Joey d'Antoni shows how it's done.

Whenever people talk about “The Cloud” I often hear: “cloud is expensive” and, sooner or later, “if performance aren’t good we can scale up in minutes later” .The mindset of taking care of performance issue by scaling up/down cloud resources is a golden egg goose for cloud providers, and a money sink for businesses.

Wednesday September 4 14.00-19.00 BST/ 08.00-13.00 Central - Register for our free virtual learning event, to enjoy educational and entertaining sessions from Microsoft MVPs and celebrate 20 years of Redgate.

Thursday August 29 18.00-19.00 BST / 10.00-11.00 Pacific - Register for this Microsoft hosted webinar to learn how Redgate tools for SQL Server help deliver software quickly.

Wednesday August 21 16.00-17.00 BST/ 10.00-11.00 Central - SQL Clone enables dev, test and CI environments to be created and refreshed in seconds, on demand or through self-service, with the latest copy of production data, masked for compliance.

Database DevOps offers the promise of quicker, easier and more secure deployments while bringing application and database development efforts in line with one another. But DevOps is not a one-size-fits-all solution to application delivery, nor is it meant to be a developer-first strategy that leaves DBAs and IT administrators behind.

Graph data is characterized by a very high relationship-to-entity ratio. If you have to model this in the relational world, the number of relationships will be too large and difficult to represent, let alone query on. This is the kind of problem that graph data modeling and querying helps us model and deal with.

Meagan Longoria shares her set of preferences for the design of Integration Services packages and ETL processes.

The SSIS Server Maintenance Job has been turned off for over a year, SSISDB has ballooned in size, and there is now only 10MB left on the data drive. The required retention period is only 14 days, but the native maintenance procedure simply can't cope with deleting so many rows. John McCormack explains how he got round this sticky problem.

There’s also loads of great free scripts and programs out there like sp_whoisactive, Ola’s maintenance scripts and the awesome dbatools project, and now there’s another free tool to add to your armoury; Aireforge Studio Community Edition.

Rounding out a few posts about SQL Server’s choice of one or more indexes depending on the cardinality estimates. Today we’re going to look at how indexes can contribute to parameter sniffing issues.

If you were struggling with tempdb contention, the best advice has always been to create multiple data files that are sized the same, with the same auto-growth settings, enable trace flag 1118 (and maybe 1117), and reduce your tempdb use. From the customer side, this has been the limit of what can be done, until SQL Server 2019.

Worried about why you're seeing the wait resource (0:0:0)? What should you do? Simple answer: ignore it and troubleshoot the other waits.

Erin Stellato discuses a common pattern of adding columns from the clustering key to the key definition for the nonclustered index and what this means when it comes to creating your nonclustered indexes.

A wonderful analysis of how a Clustered Index Seek...

Erik Darling explains the problems you'll encounte...

The general rule about leading column selectivity ...

Chris Webb shares some newly learned Power Query/M optimisation tricks that will give you some new ideas to try when optimizing your own queries.

Data sonification uses variations in audio to hear differences in data values. This post attempts to show how to produce audio tones in Power BI for greater accessibility. It also demonstrates how to blend data with a standard range of audio pitches.

Adding files, checking files in and out, viewing previous versions and reverting to previous versions.

You have an idea for a new process or a new product, or it might be a big change to existing processes or products. The challenge is getting it to happen, especially when doing so requires time from your coworkers.

Dan Luu discusses how file systems differ from each other and other issues we might encounter when writing to files.

You have a dataset with many variables, and you wa...

I love some of the new functionality in SQL Server 2019 but nobody’s perfect and occasionally the SQL Server team makes a mistake. A new feature introduced in SQL Server 2019 CTP 3.2, “Feature Restrictions”, is an unfortunate example of such a mistake. It’s a misguided attempt at improving security that not only increases the chances of SQL Injection, but it also prevented useful changes from being made.

Kevin Chant summarizes the current Linux distributions on which you can install SQL Server and explains a few of the pros and cons of each.

Demonstrating the different use cases where we can use LIKE operator to search for data from a table based on a specific pattern.

Nope, not even close. Brent Ozar demonstrates.

What you need to know to read an execution plan.

The same SELECT clause in some cases produced VARCHAR(MAX), but in same cases it cut the result to VARCHAR(8000) (or NVARCHAR(4000) )

Snapshot isolation is a great alternative to transactional isolation when you have read-heavy loads. If you want to use it with mixed read/write loads, prepare for update conflicts and be sure to keep an eye on tempdb performance and growth.

Phil Factor explains the problems you might encounter when adding a non-nullable column to an existing table or altering a column that contains NULL values to be non-nullable. He demos a migration script that can deploy such changes safely. You might also learn that in an archaic form of the Scots language, used in Cumberland, the number 17 is "tiny bumfit"; I think the tiny bumfit bus goes from Penrith to Carlisle.

Phil Factor demonstrates how to tackle builds when databases make cross-server references. The technique uses synonyms to represent the remote objects, and local 'stub' objects to overcome the problems caused by 'missing references' when building the individual objects. The final part of the article shows an automated technique using a Redgate tool (SQL Change Automation).

Erik Darling gets called in to tune a lot of prett...

I was working with containers recently with Jenkins. I didn’t want the server process running on my machine all the time, but I did need to allow some communication. Jenkins uses 8080 by default, but agents need another port. The answer? Multiple –p parameters.

You want to work with containers inside a virtual machine to do some automation testing. Is that possible? Yes, and very easy, as Grant Fritchey demonstrates.