Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
pdav221
Starting Member
4 Posts |
 Posted - 2008-01-25 : 21:06:01
|
| In January 2004, Bill Richmond wrote this regarding temp tables:"Sometimes, though, it seems the optimizer is intent on ignoring the very performance-boosting indexes that we’ve just created. This usually happens because the access plans for the temp tables have been generated before the indexes ever existed."Does anyone know if this is still true in SQL Server 2005? |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-01-25 : 23:43:55
|
| You can test it. |
 |
|
|
pdav221
Starting Member
4 Posts |
Posted - 2008-01-28 : 13:58:21
|
| It turns out to be NOT true in SQL Server 2005. I created 2 #temp tables of 4 million rows with this definition:CREATE TABLE #MyTableA( fieldA [uniqueidentifier] ROWGUIDCOL NOT NULL ,fieldB [varchar](512) NULL ,fieldC [smallint] NOT NULL ,fieldD [uniqueidentifier] NOT NULL ,fieldE [uniqueidentifier] NOT NULL, ,fieldF datetime NOT NULL, ,fieldG [uniqueidentifier] NOT NULL, ,fieldH [datetime] NULL ,fieldI [uniqueidentifier] NULL, ,fieldJ [smallint] NULL);I populated them with the same data. I put an index on one of them like this: Create index mydexB on #MyTableB(fieldB, fieldF);I queried each with a query like this: SELECT * from #MyTableA where fieldB = 'SV2*0430*HC:87224*45*UN*1**0~';The one without the index took 58 seconds.The one with the index took 2 seconds. |
|
|
|
|
|
|
|
|
Index on temp table.