Skip to main content

Altering, Dropping and Renaming Views in SQL Server

In addition to creating view in sql server, database developer also need to manage them. Management of a view includes altering, dropping, or renaming described below.

Altering views

If you define a view with a SELECT * statement and then alter the structure of the underlying tables by adding columns, the new columns do not appear in the view. Similarly, when you select all the columns in a CREATE VIEW statement, the columns list is interpreted only when you first create the view. To add new columns in the view, you must alter the view.

You can modify a view without dropping it. This ensures that permissions on the view are not lost. You can modify a view without affecting its dependent objects. To modify a view, you need to use the ALTER VIEW statement. The syntax of the ALTER VIEW statement is:

ALTER VIEW view_name [ (column_name) ]
[WITH ENCRYPTION]
AS select_statement
[WITH CHECK OPTION]
Where,

  • View_name specifies the view to be altered.
  • Column_name specifies the name of the column(s) to be used in a view.
  • WITH ENCRYPTION option enerypts the text of the view in the syscomments view.
  • AS specifies the action to be performed by the view.
  • Select_statement specifies the SELECT statement that defines a view
  • WITH CHECK OPTION forces the data modification statements to follow the criteria given in the SELECT statement.

For example, you created a view to retrieve selected data from the Employee and EmployeeDepartmentHistory tables. You need to alter the view definition by including the LoginID attribute from the Employee table.

To modify the definition, you can write the following statement:

ALTER VIEW vwEmployeeDepData
AS
SELECT e.EmployeeID, LoginID, MaritalStatus, DepartmentID
FROM HumanResources.Employee e JOIN
HumanResources.EmployeeDepartmentHistory d
ON e.EmployeeID = d.employeeID

The preceding code alters the view definition by including the LoginID attribute from the Employee Table.

Dropping Views

You need to drop a view when it is no longer required. You can drop a view from a database by using the DROP VIEW statement. When a view is dropped, it has no effect on the underlying table(s). Dropping a view removes its definition and all the permissions assigned to it.

Further, if you query any view that references a dropped table, you receive an error message. Dropping a table that references a view does not drop the view automatically. You have to use the DROP VIEW statement explicitly.
The syntax of the DROP VIEW statement is:  DROP VIEW view_name

For example, you can use the following statement to remove the vwEmployeeDepData view:
DROP VIEW vwEmployeeDepData

The preceding statement will drop the vwEmployeeDepData view from the database.

You can drop multiple views with a single DROP VIEW statement. The names of the view that need to be dropped are separated by commas in the DROP VIEW statement

Renaming Views

At times, you might need to change the name of a view. You can rename a view without dropping it. This ensures that permissions on the view are not lost. A view can be renamed by using the sp_rename system stored procedure.
The syntax of the sp_rename procedure is:  Sp_rename old_viewname, new_viewname
Where,

  • Old_viewname is the view that needs to be renamed.
  • New_viewname is the new name of the view.

For example, you can use the following statement to rename the vwSal view:
Sp_rename vwSal, vwSalary

The preceding command renames the vwSal view as vwSalary.

While renaming views, you must ensure the following:

  • The view must be in the current database.
  • The new name for the view must follow the rules for identifiers.
  • The view can only be renamed by its owner.
  • The owner of the database can also rename the view.

Comments

Popular posts from this blog

difference between structure and union in C Language

In c language article we will see the difference between union and structure. Both are the user define datatype in c language. See the table which is mentioned below: ASP.NET Video Tutorial Series Structure Union1.The keywordstruct is used to define a structure 1. The keyword union is used to define a union. 2. When a variable is associated with a structure, the compiler allocates the memory for each member. The size of structure is greater than or equal to the sum ofsizes of its members. The smaller members may end with unused slack bytes. 2. When a variable is associated with a union, thecompiler allocates thememory by considering the size of the largest memory. So, size of union is equal to the size of largest member. 3. Each member within a structure is assigned unique storage area of location. 3. Memory allocated is shared by individual members of union. 4. The address of each member will be in ascending order This indicates that memory for each member will start at different offset v…

Difference between Linear search and Binary Search in c language

SQL Video Channel : Download all SQL Video



Binary Search Linear Search Works only on sorted items. such as  1,2,3,4,5,6  etc
Works on sorted as well as unsorted items. 12,4,5,3,2,1 etc Very efficient if the items are sorted Very efficient if the items are less and present in the beginning of the list. such as Suppose your list items are : 12,3,4,5,1 and you want to search 12 number then you get beginning in the list. Works well with arrays and not on linked lists. Works with arrays and linked lists.
Number of comparisons are less More number of comparisons are required if the items are present in the later part of the array or its elements are more.

Memory representation of Linked List Data Structures in C Language

Memory representation of Linked List

             In memory the linked list is stored in scattered cells (locations).The memory for each node is allocated dynamically means as and when required. So the Linked List can increase as per the user wish and the size is not fixed, it can vary.

               Suppose first node of linked list is allocated with an address 1008. Its graphical representation looks like the figure shown below:


      Suppose next node is allocated at an address 506, so the list becomes,



  Suppose next node is allocated with an address with an address 10,s the list become,


The other way to represent the linked list is as shown below:




 In the above representation the data stored in the linked list is “INDIA”, the information part of each node contains one character. The external pointer root points to first node’s address 1005. The link part of the node containing information I contains 1007, the address of next node. The last node …