SQL Table Function BUG

SQL Table Function BUG

In Versions later than SQL2008, table function programming is provided. This is a very useful function, but it has a very bad problem. When we use SELECT * in the function to connect to other tables, if a new field is added to the original table, the result will be misplaced. 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. AID = C. CID)

The fields in Table A are AID and AName, and the fields in Table C are CID and CDemo. The execution result is as follows.

The execution result is displayed. Tables A and B are connected together. Now we add A field AT in table. Then execute the table function and obtain the following result.

The CID and CDemo data are misplaced !!! Such consequences are quite serious. After the project is run, you may need to add fields, and the increase of this field leads to such misaligned data, which results in extremely serious consequences.

What should we do?

The possible cause is that the SELECT * function of the function is pre-optimized when the function is generated. When we add a field, the function maintains the previous pre-optimization result, resulting in data dislocation.

Solution:

1. After adding a field, each corresponding function is re-executed. (This is a waste of effort, and it is likely to make a mistake)

2. Use the database architecture synchronization tool of VS to create a backup database, add fields to the backup database, and then synchronize the fields to the official database. In this way, related functions are automatically executed.

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

Indicate the source for reprinting.