CODEWITHSUNDEEP
sqlsql-servermicro-optimization
Sterling Archer
Sterling Archer

Reputation: 22405

SQL INSERT order

When using a SQL INSERT, does it matter if you ignore the order of how the columns appear in the db?

|-----------------------|
| id | derp | foo | bar |
|-----------------------|
| 1  |"herp"|"bar"|  88 |
|-----------------------|

So the order in SQL Server (2008 specifically) is id, derp, foo, bar. (Let's assume id is PK, and autoincrementing)

So I would write some SQL like this:

INSERT INTO table_name (foo, bar, derp) VALUES (42, "troll", "lolz");

Normally I would of course write it like:

INSERT INTO table_name (derp, foo, bar) VALUES ("lolz","troll", 42);

Now, this may be micro-optimization, but I'm curious if inserting out of column order will slow down my query at all?

Upvotes: 3

Views: 283

Answers (1)

davek
davek

Reputation: 22915

That sort of thing should not matter at all, as SQL is intended to be declarative by nature: you tell the database what you want, and it works out the best way to achieve it.

Of course, there are exceptions to this, otherwise there would be no need for optimizer hints and the like. But the trend - generally speaking - is away from having to worry about the finer workings of the database, to release you, the developer, for other tasks.

e.g. self-managed tablespaces, auto-assigned partitions (both available in Oracle)

HOWEVER....as we have to learn to deal with ever expanding layers of abstraction, and as these abstractions often contains leaks (when they do not work 100% as intended), then, from time-to-time, it is necessary to understand what is going on behind the scenes. If you are able to do that, or know how to find out information about how to do that (almost as good!), then that will stand you in good stead.

For that reason, I can recommend this excellent essay by Joel Spolsky to you:

http://www.joelonsoftware.com/articles/LeakyAbstractions.html

The law of leaky abstractions means that whenever somebody comes up with a wizzy new code-generation tool that is supposed to make us all ever-so-efficient, you hear a lot of people saying "learn how to do it manually first, then use the wizzy tool to save time." Code generation tools which pretend to abstract out something, like all abstractions, leak, and the only way to deal with the leaks competently is to learn about how the abstractions work and what they are abstracting. So the abstractions save us time working, but they don't save us time learning.

And all this means that paradoxically, even as we have higher and higher level programming tools with better and better abstractions, becoming a proficient programmer is getting harder and harder.

Upvotes: 4

Related Questions