Advertisements
Home > DB Concepts, Differences > DB Basics – What are DDL, DML, DCL and TCL commands & difference b/w them?

DB Basics – What are DDL, DML, DCL and TCL commands & difference b/w them?


–> DDL – Data Definition Language: statements used to Create, Alter, Drop Database Objects.
Some examples:

– CREATE: used to define new objects

– ALTER: used to modify the definition of existing objects

– DROP: used to remove existing entities.

– TRUNCATE TABLE: used to remove all rows from a table without logging the individual row deletions.

– UPDATE STATISTICS: used to update query optimization statistics on a table or indexed view.
 

–> DML – Data Manipulation Language: statements used to Retrieve, Insert, Update, Remove and Manage data within DB objects.
Some examples:

– SELECT: retrieves one or more rows from a Table or View.

– INSERT: insert one or more rows from a Table or View.

– UPDATE: changes existing data in a Table or View.

– DELETE: removes one or more rows from a Table or View.

– BULK INSERT: imports a data file into a database Table or View in a user-specified format.

– MERGE: performs Insert, Update and/or Delete operations on a Target table based on the results of a JOIN with a source table in one Transaction.

– READTEXT: reads text, ntext, or image values from a text, ntext, or image column

– UPDATETEXT: updates an existing text, ntext, or image field.

– WRITETEXT: permits minimally logged, interactive updating of an existing text, ntext, or image column.
 

–> DCL – Data Control Language: statements used to control the access of data stored in database and provide data security.
Some examples:

– GRANT: grants permissions on a securable to a principal.

– REVOKE: removes a previously granted or denied permission.

– SETUSER: allows a member of the sysadmin fixed server role or db_owner fixed database role to impersonate another user.

– EXECUTE AS, statement: sets the execution context of a session.

– EXECUTE AS, clause: define the execution context of the following user-defined modules: functions (except inline table-valued functions), procedures, queues, and triggers.

– REVERT: switches the execution context back to the caller of the last EXECUTE AS statement.

– OPEN MASTER KEY: opens the Database Master Key of the current database.

– CLOSE MASTER KEY: closes the master key of the current database.

– OPEN SYMMETRIC KEY: Decrypts a symmetric key and makes it available for use.

– CLOSE SYMMETRIC KEY: closes a symmetric key, or closes all symmetric keys open in the current session.
permission through its group or role memberships.
 

–> TCL – Transaction Control Language: statements used to manage the changes made by DML statements. It allows statements to be grouped together into logical transactions.
Some Examples:

– BEGIN DISTRIBUTED TRANSACTION: specifies the start of a Transact-SQL distributed transaction managed by Microsoft Distributed Transaction Coordinator (MS DTC).

– BEGIN TRANSACTION: marks the starting point of an explicit, local transaction. BEGIN TRANSACTION increments @@TRANCOUNT by 1.

– COMMIT TRANSACTION: marks the end of a successful implicit or explicit transaction.

– COMMIT WORK: marks the end of a transaction.

– ROLLBACK TRANSACTION: rolls back an explicit or implicit transaction to the beginning of the transaction, or to a savepoint inside the transaction.

– ROLLBACK WORK: rolls back a user-specified transaction to the beginning of the transaction.

– SAVE TRANSACTION: sets a savepoint within a transaction.
 

Check the video:

DDL DML DCL TCL
 


Advertisements
  1. jayesh
    May 27, 2016 at 11:41 am

    nice

  2. August 21, 2010 at 5:02 pm

    Truncate *IS* a logged operation, it just doesn’t log removing the records, it logs the page deallocations. How do you think it can be rolled-back if it isn’t logged? Rollback is *purely* driven by what’s in the transaction log.The difference are in functionality like:1. We can use WHERE clause with delete but not with truncate.2. Truncate reset the identity but Delete not.3. Truncate does not fire the trigger (on delete event) but Delete does.4. Truncate can be used if table is referenced by other foreign key tables but Delete can be used.More about Truncate can be rollbacked:http://blog.sqlauthority.com/2010/03/04/sql-server-rollback-truncate-command-in-transaction/

  3. August 21, 2010 at 4:55 pm

    In SQL Server, by default AutoCommit is On.So without explicit transaction neither delete can be rolled back nor Truncate command.Within a transaction both Delete and Truncate can be rolled back.More Info:-http://blog.sqlauthority.com/contact-me-contact-pinaldave/contact-me-contact-pinaldave-archive-5/#comment-59592

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: