-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathdescriptive-style-guide.htm
4199 lines (3287 loc) · 197 KB
/
descriptive-style-guide.htm
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
<!DOCTYPE html>
<html>
<head>
</head>
<body>
<h1>Descriptive SQL Style Guide</h1>
<p>Copyright (c) 2020 by Peter Gulutzan. All rights reserved.</p>
<p>This program is free software; you can redistribute it and/or modify
it under the terms of the GNU General Public License as published by
the Free Software Foundation; version 2 of the License.</p>
<p>This program is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
GNU General Public License for more details.</p>
<p>You should have received a copy of the GNU General Public License
along with this program; if not, write to the Free Software
Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA</p>
<p>All references to "this program" or "software" mean "this guide", the Descriptive SQL Style Guide.
The copy of the GNU General Public License is the final section of this guide.</p>
<p>Version 0.1.0 2020-04-30</p>
<p>This is a descriptive style guide, like a dictionary that tells you what is common usage, with citations.
To find common usage I looked at vendor manuals, expert blogs, and prescriptive style guides
(guides that tell you what you should do). Status = alpha.</p>
<p>I think that anyone might use this to decide what code conventions are best for their own organizations,
based on orthodoxy, logic, style used for another language's style guide, and chosen DBMS vendor.</p>
<p>
The prescriptive style guides are the ones on github, in English, that have more than 100 stars at time of writing:<br>
Konstantin Taranov,
<a href="https://github.com/ktaranov/sqlserver-kit/blob/master/SQL%20Server%20Name%20Convention%20and%20T-SQL%20Programming%20Style.md">
SQL Server Name Convention and T-SQL Programming Style</a>,
841 stars.<br>
Simon Holywell,
<a href="https://github.com/treffynnon/sqlstyle.guide">
SQL Style guide</a>.
811 stars. Partly based on the book "Joe Celko's SQL Programming Style".<br>
Matt Mazur,
<a href="https://github.com/mattm/sql-style-guide">
Mazur's SQL Style Guide</a>.
441 stars. Mr Mazur says his guide is "opinionated".<br>
Fred Benenson,
<a href="https://gist.github.com/fredbenenson/7bb92718e19138c20591">
KickStarter, SQL Style Guide</a>
240 stars. Some PostgreSQL emphasis.<br>
Philipp Salvisberg,
<a href="https://github.com/Trivadis/plsql-and-sql-coding-guidelines">
"Trivadis PL/SQL & SQL Coding Guidelines"</a>
62 stars + favourable reviews for example by Steven Feuerstein.
Also available as
<a href="https://trivadis.github.io/plsql-and-sql-coding-guidelines/v3.5/9-appendix/PLSQL-and-SQL-Coding-Guidelines.pdf">
pdf</a>. Oracle emphasis.<br>
Mark Donnelly,
<a href="https://github.com/meadmaker/sql-style-guide">
SQL Style guide</a>.
119 stars. Last updated in 2013.<br>
<p></p>
<p>For the "Names" sections I also consult these non-github web pages because they have details about specific object types:<br>
Tim Hall,
<a href="https://oracle-base.com/articles/misc/naming-conventions">
Oracle naming conventions</a>.<br>
Peter Gulutzan,
<a href="http://www.dbazine.com/db2/db2-disarticles/gulutzan5/">
"SQL Naming Conventions"</a>.<br>
Sehrope Sarkuni,
<a href="https://launchbylunch.com/posts/2014/Feb/16/sql-naming-conventions/">
"How I Write SQL, Part 1: Naming Conventions"</a>.<br>
Craig Mullins,
<a href="https://www.datavail.com/blog/on-db2-naming-conventions/">
"On DB2 Naming Conventions"</a>.
Also at
<a href="https://www.datavail.com/blog/on-db2-naming-conventions/">
datavail.com</a>
and
<a href="https://db2portal.blogspot.com/2006/11/on-db2-naming-conventions.html">
db2portal</a>.<br>
Jeffrey Keller,
<a href="https://www.vertabelo.com/blog/an-unemotional-logical-look-at-sql-server-naming-conventions/">
"An Unemotional Logical Look at SQL Server Naming Conventions"</a>.<br>
</p>
<p>For the "Format" sections I also consult this book chapter:<br>
Phil Factor.
<a href="http://assets.red-gate.com/community/books/redgate-guide-sql-server-development.pdf">
"SQL Server Team-Based Development. Chapter 1: Writing Readable SQL"</a>.
Section = Code Layout.</p>
<p>
The vendor manuals are:<br>
<a href="https://docs.oracle.com/en/database/oracle/oracle-database/20/sqlrf/">
Oracle Release 20 or 19 SQL Language Reference</a>.<br>
<a href="https://www.ibm.com/support/knowledgecenter/da/SSEPGG_11.5.0/com.ibm.db2.luw.sql.ref.doc/doc/r0011049.html">
DB2 manual 11.5</a>.<br>
<a href="https://docs.microsoft.com/en-us/sql/t-sql/statements/statements?view=sql-server-ver15">
SQL Server: 2019</a>. <br>
<a href="https://dev.mysql.com/doc/refman/8.0/en/create-table.html">
MySQL: 8.0</a>.<br>
<a href="https://mariadb.com/kb/en/create-table/">
MariaDB 10</a>.</br>
<a href="https://www.tarantool.io/en/doc/2.3/reference/reference_sql/sql/">
Tarantool 2.3</a>.<br>
Although I am a former or current employee of some of these companies,
I am only using information that is publicly available.</p>
<p>
For SQL Developer's
<a href="https://en.wikipedia.org/wiki/Oracle_SQL_Developer">
"code editor"</a> also called "worksheet"
I rely on
<a href="https://www.thatjeffsmith.com">www.thatjeffsmith.com</a>
and
<a href="http://totierne.blogspot.com/2013/07/text-version-of-plsql-formatter-options.html">
totierne.blogspot.com</a>.</p>
<p>Finally, I will sometimes quote the ANSI/ISO SQL standard,
and many bloggers whom I will identify as they come up.</p>
<p>Inconsistency happens so I try to look for multiple examples.</p>
<p>In following sections I will identify style guides by the author's surname,
and identify vendor manuals by the product's name.</p>
<p>My definition of SQL style is:
consistently choosing words or formats that do not affect what the DBMS returns,
often describable as a list of rules that formalize what words or formats to choose,
for example saying INTEGER rather than int.
Or, more simply: what to say when there are two ways to say (almost) exactly the same thing.</p>
<p>I do not bother with rules that only affect one vendor.</p>
<p>I begin most sections with the word Choice: followed by settings that you can pick.
Then I describe what the choices mean, then what prescriptive guides say,
what I happen to have seen in vendor manuals (warning: sampling may be affected by chance),
and what some bloggers or other sources might have said if I happened to notice them.</p>
<p>I want to avoid looking biased for one style or one vendor,
so I do not use a consistent style in examples.</p>
<H3 id="contents">Contents</H3><HR>
<p>
<a href="#choices">Choices</a><br>
<a href="#keywords-and-letter-case">Keywords and letter case</a><br>
<a href="#semicolons">Semicolons</a><br>
<a href="#not-equal-operators">Not-equal operator</a><br>
<a href="#unnecessary-keywords-and-operators">Unnecessary keywords and operators</a><br>
<a href="#select-star">SELECT *</a><br>
<a href="#order-by-ordinal">ORDER BY ordinal</a><br>
<a href="#comments">Comments</a><br>
<a href="#multiple-line-comments">Multiple-line comments</a><br>
<a href="#new-or-old-style-inner-join">New or old style inner join</a><br>
<a href="#data-types">Data types</a><br>
<a href="#literals">Literals</a><br>
<a href="#long-literals">Long literals</a><br>
<a href="#names-meaning">Names (meaning)</a><br>
<a href="#names-letter-case">Names (letter case)</a><br>
<a href="#names-legal-characters">Names (legal characters</a><br>
<a href="#names-length">Names (length)</a><br>
<a href="#names-delimiter">Names (delimiter)</a><br>
<a href="#names-qualifier">Names (qualifier)</a><br>
<a href="#names-prefix-or-suffix">Names (prefix or suffix)</a><br>
<a href="#names-of-tables">Names of tables</a><br>
<a href="#names-of-temporary-tables">Names of temporary tables</a><br>
<a href="#names-of-views">Names of views</a><br>
<a href="#names-of-columns">Names of columns</a><br>
<a href="#names-of-aliases-and-range-variables">Names of aliases and range variables</a><br>
<a href="#names-of-constraints">Names of constraints</a><br>
<a href="#names-of-indexes">Names of indexes</a><br>
<a href="#names-of-triggers">Names of triggers</a><br>
<a href="#names-of-sequences">Names of sequences</a><br>
<a href="#names-of-functions">Names of functions (or: names of routines)</a><br>
<a href="#names-of-savepoints">Names of savepoints</a><br>
<a href="#names-of-collations">Names of collations</a><br>
<a href="#names-of-variables-or-parameters">Names of variables or parameters</a><br>
<a href="#dynamic-sql">Dynamic SQL</a><br>
<a href="#format-terminology">Format terminology</a><br>
<a href="#format-choices-terminology">Format choices terminology</a><br>
<a href="#format-symbols">Format symbols</a><br>
<a href="#format-parentheses">Format parentheses</a><br>
<a href="#format-comments">Format comments</a><br>
<a href="#format-line-length">Format line length</a><br>
<a href="#indenting-units">Indenting units</a><br>
<a href="#indenting">Indenting</a><br>
<a href="#format-clauses-deciding-what-is-a-clause">Format clauses deciding what is a clause</a><br>
<a href="#format-clauses-by-indenting">Format clauses by indenting</a><br>
<a href="#format-clauses-by-right-aligning-keywords">Format clauses by right-aligning keywords</a><br>
<a href="#format-clauses-by-aligning-contents">Format clauses by aligning contents</a><br>
<a href="#format-lists">Format lists</a><br>
<a href="#format-conditions">Format conditions</a><br>
<a href="#format-subqueries">Format subqueries</a><br>
<a href="#format-with">Format WITH</a><br>
<a href="#format-joins">Format joins</a><br>
<a href="#format-insert">Format INSERT</a><br>
<a href="#format-update">Format UPDATE</a><br>
<a href="#format-create-table">Format CREATE TABLE</a><br>
<a href="#format-create-view">Format CREATE VIEW</a><br>
<a href="#format-create-procedure-or-create-function">Format CREATE PROCEDURE or CREATE FUNCTION</a><br>
<a href="#format-create-trigger">Format CREATE TRIGGER</a><br>
<a href="#format-case-expression">Format CASE expression</a><br>
<a href="#format-blocks-the-usual-way">Format blocks the usual way</a><br>
<a href="#format-blocks-with-analogies-to-other-guides-and-choices">Format blocks with analogies to other guides and choices</a><br>
<a href="#format-declare">Format DECLARE</a><br>
<a href="#format-overflow">Format overflow</a><br>
<a href="#formatters-or-pretty-printers">Formatters or pretty printers</a><br>
<a href="#contributors">Contributors</a><br>
<a href="#gpl-version-2-license">GPL Version 2 License</a><br></p>
<H3 id="choices">Choices</H3><HR>
<p>Choice: worry about style?</p>
<p>I will put "Choice: ...?" questions in most sections.</p>
<p>You do not need to care if ...<br>
You have a good-enough tool that does the job for you.<br>
You only work with SQL occasionally, and mostly for yourself.<br>
You see that fussing about appearance should be low priority.<br>
You have read one of the prescriptive guides and are satisfied.<br>
If any of those things apply to you, good, you are in the
majority and now you can go off and find better things to do.</p>
<p>You only need to care if ...<br>
You want to come up with a better tool.<br>
You are in a team whose boss insists on consistency.<br>
You would like to know what, if anything, justifies the rules.<br>
If any of those things apply to you, too bad,
now you will have to check "worry about style = yes"
and make similar checks for all the "Choice:" matters
that follow.</p>
<p>Choices are not inheritable because I do not classify well.</p>
<h2>Keywords and letter case</h2><HR>
<p>Choice: Keywords upper case or lower case?</p>
<p>Google Ngrams shows relative popularity of words and phrases in books, and it distinguishes upper versus lower case.
I used it for terms that are likely to appear only in database books:<br>
<a href="https://books.google.com/ngrams/graph?content=CREATE+TABLE%2Ccreate+table&year_start=1800&year_end=2008&corpus=15&smoothing=3&share=&direct_url=t1%3B%2CCREATE%20TABLE%3B%2Cc0%3B.t1%3B%2Ccreate%20table%3B%2Cc0">CREATE TABLE is more popular than create table</a>.<br>
<a href="https://books.google.com/ngrams/graph?content=SELECT+DISTINCT%2Cselect+distinct&year_start=1800&year_end=2008&corpus=15&smoothing=3&share=&direct_url=t1%3B%2CSELECT%20DISTINCT%3B%2Cc0%3B.t1%3B%2Cselect%20distinct%3B%2Cc0">SELECT DISTINCT is more popular than select distinct</a>.<br>
<a href="https://books.google.com/ngrams/graph?content=TO+SAVEPOINT%2Cto+savepoint&year_start=1800&year_end=2008&corpus=15&smoothing=3&share=&direct_url=t1%3B%2CTO%20SAVEPOINT%3B%2Cc0%3B.t1%3B%2Cto%20savepoint%3B%2Cc0">TO SAVEPOINT is more popular than to savepoint</a>.<br>
<a href="https://books.google.com/ngrams/graph?content=EXECUTE+IMMEDIATE%2Cexecute+immediate&year_start=1800&year_end=2008&corpus=15&smoothing=3&share=&direct_url=t1%3B%2CEXECUTE%20IMMEDIATE%3B%2Cc0%3B.t1%3B%2Cexecute%20immediate%3B%2Cc0">EXECUTE IMMEDIATE is more popular than execute immediate</a>.<br>
<a href="https://books.google.com/ngrams/graph?content=integer+primary+key%2CINTEGER+PRIMARY+KEY&year_start=1800&year_end=2008&corpus=15&smoothing=3&share=&direct_url=t1%3B%2Cinteger%20primary%20key%3B%2Cc0%3B.t1%3B%2CINTEGER%20PRIMARY%20KEY%3B%2Cc0">INTEGER PRIMARY KEY is more popular than integer primary key</a>.<br>
So saying "in general keywords are upper case" has some evidence.</p>
<p>Similarly, a poll by Lukas Eder resulted in
<a href="https://twitter.com/lukaseder/status/1052809813447593986?ref_src=twsrc%5Etfw%7Ctwcamp%5Etweetembed%7Ctwterm%5E1052809813447593986&ref_url=https%3A%2F%2Fblog.jooq.org%2F2019%2F10%2F29%2Fa-guide-to-sql-naming-conventions%2F">
a majority for SELECT in upper case</a>.</p>
<p>Exception #1: when a keyword is not being used as a keyword.
For example, IMMEDIATE is in the SQL standard's "non-reserved word" list, so I can use it as a column name.
I have trouble believing that the guides which say "capitalize keywords" mean that I should capitalize IMMEDIATE
in that circumstance. I think they really meant
"capitalize words that are not identifiers", which coincidentally excludes reserved words.</p>
<p>Exception #2: when trying to avoid SHOUTING or when trying to be like most other languages.
This might be reasonable when entering statements with a client that highlights properly.
See <a href="https://stackoverflow.com/questions/292026/is-there-a-good-reason-to-use-upper-case-for-sql-keywords">
this argument on stackoverflow</a>.</p>
<p>Exception #3: data type names. See later section = <a href="#data-types">Data types</a>.</p>
<p>Prescriptive guides:</p>
<p>Benenson, Donnelly, Holywell, Salvisberg say: upper case.<br>
Taranov says: upper case, except for data types.<br>
Mazur says: lower case ("It's just as readable as uppercase SQL and you will not have to constantly be holding down a shift key.")</p>
<p>Vendor manual examples:</p>
<p>MySQL, MariaDB, Oracle, SQL Server, Tarantool: upper case for keywords.<br>
DB2: upper case for all words, whether or not they are keywords.<br>
Oracle SQL Developer has an option "Case change" with choices "UPPER", "lower", "keep unchanged", and "Init cap"
(which presumably is intended for identifiers more than keywords).</p>
<p>Bloggers:</p>
<p>The <a href="https://www.drupal.org/docs/develop/standards/sql-coding-conventions">Drupal manual</a> says:
"Make SQL reserved words UPPERCASE. This is not a suggestion.
Drupal db abstraction commands will fail if this convention is not followed."</p>
<p>Ian Hellström in <a href="https://oracle.readthedocs.io/en/latest/sql/plans/">"Execution Plans"</a>
notes that Oracle uses a hash of the statement text to see whether it is in the
library cache. Thus even a tiny difference between two statements
can cause a cache miss and affect performance.</p>
<p>According to <a href="http://www.dba-oracle.com/t_library_cache_hash_value.htm">Don Burleson</a>
capitalization of keywords does not matter, but
according to <a href="https://books.google.ca/books?id=XUPXAQAAQBAJ&pg=PA29&lpg=PA29&dq=oracle+hash+statement+text+capitalize&source=bl&ots=ZNbcQhS-se&sig=ACfU3U3m6FCnCJLEvd66ASFSnR-KSjduKg&hl=en&sa=X&ved=2ahUKEwivx4-tpcroAhXR854KHfEMCzIQ6AEwAHoECAwQKQ#v=onepage&q=oracle%20hash%20statement%20text%20capitalize&f=false">
Morton and Osborne and Sands</a>
it does matter. Probably they're looking at different versions.
Anyway, that means that maybe sometimes in theory inconsistency will affect performance.</p>
<p>A <a href="http://www.tpc.org/tpc_documents_current_versions/pdf/tpc-h_v2.18.0.pdf">TPC-H example</a> has all lower case.</p>
<H3 id="semicolons">Semicolons</H3><HR>
<p>Choice: End all statements with semicolons?</p>
<p>In standard SQL ";" is not part of a statement, it is a signal that the statement is over,
so it is required for direct SQL (such as SQL typed to a client program) and within BEGIN ... END,
but not dynamic SQL (such as execute immediate, or SQLExecDirect in the call level interface).
Thus if a JDBC driver rejects a semicolon-terminated statement with
"SQLSyntaxErrorException: ORA-00911: invalid character",
which has been known to happen, it is within its rights.
Also see <a href="https://xkcd.com/327/">this cartoon</a>.</p>
<p>But all the vendor manuals either show that ";" is an optional part of a statement
(as in <a href="https://docs.microsoft.com/en-us/sql/t-sql/statements/drop-database-transact-sql?view=sql-server-ver15">
this SQL Server bnf</a>), or show it in examples.
The key problems are: <br>
(1) if you do not know when statements end, you cannot run a script containing multiple statements <br>
(2) it is hard to know when statements end, for example "SELECT 5 BEGIN" is a valid single statement
(in a certain dialect where BEGIN is not a reserved word and [AS] is not required),
but simple parsers could easily think it is more than one.</p>
<p>Of course, a client
might get confused and think that <semicolon> <newline> is end-of-statement
(this has happened with Toad [] within comments,
and with other clients within BEGIN ... END blocks).
However, vendors have options for changing what the client thinks is the
terminator --
<a href="https://www.ibm.com/support/knowledgecenter/SS8PJ7_9.1.0/com.ibm.datatools.sqlxeditor.doc/topics/tchngstmtterm.html">SET TERMINATOR for DB2</a>,
<a href="https://mariadb.com/kb/en/delimiters/">DELIMITER for MySQL/MariaDB</a>,
<a href="https://asktom.oracle.com/pls/apex/asktom.search?tag=escape-semicolon">SET SQLTERMINATOR for Oracle</a>,
<a href="https://github.com/tarantool/test-run/issues/178">\set delimiter</a> for Tarantool.
Of course, I like to emphasize that there is a cleaner solution --
use a client with a
recognizer that supports
<a href="http://ocelot.ca/blog/blog/2016/03/20/client-side-predictive-parsing-of-mysqlmariadb-grammar/">
client-side predictive parsing</a>.</p>
<p>It is not true that Microsoft has deprecated the feature
"Not ending Transact-SQL statements with a semicolon".
Read the fine print of Microsoft's
<a href="https://docs.microsoft.com/en-us/sql/database-engine/deprecated-database-engine-features-in-sql-server-2016?redirectedfrom=MSDN&view=sql-server-ver15">
Deprecated Notice</a>.
It says that the feature will be "supported in the next version"
although it "will be deprecated in a later version", which they have been saying
for
<a href="https://social.msdn.microsoft.com/Forums/sqlserver/en-US/a6201cf8-a7ca-45ec-918e-18646aa02f37/best-practice-use-of-semicolon-to-terminate-statements?forum=transactsql">
over a decade</a>
and lack of ; has been known to
<a href="https://www.dbdelta.com/always-use-semicolon-statement-terminators/">cause an error</a>,
but they probably are holding back because there is so much installed code.</p>
<p>Prescriptive guides: Taranov says end with ";". Factor says "generally speaking" end with ";"</p>
<p>Vendor manuals:</p>
<p>Usually ";" is not in the BNFs or railroad diagrams
(although Oracle does show it and Microsoft does show it as [;] i.e. optional);
however, most manuals' examples end with semicolons (except DB2's).</p>
<p>Bloggers:</p>
<p><a href="http://www.dbdelta.com/always-use-semicolon-statement-terminators/">Dan Guzman</a> says end with ";".<br>
<a href="http://www.dba-oracle.com/oracle_news/2005_2_2_do_semicolons_sql_expose_database_injection_attacks.htm">Don Burleson</a> says it depends.<br>
<a href="https://www.thatjeffsmith.com/archive/2012/12/sql-developer-why-do-you-require-semicolons-when-executing-sql-in-the-worksheet/">Jeff Smith</a> says it depends.<br>
Factor says "Use the semicolon to aid the reading of code, even where SQL syntax states that it is only optional."</p>
<p>The best explanation of SQL Server anomalies that I have seen is in
<a href="https://solutioncenter.apexsql.com/rules-of-sql-formatting-terminating-sql-statements-with-semicolons/">
"Rules of SQL formatting – Terminating SQL statements with semicolons"</a>.</p>
<H3 id="not-equal-operator">Not-equal operator</H3><HR>
<p>Choice: Not-Equal Operator: <> or != or ^= or ¬=?</p>
<p>The main variants are != and <> which are accepted by all major vendors.</p>
<p>DB2 and Oracle also support ^= and ¬=.
¬ is the Unicode standard symbol U+00AC "NOT SIGN" but since it is not in 7-bit ASCII.
it will not be recognized if you have the wrong code page.
Accordingly Oracle
<a href="https://docs.oracle.com/database/121/SQLRF/conditions002.htm#SQLRF52105">
says</a>
"Some forms of the inequality condition may be unavailable on some platforms"
and IBM
<a href="https://www.ibm.com/support/knowledgecenter/SSEPEK_10.0.0/sqlref/src/tpc/db2z_basicpredicate.html#db2z_basicpredicate__fnocop">
says</a>
"A logical not sign (¬) can cause parsing errors in statements passed from one DBMS to another."</p>
<p>!= is found in C, Java, Perl, Python and the like.
The Python manual used to accept <> along with the warning
<a href="https://docs.python.org/2/reference/expressions.html">
"The <> spelling is considered obsolescent."</a>
... now it does not mention <> at all.
The idea is that ! is a "not sign" (if you know C and do not know Unicode) and
= is an equals sign, so this should be easy reading,</p>
<p><> is found in Access, BASIC, Pascal, and Rexx (remember them?).
The idea is that <= means "less than or equal", so in a consistent world <> means "less than or greater than".</p>
<p><> is standard but != is more common in Oracle examples that I have seen.</p>
<p>That reminds me that once upon a time someone reported a case where
!= was faster than <> and
<a href="https://web.archive.org/web/20150909223016/http://www.freelists.org/post/oracle-l/Performance-Difference-Between-and">
spawned many answers</a>.</p>
<p>Prescriptive guides:</p>
<p>Mazur says: Use != over <> ... Simply because != reads like "not equal" which is closer to how we'd say it out loud."<br>
Benenson uses != in an example.</p>
<p>Vendor manuals:</p>
<p>Oracle's <a href="https://docs.oracle.com/en/database/oracle/oracle-database/20/sqlrf/Comparison-Conditions.html#GUID-828576BF-E606-4EA6-B94B-BFF48B67F927">inequality test example</a> uses !=<br>
MySQL's <a href="https://dev.mysql.com/doc/refman/8.0/en/comparison-operators.html">example</a> uses both != and <><br>
SQL Server <a href="https://dev.mysql.com/doc/refman/8.0/en/comparison-operators.html">example</a> uses both != and <><br>
DB2's <a href="https://www.ibm.com/support/knowledgecenter/SSEPEK_10.0.0/sqlref/src/tpc/db2z_basicpredicate.html#db2z_basicpredicate__fnocop">example</a> uses <> </p>
<p>Bloggers:</p>
<p><a href="https://books.google.ca/books?id=ivaaDgAAQBAJ&pg=PT20&lpg=PT20&dq=%22As+an+example+of+when+to+choose+the+standard+form%22+operator&source=bl&ots=srJAINy0bC&sig=ACfU3U3dVfoJYT2QfUygabpOi1lzg6eZWg&hl=en&sa=X&ved=2ahUKEwiJucvIrZ_oAhULv54KHd6AB60Q6AEwAHoECAkQAQ#v=onepage&q=%22As%20an%20example%20of%20when%20to%20choose%20the%20standard%20form%22%20operator&f=false">
Itzik Ben-Gan</a> says "This case should be a nobrainer: go for the standard one!"</p>
<p>An <a href="http://ibatis.10938.n7.nabble.com/How-to-define-not-equal-lt-gt-in-SQL-statement-td3118.html">iBATIS thread</a> shows a
user getting into trouble because XML complains about an SQL statement that contains <>
and seeing two proposed solutions: use cdata, or switch to !=.</p>
<H3 id="unnecessary-keywords-and-operators">Unnecessary keywords and operators</H3><HR>
<p>Choice: Add unnecessary keyword INNER in INNER JOIN?<br>
Choice: Add unnecessary keyword OUTER in LEFT OUTER JOIN?<br>
Choice: Add unnecessary keyword INTO in INSERT INTO?<br>
Choice: Add unnecessary parentheses around low-precedence expressions?<br>
Choice: Add unnecessary keyword COLUMN for ALTER TABLE ADD?</p>
<p>This is pleonasm, which <a href="https://dictionary.cambridge.org/dictionary/english/pleonasm">Cambridge</a>
defines as
"the use of more words than are needed to express a meaning, done either unintentionally or for emphasis;"
According to <a href="https://www.merriam-webster.com/dictionary/pleonasm">Merriam-Webster</a>
pleonasm means "the use of more words than those necessary to denote mere sense"
and "Pleonasm is commonly considered a fault of style, but it can also serve a useful function."</p>
<p>That is perhaps the reason that I can not find prescriptive guides or vendor examples
that advocate SELECT ALL, or UNION DISTINCT or ORDER BY x ASC.</p>
<p>But saying Add unnecessary keywords and operators = yes
should have these three effects, which are activated in at least one prescriptive guide or blog:</p>
<p>[INNER] before JOIN<br>
Mazur says: "Include inner for inner joins", probably the reasoning again is that it makes something easier to read.
Examples in vendor manuals seem to show INNER more often than not.</p>
<p>[INTO] after INSERT<br>
The word INTO is optional in MySQL/MariaDB and SQL Server,
but compulsory in DB2, Oracle, and the SQL standard.
Holywell says:
"Why you would willingly choose a proprietary solution when a standard SQL method already exists is beyond me."</p>
<p>Parentheses around OR conditions<br>
That is, x OR y becomes (x OR y).
The reasoning is that, if there is an AND in the vicinity,
it will take precedence -- but who remembers precedence rules?
As the
<a href="https://www.oracle.com/technetwork/java/codeconventions-150003.pdf">Java style guide</a> says:
(regarding mixed operators)
"you should not assume that other programmers know precedence as well as you do".</p>
<p>But add-unnecessary-keywords-and-operators = yes does not mean:<br>
[TRANSACTION] or [TRAN] or [WORK] after COMMIT.<br>
This is the opposite of the INSERT INTO phenomenon --
in this case not every vendor supports the unnecessary word.
So, naturally, you will not see the same usage of these keywords in all vendor manuals.</p>
<p>Prescriptive guides:</p>
<p>Salvisberg says: "Never initialize variables with NULL. ... Variables are initialized to NULL by default."
(but probably he does not mean DEFAULT NULL).</p>
<p>Holywell says:
"Keep code succinct and devoid of
redundant SQL—such as unnecessary quoting or parentheses or WHERE clauses that can otherwise be derived."</p>
<p>Vendor manual examples:</p>
<p>Oracle: <a href="https://docs.oracle.com/cloud/latest/big-data-discovery-cloud/BDDEQ/ceql_statement_join.htm#BDDEQ-concept_C34DE81F378945A68B88025E29D5CE58">does not allow LEFT JOIN with OUTER</a><br>
MariaDB, MySQL: show LEFT JOIN without OUTER</p>
<p>Bloggers:</p>
<p><a href="https://gist.github.com/mattmc3/38a85e6a4ca1093816c08d4815fbebfb">
"Modern SQL Style Guide"</a>,
explaining why "left outer join" is bad, says: "`outer` is an unnecessary optional keyword".</p>
<p><a href="https://czep.net/15/pedantic-sql.html">Scott Czepiel</a> says:
Omit the “outer” when doing a left join ... Likewise omit “inner” from inner joins</p>
<H3 id="select-star">SELECT *</H3><HR>
<p>Choice: Allow SELECT *?</p>
<p>The main complaint about SELECT * is that the definition of the table might change.
However, that is true even if we select with column names, if ALTER can be used to
change a column definition.</p>
<p>To be consistent, Allow SELECT * = no should also affect other syntax
that is based on assumptions about stable table structures, such as
INSERT without INTO, or NATURAL JOIN.</p>
<p>And maybe it is not truly pleonastic to specify column names,
if there is a chance that table definition will change someday.
So maybe SELECT * can be replaced by<br>
SELECT column-name [, column-name ...]<br>
and maybe INSERT INTO table-name can be replaced by<br>
INSERT INTO table-name (column-name [, column-name ...]).</p>
<p>Vendor manual examples:</p>
<p>Since I expect that SELECT * will be used for examples, I did not check.</p>
<p>Bloggers:</p>
<p>Factor <a href="https://www.red-gate.com/hub/product-learning/sql-prompt/finding-code-smells-using-sql-prompt-asterisk-select-list">
says</a>: use SELECT * for "ad-hoc" work, not "production" work.</p>
<H3 id="order-by-ordinal">ORDER BY ordinal</H3><HR>
<p>Choice: Allow ORDER BY ordinal?</p>
<p>"SELECT ... ORDER BY 1;" was once legal in standard SQL but became illegal in the 1999 version.
However, all major vendors still support it.</p>
<p>MySQL and Tarantool and
DB2 with
<a href="https://www.ibm.com/support/producthub/db2/docs/content/SSEPGG_11.5.0/com.ibm.db2.luw.apdv.porting.doc/doc/r_sql_compat_group_by_column.html">
NPS-mode</a> even support "GROUP BY 1".</p>
<p>"ORDER BY ordinal" would be convenient for<br>
SELECT very_long_expression, very_long_expression FROM ... ORDER BY 2;<br>
However, all major vendors support aliases too.</p>
<p>ORDER BY ordinal" would be convenient for<br>
VALUES ('b', 'c') UNION ALL VALUES ('x', 'y') ORDER BY 2;<br>
However, not all vendors support ORDER BY in such contexts.</p>
<p>Prescriptive guides:</p>
<p>Taranov, Salvisberg say: specify columns.</p>
<p>Salvisberg says: "Always specify column names instead of positional references in ORDER BY clauses."</p>
<p>Vendor manuals:</p>
<p>Oracle: <a href="https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/SELECT.html#GUID-CFA006CA-6FF1-4972-821E-6996142A51C6">
shows</a> ORDER BY position as well as ORDER BY name</p>
<p>DB2: <a href="https://www.ibm.com/support/producthub/db2/docs/content/SSEPGG_11.5.0/com.ibm.db2.luw.sql.ref.doc/doc/r0059211.html">
has</a> examples of ORDER BY name only.</p>
<p>SQL Server: <a href="https://docs.microsoft.com/en-us/sql/t-sql/queries/select-order-by-clause-transact-sql?redirectedfrom=MSDN&view=sql-server-ver15">
says</a> "Avoid specifying integers in the ORDER BY clause as positional representations of the columns in the select list."</p>
<p>Bloggers:</p>
<p>Claire Carroll
<a href="https://blog.getdbt.com/write-better-sql-a-defense-of-group-by-1/">posted</a>
"Write better SQL: In defense of group by 1"
... the <a href="https://about.gitlab.com/handbook/business-ops/data-team/sql-style-guide/">gitlab</a> folks would probably approve.</p>
<H3 id="comments">Comments</H3><HR>
<p>Choice: /* comment */ or --comment?</p>
<p>The main variants of comments are:<br>
bracketed (start with /* and end with */) also called multi-line or block or C-style or slash star,<br>
simple (start with -- and end with newline) also called single-line or line or double-dash.<br>
MySQL allows replacing -- with # for simple comments but that is rare.</p>
<p>Although the SQL standard said that simple comments are mandatory and bracketed comments are optional,
nowadays all vendors support both types.
Oddly, there are cases where bracketed comments are legal but simple comments are illegal,
for example with DB2's <a href="https://www.idug.org/p/bl/et/blogaid=943">DSNTIAUL</a>
and in standard PREPARE texts (General Rule 6 =
"If P [the contents of the SQL statement variable]
does not conform to the Format, Syntax Rules, and Access Rules of a <preparable statement>,
or if P contains a <simple comment> then ... an exception condition is raised: syntax error or access rule violation.").
I do not know why.
Maybe it has something to do with the fact that some types of SQL injection attack
depend on simple comments, as
described on <a href="https://www.netsparker.com/blog/web-security/sql-injection-cheat-sheet/">netsparker.com</a>.
Or maybe it has something to do with caching, as a
<a href="https://books.google.ca/books?id=UfjHAgAAQBAJ&pg=PA329&lpg=PA329&dq=bracketed+comment+sql&source=bl&ots=Jq3NhpfFzG&sig=ACfU3U11aqTgiXffIIeZYSwP5_5BgS7vpA&hl=en&sa=X&ved=2ahUKEwjz29-84vznAhURIjQIHcCVB4YQ6AEwA3oECAYQAQ#v=onepage&q=bracketed%20comment%20sql&f=false">
DB2 document</a> hints that only
statements with bracketed comments will get into the dynamic cache.</p>
<p>There are <a href="https://en.wikipedia.org/wiki/Comparison_of_documentation_generators">
documentation generators</a>
that have SQL support and that require a particular comment style, often starting with /**.</p>
<p>The other style question is: where to put comments?</p>
<p><a href="https://solutioncenter.apexsql.com/rules-of-sql-formatting-sql-code-commenting/">ApexSQL</a> has advice for where to put bracketed comments inside stored procedures.
but I did not find specific advice about location in other documents (except Don Burleson's blog).
Vendor manual examples show comments preceding the statement (on a separate line),
or comments following a clause (on the same line, at the end of the line),
but I found none that show comments following the statement or comments within a line.</p>
<p>Prescriptive guides:</p>
<p>Taranov says "Always use multi-line comment".<br>
Holywell says "Use the C style opening /* and closing */ where possible" and
"Avoid nesting comments" (with an example of a bracketed comment inside a simple comment).<br>
Salvisberg says: "Inside a program unit only use the line commenting technique --".</p>
<p>Vendor manuals: show both styles, no apparent preference.</p>
<p>Oracle SQL Developer has an option "Put -- comments between /* ... */" but not the other way around,
which I guess is a hint about where their sentiments lie.</p>
<p>Bloggers:</p>
<p><a href="http://www.dba-oracle.com/t_plsql_comments_best_practice_standards.htm">Don Burleson</a>
says: use simple comments except inside 3GL programs that use the Oracle Precompilers.</p>
<H3 id="multiple-line-comments">Multiple-line comments</H3><HR>
<p>Choice: multiple-line comment style 1, or 2, or 3, or 4, or 5?<br>
Choice: precede multiple-line comment with a blank line?<br>
Choice: follow multiple-line comment with a blank line?</p>
<p>Bracketed /* ... */ comments can take up multiple lines.
Usually they precede what they are commenting on.
It may be hard to reformat them so it is good if each line is short.</p>
<p>I have seen or read about five styles.
Usually they are preceded or followed by newlines.</p>
<pre>
/*
This comment is Style 1: Align start and end.
*/
/*
* This comment is Style 2: Align asterisks.
*/
/*
** This comment is Style 3: Lines start with **.
*/
/**
* This comment is Style 4: Comment starts with **.
*/
/*
This comment is style 5: Comment ends on line end. */
</pre></p>
<p>Style 3 is recommended (but not followed) by <a href="https://docs.microsoft.com/en-us/sql/t-sql/language-elements/slash-star-comment-transact-sql?view=sql-server-ver15">the SQL Server manual</a>.<br>
Style 4 is a signal to tools like <a href="http://www.doxygen.nl/manual/docblocks.html">Doxygen</a> that this is for documentation as mentioned earlier.</p>
<p>Vendor examples:</p>
<p>Oracle: <a href="https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/Comments.html#GUID-5C84C344-CEB3-4DBF-B748-337DE11CCE2A">style 5</a>.<br>
DB2: <a href="https://www.ibm.com/support/knowledgecenter/SSEPEK_10.0.0/sqlref/src/tpc/db2z_sqlcomments.html">style 2</a>.<br>
SQL Server: style 1.<br>
MySQL: <a href="https://dev.mysql.com/doc/refman/8.0/en/comments.html">style 1</a>.<br>
MariaDB: <a href="https://mariadb.com/kb/en/comment-syntax/">style 5</a>.</p>
<p>Bloggers:</p>
<p><a href="https://solutioncenter.apexsql.com/rules-of-sql-formatting-sql-code-commenting/">Apex SQL</a>
has recommendations for what should be in the comment, and options for inserting empty lines before/after
the comment.</p>
<H3 id="new-or-old-style-inner-join">New or old style inner join</H3><HR>
<p>Choice: join syntax: old-style or new-style?</p>
<p>"FROM a, b [WHERE join-condition etc.]" is old style, also called FROM-join.<br>
"FROM a ... JOIN b ON join-condition" is new style, also called ANSI or ISO or SQL92 join.</p>
<p>Back in 2005 Doug Burns <a href="http://oracledoug.com/serendipity/index.php?/archives/933-ANSI-Join-Syntax.html">described</a>
his pro-and-con thoughts, which I hereby distort:<br>
Pro: changing to an OUTER join is easy because it has almost the same syntax<br>
Pro: it is more unlikely that you will miss a condition and go Cartesian<br>
Pro: mixing up the join-condition with other conditions in the WHERE clause might mislead,<br>
Con: new style is more verbose<br>
Con: old style has a long-established base.</p>
<p>Some bloggers give the wrong impression that Microsoft has deprecated old style joins and/or they are non-standard.
In fact Microsoft only deprecated old style outer joins
(see
<a href="https://www.red-gate.com/hub/product-learning/sql-prompt/finding-code-smells-using-sql-prompt-old-style-join-syntax-st001">
this red-gate post</a>
and
<a href="https://blogs.technet.microsoft.com/wardpond/2008/09/13/deprecation-of-old-style-join-syntax-only-a-partial-thing/">
this Microsoft post</a>).
In fact the syntax "FROM table_name1 , table_name2" is legal according to the ISO/IEC ("ANSI")
SQL:2016 rules for <table reference list>
and Microsoft
<a href="https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms190014(v=sql.105)?redirectedfrom=MSDN">
is aware of that</a>.</p>
<p>Prescriptive guides:</p>
<p>Benenson, Salvisberg, Taranov say: use new style.</p>
<p>Vendor manuals:</p>
<p>DB2: the
<a href="https://www.ibm.com/support/knowledgecenter/SSEPEK_11.0.0/intro/src/tpc/db2z_innerjoin.html">"Inner join"</a>
examples mix old and new style without recommendations.</p>
<p>Oracle: the
<a href="https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/SELECT.html#GUID-CFA006CA-6FF1-4972-821E-6996142A51C6__I2066611">
"Using Join Queries: Examples"</a> show only old style.
Remember that we're only talking about inner joins here --
for outer joins, Oracle recommends
<a href="https://docs.oracle.com/cd/B19306_01/server.102/b14200/queries006.htm">against</a>
using the old style and its Oracle-specific join operator.</p>
<p>MariaDB: recommends <a href="https://mariadb.com/kb/en/comma-vs-join/">new style</a>.</p>
<p>SQL Server: says new style is
<a href="https://docs.microsoft.com/en-us/sql/relational-databases/performance/joins?view=sql-server-ver15">"recommended"</a>.</p>
<p>Tarantool: has an introductory section using only old style.</p>
<p>Bloggers:</p>
<p><a href="https://developer.salesforce.com/forums/?id=906F00000005K22IAE">Salesforce</a>
apparently is in the group that thinks that old style is
<a href="https://salesforce.stackexchange.com/questions/195461/marketing-cloud-query-doesnt-allow-two-tables-in-from">
not standard</a>.</p>
<p><a href="https://www.sqlservercentral.com/forums/topic/non-ansi-equal-join-sql-2012">Joe Celko</a> says:<br>
"But what I found is that people who use the traditional notation think in sets,
while those who use the infix notation are stuck with a procedural linear mindset."<br>
He <a href="https://www.sqlteam.com/forums/topic.asp?TOPIC_ID=162162">also</a> says:<br>
"Weaker SQL programmers use INNER JOIN, since it is sequential and a familiar, procedural infix binary operator.
Stronger SQL programmers use the WHERE syntax because it is set-oriented, obeys the law of proximity and is an n-ary operator."</p>
<H3 id="data-types">Data types</H3><HR>
<p>Choice: abbreviated data type names?<br>
Choice: upper case or lower case data type names?</p>
<p>In standard SQL you can create a column with data type int,
but in information_schema it will show up as INTEGER --
the canonical form is always the unabbreviated word in upper case.
Unfortunately not every vendor converts canonically but this
shows that for the standard INTEGER is a better word.</p>
<p>However, <a href="https://www.sentryone.com/blog/aaronbertrand/backtobasics-lower-case-data-types">Aaron Bertrand</a>
(who wrote a series of blog posts touching on SQL style) switched to int,
because of a SQL Server quirk.
In SQL Server, if you are querying a system table, it makes a difference
what collation you used when you originally defined the whole database.
It can mean that you will not find columns defined as 'int' when you search for columns defined as 'INT' or 'INTEGER'.
(In SQL Server built-in data type names are not
<a href="https://docs.microsoft.com/en-us/sql/t-sql/language-elements/reserved-keywords-transact-sql?view=sql-server-ver15">
not reserved words</a>.)</p>
<p>For another indicator, again using Ngrams ...<br>
<a href="https://books.google.com/ngrams/graph?content=integer+primary+key%2CINTEGER+PRIMARY+KEY%2Cint+primary+key%2CINT+PRIMARY+KEY%2Cint+PRIMARY+KEY&year_start=1800&year_end=2008&corpus=15&smoothing=3&share=&direct_url=t1%3B%2Cinteger%20primary%20key%3B%2Cc0%3B.t1%3B%2CINTEGER%20PRIMARY%20KEY%3B%2Cc0%3B.t1%3B%2Cint%20primary%20key%3B%2Cc0%3B.t1%3B%2CINT%20PRIMARY%20KEY%3B%2Cc0%3B.t1%3B%2Cint%20PRIMARY%20KEY%3B%2Cc0#t1%3B%2Cinteger%20primary%20key%3B%2Cc0%3B.t1%3B%2CINTEGER%20PRIMARY%20KEY%3B%2Cc0%3B.t1%3B%2Cint%20primary%20key%3B%2Cc0%3B.t1%3B%2CINT%20PRIMARY%20KEY%3B%2Cc0%3B.t1%3B%2Cint%20PRIMARY%20KEY%3B%2Cc0">INTEGER PRIMARY KEY is more popular than INT PRIMARY KEY</a><br>
<a href="https://books.google.com/ngrams/graph?content=varchar%2CVARCHAR%2Ccharacter+varying%2CCHARACTER+VARYING&year_start=1800&year_end=2008&corpus=15&smoothing=3&share=&direct_url=t1%3B%2Cvarchar%3B%2Cc0%3B.t1%3B%2CVARCHAR%3B%2Cc0%3B.t1%3B%2Ccharacter%20varying%3B%2Cc0%3B.t1%3B%2CCHARACTER%20VARYING%3B%2Cc0">
but varchar is more popular than VARCHAR or CHARACTER VARYING</a>.</p>
<p>Although the INT-versus-INTEGER questions are not settled, there is no question
that CHAR(n) is preferred instead of CHARACTER(n).
By the way, if you did not know how to pronounce CHAR, see
<a href="https://ocelot.ca/blog/blog/2016/11/30/pronouncing-database-terms/">this blog post</a>.
For the other data type abbreviation -- DEC versus DECIMAL -- I found no guidance.</p>
<p>Sometimes I have seen that the length, and/or precision and scale, is skipped when it is default.
This is probably related to the Pleonasm question -- should unnecessary words be skipped?</p>
<p>Prescriptive guides:</p>
<p>Salvisberg says: "Avoid declaring NUMBER variables, constants or subtypes with no precision."<br>
Holywell says: "It is best to avoid the abbreviated keywords and use the full length ones where available"
(but he is not talking about data types)<br>
Taranov says: "Always specify a length to any text-based data type such as varchar, nvarchar, char, nchar:"</p>
<p>Vendor Manuals:</p>
<p>I just looked at the data types that were used in the manuals' CREATE TABLE examples.</p>
<p>DB2: CHAR(n), CHAR (n), CLOB, CLOB(n), DATE, DECIMAL(n,n), DOUBLE, INTEGER, SMALLINT, TIMESTAMP, VARCHAR(n)
(I thought it was interesting tht DOUBLE was used although DOUBLE PRECISION is standard)</p>
<p>Oracle: BLOB, CHAR(n), CLOB, DATE, NUMBER,, NUMBER(n), NUMBER(n,n), number(n), NCLOB, VARCHAR2(n), varchar2(n)
(I thought it was interesting that CHAR(n) was used when n <= 2 and NVARCHAR(n) was used when n > 2 but doubt that is a rule)</p>
<p>SQL Server: char(n), DATETIME, datetime, float, INT, int, money, nvarchar(n), smallint, VARBINARY, VARCHAR(n)</p>
<p>MySQL: BLOB, CHAR(n), DATE, DATETIME, INT, VARCHAR(n)</p>
<p>MariaDB: bigint, BLOB, CHAR(n), DATETIME INT, int, varchar(n)</p>
<p>Tarantool: INT, INTEGER, SCALAR, STRING</p>
<p>... Summary: vendors do not follow style guides in this area.</p>
<p>Oracle SQL Developer has an option "Case change", and if you pick "UPPER"
then VARCHAR2 is upper case, that is, it is a keyword like any other.</p>
<p>This <a href="https://forum.red-gate.com/discussion/11420/auto-completion-insertion-keys-behaviour">redgate forum post</a>
shows that there are different options for keywords UPPERCASE and data types lowercase.</p>
<H3 id="literals">Literals</H3><HR>
<p>Choice: allow non-standard literal formats?</p>
<p>In standard SQL the format of a literal determines its type:<br>
inside '' -- CHAR<br>
inside X'' -- BINARY or VARBINARY<br>
inside DATE '' -- DATE<br>
exponential notation -- DOUBLE or REAL or FLOAT<br>
[sign]digits[[period][digits]] INTEGER or SMALLINT or DECIMAL or NUMERIC<br>
and so on.<br>
But vendors vary, and due to implicit casting the format does not really indicate much.</p>
<p>What then should we do if we want to put ' within ''?<br>
MySQL/MariaDB and SQL Server sometimes let us say "...'..." (they have to use something else for delimiting identifiers).<br>
Some DBMSs let us escape by saying '...\'...'.<br>
Some JDBC APIs let us escape by saying {escape 'escape character'}<br>
Oracle lets us change what the character string delimiter is with q'{...'...}'<br>
Perhaps all DBMSs let us say '...''...'.</p>
<p>Prescriptive guides:</p>
<p>Nobody says anything.</p>
<p>Vendor manuals:</p>
<p>Examples usually show '...''...' as it is the only standard unless the DBMS supports Unicode escaping.</p>
<p>Blogs:</p>
<p><a href="https://www.red-gate.com/simple-talk/blogs/how-to-and-not-to-escape-a-string-in-tsql/">Louis Davidson</a>
suggests functions quotename() and concat() for putting strings together.</p>
<H3 id="long-literals">Long literals</H3><HR>
<p>Choice: use standard syntax, or use a continuation character, or use ||</p>
<p>This situation comes up with both character and binary strings:<br>
the string is too long (presumably that means "Maximum line length" is exceeded).<br>
The string has to be broken up and placed on multiple lines.</p>
<p>"use standard syntax" means: depend on the fact that in
standard SQL 'A' /* whitespace */ 'B' is interpreted as 'AB'. So<pre>
SELECT 'video meliora proboque, '
'deteriora sequor.' ...</pre>
But, alas, some vendors do not allow it.<br>
And even one that does -- MySQL/MariaDB -- does not allow X'41' '42'.</p>
<p>"use a continuation character" means depend on the client
(or possibly the server) to see a line-continuation signal. So<pre>
SELECT 'video meliora proboque, '\
'deteriora sequor.' ...</pre>
But, alas, some vendors use different continuation characters.</p>
<p>"use ||" means depend on support of || for concatenation. So<br><pre>
SELECT 'video meliora proboque, ' ||
'deteriora sequor.' ...</pre>
This is the best bet for vendor support; even MySQL/MariaDB
can be forced to accept || for concatenation sometimes.<br>
The problem is not that || might make a performance difference.
The problem is that we have to specify a special format rule
regarding placement of the operator (see section <a href="#format-symbols">Format symbols</a>).</p>
<p>Prescriptive guides:</p>
<p>I have no information from any prescriptive guide.</p>
<p>Vendor manuals:</p>
<p>Oracle: In SQL/Plus, the continuation character is a hyphen.</p>
<p>DB2: line continuation is possible with \ but this is in the client</p>
<p>SQL Server: line continuation is possible with \ and
<a href="https://sqlquantumleap.com/2017/10/27/line-continuation-in-t-sql/">this</a> says that is part of T-SQL not the client</p>
<H3 id="names-meaning">Names (meaning)</H3><HR>
<p>Choice: Names should mean something?</p>
<p>We all know the setting should be 'yes' but
if you are trying to make a point about syntax,
then a <a href="https://en.wikipedia.org/wiki/Metasyntactic_variable">placeholder name</a>
has all the meaning that you need, because the referent could be anything at all.
So in a document like this I use names like
TABLE_NAME or Column1 but expect that people
would be more specific when representing something specific.</p>
As far as I can tell the
<a href="https://en.wikipedia.org/wiki/Foobar">foobar</a> placeholders,
known in other languages, are not frequent in SQL contexts.
Perhaps we have fewer reasons to swear.
<p>Prescriptive guides:</p>
<p>No need to quote. Everybody would agree.</p>
<H3 id="names-letter-case">Names (letter case)</H3><HR>
<p>Choice: Names UPPER CASE or lower case or snake_case or PascalCase or camelCase?</p>
<p>Terminology:<br>
camelCase = (all words except the first start with a capital letter)<br>
PascalCase = (all words start with a capital letter) sometimes called upper camel case<br>
SNAKE_CASE = all words all capital letters, underscore separator)<br>
snake_case = all words all lower case letters, underscore separator)<br>
...<br>
Inevitably, in the following Names sections, I am going to have to
give examples which use a particular case.
This does not mean I recommend a particular case, I leave recommendations to prescriptive guides.</p>
<p>One argument against lower case is that it does not reflect what the
standard information_schema name will look like, since the standard
rule is that the name will be folded to upper case before being stored.
PostgreSQL ignores the SQL standard
(see the blog post
<a href="https://ocelot.ca/blog/blog/2013/09/30/sometimes-mysql-is-more-standards-compliant-than-postgresql/">
"Sometimes MySQL is more standards-compliant than PostgreSQL"</a>), but some other DBMSs do not.</p>
<p>One argument against snake_case is the academic conference presentation by
David Binkley and Marcia Davis and Dawn Lawrie and Christopher Morrell,
<a href="https://www.researchgate.net/publication/221219628_To_camelcase_or_under-score">
"To camelcase or under-score"</a>
which concluded:<br>
"... it be-comes evident that the camel
case style leads to better allaround performance once a subject is trained on this style."
But their experimenting was not in an SQL situation
where names are distinguished by indentation or by having all non-identifiers in upper case.
And anyway there is another study by
Bonita Sharif and Jonathan I. Maletic,
"An Eye Tracking Study on camelCase and under_score Identifier Styles",
which concluded:<br>
"While results indicate no difference in accuracy between the two styles,
subjects recognize identifiers in the underscore style more quickly."
(I have only read the abstract.)</p>
<p>Prescriptive guides:</p>
<p>Holywell says: "Use underscores where you would naturally include a space in the name" ...
"Avoid ... CamelCase—it is difficult to scan quickly." (Apparently this is a reference to what I have called PascalCase.)</p>
<p>Salvisberg says: "write all names in lower case"</p>
<p>Benenson gives an example of snake_case: "SELECT COUNT(*) AS backers_count"
and says "Variable names should be underscore separated:"
(I consider "underscore separated" to be a synonym of "snake_case").</p>
<p>Sarkuni says: snake_case</p>
<p>Mullins says: SNAKE_CASE</p>
<p>Taranov says: PascalCase for everything except database, schema, and synonym.</p>
<p>Factor says: "Schema objects are, I believe, better capitalized." (example) "This_Is_Capitalized"</p>
<p>Vendor manual examples:</p>
<p>MariaDB, MySQL, Oracle, Tarantool: usually snake_case<br>
SQL Server: usually PascalCase<br>
DB2: usually SNAKE_CASE<br>
SQL Standard: SNAKE_CASE</p>
<p>For example these are extracts from the manuals' CREATE TABLE pages.<br>
CREATE TABLE employees_demo ... (Oracle)<br>
CREATE TABLE EMPLOYEE_SALARY ... (DB2)<br>
CREATE TABLE CREATE TABLE dbo.PurchaseOrderDetail ... (SQL Server)<br>
CREATE TABLE client_firms ... (MySQL)<br>
CREATE TABLE table_name ... (MariaDB)<br>
CREATE TABLE modules ... (Tarantool)</p>
<H3 id="names-legal-characters">Names (legal characters)</H3><HR>
<p>Choice: Names can include $?<br>
Choice: Names can include letters other than A-Z?</p>
<p>What characters should be legal for regular identifiers of most objects?
In standard SQL, the answer is _ or digit or any Unicode character that is considered to be a letter, so Cyrillic / Japanese kana / Chinese / etc. are all okay
But in practice the answers vary widely, as one can see by looking at the vendor documentation of
<a href="https://www.ibm.com/support/knowledgecenter/SSEPGG_11.1.0/com.ibm.db2.luw.admin.cmd.doc/doc/r0002141.html">DB2</a>,
<a href="https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/Database-Object-Names-and-Qualifiers.html#GU">Oracle</a>,
<a href="https://docs.microsoft.com/en-us/sql/relational-databases/databases/database-identifiers?view=sql-server-ver15">SQL Server</a>,
<a href="https://mariadb.com/kb/en/identifier-names">MariaDB</a>,
<a href="https://www.tarantool.io/en/doc/2.3/reference/reference_sql/sql/">Tarantool (see "Identifiers")</a>.
The minimum common denominator is A-z, a-z, 0-9, _ and -- for some reason I have never understood -- $.