I recently presented session on Statistics and Best Practices in Virtual Tech Days on Nov 22, 2010. The sessions was very popular and I got many questions right after the sessions. The number question I had received was where everybody can get the further information. I am very much happy that my sessions created some curiosity for one of the most important feature of the SQL Server. Statistics are the heart of the SQL Server. Let us read about Statistics Used by the Query Optimizer in Microsoft SQL Server 2008.
Microsoft has published a white paper on the subject how statistics are useful to Query Optimizer. Here is the abstract of the same white paper from Microsoft.
Statistics Used by the Query Optimizer in Microsoft SQL Server 2008
Writer: Eric N. Hanson and Yavor Angelov
Microsoft SQL Server 2008 collects statistical information about indexes and column data stored in the database. These statistics are used by the SQL Server query optimizer to choose the most efficient plan for retrieving or updating data. This paper describes what data is collected, where it is stored, and which commands create, update, and delete statistics. By default, SQL Server 2008 also creates and updates statistics automatically, when such an operation is considered to be useful. This paper also outlines how these defaults can be changed on different levels (column, table, and database). In addition, it presents how certain query language features, such as Transact-SQL variables, interact with use of statistics by the optimizer, and it provides guidance for using these features when writing queries so you can obtain good query performance.
Link to white paper Statistics Used by the Query Optimizer in Microsoft SQL Server 2008
Reference: Pinal Dave (https://blog.sqlauthority.com)
2 Comments. Leave new
hi ,
i have two table in database having following description
Table1 has 3 columns(title,shorttext,fulltext)
and
table2 have 1 column (title)
now i want to match the table2 title in table1 with all the 3 columns(title,shorttext,fulltext) and want to see only the matched words
please help me its urgent.
thanks
Bharat
@Bharat:
SELECT
A.title,
A.ShortText,
A.FullText
FROM
Table1 as A
INNER JOIN
Table2 as B
ON A.title = B.title