Question 261 – What are the Types of VIEW?

• Standard Views – Combining data from one or more tables through a standard view. Focus on specific data and simplifying data manipulation.
• Indexed Views – It has been computed and stored. You index a view by creating a unique clustered index on it. Indexed views dramatically improve the performance of some types of queries. Indexed views work best for queries that aggregate many rows. They are not well-suited for underlying data sets that are frequently updated.
• Partitioned Views – Joins horizontally partitioned data from a set of member tables across one or more servers. This makes the data appear as if from one table. A view that joins member tables on the same instance of SQL Server is a local partitioned view.

Question 262 – How Views ensure security of data?

• Specific rows of the tables.
• Specific columns of the tables.
• Specific rows and columns of the tables.
• Rows fetched by using joins.
• Statistical summary of data in a given tables.
• Subsets of another view or a subset of views and tables.

Question 263 – List some of the common examples of views?

• A subset of rows or columns of a base table.
• A union of two or more tables.
• A join of two or more tables.
• A statistical summary of base tables.
• A subset of another view, or some combination of views and base table.

Question 264 – What are the Limitations of views?

• A view can be created only in the current database.
• The name of a view must not be the same as that of the base table.
• A view can be created only if there is a SELECT permission on its base table.
• A SELECT INTO statement cannot be used in view declaration statement.
• A trigger or an index cannot be defined on a view.
• The CREATE VIEW statement cannot be combined with other SQL statements in a single batch.

Question 265 – What are Linked Servers in SQL Server?

• A linked server configuration enables SQL Server to execute commands against OLE DB data sources on remote servers.
• It is a concept in SQL by which we can add other SQL Server to a Group and query both the Server.
• Stored Procedure sp_addlinkedserver, sp_addlinkedsrvlogin will be used add new Linked Server.
• With a linked server, you can create very clean, easy to follow, SQL statements that allow remote data to be retrieved, joined and combined with local data.

Question 266 – What are the Advantages of Linked Servers?

• Remote server access.
• The ability to issue distributed queries, updates, commands, and transactions across the enterprise.
• The ability to address diverse data sources similarly.

Question 267 – What is a Cursor in SQL Server?

It is a database object used by applications to manipulate data in a set on a row-by-row basis, instead of the typical SQL commands that operate on all the rows in the set at one time.

Question 268 – What are the steps to Create Cursor in SQL Server?

Below are the steps by step approach to create a Cursor.

• Declare cursor
• Open cursor
• Fetch row from the cursor
• Process fetched row
• Close cursor
• De-allocate cursor

Question 269 – What are the Types of Cursors in SQL Server?

There are 4 types of cursors in SQL Server and are as below.

• Static
• Dynamic
• Forward only
• Keyset Driven

Question 270 – What are the Cursor Optimization Tips available?

• Avoid using SQL Server cursors, whenever possible.
• Do not forget to close SQL Server cursor when its result set is not needed.
• Do not forget to de-allocate SQL Server cursor when the cursors are not needed.
• Reduce the number of records to process in the cursor.
• Reduce the number of columns to process in the cursor.
• Use READ ONLY cursors, whenever possible, instead of updatable cursors.
• Try avoid using insensitive, static and keyset cursors, whenever possible.
• Use FAST_FORWARD cursors, whenever possible.
• Use FORWARD_ONLY cursors, if you need updatable cursor and the FETCH NEXT will be the only used fetch option.

Disclaimer – F5debug Interview Questions & Answers Series:

You may recopy extracts from these pages (“the material”) to individual third party websites or to any intranet websites, but only if:
You acknowledge www.f5debug.net as the source of the material. Such acknowledgment should include reference to www.f5debug.net in the copy of the material and should also include “© Karthikeyan Anbarasan, www.f5debug.net “. You inform the third party that these conditions apply to him/her and that he/she must comply with them.