Basic SQL Server Interview Questions



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.


45 comments:

  1. I have always thought that only Delete can be rolled back.

    ReplyDelete
    Replies
    1. ya that is correct only delete can be rollback....but truncate is faster than delete :) so u were right

      Delete
    2. Truncate can be rolled back until it is committed.

      Delete
    3. how can we rollback when delete a data

      Delete
    4. truncated table data will rollback only if it is used in active transaction that is inside begin tran...

      Delete
    5. If you used delete, truncate or drop in begin transaction then u can rollback all..

      Delete
  2. what is the importance of index in a table?

    ReplyDelete
  3. Hi Venkat,
    Since Truncate is not Logged, How is it possible to roll it back?

    ReplyDelete
    Replies
    1. 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.

      Delete
    2. In either command if a transaction is commited can it be rolled back?

      Delete
    3. If the transaction is committed, it can not be rolled back!

      Delete
    4. TRUNCATE command is used to delete complete data from an table.

      Delete
  4. truncate cant be roll back........

    ReplyDelete
  5. Most 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.

    Step 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

    ReplyDelete
    Replies
    1. Yes, you are right. Thanks for your step by step information.

      Delete
  6. Truncate can be rolled as Amit demonstrated but you can't create a trigger on it.

    ReplyDelete
  7. this is kishor,
    i 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

    ReplyDelete
    Replies
    1. Cluster index are created when have no any key,then primary key create automatically cluster index otherwise not create cluster index

      Delete
  8. Even Truncate can be roll back provided if you start the statment with BEGIN TRAN...

    ReplyDelete
  9. I think truncate in not rollback due to there is no log file created for this ...vijay raj

    ReplyDelete
  10. what is the importance of index in a table?
    Re: 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.

    ReplyDelete
  11. Excellent article..Good for beginners..!!!

    ReplyDelete
  12. Hi, the distinction between drop, delete and truncate is nice and explains the concepts very well. Great going...

    ReplyDelete
  13. what is clustered and non-clustered index?

    ReplyDelete
    Replies
    1. Clustered
      Clustered 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.

      Delete
  14. Replies
    1. Set nocount on off the message how many rows affected and it is useful in procedures because it reduces network traffic.

      Delete
  15. Very, 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.
    once again, thanks for all the great videos.

    ReplyDelete
  16. Hi ALL
    When 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.

    ReplyDelete
    Replies
    1. I dont know whether u r still looking for the ans or not.. but may be usefull for me if i am wrong...
      Asper 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..

      Delete
    2. This is Manoj Sharma
      truncate 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.

      Delete
  17. What is use of primary key and unique key

    ReplyDelete
    Replies
    1. Primary key doesn't allow null values, whereas unique key allows.

      Delete
    2. by default Primary key is a clustered index, where as unique is a non clustered index

      Delete
  18. Hi Everybody,
    Can v get all Sql Clauses in a Single Query?

    ReplyDelete
  19. Hi venkat,

    There is no rollback option in sql server so how we can rollback the changes?

    ReplyDelete
  20. #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

    ReplyDelete
  21. Hi Guys,

    One 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.

    ReplyDelete
  22. While creating primary key clustered index will be automatically created whereas unique key will have non clustered index

    ReplyDelete

If you are aware of any other sql server questions asked in an interview, please post them below. If you find anything missing or wrong, please feel free to correct by submitting the form below.

 
Disclaimer - Terms of use - Contact Us