Microsoft SQL Server by leader6

VIEWS: 0 PAGES: 28

									   Microsoft SQL Server


       Dragomir Vatkov
  http://vatkov.netfirms.com

  Presentation for Course:
Modern Software Technologies
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.
   Transact SQL
Contents
   Information Sources.
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.
    – Client/Server model.
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.
    – IPX/SPX; TCP/IP; Named Pipes.
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 Services; Glossary.
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.
    –   Data Warehousing System.
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
    – Windows 95/98 - Intel platform
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
    – Triggers.
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)
    –   Alert/Response Management
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?params
 Transact 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>)...]
       [BY <column_name> [, <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]
GO
  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
FROM CONFIGURATIONS
  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
truncate table table_name
    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
Information Sources
 http://vatkov.netfirms.com
 www.microsoft.com/sql
 www.sunsite.net.edu.ck/tutorials/seusql/ch00.
  htm
 www.fintech.ru/Library/seussql/toc.htm
 www.blindprogramming.com/database.htm

								
To top