Data storage form in SQL Server 2000 (ii)

server| data
SQL Server is a relational database management system that was originally developed by Microsoft, Sybase and ashton-tate three companies and launched its first OS/2 version in 1988. Microsoft and Sybase split up on SQL Server development after Windows NT was launched, and Microsoft migrated SQL Server to Windows NT systems to focus on developing Windows NT to promote SQL Server Version, Sybase is more focused on the application of SQL Server to UNIX operating systems.



SQL Server 2000 is the version of SQL Server database management that Microsoft Corporation launched in 2000. This version inherits the benefits of the SQL Server 7.0 version, while adding many more advanced features, with ease of use, scalability, and high integration with related software. Can span multiple platforms from laptops running Microsoft Windows 98 to large multiprocessor servers running Microsoft Windows 2000.



The following is a description of the physical and logical implementation of the data store for SQL Server 2000.



One, physical storage form:



Physically, a database consists of one or more files on one or more disks. This physical implementation is visible only to the database manager and is transparent to the user.

Each database is physically divided into data and log files, which are stored on one or more disks. These files are not shared with other files.

1) Data file:

SQL Server 2000 assigns space in a data file to tables and indexes, each with 64kb of space, called the "extents". There are two types of extents: the consolidated extents and the mixed extents.

Each of the extents is made up of pages. A page is the basic unit of data storage in SQL Server 2000, with a size of 8kb per page. Typically, data is stored as rows on each data page. A row of data up to 8060 bytes. If there is a clustered index on the data row, the data is organized sequentially based on the keywords of the cluster index (such as postal code, etc.), otherwise there is no specific order.

2 Transaction log file:

The transaction log file resides in one or more physical files that are different from the data file, and contains a series of log records rather than a page allocated by the extents.



Second, the logical storage form:



Logically, a database consists of a number of components that are visible to the user, such as tables, views, stored procedures, and so on.

The object name and function are shown in the following table:

Database objects

function

Form

Consists of columns and rows of data that are similar in format to worksheets. The row represents a unique record, and the column represents a field in the record. Type definitions specify the types of data that can be stored in a column.

View

You can limit the rows and columns that are visible to a table, or combine multiple tabular data to display as a table. A view can also centralize columns.

Index

is a table-or-view-related structure that speeds up data rows from a table or view. The table index can be clustered or non-clustered. Cluster indices are physically stored based on the order of index keywords.

Key words

is a column or group in a column that uniquely identifies a row (the primary key), defines a relationship between two tables (an external keyword), or is used to build an index

User-defined data types

is a custom data type that is based on a predefined data type. It can be used to create a table structure that is more meaningful to programmers, to help ensure that columns of similar classes that hold data have the same data type.

Stored Procedures

is a Transact-SQL statement that is compiled into an execution plan. This process is used for performance optimization and control access.

Constraints

A rule that defines the values allowed in a column is a standard mechanism for enforcing data integrity.



Default

Defines the value that is used when you insert a row without defining a value for the column.

Trigger

A special class of stored procedures that can be automatically executed when an update, insert, or DELETE statement is issued on a table or view.

User-defined Functions

A subroutine consisting of one or more Transact-SQL statements that encapsulates code for easy reuse. A function can have up to 1024 input parameters. User-defined functions can replace views and stored procedures.
Reference SQLSERVER2000 Certified Books