Use the * symbol in the sqlserver2000 view.

some friends may have questions about this title, * do you need to pay attention to the symbols? We don't have to answer this question first. Let's take a look at the example first.

Here, the database used isSqlserver2000Built-inNorthwindIn this way, you can test it on your own. First, create two views. The View Script is as follows:

-- View vcustomersa
Create   View Vcustomersa
As
Select Customerid, companyName, contactname, contacttitle,
Address, city, region, postalcode, country, phone, fax
From DBO. MERs
Go
-- View vcustomersb
Create   View Vcustomersb
As
Select   *   From Vcustomersa
Go

Then, use these two views to query the customerIDIsAlfkiThe query statement is as follows:

Select   *   From Vcustomersa Where Customerid =   ' Alfki '
Select   *   From Vcustomersb Where Customerid =   ' Alfki '

The query result is as follows:

Everything is normal. At this time, the demand has changed and we need to change it.VcustomersaThe changed script is as follows: (to illustrate the problem, we justCompanyNameAndContactnameSwap positions)

-- Modified view vcustomersa
Alter   View Vcustomersa
As
Select Customerid, contactname, companyName, contacttitle,
Address, city, region, postalcode, country, phone, fax
From DBO. MERs
Go

At this time, when we use the view againVcustomersbQuery customersIDIsAlfkiThe error has come quietly. Have you noticed it? Let's take a look at the query results of these two views. The query statement is as follows:

Select   *   From Vcustomersa Where Customerid =   ' Alfki '
Select   *   From Vcustomersb Where Customerid =   ' Alfki '

The query result is as follows:

Have you noticed any data exceptions? Use View Vcustomersb The query result is incorrect, CompanyName The information displayed is: Maria Anders, In View Vcustomersa The query result is CompanyName Yes: Alfreds futterkiste . We just Vcustomersa The positions of the two fields are swapped. Vcustomersb Data misplacement occurs when querying data. Why?

With this question, let's take a look at what is a view? In SQL Server2000 The help document describes the view as follows: "A view is a virtual table whose content is defined by the query. It is the same as a real table, A view contains a series of columns and row data with names. However, a view does not exist in the database as a stored data value set. Rows and columns are used to define the referenced tables for View query and dynamically generate the tables when the view is referenced ." Through this definition, we can see that the view is a virtual table, which only includes the definition script of the view, and the queried content is dynamically generated. After a view is created, the View Script is saved to the system table of the current database. Syscomments We can use the stored procedure provided by the system: Sp_helptext Query the view definition script. In terms of definition, it seems that we cannot get the answer we want. SQL Server2000 How to Implement the view, let's first solve the current problem (I mentioned above ). Some friends may already know how to solve the problem. Vcustomersb (In fact, you only need Create Change Alter Run the following script:

-- Run the vcustomersb definition script again.
Alter   View Vcustomersb
As
Select   *   From Vcustomersa
Go

In addition to this methodSqlserver2000It also provides an extended storage processSp_refreshviewTo help us do this, the script called is as follows:

-- Refresh the metadata of a specified view
Exec Sp_refreshview ' Vcustomersb '

I personally know the two methods. I don't know. You have other methods. If you have any, share them with me.

Sp_refreshviewFunction Description: "refresh the metadata of the specified view. Due to changes to the basic object on which the view depends, the persistent metadata of the view will expire ." BecauseSp_refreshviewOfCodeIt is encapsulated (not publicly available), so we cannot see its internal implementation. But after reading the description of this stored procedure, do you have a new understanding of the view? Based on the evidence, I guess the View works as follows:

From here, we can see that when we use a view to query data, we actually use the view metadata for query. When the View dependent object changes, the metadata of a view needs to be updated, so that the use of the view does not violate our will.

after knowing the cause of the problem, when we modify a script for a table or view, we need to update the view that depends on this object, otherwise unexpected errors will occur. How can I find objects that depend on this object (including views, triggers, and stored procedures? sqlserver2000 in the system table of the database sysdepends records These dependencies, therefore, you can query the table to obtain the information you want. However, you can use the stored procedure provided by the system: sp_depends to obtain the dependent object of the object (the first table returned) and the object dependent on this object (the second table returned). The script is as follows:

--Query the dependent objects of vcustomersa and the objects dependent on vcustomersa.
ExecSp_depends'Vcustomersa'

The query result is as follows:

Note:Sp_dependsThe code is public. If you are interested, you can take a look at its implementation process.

At this point, you should understand that when you update your table or view, you also need to refresh the view metadata dependent on these objects, that is, you need to call Sp_refreshview To refresh the View dependent on this object. However, when you query an object set that depends on a table or view, note that after you update a table or view, the dependencies created previously depend on the table or view will be lost (the object set on which the table or view you update is dependent will not be lost). In my previous example, Vcustomersb Dependent on Vcustomersa When we modify Vcustomersa Later, Vcustomersb And Vcustomersa The dependency between them will be lost Vcustomersa Dependent MERs The dependency is not lost (the dependency is created when the object is created or updated, and the previous dependency is deleted when the object is updated ). (Call Sp_depends You can see this subtle change)

I hope that after reading this article, you will be more comfortable using the view to avoid errors. If there are any mistakes in this article, you are welcome to criticize them!