Friday, October 31, 2008

Creating and Managing Database Objects using T-SQL Statements in SQL Server

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, where .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 SLECT statement from 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 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 metadata information about a view is to be viewed, it is created with the VIEW_METADATA option.

Modification 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 object_name(column_name)

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

CREATE [PRIMARY] CML 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 option 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 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 table. A combination of a PRIMARI 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 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 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 value in the OrderDate column bust be less then 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 in 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 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)
)