Model Good Behavior

I'm answering a question in the forums and I spot something that crawls up my spine:

;WITH...

The person was using a Common Table Expression (CTE) which requires that the preceding statement in the batch have a statement terminator, the semi-colon. However, since the terminator isn't required everywhere, lots of people don't use it at all. And then, when it comes to writing a CTE, for convenience, they stick the terminator, you know, the thing that goes at the end of something, at the beginning of the CTE definition. Only, this time, they were trying to use two CTEs and had the terminator in there twice, obviously breaking the code.

Why would they think they're supposed to put that semi-colon at the front of the CTE? Because it's been modeled to them that way. For convenience, and repeatability, and just to reduce the number of complaints, a lot of people posting example code, put the terminator in front of their WITH clause. I get it. Those are reasons to model, what is actually a poor practice, as a way to make things easier. Same with using SELECT * in example code. You also see a lot of example code with all sorts of other bad behaviors because, hey, it's just an example. You also see it in other kinds of code, where the "Hello World" example, is poorly structured and badly written, but, hey, it's just an example.

And then we wonder why so many people are writing so much bad code. Well, we're teaching them to write bad code.

Look, if we want people to do things correctly, we need to model that good behavior. It's how you teach. Do the things you know to be right, all the time, in order to show others that's how they should be getting stuff done. Is it inconvenient to remove SELECT * from your example code? Good gosh yes. I'm actively rewriting one of my books and I'm throwing out almost every instance of SELECT * (except a couple where I use it on purpose to illustrate a point). It's work. However, it's necessary. If I'm going to teach people how to do things, I need to model good behavior, from the start, and all the way through. Even if it inconveniences me (and yeah, worth mentioning, I was dragged to this point of view in part by Hugo Kornellis, I'll let him tell the story).

OK. You can start flaming me now.

Grant Fritchey

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

This article demos a novel way to report on the progress of your Flyway development project. It provides both SQL and PowerShell versions of code that extracts information for each database version from the Flyway schema history table and then plots it in a Gantt chart.

I have a post correcting a statement I made before...

Testing impact of Transparent Data Encryption on ...

For this month’s T-SQL Tuesday, I discussed my c...

Many SQL Server DBAs manage jobs through the SSMS ...

Database servers are used for storing vast amounts...

Beginning with today, I want to give you over the ...

Another post for me that is simple and hopefully s...

SQL Server has a very useful function called DATED...

(2022-Jan-27) When Hogarth, a nine-year-old boy, ...

Today, I worked on a service request that our customer reported that running a complex query this is executing in parallel but having more than 2 vCores in Azure...

In this article we look at the SQL Server function LOGINPROPERTY to retrieve various settings for any SQL Server login.

This article will describe how to get a list of SQL Server agent jobs without notifications using T-SQL . Read more here The post SQL Server agent jobs without notification...

Microsoft recently released Cumulative Update 15 for SQL Server 2019. It contains a bunch of fixes and some improvements, I get a bit geeky with updates like this and...

Today's post describes SQL queries to get the following information about the processors of a SQL Server instance: 1. The total number of ph The post SQL Server – Retrieve...

This article will demonstrate the method to store comma-separated values into different columns using SQL Server(T-SQL) query. The post SQL Server - How to comma-separated values into different columns appeared...

As a SQL architect or database administrator, it is required to know how to get SQL Server configuration using T-SQL. This post will share The post Get SQL Server Configuration...

This article introduces how to solve the pirate game with a recursive solving algorithm in SAS, and how to analyze and visualize the law behind the complex logic of...

In this article we look at how to implement Azure ...

Azure Cosmos DB is the next-generation database that offers the highest performance, availability, and scale, built for globally distributed The post Azure Cosmos DB introduction appeared first on SQLServerCentral.

Provision your Azure Database for MySQL - Flexible...

Azure Functions are a serverless solution that allows you to write less code, maintain less infrastructure, and save on costs. Azure SQL bindings for Azure Functions make integrating your...

With the announcement back at Ignite that SQL Serv...

Documenting objects dependencies of ETL processes ...

When working with Azure data services it is nearly...

The initial phase of the global pandemic shocked nearly every industry, with in-person events effectively grinding to a halt. Industry conferences that millions of people had relied on for...

Moving to the cloud is all the rage. According to ...

In this article we look at how to use the AWS Launch Wizard to quickly deploy SQL Server Always On environment to support Availability Groups.

Azure Key Vault service throughput limits have bee...

The Azure Percept January update includes fixes re...

Azure Cost Management has a new tabbed experience ...

Data lifecycle management rules can help organizations drive down costs. Learn how to create lifecycle rules in part one of this two-part article.

Time to read: ~ 3 minutes Words: 571 Update: Learn...

A matrix with non-negative entries is said to be d...

Data on how organizations operate can inform how t...

Sometimes it is useful to know the extreme values ...

Many SAS programmers use macros. I have seen students in my SAS classes use several methods to activate their macros. One way is to load the macro in the...

The importance of data has increased multifold as ...

For organizations in today’s complex business en...

  I was recently thinking of SQL Server temporal ...

File and block are the predominant storage access protocols. Their characteristics make them suitable for different applications and workloads.

Data modeling comprises the methodologies of creat...

What is DevOps and how should organizations introd...

Lately, you’ve heard about DevOps. Is it just another buzzword, or is it something you should investigate? DevOps emerged over ten years ago to solve the problems of the...

This article explains the transfer database task in SSDT 2017. After reading this article, you will be able to transfer the databases between the same server or different servers....

Let’s consider the below table employee_depts  ...

Azure Data Factory, ADF, and exception MySql.Data.MySqlClient.MySqlException,Message=Got a packet bigger than 'max_allowed_packet' My StackOverflow developer profile specifies "I'd prefer to not work with" and honestly, the only...

Learn how to change the Master Key encryption password for an SQL Server Integration Services Catalog (SSISDB) database in SQL Server. The post T-SQL Query to change Integration Services Catalog...

A lot of videos and articles teach you how to writ...

When migrating an Oracle database to another platf...

To troubleshoot poorly performing SQL in Oracle, you must understand which transformations the optimizer has made. Jonathan Lewis demonstrates several possible optimizations for one query.

A common issue in performance is the lookup, key o...

Angus Croll Netflix is used by 222 million members...

In this tip we will look at some different options...

Foreign keys are used in database design to enforc...

This article helps you learn how to configure an O...

One of the great things about Power BI is how they...

Parameters for the Power BI Paginated visual can g...

A question I often get from many students is: “H...

How do you do a date range with the Paginated visu...

Vertipaq optimization is a critical component of the columnstore compression process. Edward Pollack explains how it works and what you need to know to manage it.

For those considering an IT career switch, 2022 ma...

It’s no shock to anyone that 2021 was a year of big change, much of it due to the continued effects the global pandemic has on how we do...

On January 27, 2022, Microsoft released SQL Server 2019 Cumulative Update 15. This is Build 15.0.4198.2. By my count, there are 35 public fixes and improvements in this CU,...

I will explain how to use and perform security testing using SQL Server vulnerability assessment in this article. I have explained basic understanding about multiple layers of security that...

As enterprise data security concerns grow, securit...

Disclosure also puts organizations in the awkward position of trying to mitigate a vulnerability without something like a vendor patch to do the job.

After companies accelerated their adoption of cloud infrastructure, remote workers are now insiders and pose significant risks, and costs, to companies.

Fewer than one-quarter of organizations believe they are fully prepared for a ransomware attack, threatening data privacy

A German activist is trying to track down a secret government intelligence agency. One of her research techniques is to mail Apple AirTags to see where they actually end...

Is it OK to have technical debt? A few times now I’ve been asked to define technical debt. It can be an ugly term if your role is a...

A while ago, jOOQ has added the org.jetbrains:annotations dependency to the jOOQ API, in order to annotate return types with nullability information. For example, the entire DSL is non-nullable:...

In yesterday’s post, I covered the resources I u...

There are many interesting questions related to date calculations. For example, I have seen the following questions Give a date, find the first Tuesday day of the previous week/month/quarter,... The...

In the last post I wrote about what ScriptDOM is and why it is useful. From this post, I will explain how it can be put to use. What...