SQL Server 2000 tutorial

server consists of six databases by default.
www.allwalkin.blogspot.com
Master It contains system catalogs that keep information about disk space, file allocations, usage, system wide configuration settings, login accounts, the existence of other database, and the existence of other SQL Servers (for distributed operations).

Model It is a simply a template database. Every time you create a new database, SQL Server makes a copy of model to form the basis of the new database.

Tempdb Temporary database, tempdb, is a workspace. SQL Server's tempdb database is unique among all other databases because it is recreated not recovered every time SQL Server is started.

Pubs This is a sample database used extensively by much of SQL Server documentation. It's available to everyone in the SQL Server community

Northwind This is a sample database that was originally developed for the use of Microsoft Access.

Msdb This database is used by the SQL Server Agent Service, which performs scheduled activities such as backups and replication tasks.




Database Files: A database file is nothing more than an operating system file. SQL Server 2000 allows the following three types of database files:

· Primary data files (.mdf)

· Secondary data files (.ndf)

· Log files (.ldf)

www.allwalkin.blogspot.com

When we create a new user database, SQL Server copies the model database (includes 19 system tables and 2 system views (for backward compatibility) ). A new user database must be 1MB or greater in size. We can create a new database using the following command:



CREATE DATABASE newdb



Types of Backups:

1). Full Backup 2). Differential Backup 3). Log Backup



Tables: A database can contain multiple tables with the same name as long as the tables have different owners. The full name of a table has three parts, in the following form:



Database.Owner. Tablename



Identifiers: It must consist of a combination of 1 through 128 letters, digits or the symbols #, $ @ or _. If we set QUOTED IDENTIFIER ON, we can use keywords as objects names (ex: columns names). A column of type rowversion holds an internal sequence number that SQL Server automatically updates every time the row is modified..



User-Defined Data Types (UDDT): A user-defined data type provides a convenient use of underlying native datatypes for columns known to have the same domain of possible values. Ex:



EXEC sp_addtype phone_number, 'varchar(20) ', 'not null'

Create table Customer(cust_ id smallint, cust_phone phone_number)



Identity Property: The IDENTITY property makes generating unique values easy. IDENTITY isn't a datatype. It's a column property. Ex:



Create table Customer(cust_ id smallint IDENTITY not null, cust_name varchar(50) not null)



The system function @@IDENTITY contains the last identity value used by the connection.www.allwalkin.blogspot.com

SELECT @@IDENTITY:- If a trigger was fired for the INSERT, the value of @@IDENTITY might have changed.

SELECT SCOPE_IDENTITY: - If an INSERT trigger also inserted a row that contained an identity column, it would be in a different scope.

SELECT IDENT_CURRENT( 'Customer" ): - To know the last IDENTITY value inserted in a specific table from any application or user.



Constraints: There are five types of constraints

1. Primary Key and Unique Constraints: Each table can have only one primary key. If there are multiple UNIQUE identifiers for a multiple columns, such column pairs are often referred to as alternate keys or candidate keys (these terms are not used by SQL Server). In practice, one of two columns is logically promoted to primary key using the PRIMARY KEY constraint, and the other is usually declared by a UNIQUE constraint. Internally, PRIMARY KEY and UNIQUE constraints are handled almost identically. Here two ways to create a table:



Create table customer(cust_ id int IDENTITY not null PRIMARY KEY,

cust_name varchar(30) not null)

Create table customer(cust_ id int IDENTITY not null,

cust_name varchar(30) not null, PRIMARY KEY(cust_id) )



2. Foreign Key Constraint: A FOREIGN KEY is a column whose values are derived from the PRIMARY KEY or UNIQUE KEY of some other table.

By using ON DELETE CASCADE option and if a user deletes a record in the master table, all corresponding recording in the detail table along with the record in the master table will be deleted.

www.allwalkin.blogspot.com

Create table orders(order_ id int not null PRIMARY KEY,

cust_id int not null REFERENCES customer(cust_ id) ON DELETE CASCADE)



A table can have a maximum of 253 FOREIGN KEY references. This limit is derived from the internal limit of 256 tables in a single query.

If you're dropping tables, you must drop all the referencing tables or drop the referencing FOREIGN KEY constraint before dropping the referenced table.



DROP TABLE orders

DROP TABLE customer



3. Check Constraint: Check constraints allow us to define an expression for a table that must not evaluate to FALSE for a data modification statement to succeed. Check constraints deal only with some logical expression for the specific row already being operated on, so no additional I/O required.



Create table employee(emp_ id int not null PRIMARY KEY

CHECK(emp_id between 0 and 1000),

emp_name varchar(30) not null constraint no_nums

CHECK(emp_name not like '%[0-9]%'),

entered_date datetime null

CHECK(entered_ date>=CURRENT_ TIMESTAMP),

dept_no int CHECK(dept_no < 0 and dept_no > 100))

www.allwalkin.blogspot.com

4. Default Constraints: A default allows you to specify a constant value, NULL or the run-time value of a system function if no known value exists or if the column is missing in an INSERT statement.



Create table employee(emp_ id int not null PRIMARY KEY DEFAULT 1000

CHECK(emp_id between 0 and 1000),

emp_name varchar(20) DEFAULT 'Radha Krishna')

Insert into employee values(DEFAULT, DEFAULT)

The order of Integrity checks is as follows:

1. Defaults are applied as appropriate.

2. NOT NULL violations are raised.

3. CHECK constraints are evaluated.

4. FOREIGN KEY checks of referencing tables are applied.

5. FOREIGN KEY checks of referenced tables are applied.

6. UNIQUE/PRIMARY KEY is checked for correctness.

7. Triggers fire.



Altering a Table: Using the ALTER table command, we can make following types of changes to an existing table.

1. Change the datatype or NULL property of a single column.

2. Add one or more new columns, with or without defining constraints for those columns..

3. Add one or more constraints.

4. Drop one or more constraints.

5. Drop one or more columns.

6. Enable or disable one or more constraints( only applies to CHECK or FOREIGN KEY constraints) .

7. Enable or disable one or more triggers.

www.allwalkin.blogspot.com

We can add only one column for each ALTER TABLE statement.



ALTER TABLE employee ALTER COLUMN emp_name varchar(50)

ALTER TABLE orders ADD FOREIGN KEY(cust_id) REFERENCES customer(cust_ id)

ALTER TABLE customer DROP COLUMN cust_name



We cannot drop the columns such as:

1. A replicated column.

2. A column used in an index.

3. A column used in a CHECK, FOREIGN KEY, UNIQUE, or PRIMARY KEY constraint.

4. A column associated with a default defined with the DEFAULT keyword or bound to a default object.

5. A column to which a rule is bound.



System Tables: This set of tables maintained by SQL Server is sometimes called the system catalog. We can identify a system table by its name or the object_id or type column in sysobjects.


www.allwalkin.blogspot.com
SELECT name from sysobjects where type='V' and name like 'sys%'



Select Statement: The SELECT statement is the most frequently used SQL command and is the fundamental way to query data.



SELECT 'Last Name'=au_lname, 'First Name'=au_fname, city, state, zip from authors

Where au_lname='Ringer' and au_fname='Anne'



Joins: Join conditions can be specified in either the FROM or WHERE clauses

1. Inner Joins: (the typical join operation, which uses some comparison operator like = or<>).

These include equi-joins and natural joins.

Inner joins use a comparison operator to match rows from two tables based on the values in common columns from each table. For example, retrieving all rows where the student identification number is the same in both the students and courses tables.

2. Outer Joins: Outer joins can be a left, a right, or full outer join.

Outer joins are specified with one of the following sets of keywords when they are specified in the FROM clause:

a. LEFT JOIN or LEFT OUTER JOIN: The result set of a left outer join includes all the rows from the left table specified in the LEFT OUTER clause, not just the ones in which the joined columns match. When arrow in the left table has no matching rows in the right table, the associated result set row contains null values for all select list columns from the right table.

b.www.allwalkin.blogspot.com