When working with SQL Server, it’s important to be aware of certain gotchas that can affect the behavior of your code. One such gotcha involves the use of user-defined table functions (UDFs) and the asterisk (*) method for selecting columns from a table.

Let’s say you have a UDF that joins two tables and selects all columns from the first table using the asterisk method. This can be convenient when dealing with tables that have a large number of columns, as it saves you from having to explicitly list each column name in the select statement.

However, a problem arises when columns are added or removed from the base table without recreating the function. In such cases, the output of the function may not be what you expect. Let’s take a look at an example to illustrate this issue.

-- Create sample table A
CREATE TABLE dbo.TableA (
    recordid DEC(5,0) IDENTITY(1,1),
    columnA CHAR(10),
    columnB CHAR(10),
    columnC CHAR(10)
)

-- Create sample table B
CREATE TABLE dbo.TableB (
    recordid DEC(5,0) IDENTITY(1,1),
    fieldA CHAR(10),
    fieldB CHAR(10),
    fieldC CHAR(10)
)

-- Insert sample data into table A & B
INSERT INTO dbo.TableA VALUES('Robert', 'Jimmy', 'Jones')
INSERT INTO dbo.TableB VALUES('Fish', 'and', 'Chips')

-- Create a function that returns a table result from table A and table B
-- This function uses the select all columns (*) option for table A.
CREATE FUNCTION TestFunction ()
RETURNS TABLE
AS
RETURN
    SELECT TableA.*, TableB.fieldA, TableB.fieldB, TableB.fieldC
    FROM dbo.TableA AS TableA
    INNER JOIN dbo.TableB AS TableB ON TableA.recordID = TableB.recordID

In the above example, we have two tables, TableA and TableB. We then create a UDF called TestFunction that joins these two tables and selects all columns from TableA using the asterisk method. The function returns a table result that includes columns from both tables.

Now, let’s see what happens when we alter TableA by adding a new column:

-- Alter TableA to include a new column
ALTER TABLE dbo.TableA ADD columnD CHAR(10) NULL

If we run the TestFunction again, we will notice that the output has changed. The columns from TableB have shifted one position to the right, and the new columnD from TableA is now included in the output. This is because the UDF stores a table definition in SQL Server’s system tables, and this definition does not get updated automatically when the underlying table changes.

To overcome this issue, you have a couple of options. One option is to recreate the function every time the underlying table changes. This will update the system tables with the changed layout caused by the asterisk selection method. However, this can be cumbersome and time-consuming, especially if you have a large number of UDFs.

A better option is to explicitly list each column name in the select statement of the UDF. This ensures that the output columns will always maintain the same position as defined in the system tables, regardless of any changes to the underlying table.

Of course, explicitly listing each column name can be tedious, especially if you have tables with a large number of columns. To make this process easier, you can use the SP_Help stored procedure and Microsoft Excel. Here’s a step-by-step guide:

1. Within SQL Server, run the following command to get a list of the table’s columns:

SP_HELP [dbo.TableName]

2. This will produce a list of the table’s columns. Copy the column names from the “Column_name” column.
3. Open Microsoft Excel and paste the column names into a worksheet.
4. In Excel, use the CONCATENATE function to add a comma and space (“, “) before each column name.
5. Copy the modified column names from Excel and paste them into your SQL select statement, removing the leading comma.

Using this technique, you can quickly build a field list to paste into your SQL statement, saving time and reducing the chance of introducing typos in the column names.

In conclusion, it’s important to be aware of the potential issues that can arise when using the asterisk method to select columns in a user-defined table function. By understanding the underlying behavior of UDFs and taking the necessary precautions, you can avoid unexpected output and ensure the accuracy of your SQL Server code.

Remember to clean up any tables and functions created for testing purposes using the following commands:

-- Clean up afterwards
DROP TABLE dbo.TableA
DROP TABLE dbo.TableB
DROP FUNCTION TestFunction

So, the next time you’re working with user-defined table functions in SQL Server, be mindful of the asterisk gotcha and consider explicitly listing your column names to avoid any unexpected surprises.