Microsoft SharePoint Team Blog
The official blog of the Microsoft SharePoint Product Group
Fundamentals of Creating Views

View Maintenance


The Properties of a View



In Transact-SQL, a view is considered an object. As such, it can be viewed, changed, or deleted. Like any regular object, a view has its own characteristics. To see them, you can right-click the view and click Properties. A View Properties dialog box would come up. It can give you information such as the name of the database the view belongs to, the date the view was created, etc.

Practical Learning: Accessing the Properties of a View



To create another view, type the following:
-- =============================================
-- Database: YugoNationalBank
-- View: TimeSheet
-- =============================================
USE YugoNationalBank
GO

IF object_id(N'TimeSheet', 'V') IS NOT NULL
DROP VIEW dbo.TimeSheet
GO

CREATE VIEW dbo.TimeSheet
AS
SELECT EmplNumber, StartDate,
Week1Monday, Week1Tuesday, Week1Wednesday,
Week1Thursday, Week1Friday,
Week1Saturday, Week1Sunday,
Week2Monday, Week2Tuesday, Week2Wednesday,
Week2Thursday, Week2Friday, Week2Saturday,
Week2Sunday, Notes
FROM TimeSheets;


Press F5 to execute the statement
Delete the content of the window
In the Object Explorer, under YugoNationalBank, right-click Views and click Refresh
Expand Views if necessary. Right-click TimeSheet and click Properties
Press Esc to close the Properties dialog box
Modifying a View



After a view has been created, either by you or someone else, you may find out that it has an unnecessary column, it needs a missing column, it includes unnecessary records, or some records are missing. Fortunately, you can change the structure or the code of a view. This is referred to as altering a view. You have two main options:

To visually change a view, in the Object Explorer, right-click the view and click Design. From the view window, you can add or remove the columns. You can also change any options in one of the sections of the window. After modifying the view, save it and close it
To change the code of a view, in the Object Explorer, right-click it and view Edit. After editing the view's code, you can save it
From the Object Explorer, right-click the view, position the mouse on Script View As -> ALTER To -> New Query Editor Window
The basic formula to programmatically modify a view is:

ALTER VIEW ViewName
AS
SELECT Statement
You start the alteration with the ALTER VIEW expression followed by the name of the view. After the name of the view, use the AS keyword to specify that you are ready to show the change. After the AS keyword, you can then define the view as you see fit. For example, you can create a SELECT statement that includes a modification of the existing code or a completely new statement.

In the view we created to show a list of men of a table, we included a column for the gender. This column is useless or redundant because we already know that the list includes only men. Here is an example of altering the view to remove (or rather omit) the Gender column of the Persons table:

ALTER VIEW dbo.ListOfMen
AS
SELECT dbo.Persons.FirstName, dbo.Persons.LastName
FROM dbo.Genders INNER JOIN dbo.Persons
ON dbo.Genders.GenderID = dbo.Persons.GenderID
WHERE (dbo.Genders.Gender = 'Male');
Deleting a View



Instead of modifying a view, if you find it altogether useless, you can remove it from its database. You have various options. To delete a view:

In the Object Explorer, in a database, right-click the name of the view and click Delete. You would be given the opportunity to confirm your intention or to change your mind
In the Object Explorer, right-click the view, position the mouse on Script View As -> DROP To New Query Editor Window
Open an empty query window associated with the database that has the undesired view. From the Template Explorer, in the View node, drag Drop View and drop it in the query window
The formula to programmatically delete a view is:

DROP VIEW ViewName
On the right side of the DROP VIEW expression, enter the name of the undesired view and execute the statement. You will not be warned before the interpreter deletes the view.

Using a View


Data Entry With a View



As seen so far, a view is a selected list of records from a table. As you may suspect, the easiest view is probably one created from one table. Imagine you have a table of employees and you want to create a view that lists only their names. You may create a view as follows:

CREATE VIEW dbo.EmployeesNames
AS
SELECT FirstName,
LastName,
LastName + ', ' + FirstName AS FullName FROM Persons;
GO
On such a view that is based on one table, you can perform data entry, using the view, rather than the table. To do this, you follow the same rules we reviewed in Lesson 9. Here is an example:

INSERT INTO dbo.EmployeesNames(FirstName, LastName)
VALUES('Peter', 'Justice');
If you perform data entry using a view, the data you provide would be entered on the base table; this means that the table would be updated automatically. Based on this feature, you can create a view purposely intended to update a table so that, in the view, you would include only the columns that need to be updated.

Practical Learning: Performing Data Entry Using a View



To create a function we will use, enter the following code:
--==================================================
-- Database: YugoNationalBank
-- Function: CreateTimeSheetCode
-- Purpose: This function takes an employee number
-- and the start date of a time sheet.
-- Then it creates a unique number
-- in the format 0000000000000
-- The first 5 digits represent the
-- employee number,
-- the second 4 digits represent the year,
-- the 2 digits represent the month,
-- that last 2 digits represent the day
--==================================================
USE YugoNationalBank
GO

IF OBJECT_ID (N'dbo.CreateTimeSheetCode') IS NOT NULL
DROP FUNCTION dbo.CreateTimeSheetCode
GO

CREATE FUNCTION dbo.CreateTimeSheetCode(@EmplNbr varchar(6),
@dteStart datetime)
RETURNS varchar(15)
AS
BEGIN
DECLARE @strMonth AS varchar(20);
DECLARE @strDay AS varchar(20);
DECLARE @iMonth AS int;
DECLARE @iDay AS int;
DECLARE @strTimeSheetCode varchar(20);

SET @iMonth = CONVERT(varchar(20), MONTH(@dteStart));
SET @iDay = CONVERT(varchar(20), DAY(@dteStart));

IF @iMonth < 10
SET @strMonth = CONVERT(varchar(20), YEAR(@dteStart)) +
'0' + CONVERT(varchar(20), @iMonth);
ELSE
SET @strMonth = CONVERT(varchar(20), YEAR(@dteStart)) +
CONVERT(varchar(20), @iMonth);

IF @iDay < 10
SET @strDay = @strMonth + '0' +
CONVERT(varchar(20), @iDay);
ELSE
SET @strDay = @strMonth + CONVERT(varchar(2), @iDay);

SET @strTimeSheetCode = @EmplNbr + @strDay;
RETURN @strTimeSheetCode;
END
GO


Press F5 to execute
To perform data entry using a view, enter the following code:
USE YugoNationalBank
GO
-- The following code performs data entry using a view
INSERT INTO dbo.TimeSheet
VALUES('46288', '1/1/2007',
0.00, 8.50, 9.50, 8.50, 9.00, 0.00, 0.00,
10.00, 9.50, 8.50, 10.50, 9.00, 0.00, 0.00, '');
GO
INSERT INTO dbo.TimeSheet
VALUES('66286', '1/1/2007',
0.00, 8.50, 6.50, 5.50, 6.50, 0.00, 0.00,
4.00, 6.00, 6.50, 6.00, 5.50, 0.00, 0.00, '');
GO
INSERT INTO dbo.TimeSheet
VALUES('92493', '1/1/2007',
0.00, 8.00, 9.00, 8.50, 9.50, 0.00, 0.00,
5.50, 6.50, 4.50, 6.00, 4.00, 0.00, 0.00, '');
GO
INSERT INTO dbo.TimeSheet
VALUES('27199', '1/15/2007',
6.00, 8.50, 0.00, 4.00, 6.50, 0.00, 0.00,
4.00, 0.00, 6.00, 4.00, 0.00, 0.00, 0.00, '');
GO
INSERT INTO dbo.TimeSheet
VALUES('39538', '1/15/2007',
8.00, 8.00, 6.00, 8.50, 6.00, 0.00, 0.00,
9.50, 10.50, 8.00, 8.00, 8.50, 0.00, 0.00,
'There were a few missing times in the time sheet. ' +
'They have been recorded.');
GO
INSERT INTO dbo.TimeSheet
VALUES('40550', '1/15/2007',
8.50, 8.00, 0.00, 8.50, 0.00, 0.00, 0.00,
6.00, 6.50, 6.50, 0.00, 4.00, 0.00, 0.00, '');
GO
INSERT INTO dbo.TimeSheet
VALUES('66286', '1/29/2007',
8.00, 6.50, 9.50, 8.00, 7.50, 0.00, 0.00,
10.50, 9.50, 8.50, 8.00, 10.00, 0.00, 0.00, '');
GO
INSERT INTO dbo.TimeSheet
VALUES('90026', '2/12/2007',
8.50, 6.50, 8.00, 8.00, 9.50, 0.00, 0.00,
9.50, 8.00, 8.50, 8.00, 8.00, 0.00, 0.00, '');
GO
INSERT INTO dbo.TimeSheet
VALUES('92493', '2/12/2007',
4.00, 6.50, 5.50, 8.00, 6.50, 0.00, 0.00,
8.00, 8.00, 8.00, 6.00, 8.00, 0.00, 0.00, '');
GO
-- The following code updates a table using a function
UPDATE dbo.TimeSheets
SET TimeSheetCode = dbo.CreateTimeSheetCode('46288', '1/1/2007')
WHERE (EmplNumber = '46288') AND (StartDate = '1/1/2007');
GO
UPDATE dbo.TimeSheets
SET TimeSheetCode = dbo.CreateTimeSheetCode('66286', '1/1/2007')
WHERE (EmplNumber = '66286') AND (StartDate = '1/1/2007');
GO
UPDATE dbo.TimeSheets
SET TimeSheetCode = dbo.CreateTimeSheetCode('92493', '1/1/2007')
WHERE (EmplNumber = '92493') AND (StartDate = '1/1/2007');
GO
UPDATE dbo.TimeSheets
SET TimeSheetCode = dbo.CreateTimeSheetCode('27199', '1/15/2007')
WHERE (EmplNumber = '27199') AND (StartDate = '1/15/2007');
GO
UPDATE dbo.TimeSheets
SET TimeSheetCode = dbo.CreateTimeSheetCode('39538', '1/15/2007')
WHERE (EmplNumber = '39538') AND (StartDate = '1/15/2007');
GO
UPDATE dbo.TimeSheets
SET TimeSheetCode = dbo.CreateTimeSheetCode('40550', '1/15/2007')
WHERE (EmplNumber = '40550') AND (StartDate = '1/15/2007');
GO
UPDATE dbo.TimeSheets
SET TimeSheetCode = dbo.CreateTimeSheetCode('66286', '1/29/2007')
WHERE (EmplNumber = '66286') AND (StartDate = '1/29/2007');
GO
UPDATE dbo.TimeSheets
SET TimeSheetCode = dbo.CreateTimeSheetCode('90026', '2/12/2007')
WHERE (EmplNumber = '90026') AND (StartDate = '2/12/2007');
GO
UPDATE dbo.TimeSheets
SET TimeSheetCode = dbo.CreateTimeSheetCode('92493', '2/12/2007')
WHERE (EmplNumber = '92493') AND (StartDate = '2/12/2007');
GO


Press F5 to execute
Delete the content of the window





source : http://blogs.msdn.com/sharepoint/default.aspx