Interview Questions SQL Server - amitbhilagude/userfullinks GitHub Wiki

  1. Difference between SQL vs MySQL
    1. Both are relational databases.
    2. SQL is developed by Microsoft and MySQL is developed by Oracle.
    3. SQL is a licensed version and MySQL is open source
    4. SQL Supports C#, Java, PHP, etc., and MySQL was not supporting but in the recent update, there is also the support of C#
    5. Require more storage space in SQL
  2. SQL Server Agent
    1. When there is an agent then it is used to run a different job or scheduler in Azure or On-Prem.
    2. In Azure We are using Elastic Job Agent.
  3. Authentication Modes
    1. Windows authentication mode and mixed-mode on On-Prem.
    2. in Azure we have two options, Connectionstring and Service principal
  4. Local Temporary Table and Global Temporary Table
    1. Local Table gets deleted when the connection is closed. #table
    2. Global Table exists permanently and only rows get deleted. ##table
  5. Single User mode
    1. It will allow you to connect to only one user. Most common scenario if you want to change the scenario or restore data
  6. SQL Server Profiler
    1. It is used for troubleshooting and debugging the issue and analysis.
    2. It analyses the traces and give the performance of each query
  7. TCP/IP Port SQL Server runs
    1. 1433
  8. Subquery
    1. Having multiple queries outer query and subsequent queries. Subsequent queries are subqueries and will be run first.
  9. SQL Server Replication
    1. Merge, Transactional and Snapshot
    2. Transactional and Snapshot are most commonly used in Azure.
  10. Intend Lock
    1. It is a higher-level lock and it gives which lock is applied in the lock hierarchy.
    2. Three types: Shared lock, updated lock, and exclusive lock. They are used at the row level.
  11. Magic Tables
    1. It will automatically be created by SQL Server
    2. It keeps tracks of Insert and deletes records
  12. Temporal Tables
    1. Latest feature in SQL server 2016.
    2. Keep tracks of versioning of each record and get the recorded history.
  13. Triggers
    1. Execute SQL script if any event occurs e.g. Insert, Update, Delete, and Instead of
    2. Instead of will skip Insert, update or delete operation and perform the only trigger.
  14. Recursive Stored Procedure
    1. It is similar to recursive function and call the stored procedure again and again
  15. Trace Flag
  16. Substring and char index
    1. Substring will return String from StartIndex and EndIndex
    2. CharIndex will return the index of specific char
  17. Mirroring Server
    1. Mirroring server is a secondary server in Azure for failover.
  18. SQL Server curser
    1. Read each record one by one where the cursor is pointed.
  19. SQL Performance best practices
    1. How you design schema
    2. How you manage the connections
    3. How much data you retrieve
    4. How many round trips you do. Is your database chatty?
    5. How many concurrent connections are opened
    6. Memory and CPU or plan selected for your SQL server
  20. Stored Proc vs Function
    1. Function must return value. Store proc is optional
    2. You can use the function in the Select query. This was used in our data masking script.
  21. Joins in SQL
    1. Inner Join
    2. inLeft Jo
    3. Full Join
    4. Right Join
  22. Check Constraint in SQL
    1. Check is basically used to check the conditions in columns
    2. It is used in creating a table and adding a check will allow these values.
  23. Floor Function
    1. Round up the function
  24. Sp_locks
    1. List of locks currently held by the database.
  25. Sign Function
    1. Check if the number is negative, Positive
  26. Common performance issue in SQL Server
    1. Fragmentation
    2. Input or Output bottleneck
    3. Blocking
    4. Deadlock
    5. Missing Index