2.1. CREATE TABLE
Syntax: - create table tbname (column1 datatype, column2 datatype…)
Example: - create table emp (emp_id int primary key identity (1,1) , f_name nvarchar(50) , salary int , date_of_joining date );
Press F5 for execute query or click on !Execute.
Or you can also create table using user interface. for that you will expand your database field in object Explorer. And press right click on the table field. Click on the new table . And fill all column detail as display below
Datatype table
String Datatypes
| Data type | Description | Max size | Storage |
|---|---|---|---|
| char(n) | Fixed width character string | 8,000 characters | Defined width |
| varchar(n) | Variable width character string | 8,000 characters | 2 bytes + number of chars |
| varchar(max) | Variable width character string | 1,073,741,824 characters | 2 bytes + number of chars |
| text | Variable width character string | 2GB of text data | 4 bytes + number of chars |
| nchar | Fixed width Unicode string | 4,000 characters | Defined width x 2 |
| nvarchar | Variable width Unicode string | 4,000 characters | |
| nvarchar(max) | Variable width Unicode string | 536,870,912 characters | |
| ntext | Variable width Unicode string | 2GB of text data | |
| binary(n) | Fixed width binary string | 8,000 bytes | |
| varbinary | Variable width binary string | 8,000 bytes | |
| varbinary(max) | Variable width binary string | 2GB | |
| image | Variable width binary string | 2GB |
Number datatypes
| Data type | Description | Storage |
|---|---|---|
| bit | Integer that can be 0, 1, or NULL | |
| tinyint | Allows whole numbers from 0 to 255 | 1 byte |
| smallint | Allows whole numbers between -32,768 and 32,767 | 2 bytes |
| int | Allows whole numbers between -2,147,483,648 and 2,147,483,647 | 4 bytes |
| bigint | Allows whole numbers between -9,223,372,036,854,775,808 and 9,223,372,036,854,775,807 | 8 bytes |
| decimal(p,s) | Fixed precision and scale numbers. Allows numbers from -10^38 +1 to 10^38 –1. The p parameter indicates the maximum total number of digits that can be stored (both to the left and to the right of the decimal point). p must be a value from 1 to 38. Default is 18. The s parameter indicates the maximum number of digits stored to the right of the decimal point. s must be a value from 0 to p. Default value is 0. |
5-17 bytes |
| numeric(p,s) | Fixed precision and scale numbers. Allows numbers from -10^38 +1 to 10^38 –1. The p parameter indicates the maximum total number of digits that can be stored (both to the left and to the right of the decimal point). p must be a value from 1 to 38. Default is 18. The s parameter indicates the maximum number of digits stored to the right of the decimal point. s must be a value from 0 to p. Default value is 0. |
5-17 bytes |
| smallmoney | Monetary data from -214,748.3648 to 214,748.3647 | 4 bytes |
| money | Monetary data from -922,337,203,685,477.5808 to 922,337,203,685,477.5807 | 8 bytes |
| float(n) | Floating precision number data from -1.79E + 308 to 1.79E + 308. The n parameter indicates whether the field should hold 4 or 8 bytes. float(24) holds a 4-byte field and float(53) holds an 8-byte field. Default value of n is 53. |
4 or 8 bytes |
| real | Floating precision number data from -3.40E + 38 to 3.40E + 38 | 4 bytes |
Date datatypes
| Data type | Description | Storage |
|---|---|---|
| datetime | From January 1, 1753 to December 31, 9999 with an accuracy of 3.33 milliseconds | 8 bytes |
| Getdate | Return current database system date and time.. | 6-8 bytes |
| smalldatetime | From January 1, 1900 to June 6, 2079 with an accuracy of 1 minute | 4 bytes |
| date | Store a date only. From January 1, 0001 to December 31, 9999 | 3 bytes |
| Dateadd | Add time/date interval to a date . | 8 byte |
| Year | Give the year from the given date. | 3-5 bytes |
| datetimeoffset | The same as datetime2 with the addition of a time zone offset | 8-10 bytes |
| timestamp | Return the current date and time. |
Primary key
- The PRIMARY KEY constraint uniquely identifies each record in a table.
- Primary keys must contain UNIQUE values, and cannot contain NULL values.
- A table can have only one primary key, which may consist of single or multiple fields.
Example of primary key is show with above example.
If you will use primary key using user interface then right click on the column and select primary key option.
Identity
- The MS SQL Server uses the IDENTITY keyword to perform an auto-increment feature.
- In the example above, the starting value for IDENTITY is 1, and it will increment by 1 for each new record.
- Tip: To specify that the "ID" column should start at value 10 and increment by 5, change it to IDENTITY(10,5).
To insert a new record into the "emp" table, we will NOT have to specify a value for the "emp_id" column (a unique value will be added automatically).
Example:- INSERT INTO emp (f_name, salary) VALUES ('Ankit',20000);
If you will insert identity in user interface then click on column property which is in below the screen as shown in figure. In column property search Identity Specification and change Is Identity property to YES.
There will you show two another option.
I. Identity Increment :- it is a number which add in Identity seed value every time and generate new Identity Seed.
II. Identity Seed :- it is a number that describe starting index which increment by identity increment when add every new row.
Then save the table by pressing “ctrl+s” or save menu on menu bar and write table name and press OK.
Then refresh the table field in object explorer and your table is displayed in object explorer.
