- 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.
- 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.
- 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.
- 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.
- 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.
- Remote server access.
- The ability to issue distributed queries, updates, commands, and transactions across the enterprise.
- The ability to address diverse data sources similarly.
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.
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
There are 4 types of cursors in SQL Server and are as below.
- Forward only
- Keyset Driven
- 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.