TRUNCATE & DELETE:
TRUNCATE : You can't use WHERE clause
DELETE : You can use WHERE clause
TRUNCATE is a DDL command and cannot be rolled back. All of the memory space is released back to the server.
DELETE is a DML command and can be rolled back.
Both commands accomplish identical tasks (removing all data from a table) but TRUNCATE is much faster.
The Main Difference Between DELETE & TRUNCATE Are :-
[1] DELETE - is a DML Command & TRUNCATE - is a DDL Command
[2] After DELETE - can rollback the Records & After TRUNATE - cannot rollback the records
[3] In DELETE Command you can give the conditions in WHERE Clause & In TRUNCATE you cannot give conditions
[4] After using DELETE Command The memory will be occupied till the user does not give ROLLBACK or COMMIT & After using TRUNCATE Command The memory realeased immediately
TRUNCATE SQL Command:
TRUNCATE is faster and uses fewer system and transaction log resources than DELETE.
TRUNCATE removes the data by deallocating the data pages used to store the table’s data, and only the page deallocations are recorded in the transaction log.
TRUNCATE removes all rows from a table, but the table structure and its columns, constraints, indexes and so on remain. The counter used by an identity for new rows is reset to the seed for the column.
You cannot use TRUNCATE TABLE on a table referenced by a FOREIGN KEY constraint.
Because TRUNCATE TABLE is not logged, it cannot activate a trigger.
TRUNCATE can not be Rolled back.
TRUNCATE is DDL Command.
TRUNCATE Resets identity of the table.
DELETE SQL Command:
DELETE removes rows one at a time and records an entry in the transaction log for each deleted row.
If you want to retain the identity counter, use DELETE instead. If you want to remove table definition and its data, use the DROP TABLE statement.
DELETE Can be used with or without a WHERE clause
DELETE Activates Triggers.
DELETE can be Rolled back.
DELETE is DML Command.
DELETE does not reset identity of the table.
DDL,DML,DCL & TCL:
DDL
Data Definition Language (DDL) statements are used to define the database structure or schema. Some examples:
* CREATE - to create objects in the database
* ALTER - alters the structure of the database
* DROP - delete objects from the database
* TRUNCATE - remove all records from a table, including all spaces allocated for the records are removed
* COMMENT - add comments to the data dictionary
* RENAME - rename an object
DML
Data Manipulation Language (DML) statements are used for managing data within schema objects. Some examples:
* SELECT - retrieve data from the a database
* INSERT - insert data into a table
* UPDATE - updates existing data within a table
* DELETE - deletes all records from a table, the space for the records remain
* MERGE - UPSERT operation (insert or update)
* CALL - call a PL/SQL or Java subprogram
* EXPLAIN PLAN - explain access path to data
* LOCK TABLE - control concurrency
DCL
Data Control Language (DCL) statements. Some examples:
* GRANT - gives user's access privileges to database
* REVOKE - withdraw access privileges given with the GRANT command
TCL
Transaction Control (TCL) statements are used to manage the changes made by DML statements. It allows statements to be grouped together into logical transactions.
* COMMIT - save work done
* SAVEPOINT - identify a point in a transaction to which you can later roll back
* ROLLBACK - restore database to original since the last COMMIT
* SET TRANSACTION - Change transaction options like isolation level and what rollback segment to use
ACID Properties:
The ACID rules of transaction in any database assure the reliability of data in all transactions in the database.
Atomicity - this rule states that either the complete transaction takes place, or none. Even if a part of a transaction fails to work, the complete transaction will fail.
Consistency - this rule ensures that the database is stable, before and after the transaction, even if a transaction fails.
Isolation - this rule states that when a process is going on in a transaction, the data remains in isolation of other entities in the database.
Durability - this rule states that when a transaction completes successfully, it remains in stable state and is persisted in the database.
A transaction is one or more actions that are defined as a single unit of work. In the Relational Database Management System (RDBMS) world they also comply with ACID properties:
Atomicity: An atomic transaction means either all the actions happen or none of them. If an action fails half-way through a transaction, then all previous actions in the transaction must be rolled back as if they never happened.
Consistency: A transaction cannot break the integrity rules of the database; it must leave the database in a consistent state. For example, you might specify that stock levels cannot be a negative value, a spare part cannot exist without a parent object, or the data in a sex field must be male or female.
Isolation: SQL Server is designed to service many concurrent users, but from the viewpoint of each user, the data set must look like the user is the only one on the system. Each transaction must be entirely self-contained, and changes it makes must not be readable by any other transaction. SQL Server allows flexibility in the degree of isolation you can specify for your transaction so that you can find a balance between the performance and business requirements.
Durability: When a transaction is committed, it must persist even if there is a system failure immediately afterwards. When you commit a transaction in SQL Server, the information needed to replay it is physically written to the transaction log before the commit is returned to the user as successful.
JOINS:
There is another way to know the Database Connectionstring:
Take new notepad and save it with "yourFilename.udl".
for example let us take test.udl and it looks like small icon.
Double click on that, then it opens a window like this
From this you can generate a connectionstring. Finally you need to check the connection by clicking the test connection from above window.
To view the connectionstring, open this file with notepad. There you can find the database connectionstring.
DELETE : You can use WHERE clause
TRUNCATE is a DDL command and cannot be rolled back. All of the memory space is released back to the server.
DELETE is a DML command and can be rolled back.
Both commands accomplish identical tasks (removing all data from a table) but TRUNCATE is much faster.
The Main Difference Between DELETE & TRUNCATE Are :-
[1] DELETE - is a DML Command & TRUNCATE - is a DDL Command
[2] After DELETE - can rollback the Records & After TRUNATE - cannot rollback the records
[3] In DELETE Command you can give the conditions in WHERE Clause & In TRUNCATE you cannot give conditions
[4] After using DELETE Command The memory will be occupied till the user does not give ROLLBACK or COMMIT & After using TRUNCATE Command The memory realeased immediately
TRUNCATE SQL Command:
TRUNCATE is faster and uses fewer system and transaction log resources than DELETE.
TRUNCATE removes the data by deallocating the data pages used to store the table’s data, and only the page deallocations are recorded in the transaction log.
TRUNCATE removes all rows from a table, but the table structure and its columns, constraints, indexes and so on remain. The counter used by an identity for new rows is reset to the seed for the column.
You cannot use TRUNCATE TABLE on a table referenced by a FOREIGN KEY constraint.
Because TRUNCATE TABLE is not logged, it cannot activate a trigger.
TRUNCATE can not be Rolled back.
TRUNCATE is DDL Command.
TRUNCATE Resets identity of the table.
DELETE SQL Command:
DELETE removes rows one at a time and records an entry in the transaction log for each deleted row.
If you want to retain the identity counter, use DELETE instead. If you want to remove table definition and its data, use the DROP TABLE statement.
DELETE Can be used with or without a WHERE clause
DELETE Activates Triggers.
DELETE can be Rolled back.
DELETE is DML Command.
DELETE does not reset identity of the table.
DDL,DML,DCL & TCL:
DDL
Data Definition Language (DDL) statements are used to define the database structure or schema. Some examples:
* CREATE - to create objects in the database
* ALTER - alters the structure of the database
* DROP - delete objects from the database
* TRUNCATE - remove all records from a table, including all spaces allocated for the records are removed
* COMMENT - add comments to the data dictionary
* RENAME - rename an object
DML
Data Manipulation Language (DML) statements are used for managing data within schema objects. Some examples:
* SELECT - retrieve data from the a database
* INSERT - insert data into a table
* UPDATE - updates existing data within a table
* DELETE - deletes all records from a table, the space for the records remain
* MERGE - UPSERT operation (insert or update)
* CALL - call a PL/SQL or Java subprogram
* EXPLAIN PLAN - explain access path to data
* LOCK TABLE - control concurrency
DCL
Data Control Language (DCL) statements. Some examples:
* GRANT - gives user's access privileges to database
* REVOKE - withdraw access privileges given with the GRANT command
TCL
Transaction Control (TCL) statements are used to manage the changes made by DML statements. It allows statements to be grouped together into logical transactions.
* COMMIT - save work done
* SAVEPOINT - identify a point in a transaction to which you can later roll back
* ROLLBACK - restore database to original since the last COMMIT
* SET TRANSACTION - Change transaction options like isolation level and what rollback segment to use
ACID Properties:
The ACID rules of transaction in any database assure the reliability of data in all transactions in the database.
Atomicity - this rule states that either the complete transaction takes place, or none. Even if a part of a transaction fails to work, the complete transaction will fail.
Consistency - this rule ensures that the database is stable, before and after the transaction, even if a transaction fails.
Isolation - this rule states that when a process is going on in a transaction, the data remains in isolation of other entities in the database.
Durability - this rule states that when a transaction completes successfully, it remains in stable state and is persisted in the database.
A transaction is one or more actions that are defined as a single unit of work. In the Relational Database Management System (RDBMS) world they also comply with ACID properties:
Atomicity: An atomic transaction means either all the actions happen or none of them. If an action fails half-way through a transaction, then all previous actions in the transaction must be rolled back as if they never happened.
Consistency: A transaction cannot break the integrity rules of the database; it must leave the database in a consistent state. For example, you might specify that stock levels cannot be a negative value, a spare part cannot exist without a parent object, or the data in a sex field must be male or female.
Isolation: SQL Server is designed to service many concurrent users, but from the viewpoint of each user, the data set must look like the user is the only one on the system. Each transaction must be entirely self-contained, and changes it makes must not be readable by any other transaction. SQL Server allows flexibility in the degree of isolation you can specify for your transaction so that you can find a balance between the performance and business requirements.
Durability: When a transaction is committed, it must persist even if there is a system failure immediately afterwards. When you commit a transaction in SQL Server, the information needed to replay it is physically written to the transaction log before the commit is returned to the user as successful.
JOINS:
Join - A join in SQL is a clause that allows merging of records from one or more than one tables in a database. The records from the tables are fetched based on some values that are common to each. See code example below:
Say we have 2 tables, T_EMPLOYEES and T_SALARY
Select * from T_EMPLOYEES JOIN T_SALARY
ON
T_EMPLOYEES.EMP_ID = T_SALARY.EMP_ID;
Select * from T_EMPLOYEES JOIN T_SALARY
ON
T_EMPLOYEES.EMP_ID = T_SALARY.EMP_ID;
Consider the following 2 tables:
T_SHOOTERS
| T_GUNTYPES
|
Inner Join - in this type of join, every record in the tables being joined have a matching record. The condition based on which the records are matched is called the join predicate.
Implicit Vs. Explicit Inner Joins - When the clause 'Inner Join' is used, it is said to be an explicit join.
See code example below:
Implicit Vs. Explicit Inner Joins - When the clause 'Inner Join' is used, it is said to be an explicit join.
See code example below:
SELECT * FROM t_shooters
INNER JOIN t_guntypes
ON t_shooters.gun_type = t_guntypes.gun_type;
Above is an explicit inner join. The same can be achieved without using the 'inner join' clause like this:
SELECT * FROM t_shooter, t_guntype
WHERE t_shooters.gun_type = t_guntypes.gun_type;
Above is an implicit inner join.
INNER JOIN t_guntypes
ON t_shooters.gun_type = t_guntypes.gun_type;
Above is an explicit inner join. The same can be achieved without using the 'inner join' clause like this:
SELECT * FROM t_shooter, t_guntype
WHERE t_shooters.gun_type = t_guntypes.gun_type;
Above is an implicit inner join.
Equi Join - This is a type of Inner Join. (It is also called a Theta Join). - It is a join where the equality '=' operator is used. In case an operator like '<' or '>' or any other operator is used, it is not an Equi Join.
An Equi Join may be used by using equality operator or the Using clause. See code example below:
An Equi Join may be used by using equality operator or the Using clause. See code example below:
SELECT T_SHOOTERS.NAME, GUN_TYPE, T_GUNTYPES.DESCRIPTION
FROM T_SHOOTERS INNER JOIN T_GUNTYPES
USING(GUN_TYPE);
FROM T_SHOOTERS INNER JOIN T_GUNTYPES
USING(GUN_TYPE);
Natural Join - It is a type of Inner Join. It is a join where in the join predicate is based on all the column names that are common to both the tables being joined. See code example below:
SELECT * From T_SHOOTERS NATURAL JOIN T_GUNTYPES;
Results will be as below:
Results will be as below:
Gun_Type | T_Shooters.Name | T_Guntypes.Description |
1 | Ballu Balram | Katta Pt. 5 |
2 | Ekgoli Shikari | Desi Bandook Single Barrel |
3 | Fauji Bhai | Rocket Launcher |
4 | Thulla | Colt |
5 | Jackal | Colt |
Cross Join - Also called Cartesian Join. It is the result of joining each row of a table with each row of the other table.
Outer Join - In an Outer join, each record of a table does not really need to match with a record in the corresponding table. Outer joins maybe Left Outer Joins or Right Outher Joins. Outer Joins are always explicit.
Left Outer Join - This join contains all records from the left table, and matching records in the other table. However, if there are no matching records in the other table, it will still return a result, where in the records of the other table will be NULL.
Right Outer Join - This join fetches all records from the right table and only matching records from the left table. By saying left table, it means the table who's name is to the left of the Join Clause.
See code example below:
Outer Join - In an Outer join, each record of a table does not really need to match with a record in the corresponding table. Outer joins maybe Left Outer Joins or Right Outher Joins. Outer Joins are always explicit.
Left Outer Join - This join contains all records from the left table, and matching records in the other table. However, if there are no matching records in the other table, it will still return a result, where in the records of the other table will be NULL.
Right Outer Join - This join fetches all records from the right table and only matching records from the left table. By saying left table, it means the table who's name is to the left of the Join Clause.
See code example below:
SELECT * FROM t_shooters LEFT OUTER JOIN t_guntypes
ON t_shooters.gun_type = t_guntypes.gun_type;
ON t_shooters.gun_type = t_guntypes.gun_type;
Full Outer Join - A full outer join merges the result fetched from Left and Right Outer joins. See code example below:
SELECT * FROM t_shooters
FULL OUTER JOIN t_guntypes
ON t_shooters.gun_type = t_guntypes.gun_type;
FULL OUTER JOIN t_guntypes
ON t_shooters.gun_type = t_guntypes.gun_type;
There is another way to know the Database Connectionstring:
Take new notepad and save it with "yourFilename.udl".
for example let us take test.udl and it looks like small icon.
Double click on that, then it opens a window like this
From this you can generate a connectionstring. Finally you need to check the connection by clicking the test connection from above window.
To view the connectionstring, open this file with notepad. There you can find the database connectionstring.
