Table is a database object that is used to store data of a business entity. Technically, table represents a business entity. For example, Employee is a business entity which can be represented as Employee table in database.
Table is organized into rows and column. Each row represents an occurrence of entity and each column represents property of the entity. For example, salary, date of joining and bonus properties of an employee can be represented as columns of the employee table. In a single SQL Server database, we can create two billion tables and each table can hold at most 1024 columns.
Proper understanding of data type is required to define columns of table and design tables.
Importance of Data Types
Data types are very important concept in any programming language and SQL Server is no exception to it.
It is very important to know about different data types of SQL Server because in creating any table or other objects in database, the knowledge of different data types is required.
Each table has one or more columns; each column has one or more properties such as nullability, constraint, data types etc. Data type is required to define a column of table in database because the nature and size of the data that a column can hold is defined using data type.
The naming of a column of table in SQL Server is based on the rules for naming identifiers.
Rules for naming identifiers
The identifier in SQL Server can be at most 128 characters long.
The first character of the identifier must be alphabet including three special symbols which are #, @ and _.
The symbol @ is used to declare local variables.
The # symbol is used to declare local temporary objects of SQL Server database.
The ## symbol is used to declare global temporary objects of SQL Server database.
Space character is not allowed in naming an identifier but if identifier contains space character then such identifier can be written inside square brackets.
Different Categories of Data types
The SQL Server data types can be grouped into different categories for simplifying the learning. The following categories for data types are given below.
Numeric Datatypes
- int
- tinyint
- smallint
- int
- bigint
Character Datatypes
- char
- nchar
- varchar
- nvarchar
- varchar(MAX)
Date Datatypes
- date
- datetime
- datetime2
- datetimeoffset
- smalldatetime
- time
Binary Datatypes
- bit
- binary
- varbinary
Other Datatypes
- cursor
- table
- xml
String, binary, integer, approximate numeric, exact numeric, date and time money, synonym, Unicode data, computed column and special are the categories for SQL Server data types.
String Data types: You can define variable length string or fixed length string for a column of a table. For fixed and variable length strings, you use char and varchar data types respectively. The size of the string data types is given in the small bracket. For example, char (5) and varchar (9) implies that char data type is of 5 character length and varchar data type is of 9 characters.
Fixed Length Char Data type
If data entry is more than the length of char data type specified then data will be truncated. On the other hand, if data entry is less than the length of char data type specified then empty space will occupy the space and the size will remain fixed.
Integer Data types
Integer category of data types includes tinyint, smallint, int and bigint which are of 1, 2, 3 and 4 bytes respectively.
Approximate Numeric Data Types
Float and real data types belong to approximate numeric category; on the other hand, numeric data belong to exact numeric category.
Float and Real data types are approximate numeric data types. They are useful for scientific calculation and in general database programming they should not be used.
Exact Numeric Data Types
Numeric and decimal are exact numeric data types. The total digits of the numeric data type are called its precision; on the other hand the number of digits to the right side of the decimal point is called scale. Depending on the precision of the numeric data type, the storage size is required. Needless to say that higher the precision more than storage requirement is.
Date and Time Data Types
There are different data types for date and time category such as datetime a smalldatetime, datetime2, date and time. The date time data type stores both date and time together. The four bytes are used to store date and 4 bytes for time; together 8 bytes are required for datetime data type.
Binary Data types: SQL Server allows binary and varbinary data types to store binary data. Binary data string is preceded with 0x. The size of the binary data is given in the small bracket.
Special Data types: Bit is a special data type in SQL Server which is used for Boolean information; the value stored will be zero or one. The other special data types are text and image. Generally we should avoid text and image data types because they affect the performance of the database badly. The uniqueidentifier data type is used in SQL Server to store Globally Unique Identifier (GUID) to a column. GUID is a string of 16 characters constant. NewID function is used to generate GUID in SQL Server.
Table Data types: The table data type is used to store the result set of a query for later use; it is very much similar to temporary table.
Money Data types: Money and small money data types in Play store numeric values it has a scale of four size which means that after the decimal place it can store four digits.
Time Stamp: You can use time stamp data type to track the order in which items are added and modified in the table.
Creating User Defined Data Types in SQL Server
SQL Server provides build-in stored procedure to create user define data types. Sp_AddType stored procedure is used to create user define data types.
Nullability of Column of Table
Nullability is a property of a column in database table. Nullability of a column implies whether entry is required for that column or not. You can use NULL or NOT NULL keywords to define the nullability of a column.
NOT NULL word implies that data entry is required for the column. NULL word implies that data entry is optional (not required) for the column.
You can define variable length string or fixed length string for a column of a table. For fixed and variable length string you use char and varchar data types respectively. The size of the string is given in the small bracket.
SQL Server allows binary and varbinary data types to store binary data. Binary data the string is preceded with 0x. The size of the binary data is given in the small bracket.
Numeric and decimal are exact numeric data types. The total digit of the numeric data type is called its precision; on the other hand the number of digits to the right side of the decimal point is called scale.
Depending on the precision of the numeric data type the storage size is required. Needless to say that higher the precision more than storage requirement is.
Float and Real are approximate numeric data types. They are useful for scientific calculation and in general database programming they should not be used.
Bit is a special data type in SQL Server which is used for Boolean information; the value stored will be zero or one. The other special data type is text and image. Generally we should avoid text and image data types because they affect the performance of the database badly.
The uniqueidentifier data type is used in SQL Server to store globally unique identifier that is GUID to a column.
GUID is a string of 16 characters constant.
NewID function is used to generate GUID in SQL Server.
The table data type is used to store the result set of a query for later use; it is very much similar to temporary table.
The date time data type stores both date and time together. The four bytes are used to store date and 4 bytes for time; together it by required for datetime data types.
Money and smallmoney data types in Play store numeric values it has a scale of four size which means that after the decimal place it can store four digits.
You can use timestamp data type to track the order in which items are added and modified in the table.
You can create user define data types in SQL Server. SQL Server provide build in stored procedures to create user define data types. Sp_AddType stored procedure is used to create user define data types.
Data type conversion is important topic in any programming and T-SQL is no exception. You can learn about type conversion in T-SQL in the next post.
No comments:
Post a Comment