How Rename Database Name ,Table Name Through Query in SQL Server 2005 and PostgreSQL and Database view in SQL Server 2005.

Views

1. A view is an Virtual Table.

2. It does not contain any data directly, it is a set of query that are applied to one or more tables as object.

3 .It can be used for retrieving data, as well as updating or deleting rows.

4. The results of using a view are not permanently stored in the database.

5. A view serves as a security mechanism.

6. This ensures that users are able to retrieve and modify only the data seen by them.

7.  A view also serves as a mechanism to simplify query execution.

8. Complex queries can be stored in the form as a view, and data from the view can be extracted using simple 

9.Views display only those data which are mentioned in the query, so it shows only data which is returned by the query that is defined at the time of creation of the View. 

Advantages of views

1.Security 2.Query Simplicity 3.Structural simplicity 4.Consistency 5.Data Integrity

A. View the data without storing the data into the object.

B. Restict the view of a table i.e. can hide some of columns in the tables.

C. Join two or more tables and show it as one object to user.

D. Restict the access of a table so that nobody can insert the rows into the table.

Disadvantages of views

1.Performance 2.Update restrictions

A. Can not use DML operations on this.

B. When table is dropped view becomes inactive.. it depends on the table objects.

C. It is an object, so it occupies space.

Different types of Views

In SQL Server we have two types of views.

System Defined Views:
A. Information Schema View
B. Catalog View
C. Dynamic Management View
1.Server-scoped Dynamic Management View
2.Database-scoped Dynamic Management View
User Defined Views
1. Simple View
2. Complex View

System defined Views are predefined Views that already exist in the Master database of Sql Server.

These are used to display information of a database, like as tables and columns. This type of view starts with INFORMATION_SCHEMA and after this view name.

SELECT * FROM INFORMATION_SCHEMA.COLUMNS

where TABLE_NAME='Employees'

Catalog Views were introduced with SQL Server 2005. These are used to show database self describing information.

select * from sys.tables 

Dynamic Management Views were introduced in SQL Server 2005. These Views give the administrator information of the database about the current state of the SQL Server machine. These values help the administrator to analyze problems and tune the server for optimal performance. These are of two types

These are stored only in the Master database.

These are stored in each database.

 SELECT connection_id,session_id,client_net_address,auth_scheme

FROM sys.dm_exec_connections 

These types of view are defined by users. We have two types of user defined views.

When we create a view on a single table, it is called simple view.

When we create a view on more than one table, it is called complex view.

select * from SampleView

When we create a view on more than one table, it is called complex view.

create table P_Information

(

 EmployeeID varchar(55),

 EmployeeName varchar(55),

 Location varchar(55),

 Hobiees varchar(20)

)

Create VIEW SampleView1

As

Select e.EmployeeID, e.EmployeeName,i.Location,i.Hobiees

From Employees e INNER JOIN P_Information i

On e.EmployeeID = i. EmployeeID 

How to change Database Name and Table Name in SQL Server 2005.

Database Name:

ALTER DATABASE temp1

Modify Name = tempI ;

Table Name:

sp_rename 'temps','san';

PostgreSQL Change Table Name

alter table  empdetails.emp rename to empall