Why doesn't a trailing comma after the last field matter when creating a table using T-SQL?
ddl, sql-server-2008-r2, t-sql
Solution
See http://connect.microsoft.com/SQLServer/feedback/details/273348/trailing-comma-allowed-in-create-table:
Description
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.
Microsoft views this as a bug, but a minor one.
This was resolved some time ago as "won't fix" but we didn't explain why. Simply, this seems pretty harmless, and not worth fixing in a service pack. We may consider fixing this in a future release.
Problem
Shouldn't one of these statements work and one fail? Intuition says Statement 2 should fail because there is a comma after int and no second column listed. Yet both work and the trailing comma "," after the last column data type makes no difference. ``` -- Statement 1 CREATE TABLE dbo.MyTable1( col1 int); -- Statement 2 CREATE TABLE dbo.MyTable2( col1 int,); ``` However (and this is expected): two commas ",," after the last field do cause a failure: ``` -- Statement 3 CREATE TABLE dbo.MyTable3( col1 int,,); Msg 102, Level 15, State 1, Line 1 Incorrect syntax near ','. ``` Testing shows that its not just any character after the last field that is allowed through. For example, this fails: ``` -- Statement 3 CREATE TABLE dbo.MyTable3( col1 int ~); Msg 102, Level 15, State 1, Line 1 Incorrect syntax near '~'. ``` Maybe SQL Server is "saving a seat at the table" for something? The Primary Key perhaps? I really don't know. I am using Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64).