Today’s puzzle is very interesting and my favorite puzzle to ask right at the beginning of the presentation. This puzzle helps to break the ice between audience while people are still settling in the audience. Let us see my favorite puzzle of Strange Behavior With Extra Comma in Create Table Statement.
In today’s puzzle, we are going to see two different scenarios where we will create tables.
Scenario 1: Create a Temporary or Permanent Table
Let us create a temporary table and permanent table with an extra comma at the end of the column list.
CREATE TABLE #testing (col1 INT, col2 INT,) GO CREATE TABLE testing (col1 INT,col2 INT,) GO
When you run the above script, SQL Server creates a table with two column. You can check that by running a select statement on it.
SELECT * FROM #testing; SELECT * FROM testing;
Please note that there is an extra comma at the end of the column definition. Even though there was no error and table was created successfully with two column.
Scenario 2: Create Table Variable
Now we will try to attempt to create a table variable where we will keep an extra comma at the end of the column definition.
DECLARE @testing TABLE(col1 INT,col2 INT,)
When you run above script, it gives us following error
Msg 102, Level 15, State 1, Line 55
Incorrect syntax near ‘)’.
After you see both of the above scenarios, the question for you is
Puzzle: Why is there an error in Scenario 2, when Scenario 1 just works fine?
Please leave your answers in the comments section. I will publish all the comments next week on Monday.
Reference: Pinal Dave (https://blog.sqlauthority.com)
15 Comments. Leave new
Good catch.
I’d assume the DECLARE error is due to limitations compared to the CREATE TABLE that, for some strange reason, are compiled differently (hence, error).
But the true question is: why isn’t there an error on the CREATE TABLE script?
The comma in the end should throw an error for both.
Back in the old days, this was Sybase SQL Server, part of the grammar was not standard. This is why it would accept “delete” instead of the required “delete from”, “insert” instead of “insert into”, etc.
The language was to be design was designed to be a simple one pass compiler and it did not have semicolons required at that time. The actual reason for letting a line and with the, is it made it easier to rearrange the punch cards that we assumed was the only possible way anyone would ever want to enter a program. Later, the product got closer and closer to standards and, at the end of the statement was treated as an error, which it should be.
Well, never face this type of situation but it looks like a bug in SSMS.
Since Temporary or Permanent Tables are allowed to create table level constraints after last column, Scenario 1 may work.
Default table level constraint might have created in scenario 1
or
Few syntax errors are ignored by SQL server.
Ex: select ‘100’+1 results 101.
Likewise Scenario 1 may work.
This is something to do with inline statements in SQL.
Declare is used to define variables or tables and have inline capability over the other.
Create table is standalone definition of creating a table.
This is something to do with inline capability of Declare statement.
Declare statement is used to define the variables or tables and let us define multiple variables in a single statement.
Whereas Create table is standalone definition for creating a table.
I don’t know but I guess it comes out of SQL Standard
I am not sure, but I think it may be related to constraint and index.
Create table command supports constraint and index creation within parentheses, where declare table does not.
DECLARE @testing TABLE(col1 INT,col2 INT, CHECK (col1 = col2), INDEX foo clustered (col1)) works on SQL Server 2014 at least.
When executing the CREATE TABLE command, a trailing comma following the last column is allowed. Based on the grammar in BOL and comma usage in lists in other T-SQL statements, this behavior is inconsistent. This is a very minor issue and does not appear to cause any adverse side-effects. It just appears that the parser may be a bit off.
For a real table (#temporary or permanent) you could specify addidional indexes, constraints etc after the last column. For a @table variable this is not possible.
So for scenario #1 it is valid, that you list something after the last column, while there are no circumstances, where could this be valid for #2. For this reason it does not allow the comma in #2, while it will be just ignored in #1.
The GO in the batch lets it work.
think thats an undocumented feature to support inline CONSTRAINT declarations
Look at the full syntax for CREATE TABLE. It’s pretty complicated. Now look at DECLARE TABLE. Straight forward. Basically, in CREATE TABLE, there’s a host of other options that can be applied, and I would imagine the coding for validation for each option is down a few levels within the logic for the option itself, whereas the contraints are fairy simple for DECLARE and can be parsed and validated fairly easily.
So, if that’s the case, all you do on the CREATE TABLE is specify a NULL option after the final comma, which matches no options and therefore no error is returned as no option gets to validate anything.
Makes sense to me!! ;-)
Hi Sai,
select ‘100’+1 results to 101 because int (1) has higher precedence than varchar (‘100’). So ‘100’ is converted to integer 100 and 1 is added to it resulting in 101.