MS-SQL Server developer interview questions

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

• Use UNIQUE constraints
• Use PRIMARY KEY constraints
• Use DEFAULT and NOT NULL constraints
• Use FOREIGN KEY constraints
• Use SET constraints

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

• BLOB
• CHAR
• VARCHAR
• TEXT
• VARTEXT

• /* … */ 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

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.

• DEFAULT and NOT NULL constraints
• FOREIGN KEY constraints
• PRIMARY KEY and UNIQUE constraints
• IDENTITY columns
• CHECK constraints

• quarter
• dayofweek
• dayofyear
• weekday

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

• Table Name
• Database name
• LIMIT clause
• WHERE clause
• Column Names

• 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

• True
• False

• CHANGE TABLE
• MODIFY TABLE
• ALTER TABLE
• UPDATE TABLE

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

• 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

• Dec 31, 9999
• Jun 6, 2079
• Jan 1, 2753
• Jan 1, 2100

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

• desc myTable
• desc table myTable
• sp_columns myTable
• None of the above
• Using either option a or c

• 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

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

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

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

• INT
• SMALLINT
• TINYINT
• BIGINT
• MONEY

• Character
• Binary
• Logical
• Date
• Numeric
• All are supported

• 0
• NULL
• It will generate an error message
• Can’t be determined

• 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

CREATE TABLE table1(

column1 varchar(50),

column2 varchar(50),

column3 varchar(50),

column4 varchar(50));

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

• 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

• BINARY
• VARBINARY
• BIT
• IMAGE
• TESTAMP

• 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