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)