Can SQL Server Queries Use Ordinal Position for Data Selection?

Ordinal Position in SQL Server Data Selection

Retrieving column data using ordinal position is generally discouraged as it's a non-portable practice that can lead to errors. However, in certain scenarios like occasional data import processes, it may be necessary. This article explores whether ordinal positions can be used to select data in SQL Server.

Can we Use Ordinal Position to Select Data?

The direct answer is no. SQL Server does not support using ordinal positions to select column data. Instead, you must specify column names explicitly in your queries.

Workaround: Unioning Tables with Known Column Names

If you know the number but not the names of columns, you can use a workaround involving unioning two tables:

select NULL as C1, NULL as C2 where 1 = 0 
-- Returns empty table with predefined column names
union all
select * from Test 
-- There should be exactly 2 columns, but names and data type doesn't matter

This creates a table with two columns, [C1] and [C2], whose data you can select using ordinal positions:

select [2] from Test

Note: This approach is not practical for tables with a large number of columns and works best for tables with a small and predefined number of columns.

The above is the detailed content of Can SQL Server Queries Use Ordinal Position for Data Selection?. For more information, please follow other related articles on the PHP Chinese website!