SQL Server Performance Forum – Threads Archive

Table Variables

In a stored procedure I have two tables variables: DECLARE @Devol TABLE (Idbint,
Familiachar(6),
CostoRepnumeric(24, 4)
PRIMARY KEY CLUSTERED (Idb, Familia)) DECLARE @Venta TABLE (Idbint,
Familiachar(6),
CostoRepnumeric(24, 4)
PRIMARY KEY CLUSTERED (Idb, Familia)) When I do the join, it doesn#%92t uses the primary key, and executes a table scan for the two variables. If I write the CREATE in this way DECLARE @Devol TABLE (Idbint,
Familiachar(6),
CostoRepnumeric(24, 4),
CONSTRAINT PK_DEVOL PRIMARY KEY CLUSTERED (Idb, Familia)) the stored procedures doesn#%92t compiles, and if I write the SELECT in this way SELECT V.Idb, V.Familia, ISNULL(V.CostoRep, 0) – ISNULL(D.CostoRep, 0)
FROM @Devol D RIGHT OUTER JOIN @Venta V (INDEX = 1) ON V.idb = D.Idb AND V.Familia = D.Familia Either it uses the primary key How can I do to force the SELECT to use the primary key and to execute an index seek? Thanks

---

I have not tried this myself using table variables, so I know if they suggestion will work, but you might want to consider experimenting with an appropriate hint to force the use of the correct index. See this URL for information about hints. http://www.sql-server-performance.com/hints.asp
——————
Brad M. McGehee
Webmaster
SQL-Server-Performance.Com

---

I tried it, but it doesn#%92t works.
If I use the hint INDEX = 1 after the table variable in the from, the stored procedure doesn#%92t compiles (Instead if I use in the from a table, the stored procedures compiles).
I tried to force the index with the name, but when I create the table variable I can#%92t give a name to the primary key because the stored procedures either compiles

---

I am out of suggestions. And since there has been no other feedback so far, it looks like few other do also. So if you have any ideas for abertini, please offer them.
——————
Brad M. McGehee
Webmaster
SQL-Server-Performance.Com

---

Can you recreate the table alongwith indexes to affect the change. Try it and see. Satya SKJ

---

]]>