Home

Topics on Views

 

A View as a Virtual Table

 

Introduction

A view is primarily a type. As such, it uses most of the same functionalities of its parent object(s). For example, you open a view the same way you do a table from the Object Explorer. If you just want to see the records, you can right-click the view and click Select Top 1000 Rows. As we will see later, unlike tables, not all views allow data entry. For those that do, you can right-click the view and click Edit Top 200 Rows.

Once a view is opened whether in a Query Editor or in a View Designer, records navigation is performed the same way for a table: using keys or the mouse.

Practical LearningPractical Learning: Introducing Topics on Views

  1. Open the Lambda Square Apartments (Lambda Square Apartments) file. Select all its contents and copy to the clipboard (Click inside the document, press Ctrl + A  then press Ctrl + C)
  2. Launch Microsoft SQL Server and click Connect
  3. On the Standard toolbar, click New Query button New Query
  4. Paste the code you had copied (right-click inside the Query Editor and click Paste)
  5. To execute, right-click inside the Query Editor and click Execute
  6. In the Object Explorer, expand Databases, expand LambdaSquareApartments1
  7. Click the + button of Database Diagrams
  8. A message box will inform you that this database doesn't have a diagram.
    Read the message and click OK
  9.  Right-click Database Diagrams and click New Database Diagram
  10. In the Add Table dialog box, click Add and keep clicking it until all tables have been added
  11. Click Close.
    Based on how we created the database and its objects, the relationships have been established:
     
    Lambda Square Apartments
  12. Close the diagram window
  13. When asked whether you want to save it, click Yes
  14. Set its name to dgmLambdaSquareApartments
  15. Click OK

Views and Expressions

Although both use expressions, one of the fundamental differences of tables and views is in the way each deals with expressions. In a table, you can create an expression that would combine either one or more columns of the table and one ore more external constants, or the same columns of the same table. Here is an example:

USE rosh;
GO
CREATE TABLE Registration.Staff
(
	TeacherID int identity(1000, 1),
	FirstName nvarchar(20),
	LastName nvarchar(20) not null,
	YearlySalary money,
	FullName AS LastName + N', ' + FirstName,
	Constraint PK_Teachers Primary Key(TeacherID)
);
GO
INSERT INTO Registration.Teachers(FirstName, LastName, YearlySalary)
VALUES(N'Alex', N'Kingston', $42000),
      (N'Frank', N'Moons', $45000),
      (N'Justin', N'Simms', $38000),
      (N'Jeanne', N'Manson', $48000);
GO
SELECT * FROM Registration.Teachers;
GO

Among the differences is the way the expression is created for each. In a view:

  • An expression is also created by combining columns with other columns or columns with constants
  • The expression is a member of a SELECT statement
  • The AS and the name of the expression are written after the expression

If you decide to create a record using a view, you are not allowed to specify a value for the expression.

Practical LearningPractical Learning: Creating a View

  1. Click the Query Editor and press Ctrl + A
  2. To create a view that uses an expression, type the following code:
    USE LambdaSquareApartments1;
    GO
    CREATE VIEW Personnel.StaffMembers
    AS
        SELECT EmployeeNumber,
    	   LastName + N', ' + FirstName AS FullName,
               Title
        FROM Personnel.Employees;
    GO
  3. To execute, press F5
  4. Click the Query Editor and press Ctrl + A
  5. To test the view, type the following:
    SELECT * FROM Personnel.StaffMembers;
    GO
  6. To execute, press F5
     

    Views and Expressions

Views and Functions

To create more complex or advanced views, you can involve functions. As always, probably the easiest functions to use are those built-in. 

If there is no built-in function that performs the operation you want, you can create your own.

Practical LearningPractical Learning: Using a Function in a View

  1. Click the Query Editor and press Ctrl + A
  2. To create a function, type the following code:
    USE LambdaSquareApartments1;
    GO
    CREATE FUNCTION Personnel.GetFullName
    (
    	@FName nvarchar(20),
    	@LName nvarchar(20)
    )
    RETURNS nvarchar(41)
    AS
    BEGIN
    	RETURN @LName + N', ' + @FName;
    END
    GO
  3. To execute, press F5
  4. Click the Query Editor and press Ctrl + A
  5. To replace the view you had created and call your function from the view, type the following code:
    DROP VIEW Personnel.StaffMembers;
    GO
    
    CREATE VIEW Personnel.StaffMembers
    AS
    SELECT Personnel.GetFullName(FirstName, LastName) AS [Full Name]
    FROM   Personnel.Employees;
    GO
  6. To execute, press F5
  7. To test the view, type the following:
    SELECT * FROM Personnel.StaffMembers;
    GO
  8. To execute, press F5

Views and Functions

Views and Data Analysis

 

Introduction

The primary goal of a view is to hold a query that can be used over and over again. For this reason, a view is created from a SELECT statement.

Selecting Fields

When creating a view, you can add as many columns as you want as long as those columns were already created in the table that holds the original records. As done for data analysis of a table, when you execute a view, you can select just one column from it.

Practical LearningPractical Learning: Selecting Fields From a View

  1. Click the Query Editor and press Ctrl + A
  2. To change (alter) the view you previously created, type the following code:
    USE LambdaSquareApartments1;
    GO
    ALTER VIEW Personnel.StaffMembers
    AS
        SELECT EmployeeNumber,
    	   LastName + N', ' + FirstName AS FullName,
               Title
        FROM Personnel.Employees;
    GO
  3. To execute, press F5
  4. Click the Query Editor and press Ctrl + A
  5. To select fields from a view, type the following:
    USE LambdaSquareApartments1;
    GO
    
    SELECT EmployeeNumber,
           FullName
    FROM Personnel.StaffMembers;
    GO
  6. To execute, press F5

Views and Data Analysis

Introduction to Selecting Records in a View

By default, when you execute a view, you would get all records. If you want, you can use TOP to specify a percentage of, or a number of, first records to select. Here is an example:

USE LambdaSquareApartments1;
GO
SELECT TOP 4
       EmployeeNumber,
       FullName
FROM Personnel.StaffMembers;
GO

This would produce:

Selecting Records

Besides adding columns to a view, you can set a condition to restrict the resulting records. Of course, a condition is set using WHERE. To visually set a condition, either start a view by right-clicking Views and New View in the Object Explorer or right-click it and click Design. In the Criteria pane, click the box at the intersection of the column and Filter, and type the conditional value.

To programmatically set a condition:

  • If you are programmatically creating a new view, add a WHERE condition. Here is an example:
    CREATE VIEW Registration.Boys
    AS
    SELECT FirstName,
           LastName,
           Gender,
           ParentsNames
    FROM Registration.Students
    WHERE Gender = N'male';
    GO
  • If the view exists already, you can still ALTER it and add or modify the condition. Here is an example:
    CREATE VIEW FemaleStudents
    AS
    SELECT FirstName,
           LastName,
           ParentsNames
    FROM Registration.Students
    WHERE Gender = N'female';
    GO

When executing a view that has a condition, you can simply apply the SELECT keyword to the name of the view. Here is an example:

SELECT * FROM FemaleStudents;
GO

This would produce:

Views and Data Analysis

Notice the number of records.

Just as done for tables, when SELECTing records from a view, whether it already has a condition or not, you can specify a condition to restrict the records it produces. Here is an example:

SELECT * FROM FemaleStudents
WHERE ParentsNames IS NOT NULL;
GO

This would produce:

Views and Data Analysis

Notice a different number of records.

All the rules we reviewed for data analysis on tables are also available on views:

  • Creating a view that includes records with null values on a certain field. Here is an example:
    CREATE VIEW Registration.UnknownParents
    AS
    SELECT FirstName,
           LastName,
           Gender,
           ParentsNames
    FROM Registration.Students
    WHERE ParentsNames IS NULL;
    GO
  • Creating a view that includes a condition tied to a column but omit that column in the result. Here is an example:
    CREATE VIEW Registration.Girls
    AS
    SELECT FirstName,
           LastName,
           DateOfBirth,
           ParentsNames
    FROM Registration.Students
    WHERE Gender = N'female';
    GO
  • Creating a view that has a condition that compares strings based on a certain field. Here is an example:
    CREATE VIEW Registration.StudentsInSilverSpring
    AS
    SELECT FirstName,
           LastName,
           Gender,
           ParentsNames
    FROM Registration.Students
    WHERE City = N'silver spring';
    GO
  • Creating a view that uses a pattern in the condition. Here is an example:
    USE Exercise;
    GO
    CREATE VIEW LastNamesThatIncludeAN
    AS
    SELECT [First Name],[Last Name], Salary
    FROM Employees
    WHERE [Last Name] LIKE N'%an%';
    GO
  • Creating a view that uses a condition based on a number-based field. Here is an example:
    USE Exercise;
    GO
    CREATE VIEW Personnel.EmployeesWhoEarnMoreThan15Dollars
    AS
    SELECT [Last Name], [First Name], Salary
    FROM Personnel.Employees
    WHERE Salary !< 15.00;
    GO
  • Creating a view that includes a condition based on a Boolean field. Here is an example:
    CREATE VIEW Registration.StudentsInASingleParentHome
    AS
    SELECT FirstName,
           LastName,
           DateOfBirth,
           EmergencyName,
           EmergencyPhone
    FROM Registration.Students
    WHERE SingleParentHome = 1;
    GO
  • Creating a view that uses a condition based on a date/time-based field. Here is an example:
    CREATE VIEW Registration.StudentsBornIn1995
    AS
    SELECT FirstName,
           LastName,
           DateOfBirth,
           EmergencyName,
           EmergencyPhone
    FROM Registration.Students
    WHERE DateOfBirth >= N'01/01/1995';
    GO
  • Creating a view that combines conditions. Here is an example:
    CREATE VIEW Registration.EmergencyInformation
    AS
    SELECT FirstName,
           LastName,
           Gender,
           DateOfBirth
    FROM Registration.Students
    WHERE (EmergencyName IS NOT NULL) AND (EmergencyPhone IS NOT NULL);
    GO
  • Creating a view that includes a BETWEEN or an IN operation
  • Creating a view that negates any of the above conditions

You cannot specify an option to sort records in a view unless the SELECT statement includes a TOP.

Practical LearningPractical Learning: Adding a Condition to a View

  1. In the Object Explorer, under LambdaSquareApartments1, right-click Views and clicand click New View...
  2. In the Add Table dialog box, double-click Units (Presentation)
  3. Click Close
  4. In the Properties window, click Schema, then click the arrow of its combo box and select Rentals
  5. In the Diagram pane, click the check boxes of UnitNumber, Bedrooms, Bathrooms, MonthlyRate, and OccupancyStatus
     

    New View

  6. In the Criteria pane, click the box at the intersection of OccupancyStatus and Filter
  7. Type Available
  8. To see the result, on the main menu, click Query Designer -> Execute SQL
     

    Introducing Topics on Views

  9. Since we already know that all records in this view have an Available value, there is no reason to have that field in the view.
    In the Criteria pane, click the check box at the intersection of OccupancyStatus and Output to remove that check box
  10. To see the result, on the View Designer toolbar, click the Execute SQL button Execute SQL
     

    Introducing Topics on Views

  11. To close the view, click its system Close button Close
  12. When asked whether you want to save the items, click Yes
  13. In the Choose Name dialog box, type AvailableUnits
  14. Click OK

Selecting Distinct Records in a View

Consider the following view:

USE Exercise;
GO
CREATE TABLE Personnel.Employees(EmployeeNumber nchar(10), [First Name] nvarchar(20),
	[Last Name] nvarchar(20), Salary money, [Full Time?] bit);
GO
INSERT INTO Personnel.Employees
VALUES(N'29730', N'Philippe', N'Horsford', 20.05, 1),
      (N'28084', N'Joan', N'Shepherd', 12.72, NULL),
      (N'44179', NULL, N'Shepherd', 10.59, 1),
      (N'27924', N'Gregory', N'Hope', 12.85, 1),
      (N'79272', N'Joshua', N'Anderson', 18.26, 2),
      (N'22803', N'Gregory', N'Swanson', 15.95, NULL),
      (N'39742', NULL, N'Anders', 8.88, 2),
      (N'83084', N'Josephine', N'Anderson', 20.02, 1),
      (N'51508', N'James', N'Anders', 18.26, 1),
      (N'92485', N'John', N'Anderson', 12.49, NULL);
GO

CREATE VIEW Personnel.Contractors
AS
    SELECT [First Name], [Last Name], Salary
    FROM Personnel.Employees;
GO

We already know how to select records from a view. Here is an example:

SELECT [Last Name] FROM Personnel.Contractors;
GO

This would produce:

Views and Data Analysis

Notice the number of records. If you have records that have a repeating value in a view, when selecting those records, you can ask the database engine to select them distinctively. This is done by using the DISTINCT. Here is an example:

SELECT DISTINCT [Last Name] FROM Personnel.Contractors;
GO

This would produce:

Views and Data Analysis

 
 
 

Views and Joins

 

Introduction

Asides from their similarities and differences, tables and views have advantages and disadvantages each. One of the disadvantages of performing data analysis on a table is that, once you close the table, you lose the whole SQL statement you have created or written. Imagine the statement was complex or it involved many tables, and you lose it. Next time, you would have to re-create the statement. This is where one of the biggest advantages of a view is revealed. A view makes it possible to create a SELECT statement, save it, and "play" (execute) it as often as necessary. Another obvious advantages of a view is that it can be modified as many times as necessary, saved, and re-used to produced the new results.

A View With Many Tables

As seen in our introduction to joins, you can create a view that involves more than one table or more than one view. If you are starting a view, on the Add Table dialog box, click each table and click Add or double-click the desired tables. Then click OK.

After selecting the table, the design is done the same way we saw when studying joins:

  • As seen for the joins, if you are using more than one table and they are not (yet) related, you can drag a column from one table and drop it on another table to create a JOIN between them
  • To select the columns, you can use any of the sections or panes: The Diagram pane, the Criteria pane, or the SQL pane

Practical LearningPractical Learning: Creating Views With Joins

  1. In the Object Explorer, under LambdaSquare2, right-click Views and click New View...
  2. In the Add Table dialog box, double-click Employees (Personnel), Registrations (Rentals), and Tenants (Rentals)
  3. Click Close
  4. In the Properties window, click Schema, then click the arrow of its combo box and select Rentals
  5. In the Criteria pane, click the first box under Column and type LastName + N', ' + FirstName
  6. Press Tab and type Processed By
  7. In the Registrations (Rentals) table, click the check box of RegistrationDate
  8. In the Tenants (Rentals) table, click the check box of TenantName
  9. In the Registrations (Rentals) table, click the check boxes of UnitNumber and RentStartDate
     
    Views With Joins
  10. Click an empty area in the View Designer
  11. Close the view
  12. When asked whether you want to save, click Yes
  13. Set the name to RegistrationsSummary
  14. Click OK
  15. To execute one of the views, in the Object Explorer, right-click Rentals.RegistrationsSummary and click Select Top 1000 rows
  16. Close the view
  17. Still in the Object Explorer, right-click Rentals.RegistrationsSummary and click Select Top 1000 rows
  18. Close the Query Editor
  19. In the Object Explorer, under LambdaSquareApartments1, right-click Views and click New View...
  20. In the Add Table dialog box, double-click Units (Presentation), Tenants (Rentals), Registrations (Rentals), Employees (Personnel), and Payments (Rentals)
  21. Click Close
  22. In the Properties window, click Schema, then click the arrow of its combo box and select Rentals
  23. In the Payments (Rentals) table, click the check boxes of ReceiptNumber and PaymentDate
  24. In the Employees (Personnel) list, click the check box of EmployeeName
  25. In the Units (Presentation) table, click the check box of UnitNumber
  26. In the Tenants (Rentals) table, click the check box of TenantName
  27. In the Payments (Rentals) table, click the check box of AmountPaid
     
    Views With Joins
  28. To save the view, on the View Designer toolbar, click the Save button Save
  29. Set the name to PaymentsSummary
  30. Click OK
  31. Close the view
  32. In the Object Explorer, right-click Rentals.PaymentsSummary and click Select Top 1000 rows
  33. Close the view
  34. In the Object Explorer, under LambdaSquareApartments1, right-click Views and click New View...
  35. In the Add Table dialog box, double-click Tenants (Rentals) and Registrations (Rentals)
  36. Click Close
  37. Click an empty area in the View Designer
  38. In the Properties window, click Schema, then click the arrow of its combo box and select Rentals
  39. In the Registrations (Rentals) table, click the check box of UnitNumber
  40. In the Tenants (Rentals) table, click the check box of TenantName
  41. Close the view
  42. When asked whether you want to save, click Yes
  43. Set the name to UnitsAllocated
  44. Click OK

Views in Joins and Data Analysis

After adding tables to a view, you can set a criterion by which the records would be selected and kept. If you are working visually, you must first select a column in the desired table or view, then use its corresponding box in the Filter column of the Criteria pane. Of course, you can add manually write code in the SQL pane.

If you are writing code, you can also add a WHERE condition to your SELECT statement using the field(s) of your choice and/or a condition of your choice.

Practical LearningPractical Learning: Adding Conditions to Views and Joins

  1. In the Object Explorer, under LambdaSquare2, right-click Views and click New View...
  2. In the Add Table dialog box, double-click Units (Presentation), Tenants (Rentals), Registrations (Rentals), Employees (Personnel), and Payments (Rentals)
  3. Click Close
  4. In the Payments (Rentals) table, click the check boxes of PaymentID and DatePaid
  5. In the Criteria pane, click the first empty box under Column and type LastName + N', ' + FirstName
  6. Press Tab and type Collected By
  7. In the Units (Presentation) table, click the check box of UnitNumber
  8. In the Tenants (Rentals) table, click the check box of FullName
  9. In the Payments (Rentals) table, click the check box of AmountPaid
  10. In the Criteria pane, click the box at the intersection of UnitNumber and Filter
  11. Type 102
     
    Views With Joins
  12. In the Properties window, click Schema, then click the arrow of its combo box and select Rentals
  13. Close the view
  14. When asked whether you want to save, click Yes
  15. Set the name to PaymentsForUnit102
  16. Click OK
  17. To execute the view, in the Object Explorer, right-click Rentals.PaymentsForUnit102 and click Select Top 1000 Rows
  18. Close the view

Data Maintenance Using a View

 

Introduction

You can use a view to perform such operations as updating records or removing records. To take care of such actions, the view must be based on only one table. Here is an example of such a view:

USE Exercise1;
GO
CREATE SCHEMA Personnel;
GO
CREATE TABLE Personnel.EmploymentStatus
(
	StatusID int identity(1, 1),
	[Status] nvarchar(30) not null,
	Constraint PK_EmploymentStatus Primary Key(StatusID)
);
GO
CREATE TABLE Personnel.Employees
(
	EmplNbr nchar(10) not null,
	FirstName nvarchar(20),
	LastName nvarchar(20),
	Salary money,
	EmplStatus int null
	Constraint FK_EmploymentStatus Foreign Key
		References Personnel.EmploymentStatus(StatusID),
	Constraint PK_Employees Primary Key(EmplNbr));
GO
INSERT Personnel.EmploymentStatus([Status])
VALUES(N'Full Time'), (N'Part Time'), (N'Contractor'), (N'Unknown');

INSERT INTO Personnel.Employees
VALUES(N'29730', N'Philippe', N'Horsford', 20.05, 1),
      (N'28084', N'Joan', N'Shepherd', 12.72, NULL),
      (N'44179', NULL, N'Shepherd', 10.59, 1),
      (N'27924', N'Gregory', N'Hope', 12.85, 1),
      (N'79272', N'Joshua', N'Anderson', 18.26, 2),
      (N'22803', N'Gregory', N'Swanson', 15.95, NULL),
      (N'39742', NULL, N'Anders', 8.95, 2),
      (N'83084', N'Josephine', N'Anderson', 20.02, 1),
      (N'51508', N'James', N'Anders', 18.26, 1),
      (N'92485', N'John', N'Anderson', 12.49, NULL);
GO

CREATE VIEW Personnel.PartTimers
AS
    SELECT FirstName, LastName, EmplStatus
    FROM   Personnel.Employees
    WHERE  EmplStatus = 2;
GO

Updating Records Using a View

The operation of updating records using a view follows the same rules as those of a table. As you may know already, you can create a view that has a condition and only the records that follow that condition would be included in the result. The above view would produce:

Updating Records Using a View

Notice the number of records. We have already seen how to update records using a table. If you use a table, after updating the records, the view would show the result. Consider the following example:

UPDATE Personnel.Employees
SET EmplStatus = 2
WHERE EmplNbr  = N'22803';
GO
SELECT * FROM Personnel.PartTimers;
GO

This would produce:

Updating Records Using a View

Notice that, this time, the view has a different number of records. In the same way, you can update records using a view. The advantage is that if the view already contains a condition, you can simply SET the desired value. Here is an example:

UPDATE Personnel.PartTimers
SET EmplStatus = 3;
GO
SELECT * FROM Personnel.PartTimers;
GO

This would produce:

Updating Records Using a View

Checking the Condition on a View Before Updating

As mentioned already, if you update records using a view, the table would be updated. Consequently, based on the criteria set in the view, the view's result may not show the record(s) that was (were) lost. If you decide to update one or more records using a view, you can ask the database engine to first check whether one or more records in the view would be lost.

To visually specify the checking, if you are creating the view, in the Properties window, expand the Update Specification field. Set the (Update Using View Rules) field to Yes. Then set the Check Option to Yes:

Checking the Condition on a View Before Updating

To programmatically apply a check, add a WITH CHECK OPTION flag before the end of the statement. The formula to follow would be:

CREATE VIEW [SchemaName.]ViewName
AS
SELECT Statement
WITH CHECK OPTION

Here is an example:

CREATE VIEW Personnel.EarnLessThanMinimumWage
AS
    SELECT EmplNbr, FirstName, LastName, Salary
    FROM   Personnel.Employees
    WHERE  Salary !> 12.50
    WITH CHECK OPTION;
GO

Checking the Condition on a View Before Updating

If the view was created already and you want to visually add the checking process to it, in the Object Explorer, right-click the view and click Design. In the Properties window, expand the Update Specification field. Set (Update Using View Rules) to Yes. Then set the Check Option to Yes. To do the same thing with code, ALTER the view and add a WITH CHECK OPTION flag at the end of the statement. The formula to follow would be:

ALTER VIEW [SchemaName.]ViewName
AS
SELECT Statement
WITH CHECK OPTION

Here is an example:

ALTER VIEW Personnel.PartTimers
AS
    SELECT EmplNbr, FirstName, LastName, EmplStatus
    FROM   Personnel.Employees
    WHERE  EmplStatus = 2
    WITH CHECK OPTION;
GO

After adding this flag, if you update one or more records using the view, if the updated record(s) is(are) under the condition specified in the view, the update will work just fine. Here is an example:

UPDATE Personnel.EarnLessThanMinimumWage
SET Salary = 12.25
WHERE EmplNbr = N'44179';
GO

Checking the Condition on a View Before Updating

On the other hand, if you call the view to update the record(s) and if at least one record that must be changed is outside the condition specified in the condition of the view, the update will be dismissed. Here is an example:

UPDATE Personnel.EarnLessThanMinimumWage
SET Salary = 14.05
WHERE EmplNbr = N'92485';
GO

Checking the Condition on a View Before Updating

If you perform the same operation using a table, it would work fine and there would not be an error.

Deleting Records Using a View

You can remove records from a database using either a table or a view. We already know how to delete records using a table. When it comes to a view, you can first create one that includes a condition that isolates one or more records.

If you delete a record using a table, a view that depends on that table would not show that record anymore. As seen for updating records, if you create a view that has a condition and you decide to delete the record(s) that follow(s) the condition(s) in that view, you can simply call DELETE on that view and all the records that view shows would be removed from the table.

Views and Indexes

All of the indexes we have created so far were using columns on a table. In reality, as you can include columns in a view, you can create an index that uses fields from a view. As you may know already, one of the advantages of a view over a table is that the former can include columns from more than one table. This makes it possible to create an index that uses columns from many tables but the columns would be retrieved a view.

When creating an index that uses columns from a view, there are rules you must follow:

  • When creating the view
    • The view must be schema bound
    • Each column included in the view should be given an alias
  • The index must be created as unique and clustered

Practical LearningPractical Learning: Creating an Index From a View

  1. Click inside the Query Editor and press Ctrl + A
  2. To create a view, type the following code (the InformationTechnologyJournal1 database must have been created):
    USE InformationTechnologyJournal1;
    GO
    
    CREATE VIEW Publishing.AffiliatedReviewers
    WITH SCHEMABINDING
    AS
        SELECT rvs.ReviewerNumber rn, 
    	   rvs.FirstName fn,
    	   rvs.LastName ln, 
    	   rvs.Citizenship cz,
    	   pa.AffiliationName an
        FROM Authorship.ReviewersAffiliations ar
        INNER JOIN Authorship.Reviewers rvs
        ON rvs.ReviewerNumber = ar.ReviewerNumber
        INNER JOIN Publishing.Affiliations pa
        ON ar.AffiliationCode = pa.AffiliationCode;
    GO
  3. To execute, on the SQL Editor toolbar, click the Execute button Execute
  4. Click inside the Query Editor and press Ctrl + A
  5. To create an index that uses columns from the view, type the following:
    USE InformationTechnologyJournal1;
    GO
    
    CREATE UNIQUE CLUSTERED INDEX IX_AffiliatedReviews
    ON Publishing.AffiliatedReviewers(rn, ln, an);
    GO
  6. To execute, on the SQL Editor toolbar, click the Execute button Execute

A Review of Transact-SQL Built-In Views

 

Introduction

To keep track of the objects in various databases, Microsoft SQL Server uses special views that hold the objects' identifiers, names, and other details. Because those views are used to present the objects, they are called catalog views. The objects in the catalog views are usually categorized by their types.

The SYS.DATABASES Catalog View

One of the most regularly used catalog view in Microsoft SQL Server is sys.databases. The view is used to keep the names of all databases in a Microsoft SQL Server server. The view is equipped with a column named name. When you create a database, its named is entered in the name field of the sys.databases view. Therefore, to find out if a database exists, you can ask the database engine to look for its name in the name column of this view. This can be done using the following formula:

IF EXISTS (
   SELECT name 
   FROM sys.databases 
   WHERE name = N'DatabaseName'
)

In the DatabaseName placeholder, you can enter the name of the database.

If the EXISTS() function returns TRUE, it means the sys.databases catalog view contains the database you were looking for. In which case you can take appropriate action(s). If the function returns FALSE, the table was not found.

The sys.databases catalog view contains many other columns that hold different types of information such as the date (and time) the database was created, a description of how the database is currently being accessed (by one person or by many users), whether the database is encrypted or not, etc.

The SYS.OBJECTS Catalog View

The sys.objects catalog view keeps track of the various objects created in databases. Examples of such objects are tables and views. To identify the objects, the sys.objects view has a column named name.This column holds the names of all objects. If an object belongs do a schema, both are represented in the appropriate formula.

Besides the name, the sys.objects view is equipped with many other columns that can provide information about an objects. The pieces of information include the type of objects, the date it was created, and the date it was last modified. The type of object can be represented with one or two letters. For example, U represents a regular table created in a database. Based on this, to find out if a table exists already, you can use the following formula:

IF OBJECT_ID('<schema_name>.<table_name>', 'U') IS NOT NULL
  Take Action(s)

In the same way, V represents a view. Therefore, to find out if a view was created already, you can use the following formula:

IF OBJECT_ID('<schema_name>.<view_name>', 'V') IS NOT NULL
  Take Action(s)
GO

P represents a regular stored procedure, SN represents a synonym, and SO represents a sequence, etc. Alternatively, a type can be represented with a string. Examples are USER_TABLE, VIEW, or SYNONYM.

The SYS.COLUMNS Catalog View

When you add a column to a table or make a column a member of a view, the database engine stores information about that column in the sys.columns catalog view. This view is equipped with many columns. One of them is named name. It holds the names of all columns. Therefore, to check the existence of a column, that is, to find out if a column has already been created, find out if the name field contains the name of that column.

Besides the name, if the column exists already, the sys.columns catalog view contains many other fields that can produce such pieces of information as the data type of the column, the length (if the column is char-based or decimal-based), the precision and scale (for decimal-based columns), and any of the characteristics we have already seen for columns (what its collation is, whether it allows null, whether its value is an expression, whether it is an identity, etc).

Besides the catalog views we have reviewed here, Microsoft SQL Server uses many others.

The SYS.INDEXES Catalog View

Transact-SQL keeps information about indexes in a catalog view named sys.indexes. As mentioned for the other catalog views, the most common field of this index is name. To assist you with checking the existence of an index, you can use the following formula:

IF EXISTS (SELECT name FROM sys.indexes
WHERE name = IndexName)
Do something here

The primary thing you need to provide in this formula is the name of the index. Once you have checked, you can take the necessary action. Here is an example:

USE Exercise;
GO

IF EXISTS (SELECT name FROM sys.indexes
		  WHERE name = N'IX_Employees')
DROP INDEX IX_Employees
ON Employees
GO
CREATE INDEX IX_Employees
ON Employees(EmployeeNumber);
GO

The sys.indexes catalog view has columns to provide more information about an index. These include the type of index (clustered, nonclustered, etc), the uniqueness, whether the index is the primary key, whether the index is disabled, etc.

 
 
   
 

Previous Copyright © 2008-2013 FunctionX Next