Table:
A 'Table' is the only format used by the user to store and get back the data from any database. The table will contain the data in the form of rows and columns. While creating a table we must specify the following details:
a) Name of the table
b) Details of the columns such as columns name, datatype and size.
Note - 1: A table name will contains a maximum of 255 characters including spaces and it contain a maximum of 1024 columns.
Note - 2: A maximum size of data that can be entered in a single record is 8060 bytes.
Creating A table Using Navigation:
Step - 1: Open the management studio tool and connect to the server.
Step - 2: Under the required database right click on the table section.
Step - 3: Select new table option and provide the columns details
Step - 4: Click on the save table icon.
Step - 5: Specify name of the table and click OK button.
Creating Table Using Command:
To create a table using command below specified syntax will be followed:
Syntax: create table <table name>(<column 1><data type>(size)[NOT NULL| DEFAULT(value)|IDENTITY(seed,increment )],<column 2> <datatype>......<column N> <datatype>.
NOT NULL: A column with this option doesnot allow NULL value.The user must provide a value to this column while entering the data.
DEFAULT: The constant option specified in this option will be used as column data. If the user not entering a value at that time of insertion.
Example: create table products(product id int NOTNULL, prodname char(10),prodqty int DEFAULT 300).
In the above example, DEFAULT value 300 will be took if no value is given.
IDENTITY: This property will be used to generate a number automatically in a column. The auto generation can be controlled with "seed" and increment values.
Seed specifies the initial value of the column and increment specifies howmuch the previous value should be incremented for each insert. A table must contain only one identity column
Example: create table customers(custid int IDENTITY(10,1),custname char(10)).
From the above example the initial value(seed) will be 10 and every time 1(increment) will be increased.
Modifying Table Data:
Step - 1: Right click on the table name.
Step - 2: Select modify option(design option for 2008 and 2012)
Step - 3 In the window display add new columns modify or delete the existing column data.
Step - 4: Click on the save table icon to save the changes.
Manipulating Table Data:
Step - 1: Right click on the table name.
Step - 2: Select open table option(Edit top 200 rows for 2008 and 2012).
Step - 3 In the window displayed add new records,modify or delete existing record data.
Note: When we close the window changes will be automatically saved into database.
Note: The DML commands will be used such as select, insert, update and delete can be used to perform operations on the table data.
Entering Table Data:
The insert statement will be used to enter the data into the table. If we providing the values for all the columns of the table then it is optional to provide the columns list. Whereas, if we are entering the data for particular columns only then we must specify the columns list by representing the names of the columns for which we are supplying the data.
Syntax: Insert into <table name> [Columns list>] values <Columns data>
Example:
Case -1: Insert into products (prodid,prodname,prodqty) values(1.'p1',200).
Case -2: Insert into products values(2,'p2,'150)
(No need to specify the columns list)
Case -3: Insert into products values(3,'p3,'400)
(It will get error, because we havn't specified columns list)
Case -4: It is possible to enter multiple records at a time into table as follows:
Example: insert into products
select 4,'p4', 200 union all
select 6,'p5', 350 union all
select 5,'p6', 600
Modifying Table Data:
The 'update' command will be used to modify the table data of an existing table.With this command we can able to modify the single column or single record as follows:
Syntax:
update <table name> set <col 1> = <value 1>, <col 2> = <value 2>,......[where <condition>].
Example:
Case -1: update products set prodqty = 100 where prodid =3
(Modifies the property of prodqty value for only the records with period 3)
Case -2: update products set prodqty = 100
(Modifies the prodqty value to 50 for all records)
Case -3: update products set prodname='p5',prodqty = 100 where prodid=5
(update products set prodqty =200 where prodid=1 or prodid=4
A 'Table' is the only format used by the user to store and get back the data from any database. The table will contain the data in the form of rows and columns. While creating a table we must specify the following details:
a) Name of the table
b) Details of the columns such as columns name, datatype and size.
Note - 1: A table name will contains a maximum of 255 characters including spaces and it contain a maximum of 1024 columns.
Note - 2: A maximum size of data that can be entered in a single record is 8060 bytes.
Creating A table Using Navigation:
Step - 1: Open the management studio tool and connect to the server.
Step - 2: Under the required database right click on the table section.
Step - 3: Select new table option and provide the columns details
Step - 4: Click on the save table icon.
Step - 5: Specify name of the table and click OK button.
Creating Table Using Command:
To create a table using command below specified syntax will be followed:
Syntax: create table <table name>(<column 1><data type>(size)[NOT NULL| DEFAULT(value)|IDENTITY(seed,increment )],<column 2> <datatype>......<column N> <datatype>.
NOT NULL: A column with this option doesnot allow NULL value.The user must provide a value to this column while entering the data.
DEFAULT: The constant option specified in this option will be used as column data. If the user not entering a value at that time of insertion.
Example: create table products(product id int NOTNULL, prodname char(10),prodqty int DEFAULT 300).
In the above example, DEFAULT value 300 will be took if no value is given.
IDENTITY: This property will be used to generate a number automatically in a column. The auto generation can be controlled with "seed" and increment values.
Seed specifies the initial value of the column and increment specifies howmuch the previous value should be incremented for each insert. A table must contain only one identity column
Example: create table customers(custid int IDENTITY(10,1),custname char(10)).
From the above example the initial value(seed) will be 10 and every time 1(increment) will be increased.
Modifying Table Data:
Step - 1: Right click on the table name.
Step - 2: Select modify option(design option for 2008 and 2012)
Step - 3 In the window display add new columns modify or delete the existing column data.
Step - 4: Click on the save table icon to save the changes.
Manipulating Table Data:
Step - 1: Right click on the table name.
Step - 2: Select open table option(Edit top 200 rows for 2008 and 2012).
Step - 3 In the window displayed add new records,modify or delete existing record data.
Note: When we close the window changes will be automatically saved into database.
Note: The DML commands will be used such as select, insert, update and delete can be used to perform operations on the table data.
Entering Table Data:
The insert statement will be used to enter the data into the table. If we providing the values for all the columns of the table then it is optional to provide the columns list. Whereas, if we are entering the data for particular columns only then we must specify the columns list by representing the names of the columns for which we are supplying the data.
Syntax: Insert into <table name> [Columns list>] values <Columns data>
Example:
Case -1: Insert into products (prodid,prodname,prodqty) values(1.'p1',200).
Case -2: Insert into products values(2,'p2,'150)
(No need to specify the columns list)
Case -3: Insert into products values(3,'p3,'400)
(It will get error, because we havn't specified columns list)
Case -4: It is possible to enter multiple records at a time into table as follows:
Example: insert into products
select 4,'p4', 200 union all
select 6,'p5', 350 union all
select 5,'p6', 600
Modifying Table Data:
The 'update' command will be used to modify the table data of an existing table.With this command we can able to modify the single column or single record as follows:
Syntax:
update <table name> set <col 1> = <value 1>, <col 2> = <value 2>,......[where <condition>].
Example:
Case -1: update products set prodqty = 100 where prodid =3
(Modifies the property of prodqty value for only the records with period 3)
Case -2: update products set prodqty = 100
(Modifies the prodqty value to 50 for all records)
Case -3: update products set prodname='p5',prodqty = 100 where prodid=5
(update products set prodqty =200 where prodid=1 or prodid=4
Case -4:Update products set prodqty =
100 where prodid =3 or prodid =4.
(Modifies the prodqty
value for the records with the period 3 or 4)
Case -5:Update products set prodqty =
100 where prodid IN(1,2,3).
(Modifies the property of
prodqty value for the records with the prodid value as 1/2/3).
Deleting the table data:
Either
by using delete or truncate, we can able to remove the data from the
table. Both these commands will remove only the table data not the structure.
Syntax: (Truncate)
Truncate table <table name>
Example : truncate table <products>
Syntax: (Delete)
Delete from <table name> where
<condition>
Example: delete from products where prodid
= 2
Differences between truncate and delete
commands:
Truncate:
- It is used to remove all the records of the table only.
- It removes entire table data at once. So, it executes faster than delete command.
- After deleting all the records when we insert a new record in the identity column, the auto generation will be restarted from the beginning.
- It maintains the removed “ datapage” details in the transaction log file.
Delete:
- It remove either all the records or particular records of table.
- Even though we are deleting all the records, the deletion will be done row by row.
- In this case, the auto generation will be continued normally.
- It maintains the removed record details in the log file.
Datapage:
It is the format of storing the data
physically inside SQL sever. As a user we will store and get back the data in
the form of table data inside the SQL server will be stored in the form of
datapages. Each datapage will occupy 8 kb of memory. If the table data is not
fit in a single page then multiple pages will be occupied by the table. A
combination of 8 pages is called as “1 extent”.
Note: In case of ‘ORACLE’ database, after
removing all the records with the truncate command we cannot get back the deleted
records by using the ‘ROLLBACK’ statement. But, after deleting the data with
‘DELETE’ command by using ‘ROLLBACK’ we can get back the deleted records.
Whereas, in SQL server either after truncate or delete commands we can get back
deleted records by using the ‘ROLLBACK’.
Example by using ‘DELETE’ command:
Begin transaction
Select * from products
Delete from products
Rollback
Example by using ‘TRUNCATE’ command:
Begin transaction
Select * from products
Truncate table products
Select *
from products
Rollback
Select * from products
