Data storage concepts

When converting from Informix® to Microsoft™ SQL Server the aim is to try to preserve as much of the data storage information as possible in the process. The most important storage decisions made for Informix database objects (like initial sizes and physical placement) can be applied to the SQL Server database.

Storage concepts are quite similar in Informix and in Microsoft SQL Server, but the names are different.

These table compares Informix storage concepts to Microsoft SQL SERVER storage concepts:

Table 1. Physical units of storage
Informix	Microsoft SQL SERVER
The largest unit of physical disk space is a "chunk", which can be allocated either as a cooked file ( I/O is controlled by the OS) or as raw device (= UNIX™ partition, I/O is controlled by the database engine). A "dbspace" uses at least one "chunk" for storage. You must add "chunks" to "dbspaces" in order to increase the size of the logical unit of storage.	SQL Server uses "filegroups", based on Windows NT™ operating system files and therefore define the physical location of data.
A "page" is the smallest physical unit of disk storage that the engine uses to read from and writeto databases. A "chunk" contains a certain number of "pages". The size of a "page" must be equal to the operating system's block size.	As in Informix, SQL Server stores data in "pages" with a size fixed at 2Kb in V6.5 and 8Kb in V7 and later.
An "extent" consists of a collection of continuous "pages" that the engine uses to allocate both initial and subsequent storage space for database tables. When creating a table, you can specify the first extent size and the size of future extents with the EXTENT SIZE and NEXT EXTENT options. For a single table, "extents" can be located in different "chunks" of the same "dbspace".	An "extent" is a specific number of 8 contiguous pages, obtained in a single allocation. Extents are allocated in the filegroup used by the database.

Table 2. Logical units of storage
Informix	Microsoft SQL SERVER
A "table" is a logical unit of storage that contains rows of data values.	Same concept as Informix.
A "database" is a logical unit of storage that contains table and index data. Each database also contains a system catalog that tracks information about database elements like tables, indexes, stored procedures, integrity constraints and user privileges.	Same concept as Informix. When creating a "database", you must specify which "database devices" (V6.5) or "filegroup" (V7) has to be used for physical storage.
Database tables are created in a specific "dbspace", which defines a logical place to store data. If no dbspace is given when creating the table, Informix defaults to the current database dbspace.	Database tables are created in a database based on "database devices" (V6.5) or a "filegroup" (V7), which defines the physical storage.
The total disk space allocated for a table is the "tblspace", which includes "pages" allocated for data, indexes, blobs, tracking page usage within table extents.	No equivalent.

Table 3. Other concepts relating to storage
Informix	Microsoft SQL SERVER
When initializing an Informix engine, a "root dbspace" is created to store information about all databases, including storage information (chunks used, other dbspaces, etc.).	SQL Server uses the "master" database to hold system stored procedures, system messages, SQL Server logins, current activity information, configuration parameters of other databases.
The "physical log" is a set of continuous disk pages where the engine stores "before-images" of data that has been modified during processing. The "logical log" is a set of "logical-log files" used to record logical operations during on-line processing. All transaction information is stored in the logical log files if a database has been created with transaction log. Informix combines "physical log" and "logical log" information when doing fast recovery. Saved "logical logs" can be used to restore a database from tape.	Each database has its own "transaction log" that records all changes to the database. The "transaction log" is based on a "database device" (V6.5) or "filegroup" (V7) which is specified when creating the database. SQL Server checks the "transaction logs" for automatic recovery.