January 2, 2014 at 9:23 am
From what I've read, temp tables do have statistics on them. Would SQL Server create its own on them if you don't have an index on the table? Like the _WA_Sys ones.They're tough to find and DBCC ShowStatistics requires a statistic name.
This could be a red herring as I don't see a lot of folks asking about this.
Ken
January 2, 2014 at 9:46 am
Grant Wrote a wonderful article on statistics early this year, explaining stats on Temp Tables/Variables.
From his article:
Are statistics created on temporary tables?
Yes. The major difference between a table variable and a temporary table is that a temporary table has statistics. The rules for the creation and maintenance of these statistics are exactly the same as for a regular table within SQL Server. So if you reference a column in a temporary table in a filtering command in T-SQL such as WHERE or JOIN, then a set of statistics will get created. Unfortunately, the creation of the statistics causes a statement recompile. This is a potential disadvantage of temporary tables: For small statements this is a cheap operation. For larger queries this can be very expensive. That’s a reason why you have to be careful about how you work with your temporary tables.
You can find his full article(where I got this info) here for more details on Statistics: https://www.simple-talk.com/sql/performance/sql-server-statistics-questions-we-were-too-shy-to-ask/
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply