Datatypes and Operators
Written by
What is a Datatype?
Datatypes are memory element that holds some particular data defined/entered by the User.
The need for different datatypes is to ensure the efficiency of memory usage with respect to the data to be stored.
For example, for storing character elements character (not a keyword) datatypes can be used and for integers, their respective datatypes are used.
There are 5 major categories of data types that can be used to define the type of input.
| Datatype | From | To |
| bigint | -9,223,372,036,854,775,808 | 9,223,372,036,854,775,807 |
| int | -2,147,483,648 | 2,147,483,647 |
| smallint | -32.768 | 32,767 |
| tinyint | 0 | 255 |
| bit | 0 | 1 |
| decimal | -10^38+1 | 10^38-1 |
| numeric | -10^38+1 | 10^38-1 |
| money | -922,337,203,685,477.5808 | 922,337,203,685,477.5807 |
| smallmoney | -214,748.3648 | 214,748.3647 |
| float | -1.79E + 308 | 1.79E + 308 |
| real | -3.40E + 38 | 3.40E + 38 |
| datetime | Jan 1, 1753 | Dec 31, 9999 |
| smalldatetime | Jan 1, 1900 | Jun 6, 2079 |
| date | Stores a data in the form July 5, 2000 | |
| time | Stores a time in the form 8:30 P.M. | |
| Datatype | Description |
| char | Maximum length of 8000 characters (Fixed length) |
| varchar | Maximum of 8000 characters (Variable length) |
| varchar(max) | Max length of 2E+31 characters (Variable length) |
| text | Variable length with a max length of 2,147,483,647 |
| nchar | Maximum length of 4000 characters (Fixed length) |
| nvarchar | Maximum of 4000 characters (Variable length) |
| nvarchar(max) | Max length of 2E+31 characters (Variable length) |
| ntext | Max length of 1,073,741,823 characters |
| binary | Maximum length of 8000 bytes (Fixed length) |
| varbinary | Maximum of 8000 bytes (Variable length) |
| varbinary(max) | Max length of 2E+31 bytes (Variable length) |
| image | Max length of 2,147,483,647 bytes (Variable length) |
At the same time, the console supports the mathematical/relational equations that help us to determine certain outcomes and workflow.
| Operator | Use | Example | Result |
| Arithmetic | |||
| + | Adds the two numbers | SELECT 17 + 5; | 22 |
| – | Subtracts the two numbers | SELECT 17 – 5; | 12 |
| * | Multiplies the two numbers | SELECT 17 * 5; | 85 |
| / | Divides the number two numbers and rounds off to greatest integer of the result | SELECT 17 / 5; | 3 |
| % | Gives the remainder after division | SELECT 17 % 5; | 2 |
| Bitwise | |||
| & | Bitwise AND | ||
| | | Bitwise OR | ||
| ^ | Bitwise XOR | ||
| Comparison | |||
| = | Equal to | SELECT * FROM Students WHERE Marks = 50; | Students with marks 50 |
| > | Greater than | SELECT * FROM Students WHERE Marks > 33; | Students with marks more than 33 |
| < | Less than | SELECT * FROM Students WHERE Marks <90; | Students with marks less than 90 |
| >= | Greater than or equal to | SELECT * FROM Students WHERE Marks >= 67; | Students with marks greater than or equal to 67 |
| <= | Less than or equal to | SELECT * FROM Students WHERE Marks <= 30; | Students with marks less than or equal to 30 |
| <> | Not equal to | SELECT * FROM Students WHERE Marks = 50; | Students with marks 50 |
| Compound | |||
| += | Add Equals | ||
| -= | Subtract Equals | ||
| *= | Multiply Equals | ||
| /= | Divide Equals | ||
| %= | Modulo Equals | ||
| &= | Bitwise AND Equals | ||
| ^-= | Bitwise Exclusive Equals | ||
| |*= | Bitwise OR Equals | ||
| Logical | |||
| ALL | TRUE if all subquery value meets the condition | ||
| AND | TRUE if all statements separated by AND is TRUE | ||
| ANY | TRUE if any of the subquery value meets the condition | ||
| BETWEEN | TRUE if the operand is within the range of comparisons | ||
| EXISTS | TRUE if the subquery returns one or more records | ||
| IN | TRUE if the operand is equal to one of a list of expressions | ||
| LIKE | TRUE if the operand matches a pattern | ||
| NOT | Displays a record if the condition is NOT TRUE | ||
| OR | TRUE if any of the conditions separated by OR is TRUE | ||
| SOME | TRUE if any of the subquery values meet the condition | ||
