A technical blog about my projects, challenges, and discoveries in the world of data warehousing using SQL Server, Power BI Desktop, DevExpress, and more.

Tuesday, March 8, 2011

The Semicolon and Common Table Expressions

In the last few months I have begun using Common Table Expressions. These are subqueries that could be used in the FROM clause of the primary query, but instead they have been moved into a WITH clause before the primary query statement, even above the SELECT clause.

Although I will explore Common Table Expressions in later blog posts, I just want to document something I discovered in relation to using them in ShelbyQUERY. Curiously, what I discovered is also related to the little-used semicolon punctuation mark.

The semicolon is the terminator symbol for a T-SQL command. This includes query statements and other functions supported in T-SQL. Therefore every query written in T-SQL could -- some might say should -- end in a semicolon to indicate the end of the statement. However, I will admit that none, or almost none, of the queries I write end with a semicolon. This is because the semicolon is optional in the great majority of situations. There is one situation where the semicolon is not optional, and that is for any T-SQL command that precedes a WITH clause to define common table expressions.

This is particularly important to note in ShelbyQUERY because experience has shown that ShelbyQUERY actually prefixes one or more T-SQL commands in front of the actual query that is written in the text editor. Although I do not yet know what these commands are exactly, I suspect they are there to enforce the table-level security restrictions of the Supervisor user security setup and also the General Ledger user account restrictions. Whatever the commands are, it has become clear that sometimes (though not always) the final command prior to the actual query does not terminate with a semicolon.

Of course in the vast majority of cases, the lack of a semicolon on the prefixed command is moot. However, if the query is to begin using WITH and common table expressions, it is definitely not moot. In such a case the lack of a semicolon on the prefixed command with trigger a syntax error near the word WITH.

The simplest solution is to add a semicolon before the word WITH, either on a line above it or just in front of it on the same line. The fact that T-SQL is not picky about spacing means that you can put it anywhere you like, as long as it is in front of the word WITH.

While I'm on the topic, I will also caution anyone out there who uses variables in your queries. If you put DECLARE and SET statements in front of a WITH clause, you also need to put the semicolon after the last statement prior to the word WITH. Of course, you could also put the semicolon after every complete statement. Putting the semicolon only in front of the word WITH is the minimum requirement, but it is certainly more rigorous to put one after every complete T-SQL statement.

Monday, March 7, 2011

Discovering the Compatibility Level of a Database

Even though a database is running in a particular version of SQL Server, it can have a compatibility level set to a prior version. If this is the case queries that rely on features implemented in the current version of SQL Server may fail to work on your database even though the SQL Server version meets the requirements.

To find out your database's compatibility level, run the following query:

select name, compatibility_level from sys.databases

The number corresponds to the version of SQL Server as shown below:

80 = SQL Server 2000 (or MSDE)
90 = SQL Server 2005
100 = SQL Server 2008

If the compatibility_level value is lower than the version of SQL Server you are using to host the database, you can change the compatibility level to match by using the SQL Server Management Studio software on the server. Right-click on the database name and choose Properties. Then choose the Options page and set the Compatibility Level drop-down option to the highest level available, which will be the same as the version of the SQL Server host.

Followers