The Complete Weekly Roundup of SQL Server News

Hand-picked content to sharpen your professional edge

As a community, we've often done well at providing standards for the way we do many aspects of database development, administration and management. There have been other areas where it has been too difficult to gain a consensus. SQL Naming conventions are one of these. Layout is another. Both are important for teamwork. I hate having to maintain databases that are poorly laid out, or have badly-named tables, procedures and functions.

Many organisations and individuals publish their own standards for SQL Server. Some of the ones I've read are excellent. Some are so rigorous that they allow some chores such as the creation of foreign key constraints to be automated. In general, though, they tend to find it difficult to advise on a standard naming convention for SQL Server objects, which includes everything listed in sys.objects, from Aggregate CLR functions (AF) to Extended stored procedures (X).

There have been attempts in the past. Joe Celko was courageous enough to tackle the topic in a book. Probably the most widespread attempt was the use of prefixes in 'reverse-Hungarian' notation. This wasn't a considered move but was due to two unrelated causes. Firstly, some system procedures and functions were given prefixes "sp_", "xp_" or "dt_", to signify that they were 'special' and should be searched for in master first. Secondly, use of the tbl_ prefix, for a table, often called 'tibbling', came from databases imported from Access. Unfortunately, this was an Access convention inherited from Visual Basic, a loosely typed language. There are established codes for SQL Server and a table is U, not tbl. However, SQL Server objects are unlikely ever to need a type-prefix because you will never be in any doubt as to the type of object you're using, if you know its name, schema and database; its type is there in sys.objects. Also, it is obvious from the usage.

Going beyond the prefix, I was very unsure how to name procedures and functions until PowerShell firmly set out its verb/noun convention, using standard verbs. It was well thought-out even before they went public with it. These standard verbs have been accepted and used without any criticism or issues. Obviously, our SQL verbs are different, but surely, we could do a similar exercise to create a meaningful naming convention? After all, we Get_InvoiceDefaults, New_PreparedStatement, Find_SimilarWord, and so on.

What seems important to me is that we should respect the idea of SQL being an intelligible language, based on written language. This would suggest that function names should fit into the semantics of the SELECT sentence. If we have a function that capitalises a sentence, i.e. makes the first character a capital letter if the word is longer than three characters (MLA), then you'd call it 'capitalized()'. Procedures would be verb-noun names of tasks, since they are executed.

I gather that there are many style guides for naming conventions out there. Shouldn't we as a community, be talking a lead by making things easier for anyone wrestling with SQL coding standards, by coming up with templates that organisations can adopt?

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

The Importance of Provisioning in Compliant Database DevOps - Compliance shouldn’t mean that you are forced to slow down your development process or compromise the quality of your work. In this webinar we will highlight the importance of provisioning as a part of your Compliant Database DevOps story and how it can accelerate your delivery processes while remaining in line with relevant legislation....(more)

The single question to predict success for DevOps, and what it means for change control - In this 30 minute session, Microsoft MVP Kendra Little will share and discuss her three key insights from the 'Gene Kim joins Redgate to discuss The 2018 Accelerate State of DevOps Report' webinar, and show why these insights make a compelling argument for rethinking your organization's change control process....(more)

Beyond SQL Server 2008 End of Life - Extended Support for SQL Server 2008 and SQL Server 2008 R2 will end on 9th July 2019. This could leave you in a vulnerable position. James Boother from Coeo exposes the problem, its scale and shares all the options you have for life beyond extended support....(more)

How to achieve true DevOps by including the database - As proven in the ‘The 2018 Accelerate State of DevOps Report’ including the database in DevOps initiatives greatly improves performance. In this webinar we will walk you through Redgate’s Database DevOps solution, showing reliable, scalable and repeatable processes for automating your database development and deployment....(more)

Virtualization and Containers

Building a Perfect Development Lab With SQL Server in Containers - Too much checkpointing of his VMs was causing a maintenance issue, so Dmitri Korotkevitch explores the use of Docker and SQL Server in containers as an alternative to VM-based virtualization....(more)

Vendors/3rd Party Products

Pseudonymizing a Database with Realistic Data for Development Work - For this demonstration, Phil Factor will take AdventureWorks and produce a pseudonymized copy for development work. The aim is to surgically alter just the data that can identify individuals but leave everything else intact....(more)

Avoid running out of Disk Space ever again using SQL Monitor - If your SQL Server runs out of disk space, and it is running a database for an enterprise’s trading application, then the company can’t take money until the DBA fixes the problem. Even the worry of that ever happening is enough to keep a DBA up at night. No wonder, then, that the recent State of SQL Server Monitoring survey confirmed that in 2018 disk space management is still the DBA’s seconds biggest administrative challenge....(more)

T-SQL

Approximate Distinct Count - Niko Neugebauer explores the APPROX_COUNT_DISTINCT function, available in the CTP 2.0 of SQL Server 2019....(more)

A Play Around With Table Variable Deferred Compilation in SQL 2019 - In the afterglow of Microsoft Ignite, people start tinkering with SQL Server 2019. Wave goodbye to trouble with table variables?...(more)

Using Extended Events to Capture Implicit Conversions - Implicit conversions can lead to poor performance. However, other than looking at every execution plan in your system, is there a better way to catch them? No! Actually, yes......(more)

Internal Query Processor Error Debugged - A DELETE operation that touches a filtered index deep in a tree of referencing tables provokes a mysterious Internal Query Processor Error....(more)

SQL Server Security

How does encryption affect your database performance? - Exploring how much longer some of the most common database operations take when the database is protected with Transparent data encryption (TDE)....(more)

SQL Server on Linux

SQL Server on Linux – External Memory Pressure with 2019 CTP2 - Anthony Nocentino explores how SQL Server on Linux responds to external memory pressure. He doesn't find all the answers, but the journey is pretty fun....(more)

SQL Server News

Cumulative Update #14 for SQL Server 2014 SP2 - You know the drill. Install it!...(more)

Software Development

Connecting to SQL Server 2017 using Visual Studio 2017 and getting “An incompatible SQL Server version detected” - If in doubt, upgrade Visual Studio....(more)

R Language

Running R scripts within in-database SQL Server Machine Learning - The title is a tongue-twister but the idea is simple: stack all the functions, classes, libraries and configurations you want to use in a specific environment and save it in a R file. ...(more)

PowerShell

PowerShell approved verbs - If you don't want Import-Module complaining at you, and who does, then you should use only approved verbs when writing functions or cmdlets....(more)

Powershell: Everything you wanted to know about arrays - What is an array, iteration with arrays, nested arrays, array lists, and a whole lot more....(more)

Testing PowerShell Modules with Pester - In the third article of this series on testing PowerShell code with Pester, Robert Cain demonstrates how to test the functions in a PowerShell module....(more)

PowerPivot/PowerQuery/PowerBI

Testing Power BI Premium Features With Power BI Embedded - Want to know whether Power BI Premium is worth the substantial investment, or to stick with Power BI Pro? All you need to do is create a new Power BI Embedded capacity in the Azure portal and assign a Workspace to it, and you’ll get access to all the Premium features. ...(more)

Transform text with exceptions in Power BI and Power Query - How to transform some words in a column to proper case, but leave others....(more)

Quick DAX : Word count - How to split text down to individual words to help count, rank or otherwise aggregate them, using the MID function to find the words....(more)

Data Profiling in Power BI - Matt Allington explains how the new Data Profiling tool will help you understand the overall shape and structure of the data, in Power Query, BEFORE you load it....(more)

Power BI and Azure ML make them work with Power Query - A step by step guide to using the Azure ML web service in Power BI (Power Query)....(more)

Performance Tuning SQL Server

Query Memory Grants and Resource Semaphores in SQL Server - Klaus Aschenbrenner explains Query Memory in SQL Server, and you how fast a lack of it can degrade the performance of your queries. ...(more)

Using NOLOCK? Here’s How You’ll Get the Wrong Query Results - Slapping WITH (NOLOCK) on your query seems to make it go faster – but what’s the drawback? Let’s take a look....(more)

Skewing Parallelism For Fun And Profit - It turns out that the yellow circle with 2 arrows that indicates parallelism...might by fibbing....(more)

SQL Server 2019 CTP 2.0 New Features – Introducing the Page Cracker (AKA sys.dm_db_page_info)! - A peek at a new DMF for troubleshooting page-related waits!...(more)

Query Store Missing Intervals - If there is no activity on your user database for any given interval then Query Store won't capture anything. The case of the Missing Intervals explained....(more)

NoSQL

The Netflix Media Database - A poke around some of the features of the new Netflix Media DataBase (NMDB)?—?a highly queryable data system built on the Netflix micro-services platform....(more)

HA/DR/Always On/Clustering

Removing a database from a replica in an Availability Group - Erin Stellato encounters a two-node Availability Group where multiple large-batch modification queries were causing a large redo queue on the replica. It had fallen so far behind that it made more sense remove the database from the replica and re-initialize. She explains how to do it, and how to avoid having to do it....(more)

Lazy log truncation or why VLFs might stay at status 2 after log clearing - Paul Randal explains why you might still see a lot of "active" VLFs in the log, even after truncation, when using mirroring and Availability Groups. ...(more)

ETL/SSIS/ELT

Polling in SQL Agent - Putting together a host of TSQL ingredients, including some naughty, undocumented extended stored procedures, the following recipe could be used as a SQL Agent job step to poll for a file's existence before running an SSIS package....(more)

SSIS and Pentaho – A Quick Comparison - How does Pentaho Data Integration (PDI) compare to SSIS? After a decade of working with the latter, Koen Verbeeck is well placed to draw a quick comparison between the two....(more)

Data Visualisation

Master Data Services in SQL Server 2019 - A functional HTML interface for MDS in SQL Server 2019! Can it be true? Niko Neugebauer is certainly glad to be alive to see it....(more)

For the Love of Font Size - As an editor, this exploration of the importance of legible fonts, font sizes and their use in structuring content, is compulsive reading....(more)

Data Privacy, Complianace, and GDPR

Monitoring Changes in Permissions, Users, Roles and Logins - Compliance means keeping a close grip on any changes to the permissions and access control of a database. Sadly, the law has had to acknowledge, from bitter experience, that it is not just external intruders who want to do this, but it could also be attempts at fraud or data theft from within the organisation. Permission changes are certainly one of the things that security experts advise you look out for; you need the equivalent of CCTV trained on your servers....(more)

Data Mining/Data Analysis

Sports Data in Transition | SQL Databases in Sports – Part 1 - Sports data can help athletes and their teams understand anomalies in performance, create more effective training regimes, and improve recovery time – just to name a few. With all this data being thrown about, what good would all this be – without a good way to structure it and query it? To put it bluntly, SQL is “considered a must" for those considering a sports analyst career....(more)

Conferences, Classes, and Events

What’s in my backpack - You know PASS Summit is approaching when SQL Server people start writing about their backpacks. ...(more)

What’s In My Bag, 2018 Edition - Brent Ozar reveals the electronic gear that's always in his organizer pouch, when traveling....(more)

Career Growth

T-SQL Tuesday #107 Round Up: Death March Project - Bedtime horror stories for SQL Server people....(more)

Azure DevOps

Use Azure CLI…I Beg You. - Not just for creating databases and a few servers/containers/vms, but for the entire STACK. This blog starts you off with a few tips and tricks, along with a 101....(more)

Administration of SQL Server

Announcing Public Preview of Accelerated Database Recovery - Accelerated Database Recovery (ADR) is a complete redesign of the current SQL Server recovery process, which "greatly improves database availability, especially in the presence of long running transactions"...(more)

Find Out Which Databases Have Multiple Files - Some simple queries to find databases with multiple database files and multiple transaction log files....(more)

Dropping Offline Databases? Be Careful! - Why you might want to bring a database back online before dropping it....(more)

Fix SQL Server with one click - Behind the, err, click-bait headline is news of a forthcoming tool from Randolph West that will be designed to set some of the default SQL Server configuration settings to more sensible values....(more)