Sybase Views

Sybase View is a virtual table which is defined using SELECT statement on table(s) or other view(s). A basic view can be nothing more than a way to select a subset of a table columns.

Sybase Views Syntax:

create view [owner.]view_name
	[(column_name[, column_name]...)] 
	as 
	select [distinct] select_statement
	[with check option]

Create Table:

CREATE TABLE EMP
(EMPNO INT NOT NULL,
ENAME VARCHAR(10),
JOB VARCHAR(9),
MGR NUMERIC(4),
HIREDATE DATETIME,
SAL NUMERIC(7, 2),
DEPTNO NUMERIC(2))
INSERT INTO EMP VALUES
(7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 800,  20)
INSERT INTO EMP VALUES
(7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-28', 1600,  30)
INSERT INTO EMP VALUES
(7521, 'WARD', 'SALESMAN', 7698, '1981-02-22', 1250, 30)
INSERT INTO EMP VALUES
(7566, 'JONES', 'MANAGER', 7839, '1981-04-2', 2975,  20)
INSERT INTO EMP VALUES
(7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28', 1250,  30)
INSERT INTO EMP VALUES
(7698, 'BLAKE', 'MANAGER', 7839, '1981-05-1', 2850, 30)
INSERT INTO EMP VALUES
(7782, 'CLARK', 'MANAGER', 7839, '1981-06-9', 2450,  10)
INSERT INTO EMP VALUES
(7788, 'SCOTT', 'ANALYST', 7566, '1982-12-09', 3000,  20)
INSERT INTO EMP VALUES
(7844, 'TURNER', 'SALESMAN', 7698, '1981-09-08', 1500, 30)
INSERT INTO EMP VALUES
(7876, 'ADAMS', 'CLERK', 7788, '1983-01-12', 1100,  20)
INSERT INTO EMP VALUES
(7900, 'JAMES', 'CLERK', 7698, '1981-12-03', 950,  30)
INSERT INTO EMP VALUES
(7902, 'FORD', 'ANALYST', 7566, '1981-12-03', 3000,  20)
INSERT INTO EMP VALUES
(7934, 'MILLER', 'CLERK', 7782, '1982-01-23', 1300,  10)

Create Sybase View:

1. Create view from EMPNO,ENAME,HIREDATE,DEPTNO from table EMP.

create view EMP_view
  as select EMPNO,ENAME,HIREDATE,DEPTNO from EMP

2. To check whether its view or not.

1> sp_help EMP_view
2> go
 Name             Owner      Object_type            Object_status
         Create_date
 ---------------- ---------- ---------------------- --------------------------
         --------------------------------------
 EMP_view         dbo        view                    -- none --

3. To fetch the data from view.

select * from EMP_view
go

Restrictions on views

  • We can create a view only in the current database.
  • The number of columns referenced by a view cannot exceed 1024.
  • We cannot create a view on a temporary table.
  • We cannot create a trigger or build an index on a view.
  • We cannot use readtext or writetext on text, unitext, or image columns in views.
  • We cannot include order by, compute clauses, or the keyword into in the select statements that define views.
  • We cannot update, insert, or delete from a view with select statements that include the union operator.