In tsql, the value of tablename (1) means

Question

I am trying to parse a query to extract logic, and I see several select statements that use the SELECT FROM syntax tableName (1). The following are some examples of using this syntax. This is a pretty big insert statement, so I cannot post the whole query.

select nh.firm_code, nh.acct_num, nh.sub_code, b.amt from nav_hist nh(1), breakpoints b where nh.sales_load_id = b.schedule_id select sum(weekdays.factor) from calc_hist weekdays(1) where weekdays.sys_date >= dateadd(dd, f.usr_num_days - 7, f.sys_date) and weekdays.sys_date < f.sys_date and c.firm_code = weekdays.firm_code 

Does anyone know what (1) means in a from statement?

Answer 1

I did a digging, and I think what happens here is that you are actually using a query hint, which is "(1)". This is interpreted as an INDEX prompt, you can read about it here .

According to the documentation using this format without specifying WITH, is deprecated, and I can't do it in 2008, but maybe you are using a different specific version or using some kind of compatibility mode that affects this.

If you can provide more resources on queries containing this syntax, I can continue digging.

Here is what I get when I run on my server:

 select * from sysobjects WITH (1) --Warning: Index hints supplied for view 'sysobjects' will be ignored. 

EDIT
I looked further at it, ACCEPTING that I am right with my assumption and

 SELECT 1 FROM TABLENAME(1) 

Equally

 SELECT 1 FROM TABLENAME WITH (1) 

It is then shown below that when defining an integer greater than the number of indices available in the table, it will throw an exception. Take a look:

 --Table sysjobs has 4 indexes select * from msdb..sysjobs with (4) --1 row affected select * from msdb..sysjobs with (5) --Msg 307, Level 16, State 1, Line 2 --Index ID 5 on table 'msdb..sysjobs' (specified in the FROM clause) does not exist.