SQL Queries - Assignment-8

 ASSIGNMENT 08

----------------------------


Interview Questions Based On IDENTITY in SQL Server 

  1. What is the purpose of the IDENTITY property in SQL Server?
  2. How do you define an IDENTITY column in SQL Server?
  3. Can you insert values manually into an IDENTITY column? If yes, how?
  4. How would you reset the identity seed of a table after deleting all rows from it?
  5. What is the difference between an IDENTITY column and a SEQUENCE in SQL Server?
  6. What is SCOPE_IDENTITY() and how does it differ from @@IDENTITY and IDENT_CURRENT()?
  7. How can you retrieve the last identity value generated in the current session or scope?
  8. Explain how DBCC CHECKIDENT works.
  9. Can an IDENTITY column have a negative seed or increment?
  10. What happens if you try to insert a record after the identity value reaches its maximum limit?
  11. How would you handle gaps in an IDENTITY column caused by transaction rollbacks or deletions?
  12. If you delete a row from a table with an IDENTITY column, will SQL Server reuse that identity value for a new row? Why or why not?
  13. What happens when a transaction that inserts a row into a table with an IDENTITY column is rolled back? Does the identity value also roll back?
  14. Describe a scenario where an IDENTITY column could cause issues with replication or data synchronization.
  15. How would you troubleshoot or resolve an issue where SCOPE_IDENTITY() returns NULL after an insert operation?

Hands on Practicals :

  • Write a SQL query that inserts a row into a table with an IDENTITY column, and then retrieves the identity value of the inserted row using SCOPE_IDENTITY()
  • Demonstrate how to reset the identity value of a table to start from 1, even if there are existing rows in the table.
  • Create a table with an IDENTITY column that increments by -1 starting from -100, and insert three rows into it. What identity values will be generated?
  • Suppose you have a table where the identity column has reached its maximum limit for INT. How would you modify the table to handle more rows without losing existing data?

Comments

Post a Comment