Can You Select SQL Server Columns Using Ordinal Position?

Selecting SQL Server Data Using Column Ordinal Position

While using ordinal positions for column selection is generally discouraged, it may be necessary for certain scenarios, such as one-time data importing processes. The question arises: is it possible to select column data using the ordinal position in SQL Server?

To elaborate on the example provided, consider the following table:

create table Test(
    Col1 int,
    Col2 nvarchar(10)
)

Instead of using:

select Col2 from Test

Can we achieve the same result using:

select "2" from Test -- for illustration purposes only

The answer is: no.

SQL Server does not allow selecting columns based on ordinal position. Instead, it requires specifying the column names explicitly. Therefore, the second example provided is not valid syntax.

If the number of columns is known but not their names or data types, a workaround can be employed:

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 method creates a temporary table with the required number of columns, allowing us to access the data using the ordinal position as an index. However, it is not recommended for tables with a large number of columns.

The above is the detailed content of Can You Select SQL Server Columns Using Ordinal Position?. For more information, please follow other related articles on the PHP Chinese website!