[Solved] SELECT columns by column-index NOT by columnname!

The column-index is for internal use for the database. Of course you can query the indexes, but they don't mean much. While in most part they column index will be the order the columns created, but they can change if you delete or insert columns.

There is no need or usage in using the column-index in your query, unless you are certain it will NEVER change AND you are willing to take consequence and add headache whenever it changes.

Make your like easier by using column names.

I've had to do something like this in the past when working with a couple of products/technologies that didn't play together. I had to use columns that were called A B and C, though, but the solution might be similar.

You can create a view and rename the column names in the process mapping from the name to a number.

So, you'd do something like:

select firstname as [1], lastname as [2] from mytable;

If you do that, you'd be able to select by a numbered column system, but I'm not sure if this helps your particular situation.

Cheers.

I got my answer.

Here is the query by which we can select records by column index

Declare @WhichOne int;
Declare @Sql varchar(200);
Set @WhichOne = 2;
With cte As
(Select name, Row_Number() Over (Order By column_id) As rn
From sys.columns
Where Object_Name(object_id) = 'MyTable')
Select @Sql = 'Select ' + QuoteName(name) + ' From MyTable'
From cte
Where rn = @WhichOne;
Exec(@Sql);

Declare @WhichOne int;
Declare @Sql varchar(200);
Set @WhichOne = 2;
Set @Sql = concat('Select ', QuoteName(Select name from sys.syscolumns
    Where Object_Name(object_id) = 'MyTable' And colid = @WhichOne), ' From MyTable')
Exec(@sql)

Set Sql = Concat('Select ',
    QuoteName((Select name From cte Where rn = 1)), ', ',
    QuoteName((Select name From cte Where rn = 2)), ', ',
    QuoteName((Select name From cte Where rn = 3)), ', ', ' From MyTable');