Introduction to SQL Server 2000 by jizhen1947


									Information Technologies
and Microsoft SQL Server

          Day 1
     by Alper Özpınar
Course Overview
 Introduction to Information Technologies
    Historical background
    Data and information
    Data collecting and storing
    Data processing
 SQL Server
    SQL Server Technical Details
    Creating a database
    Security and users
    Table’s and data types
    SQL Language
    SQL Functions
    Stored Procedures
 Applications
Structure of World Economy
                                           The World
High Imperialism      World War I                           World War II   Post World War II
                                        between the Wars
   1880 - 1914       1914 - 1918/23                         1939 - 1945      1945 - 1949
                                         1918/23 - 1939

Industrialization     War Economy        Political Issues   War Economy     Political Issues

                                                            Demand >>>        Demand >>
                                                              Supply            Supply

 The Early Cold     The Late Cold War   New World 1990
War 1949 - 1969        1969 - 1990           -2000

                     New Business
Technology Race                           Globalization

  Demand >>                                                 Demand <<<
                    Demand = Supply     Demand < Supply
    Supply                                                    Supply
Computers and Humanbeings

"I think there is
  a world
  market for
  maybe five
     Thomas Watson (1874-1956), Chairman of IBM, 1943
Computers and Humanbeings

"There is no reason anyone
 would want a computer in
 their home."
 Ken Olson, president, chairman and
  founder of Digital Equipment Corp.,
Structure of old economy
        The capital rise and grow
 Most of the producers have a local or national market
    competition, not more than a competition with
    neighboring countries
   Competition based on costing and quality
   Product improvements still continues while the
    product on the market
   Products have a long market life
   Product development includes continues actions
Structure of new economy

The information rise and grow
    Competition
         Competition in world markets
         Management and control in a global marketplace
         Global work groups & delivery systems
    Products & Services
         Complex and variable
         Short market life
         Product & Service development processes are
          mostly parallel and multidisciplinary
Structure of new economy
    Weapons of the competitive market
         Perfection in product & service design
         Creativity and Innovation
         Flexible to the customer demands
         High quality
         New product development and entering the market timing
         Limited employee knowledge base
         Leadership
Structure of new economy
    Production & Services
         Ready to work with uncertainty and fuzzy situations
         Flexible network production
         Distributed & Outsourced
         All disciplines and departments works parallel in
         Low capacity high flexibility
         Cheaper
         Faster
         Durable
         Reliable
Structure of new economy
    Transformation of the Enterprise
         Flattening
         Decentralization
         Flexibility
         Location Independence
         Low transaction and coordination costs
         Empowerment
         Collaborativework and teamwork
Structure of new economy

    New way of operating the business
            SCM (Supply Chain Management )
            MRP ( Management Resources Planning)
            MIS ( Management Information Systems )
            ERP (Enterprise Resource Planning )
            CRM (Customer Relationship Management )
            ...
  Functions of Information Systems


     Customers                            Suppliers


          Input                      Output


Regulatory Agencies
Information Systems

       Organizations            Management


Key Systems in Organization
     Because there are different interests,
     specialities, and levels in an organization,
     there are different types of systems in an
     organization these are;
1.   Operational-level Systems
2.   Knowledge-level Systems
3.   Management-level Systems
4.   Strategic-level Systems
Time Sequence
 mid-1950s   Transaction Processing Systems (TPS)
 1960s       MIS
 1970s       Office Automation Systems
 1980s       DSS Expanded
              Commercial applications of expert systems
              Executive Information Systems
 1990s       Group Support Systems
              Neural Computing
              Integrated, hybrid computer systems

    KIND OF SYSTEM                               GROUPS SERVED
    STRATEGIC LEVEL                                               SENIOR

    MANAGEMENT LEVEL                                              MIDDLE

 KNOWLEDGE LEVEL                                              KNOWLEDGE &
                                                              DATA WORKERS

OPERATIONAL                                                      OPERATIONAL
LEVEL                                                              MANAGERS

        MARKETING                                          RESOURCES
Operational-level Systems
  Support operational managers by keeping track
   of the elementary activities and transactions of
   the organisation. The principle purpose of
   systems at this level is to answer routine
   questions and track the flow of transactions
   through the organisation. Covers things such
   as sales, receipts, cash deposits, payroll,
   credit decisions, flow of materials.
Knowledge-level Systems
 Support knowledge and data workers in an
  organisation. The purpose of these systems is
  to help the organisation discover, organise
  and integrate new and existing knowledge in
  to the business, and to help control the flow of
  paperwork. These systems, specially in the
  form of collaboration tools, workstations, and
  office systems, are the fastest growing
  applications in business today.
Management-level Systems
 Designed to serve the the monitoring,
  controlling, decision-making, and
  administrative activities of middle managers.
  These typically provide periodic reports rather
  than instant information on operations. Some
  of these systems support non-routine
  decision-making, focusing on less-structured
  decisions for which information requirements
  are not always clear. This will often require
  information from outwith the organisation, as
  well as from normal operational-level data.
Strategic-level Systems
  Help senior management tackle and address
   strategic issues and long-term trends, both
   within the organisation and in the external
   environment. Principal concern is matching
   organisational capability to changes, and
   opportunities, occurring in the medium to long
   term (i.e. 5 - 10 years) in the external
 Typically, an organisation might have
   operational, knowledge, management and
   strategic level systems for each functional
   area within the organisation.
 This would be based on the management model
   adopted by the organisation, so, while the
   most commonly-adopted systems structure
   would simply follow the standard functional
   model, structures reflecting bureaucratic,
   product and matrix models are also possible.

         TPS                                    MIS
         Order Processing             SALES

ORDER FILE                            UNIT
         Materials Resource           COST        MIS     REPORTS
             Planning System
             General Ledger
                                      EXPENSE           MANAGERS
                 System               DATA

  Decision Support Systems

Linear Logic                             Specialised heuristics
Regular Reports                          System makes
No support of                            decision itself
decisions                                No regular reports

TPS          OAS   MIS   ESS/EIS   DSS     KWS         ES
  Decision-Support Systems (DSS)
Components of a DSS Decision

                      User                    Management
                    Interface                Support Systems

          DBMS                    MBMS
         Database               Model Base
   Management Systems           Management
         Corporat                 Models          Mail,
            e                                    News,
                                 and Aids
         Databas                               Discussion
           es                                    Groups


       MIS         DSS

 "There are two ways of constructing a
  software design; one way is to make it so
  simple that there are obviously no
  deficiencies, and the other way is to make it
  so complicated that there are no obvious
  deficiencies. The first method is far more
  difficult." - C. A. R. Hoare
What is a Database?
 The term database has fallen into loose use lately,
  losing much of its original meaning. To some people,
  a database is any collection of data items (phone
  books, laundry lists, parchment scrolls . . . whatever).
 A record is a representation of some physical or
  conceptual object. Say, for example, that you want to
  keep track of a business’s customers. You assign a
  record for each customer. Each record has multiple
  attributes, such as name,address, and telephone
  number. Individual names, addresses, and so on are
  the data.
What Is a Database Management
 A database management system (DBMS) is a set of
  programs used to define, administer, and process
  databases and their associated applications.The
  database being “managed” is, in essence, a structure
  that you build to hold valuable data.
 A DBMS is the tool you use to build that structure and
  operate on the data contained within the database.
 Many DBMS programs are on the market today.
  Some run only on mainframe computers, some only
  on minicomputers, and some only on personal
What is SQL Server 2000?
 SQL Server is a client/server based relational
  database management system
 Runs on Windows 2000 Professional, Server,
  Advanced Server, NT 4, Windows 9x/ME or
  Windows CE
 Included in BackOffice product family
 Include in .Net Servers family
Server Side         Client Side
 Database Engine    User Interface
 Security           Forms
 Fault-tolerance    Reports
 Performance        Queries
 Concurrency
 Reliable backup
    Desktop Databases
                     User Runs
                 1   the Query
                                 2      database
                                      from Server                  Database
Query is
  run      Workstation            3    Entire Database
                                       is copied to the

                           Results are
                5        presented to the
    Client/Server Database
          User Runs
      1   the Query
                            Query is
                      2    sent to the
                             server                    Database

Workstation            4   Results are sent
                             back to the
                                              Server    Query is
                                                       run on the
                            Workstation                  server

                Results are
     5        presented to the
Types of Databases
 Relational
 Flat-File
 Hierarchical
 Relational Vs. Flat-file
      Database Objects
             COLUMN        COLUMN
                                                   •   Table
                                                   •   Row
ROW                   CA            ALL CAPS       •   Column
                                                   •   Data Type
                                           TABLE   •   Stored
                                                   •   Trigger
  Salary                                 RULE      •   Rule
                                                   •   Default
  STORED                        VIEW
                                                   •   View
PROCEDURE                                          •   Index
Data-Warehousing Vs. Transaction
Data-Warehousing            Online Transaction
 Decision Support            Processing (OLTP)
  Systems (DSS)              Continuously changing
 Online Analytical           data
  Processing (OLAP)          Updates relatively small
 Relatively fixed data       transactions
 Long running queries

   SQL Server works well with either applications
    Background on SQL Server & SQL
  History
  Position in the Market
  History of SQL – IBM 1970
SQL Language
 DDL Data Definition Language
 DML Data Manipulation Language
Brief History of Windows and Versions of Windows 2000
SQL Server Product Roadmap
 SQL Server                 SQL Server               SQL Server                       SQL Server
    6.5                        7.0                     2000                            “Yukon”

• Data warehousing       • Re-architecture of      • Reliability and scalability   • Enterprise-class
• Internet support         relational server         advancements                    scalability
• Differentiation from   • First to include OLAP   • Deep XML support              • Programmability
  Sybase SQL Server        in database             • Data warehousing                advancements
                         • Auto tuning             • SQL Server CE                 • End-to-end business
                         • Ease-of-use             • 64 bit support                  intelligence
                                                                                   • Manageability
                                                                                   • Support for multiple types
                                                                                     of data

 Foundations of              • Lowest TCO              • Performance and Scalability
  each release:              • Ease-of-Use             • Integrated Business Intelligence
    Versions of SQL Server 2000
   SQL Server CE
        Runs on Windows CE
        Replicate data from Standard and Enterprise Edition
   Personal
        Runs on Windows 9x/ME/2000 Pro
        No license required if you have Standard or Enterprise Edition
   Developer
        Designed for Developers to be used on Single Machine
        Runs on Windows NT/2000
   Standard
        Comes with most of the features for workgroups and departments
        Lacks dome enterprise level features
        Runs on Windows NT/2000 Server
   Enterprise:
        All features including clustering support, log shipping, parallel computing support,
         enhanced read-aheads, partitioning support, HTTP support, Very Large Database
         (VLDB) Support
        Runs on Windows NT/2000 Server
Feature                            Personal                Standard                          Enterprise
Runs on Microsoft Windows NT
     4 Server or Windows 2000      Yes                     Yes                               Yes
Runs on Windows NT 4 Server,
     Enterprise Edition or
                                   Yes                     Yes                               Yes
     Windows 2000 Advanced
AWE Support (Windows 2000
                                   No                      No                                Yes
SQL Server failover support        No                      No                                Yes
Supports Microsoft Search
                                   Yes, except on
    Service, full-text catalogs,                           Yes                               Yes
                                         Windows 98
    and full-text indexes
Maximum database size              2 GB                    1,048,516 TB                      1,048,516 TB
                                                                                             32 on Windows 2000 Datacenter
                                                                                                  Server 8 on Windows NT 4
                                   2 on all platforms      4 on all platforms except
Number of symmetric                                                                               Server Enterprise Edition
                                         except Windows          Windows NT 4 Server,
    multiprocessing (SMP)                                                                         and Windows 2000
                                         98, which               Enterprise Edition, which
    CPUs                                                                                          Advanced Server 4 on
                                         supports only 1         supports 8
                                                                                                  Windows NT 4 Server and
                                                                                                  Windows 2000 Server
                                                                                             64 GB on Windows 2000
                                                                                                 Datacenter Server 8 GB on
                                                                                                 Windows 2000 Advanced
                                                                                                 Server 4 GB on Windows
Physical memory supported          2 GB                    2 GB
                                                                                                 2000 Server 3 GB on
                                                                                                 Windows NT 4 Server,
                                                                                                 Enterprise Edition 2 GB on
                                                                                                 Windows NT 4 Server
SQL Server 2000 Clients

 Directly Supported:
   Windows 9x/ME
   Windows 2000
   Windows NT
 Does not ship with 16-bit drivers, however
  you can use drivers from old version of SQL
 When correctly configured with Web Server,
  any client can access it
 Differences on Windows 9x
                      Windows 9x               Windows 2000 AS

SQL Engine            Runs as an application   Runs as a Service

Integrated Security   No                       Yes

Automated Alerts      No                       Yes

Maximum Users         Five                     Unlimited (Limited by
(Recommended)                                  Hardware resources)

Performance Monitor   No                       Yes
Special System Databases
 A new SQL Server 2000 installation
  automatically includes six databases: master,
  model, tempdb, pubs, Northwind, and msdb.
 The master database is composed of system tables that keep
  track of the server installation as a whole and all other
  databases that are subsequently created. Although every
  database has a set of system catalogs that maintain information
  about objects it contains, the master database has system
  catalogs that keep information about disk space, file allocations,
  usage, systemwide configuration settings, login accounts, the
  existence of other databases, and the existence of other SQL
  servers (for distributed operations). The master database is
  absolutely critical to your system, so be sure to always keep a
  current backup copy of it. Operations such as creating another
  database, changing configuration values, and modifying login
  accounts all make modifications to master, so after performing
  such activities, you should back up master.
 The model database is 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.
  If you'd like every new database to start out
  with certain objects or permissions, you can
  put them in model, and all new databases will
  inherit them.
Pubs & Northwind
 The pubs database is a sample database used
  extensively by much of the SQL Server
 The Northwind database is a sample database that
  was originally developed for use with Microsoft
  Access. Much of the documentation dealing with
  APIs uses Northwind, as do some of the newer
  examples in the SQL Server documentation. It's a bit
  more complex than pubs, and at almost 4 MB, slightly
 The Northwind database can be rebuilt just like the
  pubs database, by running a script located in the
  \Install subdirectory. The file is called Instnwnd.sql.
 The msdb database is used by the SQL
 Server Agent service, which performs
 scheduled activities such as backups and
 replication tasks. In general, other than
 performing backups and maintenance on this
 database, you should ignore msdb.
Database Files
 A database file is nothing more than an operating system file. (In
  addition to database files, SQL Server also has backup devices,
  which are logical devices that map to operating system files, to
  physical devices such as tape drives, or even to named pipes. :
 Primary data files Every database has one primary data file
  that keeps track of all the rest of the files in the database, in
  addition to storing data. By convention, the name of a primary
  data file has the extension MDF.
 Secondary data files A database can have zero or more
  secondary data files. By convention, the name of a secondary
  data file has the extension NDF.
 Log files Every database has at least one log file that contains
  the information necessary to recover all transactions in a
  database. By convention, a log file has the extension LDF.
Creating a Database
 The easiest way to create a database is to
  use SQL Server Enterprise Manager, which
  provides a graphical front end to Transact-
  SQL commands and stored procedures that
  actually create the database and set its
 by Command
 Creating a Table
  Data Types                                    Description
bigint          Integer data from -2^63 through 2^63-1
int             Integer data from -2^31 through 2^31 - 1
smallint        Integer data from -2^15 through 2^15 - 1
tinyint         Integer data from 0 through 255
bit             Integer data with either a 1 or 0 value
decimal         Fixed precision and scale numeric data from -10^38 +1 through 10^38 -1
numeric         Fixed precision and scale numeric data from -10^38 +1 through 10^38 -1
money           Monetary data values from -2^63 through 2^63 - 1
smallmoney      Monetary data values from -214,748.3648 through +214,748.3647
float           Floating precision number data from -1.79E + 308 through 1.79E + 308
real            Floating precision number data from -3.40E + 38 through 3.40E + 38
                Date and time data from January 1, 1753, through December 31, 9999,
                with an accuracy of 3.33 milliseconds
                Date and time data from January 1, 1900, through June 6, 2079,
                with an accuracy of one minute
Creating a Table
  Data Types                                     Description
char             Fixed-length character data with a maximum length of 8,000 characters
varchar          Variable-length data with a maximum of 8,000 characters
text             Variable-length data with a maximum length of 2^31 - 1 characters
nchar            Fixed-length Unicode data with a maximum length of 4,000 characters
nvarchar         Variable-length Unicode data with a maximum length of 4,000 characters
ntext            Variable-length Unicode data with a maximum length of 2^30 - 1 characters
binary           Fixed-length binary data with a maximum length of 8,000 bytes
varbinary        Variable-length binary data with a maximum length of 8,000 bytes
image            Variable-length binary data with a maximum length of 2^31 - 1 bytes
cursor           A reference to a cursor
                 A data type that stores values of various data types,
                 except text, ntext, timestamp, and sql_variant
table            A special data type used to store a result set for later processing
                 A database-wide unique number that gets updated every time
                 a row gets updated
uniqueidentifier A globally unique identifier

To top