SQL Server New column added to table but problems with Select *

Morning,

I have a stored procedure that returns 'SELECT *' from a table.

Whenever I add a new column to the table, the 'SELECT *' often returns some data in the wrong columns.

Is this an optimization or caching problem?开发者_如何学运维 How do I solve this without having to explicitly define the return column names in my stored procedure?

Thanks!

Regardless of the exact nature of your problem, or a solution to it, I would recommend that you don't use Select * From Table. This is less efficient - each time you run the query, an extra request is sent to the DB to determine exactly what columns '*' constitutes, and then a proper request is sent with specific column information.

The reason for your problem probably depends on the client stack you're using.

Speaking in very general terms, SQL Server will return the columns in the order they're added to the table if you perform a SELECT *. If there is more than one table involved in the query, it will return the columns from each table in the order they appear in the query.

Neither caching nor optimisation should affect this server-side if the table columns have changed, so it may be something happening between your code and the server, in whatever data access stack you happen to be using.

This is one of the reasons it's generally recommended not to use "SELECT *" in client code.

The best way to avoid this problem is to have the stored procedure return a predefined set of columns (SELECT a, b instead of SELECT *) and use a table join to retrieve the rest of the code. Because stored procedures cannot be part of a query, you could refactor the stored procedure into a table-valued user-defined function and perform a join on it:

SELECT f.a, f.b, t.*
FROM dbo.fn_YourFunction('a', 'b') f
INNER JOIN YourTable t ON f.id = t.id