Microsoft SQL
Server
Dragomir Vatkov
http://vatkov.netfirms.com
Presentation for Course:
Contents
MS SQL Server 6.5
– Introduction, History, Important terms, Versions, Security,
Protocols, Tools and Utilities.
MS SQL Server 7.0
– Base Characters, Data
warehousing, Platforms, RDBCS,
SDBCS, Utilities, Ease of Use, Extra Tools, Internet and e-commerce.
MS SQL Server 2000
– What’s new, .NET platform, XML Support.
Contents
Microsoft SQL Server
6.5
Introduction to Relational
Database Management System
(RDBMS).
SQL History.
– SEQUEL -Structured English Query Language – TSQL - Transact SQL
Important terms.
– Service; Relational Database; Indexes. – SQL Server Database Type.
Microsoft SQL Server
6.5
MS SQL Server 6.5 Versions.
– Enterprise Edition – Workstation Edition
MS SQL Server Security Options.
– Integrated; Standart; Mixed.
SQL Server Client/Server
Protocol Support.
MS SQL Server 6.5
Involves
SQL Server Manager.
SQL Server Enterprise Manager.
Control Panel Services.
Tools and Utilities.
– Books Online: What’s New; SQL Server Setup; DB Developer’s Companion; Administrator’s
Companion; T-SQL Reference; Programming SQL Distributed Management Objects; MS Distributed Transaction Coordinator; ODBC API Reference;
Programming ODBC for SQL Server; Programming DB Libraly for C&VB; Programming Open Data
MS SQL Server 6.5
Involves
Tools and Utilities.
– ISQL/w Utility. – MS Query Tool.
– Client Configuration Utility. – SQL Trace Utility.
– SQL Security Manager. /trusted security connections/
– SQL Server Web Assistant. – SQL Server Service Manager. – SQL Server Setup.
– Performance Monitor. – DBC SQL Server Driver.
Microsoft SQL Server
7.0
What’s New.
– data warehousing, e-commerce, mobile support.
Base Characters.
– DB from laptop to network solutions. – Auto-configurate and extensible
database.
– Integration with OLAP(Online Analitical Processing).
– Integration with DTS(Data Transformation Services).
– Data Warehousing Framework. – Multi-server management.
Microsoft SQL Server
7.0
Base Characters.
– Wide generate different databases. – Thin connection to NT Server and
BackOffice family.
– Universal access to data. – Low memory foot print. – Multi-site replication.
Microsoft SQL Server
7.0
Data Warehousing Framework.
– OLAP Services
– Data Transformation Services. – Sub query Support.
– Visual Design Tools.
– Integrated replications.
Supported Platforms.
– Windows NT 4.0 - Intel and Alpha platforms
Microsoft SQL Server
7.0
RDBCS - Relational DataBase
Control System.
– Query Processor. – Parallel Queries. – Distributed Queries. • multi-server access• hetherogenic databases DB/2, ORACLE • file systems
• network sources
Microsoft SQL Server
7.0
SDBCS - Store DataBase Control
System.
- Dynamic Row-Level Locking /page lock, row-level lock/
- Dynamic Memory and Space Manager
- New Page and Row Formats - Data Base Integrity to Files
- Unicode /ntext, nchar, nvarchar/ - Data Types Changes
- Text and Graphical Data Types /text, ntext, image/
Microsoft SQL Server
7.0
Utilities.
– Backup and Restore
– DataBase Consistency Checker (DBCC )
– Bulk Data Log
Ease of Use.
– Dynamic Self Management – Multi-site Management
– MS Management Console Support (MMC)
Microsoft SQL Server
7.0
Ease of Use.
– Job Scheduling and Execution
– Distributed Management Objects
External Tools.
– SQL Server Profiler
– Graphical Query Analyzer – Index Tuning Wizard
– Security /dbcreator, diskadmin, sysadmin/
Microsoft SQL Server
7.0
External Tools.
– Consistency and Standart Integrity /ANSI/ISO SQL-92 Standart/
– Upgrade
• side-by-side
• computer-to-computer
Data Warehousing Strategy.
– MS Data Warehousing Framework – MS Alliance for Data Warehousing
Microsoft SQL Server
7.0
Data Warehousing Strategy.
– Product Enhancement – OLAP Services
– The Microsoft Repository
Internet, Intranet and
E-COMMERCE.
– Full-Text Search – Web Assistant
– Proxy Server Integration – ASP Support
Microsoft SQL Server
2000
What’s New.
– XML, HTTP and Windows 2000 Support SQL Server 2000 Evaluation
– SQL Server 2000 Enterprise Edition – SQL Server 2000 Standart Edition – SQL Server 2000 Personal Edition – SQL Server 2000 Developer Edition – SQL Server 2000 Evaluation Edition – SQL Server 2000 Desktop Engine
Microsoft SQL Server
2000
SQL Server 2000 and .Net
Platform -Fully Web Enable
System
– Rich XML and Internet Standart Support
• best_of_breed XML support • XPATH, URL Queries, XML
Updategrams • FORXML clause
• OPENXML T-SQL function
– Access to Data trought Web
• HTML Pages, Post to HTTP and FTP
– Close HTTP Connection to Database
Microsoft SQL Server
2000
SQL Server 2000 and .Net
Platform -Fully Web Enable
System
– URL Specifications. • http://server/vroot?sql=“…” • http://server/vroot/dbbobject/xpath • http://server/vroot/vname?params • http://server/vroot/vname/xpath?para msTransact SQL - Select
SELECT [ALL | DISTINCT] <select_list> INTO [<new_table_name>]
[FROM <table_name> [, <table_name2> [..., <table_name16>]]
[WHERE <clause>] [GROUP BY <clause>] [HAVING <clause>] [ORDER BY <clause>]
[COMPUTE <clause>] [FOR BROWSE]
where <table_name> | <view_name> =
[[<database>.]<owner>.]{<table_name>. | <view_name>.} <joined_table> =
{<table_name> CROSS JOIN <table_name> | <table_name> {INNER | LEFT [OUTER] | RIGHT [OUTER] |
FULL [OUTER]} JOIN <table_name> ON <search_conditions>} <optimizer_hints>
One or more of the following, separated with a space: [INDEX = {<index_name> | <index_id>}]
[NOLOCK] [HOLDLOCK] [UPDLOCK] [TABLOCK] [PAGLOCK] [TABLOCKX] [FASTFIRSTROW]
T-SQL Select - Clauses
WHERE <clause> =WHERE <search_conditions> GROUP BY <clause> =
GROUP BY [ALL] <aggregate_free_expression> [[, <aggregate_free_expression>]...]
[WITH {CUBE | ROLLUP}] HAVING <clause> = HAVING <search_conditions> ORDER BY <clause> = ORDER BY {{<table_name>. | <view_name>.}<column_name> | <select_list_number> | <expression>} [ASC | DESC] [...{{<table_name16>. | <view_name16>.}<column_name> |
<select_list_number> | <expression>} [ASC | DESC]]
COMPUTE <clause> =
COMPUTE <row_aggregate>(<column_name>) [, <row_aggregate>(<column_name>)...]
T-SQL Select Example
SELECT O_ID AS ORDER_ID, SUM(PRICE * QNT) AS PRICE FROM SPARTS, STORE
WHERE PART_ID = PART GROUP BY O_ID
T-SQL Table
Creating Tables
CREATE TABLE [database.[owner].]table_name ({col_name column_properties [constraint
[constraint [...constraint]]] | [[,] constraint]} [[,] {next_col_name | next_constraint}...])
CREATE TABLE [dbo].[STORE] (
[PART_ID] [int] IDENTITY (1, 1) NOT NULL , [NAME] [char] (10) NOT NULL ,
[PRICE] [money] NOT NULL ,
[WARANTY] [int] NOT NULL CONSTRAINT [DF_STORE_WARANTY] DEFAULT (1), [QUANTITY] [int] NOT NULL CONSTRAINT [DF_STORE_QUANTITY] DEFAULT (1),
CONSTRAINT [PK_STORE] PRIMARY KEY NONCLUSTERED (
[PART_ID] ) ON [PRIMARY] ) ON [PRIMARY]
T-SQL View
Views
CREATE VIEW [owner.]view_name
[(column_name [, column_name]...)] [WITH ENCRYPTION]
AS select_statement [WITH CHECK OPTION]
CREATE VIEW dbo.CONFIG_GUARANCY AS
SELECT CONF_ID, CUSTOMER, DATEADD(MM, WARANTY, DATE_ASSEM) AS GUARANCY
T-SQL DML
Insert statement
insert [into] table_name [(column_list)] {values (expression [,expression]...}| {select statement} Update
update table_name set column_name1 =
{expression1/null | (select statement)}
[,column_name2 = {expression2/null | (select statement)}...]
from table_name [where search_conditions]
Delete statement
delete table_name [from table_name, table_name...] [where search_conditions]
or
T-SQL DML Example
CREATE PROCEDURE NEW_CONFIG (@1 INT, @2 INT, @3 INT, @4 INT, @5 INT, @6 INT, @7 INT, @8 INT, @9 INT, @10 INT, @C INT)
AS
INSERT INTO CONFIGURATIONS
(ID1, ID2, ID3, ID4, ID5, ID6, ID7, ID8, ID9, ID10, CUSTOMER) VALUES
(@1, @2, @3, @4, @5, @6, @7, @8, @9, @10, @C) UPDATE STORE
SET QUANTITY = QUANTITY - 1 WHERE PART_ID = @1 OR PART_ID = @2 OR PART_ID = @3 OR PART_ID = @4 OR PART_ID = @5 OR PART_ID = @6 OR PART_ID = @7 OR PART_ID = @8 OR PART_ID = @9 OR PART_ID = @10 GO
T-SQL Store Procedure
CREATE PROCEDURE GUARANCY_CHECK (@ID INT) AS DECLARE @W DATETIME, @I INT SELECT @W = GUARANCY FROM CONFIG_GUARANCY WHERE CONF_ID = @ID
SELECT @I = DATEDIFF(MM, @W, GETDATE()) IF @I < 0
UPDATE REPAIR SET PRICE = 0
WHERE CONFIGURATION = @ID GO
