4.2 SQL Server Database physical structure

4.2 SQL Server Database physical structure
SQL Server stores all of the data in a file. These files are partitioned into sub-structures that SQL Server manages to maintain the integrity, structure, and logical organization of the data contained therein. Although this book only provides information about SQL
Server 2008 is an entry-level guide to database management, but it is important for a new database administrator to understand the big topics like the physical architecture of the database. Understanding how SQL Server stores and maintains data helps to better understand how data changes affect performance and more effectively diagnose database problems.
4.2.1 Physical Storage data type
Before you learn the physical storage of data, it is important to understand the types of data stored by SQL Server. SQL Server 2008 Books Online divides the data types into the following 7 categories:
? Exact numbers
? Approximate numbers
? Date and time
? String

? Unicode string
? Binary string
? Other data types

Although this grouping makes sense when you look at data types from a usability perspective, they are related to what is discussed in this section
Is how the data is stored. SQL Server data types are basically divided into 3 storage types: Fixed-length data types, variable-length data types, and large object data types. in some cases, large object data types can also be used as variable-length types,
will be explained later. The data types described in this section can only be assigned the table column data type for the physical storage of the associated data.
This does not include the cursor and table data types that will be described later in this chapter

1. Fixed length data type
The size of the fixed-length data type is constant. The amount of memory or disk space used to store this data does not change
Of. The fixed-length data types are listed below:
? Bit--bit is an integer data type that supports a value of 0 or 1. Contrary to its name, the bit data type actually occupies one byte of space for 8 bits or less of data. The Tinyint--tinyint data type uses 1 bytes of storage space to store unsigned integer values between 0?255.
? smallint-----The smallint data type uses 2 bytes of storage space to store a signed integer,
Its value range is-32 768?32 767.
? The Int--int data type uses 4 bytes of storage space to store a signed integer with a value of
2 147 483 648? 2 147 483 647.
? The Bigint--bigint data type uses 8 bytes of storage space to store a signed integer that takes
Values range from 9 223 372 036 854 775 808-9 223 372 036 854 775 807.
? The decimal and numeric functions are the same------the decimal and numeric data types. Should generally be used
The decimal data type because it is more descriptive about the stored data. Based on usage, you can set the decimal data type to use a different fixed storage space.

When you use the decimal data type, you can also specify the precision (P) and scale (s) of the data to be stored. The representation is decimal (p,s). The precision and scale numbers are specified by a positive integer value between 0?38. However, the decimal place value must be less than or equal to the precision value, and
Can be specified only if the precision value is specified. The storage space depends on the precision value, as shown in table 4-1.

SmallMoney-----smallmoney Data type store between 214 748.3648?214 748.3647
's currency value. The smallmoney data type is accurate to one out of 10,000 of the stored currency unit and occupies 4 characters
Section space.

? Money------Money data type store between-922 337 203 685 477.5808?922 337 203 685
The currency value between 477.5807. It is accurate to one out of 10,000 of the stored currency unit, taking up 8 bytes of space.
? The Real--real data type is a floating-point number, so its value is approximate. The value that real supports is between
-3.40E+38? -negative numbers between 1.18E-38, 0 and between 1.18e-38?3.40e+38
Positive. It occupies a space of 4 bytes.
? The Float--float data type is a floating-point number, so its value is also approximate. The range of values supported by float and the required storage space depend on the precision of float. The precision is expressed as float (n), where n is the number of bits used to store the mantissa in scientific notation. The precision value allows between 1?53. 1?24 precision value requires 4 words
section of the storage space, the 25?53 precision value requires 8 bytes of storage space. For the default precision 53来
said that the value range supported by float is a negative number of 1.79E+308-----2.23E-308, 0 and 2.23E-308? A positive number between 1.79E+308.
? smalldatetime------smalldatetime data type for storage January 1, 1900 to June 2079
The date between 6th. It can be accurate to minutes and takes up 4 bytes of space. SQL Server internally stores the smalldatetime data as a pair of 2-byte integers. The first 2-byte integer is used to store the date since January 1, 1900, and the other 2-byte integer is the number of minutes after the zero is stored.
? DATETIME-----datetime data type is used to store January 1, 1753 to December 9999 3 1st
Date and time between the two. It can be accurate to 3.33 milliseconds and consumes 8 bytes of storage space. SQL Server internally stores the datetime data as a pair of 4-byte integers. The first 4-byte integer is used to store days since January 1, 1753, and the second 4-byte integer is used to store the number of milliseconds after zero (rounded to
3.33) O
? datetime2------The DateTime2 data type is an extension of the datetime data type that supports a wider range of day
Period and higher accuracy. It can be used to store between 31 from January 1, 01 to December 9999
Date and time, accurate to 100 nanoseconds. Similar to the decimal and numeric data types, it uses an optional precision declaration. The default precision for fractional seconds is 7 bits, or 100 nanoseconds. Accuracy equal to or small
At 3 o'clock, the storage space is 6 bytes: The precision is 4 and 5 o'clock is 7 bytes; The precision is 6 and 7 o'clock is 8
A byte.
? DateTimeOffset------DateTimeOffset data type for storage from January 1, 01 to 9999
Date and time between December 31 and a negative 14 time zone offset from UTC (Coordinated Universal Time)
Hour to positive 14 hours. As with the DateTime2 data type, it is accurate to 100 nanoseconds, using an optional precision declaration.
? The Date--date data type is used to store days between January 1, 01 and December 9999 31
The period value, which is accurate to the day, occupies 3 bytes of storage space. SQL Server internally stores the date data as a 3-byte integer that is used to store the number of days since January 1, 01.
? The Time--time data type is used to store the time between 00:00:00.0000000 to 23:59:59.9999999
Value. It is accurate to 100 nanoseconds. Similar to the decimal and numeric data types, it uses an optional precision declaration. The default precision for fractional seconds is 7 bits, or 100 nanoseconds. Accuracy less than 3 o'clock, storage size
is 3 bytes, the precision is 3 and 4 o'clock is 4 bytes, the precision is 5, 6, and 7 o'clock is 5 bytes.
? char------char data type is used to store fixed-length non-Unicode data between 1?8000 characters, expressions
is Charoi), where n is the number of characters to store. Each character requires 1 bytes of storage space.

? NCHAR------NCHAR data type to store 1? Fixed-length Unicode data between 4000 characters,

Expression is
C
Har (n), where n is the number of characters to store. Each character requires 2 bytes of storage space. If you must support multiple languages, the Unicode type is appropriate. binary------binary data type used to store 1? 8000 bytes of quantitative binary data, the expression is binary (n), where n is the number of binary bytes to store.
? rowversion or timestamp------rowversion and timestamp data types are synonyms, occupy 8 words
The storage space for the section. In any case, specify rowversion instead of timestamp as much as possible, because rowversion more accurately reflects the nature of the data type. The timestamp data type is completely unrelated to time. It is actually a 8-byte binary string that defines the version value of the table row. When
When rowversion or timestamp is specified as the data type of a table column, each insert and update performed on that table causes SQL server to generate a new value and put it in the appropriate field.
? uniqueidentifier-----The uniqueidentifier data type is stored as a 16-byte binary string, represented by 32 16-character characters. SQL Server can use the NEWID () function to generate uniqueidentifier, or to insert an existing uniqueidentifier and store it in a uniqueidentifier column.

2. Variable-length data types and large object data types
When the amount of space the data requires is unpredictable (for example, a column that holds a person's last name), you need to use a variable length
The data type. The varchar, nvarchar, and varbinary data types all belong to the variable-length data type.
However, when you specify the (MAX) option for the length of a string or a binary string, you can put these mutable data
Type is considered a large object data type. The main difference is how the data is stored. Large object data is stored by default in the number of
The variable-length data is stored in the data row, according to the individual physical structure of the outer row.
These data types are described below:
? VARCHAR------varchar data type used to store 1? Variable-length non-Unicode data between 8000 characters, the expression is Varcharoi), where n is the maximum number of characters stored. Each character requires 1 bytes of storage space. Varchai? The actual storage space used is the n value plus 2 bytes. The varchar data type also supports an
The selected (MAX) length designation. When using varchar (max), the maximum number of characters supported is 2 147 483 647, which requires up to 2GB of storage space. After you specify the (MAX) option, SQL Server puts the varchar data
stored in a data row, unless the data shelled exceeds 8000 bytes, or it is more than 8060 bytes
The maximum row size. In these cases, SQL Server moves the varchar data to a single large object storage space (see the "Data Pages" section later in this chapter).
? nvarchar------The nvarchar data type is the same as the varchar data type, except that it is used to store Unicode data. Each Unicode character requires 2 bytes of storage space, so that the maximum number of characters it supports
The number of characters is 1 073 741 824.
? VARBINARY------The varbinary data type is similar to the varchar data type, except that it is used to store the binary
Data instead of character data. In addition, the (MAX) option is stored and used as described above.
? Text-----The text data type is a large object data type, similar to the varchar (MAX) data type,
Because it can also be used to store up to 2GB of character data. The main difference is that the text data is stored by default outside the data row, and the text data type cannot be passed as a parameter to a SQL Server function, stored procedure, or trigger.

? The Ntext--ntext data type is the same as the text data type, except that it is used to store Unicode data. 2GB Unicode character data only supports 1 073 741 824 characters.? Image------The image data type is a large object data type, and the varbinary (MAX) data type
Very similar. It can also be used to store up to 2GB of binary data, but is always stored outside the data row
A separate large object data page.
? The Xml--xml data type is a large object data type that is used to store XML in local format.
Up to 2GB of XML data can be stored in each row of data.
? SQL Variant-----You can use the sql_variant data type in an object when you do not know the actual data type of the value. The sql_variant data type can be used to store any value that is less than 8000 bytes. Data types that are incompatible with the sql_variant type have text, ntext. Image, timestamp, cursor, varchar (max), and nvarchar (max).

3. CLR data type
SQL Server 2008 contains 3 different CLR-based data types. First is HierarchyID, which is used for tube

Roads and lakes, and so on. the spatial data type of SQL Server 2008 follows the simple Features for SQL 1.1, version 0 standard of open geo-space
Federation (Ogc,open Geospatial Consortium).? hier Archyid------The HierarchyID data type is used to create a hierarchy of tables with hierarchies or data that references
at another location. The required storage space depends on the number of records and the number of hierarchies in the row. To store the hierarchy number for a
organization with 100 000 employees (divided into 7 levels), 5 bytes are required. The more rows
and the more hierarchies you divide, the more storage space you need. The data type is limited to 892
bytes.
? geometry--This type represents the data in the Euclidean (planar) coordinate system.
? Geography-----Geography (geospatial) data type stores the Nan Sphere (sphere) data, such as GPS taboo
and longitude coordinates.

4. In-line data
By using the large value types out of row table option, the database administrator can specify that all varchar (max), nvarchar (max), and varbinary (max) data are treated as large object data and stored outside of the row
On a separate large object data page. You can set this option to ON or O FF, as follows:
sp_tableoption * tablename ', ' large value types out of Row1, *0n1 sp_tableoption 1tablename1, ' large value types out of RO W ', ' OFF '
Similarly, if the database administrator wants to save text or ntext data in rows until they exceed a specified size, you can specify the table option "text in row". This option allows the database administrator to specify the range of data that is saved in the row. The supported data range is 24?7000 bytes. With the on option value, you can set the default value of 256
Bytes. To turn off this option, use off:

sp_ tableoption * t a b l e n ^ m e ', 1 text in row '. * N u m b e r o f b y t e s 1
sp_ tableoption * ta b le n a m E *, ' text in row*,? On,

sp_ tableoption ' Ta b le n a m e 1, * text in row*,? off*

4.2.2 FILESTREAM Data
One of the enhancements to SQL Server 2008 is the storage of unstructured data, such as text documents, images, and videos, outside the database, but also links to the rows in which the columns are defined. by varbinary (MAX) binary large object (BLOB)
The data is stored as a file on the file system, rather than being stored in a separate large object data page in the database data file.
The FileStream integrates the database engine with the NT file system. Transact-SQL statements can be inserted, updated, consulted
And back up FileStream data.
in order to use FileStream, the database requires a filegroup that is designated as a FileStream storage area.
The following example shows how to add a FILESTREAM filegroup to a AdventureWorks2008 database??
Use Master GO
ALTER DATABASE AdventureWorks2008 ADD FILEGROUP MyFilestreamGroup2 CONTAINS FILESTREAM
GO
ALTER DATABASE AdventureWorks2008 ADD FILE (NAME = N ' Filestreamdata ')
R
FILENAME = N, D:\SQLData\FileStreamData,) to FILEGROUP Myfilestreamgroup
GO
After you have added a new filegroup to the database, you can add or modify tables to make the binary large object data of the table
Objects that are managed by the database engine are stored in the file system. The following example shows how to create a FileStream using the
Stored tables:
Use AdventureWorks2008
GO
CREATE TABLE mylargedata (documentidentifier uniqueidentifier ROWGUIDCOL not NULL UNIQUE, Documentfile VARBINARY (MAX) FIL eSTREAM NULL)
GO
Remember that a table that is enabled for FILESTREAM storage must have a unique rowguid column that is not NULL. Want to
Existing columns Add FILESTREAM columns, you must make sure that the table has a rowgu1d column, or you must add one.

4.2.3 Other data types
As mentioned earlier, there are two data types in SQL Server 2008 that are part of a table or index definition and are not used to physically store data on disk. These two data types are used to manipulate data in programming objects.

? Table A table data type is used to store a set of data rows in memory. It is used primarily with table-valued functions, but it can also be useful for any programming object to return an organized result set-the result set has
Most properties of the actual table. You can use a set of columns, a specified primary key, a check constraint, and a default
Constraint to declare and instantiate a table variable.
? CURSOR------Transact-SQL can perform well with dataset operations, but sometimes must be done one line at a time
Data. The cursor data type can meet such requirements. A cursor holds a set of complete rows of data from a query, and the cursor can be manipulated to return one row at a time. To learn more about cursors and their uses,
For a complete discussion, you can refer to the Getting Started with T-SQL programming, written by Paul Turley and Dan Wood, which covers
SQL Server & 2005) "Book.

4.2 SQL Server Database physical structure