Sql Server Interview Questions and Answers (1758) - Page 63

Latest and authentic Interview questions. You can also post an interview question and win monthly prizes as well as gain community credit points.

1758 records found.

Post |

Interview Experiences |

Interview FAQs |

Online Interviews |

Exclusive Questions

Get 650+ Questpond's Interview videos on discount

As part of a new enterprise project, you're designing a new table to store financial transactions. This table could eventually store millions of rows and so storage space is very important. One of the columns in the table will store either a 1 or 0 value. Which data type would be most appropriate?

NOTE: This is objective type question, Please click question title for correct answer.

DML triggers are used to enforce business rules when data is modified and to extend the integrity checking logic of Microsoft SQL Server constraints, defaults, and rules. Which of the following is FALSE regarding DML triggers

NOTE: This is objective type question, Please click question title for correct answer.

Create a computed column that returns the sum total of the ItemsInStore and ItemsInWarehouse values for each row. The new column is expected to be queried heavily, and you need to be able to index the column. Which Transact-SQL statement should you use?

NOTE: This is objective type question, Please click question title for correct answer.

You administer a Microsoft SQL Server 2012 server. You plan to deploy new features to an application. You need to evaluate existing and potential clustered and non-clustered indexes that will improve performance. What should you do?

NOTE: This is objective type question, Please click question title for correct answer.

You administer a Microsoft SQL Server 2012 database named ContosoDb. The database contains a table named Suppliers and a column named IsActive in the Purchases schema. You create a new user named ContosoUser in ContosoDb. ContosoUser has no permissions to the Suppliers table. You need to ensure that ContosoUser can delete rows that are not active from Suppliers. You also need to grant ContosoUser only the minimum required permissions. Which Transact- SQL statement should you use?

NOTE: This is objective type question, Please click question title for correct answer.

You use Microsoft SQL Server 2012 to develop a database application. You need to implement a computed column that references a lookup table by using an INNER JOIN against another table. What should you do?

NOTE: This is objective type question, Please click question title for correct answer.

You are a database developer at an independent software vendor. You create stored procedures that contain proprietary code. You need to protect the code from being viewed by your customers. Which stored procedure option should you use?

NOTE: This is objective type question, Please click question title for correct answer.

You are inserting some values into Table but your Insert Statements Fails.So what may be the reasons?

Some of the most common errors which occur while executing INSERT statement:-
A).There is a mismatch between the number of columns and the number of values
B).If the datatype of an inserted value is a mismatch with the column of the target table
C).There is a size mismatch between a value and its target column.
D).When a column is defines as not NULL and the insertion value is NULL.

When does Delete statement fails?

Mostly Delete statement fails:-
When there is a violation of Foreign key meaning that idf we delete a parent key but it's references are present on child table.
When there is written wrong statement as Delete * from tablename.
When supplied column is not present.
When the column value has different datatype.

You are a database developer at an independent software vendor. You create stored procedures that contain proprietary code. You need to protect the code from being viewed by your customers. Which stored procedure option should you use?

NOTE: This is objective type question, Please click question title for correct answer.

What are the Magic Tables in Sql server?

Sql Server automatically creates and manages two temporary,memory-resident tables that are called as Deleted and Inserted tables which are popularly known as Magic Tables.
Usually these magic tables are used with the DML triggers.We Can not directly modify the data in the tables or perform ddl operation.
Primarily used to perform certain action like-

1). Extend referential integrity between tables.
2). Test for errors and take action based on the error.
3). Find the difference between the state of a table before and after data modification and take actions based on that difference.

What is the correct syntax?

NOTE: This is objective type question, Please click question title for correct answer.

Drop statement does which operation?

NOTE: This is objective type question, Please click question title for correct answer.

What type of join is needed when we want such rows that do not have matching values?

NOTE: This is objective type question, Please click question title for correct answer.

What type of join is required when we wish to return rows that do have matching values?

NOTE: This is objective type question, Please click question title for correct answer.

Which of the following statement is true concerning a stored procedure?

NOTE: This is objective type question, Please click question title for correct answer.

A Case Sql statement consists of the following?

NOTE: This is objective type question, Please click question title for correct answer.

Which of the following statements is true concerning routines and triggers?

NOTE: This is objective type question, Please click question title for correct answer.

How to get particular part of string in SQL Server?

We have to use Substring method to achieve the same as:-
For Example:-

SELECT SUBSTRING('Vishal Kumar',1,6);

Output
Vishal

SELECT SUBSTRING('Vishal Kumar',7,6);

Output
Kumar

What are the benefits of Stored procedure over inline queries?

1). Improved Performance : Database can optimize the data access plan used by procedure and cache it for subsequent reuse.

2). Security : Stored proceudre can be individually secured within the database. A client can be granted permissions to execute SP without having any permission on the underlying tables.

3). Easy to maintain : easier to modify a stored procedure than it is to change a hard-coded sql statement.

4). Reduce network traffice : Sql statement can be executed in batches rather than sending multiple requests from the client