What is the difference between primary key and unique key ?
1. A primary key does not allows a null value where as unique key allows null value.
2. Primary key created cluster index by default. Whereas unique key creates non clustered index.
3. There are only one primary key in any table while there are multiple unique key in a single table.
What is the difference between implicit and explicit transaction?
1. Implicit transaction is auto commit, there are no beginning and ending of the transaction while explicit transaction has beginning and end and rollback command.
2. In explicit transaction, if error occurs between transaction then it can be roll back where as it is not possible in implicit transaction.
What is #temp and @table variable in SQL server?
#Temp table : it is temporary table that is generally created to store session specific data. #Temp table is visible only to the current scope. Generally ,the table gets clear up automatically when the current procedure goes out of scope.
@Table variable : @Table variable is similar to temporary table except with more flexibility. It is not physically stored in hard disk. We should choose it when we need to store less than 100 records.
How are transaction used?
Transaction allows you to group SQL command into single unit. The transaction begins with certain task and ends when all task within it are completed. The transaction completes successfully only if all command within it complete successfully. The whole things fail if command fails. The begin transaction, Rollback and commit transaction are used to work within transaction.
What is difference between procedure and function?
1. Procedure may have 0 to n return parameter. Whereas function return single parameter.
2. Procedure may have input and output parameter where as function may have only input parameter.
3. A procedure can use select and DML statement, where as function can use only select statement
4. Procedure can use Try … catch for error handling where as function cannot use it.
5. Procedure can call function where function cannot call procedure.
6. Procedure can go for transaction management where as function can’t.
What is the difference between clustered and non-clustered index?
Clustered Index : A clustered index is a special type of index where records are physically stored in a table. There may be a single clustered index in a table. And leaf node of clustered index contains a data page.
Non-Clustered Index : A non-clustered index is a special type of index where logical order of index does not match a physical stored order of the row on the disk. It contains pointer to the data that stored in data page.
Or simply we can say that.
Clustered Index: Clustered index physically rearrange the data that users inserts in your tables. It is nothing but a dictionary type data where actual data remains.
Non-Clustered Index: It Non-Clustered Index contains pointers to the data that is stored in the data page. It is a kind of index backside of the book where you see only the reference of a kind of data.
What is Trigger?
A trigger is special type of store procedure which executes automatically in place of or after data modification (Insert, Update, Delete). It allows us to execute a batch of SQL code when either an insert, update or delete command is executed against a specific table. There are four types of trigger. They are :-
1. Insert
2. Update
3. Delete
4. Instead of
What is cursor?
A cursor is a database object used by application to maintain data in a set on row by row basis.
What is constraints?
SQL Server uses constraints to enforce limitations on the data that can be entered into a particular column in table. There are following types of constraints.
Unique, Default, Check, Primary Key, Foreign Key, Not Null.
What is difference between Truncate and Delete?
1. In TRUNCATE we cannot rollback. Where as in DELETE we can rollback.
2. Delete keep the lock over each row where Truncate keeps the lock on table not on all the row.
3. Counter of the Identity column is reset in Truncate where it is not reset in Delete.
4. Trigger is not fired in Truncate where as trigger is fired in Delete.
5. Truncate delete whole record of table at a time where as delete does it one by one.
How do you implement one-to-one, one-to-many and many-to-many relationships while designing tables?
One-to-One relationship can be implemented as a single table and rarely as two tables with primary and foreign key relationships.
One-to-Many relationships are implemented by splitting the data into two tables with primary key and foreign key relationships.
Many-to-Many relationships are implemented using a junction table with the keys from both the tables forming the composite primary key of the junction table.
What is difference between union All statement and Union?
The main difference between union All statement and Union is Union All statement is much faster then union. Because Union all statement does not look for duplicate rows, but on the other hand union statement does not look for duplicate rows, where or not exist.
1. A primary key does not allows a null value where as unique key allows null value.
2. Primary key created cluster index by default. Whereas unique key creates non clustered index.
3. There are only one primary key in any table while there are multiple unique key in a single table.
What is the difference between implicit and explicit transaction?
1. Implicit transaction is auto commit, there are no beginning and ending of the transaction while explicit transaction has beginning and end and rollback command.
2. In explicit transaction, if error occurs between transaction then it can be roll back where as it is not possible in implicit transaction.
What is #temp and @table variable in SQL server?
#Temp table : it is temporary table that is generally created to store session specific data. #Temp table is visible only to the current scope. Generally ,the table gets clear up automatically when the current procedure goes out of scope.
@Table variable : @Table variable is similar to temporary table except with more flexibility. It is not physically stored in hard disk. We should choose it when we need to store less than 100 records.
How are transaction used?
Transaction allows you to group SQL command into single unit. The transaction begins with certain task and ends when all task within it are completed. The transaction completes successfully only if all command within it complete successfully. The whole things fail if command fails. The begin transaction, Rollback and commit transaction are used to work within transaction.
What is difference between procedure and function?
1. Procedure may have 0 to n return parameter. Whereas function return single parameter.
2. Procedure may have input and output parameter where as function may have only input parameter.
3. A procedure can use select and DML statement, where as function can use only select statement
4. Procedure can use Try … catch for error handling where as function cannot use it.
5. Procedure can call function where function cannot call procedure.
6. Procedure can go for transaction management where as function can’t.
What is the difference between clustered and non-clustered index?
Clustered Index : A clustered index is a special type of index where records are physically stored in a table. There may be a single clustered index in a table. And leaf node of clustered index contains a data page.
Non-Clustered Index : A non-clustered index is a special type of index where logical order of index does not match a physical stored order of the row on the disk. It contains pointer to the data that stored in data page.
Or simply we can say that.
Clustered Index: Clustered index physically rearrange the data that users inserts in your tables. It is nothing but a dictionary type data where actual data remains.
Non-Clustered Index: It Non-Clustered Index contains pointers to the data that is stored in the data page. It is a kind of index backside of the book where you see only the reference of a kind of data.
What is Trigger?
A trigger is special type of store procedure which executes automatically in place of or after data modification (Insert, Update, Delete). It allows us to execute a batch of SQL code when either an insert, update or delete command is executed against a specific table. There are four types of trigger. They are :-
1. Insert
2. Update
3. Delete
4. Instead of
What is cursor?
A cursor is a database object used by application to maintain data in a set on row by row basis.
What is constraints?
SQL Server uses constraints to enforce limitations on the data that can be entered into a particular column in table. There are following types of constraints.
Unique, Default, Check, Primary Key, Foreign Key, Not Null.
What is difference between Truncate and Delete?
1. In TRUNCATE we cannot rollback. Where as in DELETE we can rollback.
2. Delete keep the lock over each row where Truncate keeps the lock on table not on all the row.
3. Counter of the Identity column is reset in Truncate where it is not reset in Delete.
4. Trigger is not fired in Truncate where as trigger is fired in Delete.
5. Truncate delete whole record of table at a time where as delete does it one by one.
How do you implement one-to-one, one-to-many and many-to-many relationships while designing tables?
One-to-One relationship can be implemented as a single table and rarely as two tables with primary and foreign key relationships.
One-to-Many relationships are implemented by splitting the data into two tables with primary key and foreign key relationships.
Many-to-Many relationships are implemented using a junction table with the keys from both the tables forming the composite primary key of the junction table.
What is difference between union All statement and Union?
The main difference between union All statement and Union is Union All statement is much faster then union. Because Union all statement does not look for duplicate rows, but on the other hand union statement does not look for duplicate rows, where or not exist.
No comments:
Post a Comment