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