How to get @@error and @@rowcount at the same time?

QA-SQL Server

If @@Rowcount is checked after Error checking statement then it will have 0 as the value of @@Recordcount as it would have been reset.
And if @@Recordcount is checked before the error-checking statement then @@Error would get reset. To get @@error and @@rowcount at the same time do both in same statement and store them in local variable.


What is De-normalization?

QA-SQL Server

De-normalization is the process of attempting to optimize the performance of a database by adding redundant data. It is sometimes necessary because current DBMSs implement the relational model poorly.

A true relational DBMS would allow for a fully normalized database at the logical level, while providing physical storage of data that is tuned for high performance. De-normalization is a technique to move from higher to lower normal forms of database modeling in order to speed up database access.

What are primary keys and foreign keys?

QA-SQL Server

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?

QA-SQL Server

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?

QA-SQL Server

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?

QA-SQL Server

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?

QA-SQL Server

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.