Insert Query: Why is it a bad idea to not include

Question

I was told at my last position never to do this; it wasn't explained why. I understand it enhances the opportunity for mistakes, but if there are just a couple of columns and I'm confident of their order, why can't I shorthand it and just insert the values in the right order without explicitly matching up the column names? Is there a large performance difference? If so, does it matter on a small scale?

If there's no performance hit and this isn't a query that will be saved for others to view, why shouldn't I?

Thanks in advance.

Answer 1

Its mostly for readability so if its just you its fine but if you do muck up the order it can be a hard one to explain if people find out you were not using good practices, especially if someone modifies the structure while you were writing your query... Also if you have complex things to do in a column it can help visualisation and if as you say it is small then adding a few column names is hardly going to take long so you may as well.

Answer 2

Because things change and if you code that query in your program, thinking that the column order will never change and then comes along someone else that decides to alter the table adding a new column your query will fail.

Answer 3

A relation has no left-to-right ordering of columns. The same cannot be said of a SQL table, not a good thing. However, just because SQL has non-relational features doesn't mean you have to use them! Specifying a column list as part of a table or row value constructor is one way of mitigating against SQL's column ordering.

Consider that the following SQL statements are semantically equivalent:

INSERT INTO Table1 (col1, col2, col3) VALUES (1, 2, 3);

INSERT INTO Table1 (col2, col1, col3) VALUES (2, 1, 3);

INSERT INTO Table1 (col3, col2, col1) VALUES (3, 2, 1);

Answer 4

In most development shops, you will not be the only developer working on a given project. In which case, you run the risk of unintended inserts. You may be confident now that there won't be any schema changes and things can change... now or after you're gone.

Also, if a column is added, you inserts will fail.

Answer 5

This is acceptable only when you type your query by hand into an interactive DB tool. When your SQL statement is executed by your program, you cannot be absolutely confident about the order of columns in a table, unless you are the only developer who has access to your database. In other words, in any team environment there is an opportunity that someone would break your query simply by re-ordering columns in your database. Logically, your table would remain the same, but your program would still break.

Answer 6

Devil's advocate: if there are only a couple of columns, what does short-handing it gain you? You saved a few keystrokes, big deal.

For ad hoc queries you're writing once and throwing away, it's not a big deal. I suspect you were told to never do this in production code or anything anyone else would later have to reverse engineer (either simply to understand it, or to account for underlying schema changes). Remember that code that you write may only ever be viewed and maintained by you right now but you should be writing code with the intention that it will outlast you.

Another reason including the column list is good is if you later want to search for all references to a specific column name in your data model...