sql server 2000 research series - essential knowledge

Technical Integration DepartmentTechnical Integration DepartmentSystem Analysis & Training GroupSystem Analysis & Training Group

Jerry YangJerry YangJuly 21, 2005July 21, 2005

SQL Server 2000 Research Series SQL Server 2000 Research Series

Essential KnowledgeEssential Knowledge

IntroductionIntroduction SQL Server 2000 EnvironmentSQL Server 2000 Environment Fundamental OperationsFundamental Operations SummarySummary

AgendaAgenda

EvolutionEvolution COBOL ANSI FileCOBOL ANSI File IBM DB2, TANDEM non-STOP SQLIBM DB2, TANDEM non-STOP SQL Oracle , Sybase , InformixOracle , Sybase , Informix Microsoft SQL ServerMicrosoft SQL Server

Microsoft SQL Server 2000Microsoft SQL Server 2000 Middle Level PriceMiddle Level Price High Level ArchitecturalHigh Level Architectural DesignDesign Microsoft SolutionMicrosoft Solution

IntroductionIntroduction

MSDE 2000 – Microsoft SQL Server 2000 MSDE 2000 – Microsoft SQL Server 2000 Desktop EngineDesktop Engine The Secret of The SphinxThe Secret of The Sphinx LimitationLimitation

Storage: 2 GB Per DatabaseStorage: 2 GB Per Database Concurrency Workload: 5 Optimal PerformanceConcurrency Workload: 5 Optimal Performance

ExtensibilityExtensibility

IntroductionIntroduction

Service ManagerService Manager Enterprise ManagerEnterprise Manager Query AnalyzerQuery Analyzer SQL Server ProfilerSQL Server Profiler DTS - Data Transformation ServicesDTS - Data Transformation Services Client Network UtilityClient Network Utility

SQL Server 2000 EnvironmentSQL Server 2000 Environment

Service ManagerService Manager


Enterprise ManagerEnterprise Manager


Enterprise Manager (Continued)Enterprise Manager (Continued)


Query AnalyzerQuery Analyzer


Query Analyzer (Continued)Query Analyzer (Continued)


The Entrance of DatabaseThe Entrance of Database DDL – Data Definition LanguageDDL – Data Definition Language DML – Data Manipulation LanguageDML – Data Manipulation Language DCL – Data Control LanguageDCL – Data Control Language

Fundamental OperationsFundamental Operations

The Entrance of DatabaseThe Entrance of Database DBMS – Database Management SystemDBMS – Database Management System


Application Programs/Queries

Software to ProcessQueries/Programs

Software to AccessStored Data

Users/Programmers

DATABASESYSTEM

DBMSSOFTWARE

Stored DatabaseDefinition

(Meta-Data)

StoredDatabase

The Entrance of Database (Continued)The Entrance of Database (Continued) Table, Row, and ColumnTable, Row, and Column Page (8K Byte/Page)Page (8K Byte/Page) IndexIndex

Clustered IndexClustered Index Non-Clustered IndexNon-Clustered Index


DDL – Data DefinitionDDL – Data Definition LanguageLanguage Creation of Table, View, Index, etc.Creation of Table, View, Index, etc.

Example I.Example I. Table CreationTable Creation CREATE TABLE CustomerCREATE TABLE Customer (( customer_id int Identity,customer_id int Identity, name char(20),name char(20), phone char(12),phone char(12), address varchar(100)address varchar(100) ))


DDL – Data DefinitionDDL – Data Definition Language (Continued)Language (Continued) Example II.Example II. Index CreationIndex Creation

Clustered IndexClustered Index CREATE UNIQUE CLUSTERED INDEX customer_clidx ON CREATE UNIQUE CLUSTERED INDEX customer_clidx ON

Test.dbo.Customer(customer_id)Test.dbo.Customer(customer_id)

Non-Clustered IndexNon-Clustered Index CREATE NONCLUSTERED INDEX customer_nonclidx ON CREATE NONCLUSTERED INDEX customer_nonclidx ON

Test.dbo.Customer(phone)Test.dbo.Customer(phone)


Database Name

DB Owner Name(It is the name of the user that createdthe object.)

Table Name

Column Name

Index Name

DML – Data Manipulation LanguageDML – Data Manipulation Language INSERT, UPDATE, DELETEINSERT, UPDATE, DELETE SELECTSELECT

SELECT can manipulate data as the data is SELECT can manipulate data as the data is returned by using functions, aggregates, grouping, returned by using functions, aggregates, grouping, or the like.or the like.

Example: SELECT SUM(age)Example: SELECT SUM(age)

FROM EmployeeFROM Employee


DML – Data Manipulation Language DML – Data Manipulation Language (Continued)(Continued) Example I. Inserting DataExample I. Inserting Data

INSERT INTO Customer (name, phone, address) INSERT INTO Customer (name, phone, address) VALUES (‘Allen’, ’02-12345678VALUES (‘Allen’, ’02-12345678’’, ‘Test Address 1’), ‘Test Address 1’)

Example II. Selecting DataExample II. Selecting Data SELECT * FROM CustomerSELECT * FROM Customer WHERE name = ‘Allen’WHERE name = ‘Allen’


DML – Data Manipulation Language DML – Data Manipulation Language (Continued)(Continued) Example III. Updating DataExample III. Updating Data

UPDATE CustomerUPDATE Customer

SET address = ‘New Address’SET address = ‘New Address’

WHERE name = ‘Allen’WHERE name = ‘Allen’ Example IV. Deleting DataExample IV. Deleting Data

DELETE FROM CustomerDELETE FROM Customer

WHERE phone = ’02-12345678’WHERE phone = ’02-12345678’


DCL – Data Control LanguageDCL – Data Control Language GrantGrant

Means that a user has permission to use an object Means that a user has permission to use an object or statement.or statement.

DenyDeny Means that a user is not allowed to use a statement Means that a user is not allowed to use a statement

or object, even if the user has previously inherited or object, even if the user has previously inherited permission.permission.

RevokeRevoke Means that records which were stored for that Means that records which were stored for that

security account are removed from the system-security account are removed from the system-protected table.protected table.


What Do You Need To Know Today…What Do You Need To Know Today… SQL Server 2000 EnvironmentSQL Server 2000 Environment

Service ManagerService Manager Enterprise ManagerEnterprise Manager Query AnalyzerQuery Analyzer

Fundamental OperationsFundamental Operations The Entrance of DatabaseThe Entrance of Database

• Database Management System (DBMS) Database Management System (DBMS) • Table, Row, Column, Index, PageTable, Row, Column, Index, Page

Data Definition LanguageData Definition Language Data Manipulation LanguageData Manipulation Language Data Control LanguageData Control Language

SummarySummary

Fundamentals of Database SystemsFundamentals of Database Systems Author:Author: Elmasri / NavatheElmasri / Navathe Publisher:Publisher: Addison-Wesley Publishing CompanyAddison-Wesley Publishing Company

Inside of Microsoft SQL Server 2000Inside of Microsoft SQL Server 2000 Author:Author: Kalen DelaneyKalen Delaney Publisher:Publisher: Microsoft PressMicrosoft Press

SQL Server 2000 – Stored Procedure & XML ProgrammingSQL Server 2000 – Stored Procedure & XML Programming Author:Author: Dejan SundericDejan Sunderic Publisher:Publisher: Brandon A. NordinBrandon A. Nordin

Microsoft Official Web Site: www.microsoft.comMicrosoft Official Web Site: www.microsoft.com

ReferenceReference

Any Question?Any Question?

sql server 2000 research series - essential knowledge

Technology

introduction sql server

enterprise manager sql

service manager sql

microsoft press sql

allen fundamental operations

inserting data insert

table customer customer

index creation clustered