Sql class - 3
database vs file system
data types in sql
sql vs nosql
rdbms vs dbms
standard sql
----------
Data types mainly classified into three categories for every database.
- String Data types
- Numeric Data types
- Date and time Data types
SQL Server String Data Type
| . | |
| varchar(n) | It is a variable width character string data type. Its size can be up to 8000 characters. |
| varchar(max) | It is a variable width character string data types. Its size can be up to 1,073,741,824 characters. |
| text | It is a variable width character string data type. Its size can be up to 2GB of text data. |
| nvarchar | It is a variable width Unicode string data type. Its size can be up to 4000 characters. |
| ntext | It is a variable width Unicode string data type. Its size can be up to 2GB of text data. |
| binary(n) | It is a fixed width Binary string data type. Its size can be up to 8000 bytes. |
| varbinary | It is a variable width Binary string data type. Its size can be up to 8000 bytes. |
SQL Server Numeric Data Types
| bit | It is an integer that can be 0, 1 or null. |
| tinyint | It allows whole numbers from 0 to 255. |
| Smallint | It allows whole numbers between -32,768 and 32,767. |
| Int | It allows whole numbers between -2,147,483,648 and 2,147,483,647. |
| bigint | It allows whole numbers between -9,223,372,036,854,775,808 and 9,223,372,036,854,775,807. |
| float(n) | It is used to specify floating precision number data from -1.79E+308 to 1.79E+308. The n parameter indicates whether the field should hold the 4 or 8 bytes. Default value of n is 53. |
SQL Server Date and Time Data Type
| datetime | It is used to specify date and time combination. It supports range from January 1, 1753, to December 31, 9999 with an accuracy of 3.33 milliseconds. |
| datetime2 | It is used to specify date and time combination. It supports range from January 1, 0001 to December 31, 9999 with an accuracy of 100 nanoseconds |
| date | It is used to store date only. It supports range from January 1, 0001 to December 31, 9999 |
| time | It stores time only to an accuracy of 100 nanoseconds |
| timestamp | It stores a unique number when a new row gets created or modified. The time stamp value is based upon an internal clock and does not correspond to real time. Each table may contain only one-time stamp variable. |
| No. | DBMS | RDBMS |
|---|---|---|
| 1) | DBMS applications store data as file. | RDBMS applications store data in a tabular form. |
| 2) | In DBMS, data is generally stored in either a hierarchical form or a navigational form. | In RDBMS, the tables have an identifier called primary key and the data values are stored in the form of tables. |
| 3) | Normalization is not present in DBMS. | Normalization is present in RDBMS. |
| 4) | DBMS does not apply any security with regards to data manipulation. | RDBMS defines the integrity constraint for the purpose of ACID (Atomocity, Consistency, Isolation and Durability) property. |
| 5) | DBMS uses file system to store data, so there will be no relation between the tables. | in RDBMS, data values are stored in the form of tables, so a relationship between these data values will be stored in the form of a table as well. |
| 6) | DBMS has to provide some uniform methods to access the stored information. | RDBMS system supports a tabular structure of the data and a relationship between them to access the stored information. |
| 7) | DBMS does not support distributed database. | RDBMS supports distributed database. |
| 8) | DBMS is meant to be for small organization and deal with small data. it supports single user. | RDBMS is designed to handle large amount of data. it supports multiple users. |
| 9) | Examples of DBMS are file systems, xml etc. | Example of RDBMS are mysql, postgre, sql server, oracle etc. |
Comments
Post a Comment