Database Design:Part3

SQL Server Database Storage

·         SQL Server maps a database over a set of operating-system files

·         Database files 

SQL Server DataTypes

Objects that contain data have an associated data type that defines the kind of data; for example, character, integer, or binary, the object can contain. The following objects have data types:

·         Columns in tables and views.

·         Parameters in stored procedures.

·         Variables.

·         Transact-SQL functions that return one or more data values of a specific data type.

·         Stored procedures that have a return code, which always has an integer data type.

Assigning a data type to an object defines four attributes of the object:

·         The kind of data contained by the object.

·         The length or size of the stored value.

·         The precision of the number (numeric data types only).

·         The scale of the number (numeric data types only).

For more information about data type precision, scale and length, see Precision, Scale, and Length (Transact-SQL).

Transact-SQL has these system data types. 

bigint	binary	bit	char	CLR
cursor	date	datetime	datetime2	datetimeoffset
decimal	float	hierarchyid	image	int
money	nchar	ntext	numeric	nvarchar
real	rowversion	smalldatetime	smallint	smallmoney
sql_variant	table	text	time	timestamp
int, bigint, smallint, and tinyint (Transact-SQL)	varbinary	varchar	uniqueidentifier	xml (Transact-SQL)

All data stored in Microsoft SQL Server must be compatible with one of these base data types. The cursor data type is the only system data type that cannot be assigned to a table column. It can be used only with variables and stored procedure parameters.

Several base data types have synonyms (for example, rowversion is a synonym for timestamp, and national character varying is a synonym for nvarchar). For more information about the behavior of synonyms, see Data Type Synonyms (Transact-SQL).

Two kinds of user-defined data types can also be created:

·         Alias data types are created from base data types. They provide a mechanism for applying a name to a data type that is more descriptive of the types of values to be held in the object. This can make it easier for a programmer or database administrator to understand the intended use of any object defined with the data type. For example:

·         -- Create a birthday datetype that allows nulls.

CREATE TYPE birthday

FROM datetime NULL

GO

-- Create a table using the new data type.

CREATE TABLE employee (emp_id char(5), emp_first_name char(30), emp_last_name char(40), emp_birthday birthday)

For more information, see Working with Alias Data Types.

·         CLR user-defined data types are based on data types created in managed code and uploaded in a SQL Server 

 

SQL View:

Index: