Understanding MS SQL Server 2000 views

The benefit of the view, which is to split the table vertically, and to perform the actions on the view that are on the table, such as Insert, Update, Delete. These operations are as large as the corresponding operations on the table, but there are some differences. The columns that operate on the view are columns that belong only to the view, and they do not necessarily contain all the columns in the corresponding table, so when you perform an insert operation or an update operation, if there are columns that are not included in the view, and those columns are not NULL, A corresponding insert or update operation will fail on the view, and you cannot perform an INSERT or update operation when you use the group by, distinct, and so on view. And one thing to be aware of is that if the view contains more than a single table, performing an INSERT or update can only operate on one table in the view at a time, that is, if you have two tables A and B in the views, you need to perform two insert or update operations on the visual. Each column of table A is manipulated one at a time, and each column of table B is manipulated once.

Grammar:

Create View view_name [ColumnName,......]

As Select Statement [with CHECK OPTION] [with encryption]

An example is provided:

Use Somedatabase go– This is the same as MySQL, means to enter a database

Create VIEW someview– creates a view Someview

As

Select * from UserInfo [with encryption]

– Encrypt the view by adding with encryption after the SELECT statement, so that when other users view the view, the contents of the views are not visible.

Go

Transaction:

There are four operations in MS SQL Server for transactions, which are:

Begin Transaction, Commit, Rollback, and save Transaction.

Index:

There are four indexes, a unique index, a clustered index, a nonclustered index, and a composite index.

Table scans are typically used when large tables return most data or small tables to query for some data. Query a large table for a limited amount of data, generally using the index.

An index is actually an object that is bound to a column in a table.

Time to use indexing:

1. Columns that are frequently queried for search.

2. Columns used in the ORDER BY clause.

3. A column that is a foreign key or primary key.

4. The column has a unique column value.

Conditions that cannot be used or are unsuitable for use with indexes:

1. Columns that are rarely referenced in a query.

2. Columns with a data type of bit, text, and image cannot be indexed.

Grammar:

Create [unique][clustered| Nonclustered] Index index_name

On TableName (Column [,... n])

An example is provided:

Create UNIQUE CLUSTERED Index someindex on UserInfo (UserName)

This is the index of a unique cluster set up on the column username of the table userinfo.