Explain DML, DDL, DCL and TCL statements with examples?
DML: DML stands for Data Manipulation Language. DML is used to retrieve, store, modify, delete, insert and update data in database.
Examples of DML statements: SELECT, UPDATE, INSERT, DELETE statements.
DDL: DDL stands for Data Definition Language. DDL is used to create and modify the structure of database objects.
Examples: CREATE, ALTER, DROP statements.
DCL: DCL stands for Data Control Language. DCL is used to create roles, grant and revoke permissions, establish referential integrity etc.
Examples: GRANT, REVOKE statements
TCL: TCL stands for Transactional Control Language. TCL is used to manage transactions within a database.
Examples: COMMIT, ROLLBACK statements
What is the difference between Drop, Delete and Truncate statements in SQL Server?
Drop, Delete and Truncate - All operations can be rolled back.
All the statements (Delete, Truncate and Drop) are logged operations, but the amount of information that is logged varies. Delete statement logs an entry in the transaction log for each deleted row, where as Truncate Table logs only the Page deallocations.Hence, truncate is a little faster than Delete.
You can have a where clause in Delete statement where as Truncate statement cannot have a where clause. Truncate will delete all the rows in a Table, but the structure of the table remains. Drop would delete all the rows including the structure of the Table.
Please refer to the screen shot below for the differences summary snapshot between Drop, Delete and Truncate statements in SQL Server.
 |
What is Cascading referential integrity constraint?
Cascading referential integrity constraints allow you to define the actions Microsoft SQL Server should take when a user attempts to delete or update a key to which an existing foreign keys point.
You can instruct SQL Server to do the following:
1. No Action: This is the default behaviour. No Action specifies that if an attempt is made to delete or update a row with a key referenced by foreign keys in existing rows in other tables, an error is raised and the DELETE or UPDATE is rolled back.
2. Cascade: Specifies that if an attempt is made to delete or update a row with a key referenced by foreign keys in existing rows in other tables, all rows containing those foreign keys are also deleted or updated.
3. Set NULL: Specifies that if an attempt is made to delete or update a row with a key referenced by foreign keys in existing rows in other tables, all rows containing those foreign keys are set to NULL.
4. Set Default: Specifies that if an attempt is made to delete or update a row with a key referenced by foreign keys in existing rows in other tables, all rows containing those foreign keys are set to default values.
DIfference between primary key and unique key in SQL Server?
1. A table can have only one primary key. On the other hand a table can have more than one unique key.
2. Primary key column does not accept any null values, where as a unique key column accept one null value.
I have always thought that only Delete can be rolled back.
ReplyDeleteya that is correct only delete can be rollback....but truncate is faster than delete :) so u were right
DeleteTruncate can be rolled back until it is committed.
Deletehow can we rollback when delete a data
Deletetruncated table data will rollback only if it is used in active transaction that is inside begin tran...
DeleteIf you used delete, truncate or drop in begin transaction then u can rollback all..
Deletewhat is the importance of index in a table?
ReplyDeleteUse for shorting data.
DeleteFast Access the data
DeleteUse for access data and increase performance of queries
DeleteHi Venkat,
ReplyDeleteSince Truncate is not Logged, How is it possible to roll it back?
DELETE and TRUNCATE both can be rolled back when surrounded by TRANSACTION if the current session is not closed. If TRUNCATE is written in Query Editor surrounded by TRANSACTION and if session is closed, it can not be rolled back but DELETE can be rolled back.
DeleteIn either command if a transaction is commited can it be rolled back?
DeleteIf the transaction is committed, it can not be rolled back!
DeleteTRUNCATE command is used to delete complete data from an table.
Deletetruncate cant be roll back........
ReplyDeleteMost of the web sites say Truncate cannot be rolled back. That is not true. I have tested this on SQL Server 2008. Truncate can be rolled back. If you want try truncate yourself follow these steps.
ReplyDeleteStep 1: Truncate the table as part of the transaction, but do not commit.
Begin Tran
Truncate Table tblProduct
Step 2: Now issue a select statement. You will see no rows in the table.
Select * from tblProduct
Step 3: Now issue a rollback statement, to rollback the truncate operation.
Rollback
Step 4: Now issue a select statement. You will see all the rows in the table. This proves that Truncate can be rolled back.
Select * from tblProduct
Yes, you are right. Thanks for your step by step information.
DeleteNice explanation amit
DeleteTruncate can be rolled as Amit demonstrated but you can't create a trigger on it.
ReplyDeletethis is kishor,
ReplyDeletei think a cluster index get created by default when we create primary key..and a non-cluster index get created when we create unique key
Cluster index are created when have no any key,then primary key create automatically cluster index otherwise not create cluster index
DeleteEven Truncate can be roll back provided if you start the statment with BEGIN TRAN...
ReplyDeleteI think truncate in not rollback due to there is no log file created for this ...vijay raj
ReplyDeletewhat is the importance of index in a table?
ReplyDeleteRe: Index are only way to make fast retrieve of data.As like in book if we know index Number we can find page easy.Same way if our table contain millions of records it makes easy and fast search.There are two types of indexes cluster and non cluster.
Excellent article..Good for beginners..!!!
ReplyDeleteHi, the distinction between drop, delete and truncate is nice and explains the concepts very well. Great going...
ReplyDeletewhat is clustered and non-clustered index?
ReplyDeleteClustered
DeleteClustered indexes sort and store the data rows in the table or view based on their key values. These are the columns included in the index definition. There can be only one clustered index per table, because the data rows themselves can be sorted in only one order.
The only time the data rows in a table are stored in sorted order is when the table contains a clustered index. When a table has a clustered index, the table is called a clustered table. If a table has no clustered index, its data rows are stored in an unordered structure called a heap.
Nonclustered
Nonclustered indexes have a structure separate from the data rows. A nonclustered index contains the nonclustered index key values and each key value entry has a pointer to the data row that contains the key value.
The pointer from an index row in a nonclustered index to a data row is called a row locator. The structure of the row locator depends on whether the data pages are stored in a heap or a clustered table. For a heap, a row locator is a pointer to the row. For a clustered table, the row locator is the clustered index key.
You can add nonkey columns to the leaf level of the nonclustered index to by-pass existing index key limits, 900 bytes and 16 key columns, and execute fully covered, indexed, queries. For more information, see Create Indexes with Included Columns.
What does SET NOCOUNT ON do?
ReplyDeleteSet nocount on off the message how many rows affected and it is useful in procedures because it reduces network traffic.
Deleteyes you are right sailaja
DeleteVery, very nice videos on SQL, great job. my question is can I pass the 70-461 exam by watching these videos? in otherwise, those the videos follow the exam objective. your response to this question will be greatly appreciated.
ReplyDeleteonce again, thanks for all the great videos.
Hi ALL
ReplyDeleteWhen a TRUNCATE Table statement is executed the data in the table gets deleted(also memory is deallocated).It implies that the Truncate statement affects the data.Then why is it categorized under DDL statements.
So,Why is a TRUNCATE DDL and not DML?
Thanks in advance.
I dont know whether u r still looking for the ans or not.. but may be usefull for me if i am wrong...
DeleteAsper my knowledge tough truncate affects data indirectly .. the operation is not done at data level rather its done at table level...
So, Truncate is categorized under ddl...
Correct me if i am wrong..
This is Manoj Sharma
Deletetruncate command is categorized under ddl because it affect definition of table , i mean when you truncate a table it perform two task deleting rows and reseeding the seeds of identity column thats why it comes under ddl.
What is use of primary key and unique key
ReplyDeletePrimary key doesn't allow null values, whereas unique key allows.
Deleteby default Primary key is a clustered index, where as unique is a non clustered index
DeleteHi Everybody,
ReplyDeleteCan v get all Sql Clauses in a Single Query?
Hi venkat,
ReplyDeleteThere is no rollback option in sql server so how we can rollback the changes?
what is #table and ##table.
ReplyDelete#table is a local temp table which is used for that particular window and gets deleted when connection is closed and ##table is global temp table which is used by all the windows and servers but this is also get deleted when the connection of the server get closed and both does not occupy any memory
ReplyDeleteHi Guys,
ReplyDeleteOne more difference between Delete and Truncate:
if you have identity column in the table, the record will start from start means, its start from 1, whereas delete will start where it has end last identity.
Ex: table tblTest having 6 record, if you use delete query, it'll delete all record and when you insert new record it'll start at 7th ID.
I think is also one of the difference between Delete and Truncate
Kindly check from your end.
While creating primary key clustered index will be automatically created whereas unique key will have non clustered index
ReplyDelete