Can i use column number instead of column name in where condition of a select query in SQL Server 2005?

Question

hi, can we use column number instead of column name in where condition of a select query in SQL Server 2005 so that even if the column name is so long.

thanks in advance.

Answer 1

no you cannot directly, just drag the column name you want from the object explorer into your query...you don't need to type it

Hit F8, this will open Object Browser. Navigate to DatabaseName/TableName/Columns. Click on the column folder and drag the column folder into the Code Window. Upon release you will see that all the column names are in the Code Window.

Another option would be (and I do not recommend this )

you would need to use dynamic SQL and do a lookup in information_schema.columns and look for ordinal_position to get the column number. If you need column 5 you would get ordinal_position 5 to grab the column name. this however is messy

Answer 2

If you have a hard time remembering the column names, there are some SQL intellisense programs out there, I know RedGate offers one.

SqL Prompt

SQL 2008 finally comes with Intellisense.

Answer 3

No, you can't do this. Even if you could, it would be considered bad practice. Column names are far more readable than numbers, no matter how long the column names are (considering that column name limit is 128 chars in MS SQL)

Answer 4

No, you can't, but you can create an inline view instead:

SELECT  *
FROM    (
        SELECT  Supercalifragilisticexpialidociousness AS s
        FROM    mytable
        ) q
WHERE   s = 1

SQL Server is smart enough to push the predicate into the view so it won't hurt performance.

Answer 5

Even if you could it would be asking for trouble.

Imagine, a few months later, you add a column to the SELECT clause and forget/overlook the matching WHERE clause...

Answer 6

here is my query select * from table_name where somecolumn=5 here i get the column name dynamically so insted so insted of using column name can i use the column number

From your comment I guess you are trying to query by getting a column name dynamically. You can do this by fetching the column names into a variable and the creating a dynamic SQL query.

declare @colname varchar(128)
declare @sql varchar(max)
set @colname = 'Server'
set @sql = 'select * from Jobs where '+@colname+'='+'''S02AAPEXP01'''
exec (@sql)

Beats me why you would want to do this, though!!!

Raj

Answer 7

This is just an all around bad idea. Use the column name no matter how long it is. Honestly, this is a maintenance nightmare and you will regret doing this.

And really try to avoid using dynamic SQL; it is generally a poor practice.

Answer 8

No, you can't do this for a WHERE clause. You CAN, however, do this for an ORDER BY clause. I use it all the time for ad-hoc queries where I don't want to have to re-type a complicated function in the ORDER BY:

SELECT ServerCode, Count(*) as Databases 
FROM DatabaseList 
GROUP BY ServerCode 
ORDER BY 2 DESC

or

SELECT EventCode, EndTime, Convert(Varchar(50),TextData) as QueryName, CPU, READS
FROM TraceData
ORDER BY 3

Now, would I put it in a production stored proc? Probably not.

Answer 9

Here are two tips for quickly selecting multiple column names, both which seem to be rarely known/used by SQL Server developers.

1 - Highlight your table name in Query Analyzer (double-click it). Then press Alt-F1. You will see tons of info about the table, among it the column names. Drag to select one or more column names, and use Ctrl-C, Ctrl-V, to paste them into your query. This is also a good way to see what indexes and constraints are on the table.

2 - From Object Explorer, right-click on your table and select Script Table as/SELECT To/Clipboard. Then paste the results into your query and remove the parts you don't need.