SQL Server 2000: File and Filegroup

Filegroup classifying a number of files for administrative purposes and the placement of data. For example, three files (Data1.ndf, Data2.ndf, Data3.ndf) can be placed on three disk drives and stored in a filegroup named filegroup1. Files and FIlegroup facilitate the addition of new files on a new disk. If your database exceeds the maximum file size of Windows, you can use a secondary file.

A database has a primary filegroup and some filegroup, which is defined by the user. Filegroup primer contains primary files. When a database is created, the primary filegroup contains the primary data files and other files that are not stored into another filegroup. If the primary filegroup size exceeds the maximum limit, the information can not be added to the system tables. To fix this you need to move some files.

Filegroup defined by the user, is filegroup created by the user when the user create or modify a database. If filegroup is full, only the user tables that are allocated to that filegroup are affected. If you create an object and not Declare filegroup, then use the default filegroup. Filegroup default can be changed by using ALTER DATABASE

Inner Join

Inner Join is a join that produces minimal rows that there is a rows in both tables that match the join condition, which is different rows with another row in a table will be removed. In the SQL-92 standard, inner join is expressed in FROM or WHERE clause. Inner join is the only joint which is supported by SQL-92 standard in the WHERE clause. Inner join in WHERE clause otherwise known as old style inner-join.

In the examples in this module we will use an existing database. For example we will use data from the pubs database to display the Title column from Titles table and column PubName from Publishers table. Both these tables have a relationship at Pub_id column.

SELECT t.Title, p.Pub_Name FROM Publishers p INNER JOIN Titles t
ON p.Pub_id = t.Pub_id ORDER BY Title ASC

If you use multiple joins to connect more than two tables, it's easier if you think about each joint separately. For example you have three tables: A, B and C will be connected. First join will linking A and B, and then the results will be combined with C with the second join to produce the final data. Query optimizer will make a plan of execution, although the result is same. From a number of plans selected the most optimal execution.

How to Use CREATE DATABASE Command

The command "CREATE DATABASE" can be used to create a database and files to store data. You can also express some of the parameters that define the characteristics of a database. For example you can declare a maximum size of the file. If you use a simple command is:

CREATE DATABASE database_name

without parameters, the database will be created with the same size with a database that was created by  SQL Server named Model. SQL Server implemented CREATE DATABASE command in two phases:

1. SQL Server, copying the Model database for the initial database with meta data.
2. Then, SQL Server fills the page blank except for page that contains internal data that declare how to use a place name in the database. 

All objects in the database Model will be copied into the new database. You can also add objects such as tables, views, stored procedures, data types, etc into the database Model, so every time you create a new database objects will be included.

Each of the settings of the database model will be included in the new database unless you use the FOR ATTACH. For example the Model database option select into / bulkcopy set to OFF. If you change the database option with the command ALTER DATABASE model, the setting will affect the new database will be created.

Using UNION operator

You can combine the results of several queries with UNION operator. By default, duplicate rows will be removed. To maintain a duplicate, then use the keyword ALL. Some rules of the UNION operator :

• Queries will be merged, must have many same column,same order, and similar data types.
• If you use INTO clause in one query, you should put that query as first query.
• You can use GROUP BY and HAVING clause in individual query.
• ORDER BY clause and compute, are permitted only at the end of the UNION operator, to define the sequence of final results or to calculate the final value.

 For example we will display all of the customers and suppliers from the USA, sorted by CompanyName:

SELECT CompanyName, ContactName FROM
Suppliers WHERE Country = ‘USA’
UNION
SELECT CompanyName, ContactName
FROM Customers
WHERE Country=’USA’ ORDER BY CompanyName

If you want to save the results combined into a temporary table, you must use the INTO clause followed by table name, for example TempUSA

SELECT CompanyName, ContactName FROM
INTO TempUSA
Suppliers WHERE Country = ‘USA’
UNION
SELECT CompanyName, ContactName
FROM Customers
WHERE Country=’USA’ ORDER BY CompanyName

Subquery With EXISTS Operator

If a subquery uses EXISTS, such as testing the same function exists or not. WHERE clause test the existence of the outer query-lines produced by the subquery. Subquery does not return data, but produces the value TRUE or FALSE. Subquery examples that use IN, will be changed by using EXISTS:

SELECT DISTICT FompanyName,PostalCode FROM Customers WHERE EXITS (SELECT * FROM Suppliers WHERE PostalCode = Customers.PostalCode)

If you use exists, it must use SELECT *. Keyword, NOT EXISTS is the opposite of EXISTS, WHERE clause subquery is used to indicate that there is no result.

Compute and Compute BY clause

In the previous article I had explained about "order by" clause, now I will explain about the compute and compute by clause.COMPUTE and COMPUTE BY clause is used to generate new rows of data containing detailed and summary. COMPUTE  clause produces detail lines and a line that contains the summary. For example we will display all the data from Titles table from Pubs database, this is the same as ordinary SELECT command. After the last line of the data, we want to display the number of fields ytd_sales, for that we need the COMPUTE clause.

Here is the Example:

SELECT * FROM Titles compute sum(ytd_sales)

COMPUTE BY clause  producing new lines of summary data, similar to the GROUP BY clause, but the result rows as a subgroup with the values summarized. If you use COMPUTE BY clause must be accompanied with ORDER BY. For example we want to display data from table Titles sorted by Type, and after  Type, a total of ytd_sales displayed for each Type, then the command is:

SELECT type,ytd_sales FROM Titles ORDER BY type compute sum(ytd_sales) by type

Learning About ORDER BY clause

ORDER BY clause used to sort the results based on one or more columns. Ordering can be ascending (ASC) or descending (DESC), the default is ASC. If sorting by several columns, nested sequencing process.

For example we will display the employee data was sorted by country:

SELECT country, EmployeeID, LastName, FirstName FROM Employees ORDER BY Country

Data will be sorted based on the Country. Notice, how the LastName, LastName whether in each country will be sorted? No, if we wish for a country such data was sorted by LastName, you must declare two fields in the ORDER BY:

SELECT country, EmployeeID, LastName, FirstName FROM Employees ORDER BY Country, LastName

Adding Data With Subquery

You can use subquery, SELECT in INSERT statement to add values to a table or several tables or views. Subquery allows you to add a few rows at once into a table.

What is the difference subquery usage, with usage VALUES?. Subquery will add a number of existing data, while the VALUES to add new data. For example, we create a new table named TmpPublisher into the pubs database. The table contains four fields and made with command :

CREATE TABLE TmpPublisher
SELECT pub_name varchar, city, state, country FROM Publisher WHERE COUNTRY =’USA’

If the subquery is used in INSERT command, then the subquery is not necessary are enclosed in brackets. Subquery produce the same four columns and fields in the destination table. For example in tanel pub_name TmpPublisher will be filled only for the data not from the USA, then the INSERT clause you should declare a column that will contain the data.

INSERT INTO TmpPublisher (pub_name)
 SELECT pub_name FROM Publisher WHERE NOT COUNTRY = 'USA'

Reliability and Concurrency

SQL Server 2000 uses the concept of transactions and transaction logging to ensure consistency and the ability to recover information stored in the database. Transaction is the smallest unit that can be an INSERT or UPDATE single operation, or a set of existing operations within the statement BEGIN and COMMIT transaction. If the transaction contains multiple operations, all transactions must complete or cancel all.

SQL Server ensures all work units are consistent. This is done by writing the data before and after the transaction in a transaction log. Logs can be used to restore the database to a consistent state if a rollback application process, or the application will recover the data due to system failure.

SQL Server supports Microsoft Distributed Transaction Coordinator (MS DTC) so that the integrity of the transaction can be applied to distributed operations that affect multiple installations of SQL Server by using two-phase commint way.

Concurrency control on the SQL Server is updated by implementing the concept of dynamic locking. This concept of locking strategy for analyzing each transaction. SQL Server uses several types of locking, for use on a record or a collection of records or a table.

With dynamic locking, SQL Server can choose the type of locking used by the operation involved.

Batch In SQL Server 2000

Batch is a collection of commands that are sent at once by an application to SQL Server for execution. SQL Server to copy the commands from a batch into an execution plan. Furthermore, the commands in the execution plan is run one by one.

For example, in the execution plan there is an error, then all commands in the batch will not be done. If there is a run-time error, such as overflow or a constraint violation will occur the following things :

1. If there is a run-time, the relevant commands and the commands later dismissed.
2. If constraint violations occur, only the relevant commands dismissed, while commands thereafter will be continued.

Commands are executed before the run-time errors will not be affected. If the batch is in a transaction, and error causes rollback, modification of data that have not been in-commit will be aborted.

For example there are five commands in a batch. If the third command syntax errors, the command will not run. If the batch has been compiled and run second command fails, the outcome of the first command will not be affected because it has completed execution.

Query Optimization

SQL Server is an intelligent database server. SQL Server has a main function for processing database queries and transactions provided by the client application with good performance. Applications only need to provide a set of SQL commands, regardless of physical implementation of tables. SQL Server will determine the best way to process these commands with a technique called Cost-Based Query Optimization.

For example an application provides a complex query with the SELECT command. Query is to combine three tables with a join operation and then perform some aggregate operation on numerical data with a particular grouping. The query results should be sorted by date. SQL Server can optimize the query so that it produces several different strategies to run the query. Execution plan, determine the index that is used, the order of the join of the table, the most efficient way to perform aggregate calculations, and the fastest method to sort the results. SQL Server chooses the fastest. Selection is based on the cost of each execution plan consisting of CPU usage.

To generate the execution plan that accurately shows the number and distribution of data in the database, SQL Server uses distribution statistics. Statistical distribution, is a type of histogram that describes the distribution of index and data in a column. SQL Server 2000 provides several mechanisms of query plan reuse and also put together in between multiple users

Single Transtact SQL Command

Transtact SQL command is a command non procedural, meaning that the command does not declare the stretcher on the steps executed by the server. Server must analyze command to obtain the most efficient. This process is called optimization and is performed by the query optimizer.

A command, will have several execution plans. Of the possibilities would serve targeted selected a best execution plan, this process is called optimization. Query optimizer is the most important components in a system of the database SQL, because it can handle different conditions without having to change the program.

For example to process a SELECT command, the SQL Server run the following commands:

1. SQL trace and split into logical units such as keywords, expressions, operators, and the identifier.
2. Formed query tree to illustrate the logical steps necessary to change the data source into the desired shape.
3. Query optimizer to analyze all the ways to access the source tables and choose the steps that use the fastest and least resources. Query trees, repaired to record the steps that ultimately yield better execution plans.
4. Database engine started to run the execution plan.

Basic steps for processing the SELECT command can be used for other commands such as INSERT, UPDATE, and the DELETE. UDPATE and DELETE commands that have a target on the set of rows are basically doing the same process with the SELECT command. After the lines obtained were alteration or deletion.

Transaction and Lock

Transaction and Lock used to ensure consistency and integrity of the database. Transaction is a logical unit that contains a set of commands. Lock prevents conflict between users so that users can not read or modify data that is being modified by another user. Transactions use the lock to prevent users changing data that is being used by the transaction.

Each SQL Server database has a transaction log that records all transactions and database modifications made by the transaction. Log supports three operations :

• The process of recovery from single transactions
  If an application executes a ROLLBACK command or SQL Server detects an error, the records in the log is used to return to the data before the transaction begins.
• Recovery process all the transactions that have not been completed at the time SQL Server starts
  If SQL Server stops suddenly for some reason, the database may be in a state is experiencing a change that has not been written into the data file. If SQL Server starts, the recovery process will be run on each database.
• Restore the database to its original state after a failure
  Once there is a failure on the hard disk, you can restore the data on the situation before damage occurs.
  

Transaction log, not a table but is a separate file or several files in the database. Cache of the log, is a separate place in the database. Forms of data in the log is not the same as the format of page data. You can create the transaction log into multiple files. You can also set the log file growth size of the log thus avoiding uncontrolled logging.

Transaction log can be regarded as a serial string of log records. Log sequence number (LSN) identifies each log. New log record is written at the end of the log with higher LSN than the previous LSN record.

Database Management and Administration Services

SQL Server provides a set of services for development of database applications. Here is a brief description so you will understand its usefulness. SQL Server provides a number of services for database administration and you can use it on your application.

SQL Server provides a number of services for database administration and you can use it on your application. This collection service, called SQL Server Distributed Management Framework (SQL-DMF).

SQL Server provides several graphical administration tools. SQL Server Enterprise Manager is the main administrative environment for SQL Server and can be used for the installation of SQL Server database administration locally and remotely. SQL Server Enterprise Manager provides a wizard for easy administration. Some wizard that there is Database, Data Transformation Services, Management, and Replication.

In addition to the wizard of the most useful one is the Database Management Wizard that automates the maintenance plan. SQL Server Enterprise Manager and other administrative tools using a software component used together which is called SQL Server Distributed Management Objects (SQL-DMO). SQL-DMO provides a set of COM interfaces for database administration of SQL Server installations.

With SQL Namespace (SQL-NS) allows you to use dialog boxes that are part of SQL Server Enterprise Manager within your administration.

SQL Server Agent is an integrated environment for job scheduling and execution. With this facility, you can create some jobs for database administration work, either on a single server or multiple servers.

SQL Server provides two important tools that SQL Server Profiler and Index Tuning Wizard. SQL Server Profiler is used to monitor and record activity in SQL Server performance measurement and optimization. Log files SQL Server Profiler can be used to determine the query. Workload of the SQL Server Profiler, can be restarted on other SQL Server installations so you can recreate the performance characteristics for a workload.

Decision Support Services

SQL Server also provides decision support facilities. This facility is known as data warehousing or business intelligence. Decision support, analysis support the user at the database information.

Decision support system database is implemented as a separate duplicate information and read-only and stored in another operating system, and is usually called a data mart or data warehouse. Some reasons for the decision support system separate databases :

1. Decision support activities can be located on a separate database server so that even the integrity and operational databases performance undisturbed.
2. Operational database structure is usually very complicated and optimized for the transaction.
3. Decision support database must declare the consolidation of a number of operating system databases, operating systems, and different hardware platforms.

The data did not suddenly appear in a decision support database. Such data must be moved periodically. Before the transfer of data should be validated, cleaned, and in the transformation. This process is called extraction and transformation. SQL Server has a facility called Data Transformation Services (DTS) to conduct a population of decision support database. With DTS, you can create a routine to extract data from a number of operating systems using OLE DB or ODBC, perform data validation and transformation, and then call him at high speed into a decision support database.

Usually, decision support databases are redundant, because it is a copy of the operational database, thus requiring periodic maintenance involving large amounts of data. Query processor of SQL Server provides a number of techniques called bulk processing updates that are designed to improve the performance of operations serve targeted. For example, when changing large amounts of data, the query processor will try to save all changes to the index that are affected in a pass if possible. This method is faster than by changing the index by way of line by line.

SQL Server also contains a number of tools to write queries in natural language. This tool is called English Query. You can use this facility to create a description of the entry and relationships expressed in a database.

SQL Server 2000 Architecture

SQL Server 2000 is a database engine client / server is different from traditional single computer database, which uses a file system usage together for example dBASE, Microsoft Jet, FoxPro. Database systems that use the shared files depends on a single client processes per user to manipulate the data in shared files on network servers. In multi-user environment will appear to various problems, namely concurrency control using locking mechanism at the network layer.

Database system with client / server such as SQL Server 2000 using a process server to manipulate data, and requires the client process associated with the process server using IPC mechanism (inter-process communication), a local or remote, such as TCP / IP socket. The process server is a server application that processes SQL commands. The process server also handles concurrency by using more sophisticated locking mechanism from the network file system, which is used together. After running the server processes the commands, the results will be sent back to the client process through the mechanism of IPC. In this way client / server provides better access to data, which is shared by many users.

SQL Server 2000 provides a good language and interface for programming and communication with the server. Transtact-SQL Server is a programming language that is a superset of ANSI-SQL. ANSI-SQL defines four basic commands for manipulating the data which are SELECT, INSERT, UPDATE and DELETE, and a number of commands to define the database structure. Transtact-SQL add on a few things on the ANSI-SQL. Additional programming is the construction, which allows the use of stored procedures and triggers to modify data that will be executed for a particular event occurs.