Data Type | Used to Store |
int | Integer data |
smallint | Integer data |
tinyint | Integer data |
bigint | Integer data |
decimal | Numeric data type with a fixed precision and scale. |
numeric | numeric data type with a fixed precision and scale. |
float | floating precision data |
money | monetary data |
datetime | data and time data |
char(n) | fixed length character data |
varchar(n) | variable length character data |
text | character string |
bit | integer data with 0 or 1 |
image | variable length binary data to store images |
real | floating precision number |
binary | fixed length binary data |
cursor | cursor reference |
sql_variant | different data types |
timestamp | unique number in the database that is updated every time in a row that contains timestamp is inserted or updated. |
table | temporary set of rows returned as a result set of a table-valued function. |
xml | store and return xml values |
Columns | Data Type | Checks |
EmployeeID | int | NOT NULL |
LeaveStartDate | date | NOT NULL |
LeaveEndDate | date | NOT NULL |
LeaveReason | varchar(100) | NOT NULL |
LeaveType | char(2) | NOT NULL |
Constraint | Description | Syntax |
Primary key | Columns or columns that uniquely identify all rows in the table. | CREATE TABLE table_name ( col_name [CONSTRAINT constraint_name PRIMARY KEY] (col_name(s)) ) |
Unique key | Enforces uniqueness on non primary key columns. | |
Foreign key | Is used to remove the inconsistency in two tables when the data depends on other tables. | |
Check | Enforce domain integrity by restricting the values to be inserted in the column. |
StudentID | FirstName | LastName | Marks |
101 | John | Ray | 78 |
102 | Steve | Jobs | 89 |
103 | Ben | Matt | 77 |
104 | Ron | Neil | 65 |
105 | Andy | Clifton | 65 |
106 | Park | Jin | 90 |
EmployeeID (PK) | FirstName | LastName | Title |
1001 | Ron | Brent | Developer |
1002 | Alex | Matt | Manager |
1003 | Ray | Maxi | Tester |
1004 | August | Berg | Quality |
EmployeeID (FK) | Department | Salary |
1001 | Application | 65000 |
1002 | Digital Marketing | 75000 |
1003 | Web | 45000 |
1004 | Software Tools | 68000 |
Function Name | Example |
left | |
len | |
lower | |
reverse | |
right | |
space | |
str | |
substring | |
upper |
Function Name | Parameters | Description |
dateadd | (date part, number, date) | Adds the number of date parts to the date. |
datediff | (date part, date1, date2) | Calculates the number of date parts between two dates. |
Datename | (date part, date) | Returns date part from the listed as a character value. |
datepart | (date part, date) | Returns date part from the listed as an integer. |
getdate | 0 | Returns current date and time |
day | (date) | Returns an integer, which represents the day. |
month | (date) | Returns an integer, which represents the month. |
year | (date) | Returns an integer, which represents the year. |
Function Name | Parameters | Description |
abs | (numeric_expression) | Returns an absolute value |
acts,asin,atan | (float_expression) | Returns an angle in radians |
cos, sin, cot,tan | (float_expression) | Returns the cosine, sine, cotangent, or tangent of the angle in radians. |
degrees | (numeric_expression) | Returns the smallest integer greater than or equal to specifies value. |
exp | (float_expression) | Returns the exponential value of the specified value. |
floor | (numeric_expression) | Returns the largest integer less than or equal to the specified value. |
log | (float_expression) | Returns the natural logarithm of the specified value. |
pi | 0 | Returns the constant value of 3.141592653589793 |
power | (numeric_expression,y) | Returns the value of numeric expression to the value of y |
radians | (numeric_expression) | Converts from degrees to radians. |
rand | ([seed]) | Returns a random float number between 0 and 1. |
round | (numeric_expression,length) | Returns a numeric expression rounded off to the length specified as an integer expression. |
sign | (numeric_expression) | Returns positive, negative or zero. |
sqrt | (float_expression) | Returns the square root of the specified value. |
Function Name | Description |
avg | returns the average of values in a numeric expression, either all or distinct. |
count | returns the number of values in an expression, either all or distinct. |
min | returns the lowest value in an expression. |
max | returns the highest value in an expression. |
sum | returns the total of values in an expression, either all or distinct. |