Microsoft SQL Interview Questions

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