A community of more than 1,600,000 database professionals and growing

The Voice of the DBA

Will Terminators Be Required?

I was looking at an article the other day and noticed that there was a CTE sample with the semicolon on the line before the code. I've been seeing this convention for years, starting your CTE with a semicolon because people aren't sure this will get dropped in a batch with other code. It's not that the CTE needs this, but the previous statement needs to be terminated. There are a few other T-SQL constructs that require any previous statements to be terminated, and as a result, we have a series of strange publishing conventions for sample code.

I really wish that the language designers had thought this through and stopped trying to overload and reuse keywords. We could have avoided this with a simple CTE language element to indicate the structure. I know, I know, there are other considerations, but this seems annoying. I'm sure that the addition of the CTE fully expected that at some point semicolons would be required for all code.

Brent wrote about this a few years ago. The Syntax page for T-SQL currently says this about the semicolon: "Transact-SQL statement terminator. Although the semicolon is not required for most statements in this version of SQL Server, it will be required in a future version." There is no shortage of confusion about where terminators might be required and how to structure code, partially because SQL hasn't ever used terminators and the evolution of the language has been a bit inconsistent with regard to structure.

These days it seems that nothing will ever be removed. It appears that nothing else will be deprecated in this age of cloud software and feature toggles.I suspect at this point that we'll see features wither in the codebase, not receiving future development if Microsoft doesn't see them as valuable, living in limbo forever.

I don't think we'll ever see terminators required, and as the amount of legacy code grows, it becomes less and less likely they will become mandated.

Steve Jones from SQLServerCentral.com

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

The Voice of the DBA Podcast

Listen to the MP3 Audio ( 3.2MB) podcast or subscribe to the feed at iTunes and Libsyn.

The Voice of the DBA podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music.

How to track every change to your SQL Server database

See who’s changing your database, alongside affected objects, date, time, and reason for the change with SQL Source Control. Get a full change history in your source control system. Learn more

Reducing Attack Surface in DevOps

Join Redgate’s Data Privacy and Protection Specialist Chris Unwin to explore the breach and hacking epidemic, and how this is impacting the way we work, handle, and protect our data.
Register now

Featured Contents

SQL Server Data Table as CRM Data Source on VBA Form

Thomas Stricker from SQLServerCentral.com

Using SQL SERVER Database as Data Source FOR MS Access VBA Switchboard and Data Entry Form. I am presenting, once again, a method of connecting and upgrading a SQL data source into a VBA presentation process. More »

How to Successfully Deliver IT Projects in a Not-so-Agile Organization

Additional Articles from SimpleTalk

Agile methodology for software delivery is accepted at many organizations but is not used everywhere. In this article, Mohammad Rizvi gives advice on how to successfully implement a software project in a non-Agile environment. More »

The ‘Right to be Forgotten’ and Data Masker for SQL Server

The right to be forgotten is one of the main features of new data protection legislation across the globe. Under Article 17 of one such piece of legislation in Europe, the GDPR, individuals have the right to have personal data erased from all systems and data the company may be storing about them. More »

From the SQLServerCentral Blogs - Index on Key vs Included

SQL_Girl from SQLServerCentral Blogs

Indexing can be quite confusing at times if you not 100% sure on what to do. Do you just index... More »

Question of the Day

Today's Question (by Steve Jones):

I create this trigger:

 CREATE TRIGGER StopAlter ON DATABASE FOR ALTER_TABLE AS ROLLBACK;

Now I want to drop it. How do I do this?

Think you know the answer? Click here, and find out if you are right.

We keep track of your score to give you bragging rights against your peers.
This question is worth 1 point in this category: DDL Triggers.

We'd love to give you credit for your own question and answer.
To submit a QOTD, simply log in to the Contribution Center.

Expert T-SQL Window Functions in SQL Server

Expert T-SQL Window Functions in SQL Server takes you from any level of knowledge of windowing functions and turns you into an expert who can use these powerful functions to solve many T-SQL queries. Replace slow cursors and self-joins with queries that are easy to write and fantastically better performing, all through the magic of window functions. Get your copy from Amazon today.

Yesterday's Question of the Day

Yesterday's Question (by Steve Jones):

I have a SQL Server 2017 instance. I want to verify my backup file and decide to run RESTORE VERIFYONLY on my backup file. What does this do?

Answer: Checks the backup set if complete and volumes are readable, a portion of the headers, checksums, and if there is sufficient space on destination drives

Explanation:

The RESTORE VERIFYONLY checks:

That the backup set is complete and all volumes are readable.
Some header fields of database pages, such as the page ID (as if it were about to write the data).
Checksum (if present on the media).
Checking for sufficient space on destination devices.

Ref: RESTORE VERIFYONLY - click here

» Discuss this question and answer on the forums

Database Pros Who Need Your Help

Here's a few of the new posts today on the forums. To see more, visit the forums.

SQL Server 2016 : SQL Server 2016 - Administration

Always On FailureConditionLevel and Session Timeout - Hi All, Over the past week, I've been monitoring my AOAG setup and I've noticed around the same time each night,...

SQL Server 2016 : SQL Server 2016 - Development and T-SQL

Best way to SELECT with one Case Expression - I have three seperate tables that can be joined to eachother. For examples sake We'll call them three tables MainTableOne,...

SQL Server 2012 : SQL 2012 - General

????????????|?????|Q/?10440772|KPU??????|???????????|???? Kwantlen Polytechnic University - ???(??:10440772)???????????????????????????????????????????V?????????????--?????V ?:10440772???---- ? ???????????????????????????????: ???????,?????????????????,?????????????? ???????????????????a,??????????????,????????????????,?????????????????,?????? ??? ?????,??,????,??????,??????????????????,????????????????,????????????,??????? ??,???????????????,???????

???????????|?????|Q/?10440772|VIU??????|??????????|???? Vancouver Island University - ???(??:10440772)???????????????????????????????????????????V?????????????--?????V ?:10440772???---- ? ???????????????????????????????: ???????,?????????????????,?????????????? ???????????????????a,??????????????,????????????????,?????????????????,?????? ??? ?????,??,????,??????,??????????????????,????????????????,????????????,??????? ??,???????????????,???????

???????????|?????|Q/?10440772|SFU??????|??????????|???? Simon Fraser University - ???(??:10440772)???????????????????????????????????????????V?????????????--?????V ?:10440772???---- ? ???????????????????????????????: ???????,?????????????????,?????????????? ???????????????????a,??????????????,????????????????,?????????????????,?????? ??? ?????,??,????,??????,??????????????????,????????????????,????????????,??????? ??,???????????????,???????

?????????????|?????|Q/?10440772|UBC??????|????????????|???? University of British Columbia - ???(??:10440772)???????????????????????????????????????????V?????????????--?????V ?:10440772???---- ? ???????????????????????????????: ???????,?????????????????,?????????????? ???????????????????a,??????????????,????????????????,?????????????????,?????? ??? ?????,??,????,??????,??????????????????,????????????????,????????????,??????? ??,???????????????,???????

??????????|?????|Q/?10440772|NU??????|?????????|???? Nipissing University - ???(??:10440772)???????????????????????????????????????????V?????????????--?????V ?:10440772???---- ? ???????????????????????????????: ???????,?????????????????,?????????????? ???????????????????a,??????????????,????????????????,?????????????????,?????? ??? ?????,??,????,??????,??????????????????,????????????????,????????????,??????? ??,???????????????,???????

???????????|?????|Q/?10440772|UVic??????|??????????|???? University of Victoria - ???(??:10440772)???????????????????????????????????????????V?????????????--?????V ?:10440772???---- ? ???????????????????????????????: ???????,?????????????????,?????????????? ???????????????????a,??????????????,????????????????,?????????????????,?????? ??? ?????,??,????,??????,??????????????????,????????????????,????????????,??????? ??,???????????????,???????

?????????|?????|Q/?10440772|Lakehead??????|????????|???? Lakehead University - ???(??:10440772)???????????????????????????????????????????V?????????????--?????V ?:10440772???---- ? ???????????????????????????????: ???????,?????????????????,?????????????? ???????????????????a,??????????????,????????????????,?????????????????,?????? ??? ?????,??,????,??????,??????????????????,????????????????,????????????,??????? ??,???????????????,???????

????????????|?????|Q/?10440772|UOI??????|???????????|???? University of Ontario Insititute - ???(??:10440772)???????????????????????????????????????????V?????????????--?????V ?:10440772???---- ? ???????????????????????????????: ???????,?????????????????,?????????????? ???????????????????a,??????????????,????????????????,?????????????????,?????? ??? ?????,??,????,??????,??????????????????,????????????????,????????????,??????? ??,???????????????,???????

??????????|?????|Q/?10440772|Laurentian??????|?????????|???? Laurentian University - ???(??:10440772)???????????????????????????????????????????V?????????????--?????V ?:10440772???---- ? ???????????????????????????????: ???????,?????????????????,?????????????? ???????????????????a,??????????????,????????????????,?????????????????,?????? ??? ?????,??,????,??????,??????????????????,????????????????,????????????,??????? ??,???????????????,???????

??????????|?????|Q/?10440772|Ryerson??????|?????????|???? Ryerson University - ???(??:10440772)???????????????????????????????????????????V?????????????--?????V ?:10440772???---- ? ???????????????????????????????: ???????,?????????????????,?????????????? ???????????????????a,??????????????,????????????????,?????????????????,?????? ??? ?????,??,????,??????,??????????????????,????????????????,????????????,??????? ??,???????????????,???????

?????????|?????|Q/?10440772|Trent??????|????????|???? Trent University - ???(??:10440772)???????????????????????????????????????????V?????????????--?????V ?:10440772???---- ? ???????????????????????????????: ???????,?????????????????,?????????????? ???????????????????a,??????????????,????????????????,?????????????????,?????? ??? ?????,??,????,??????,??????????????????,????????????????,????????????,??????? ??,???????????????,???????

??????????|?????|Q/?10440772|Ottawa??????|?????????|???? UNIVERSITY OF Ottawa - ???(??:10440772)???,???,???,???,?????,?????,???,???,???,???V????????????? - ?????V?:10440772???---- ???????,?,?,?,????????????????????:

??????????|?????|Q/?10440772|Brock??????|?????????|???? Brock University - ???(??:10440772)???????????????????????????????????????????V?????????????--?????V ?:10440772???---- ? ???????????????????????????????: ???????,?????????????????,?????????????? ???????????????????a,??????????????,????????????????,?????????????????,?????? ??? ?????,??,????,??????,??????????????????,????????????????,????????????,??????? ??,???????????????,???????

BCP Error - Hi All: I'm trying to import data using bcp however, my I keep getting the below message. I have checked the...

SQL Server 2008 : SQL Server 2008 - General

While loop runs forever with no records in cursor object - Dear Friends, I am new to TSQL programming . Need your help in making this SP work. The first test case I...

Cursor object running when @@FETCHSTATUS 0 - Dear Friends, I am new to TSQL programming . Need your help in making this SP work. The first test case I...

SQL Server 7,2000 : General

Transaction Log when FIXED size, does it hang? - If I restrict the Transaction log to a fixed size (no auto growth) and then do a large delete, what...

Microsoft Access : Microsoft Access

Remove duplicates Access - In SQL Server this is much easier because I can use Window functions and ROW_NUMBER()... Is there an easy way...

This email has been sent to newsletter@newslettercollector.com. To be removed from this list, please click here.
If you have any problems leaving the list, please contact the webmaster@sqlservercentral.com.

This newsletter was sent to you because you signed up at SQLServerCentral.com.
Feel free to forward this to any colleagues that you think might be interested.
If you have received this email from a colleague, you can register to receive it here.