One biggest advantage of table variable for me is you can’t put index on the columns. I was having a problem where doing self join in table variable (it has 1000+ records) is taking really really long time. One way I found to optimize it is to add primary key assuming every single record is unique on the table variable. The performance improvement is really significant by adding the primary key on table variable. And by changing table variable to temp table, it improves the performance more than by twice
CREATE TABLE #dtblJobsBillDates ( chProcessID CHAR(03) COLLATE database_default NOT NULL, intAssgnmtID INTEGER NOT NULL, chBillOfficeCode CHAR(03) COLLATE database_default NOT NULL, chBillEntityCode CHAR(03) COLLATE database_default NOT NULL, chBillNo CHAR(08) COLLATE database_default NOT NULL, PRIMARY KEY (intAssgnmtID, chBillNo, chProcessID, chBillOfficeCode, chBillEntityCode) )
Leave a Reply