Click here to Skip to main content
15,888,014 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more: , +
I tried below code but it is for sql server 1014.

My aim is
1. Fill product Ids from salesOrder table.
2. Then get price of there products from 'price table' Join TablleX
3. Finally get sum of price.

Its Old running code, so can not change any thing in table design. only option is bit modify SP.

What I have tried:

SQL
Declare @tmptable table (Id int identity(1,1),ProductId int INDEX IX3 NONCLUSTERED(Id,ProductId))


Here error near to INDEX keyword.

Thanks
Posted
Updated 30-Mar-16 23:35pm
Comments
F-ES Sitecore 31-Mar-16 5:27am    
What is the error?
Santosh K. Tripathi 31-Mar-16 5:33am    
thanks for response.

1. incorrect syntax near to 'INDEX'.
2. incorrect syntax near to 'ID'. Expecting select, or '('. -(this is after NONCLUSTERED key word).

1 solution

Please see CREATE INDEX (Transact-SQL)[^]
You have to declare the table first, and create your index on it in a second time.

Updated after comment:
I checked, and a temp table is declared with the # sign, not the @ sign (which defines a variable).
Thus:
SQL
CREATE TABLE #tmptable (Id int IDENTITY(1,1) PRIMARY KEY, ProductId int)
CREATE NONCLUSTERED INDEX ind1 ON #tmptable (Id, ProductId)

The NONCLUSTERED keyword is optional, as it is the default.

Source:
Create Index on Temp Table[^]

Note: you will have to drop the temp table at the end of your script if you do not want it to persist:
SQL
DROP TABLE #tmptable


OR
You can declare an index in a table variable, you just omitted a comma:
SQL
DECLARE @tmptable TABLE (
  Id int identity(1,1)
 ,ProductId int
 ,INDEX IX3 NONCLUSTERED(Id,ProductId)
)

Source:
SO: SQL Server : Creating an index on a table variable[^]
 
Share this answer
 
v3
Comments
Santosh K. Tripathi 31-Mar-16 5:43am    
as you told new code is

Declare @tmptable table (Id int identity(1,1) primary key ,ProductId int)
CREATE NONCLUSTERED INDEX ind1 ON @tmptable (Id, ProductId)

Now in second line
1. incorrect syntax near to '@tmptable'.

Note:- i have to use table variable, not temp table.
phil.o 31-Mar-16 5:54am    
Please see my updated answer.
Santosh K. Tripathi 31-Mar-16 5:59am    
Note:- due to some reason, i have to use table variable, not temp table.
phil.o 31-Mar-16 6:00am    
Please re-check the solution :)
Santosh K. Tripathi 31-Mar-16 6:05am    
thanks for response. i just copy and paste you code.


DECLARE @tmptable TABLE (
Id int identity(1,1)
,ProductId int
,INDEX IX3 NONCLUSTERED(Id,ProductId)
)

still same error...

1. incorrect syntax near to 'INDEX'.
2. incorrect syntax near to 'ID'. Expecting select, or '('. -(this is after NONCLUSTERED key word).

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900