What are the different types of commands in SQL, divided based on the type of operation they perform with simple explanation.

A column is called a PRIMARY KEY if it uniquely identifies a specific row in a table of multiple rows. A UNIQUE key is a constraint applied on a column.

What are the major differences between Clustered Indexes and Non-Clustered Indexes, with examples, that you must know in SQL.

The SELECT command is a SQL (Structured Query Language) statement used to retrieve data from one or more tables in a database.

What are RANK, DENSE_RANK and ROW_NUMBER in SQL Server? What are the differences between them and how to use them? Explain in simple terms.

• Use column names instead of * in SELECT query
• Reduce the use of temp tables created in the SP, delete the tables once their use is over.
• Create indexes on frequently queried columns,
• use JOIN statements instead of sub-queries or correlated sub-queries.

We can use raiseError to throw custom exceptions in an P-SQL block.

Example:

RAISERROR (N'Error Raised: %s %d.', -- Message text.  
           10, -- Severity,  
           1, -- State,  
           N'number', -- First argument from the 
           5); -- Second argument.  

Returns Error Message: Error Raised: number 5.

DQL or Data Query Language commands are used for querying data from database objects. This is only a single command which can result in records from dbo such as tables, views etc and can be subject to projection.

SELECT [ DISTINCT column_1, column_2 .. | * ]
FROM <table_name>
WHERE <query_condition>
GROUP BY <columns_list>
HAVING <condition>
ORDER BY <columns_list> [ ASC | DESC ]

TCL or Transaction Control Language commands are used to enforce control on transactions within a database object which might result in a change in the state of data within the dbo.

These are:

• COMMIT
• ROLLBACK [ TO save_point_name ]
• SAVEPOINT save_point_name