In tsql what does tablename(1) mean

Question

I'm trying to analyze a query to extract the logic and I am seeing several select statements that use the syntax SELECT FROM tableName(1). Below is a few examples of this syntax being used. This is a fairly large insert statement so I am unable to post the entire 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 the (1) means in the from statement?

Answer 1

I've done some digging and I think what's happening here is that you're actually using a Query Hint which is the '(1)'. This is intereprted as a INDEX Query hint, you can read more about these here.

According to documentation using this format without specificing WITH is deprecated and I cannot do this on 2008, but maybe you're using a different specific version or use some compatibility mode that affects this.

If you can provide more resources on queries containing these syntax, I'd be able to 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've looked further into this, ASSUMING that I'm right with my assumption and

SELECT 1 FROM TABLENAME(1)

Equals

SELECT 1 FROM TABLENAME WITH (1)

Then the below proves the point, when specifying an integer greater than the number of indexes available in the table will cause 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.