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.

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.

Table Aliases - and an apology

First, I need to apologize for the lengthy interruption in my posting to this blog. For explanation I will just point to the Thanksgiving and Christmas holidays, the usual January crunch in the Shelby Systems offices when it is "all hands on deck" to answer Support questions, and also (perhaps primarily) the fact the fact that I hit a file-system permissions snag on the transactional replication process, and I have been unable to make more headway on that since my last post.

Anyway, while the transactional replication concept stews for awhile, I want to put out a quick post on using table aliases. This is a technique I have already used in a few earlier SQL query posts, in fact most of them, but I haven't given it an explanation of its own yet. Using a Support inquiry as my impetus, I will post that explanation now and revive this blog at the same time I address this question.

A table alias is nothing more than a "new name" for a database table. Table aliases are assigned in the FROM clause, and when they are used, all other clauses must reference the table by the alias instead of the original name. This applies to the SELECT clause as well. This may seem counter-intuitive because the SELECT clause appears before the FROM clause and thus "before" the alias has been assigned. But this is only in the visible order of operations. In the logical order of operations (the order that matters to the server), the FROM clause is interpreted first.

Table aliases are assigned simply by putting the alias name immediately after the table name in the FROM clause. An optional "AS" keyword can introduce the alias. If the alias contains a space character, brackets are required around the alias. Here is an example

SELECT Names.NameCounter, Names.FirstMiddle, Names.LastName
FROM Shelby.NANames AS NamesORDER BY Names.LastName, Names.FirstMiddle

Notice in this example that the alias of the NANames table is Names. The SELECT clause references the Names alias, not the original table name. And the ORDER BY clause also references the alias.

There are several advantages to using table aliases, including:

• The ability to reference the same table multiple times, as long as each as a different alias. (This is perhaps the biggest advantage.)
• The ability to give more meaningful names to tables.
• The ability to give shorter names to tables.
• The ability to type only once the fully qualified table name, which can include the database name, the schema name, and the table name, and instead use the alias alone for all other references to the table.

Because of these advantages, I use table aliases almost 100% of the time when I write queries. For examples in practice, view the query samples in these earlier blog posts:

Listing Husband and Wife on One Line
Calculating Age in T-SQL
Rollup and Grouping Functions
Pivoting Rows to Columns

Transactional Replication: A Journey into the Unknown Part 2

I worked some more on this transactional replication process this morning. I discovered that the snapshot creation problem was being caused by one or more "views" that included select * somewhere in them. I eliminated all my personal views and left only the "official" views created automatically by the Shelby v.5 system, and the snapshot creation process completed normally.

After this things went smoothly until I tried to initiate the first replication process, at which point I started getting errors saying that the "replication subsystem failed to load" and also that the process as "rejected" because the job was "suspended." It took more digging, but I discovered that all I needed to do was to restart the SQL Server Agent service. After I restarted it and tried again, the synchronization process "started successfully."

As I type this the first synchronization is still in progress. Its been running for several minutes, and I'm not sure when it will complete. I'm going to let it keep running and I'll post again once I have more to report.

For now, here are three things I learned about implementing Transactional Replication that was not included in the 1-2-3 steps of the SQL Server Management Studio wizard:

1. Make sure the SQL Server Browser service is running on the Principal/Distribution server(s).
2. Do not include any views as replication articles if they include select * anywhere in them.
3. After setting up the Subscription on the Subscriber server, restart the SQL Server Agent.

Knowing these three points would have helped me avoid several minutes of hunting down the causes of problems and then following several trouble-shooting steps. I'm sure there will be more to learn ahead.

Transactional Replication: A Journey into the Unknown Part 1

As excited as I am about the potential that an OLAP database would offer for reporting and other business intelligence solutions, I discovered yesterday that with regard to SQL Server concepts, high availability is currently more on the minds of our development team. As a result I have started exploring concepts such as replication, mirroring, fail-over databases, and such.

After reading over all the options, I have decided to try to create a transactional replication solution as a test case. My desktop will be the primary server (where the transactional database lives) and also the Publication server (where the database is "staged" for replication). My laptop will be the Subscription server (where the remote duplicated database lives). 

After installing SQL Server Management Studio and the Replication tools onto my laptop, I ran the SSMS wizards on both my desktop and my laptop. The desktop SSMS installation ran the Publication wizard without a hitch, excecpt that the SQL Agent didn't run. I started the SQL Agent service manually after the fact. At that point, everything appeared normal.

Then I ran the subscription wizard. I had trouble getting the SSMS on the laptop to connect to the desktop server instance until I figured out that the SQL Server Browser service was needed but was not running. I started the browser, and then the laptop connected to the desktop like a champ. I walked through the wizard without any trouble until I got to the message that the initial snapshot of the source database needed to be made before I could continue.

I checked the desktop SSMS replication properties to verify the existence of the snapshot, only to instead find out that the snapshot did not exist. I tried to run the SQL Snapshot Agent manually, but I started getting errors during the process. Something about GROUP BY and the need for LEFT OUTER JOIN. 

Well, this took me a couple more hours than I was expecting, so I will have to leave the troubleshooting of the Snapshot Agent for another day. But once I figure out what is happening with that, I'll post the next chapter in the transactional replication adventure.

Top 10 Things I Learned Thursday at SQL Pass Summit

Sorry for the delay in posting this last Top 10 list of things I learned at the SQL PASS Summit in Seattle. Here is the final list:

10. The Paramount Hotel is probably where I need to stay next year if I get to come back.
9. A data mart/data warehouse solution would answer a lot of the temporal reporting challenges I have been unable to solve with straight T-SQL against the OLTP database.
8. The query execution process turns out to have three layers: the visible "typed" layer, the logical layer, and the physcial layer. I was only aware of the first two before.
7. Query optimization is more arcane and difficult than I would have ever thought (not that I ever thought about it).
6. The "shared data sources" and "mini-charts" in Reporting Services 2008 R2 will be a great help for self-service reporting.
5. The T-SQL function APPLY allows the query to apply a calculated table column or function to every row of the "left" table.  Believe me, it is much more useful than it sounds.
4. "ETL" means "extract, transform, and load."
3. I need to learn how to use the ETL functions in SQL Server Integration Services.
2. A "fact table" is a table comprised of measureable (i.e. usually numeric) "facts" along with a set of keys to to the facts to "dimension tables" comprised of descriptive (i.e. usually non-numeric) "dimensions."  Together these two tables form a "star schema," the fundamental structure of data marts and data warehouses.
1. I need to learn how to create a data mart for the Shelby v.5 OLTP database.

As a result of SQL PASS Summit 2010, you will be reading a lot more about PowerPivot, data marts, OLAP cubes, and more. I certainly have enough to learn to keep me busy until next year's Summit!

Top 10 Things I Learned at SQL PASS Summit Today (Wednesday)

10. The joy of having the ability to play arcade games an unlimited number of times all night long is somewhat tarnished by two factors: a) the hundreds of other people in the same arcade with equally unlimited number of times to play and b) I am no longer 12 years old.
9. What I do is actually a hybrid of T-SQL development and BI development.
8. It is an interesting feeling being the SQL novice in every conversation during the conference.
7. I should never post proprietary code on my blog. I don't think I've done that exactly, but the references to the Shelby v.5 database might be a gray area on that one. I am going to have to reconsider my posting style, and I may change how I handle code that is specifically for Shelby v.5 data.
6. I really need to learn the DAX "language" to make PowerPivot a truly potent reporting tool.
5. PowerPivot has no innate "grouping" ability. This means that the query tables I make for PowerPivot usage need to have grouping columns. A little planning ahead will go a long way in making that easier.
4. SharePoint is growing in importance as a collaborative platform in the Microsoft world, meaning that it will be a necessary tool for managing BI tools.
3. I can set up a SQL Azure account for just under $10 a month to have my own little database-in-the-cloud architecture to play with.
2. Reporting Services uses VB .Net for its expression language.
1. I have not even scratched the surface of the amazing things Reporting Services can do.