Microsoft SQL Interview Questions

Posted on 11:05 PM by Bharathvn

  1. Which of the following has the highest order of precedence?
    • Functions and Parenthesis
    • Multiplication, Division and Exponents
    • Addition and Subtraction
    • Logical Operations
  2. When designing a database table, how do you avoid missing column values for non-primary key columns?
    • Use UNIQUE constraints
    • Use PRIMARY KEY constraints
    • Use DEFAULT and NOT NULL constraints
    • Use FOREIGN KEY constraints
    • Use SET constraints

  3. Which of the following is the syntax for creating an Index?
    • CREATE [UNIQUE] INDEX index_name OF tbl_name (index_columns)
    • CREATE [UNIQUE] INDEX OF tbl_name (index_columns)
    • CREATE [UNIQUE] INDEX ON tbl_name (index_columns)
    • CREATE [UNIQUE] INDEX index_name ON tbl_name (index_columns)
  4. Which of the following is not a valid character datatype in SQL Server?
    • BLOB
    • CHAR
    • VARCHAR
    • TEXT
    • VARTEXT
  5. Which of the following statements about SQL Server comments is false?
    • /* … */ are used for multiline comments
    • // is used for single line comments
    • – is used for single line comments
    • Nested comments are allowed i.e. /* comment 1 /* comment 2 */ comment 1*/
    • ‘ is used for single line comments
  6. Consider the following transaction code:

    Begin Transaction
    Update names_table set employee_name = "Ramesh" where employee_name = "Mahesh"
    Save Transaction SAVE_POINT
    Update salaries set salary=salary + 900 where employee_job = "Engineer"
    Rollback transaction
    Commit transaction

    What will be the result produced by this transaction?
    • “Ramesh” will be updated to “Mahesh”, but salaries of engineers will not be
    • updated

    • Neither “Ramesh” will be updated to “Mahesh”, nor the salary of engineers will be updated.
    • “Ramesh” will be updated to “Mahesh” and salary of engineers will also be
    • updated.

  7. Which of the following constraints can be used to enforce the uniqueness of rows in a table?
    • DEFAULT and NOT NULL constraints
    • FOREIGN KEY constraints
    • PRIMARY KEY and UNIQUE constraints
    • IDENTITY columns
    • CHECK constraints
  8. Which of the following are not date parts?
    • quarter
    • dayofweek
    • dayofyear
    • weekday
  9. The IF UPDATE (column_name) parameter in a trigger definition will return
    TRUE in case of an INSERT statement being executed on the triggered table:
    • Yes
    • No
    • It returns TRUE only if an UPDATE query is executed
    • Both b and c
  10. Which one of the following must be specified in every DELETE statement?
    • Table Name
    • Database name
    • LIMIT clause
    • WHERE clause
    • Column Names
  11. Which one of the following correctly selects rows from the table myTable that have null in column column1?
    • SELECT * FROM myTable WHERE column1 is null
    • SELECT * FROM myTable WHERE column1 = null
    • SELECT * FROM myTable WHERE column1 EQUALS null
    • SELECT * FROM myTable WHERE column1 NOT null
    • SELECT * FROM myTable WHERE column1 CONTAINS null
  12. Is this statement true or false:
    A cursor is a pointer that identifies a specific working row within a set
    • True
    • False
  13. Which of the following commands is used to change the structure of table?
    • CHANGE TABLE
    • MODIFY TABLE
    • ALTER TABLE
    • UPDATE TABLE
  14. Consider the following statements and pick the correct answer:

    1. ceiling() - returns the smallest integer greater than or equal to the specified value
    2. floor() - returns the largest integer less than or equal to the specified value
    • 1 is true and 2 is false
    • 1 is false and 2 is true
    • Both 1 and 2 are true
    • Both 1 and 2 are false
  15. What is the correct SQL syntax for returning all the columns from a table named “Persons” sorted REVERSE alphabetically by “FirstName”?
    • SELECT * FROM Persons WHERE FirstName ORDER BY FirstName DESC
    • SELECT * FROM Persons SORT REVERSE ‘FirstName’
    • c . SELECT * FROM Persons ORDER BY ‘FirstName’

    • SELECT * FROM Persons ORDER BY FirstName DESC
    • SELECT * FROM Persons ORDER BY DESC FirstName

  16. What is the maximum value that can be stored for a datetime field?
    • Dec 31, 9999
    • Jun 6, 2079
    • Jan 1, 2753
    • Jan 1, 2100
  17. Consider the following queries:
    1. select * from employee where department LIKE "[^F-M]%”;
    2. select * from employee where department = “[^F-M]%”;

    Select the correct option:
    • Query 2 will return an error
    • Both the queries will return the same set of records
    • Query 2 is perfectly correct
    • Query 2 would return one record less than Query 1
  18. How can you view the structure of a table named “myTable” in SQL Server?
    • desc myTable
    • desc table myTable
    • sp_columns myTable
    • None of the above
    • Using either option a or c
  19. What does referential integrity (also called relational integrity) prevent?
    • Loss of data from employee sabotage
    • Loss of data from any one corrupted table
    • Recursive joins
    • One-to-many or many-to-many relationships between columns in a table
    • Data redundancy
  20. Which of the following is not a global variable?
    • @@colcount
    • @@error
    • @@rowcount
    • @@version
    • All are valid global variables
  21. Consider the following two tables:

    1. customers( customer_id, customer_name)
    2. branch ( branch_id, branch_name )
    What will be the output if the following query is executed:
    Select * branch_name from customers,branch
    • It will return the fields customer_id, customer_name, branch_name
    • It will return the fields customer_id, customer_name, branch_id, branch_name
    • It will return the fields customer_id, customer_name, branch_id, branch_name, branch_name
    • It will return an empty set since the two tables do not have any common field name
    • It will return an error since * is used alone for one table only
  22. Which of the following is not a control statement?
    • if…else
    • if exists
    • do…while
    • while
    • begin…end
  23. Which of the following is not a valid Numeric datatypes in SQL Server?
    • INT
    • SMALLINT
    • TINYINT
    • BIGINT
    • MONEY
  24. Which of the following datatypes is not supported by SQL-Server?
    • Character
    • Binary
    • Logical
    • Date
    • Numeric
    • All are supported
  25. What will the output be if you try to perform arithmetic on NULL values?
    • 0
    • NULL
    • It will generate an error message
    • Can’t be determined
  26. Which of the following options is not correct about the DATEDIFF() function?
    • It returns the difference between parts of two specified dates
    • It takes three arguments
    • It returns a signed integer value equal to second date part minus first date part
    • It returns a signed integer value equal to first date part minus second date part
  27. Sample Code

    CREATE TABLE table1(
    column1 varchar(50),
    column2 varchar(50),
    column3 varchar(50),
    column4 varchar(50));

    Which one of the following is the correct syntax for adding the column named “column2a” after column2 to the table shown above?
    • ALTER TABLE table1 ADD column2a varchar(50) AFTER column2;
    • MODIFY TABLE table1 ADD column2a AFTER column2;
    • INSERT INTO table1 column2a AS varchar(50) AFTER column2;
    • ALTER TABLE table1 INSERT column2a varchar(50) AFTER column2;
    • CHANGE TABLE table1 INSERT column2a BEFORE column3;
  28. State which of the following are true
    • Views are a logical way of looking at the logical data located in the tables
    • Views are a logical way of looking at the physical data located in the tables
    • Tables are physical constructs used for storage and manipulation of data in databases
    • Tables are logical constructs used for storage and manipulation of data in databases
  29. Which of the following is not a valid binary datatype in SQL Server?
    • BINARY
    • VARBINARY
    • BIT
    • IMAGE
    • TESTAMP
  30. Which of the following is false with regards to sp_help?
    • When a procedure name is passed to sp_help, it shows the parameters
    • When a table name is passed to sp_help, it shows the structure of the table
    • When no parameter is passed, it provides a list of all objects and user-defined datatypes in a database
    • All of the above are true
    • Which of the following are false for batches (batch commands)?
      • Statements in a batch are parsed, compiled and executed as a group
      • None of the statements in the batch is executed if there are any syntax errors in the batch
      • None of the statements in the batch is executed if there are any parsing errors in the batch
      • None of the statements in the batch is executed if there are any fatal errors in the batch
    • Select the correct option:
      • Optimistic locking is a locking scheme handled by the server, whereas pessimistic locking is handled by the application developer
      • Pessimistic locking is a locking scheme handled by the server, whereas optimistic locking is handled by the application developer