Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

SQL style guide

sqlstyle.guide

Post reply on HN
Print view View original

81–90 of 151 posts

Re: SQL style guide

#81

Post by Pxtl »

systems wrote: post #69
6nf wrote: post #66

Earlier quoted context omitted.

I don't know if you've ever worked with MS SQL Server in production but for us it's been the most amazing piece of technology in our stack for the past 15 years. SQL Server is in the top 3 best things to be published by Microsoft ever. (The other 2 are probably Excel and Flight Simulator)

and SQL Server BI stack, is very solid and cost effective SSRS is really good SSIS is very acceptable (I mainly use to call SQL Tasks) and SSAS is really second to none

SSRS still doesn't have support for msbuild, SSDT is a goddamned dumpster fire, and SSIS jobs have that hilariously over designed graphical programming language and are do frustratingly brittle that they are functionally single-use. And SSMS has the worst use of tabbed files I've ever seen.

There's a link to like about mssql, but a lot to hate too.

Re: SQL style guide

#82

Post by Pxtl »

bigger_cheese wrote: post #79

Naming conventions are pretty controversial I think. The databases I deal with are mostly for storing time series data taken from measurement instruments (i.e Load Cells, Thermocouples, Flow transmitters, Pressure gauges etc). We seem to use prefixes to distinguish columns i.e. MS_ (Mass in Tonnes, MS_KG_ etc. Is used If not stored as Tonnes) VL_ (volume, in cubic meters) RT_FL_ (is Flow rate in normal cubic meters p…

I tend to prefer English suffixes and camelcase rather than Hungarian prefixes and underscores. Like, for a steel coil, InnerDiameterMM, MassTonnes, GaugeMM, etc. I prefer to leave abbreviations for cases where it's obvious even to non-experts, like mm for millimeters.

Re: SQL style guide

#83

Post by Pxtl »

I never get the fussing with aligning. I get the importance of clean indenting, but so much SQL I see looks like the writer spent a long time tweaking the alignment with spaces to make sure the data types lined up perfectly or the ON clauses or what have you.... And then had do do it all over again the moment they changed something.

Just indent when scope changes, and don't align things. Alignment is a time-sink.

Re: SQL style guide

#84

Post by bigger_cheese »

Pxtl wrote: post #82
bigger_cheese wrote: post #79

Naming conventions are pretty controversial I think. The databases I deal with are mostly for storing time series data taken from measurement instruments (i.e Load Cells, Thermocouples, Flow transmitters, Pressure gauges etc). We seem to use prefixes to distinguish columns i.e. MS_ (Mass in Tonnes, MS_KG_ etc. Is used If not stored as Tonnes) VL_ (volume, in cubic meters) RT_FL_ (is Flow rate in normal cubic meters p…

I tend to prefer English suffixes and camelcase rather than Hungarian prefixes and underscores. Like, for a steel coil, InnerDiameterMM, MassTonnes, GaugeMM, etc. I prefer to leave abbreviations for cases where it's obvious even to non-experts, like mm for millimeters.

My only issue with that is you'd wind up with some very long column names.

Using prefixes you can encode a lot of information in a relatively short ID. Which may or may not be useful. As you mentioned though the downside is it requires some domain knowledge to decode.

A lot of our columns are in a form like this:

TP_TSC_HEX1_XXXXX

(where XXXX is some ID usually from scada system)

I can see from the name this is a temperature corresponding to thermocouple id: XXXX in TSC Heat exchanger 1.

Edit: I should also mention some programming languages (not sql) have a language feature for specifying named ranges (I think it is called lazy evaluation) which tends to favor prefixed names over suffixed - I.e x1,x2,...,xn to iterate over the prefixed range. We use SAS heavily at my work which supports using this syntax, which probably influenced the design of our DB tables.

Re: SQL style guide

#85

Post by Stasis5001 »

quizotic wrote: post #80
Stasis5001 wrote: post #47

Weird, I really dislike a lot of the suggestions. In particular, an example: SELECT first_name AS fn FROM staff AS s1 JOIN students AS s2 ON s2.mentor_id = s1.staff_num; We already agreed that staff is a good name for a table, so why are we renaming it to s1? There are all sorts of subtle bugs that arise when s2.mentor_id = s1.staff_num is wrong, and the variable names provide no help here that we're doing things rig…

> Honestly, I think AS should basically never be used for tables. I believe AS is required for self-joins. A common expository query is to find all employees who earn more than their managers, which can be expressed via "select name from staff as employee join staff as manager on (employee.manager_id = manager.id) where employee.salary > manager.salary". Also, SQL allows a sub-select to be listed in the FROM clause.…

Sure, I forgot the self-join case. In my experience these are a minority of joins, but definitely. In postgres at least, a lot of self-joins can be solved with a DISTINCT ON instead.

Sub-selection with a FROM clause, yeah, those definitely need names. But those don't have perfectly good names that everybody already knows, whereas tables do. If you're maintaining somebody's code and they declare

  foo_bar = 10
I think it'd be pretty strange to ever consider writing

  fb = foo_bar
just because you find foo_bar too long to write or something.

Re: SQL style guide

#86

Post by userbinator »

mmgutz wrote: post #15

Still dont get why sql uses all caps for keywords. It's one of the few languages that has it as a best practice. I can read C/JavaScript/go just fine without all caps. I do it anyway to conform.

I have a feeling it may be related to increasing its COBOL-ness. Both are languages that were designed to be "human-readable".

Re: SQL style guide

#87

Post by ryeguy »

no_protocol wrote: post #55

I have often seen commas placed at the start of each line when columns are being listed. I'm a little surprised this guide doesn't follow that convention. It would fit nicely with the established pattern of lining everything up: SELECT first_name , last_name , email_address FROM users Can anyone speculate why it's not done this way in the guide? I think the reasoning behind it was to make later modification easier be…

I just think it looks visually ugly. I've heard others think that too, so it's not just me. I don't feel like the problem it solves is really that big of a deal. I can always just clone the second line up since order normally doesn't matter.

Re: SQL style guide

#88

Post by alistairbayley »

6nf wrote: post #66
mmphosis wrote: post #41

Earlier quoted context omitted.

Or avoid expensive, uncooperative, proprietary SQL vendors. I'll keep pretending and stick with sqlite and PLSQL.

I don't know if you've ever worked with MS SQL Server in production but for us it's been the most amazing piece of technology in our stack for the past 15 years. SQL Server is in the top 3 best things to be published by Microsoft ever. (The other 2 are probably Excel and Flight Simulator)

What's so amazing about it, when compared to the other SQL DBMS's? (e.g. postgresql, oracle, sybase, db2, etc)

Especially as it's just a re-badged Sybase...

Re: SQL style guide

#89

Post by ryeguy »

dahdum wrote: post #16
Dowwie wrote: post #5

"Where possible avoid simply using id as the primary identifier for the table." Has anyone had trouble by using surrogate primary keys? I've found the opposite of what the author said could be more true: composite keys should be avoided instead.

I disagree with this too, using table.id is easier to type and consistent when creating joins. It also encourages (forces) to reference the fields with tables prefix.

One advantage of using user_id on both the pk of users as well as the fk side is you can do:

    INNER JOIN comments USING (user_id)
instead of:

    INNER JOIN comments ON comments.user_id = user.user_id

Re: SQL style guide

#90

Post by Treffynnon »

no_protocol wrote: post #55

I have often seen commas placed at the start of each line when columns are being listed. I'm a little surprised this guide doesn't follow that convention. It would fit nicely with the established pattern of lining everything up: SELECT first_name , last_name , email_address FROM users Can anyone speculate why it's not done this way in the guide? I think the reasoning behind it was to make later modification easier be…

This is a pretty commonly asked question. The answer is two fold:

1. putting the comma before doesn't actually make commenting out columns easier - it just moves the problem to the other end of the list. Just try commenting out `first_name` in your example and you'll see what I mean.

2. because normal convention would be for the comma to come immediately after an item in written English.

Post reply on HN