What is a Schema?

In Microsoft SQL Server 2005, a schema is a collection of objects adhering to the ANSI SQL-92 standard.

The ANSI SQL-92 standard defines a schema as a collection of database objects that are owned by a single principle and form a single namespace.

All objects within a schema must be uniquely named and a schema must be uniquely named in the database catalog. SQL Server 2005 breaks the link between users and schemas, users do not own objects. Schemas own objects and principles own schemas.

A schema can be owned by either a primary or secondary principle, with the term “principle” meaning any SQL Server entity that can access securable objects.

Principle types that can own schemas:

• Primary
  • SQL Server Login
  • Database User
  • Windows Login
• Secondary
  • SQL Server Roles
  • Windows Groups
  • Default Schemas

Users can now have a default schema assigned using the DEFAULT_SCHEMA option of CREATE USER and ALTER USER commands. If no default schema is supplied for a user then DBO will be used as the default schema.

——————————————————————————–

SQL Server 2005 Schema Objects

• Tables
• Indexes
• Views
• Synonyms
• Stored Procedures
• Triggers
• Functions
• Assemblies
• Types
• Defaults

Download the SQL 2008 System View Map

The Microsoft SQL Server 2008 System Views Map shows the key system views included in SQL Server 2008, and the relationships between them.

I am getting ready to upgrade an existing SQL 2005 application and have found the following SQL 2008 features worth more exploration:

1. INSERT (Transact-SQL Row Constructors) – Allows the ability to add one or more new rows to a table or a view. Seems novel but not sure how useful in practice.
2. MERGE Statement. This new Transact-SQL statement performs INSERT, UPDATE, or DELETE operations on a target table based on the results of a join with a source table. Oracle has had this statement for years. I am glad Microsoft has finally added it to TSQL.
3. Full-Text Search: Full-text search is now a fully integrated feature of a database. Wow that took forever. I still remember the 8 hour Microsoft support call back in 2002, that included my rant session to support about the need for 1. Logging, 2. Database integration.
4. SP_DEPENDS: Identifies down to the columns of each table your stored procedure uses.

Things I am still waiting for:

1. Row Level Security
2. Something equivalent to the Oracle %ROWTYPE attribute in variable declarations as a datatype specifier.

SQL Server 2005 has the following data types available:

• Exact Numerics
  • BIGINT
  • INT
  • SMALLINT
  • TINYINT
  • BIT bit
  • DECIMAL
  • NUMERIC
  • MONEY
  • SMALLMONEY
• Approximate Numerics
  • FLOAT
  • REAL
• Date and Time
  • DATETIME
  • SMALLDATETIME
• Character
  • CHAR
  • VARCHAR
  • TEXT
• Unicode Character
  • NCHAR
  • NVARCHAR
  • NTEXT
• Binary
  • BINARY
  • VARBINARY
  • IMAGE
• Other
  • CURSOR
  • SQL_VARIANT
  • TABLE
  • TIMESTAMP
  • UNIQUEIDENTIFIER
  • XML

SQL Server is not yet Oracle but it does provide a key piece of the Microsoft developer platform. It must be judged for what it is, and that is a great database if designed within its limitations.