Creating and Managing Database Objects through T-SQL Statements

Database: A database comprises information in structured and organized form that can easily be accessed and manipulated. According to the needs of a Web site, the database can be large or small in size. A database can be stored on a single PC or on a collection of high-powered database servers depending upon the size. When a database is stored on a collection of servers, it is known as a distributed database. It is a collection of relevant data that is interconnected and organized systematically so that its contents can easily be accessed, managed, and updated electronically.

The syntax for creating a database is as follows:

CREATE DATABASE database_name

Two files whose extensions are .mdf and .ldf are responsible for creating a database. The .mdf file is a data file, whereas .ldf is log file.

Database Objects: A database consists of various types of objects that are used to manage a database. The database objects are used to store data. Various properties of these database objects are used to sort, index, and search data. The database objects are as follows:

• Table
• View
• Index
• Constraint

Table: A table is the basic unit of data storage in a SQL Server database. It is composed of columns and rows. A column is vertical space in a database table, which represents a particular domain of data. A row is a collection of data in columns corresponding to a single record. A table can be created in a database at any time, even while users are using the database. While creating a table, its size need not be specified. It is ultimately determined by the amount of space allocated to the database as a whole.

The syntax for creating a table is as follows:

CREATE TABLE table_name
(
       column1 datatype NULL | NOT NULL
       column2 datatype NULL | NOT NULL
       column3 datatype NULL | NOT NULL
       column4 datatype NULL | NOT NULL
       column5 datatype NULL | NOT NULL
)

Modifications can be made to an existing table by using the ALTER TABLE statement. In order to alter the column settings of a table, the following syntax is used:

ALTER TABLE table_name
{
       ALTER COLUMN column_name
       {
               datatype
               CONSTRAINT constraint_name
       }
}

In order to modify collation settings, the following syntax is used:

ALTER TABLE table_name
{
       COLLATE collation_name
}

The DELETE statement is used to delete records from a table or a view. The syntax for using the DELETE statement is as follows:

DELETE * FROM table_name

The above syntax will delete all the records from a table.

DELETE FROM table_name WHERE column_name=value

The above syntax will delete records based on the search criteria.

In order to delete a table, the DROP TABLE statement is used. The syntax for deleting a table is as follows:

DROP TABLE table_name

View: A view can be thought of as a virtual table. Data accessible through a view is not stored in the database as a distinct object. A View is created by defining a SELECT statement. The result set of the SELECT statement forms the virtual table. A user can use this virtual table by referencing the view name in SQL statements in the same way a table is referenced.

The syntax for creating a view is as follows:

CREATE VIEW view_name AS
SELECT columns
FROM table_name
[WITH CHECK OPTION
ENCRYPTION
SCHEMABINDING
VIEW_METADATA]

The WITH CHECK OPTION statement is used to force all data modifications to follow the criteria set within a SELECT statement given in the CREATE VIEW statement.

The WITH ENCRYPTION clause is used with a view or stored procedure definition. Using the WITH ENCRYPTION clause changes the view or stored procedure definition to an encrypted form. This prevents anyone from viewing the statement used to create the view or stored procedure.

The WITH SCHEMABINDING clause safeguards a view definition against any structural modification of the underlying table. If a view is created with the WITH SCHEMABINDING clause, the underlying tables cannot be deleted or altered in a way that affects the view definition.

The view specified with VIEW_METADATA can return the DBLIB, ODBC, and the OLEDB APIs. When the metadata information about a view is to be viewed, it is created with the VIEW_METADATA option.

Modifications can be made in an existing view by using the ALTER VIEW statement.

ALTER VIEW view_name AS
SELECT columns
FROM table_name
[WITH CHECK OPTION
ENCRYPTION
SCHEMABINDING
VIEW_METADATA]

In order to delete a view, the DROP VIEW statement is used. The syntax for the DROP VIEW statement is as follows:

DROP VIEW view_name

Index: An index is a data structure that improves the performance of queries issued against a table. An index can be created for one or more columns of a table. Once created, an index is automatically updated and used by the database. It is logically and physically independent of data. The table or other indexes remain unaffected when an index is created or deleted.

The syntax for creating an index is as follows:

CREATE [CLUSTERED] [NONCLUSTERED][UNIQUE] INDEX index_name ON object_name(column_name)

In order to create an XML index, the following syntax is used:

CREATE [PRIMARY] XML INDEX index_name ON object_name(xml_column_name)

Modifications can be made to an existing index by using the ALTER INDEX statement.

In order to specify rebuild options for an index or to enable an index, the following syntax is used:

ALTER INDEX index_name ON object_name
{
       REBUILD WITH rebuild options
}

In order to disable an index, the following syntax is used:

ALTER INDEX index_name
{
       DISABLE
}

In order to drop an index, the DROP INDEX statement is used.

DROP INDEX index_name

Constraints: Constraints are implemented on a table or a view to ensure that only valid data is inserted. Constraints prevent users from inserting invalid data or updating a column of a table or view with wrong data. These constraints are as follows:

• PRIMARY KEY constraint
• FOREIGN KEY constraint
• UNIQUE constraint
• CHECK constraint
• DEFAULT constraint
• NULL Values

PRIMARY KEY Constraint: A PRIMARY KEY constraint is implemented on a column or on a group of columns of a table. These columns identify a row of a table uniquely. A table can contain only one primary key. The column on which a primary key constraint has been implemented cannot contain NULL values.

FOREIGN KEY constraint: A FOREIGN KEY constraint is implemented on a column of a table to establish referential integrity between two tables. A combination of a PRIMARY KEY and a FOREIGN KEY is used in the JOIN clause to retrieve data from two or more tables.

UNIQUE constraint: UNIQUE constraint ensures that no two rows in a specified column or set of columns have duplicate values. Multiple UNIQUE constraints can be defined for a table. The UNIQUE constraint can be created by using the CREATE TABLE statement while creating a table. It can also be created by using the ALTER TABLE statement while modifying the table. The UNIQUE constraint allows NULL values unless the NOT NULL constraint is specified for the same column.

CHECK constraint: A CHECK constraint enforces domain integrity by limiting the values that are accepted by a column. Multiple CHECK constraints can be applied to a column. A CHECK constraint can be created by using a logical expression to validate the value to be accepted by a column. It is also possible to use multiple-columns in a CHECK constraint. For example, a CHECK constraint can be used to confirm that the value in the OrderDate column must be less than the value in the InvoiceDate column. However, a CHECK constraint cannot be created to enforce a rule that requires column value from another row in the table or column value from another table.

DEFAULT constraint: The DEFAULT constraint is a constraint in which a default value is given to the column if the value for that column is unknown. If no value is provided for that column, the default value is automatically inserted. If a default value is not provided, then NULL is inserted. If a column does not allow NULL value and a default value is also not assigned for that column, an error is sent by the database engine.

In order to implement a constraint on a column while creating a table, the following syntax is used:

CREATE TABLE table_name
(
       CONSTRAINT constraint_name(column_name)
)

In order to implement a constraint on a column of an existing table, the following syntax is used:

ALTER TABLE table_name
(
       CONSTRAINT constraint_name(column_name)
)

• Download practice question and study guide for mcad for exam.
• Click here to download mcp test study guide and practice question.
• Download free practice test for mcpd MCPD exam.
• Pass Microsoft mcpd-enterprise-applications-developer - MCPD Enterprise Applications Developer
• Become Microsoft MCSA 2000 to 2003 certified.
• Get certified in first attempt download mcsa-to-mcse-2003 - MCSA to MCSE-2003 simulation.
• Best exam simulation Microsoft mcse-2003 download free trial.
• Pass Microsoft mcts-microsoft-visual-studio-2008 - MCTS Microsoft Visual Studio 2008
• Become Microsoft MCTS SQL Server 2005 certified.
• Best exam simulation Microsoft 70-431 download free trial.
• Download practice question and study guide for 70-561-CSHARP for exam.

• Previous Entry: Replication
• Next Entry: What is the Processor: % Processor Time counter?