comma in SQL

Question

Post reply

comma in SQL

sqlfriend

SSC Guru

Points: 52769
More actions
August 9, 2012 at 5:22 pm

#265807

I just found out we have a sql script to create a table.
CREATE TABLE [dbo].[DayList](
[DateID] [int] NOT NULL,
[EnrDate] [int] NOT NULL,
[PrevDate] [int] NULL,
[NextDate] [int] NULL,
[RunDate] [int] NOT NULL,
)
my quesiton is the comma at the end before the ).
I thought it is an error, but I found it works too, if a comma is added there, why is that?
Thanks

Viewing 15 posts - 1 through 15 (of 21 total)

You must be logged in to reply to this topic. Login to reply

Dwain Camps SSC Guru Points: 86908 More actions · Answer 1

Interesting and you're right it doesn't throw a syntax error!

You can follow the comma with more columns or something like this:

PRIMARY KEY ([DateID]

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

deepzzzz SSCrazy Points: 2890 More actions · Answer 2

deepzzzz

SSCrazy

Points: 2890

August 10, 2012 at 4:51 am

#1524324

Why it is not an error?

Thanks

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 3

deepzzzz (8/10/2012)
Why it is not an error?

Because it isn't. Probably a minor bug in the syntax checker.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

sqlfriend SSC Guru Points: 52769 More actions · Answer 4

It is wierd it didn't return any error at all, and the table is created successfully.

CELKO talked aobut DDL of my post, but here I only ask the question about the extra comma,

If you can create any table using this SQL with extra comma,

It will be the same, no error.

davidandrews13 SSCertifiable Points: 6792 More actions · Answer 5

there's an answer at the bottom of this page

http://msdn.microsoft.com/en-us/library/ms174979(v=sql.105).aspx

it states:

Allowing an extra comma after the last column definition makes the code easier to generate and maintain because all lines can end in a comma, instead of all lines except the last one. $0In C and C# this same pattern appears in several places e.g. C#'s object initializer syntax.

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 6

sqlfriends (8/10/2012)
CELKO talked aobut DDL of my post, but here I only ask the question about the extra comma,

Don't worry, that's what he does.

Most people just ignore him and go on with their lives.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

Koen Verbeeck (8/10/2012)
sqlfriends (8/10/2012)
CELKO talked aobut DDL of my post, but here I only ask the question about the extra comma,
Don't worry, that's what he does.
Most people just ignore him and go on with their lives.

Or we try to and don't always succeed.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 8

CELKO (8/11/2012)
CELKO talked aobut DDL of my post, but here I only ask the question about the extra comma
A reflex on my part. I assume that people want to learn and become better SQL programmers, not just kludge their code. What you posted was weird so I was interested.
MicroSoft has a screwed up parser and they are slowly fixing it. Seen the semi-colon in front of the WITH keyword? The MERGE statement requiring a semi-column?
Originally, Sybase T-SQL did not need a semi-colon to terminate a statement. But later, we made Standard SQL into an LALR(1) grammar. A guy named Steve Feltz checked this after every X3H2 meeting and he corrected the BNF for the committee.

I don't mind terminating SQL statements with a semicolon (;). Pet peeve though is starting CTEs with a semicolon (;). Sorry, the semicolon is a statement terminator, not a statement begininator.

Jeff Moden SSC Guru Points: 1003894 More actions · Answer 9

sqlfriends (8/9/2012)
I just found out we have a sql script to create a table.
CREATE TABLE [dbo].[DayList](
[DateID] [int] NOT NULL,
[EnrDate] [int] NOT NULL,
[PrevDate] [int] NULL,
[NextDate] [int] NULL,
[RunDate] [int] NOT NULL,
)
my quesiton is the comma at the end before the ).
I thought it is an error, but I found it works too, if a comma is added there, why is that?
Thanks

Now that the dilema of the trailing comma has been solved, I'd like to suggest that defining dates as INTs is going to lead to a world of hurt. It kills just about every anything you might want to easily do with temporal calculations.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

davidandrews13 SSCertifiable Points: 6792 More actions · Answer 10

I actually think celko has a point with his comment here. I certainly think its something that should have been brought up

It's not as bad as sOme of his other comments 😉

Dwain Camps SSC Guru Points: 86908 More actions · Answer 11

Lynn Pettis (8/11/2012)
CELKO (8/11/2012)
CELKO talked aobut DDL of my post, but here I only ask the question about the extra comma
A reflex on my part. I assume that people want to learn and become better SQL programmers, not just kludge their code. What you posted was weird so I was interested.
MicroSoft has a screwed up parser and they are slowly fixing it. Seen the semi-colon in front of the WITH keyword? The MERGE statement requiring a semi-column?
Originally, Sybase T-SQL did not need a semi-colon to terminate a statement. But later, we made Standard SQL into an LALR(1) grammar. A guy named Steve Feltz checked this after every X3H2 meeting and he corrected the BNF for the committee.
I don't mind terminating SQL statements with a semicolon (;). Pet peeve though is starting CTEs with a semicolon (;). Sorry, the semicolon is a statement terminator, not a statement begininator.

Guess I'm on your ;S; list then. 🙂 Must be a holdover from my Algol days.

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

OTF SSCertifiable Points: 6054 More actions · Answer 12

Lynn Pettis (8/11/2012)
CELKO (8/11/2012)
CELKO talked aobut DDL of my post, but here I only ask the question about the extra comma
A reflex on my part. I assume that people want to learn and become better SQL programmers, not just kludge their code. What you posted was weird so I was interested.
MicroSoft has a screwed up parser and they are slowly fixing it. Seen the semi-colon in front of the WITH keyword? The MERGE statement requiring a semi-column?
Originally, Sybase T-SQL did not need a semi-colon to terminate a statement. But later, we made Standard SQL into an LALR(1) grammar. A guy named Steve Feltz checked this after every X3H2 meeting and he corrected the BNF for the committee.
I don't mind terminating SQL statements with a semicolon (;). Pet peeve though is starting CTEs with a semicolon (;). Sorry, the semicolon is a statement terminator, not a statement begininator.

I don't think you need to start cte's with a semi-colon? I think any statements before it need to be terminated though.

so this should work, for example...

with cte as (select 'a' as a)

select * from cte;

sqlfriend SSC Guru Points: 52769 More actions · Answer 13

CELKO (8/11/2012)
CELKO talked aobut DDL of my post, but here I only ask the question about the extra comma
A reflex on my part. I assume that people want to learn and become better SQL programmers, not just kludge their code. What you posted was weird so I was interested.
MicroSoft has a screwed up parser and they are slowly fixing it. Seen the semi-colon in front of the WITH keyword? The MERGE statement requiring a semi-column?
Originally, Sybase T-SQL did not need a semi-colon to terminate a statement. But later, we made Standard SQL into an LALR(1) grammar. A guy named Steve Feltz checked this after every X3H2 meeting and he corrected the BNF for the committee.

Thanks for the comments.

I said I am more interest in the comma issue now is because:

I found these SQLs in an existing database a contractor develped and has left. The database used mainly like a warehouse that imports data from other sources. It uses a lot of ETL. Date is defined like that in many tables.also is like that in the source file.

At this time, I won't change the format of the date, no time and don't want to change this because it will affect many places and reports.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 14

OTF (8/13/2012)
Lynn Pettis (8/11/2012)
CELKO (8/11/2012)
CELKO talked aobut DDL of my post, but here I only ask the question about the extra comma
A reflex on my part. I assume that people want to learn and become better SQL programmers, not just kludge their code. What you posted was weird so I was interested.
MicroSoft has a screwed up parser and they are slowly fixing it. Seen the semi-colon in front of the WITH keyword? The MERGE statement requiring a semi-column?
Originally, Sybase T-SQL did not need a semi-colon to terminate a statement. But later, we made Standard SQL into an LALR(1) grammar. A guy named Steve Feltz checked this after every X3H2 meeting and he corrected the BNF for the committee.
I don't mind terminating SQL statements with a semicolon (;). Pet peeve though is starting CTEs with a semicolon (;). Sorry, the semicolon is a statement terminator, not a statement begininator.
I don't think you need to start cte's with a semi-colon? I think any statements before it need to be terminated though.
so this should work, for example...
with cte as (select 'a' as a)
select * from cte;

You are correct. CTEs require that the previous statement be terminated by a semicolon. With that said, if you look at many of the CTE based solutions provided by many of the volunteers here on ssc as well has OPs posting questions, you find that many of them are started with the semicolon. The reasoning behind this is to ensure that the previous SQL statement is terminated by a semicolon. I call it lazy, especially when the previous SQL statement is provided as part of the solution and it isn't terminated by a semicolon.

And it you use this logic when writing a MERGE statement that uses a CTE, you end up with a statement that will look like this:

;WITH MyCTE (

Col1,

Col2 ...

) as (

SELECT

col1,

col2 ...

FROM

...

)

MERGE ...;