Interview Questions -- SQL Server -- Part-1
Following are the SQL Server basic interview questions.
Questions:-
- What is the difference between DELETE and TRUNCATE?
- What is MERGE Command and its advantage.
- What is the difference between DROP and TRUNCATE.
- What is HAVING Clause and how it is differentiate from WHERE Clause.
- How to check Table existence/availability, that means if table already available with the given name then DROP and create.
- What are the two authentication modes in SQL Server.
- What is CONSTRAINT and how many typ of Constraints available.
- What is the Prerequisite for Creating FOREIGN KEY Constraint.
- What is IDENTITY Column in a table.
- How many types of Transactions
- Implicit Transaction
- Explicit Transaction
- What is COMMIT and ROLLBACK.
- How to copy data from one table to another table.
- What is Local and Global Temp Tables.
- What is difference between Variable and Table Variable.
- What is CTE
- What is Recursive CTE.
- What is Sub Query and its types.
Please share your answer through comments.
SQL Server handles two types of transaction mode:
ReplyDelete(a) Implicit transaction
(b) Explicit transaction
Implicit transaction: In Implicit transaction mode, the instance of the SQL Server Database Engine automatically starts a new transaction after the current transaction is committed or rolled back for each DML statement. We do not need to define the start of a transaction; each statement level transaction will be committed or rolled back automatically.
Implicit transaction can be turned on or off per batch basis. We can use the “SET IMPLICIT_TRANSACTIONS ON/OFF” statement to turn implicit transaction mode off. We can use the “COMMIT TRANSACTION” or “ROLLBACK TRANSACTION” statements to end each transaction.
Explicit transaction: An explicit transaction is one in which we explicitly define both the starting and ending of the transaction. To start an explicit transaction, we can use “BEGIN TRANSACTION” and then complete the transaction by using “COMMIT TRANSACTION” or “ROLLBACK TRANSACTION”.
How to take Back up of table1 :-
ReplyDeleteSELECT *
INTO newtable [IN externaldb]
FROM table1;
ANSWER FOR Q1)
ReplyDelete1>TRUNCATE is a DDL command whereas DELETE is a DML command.
2>TRUNCATE is much faster than DELETE.
Reason:When you type DELETE.all the data get copied into the Rollback Tablespace first.then delete operation get performed.
3>You Can not use conditions(WHERE clause) in TRUNCATE.
But in DELETE you can write conditions using WHERE clause
4>Reset auto increment after deleting a table rows is possible in truncate not in delete