Microsoft SQL Server Quiz

25 Questions | Total Attempts: 1750

SettingsSettingsSettings
Please wait...
SQL Server Quizzes & Trivia

Microsoft SQL Server Quiz


Related Topics
Questions and Answers
  • 1. 
    • A. 

      Gives an error.

    • B. 

      Returns all Professions which has not null values.

    • C. 

      is not an operator in SQL Server.

    • D. 

      None of the above.

  • 2. 
    What is the result of the below query: SELECT SUBSTRING('DOTNET', 1, 3) AS 'Substring'
    • A. 

      DOT

    • B. 

      OTN

    • C. 

      OT

    • D. 

      None of the above.

  • 3. 
    Which of the following is true about the SERIALIZABLE isolation level ?
    • A. 

      It protects against phantom reads.

    • B. 

      It causes the highest level of contention.

    • C. 

      It causes the highest level of blocking.

    • D. 

      All of the above.

  • 4. 
    What is the result of the below query: SELECT PATINDEX('DOT%', 'DOTNET') AS 'Index'
    • A. 

      0

    • B. 

      1

    • C. 

      -1

    • D. 

      2

  • 5. 
    • A. 

      DELETE FROM EMP OUTPUT deleted.* INTO EmpCompress FROM Emp JOIN deptON EMP.DeptId = Dept.DeptId WHERE DeptName='IT';

    • B. 

      DELETE FROM EMP OUTPUT deleted.* INTO EmpCompress FROM Emp JOIN Dept ON EMP.DeptId = Dept.DeptId ;

    • C. 

      DELETE FROM EMP OUT PUT deleted.* INTO EmpCompress FROM Emp JOIN deptON EMP.DeptId = Dept.DeptId WHERE DeptName='IT';

    • D. 

      None of the above.

  • 6. 
    • A. 

      SELECT clause

    • B. 

      FROM clause

    • C. 

      Both A and B.

    • D. 

      None of the above.

  • 7. 
    Which of the following is true?
    • A. 

      BUILT-IN functions in SQL Server are Nondeterministic.

    • B. 

      ISNULL function in SQL Server is Deterministic.

    • C. 

      All of the above.

    • D. 

      None of the above.

  • 8. 
    • A. 

      NULL values are ignored for all the aggregate functions.

    • B. 

      NULL values are included for all the aggregate functions.

    • C. 

      When an aggregate function is included in the SELECT clause, all other expressions in the SELECT clause must either be aggregate functions or included in a GROUP BY clause.

    • D. 

      Both A and C.

    • E. 

      Both B and C.

  • 9. 
    Assuming UserProfile is a table with PKUserId int, Profession Varchar(50)columns. What is the result of the below query: SELECT PKUserId,Profession  FROM UserProfile WHERE Profession = 'Engineer' AND (PKUserId > 12 OR PKUserId = 1)
    • A. 

      Displays Users with Profession as 'engineer' with PKUserId > 12 as well as the users with PKUserId of 1.

    • B. 

      Displays Users with Profession as 'engineer' with PKUserId > 12 only.

    • C. 

      Displays Users with Profession as 'engineer' with PKUserId of 1 only.

    • D. 

      None of the above.

  • 10. 
    • A. 

      The data is rolled back to the transaction starting on line1.

    • B. 

      The inserted row does not exist in the table.

    • C. 

      All of the above.

    • D. 

      None of the above.

    • E. 

      Only B.

  • 11. 
    Assuming Emp table with EmpId(int), Name(varchar(100)),HireDate(DateTime),Designation(varchar(20)) columns and Audit table with currentDate(DateTime), EmpId columns. What is the result of the following query: INSERT INTO Emp(Name,HireDate,Designation) OUTPUT getdate(), inserted.EmpId INTO Audit VALUES ('Robot','1/1/2011','Executive');
    • A. 

      Only inserts a new record into the Emp table.

    • B. 

      OUTPUT clause cannot be used in the INSERT command.

    • C. 

      Adds a new row to the Emp table, and also adds a corresponding row with the current date and time and the EmpId for the new employee into the Audit table.

    • D. 

      None of the above.

  • 12. 
    How many transactions at a time can obtain an update lock on a resource?
    • A. 

      1

    • B. 

      2

    • C. 

      3

    • D. 

      128

  • 13. 
    Which of the following queries returns the users whose username starts with any of the character between v to z?
    • A. 

      SELECT PKUserId, UserName FROM UserProfile WHERE (UserName LIKE '[vz]%')

    • B. 

      SELECT PKUserId, UserName FROM UserProfile WHERE (UserName LIKE '[v-z]%')

    • C. 

      SELECT PKUserId, UserName FROM UserProfile WHERE (UserName LIKE '[v-z%]')

    • D. 

      None of the above.

  • 14. 
    Which of the following options can be used for viewing lock status within your computer running SQL Server?
    • A. 

      We can use SQL Profiler to capture lock and blocking information.

    • B. 

      We can use the System Monitor that is part of the performance console to capture statistics on lock wait times, locks per second, and so on.

    • C. 

      We can use the Activity Monitor in SSMS to see information on blocking processes.

    • D. 

      We can use the sys.dm_tran_locks dynamic management view to gather information on locks being held by transactions.

    • E. 

      All of the above.

  • 15. 
    Which of the following commands is used to retrieve information about the information contained in the current transaction log ?
    • A. 

      DBCC LOGGING statement

    • B. 

      DBC LOG statement

    • C. 

      DBCC LOG statement

    • D. 

      None of the above.

  • 16. 
    The _____ object is used to retrieve information about all active transactions on an instance.
    • A. 

      Sys.dm_active_transactions

    • B. 

      Sys.dm_tran_transactions

    • C. 

      Sys.dm_tran_active_transactions

    • D. 

      None of the above

  • 17. 
    By default, what type of index is created for a unique constraint?
    • A. 

      Clustered index

    • B. 

      Non-Clustered index

    • C. 

      Both A and B

    • D. 

      None of the above.

  • 18. 
    To rollback a portion of a transaction, We have to define ___.
    • A. 

      Savepoints

    • B. 

      Checkpoints

    • C. 

      Transactions

    • D. 

      None of the above.

  • 19. 
    Assuming the following query is executed on 31st Dec 2011. What is the result of the below query. SELECT CONVERT(varchar(30), GETDATE(), 111) AS Expr1
    • A. 

      11/12/31

    • B. 

      31/12/11

    • C. 

      31/12/2011

    • D. 

      2011/12/31

  • 20. 
    • A. 

      Access resources in the same order whenever possible within transactions

    • B. 

      Collect and verify input data from users before opening a transaction

    • C. 

      Keep transactions long.

    • D. 

      All of the above.

    • E. 

      Only A and B.

  • 21. 
    What is the default join type used when only the keyword JOIN is specified?
    • A. 

      LEFT OUTER JOIN

    • B. 

      RIGHT OUTER JOIN

    • C. 

      SELF JOIN

    • D. 

      INNER JOIN

  • 22. 
    What is the output of the below query: SELECT CHARINDEX('DOT%', 'DOTNET') AS 'CharIndex'
    • A. 

      1

    • B. 

      0

    • C. 

      2

    • D. 

      -1

  • 23. 
    Which of the following is correct?
    • A. 

      The DELETE statement logs information on each row deleted.

    • B. 

      The TRUNCATE TABLE statement logs information on each row deleted.

    • C. 

      The TRUNCATE TABLE statement executes fast and requires fewer resources on the server.

    • D. 

      All of the above.

    • E. 

      Only A and C.

  • 24. 
    • A. 

      Returns records which has values in profession column

    • B. 

      Gives an error.

    • C. 

      Does not retrieve any records even if profession column contains values.

    • D. 

      None of the above.

  • 25. 
     If the following code was run on 31st Dec 2011, What is the result of the below query: SELECT DATEDIFF(YEAR, GETDATE(), '8/8/2003') AS 'Difference'
    • A. 

      -8

    • B. 

      8

    • C. 

      0

    • D. 

      -1