SHASHANGKA SHEKHAR'S BLOG

#Author #Speaker

What are primary keys and foreign keys?

Primary keys are the unique identifiers for each row. They must contain unique values and cannot be null. Due to their importance in relational databases, Primary keys are the most fundamental of all keys and constraints.

A table can have only one Primary key.

Foreign keys are both a method of ensuring data integrity and a manifestation of the relationship between tables.

What is the difference between a local and a global variable?

local temporary table exists only for the duration of a connection or, if defined inside a compound statement, for the duration of the compound statement.

global temporary table remains in the database permanently, but the rows exist only within a given connection. When connection are closed, the data in the global temporary table disappears.

However, the table definition remains with the database for access when database is opened next time.

What is Raiseerror?

Stored procedures report errors to client applications via the RAISERROR command.

RAISERROR doesn’t change the flow of a procedure; it merely displays an error message, sets the @@ERROR automatic variable, and optionally writes the message to the SQL Server error log and the NT application event log.

What is @@ERROR?

The @@ERROR automatic variable returns the error code of the last Transact-SQL statement.

If there was no error, @@ERROR returns zero.

Because @@ERROR is reset after each Transact-SQL statement, it must be saved to a variable if it is needed to process it further after checking it.

Can a stored procedure call itself or recursive stored procedure? How many level SP nesting possible?

Yes,Because Transact-SQL supports recursion, you can write stored procedures that call themselves. Recursion can be defined as a method of problem solving wherein the solution is arrived at by repetitively applying it to subsets of the problem.

A common application of recursive logic is to perform numeric computations that lend themselves to repetitive evaluation by the same processing steps. Stored procedures are nested when one stored procedure calls another or executes managed code by referencing a CLR routine, type, or aggregate. You can nest stored procedures and managed code references up to 32 levels.

What is SQL server agent?

SQL Server agent plays an important role in the day-to-day tasks of a database administrator (DBA).

It is often overlooked as one of the main tools for SQL Server management. Its purpose is to ease the implementation of tasks for the DBA, with its full-function scheduling engine, which allows you to schedule your own jobs and scripts.

What are types of sub-queries?

  1. Single-row subquery, where the subquery returns only one row.
  2. Multiple-row subquery, where the subquery returns multiple rows and
  3. Multiple column subquery, where the subquery returns multiple columns.