Floating FB popout byF5debug

Interview Questions and Answers on .Net Framework, OOPS, ASP.Net, C#.Net, SQL Server, WCF Series – Part 27

 

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.

About Author: Karthikeyan Anbarasan, Microsoft MVP (Most Valuable Professional) in ASP.NET/IIS Architecture. He is the Founder and Chief Editor of the www.f5debug.net website and has authored books on Windows Phone and Business Intelligence(SSIS). He is also a Passionate Speaker and a Blogger on Microsoft Technologies.

You can Join Me On: Facebook, Twitter, Google Plus, LinkedIn

Add comment

  Country flag

biuquote
  • Comment
  • Preview
Loading

Recent Posts

Random Posts

Most Viewed

URL Shortner

Create your own short urls!!!

Site Counter

free counters

Disclaimer

This is a personal weblog. The opinions expressed here represent my own and not those of my employer. For accuracy and official reference refer to MSDN/ TechNet. I have documented my personal experience on this blog.

Protected by Copyscape Web Plagiarism Finder

Site Meter