Chapter Summary

  • Article
  • Table joins enable you to return data from one or more related tables. Linked servers enable you to query data from heterogeneous data sources in the same way you would query SQL Server.
  • New operators include: the PIVOT operator, which enables you to generate an output table that can then be used to create cross-tab reports by performing aggregations of the data. The APPLY operator is used in the FROM clause and enables you to apply a table-valued function to each row in the outer table. The EXCEPT operator can be used to combine and limit result sets by only returning distinct values from the left side. The INTERSECT operator does the same thing but returns distinct values from the left and right sides.
  • Full-text searches enable you to perform partial searches of large columns more efficiently than traditional LIKE searches. Once a catalog and index are built, you can use the CONTAINS predicate to search columns for partial words or phrases. Other predicates that can be used are CONTAINSTABLE and FREETEXT.
  • The query optimizer for SQL Server 2005 will automatically search for the optimal execution plan. There might be specific instances where experienced developers or database administrators have the need to override this capability and use a specific execution plan and a query hint. Plan guides enable you to apply a query hint to a query without modifying the query itself.
  • A searchable argument (SARG) is used by the query optimizer when determining the correct execution plan and index to utilize. Therefore, it might be possible for you to improve the performance of an existing query by rewriting it to use a searchable argument.
  • You can query XML columns using an XQuery (an XML querying language) expression and one of the following methods: query, value, exist, nodes. The modify method is used to update the XML document in a column defined as such.
  • The FOR XML clause enables you to return relational data as XML. You can use one of the following modes to affect the shape of the XML: RAW, EXPLICIT, PATH, or AUTO.

< Back     

 

 

© Microsoft. All Rights Reserved.