Database Advanced Level ~8 min read

MS-SQL Server developer interview questions

Quick Revision Summary

Here are some of the Microsoft SQL Server developer interview questions: Which of the following has the highest order of precedence? Functions and Parenthesis Multiplication, Di...

Interactive Practice Mode

Formulate your thoughts or take notes before revealing the model solution.

Model Answer & Deep-Dive Explanation

Here are some of the Microsoft SQL Server developer interview questions:

Which of the following has the highest order of precedence?

Functions and Parenthesis
Multiplication, Division and Exponents
Addition and Subtraction
Logical Operations

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

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)

Which of the following is not a valid character datatype in SQL Server?

BLOB
CHAR
VARCHAR
TEXT
VARTEXT

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

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
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

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

Which of the following are not date parts?

quarter
dayofweek
dayofyear
weekday

The IF UPDATE (column_name) parameter in a trigger definition will retu TRUE in case of an INSERT statement being executed on the triggered table:

Yes
No
It retu s TRUE only if an UPDATE query is executed
Both b and c

Which one of the following must be specified in every DELETE statement?

Table Name
Database name
LIMIT clause
WHERE clause
Column Names

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

Is this statement true or false: A cursor is a pointer that identifies a specific working row within a set

True
False

Which of the following commands is used to change the structure of table?

CHANGE TABLE
MODIFY TABLE
ALTER TABLE
UPDATE TABLE

Consider the following statements and pick the correct answer:


          
            1
            
              ceiling() - retu
              s the smallest integer greater than or equal to the specified value
            
          
        


          
            2
            
              floor() - retu
              s 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

What is the correct SQL syntax for retu ing 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’
SELECT * FROM Persons ORDER BY FirstName DESC

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

Consider the following queries:




          
            1
            
              select * from employee where department LIKE "[^F-M]%";

`select * from employee where department = "[^F-M]%";`

Select the correct option:

Query 2 will retu an error
Both the queries will retu the same set of records
Query 2 is perfectly correct
Query 2 would retu one record less than Query 1

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

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

Which of the following is not a global variable?

@@colcount
@@error
@@rowcount
@@version
All are valid global variables

Consider the following two tables:

customers( customer_id, customer_name)

branch ( branch_id, branch_name )

What will be the output if the following query is executed: Select * branch_name from customers,branch

It will retu the fields customer_id, customer_name, branch_name
It will retu the fields customer_id, customer_name, branch_id, branch_name
It will retu the fields customer_id, customer_name, branch_id, branch_name, branch_name
It will retu an empty set since the two tables do not have any common field name
It will retu an error since * is used alone for one table only

Which of the following is not a control statement?

if…else
if exists
do…while
while
begin…end

Which of the following is not a valid Numeric datatypes in SQL Server?

INT
SMALLINT
TINYINT
BIGINT
MONEY

Which of the following datatypes is not supported by SQL-Server?

Character
Binary
Logical
Date
Numeric
All are supported

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

Which of the following options is not correct about the DATEDIFF() function?

It retu s the difference between parts of two specified dates
It takes three arguments
It retu s a signed integer value equal to second date part minus first date part
It retu s a signed integer value equal to first date part minus second date part

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;

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

Which of the following is not a valid binary datatype in SQL Server?

BINARY
VARBINARY
BIT
IMAGE
TESTAMP

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

Interviewer Evaluation Points & Scoring Rubric

1 Explain trade-offs between memory, CPU, and network latency.
2 Identify failure modes, boundary conditions, and scalability bottlenecks.
3 Discuss caching strategies, data consistency models, and recovery.

Previous: DBA Interview Questions All Database Next: How do a primary key, foreign key, and unique constraint serve different purposes?

floor() - retu

`select * from employee where department = "[^F-M]%";`

MS-SQL Server developer interview questions

ceiling() - retu

select * from employee where department LIKE "[^F-M]%";

customers( customer_id, customer_name)

branch ( branch_id, branch_name )

Interviewer Evaluation Points & Scoring Rubric

Related Questions in Database

Report Question or Answer