Results of the SQL Performance Quiz

The biggest mistake in SQL was in using NULLs to mean three different things (no record found in an outer join, vs not applicable, vs not yet known). Fortunately with some types (varchar) on sane db's you can use dedicated empty values ('' for varchar) for not applicable, but you are still stuck with the other two as possibly conflicting.

This is actually a big issue because your ability to have complex declarative constraints goes up with table width, so breaking off potentially nullable columns also removes them from being available for cross-column check constraints.

So, yeah, but, like UPDATE can employ a WHERE clause, to apply conditional logic, whereas INSERT, not so much. You insert one record to a table. As long as you know the table? Boom. Done. Oh, but now you'd like to change some records in the table? Well, that's great, but which ones? Tell me where?

I think the whole readable natural language premise is a neat idea, but much in the same way that natural language can be easily complicated, so too, with SQL. Heap abstractions of logic and arithmetic on top, and it gets even worse. But it's still a cool idea. If it works out in your favor, you'll get self documenting code. But alas, there is no floor or cieling to the complexity one can introduce, be it deliberately or accidentally. And not only is it the query itself (or the author) that might over complicate matters. The data schema, defined by the DDL, can easily induce unwanted complexity in subtle ways, and this might be a problem deliberately placed beyond the reach of the person attempting to read or change the data locked within.

As for complaints about parsers, that's not a deficiency of the language. It's a deficiency of the tools used. If the development environment isn't presenting problems to the developer in a convenient manner, then the utility sucks. Get a new one. Don't blame the text-mode clients though. They are just as bare-bones as a command line, and deliberately so. At a certain level, SQL can be regarded the same way a shell script might be regarded, in terms of possessing the convenience of (or lack thereof) a basic TTY command interpretter. But there's no real reason why it should be impossible to create a SQL script authoring tool, which provides syntax validation as convenient as any compiled language might employ, when communicating compilation errors to the developer.

No, it wouldn't.

If we're told that:

SELECT a, date_column FROM tbl WHERE a = @a

Returns 100 rows.

Then:

SELECT a, date_column FROM tbl WHERE a = @a AND b = @b

Will only have to scan column b over 100 rows.

Even without an index that will always be neglible, not compared to using the index to grab 100 rows from 10million but just compared to running a query and returning results at all.

The reason that the original can be a lot slower is that the 100 and 10 rows of results are comprised of a lot more rows of actual information, because of the grouping.

You're right that:

SELECT a, date_column FROM tbl WHERE a = @a AND b = @b

would be a lot slower, given the same data, but that isn't the scenario, the group by has implications about what "returns 100 rows, returns 10 rows" actually means in terms of data read.

Not enough info to make good choices on all, at least for MSSQL. Besides, the correct answer to most database issues is "it depends".