Select * from Ben
Ben Forta has been blogging about mssql lately, but we shall thank him since it's useful information.
For example, here's a comparison between temp tables and table variables. I always use table variables -- a lot -- and they're quite useful. I never ran into any of the restrictions Ben mentioned, but it's good to know that if I run into any of them, there is an alternative; for example, I never had to Select Into a table variable, but I do see where it can be extremely handy.
Another nice post mentions how to perform case sensitive searches w/o changing the collation for the entire database. As you may know, you may define different collations when creating a database which will indicate the character set and case sensitivity among other properties, but you can use a different collation in run time; I actually did not know that. Ben shows two examples:
FROM MyTable
WHERE Col3 COLLATE SQL_Latin1_General_CP1_CS_AS LIKE '%foo%'
--- or ---
CRETE VIEW MyTableCS AS
SELECT Col1, Col2, Col3 COLLATE SQL_Latin1_General_CP1_CS_AS as Col3
FROM MyTable
This reminds me that I should blog more about SQL ... you may expect some coming soon.
http://www.robgonda.com/blog/trackback.cfm?50698CDF-3048-7431-E4B6FE0F6247E5FC
I _think_ it will use the indexes from the underlying table, but only if your view complies with certain pre-requisites.
Here're a few restrictions on the View to be Indexed:
* Must be created the WITH SCHEMABINDING view option
* May only refer to base tables in the same database.
* If there is a GROUP BY clause, the view may not have a HAVING, CUBE, or ROLLUP.
* May not have an OUTER JOIN clause.
* May not have a UNION.
* May not have DISTINCT or TOP clauses
* May not have full-text predicates such as CONATINSTABLE
* May not have a ROWSET function such as OPENROWSET
* May not use derived tables or subqueries.
* Must be created with ANSI_NULLS ON and QUOTED_IDENTIFIER ON
These restrictions will apply even if you create the index manually against the view, for both SQL 2000 and 2005.
Here's some additional reading:
http://www.microsoft.com/technet/prodtechnol/sql/2...
http://www.databasejournal.com/features/mssql/arti...