Rick Caminiti dotCOM

First of all, when I’m trying to find metada in Oracle, I am usually using Toad, and have no problem finding what I need because it’s such a great too. However, even in SQL-Plus, it’s still really easy to find what I need by querying the data dictionary:


select *
from all_columns
where owner = user
and table_name = 'tablename'

… Or even easier, if it’s in the schema I’m already in:

select *
from user_columns
where table_name = 'tablename'


It seems that it’s still pretty easy in SQL Server to do this, but off the bat, it’s not exactly as intuitive. Apparently you can go through sysobjects, but even Microsoft suggests against it, considering they reserve the right to change any and all system tables from version to version.

There are alternatives though; views in INFORMATION_SCHEMA:
For instance, to view tables:

select *
from INFORMATION_SCHEMA.TABLES

… And for columns:

select *
from INFORMATION_SCHEMA.Columns
where table_schema = 'schemaname'
and table_name = 'tablename'


Here’s some more great information about INFORMATION SCHEMA: Here

Like all other query optimizers, SQL uses statistics to create query plans that improve query performance. The optimizer generally generates the necessary statistics for a high quality query plan, however, in a few cases, you need to create additional statistics or modify the query design for “optimal” results.

Query Optimization Statistics are objects that contain statistical information about the distribution of values in one or more columns of a table or indexed view. The query optimizer uses these statistics to estimate the cardinality, or number of rows, in the query result. These cardinality estimates enable the query optimizer to create a high-quality query plan. For example, the query optimizer could use cardinality estimates to choose the index seek operator instead of the more resource-intensive index scan operator, and in doing so improve query performance.

You can create statistics objects with filters to improve query performance for queries that select from well-defined subsets of data. They use a filter predicate to select the subset of data that is included in the statistics. Well-designed filtered statistics can improve the query execution plan compared with full-table statistics.

Syntax

CREATE STATISTICS statistics_name
ON { table_or_indexed_view_name } ( column [ ,...n ] )
  [ WHERE <filter_predicate> ]
  [ WITH
  [ [ FULLSCAN
   | SAMPLE number { PERCENT | ROWS }
   | STATS_STREAM = stats_stream ] [ , ] ]
  [ NORECOMPUTE ]
] ;

<filter_predicate> ::=
  <conjunct> [AND ]

<conjunct> ::=
  <disjunct> | <comparison>

<disjunct> ::=
  column_name IN (constant ,…)

<comparison> ::=
  column_name <comparison_op> constant

<comparison_op> ::=
  IS | IS NOT | = | <> | != | > | >= | !> | < | <= | !<

SAMPLES

USE AdventureWorks;
GO
CREATE STATISTICS ContactMail1
  ON Person.Contact (ContactID, EmailAddress)
  WITH SAMPLE 5 PERCENT;

OR

IF EXISTS (SELECT name FROM sys.stats
  WHERE name = N'ContactPromotion1'
  AND object_id = OBJECT_ID(N'Person.Contact'))
DROP STATISTICS Person.Contact.ContactPromotion1
GO
CREATE STATISTICS ContactPromotion1
  ON Person.Contact (ContactID, EmailAddress, EmailPromotion)
WHERE EmailPromotion = 2
WITH SAMPLE 50 PERCENT;
GO


For more information, see http://msdn.microsoft.com/en-us/library/ms188038.aspx

For information about when to CREATE STATISTICS, see http://msdn.microsoft.com/en-us/library/ms188038.aspx