Most Importan Question of SQL in Interview.
If you are looking for most important question of SQL in the interview then you are the at right place landed on this website. In this website you will got idea about most important question of SQL which is help you to crack your interview in terms of SQL based question.
How Error handling done in SQL? Have you done error handling in your project?
This is very important question of SQL which is asking every interviewer in interview to each candidates.
We use Try Catch block just like C# language to catch and handle the exceptions in SQL. We cannot use try catch blocks in functions. If we have to through error to calling application then we use RAISEERROR function in catch block.
Also we specify ROLLBACK command in catch block when we are dealing with transactions.
RAISEERROR Function is use to throw exception directly to the calling application.
Syntax of RAISEERROR Function is – RAISERROR(‘Error Message’, ErrorSeverity, ErrorState)
Severity and State are integers. In most cases, when you are returning custom errors, the severity level is 16, which indicates general errors that can be corrected by the user.
ErrorState is also an integer between 1 and 255. RAISERROR only generates errors with state from 1 through 127.
What are transactions in SQL? What all commands used in Transaction?
A transaction is a group of commands that change the data stored in a database. A transaction is treated as a single unit. A transaction ensures that, either all of the commands succeed, or none of them. If one of the commands in the transaction fails, all of the commands fail, and any data that was modified in the database is rolled back. In this way, transactions maintain the integrity of data in a database.
Transaction processing follows these steps:
1. Begin a transaction.
2. Process database commands.
3. Check for errors.
If errors occurred,
rollback the transaction,
else,
commit the transaction
We use Commit command to commit the changes permanently to database and Rollback command to rollback the
changes on any error while working with transactions.
What are Cursors in SQL? Can you tell me what all steps are followed while using Cursors?
If there is ever a need to process the rows, on a row-by-row basis, then cursors are your choice. Cursors are very bad for performance, and should be avoided always. Most of the time, cursors can be very easily replaced using joins.
There are different types of cursors in sql server as listed below.
1. Forward-Only
2. Static
3. Keyset
4. Dynamic
We use below steps while using the cursors :
- Declare the cursor
- Open statement
- Fetch the row from the result set into the variable
- @@Fetch_Status to check if result set still has rows
- Release the row set Deallocate the resources associated with the cursors.
[Note : If interviewer ask where did u use cursors in your project? Ans : I have never came across situation where I can implement cursors in my project. Again they are bad over performance.]
What are Row_Number(), Rank(), Dense_Rank() functions?
Row_Number function
Returns the sequential number of a row starting at 1
ORDER BY clause is required
PARTITION BY clause is optional
When the data is partitioned, row number is reset to 1 when the partition changes.
Syntax : ROW_NUMBER() OVER (ORDER BY Col1, Col2)
Rank and Dense_Rank functions
Returns a rank starting at 1 based on the ordering of rows imposed by the ORDER BY clause
ORDER BY clause is required
PARTITION BY clause is optional
When the data is partitioned, rank is reset to 1 when the partition changes
Difference between RANK, DENSE_RANK and ROW_NUMBER functions
ROW_NUMBER : Returns an increasing unique number for each row starting at 1, even if there are duplicates.
RANK : Returns an increasing unique number for each row starting at 1. When there are duplicates, same rank is assigned to all the duplicate rows, but the next row after the duplicate rows will have the rank it would have been assigned if there had been no duplicates. So RANK function skips rankings if there are duplicates.
DENSE_RANK : Returns an increasing unique number for each row starting at 1. When there are duplicates, same rank is assigned to all the duplicate rows but the DENSE_RANK function will not skip any ranks. This means the next row after the duplicate rows will have the next rank in the sequence.
What is Pivot and UnPivot in SQL? not much Imp.
- Pivot is a sql server operator that can be used to turn unique values from one column, into multiple columns in the output, there by effectively rotating a table.
- UNPIVOT performs the opposite operation to PIVOT by rotating columns of a table-valued expression into column values.
- In short, PIVOT operator turns ROWS into COLUMNS, where as UNPIVOT turns COLUMNS into ROWS.
Most Importan Question of SQL in Interview at end your got your answer for your SQL most important question in this website. Article please comment your advice if any things you like to make more I will definitely provide you.Most Importan Question of SQL in Interview.
Most Importan Question of SQL in Interview, Most Importan Question of SQL in Interview