Bugs in SQL table functions

The version after SQL2008 provides the programming of table functions, which is a very useful feature, but there is a problem of egg-drop. When we use SELECT * in a function to connect to other tables, the result is misplaced if new fields are added to the original table. The sample code is as follows

Table function Code

Use [extest]go/****** Object:  userdefinedfunction [dbo].[ Test]    Script date:02/06/2015 17:16:58 ******/set ansi_nulls ongoset quoted_identifier ongoalter FUNCTION [dbo].[ Test] () RETURNS Table Asreturn (SELECT * FROM dbo. Aleft JOIN  dbo. CON  a.aid=c.cid         )

Table A fields are aid and aname, and Table C is the field of CID and CDemo. The execution results are as follows.

You can see the results of the execution, and table A and table B are connected together. Now we add a field at Table A. Then execute the table function and get the following result.

will see, CID and CDemo data dislocation!!! Such consequences are quite serious. Since the project is running, it may be necessary to increase the field, and this field increases to get such misplaced data, which results in extreme severity.

So what?

The reason for the analysis is that the SQL has pre-optimized processing when the function's SELECT * is generating the function. When we add a field, the function keeps the previous pre-optimization result, causing the data to be misaligned.

Workaround:

1. After the new field, each corresponding function is re-executed once. (This is quite a waste of power, and it's probably wrong)

2. Using the VS Database schema Synchronization tool, make a backup library, then add fields to the backup repository and then sync to the official library. This automatically executes the related function.

3. Avoid using SELECT * to write it into a specific field.

Reprint please indicate the source.

Bugs in SQL table functions