SQL Interview Questions:

Questions:
1) What is the name of the SQL Server query language?
2) What is the difference between DML and DDL?
3) Name the four main types of DML Query operations.
4) Name three reasons to use a stored procedure.
5) What is the difference between a stored procedure and an extended stored procedure? Where is an extended stored procedure stored?
6) What is the difference between "Truncate" and "Delete From"?
7) What is a SQL Server Page, and how is it used? How big is a page? How much data space is available on each SQL Server Page?


Answers:
1) Transact-SQL (used by Microsoft and Sybase)
2)
DDL is Data Definition Language statements. Some examples:

CREATE - to create objects in the database
ALTER - alters the structure of the database
DROP - delete objects from the database
TRUNCATE - remove all records from a table, including all spaces allocated for the records are removed
COMMENT - add comments to the data dictionary
GRANT - gives user's access privileges to database
REVOKE - withdraw access privileges given with the GRANT command

DML is Data Manipulation Language statements. Some examples:

SELECT - retrieve data from the/a database
INSERT - insert data into a table
UPDATE - updates existing data within a table
DELETE - deletes all records from a table, the space for the records remain
CALL - call a PL/SQL or Java subprogram
EXPLAIN PLAN - explain access path to data
LOCK TABLE - control concurrency

3) From above most popular are Select, Insert, Update and Delete.

4)
Encapsulation - changing the inner implementation as often you like.
Security - secured at a database level, and help prevent SQL Injection attacks.
Performance - Improving network traffic, etc..

5)Extended store procedure is accessed as though it was a compiled SQL program. They are not written in Transact-SQL, they reside in DLLs.

6)
Truncate is DDL and drops all rows no ROLLBACK possible, no WHERE clauses possible. Truncate drops blocks created for the table without erasing the definition of the table, delete just the content of those blocks. Aditional to this, clustered tables cannot be truncated. DELETE is DML and uses various memory structures allowing ROLLBACK and WHERE clauses.

DELETE from table_name where... ;
TRUNCATE table table_name;

7) Pages exist to store records. A database page is an 8192-byte (8KB) chunk of a database data file. They are aligned on 8KB boundaries within the data files, starting at byte-offset 0 in the file. Pages are the basic unit of IO that SQL server uses. 8060-bytes are used for storing data.