I. Database Objects 3
a. Tables 3
b. Views: 3
c. Indexes 4
1. Clustered indexes 4
2. Non-clustered indexes 5
d. Stored Procedures 5
e. Triggers 6
f. Functions 6
g. Defaults, Rules, and Constraints: 6
II. Domain, Entity, and Referential Integrity 8
1. Domain integrity: 8
2. Entity integrity: 8
3. Referential integrity: 8
III. Structured Query Language 9
Batches and Transactions 9
IV. Advantages and Disadvantage of SQL Server 2000 10
Advantages 10
Disadvantages 10
V. SQL Server Architecture 11
a. Editions of SQL Server 2000 11
b. SQL Server Services 12
i. Data Storage Architecture 13
1. Files and Filegroups 14
2. The Transaction Log 15
ii. Backup and Recovery Architecture 16
iii. New Features in SQL Server 2000 18
1. Support for Extensible Markup Language (XML) 18
2. Materialized Views 19
3. Partitioned Views 19
4. Address Windowing Extensions (AWE) 21
5. Integration with the Active Directory 22
6. Support for Multiple Database Versions 22
iv. The Transact-SQL Language 23
1. System Stored Procedures 23
2. Information Schema Views 23
3. Locking and Concurrency 23
4. Network Communications 23
SQL server 2000 is a Relational database developed by Microsoft. Its fundamental purpose is to store data in an organized manner and provide data to many users quickly. They must do this while managing issues such as security, concurrency, and transactions as well as maintaining the integrity of the data they contain. Managing issues such as security, concurrency, and transactions is the responsibility of the database server engine. The two most important responsibilities of a database system are database administration, which is responsible for managing and maintaining database servers as well as managing backups and recovery, setting up new databases. The other important responsibility of a database system is database development, which has to deal with actual development of a database such as object creation and the implementation of a database structure that meets business requirements. Another very important issue regarding the RDBMSs is the manageability of the database and realizing this Microsoft tried to ensure that SQL Server 2000 is easy to administrate by using the simple interface of Enterprise Manager which make you control the most common tasks by just a few clicks away.
I. Database Objects
Database objects are the structures that enable users to interact with the database, retrieving and storing data as they wish. The database server organizes and stores these data in an organized manner.
a. Tables
The fundamental unit of data storage in a relational database is the table. Tables are the structures in which actual information resides. They store information in rows, which contain values for one or more columns that specify related information about a single data item. Tables generally refer to a single logical entity. In SQL Server, tables are built within a database (which, in turn, resides on a data file).
b. Views:
A view is a database object that actually refers to data stored in one or more tables. They are defined by SQL queries, which specifies which information is to be returned to a user. Users interact with views in the same way that they interact with tables. However, views do not actually store data; instead, they retrieve relational information from tables as a result views have a minimal impact on the size of a database. Views can have several benefits one of them is security. You can create a view of a table that allows users to see only a subset of the information stored in one or more tables. Furthermore, you might choose to restrict permissions on the underlying database itself and assign permissions to the view only. Views can also be useful for encapsulating business logic. By storing commonly used queries as views, thus reducing the chance that a developer will make an error in retrieving data. In short views enable you to simplify administration, increase manageability, and improve security.
c. Indexes
Indexes are database objects that store a subset of a table’s columns. They are used to speed data searches by minimizing the amount of information that must be searched by SQL Server. This works in much the same way as you might use the index of this book. Instead of going through all pages to find what you’re looking for, you could look for simple keywords in the index. When you find those words, you’re referred to a page number that contains the detailed information. Clearly, searching through the index is much quicker than searching through the entire book page by page. Indexes are used to reduce the amount of time it takes SQL Server to find the information a user requests. By placing index on certain column of a table and a query is requesting information from a table that is based on that column the SQL Server will not have to search through all the information in the table to gain this information. Instead, it will search the index and then go to the pertinent rows for the remainder of the data. A query that refers only to indexed columns is often called a covered query.
SQL Server 2000 supports two types of indexes:
1. Clustered indexes
Clustered indexes involve the physical ordering of items in a database. This means that related information is physically stored together on the hard disk. By default, SQL Server automatically creates a clustered index on the primary key when a table is created. Since this type determines the actual order in which pages are stored on the disk, a table can have only one clustered index. Clustered indexes increase performance most when you will be returning an ordered subset of information. Clustered index will increase performance if I am requesting a range of data form a certain record to another, for example I want to retrieve info about employees whose SSN ranges from 1 to100. This is because this data is stored in order and, therefore, will require fewer I/O operations. However, in databases that are mainly used for transaction information, clustered indexes can actually decrease performance.
2. Non-clustered indexes
Non-clustered indexes are separate structures that store a subset of the information in a table, but do not affect the physical storage of data on the disk. A table may have many non-clustered indexes. These indexes actually store links to the information in the covered columns, to speed searches. The proper use of indexes can dramatically improve SQL Server performance, especially for very large or very active databases.
d. Stored Procedures
They are simply named collections of Transact-SQL statements or transactions that are stored within a database as an object. They contain procedural code that can be executed on demand. One of the main benefits that you get from using stored procedures is the increase in speed, this is due to the fact that the SQL server 2000 stores a predetermined optimal data recovery plan in cache memory. This also explains why you get a quicker response time the second time you run a query but this only happens if the data pages have not expired from the cache. Another benefit is the security that you get by using the stored procedures, similar to views, stored procedures can be used to hide from users the underlying database objects that are being affected. This helps you restrict the actions that users can perform directly on database objects, and prevents you from having to rely on (and manage) complicated permissions structures. It also allows for better management of underlying table structures. Additionally, stored procedures can call other stored procedures, allowing the development of modularly coded business rules and SQL statements.
e. Triggers
Triggers allow you to apply some actions every time data in a table is accessed. Triggers automatically fire a SQL statement whenever users execute commands that access a table. The statement can then execute one or more SQL statements that modify data or perform validity checks on the operation. The trigger can access both “before” and “after” images of the data when it is executed. Optionally, SQL Server 2000 triggers can call other triggers to form a cascading effect.
f. Functions
Database administrators and designers are familiar with using functions that are built into SQL Server. For example, the GetDate() and aggregate functions such as SUM, AVG, and COUNT. In SQL Server 2000, in addition to those built in functions, the database administrators also have the ability to create their own custom functions to perform common tasks based on business logic. Sometimes their use degrade the performance.
g. Defaults, Rules, and Constraints:
Although triggers are a good solution for executing tasks based on user actions, it’s often simpler to place restrictions on the types of information that are acceptable in a column of data. SQL Server provides several different types of constraints:
1) NOT NULL: A value must be specific for this column.
2) CHECK: place limitations on the types of information that can be stored in a specific column of data. A database developer can define a CHECK constraint to ensure that an entered integer is an even, positive value.
3) UNIQUE: No values in this column may be duplicates of another. This can be used with primary keys.
4) PRIMARY KEY: Defines which column or columns uniquely identify each row in the database. No two rows can have the same values for the primary key.
5) FOREIGN KEY: Enforces data integrity between tables by creating a reference for specific types of information.
Defaults are settings placed on a table that specify which values should be used if none are specified.
Rules function similarly to constraints but have the added benefit of existing as database objects. In contrast to constraints, which are defined as part of a column’s definition, rules can be bound or unbound to columns. This allows the flexibility of disabling a rule without losing its definition. However, only one rule may apply to a column’s definition. Rules are provided mainly for backward compatibility with SQL Server applications. Microsoft recommends that, wherever possible, CHECK constraints be used instead of rules.
II. Domain, Entity, and Referential Integrity
It is possible to create a database in which all the information stored in tables is completely unstructured and unrelated, but this would lead to many problems. Generally, different pieces of information stored in your database objects relate to each other in some way. Integrity constraints are created to ensure that these relationships are maintained in a consistent manner. There are three major types of integrity that database designers must keep in mind:
- Domain integrity:
Ensures that values stored within a column are consistent and in accordance with business rules. Domain integrity can be based on constraints such as UNIQUE and CHECK constraints that specify what values are acceptable for each column.
- Entity integrity:
Refers to information stored in rows. This type of constraint makes sure that the information stored in rows within a table is unique and follows any other business rules that are specified.
- Referential integrity:
Applies across tables and ensures that information between these objects is consistent. Referential integrity includes relationships between tables. The actual columns that match between the tables are known as foreign keys and primary keys. Referential integrity ensures that related information remains consistent.
III. Structured Query Language
The Structured Query Language is the primary method used for obtaining information from an RDBMS. The American National Standards Institute (ANSI) defines it under several versions. There are four main SQL Data Manipulation Language (DML) commands: select, insert, update and delete.
Batches and Transactions
SQL commands are often executed in groups of related commands. Queries that you run in SQL Query Analyzer are sent as batch statements which a group of related commands. However Transactions are SQL commands that are not necessarily related, but must execute in an all-or-nothing fashion. Transactions must pass the “ACID” test, having all of the following properties, Atomicity (Each transaction is represented as a single unit i.e.: either all steps are carried out or the entire process is aborted), Consistency (No data changes should violate the structure and design of the database), Independence (Transactions are not aware that other transactions are being run at the same time), Durability (If an error occurs during the processing of a transaction, partial transactions should be reversed). Another important concept of a transaction is distributed transactions in which data is stored in more than one site. It is often necessary to run transactions across multiple databases and platforms since the database can be implemented at each site using a different platform, which may be anything such as Microsoft Access database or could be another RDBMS such as Oracle. SQL Server 2000 supports the use of linked servers, which allow users to enter queries that refer to objects stored on other machines. DBAs can also specify whether the statements will execute on the SQL Server or on the remote data repository. In this way, users can access information stored in various locations transparently.
IV. Advantages and Disadvantage of SQL Server 2000
Advantages
1) Dynamic resource allocation: it lets disk and memory usage scale to meet the changing database demands. This provides better performance and simplifies management.
2) Graphical query analyzer: this is an essential tool for building and executing interactive query. This graphical query analyzer allows also to see the steps the query processor uses to execute the query.
3) Data transformation service: It simplifies importing and exporting data between any two objects linking and embedding objects. It also generates visual basic script files that you can run from the windows scripting host or other component object model environment
Disadvantages
1) Installation and operation requires internet explorer (IE) 4.0
2) No cascading declarative referential integrity; which can be overcome by using triggers.
3) Migration requires a database reload: reloading all data in a database is a dangerous task that can cause data loss.
V. SQL Server Architecture
a. Editions of SQL Server 2000