The Logical Database Architecture

The window into the logical abstraction, the external level, of SQL Server 2005’s OLTP database world uncovers an assortment of components. These objects and their properties are what you or your users work with, directly and indirectly, for the storage, manipulation, and retrieval requirements of your various applications.

Introduction to SQL Server Tables: The Conceptual Level

The unit or object of data storage in a SQL Server database is the table, which is the same thing as a relation in relational systems parlance. Each table contains data that relates to entities known, again, as objects. For example, a table in the accounts receivable database might contain debtors and their attributes, such as the names of the debtors, their account balances, terms, and credit periods.

Tables are similar to spreadsheets; they contain columns and rows (also known as tuples). The comparison to a spreadsheet, however, is a dangerous one because it can lead you down the wrong road to understanding how SQL Server works. Each column represents one attribute of the object, such as name or customer number. The rows represent the instances or quantity of the objects in the table.

Each row can be thought of as a record if it is not related to rows in any other table. For example, if you have ten rows in the debtors table, you actually have ten instances of debtors, or ten “records” of type debtor. (The instances can also be identical if there is no unique rule, primary key, or similar constraint in effect over the table…but more about that shortly.) The similarity to the spreadsheet’s page or worksheet ends here, however, because the individual “cells” of the table are not accessed in the same way as cells of a spreadsheet.

The term record is correct database terminology, but you should take care not to confuse records and rows, because they are different things. The concept of a record extends beyond a single row in a table. In fact, a record is a combination of all the related rows of all tables in the entire database, which are linked by keys (discussed a little later in this chapter).

If you only have one table in the database, then of course each row can represent a complete and unique record. But if you break out the data into several tables, then your record spans to the other tables as well. I will refer to rows when our operations affect or act upon the individual rows in a table, such as the sorting of rows, a physical operation. But I will refer to records when referring to the actual data, such as when updating the value in a column. As you will also learn, deleting a row and deleting a record are two different things. Understanding the difference between a record and a row is a precondition to grasping the concept of referential integrity, which is covered in Chapter 12.

Creating Tables

There are two means of creating tables in SQL Server databases: interactively (through GUI tools such as SQL Server Management Studio) or directly with ANSI-compliant T-SQL syntax. The T-SQL code is a data definition language (DDL), and no matter which method you use, DDL is what gets sent to the server to create or alter the table. The following code is a simple clause that creates the “inventory” table:

 CREATE TABLE Inventory (item char (120))

The code creates the table inventory and inserts one column called item of data type char, which is 120 characters in length. Creating the table interactively is illustrated in Figure 2–5.

Figure 2–5: Creating a table using Management Studio

Naming Convention

Tables are named in code using dot notation representing a hierarchy comprising the database, the database schema that owns the table, and the table name. This is represented as follows: databasename.schemaname.tablename. The owner of the database, which is defaulted to the dbo schema if it is not specified, or a user with ownership rights and permission to create an object, is the only entity allowed to create or edit the objects within a database or a table.

Note 

Owners, users, groups, and roles are discussed in depth in Chapter 5. You should not overly concern yourself with ownership at this point. Schemas are discussed in Chapters 6 and 7.

Naming columns requires nothing more than common sense and conformance to the SQL Server convention, which specifies that the column name must be no more than 128 characters long. It can consist of letters; digits; or the symbols #, @, $, and _ (underscore).

Tip 

Keep column names simple and if possible use PascalCase for column names (such as ColumnName or MyColumn). PascalCase makes your database model easier to understand and document.

You also need to be careful not to use reserved words in your column names. A good example is the word “Group,” which is a SQL keyword. You could use the term “Category” instead if the aim, for example, is to classify a collection of items. (See SQL Server Books Online, which lists SQL Server keywords.)

Data Types and Their Domains

As mentioned earlier, the objects in the database have properties, as do their child objects, and one of the properties of the column in the table is the data type. The data type defines the type of data that can be stored in the records of the column. In the preceding example, we specified that the item column supports the data type char.

The domain of a data type is an ANSI SQL term for specifying a set of allowable values in a column. In other words, it is all the allowable attributes that describe the data type, including its name and other properties, such as color and class. Domain is often thought to be synonymous with data type, but that is incorrect at the worst, confusing at the least. In a table identifying the records of used cars on a lot, one column of type varchar holds information pertaining to the color of each car. Color is not the data type; it is the domain (although you can create a custom data type called “car_color” with no problem). The domain can be used to specify a constraint on the column. For example, the inventory person is not allowed to use the term “off-white” but should rather use “cream.”

Table in the appendix provides a list of the base data types supports by SQL Server tables.

Null

Data types can also hold the value NULL, which might seem confusing to you. While null is understood to be “nil” or “zero” or “no value” in some programming environments, SQL Server interprets the NULL value in a record to mean “unknown” or “missing.” A blank space and zero are legal values in database records, but this is not the same thing as null, so you need to be very careful about passing these as parameters.

Using NULL requires special discussion, and it will be covered in Chapters 10 and 12. Also, in Chapters 15 and 16, I discuss NULL along with instructions on how to create tables and data types that either take or refuse NULL values. For now, when you create a table interactively, keep this option checked. In other words, allow NULL values in your records until you fully understand the implications of NULL usage and have properly modeled your database. You can always alter the table at a later date, but allowing a NULL is a convenient way to “play” with records that have missing values.

There are many circumstances where it seems impossible to accept anything other than NULL. On the well-known online auction site eBay, for example, the registration asks for several items that could be unknown or missing, such as gender. In many sections they use the item “unknown” as a possible value.

System Tables

You will notice when you create a new user database that a collection of system tables is also created by SQL Server and is associated with the user database. These tables are used by SQL Server to store configuration and various system data, such as indexes, relating to the user databases and applications (do not confuse these with the master database tables, but they can be considered as extensions to the catalog). You will have little need to work with the system tables in your database; however, SQL Server does not preclude you from referencing them in your applications, especially through stored procedures, or using the data in them for some special reason. System tables are further discussed in Chapter 6 and 7.

I mentioned earlier that you might also notice tables that are prefixed with the pound or number sign, such as #temptable. These are SQL Server temporary tables, which are stored in the tempdb database. They are used for storing temporary information. The server usually drops them when a user disconnects or they are no longer needed.

There are two types of temporary tables, local and global. The local temporary tables are application or user specific, and they are thus only visible to the connecting user. Global temporary tables, prefixed with two number signs, ##temptable, are visible to all connections and are usually referenced by SQL Server and system-wide tasks.

The Table Data Type

SQL Server 2005 supports a special data type (a variable) that represents a table. Such a data type is similar to a temporary table that can be used to hold a result set for later application. Indexes and constraints (discussed shortly) can be applied to this table data type, but they have to be declared in the CREATE TABLE statement. The table data type is also discussed in Chapter 6, as is the common table expression or CTE (new to SQL Server 2005).

Views

Views are essentially virtual tables created with SQL code that can perform relational algebra on the data in a table or collection of tables.

Note 

The term virtual is an ANSI SQL-92 term, but it is not a description I like (and I say so again later in this book). “Virtual” implies something that appears to be real but is not, such as virtual reality. “Views,” on the other hand, are actual result sets, derived from base tables, and created using the SELECT statement, as demonstrated in Chapter 16.

Views are very similar to Microsoft Access queries in that they are created using the query language and return data in tabular format. What makes a view a view, and not a table, is that the view’s tabular structure is not stored in any database, nor is it persistent. Instead, a SELECT statement that retrieves the data for the view is stored in the database. In other words, views are for viewing only and in certain situations can update their source tables. Views are useful for a number of tasks:

• They can be used to hide rows in a table. In other words, a view might return only the collection of rows that pertain to a particular user or process.

• They can be used to hide columns in a table. A user, for example, can be restricted from viewing confidential information in the customer table, such as credit card information or credit status.

• They can be used in join constructions to collect columns from a number of tables into one object representing a single table. This practice is useful in the construction of a data source for reports, although there are pros and cons regarding the use of views to report.

• They can be used to perform operations on the data in tables, such as presenting the sum of all values in a specific column.

Creating a view is straightforward, although the SELECT statement of the entire view object can be as complex as a SELECT statement needs to be. Performance of views can also be enhanced using indexes on the views. The following code is an example of a simple view, and a simple SELECT statement. SELECT statements inside views can run to hundreds of lines of code.

 CREATE VIEW inventory_view AS SELECT item, quantity, value FROM inventory_base

Views can also reference other views. In other words, you might return view “A” from the first CREATE statement and then create view “B” from the “A” view. The following code is such an example:

 CREATE VIEW user_view AS SELECT item, quantity FROM inventory_view

Views are also a key feature used in SQL Server 2005 high-availability architecture. Using UNION views, data is partitioned across a number of SQL Server databases or instances of SQL Server running on separate servers. Partitioned views are the key component of SQL Server’s scalability (scale-out) architecture. In terms of availability they provide a similar benefit, to the clustering of servers, without the possibility of shared hardware, such as the hard disk array, becoming the point of failure that crashes everything. The difference between clustering SQL Server and partitioned views is that all servers participating in the view are active. A cluster of SQL Servers might follow an active-passive architecture where one server in a two-node cluster remains inactive. You will learn about data partitioning and partitioned views in Chapter 9.

Data in views can also be manipulated using INSERT, UPDATE, and DELETE statements. Updates are “cascaded” down to the base tables, including all member tables that are referenced by the view.

Later, in Chapter 6, I look into the finer points of handling views, especially with respect to performance, joins, unions, and so on. I will also discuss indexed views, which, as I told you in the preceding chapter, force a view result-set to be stored in the database until the base table has to be altered.

Indexes and Keys

SQL Server indexes can be associated with tables and views to speed up the retrieval of rows, and to speed up updates and deletes. Indexes are very important in SQL Server, especially for complex databases. That’s because, like all relational engines, SQL Server must first find a row in a table before the data can be presented or manipulated. By referencing an index, you are thus helping SQL Server find the row quicker. Indexes arrange the rows in the internal structures of the table, according to a peculiar logic or sorting algorithm.

A key, on the other hand, does not directly influence sorting, searching, and retrieval. A key ensures that a row in a table is unique. The debate on uniqueness has been raging since mankind emerged from a hole in the ground in the middle of nowhere. You cannot be lax about uniqueness in your database. Ask yourself how confused you would feel if you woke one day to find an identical person living in the same world as you. The same reasoning applies to the rows in a table. It might not seem a problem at first to add a duplicate row to a table, but what do you do when you need to delete one of those cloned rows? Exactly which row do you delete? How sure are you that you are deleting the right one?

You can create a key in a table on one or more columns. When you create a key on more than one column, you are allowing a value in a column to be unique by combining it with a value in another column. In other words, the key protects the uniqueness of the row if the combination of values in the unique one combines to give a unique key An example: red+spanner and red+hammer gives you two unique tools that have identical values for the color column. If the unique key were specified on color alone, you would have more than a slight glitch.

The recognized name for the unique key on your primary table (you could call it a root table) is known as the primary key. You can also call it the candidate key, especially when it involves more than one column.

If you break out or modularize your tables into several related tables, you will need to connect them (so as to preserve the record) by creating a key in the foreign tables. This “table-hopping” key is called the foreign key, which is essentially the tie that binds the rows in the foreign table to the rows in the primary table, thus making them one record. The primary key/foreign key relationship is illustrated in Figure 2–6.

Figure 2–6: The primary key in Clients links the to the row in Phones. The rows thus combine to form a complete record. If you delete a row without cascading the delete to foreign tables, the rows in the foreign tables will no longer have a relationship with any other table as part of a record

You can only have one primary key in a table, signified with the little key icon in the table designer. When you create the key, SQL Server automatically creates a clustered index in association with it. In Chapter 16, I have included examples of how searching on the primary key index helps speed up location of records.

Tables without indexes make SQL Server work harder. My son’s room is like a database table without an index (and certainly without a key). Everything is lying around in no particular stack or order. He can never find what he is looking for. If the room were sorted, he would be able to locate the things he needs (although he swears he can find things faster in the chaos). I often call his room the heap. When you create a table or a view and do not impose any index on it, the rows are not stored according to any logic or system. Such a structure is also called a heap in SQL Server lingo.

Indexed keys can also be created when you define an index from the combinations of one or more columns in a table or view. Indexes and keys are persistent; that is, they are also objects and properties that are stored in a database. SQL Server thus maintains the sort order of rows-even when they are not being accessed. When you create an index, you can also specify that the data be stored in ascending or descending order.

Note 

SQL Server 2005 keys have a 900-byte limit that caps the maximum size on an index key. SQL Server will permit the creation of an index key larger than the limit, but it will process the key with a warning or failure notice. Programmatic handling of indexes and key attributes is discussed at length in Chapter 12.

Indexes can be created interactively using Management Studio, as illustrated in Figure 2–7, or in T-SQL code using the CREATE INDEX statement (see Chapter 12 for creating indexes interactively).

Tip 

The creation and management of indexes is usually the responsibility of the DBA, so it will pay big dividends to become fully conversant in this subject.

Figure 2–7: Creating an index using Management Studio

An index can also be created, as a substatement, inside the CREATE TABLE statement. (This is further discussed in Chapter 6, which discusses Data Definition Language.) The CREATE TABLE statement is illustrated as follows:

 CREATE TABLE inventory (item_id int PRIMARY KEY CLUSTERED)

You can create additional indexes for a table, targeted at certain columns. Multiple indexes require more resources, however, so you need to be conservative and limit new indexes to columns you know are frequently searched on. In other words, no two rows can have identical values for the index key. You can also specify a nonunique index, and that means you can have duplicate values for a particular indexed column in your table.

Another important attribute of the indexing architecture is that SQL Server indexes can be either clustered or nonclustered. The application of the two types of index is described as follows:

Clustered indexes store data in a table according to key values. The rows in the table are sorted in the order specified by the clustered index key. You can only create one clustered index per table, which is logical because you can only sort the rows according to one algorithm. When you impose an index on data, the data is sorted, which is the opposite of a heap.

Nonclustered indexes do not impose any sort logic on a collection of rows in a table, and the rows are essentially heaped. But the nonclustered index does maintain a pointer to rows in the table, which is in essence the key. The pointer starts out with the lowest row in the table. This pointer is also known as a row locator.

So, when you create an index you can specify that it be created according to certain requirements using the following parameters:

• PRIMARY KEY   Creates a primary key on the column or columns in the primary table.

• UNIQUE   Creates a unique index, not necessarily a key.

• CLUSTERED   Specifies the index should be clustered.

• NONCLUSTERED   Specifies the index should not be clustered.

You can also control how tightly packed a SQL Server index should be when you create one. The property that controls this is called the fill factor, and the higher the fill factor, the smaller the size of the index, which conserves the amount of disk reads. The fill factor default is usually 0 percent, as is illustrated in Figure 2–8. The more active a table (for instance, the more aggressive delete and insert activity it has), the lower should be the fill factor you set. This leaves space in the index for future keys.

Figure 2–8: Specifying the fill factor of an index

Often complex queries that return a large number of rows in a view would benefit from indexing. Examples are views that are returned from complex aggregation routines and complex joins. Performance is greatly improved by defining indexes on such views. All indexes on views are dropped as soon as the views are dropped. For more information on placing indexes on views, see Chapter 6.

Large and complex databases and applications will require you to spend more effort designing adequate indexed solutions.

Data Integrity Mechanisms

There are two classes of data integrity definitions that can ensure the “plausibility” of the data entered into SQL Server 2005 tables. (For a blow-by-blow account of integrity in SQL Server, see Chapter 12.)

First, you have data integrity definitions, which act upon the data values that are placed in the records stored within each column. These integrity definitions ensure that the data is of the right type, that they follow the value declarations encompassed in the domains of each data type, and that the data conforms to your own business or database rules and constraints. Data integrity definitions are thus local to an individual table and its records.

Second, you have referential integrity definitions that ensure that the relationships between tables are sound. Referential integrity ensures that data in a table refers only to data that actually exists in other tables. In other words, if you are going to refer to rows in other tables, you need to be certain that those rows exist. Referential integrity, as well as data integrity, is discussed at length in Chapter 12.

SQL Server provides four formal types of integrity machinery as follows:

• Constraints

• Rules

• Defaults

• Triggers

Constraints   Constraints are the mechanism of choice for ensuring data integrity. Constraints are essentially a system of rules that are applied to values that are inserted into a column. A constraint can be defined over the table or the column, or both. The column constraint applies only to the column, while the table constraint is declared independently of the column constraint and can encompass more than one column. This subject is further described in Chapter 12.

SQL Server 2005 supports five classes of constraints:

• NOT NULL

• CHECK

• UNIQUE

• PRIMARY KEY

• FOREIGN KEY

NOT NULL is the simplest constraint to implement; it prevents a column from accepting a NULL value. (As discussed earlier, NULL means that the record is deemed to be unknown or missing.)

A CHECK constraint enforces domain integrity by limiting what values can be inserted into the record. The CHECK evaluates a Boolean value; it determines if the value you are trying to insert meets the constraint (TRUE) or does not meet the constraint (FALSE). If a value “checks” out to be TRUE, it is rejected and cannot be applied to the row. Each column in a table can be protected with multiple CHECK constraints.

You can create CHECK constraints with the services of Management Studio (see Chapter 12). You can also specify a CHECK constraint in your T-SQL DDL code when you create a table. The following code is an example of a CHECK constraint (quantity) created in the T-SQL CREATE TABLE statement:

 CREATE TABLE inventory (item char (120), quantity int PRIMARY KEY, (quantity > 100))

A UNIQUE constraint enforces the uniqueness of the values in a column, or it can be used to check that no two rows are identical. As mentioned earlier, you can use a unique index to enforce uniqueness. However, the UNIQUE constraint is preferred; first; all you want to do is enforce uniqueness, because it requires a lot fewer resources than a primary key index. Indexes tax the system more than constraints do because constraints are evaluated when the column value is referenced, while indexes need to be updated every time you update the table.

The PRIMARY KEY constraint identifies the column with the values that uniquely identify the row in a table.

The FOREIGN KEY constraint applies to the relationship between tables and thus supports referential integrity. The integrity rule here ensures that the candidate key in the primary table refers to the foreign key in another table. This constraint prevents the problem of a row with a foreign key that has no corresponding candidate keys in the remote table.

The foreign key rule also ensures that you cannot delete a row that is linked to a foreign key (which would break the record). When you try to delete the row, the ON DELETE or ON UPDATE action can trigger one of two predefined events:

• NO ACTION   This will specify that the delete operation fails with an error.

• CASCADE   This will specify that the rows with the foreign keys that are related to the candidate keys are also deleted. This action is known as a cascading delete.

The ON UPDATE clause is used extensively to validate input; it is further discussed in Chapter 12.

Rules   Rules are similar to constraints and are the legacy integrity features of SQL Server. They have been kept in the product for compatibility with legacy code. CHECK constraints are more powerful than rules, and you should use CHECK constraints in all new code. (When you have designed a complex database with many tables, you’ll thank heaven, or the SQL Server development team, for features such as cascading deletes.)

Rules are created externally to the CREATE TABLE statement and are thus objects that are bound to the column. This means that you can only have one rule bound to a column. Multiple CHECK constraints, on the other hand, can be applied to a column.

Defaults   Defaults are a form of “failsafe” value applied to a column so that no NULLs make it into the row. In other words, if your code creates a row and your user does not apply a certain value to a column, the default value is applied instead. Defaults can be generated out of the following constants:

• A constant value; for example, the value “0” for the column quantity

• A built-in or user-defined function that generates the value

• A mathematical expression that generates a value

Defaults are typically created using the DEFAULT keyword in the CREATE TABLE statement. They can also be applied using the system stored procedure sp_bindefault.

Triggers   Triggers are a valuable integrity tool that can be used alone or in conjunction with the integrity features we have just discussed. Triggers fire automatically when defined over UPDATE, INSERT, and DELETE statements issued in certain areas of your code, against target tables and views. A trigger is similar in purpose and function to an event that fires in software components, such as the OnClick events found in all software controls. Trigger creation is demonstrated in Chapter 13.

A trigger is defined with the FOR UPDATE, FOR INSERT, and FOR DELETE clauses to point the trigger at the correct actions. Triggers can be used in many different scenarios. They can be used in a similar fashion to IF…THEN…ELSE logic in most development languages. For example, you can define a trigger when the quantity value of a certain item in the inventory table reaches a certain level. You can then execute a number of routines that request replenishment or alert users or set applicable wheels in motion.

Triggers are essentially stored procedures that also contain T-SQL statements, and they can also be written in a .NET language for execution on the common language runtime (CLR). They can also be used to return a result set, or a dialog box when SELECT or COUNT statements are used within the trigger code. For example, let’s say your order taker is about to make a sale that debits 50 units of X items to the inventory table. If there are fewer than 50 units of X, the trigger could query inventory and show the order taker just how many units he or she can debit from the table.

The FOR clause is used to dictate when the trigger fires. Triggers can be fired after your T-SQL code has executed or instead of the triggering action. Both AFTER and INSTEAD OF triggers are discussed in more detail in Chapter 13. I have demonstrated how to use these triggers to implement a server-side audit trail for any application.

Stored Procedures

A stored procedure is to SQL Server what a function call in a dynamic linked library (DLL) is to an application (although the user-defined functions are more like the functions of standard programming languages). It allows you to implement a collection of routines that can be accessed by any number of applications. Stored procedures are some of the most powerful features in all client/server database systems and can be used to enforce application logic and administer business or enterprise rules to all applications. Stored procedures are compiled T-SQL statements or CLR-executed methods that return data in one of five ways:

• They can provide parameters that can return data, such as an integer or a character, a cursor variable, and an error message.

• They can provide return codes (integers).

• They can provide result sets that are created from SELECT statements embedded in the stored procedure.

• They can reference external cursors (result sets) and stored procedures.

• On the CLR they can pretty much do anything.

(T-SQL stored procedures are compiled into so-called execution plans, which will be covered in the next chapter.) You, the SQL Server developer, create stored procedures that can then be called from any application. You can also create stored procedures for applications written by other parties. Stored procedures do not necessarily have to return data to the user either. They are executed on the server and can thus comprise logic that performs functionality without the knowledge or intervention of the user or referencing application. SQL Server is itself maintained by a collection of system stored procedures that cannot be accessed by users. Using stored procedures exclusively in certain applications means that absolutely all processing is performed on the server.

Your applications can execute stored procedures by merely issuing EXECUTE, EXEC, or CALL (ODBC) statements in the code. This is a similar practice to linking in or loading a DLL and issuing a call to a function. The difference is that a call to a function in a DLL results in the function being processed in the processing space of the calling application. All applications that have access to the DLL can call the function or procedure in the DLL.

Stored procedures are also very useful for applications that use SQL Server as a data storehouse. You would use them, for example, with the administrative software of a computer telephony application or an Internet application to perform automated system administration, without requiring manual intervention or human input in any way.

Your applications can also build stored procedures on the fly to meet certain requirements. These stored procedures can then be saved or stored in the tempdb. If they are stored in tempdb, they are later dropped when the application terminates or the user disconnects. If, however, your application needs frequent use of the stored procedure, it would be better to create the stored procedure once and then maintain it in the system for later use. We will discuss stored procedures at length in Chapter 14.

User-Defined Data Types

User-defined data types are made possible by extending the existing SQL Server 2005 base types with custom names and formats. To create a user-defined data type, you would call the stored procedure sp_addtype. The example in the following code creates a user-defined data type, time of death, extended from the base data type datetime.

 EXEC sp_addtype time of death, datetime, 'NULL'

In this emergency room database, the user-defined time of death data type records the time of death of patients. It can take values of NULL in the event the time of death is not recorded or the subject was dead on arrival. User-defined data types are discussed further in Chapter 11 when we deal with the CLR.

User-Defined Functions

SQL Server supports user-defined functions. These functions are built using T-SQL code, specifically the CREATE FUNCTION statement, and any .NET languages for functions created in managed code. The functions are used to return a value or a result code. They can return values as single scalars, such as integers, characters, and decimals. User-defined functions are useful for many application scenarios and can be used extensively in analytical and statistical applications.

SQL Server 2005 UDFs can also return the table data type. For example, a function can declare the internal table variable, insert rows into the table, and then return the table as the return value. You can also use a class of functions known as inline functions that can perform relational algebra on your tables, using the SELECT statement and returning the table variable.

User-defined functions can contain complex code comprising multiple SELECT statements. They are thus an ideal mechanism for returning tables where the complexity of the code precludes the generation of a view. Likewise, the user-defined function can also replace a stored procedure.

Properties

User-defined properties are known as extended properties and can be applied to all database objects. The architecture is thus OO in design (properties can publish information) and allows the database designer or developer to extend the collection of properties on database objects.

The properties are essentially placeholders for information that can be used by applications. The values of an extended property are of the sql_variant data type and can hold up to 7,500 bytes of data. The extended properties are created, managed, and deleted by the stored procedures sp_addextendedproperty, sp_updateextendedproperty, and sp_ dropextendedproperty The system function, fn_listextendedproperty, lets you read the value of an extended property.

Full-Text Indexes

SQL Server 2005 can create full-text indexes that are used for sophisticated searching of character string data. The full-text index stores information about certain words and their locations within their columns. This will allow you to create full-text queries and return either character string data or complete documents. You can search for instances of individual words or combinations of words.

The indexes are stored in catalogs, and each database you create can contain one or more full-text catalogs. The actual catalog and the indexes are not, however, stored in the database. They are stored externally as files and managed by the Microsoft Search service (see Chapter 3). Also, a full-text index can only be defined on a base table and not on the virtual objects, such as views and temporary tables.