3 ms·
How would Select * break with a new column?
by croes 3y ago
How would Select * break with a new column?
- cwbriscoe 3y agoIf you have a table with two columns and you do a 'select *', adding a column to the table can break code that is only expecting 2 columns.
- croes 3y agoUsually code refers to columns by name and additional columns are just ignored, the only case I know is when you add a column to a joined table and the column name already exists in the other table resulting in ambiguous column names. In an exists clause the * is harmless
- cwbriscoe 3y agoI am talking about 'select ' at the top level, they can be harmless in exists, sub-selects and CTE's. The number of columns sent to the client (the program) will change when you add a column to the database. If you don't remember to change all of the places in your code where you used a 'select ', you program is likely going to fail or have unexpected results.
- hobs 3y agoAny situation where a new or elsewise unknown attribute breaks your code, binding is a big one. * Say you are joining two tables and one now has a conflicting/duplicate name, surprise, you now have broken code, it literally will not execute with ambiguous duplicate references. * By the same token, downstream views can break for the same/similar reason. * In some engines views are not going to actually include your columns until they are "refreshed"(SQL Server) so that one day that's out of band of your deployment your views will suddenly change. * Say you have a report with specific headers - tada, it's now got whatever people add to the table - and sourcing it can be a pain because its unclear where the source is in the query, requiring schema. * Performance expectations can change if the data type is much larger, up to the point of actually breaking the client or consuming tens of billions of times more resources.