SQL Server2000 review notes

I. Page and Disk Area ):
All information in SQL Server is stored on the page. Except transaction logs (which are composed of a series of records), the page is the minimum data unit used in the database, each page stores 8 KB of information. SQL Server2000 has the following types of pages:

• Data Page: used to store database data, but does not include text, ntext, and image data.
• Index page: used to store index data
• Text/image page: used to store text, ntext, and image data
• Global allocation page: information used for disk Distribution
• Page remaining space page: used to store information about the remaining space on the page
• Index allocation page: used to store information about the disk areas used by tables or indexes on a page.

A disk is a data structure consisting of eight consecutive pages. It is the smallest unit allocated space for tables and indexes in SQL 2000. That is to say, when you create a table, SQL automatically allocates space to a disk. Two disk zones are designed in SQL 2000:

• Single Disk zone: used only by a single database object. All eight pages are used for this object.
• Hybrid disk zone: it can be used together by a maximum of eight objects. A newly created table or index first allocates a page from the hybrid disk zone. When the data volume in a table or index increases to eight pages, then convert it into a single disk.

Ii. Special Database types:
1. Text and image data are expressed in the text, ntext, and image data types in SQL Server, because the data volume is usually large, therefore, unlike other types of data in a table, data is stored on a special page instead of a row or a row, only the pointer pointing to the actual storage location of the Data row is stored in the corresponding location of the Data row. However, in SQL 2000, data of the text, ntext, and image types can be stored in rows, but two conditions must be met:

• The size of the text and image data cannot exceed 7000 bytes.
• Data rows have enough space to store the data.

Specific settings: sp_tableoption [Table], 'text in row', 'on', 'off', or the upper limit is '123'
Writetext is used to modify data of this type. The updated text or image is completely overwritten. By default, the operation of the writetext statement is not recorded in the transaction log.
Syntax: writetext table. Column text_ptr (pointer to the text or image data to be modified, whose type must be binary (16) data.
Eg:
First, learn sp_dboption,

display or Change Database options. You cannot use sp_dboption On the master or tempdb database. sp_dboption is supported for backward compatibility. Use alter database to set database options.
syntax
sp_dboption [[ @ dbname = ] ' Database ']
[, [ @ optname = ] ' option_name ' ]
[, [ @ optvalue =] ' value ']
select into/builcopy option name, if this parameter is set to true, select into statements and fast large-capacity replication are allowed.

text data is not stored in the row:
exec sp_dboption 'market', 'select into/bulkcopy ', 'true'
go
declare @ ptrval binaty (16)
select @ ptrval = textptr (description) -- Get pointer
from goods
where name = 'battery'
writetext goods. description @ ptrval 'the descriotion of battery, just for test. '
store text data in a row:
exec sp_tableoption 'goods', 'text in row ', 'on'
go
begin Tran
declare @ ptrval binaty (16)
select @ ptrval = textptr (description) -- Get pointer
from goods
where name = 'battery'
writetext goods. description @ ptrval 'the descriotion of battery, just for test. '
commit
to modify some data, use updatetext

for example, the following query uses the letter Z to replace the text column of New Moon books with 88th characters (the second letter O in Moon ):
Use pubs
sp_dboption 'pubs', 'select into/bulkcopy', 'true'
declare @ ptrval varbinary (16)
select @ ptrval = textptr (pr_info)
from pub_info PR inner join publishers P
on p. pub_id = Pr. pub_id
and P. pub_name = 'new moon books '
updatetext pub_info.pr_info @ ptrval 88 1 'Z'
sp_dboption 'pubs ', 'select into/bulkcopy', 'false'
2. User-defined types are database objects, just like tables, view ...... It is generally based on the basic type, such as defining an email type, adding the default value, and checking the Rules '@'. This custom type can be used when the email field of the table is created.
Add custom types: exec sp_addtype enail, 'varchar (10) ', not null'

Iii. Forced Data Integrity Mechanism:
1. Primary Key constraints. The primary key can force Object Integrity.
2. Foreign key constraint. SQL 2000 adds the cascade modification and cascade deletion functions. The value of a column as a foreign key can be null.
3. uniqueness constraints: A table can only define one primary key constraint, but multiple uniqueness constraints can be defined; databases on columns that define uniqueness constraints can be empty. Can be referenced by foreign key constraints.
4. Check constraints: Check constraints can be used to restrict acceptable data values on a column and use logical expressions to restrict acceptable data on the column. You can use multiple check constraints in a column, or create a check constraint that can be used in multiple columns on a table.
5. Default Value: The default value is also a type of database object. You can set it when creating a table, or create a default value object in the database and bind it to the specified column.
Create default gooddesp as 'common goods'
Exec sp_bindefault 'gooddesp ', 'goods. description'
6. null)

The integrity of the above constraints is calledDeclarative data integrity,They are implemented in syntax as part of table and column definitions, and there is also a way to implement data integrity:Process Data IntegrityIt is implemented independently of tables and needs to be bound to tables, such as database objects such as default values, rules, and triggers. These data integrity is not unique to a table, it can be applied to different tables in the database.

4. Apply declarative data integrity in Table operations:
1. Naming constraints: Use a constraint to restrict the type of a name. If it is a naming constraint, you can change or delete it. For example:
Create Table goods
(
Name varchar (10)
Constraint pk_goodname primary key,
...
Status tinyint default (0)
)

2. Non-naming constraints:
Create Table goods
(
Name varchar (10) primary key,
...
Status tinyint default (0)
)

3. Create a foreign key constraint:

• References table (field) on Delete no action (SQL 7.0 has no Cascade deletion)/cascade (SQL 2000 cascade deletion), without the foreign key clause
• Foreign key (field) References table (field) on Delete no action (SQL 7.0 has no Cascade deletion)/cascade (SQL 2000 cascade deletion)

Create tabel orders
(
Orderid int indentity (1, 1) Constraint pk_orderid primary key,
Goodname varchar (20) not nullReferencesGoods (name) on Delete Cascade,
Customerid int not nullForeign key (custimerid) References MERs (customerid) on Delete no action,
Quantity int not nullConstraint ck_quantity check (quantity> 0)
)

4. Modify, add, and delete Columns
Alter table MERs alter column name varchar (40) not null
Alter table MERs Add email varchar (50) null constraint ck_ea check (email like '% @ % ')
Alter table MERs drop column email

5. Modify constraints: 'with check or nocheck' indicates whether to check the existing data in the table. 'Nocheck' indicates that the constraint is invalid, and 'check' indicates that the constraint is valid again.
Alter table goods with nocheck Add/drop constraint pk_name primary key (name)
Alter table goods nocheck/check constraint pk_name

6. Differences between where and having:

• Where for a single row, having for a group of rows
• Remove the rows that do not meet the where condition before grouping the group by clause. The conditions in the having clause are applied after grouping.
• Having clauses can include aggregate functions in conditions, but where clauses cannot.

Select name, sum (Quantity) as sumquty from orders
Group by name having sum (Quantity)> 500

7. The computer returns a set of all rows. The computer... by clause returns a set of records after a group.
Select name quantity from orders
Computer sum (Quantity) [by name]
Two results are returned.

8. You can use with encryption to encrypt views, stored procedures, and triggers. After encryption, no specific content is visible to anyone with permissions.

5. Rules: A database object. It has the same functions as some check constraints. when inserting or updating data to a column in a table, it is used to limit the value range of the input value. The difference between rules and constraints is:
1. Check constraints are specified during table creation. Rules must be implemented as separate database objects.
2. Only one rule can be used in a column, but multiple check constraints can be used.
3. rules can be applied to multiple columns and can be used for custom types. Check can only be used to define columns.
To apply a rule, you must first define the rule and then bind it:
Create rule rule_name as @ value> 0
Sp_bindrule/sp_unbindrule rule_name Field
Eg: exec sp_bindrule 'range _ rule', 'Orders. Quantity'

Vi. CustomersProgramDatabase Access process:
1. the query statement is sent to the server. If it is a stored procedure, few SQL statements are sent, reducing the bandwidth pressure. The user does not see the table, so the stored procedure can be used as a security mechanism.
2. The server compiles t_ SQL statements
3. Optimize the query plan generated. 2 and 3 are both in the stored procedure, so the stored procedure is faster.
4. query by the Database Engine
5. Send the execution result back to the client program

It is best not to use SP _ as the prefix of the name for stored procedures, because SQL Server searches for stored procedures starting with SP _ in the following order:
1. Search for the master database
2. If the Stored Procedure specifies the database and owner, the stored procedure based on the specified database and owner is searched.
3. If the database and owner of the stored procedure are not specified, DBO is used as the owner to search for the stored procedure.
4. If the User-Defined stored procedure has the same name as the system stored procedure, the User-Defined stored procedure will never be executed

7. Trigger: A trigger is a special stored procedure. The trigger and the t_ SQL statement that causes the trigger to execute are treated as a transaction. Therefore, you can roll back the transaction in the trigger. If the t_ SQL statement that causes the trigger to execute an illegal operation, you can roll back the transaction so that the statement cannot be executed, after the rollback, SQL Server automatically returns to the status before the transaction is executed.
1. What are the roles of the inserted Table and the deleted table in the trigger execution process?
SQL Server creates inserted and deleted tables for each trigger. These two logical tables are maintained by the system and cannot be modified. They are stored in the memory rather than the database, and their table structure is the same as the table structure that the trigger acts on. After the trigger is executed, the two tables will also be deleted.

• The deleted table stores all rows to be deleted from the table due to executing the delete or update statement. That is to say, when executing delete or update, the deleted rows are moved from the activated trigger table to the deleted table, and the two tables do not have the same rows.
• The inserted Table stores all rows to be inserted in the table due to the execution of the insert or update statement. In an insert or update transaction, new rows are added to the active trigger table and inserted Table at the same time. The content of the inserted Table is a copy of the new row in the table that activates the trigger.

An update transaction first executes a delete operation and then an insert operation. The old row is first moved to the deleted table, then insert the table and inserted Table that activate the trigger at the same time in the new row.
Create trigger goodsdelete on goods after Delete as
Delete from orders where goodsname in (Select name from deleted)
Create trigger orderinsert on orders after insert as
If (select status from goods, inserted where goods. name = inserted. goodsname) = 1
begin
Print 'the goods is being processed, the order cannot be committed '
rollback transaction
end

8. Index: an index is a physical structure that provides the ability to quickly search for rows in a table based on the values of one or more columns. The index records key values in the table and provides pointers to the row in the table. Indexes allow database applications to find desired data without scanning the entire table.
1. Non-indexed and indexed queries:
If a field does not have an index, you must query all records in a row in the storage order to check whether the corresponding field meets the conditions. If an index is created on the corresponding column, SQL Server first searches for the index and finds the value that meets the condition in the index, then, locate the rows in the table based on the corresponding rows recorded in the index. Because the index is classified and the index contains fewer rows and columns than the full table, index search is fast.
2. Is the more indexes the better?
No. First, it takes time and storage space to create an index, and second, it slows down data modification and insertion even though the index accelerates the search speed. Because every time you perform a data modification (insert, delete, update), you need to maintain the index, it takes longer to perform the modification operation on a column with an index than on a column without an index. The more data modified, the higher the overhead involved in index maintenance.
3. Index classification:
Clustered and non-clustered indexes are divided according to the index structure, and the unique and non-unique indexes are divided according to the function implemented by the index. The unique index ensures that duplicate data is not included in the column or multi-column combination of the created index. Both clustered and non-clustered indexes can be unique indexes. The Uniqueness index is automatically created on the columns that create the primary key constraint and the uniqueness constraint.

• Clustered index: In clustered indexes, the physical storage order of rows is the same as that of indexes. That is, the order of indexes determines the storage order of rows in the table, because rows are sorted, therefore, each table can have only one clustered index. Because the order of clustered indexes is the same as the physical order of data rows, it is easy to find the rows with the beginning of a range. Therefore, clustered indexes are conducive to range search. If no clustered index is created in the table, the clustered index is automatically created on the primary key column of the table.

Create unique clustered index ix_orderid on orders (orderid) DESC

• Non-clustered indexes: Non-clustered indexes do not arrange data on physical storage. That is, the index order is not the same as the physical storage order of the row in the table. The index only records the position pointer pointing to the row in the table, these pointers are ordered and can be used to quickly locate data in the table. You can define non-clustered indexes for each commonly used column in the table. The non-clustered index determines that it is suitable for queries that directly match a single condition. Indexes created for a table are non-clustered indexes by default. If you set uniqueness constraints on a column, non-clustered indexes are automatically created on the column.

Create unique [nonclustered] index ix_name on MERs (lastname, firstname)