Is it possible to select sql server data using column ordinal position

Question

Is it possible to select column data using the ordinal_position for a table column? I know using ordinal positions is a bad practice but for a one-off data import process I need to be able to use the ordinal position to get the column data.

So for example

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

)

instead of using

select Col2 from Test

can I write

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

Ambages

Thanks everyone for taking time to answer my questions. It looks what I'm trying to do cannot be done. – Ambages

Rats. I was just typing up this same question. – Frequent

Great question. I was just wondering if this was possible today. – Shanna

Answer 1

If you know quantity of columns, but don't know its names and types, you can use following trick:

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

As a result, you will have a table with 2 columns [C1] and [C2]. This method is not very usefull if you have 100 columns in your table, but it works well for tables with small predefined number of columns.

Bodily

This is a nice trick, but what happens to the execution plan? Say I have a million rows in my table, and say I want to select top 1000. Would this trick still let the server use the index? – Tinishatinker

This is the best way if you're returning data from a pivoted query since information table schema won't help if skipping some columns. Alternatively Dynamic SQL is the only other way I would recommend. – Overblown

@FyodorSoikin, just add "top 1000" in the 2nd select statement. – Maser

I didn't ask "how to select top 1000" @oldpride. I asked what happens to the execution plan in that case. – Tinishatinker

Answer 2

You'd have to do something like

declare @col1 as varchar(128)
declare @col2 as varchar(128)
declare @sq1 as varchar(8000) 

select @col1 = column_name from information_schema.columns where table_name = 'tablename'
and ordinal_position = @position

select @col2 = column_name from information_schema.columns where table_name = 'tablename'
and ordinal_position = @position2

set @sql = 'select ' + col1 ',' + col2 'from tablename' 

exec(@sql)

Izolaiztaccihuatl

Thanks. My first attempt was using dynamic SQL which actually slowed my proc contrary to what I expected which led me to wonder if I could use the column ordinal position info any which way. – Ambages

Answer 3

You can use this query

select * from information_schema.columns

to get the ordinal positions of the columns. Like Michael Haren wrote, you'll have to build a dynamic query using this, either in code or in a sproc that you pass the column positions to.

FWIW, this is pure evil.

Also, deathofrats is right, you can't really do this, since you'll be building a regular query w/ column names based on position.

Izolaiztaccihuatl

Answer 4

Yes, you could do this with some really ugly hits into the system tables. You'd probably need to fall into the world of dynamic sql.

I really, really do not recommend this approach.

If that didn't deter you, then this might get you started (ref):

select table_name, column_name, ordinal_position, data_type
from information_schema.columns
order by 1,3

Author

Answer 5

No, you can't select columns based on their ordinal position, as far as I know.

When looking at the transact SQL reference, there is nothing to suggest you can (http://msdn.microsoft.com/en-us/library/ms176104(SQL.90).aspx).

Sunbreak

Answer 6

If you know the number of columns, one way might be to transfer the data into a tmp table with that number of columns and select from the temp table...

declare @tmp table(field1 sql_variant, field2 int, field3 sql_variant)

insert into @tmp
select * from Test

select field2 from @tmp

Neilla

Answer 7

An option you have is using conditions: In your example:

    SELECT 
     CASE YourColumnNumber 
      WHEN "1" THEN Col1
      WHEN "2" THEN Col2
      ELSE "?"
     END AS Result
    FROM Test

Going to schema will slow query I am afraid...

Augite

Answer 8

I don't think you can. As @Michael Haren showed, you can use ordinal positions in ORDER BY clauses but I've never seen them used elsewhere in SQL Server.

I'm not sure what problem you are havng with your one-off data import that this would help with - presumably some unfortunate column name? Can you explain a little more?

Ferryman

@Ferryman - I am working with a DB that uses mapping info. Col names are stored in a mapping table. A proc with a HUGE case statement runs import and I am trying to improve it. Using dynamic SQL did not speed up the process. – Ambages

ah, I see. That's quite an interesting problem! I wonder if it could be done by creating views dynamically? Wish i had something to hand right now to play with! :-) – Ferryman

Answer 9

I don't know of any way to do this short of using dynamic SQL. Maybe if you include a bit more information about why you feel you have to use the ordinal values someone on here can give you advice on how to get around that problem.

EDIT: I see that you answered this to some degree in another comment. Can you provide more specifics? The import proc and/or table definitions?

Erie

Answer 10

If you are using MS SQL 2005 you can use the ROW_NUMBER function.

SELECT Col1, Col2, ROW_NUMBER() OVER(ORDER BY Col1) FROM Test WHERE ROW_NUMBER() Over(Order BY Col1) Between @Position AND @Position

That should get you the desired results if I am reading the question correctly.

Benedicite