1 / 28

Chapter 4

Chapter 4. SQL. SQL server. Microsoft SQL Server is a client/server database management system. A client/server database management system consists of two components: A front-end component (the client), which is used to present and manipulate data;

zena
Download Presentation

Chapter 4

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Chapter 4 SQL

  2. SQL server • Microsoft SQL Server is a client/server database management system. • A client/server database management system consists of two components: • A front-endcomponent (the client), which is used to present and manipulate data; • A backendcomponent (the database server), which is used to store, retrieve, and protectthe databases.

  3. Components of SQL Server • The commands you primarily use to query a database on a database server are • part of the Structured Query Language (SQL). • The Structured Query Language isa standardized set of commands used to work with databases. • Microsoft SQLServer 2000 supports an enhanced version of SQL referred to as Transact-SQL • Transact-SQL commands used to create, maintain, and query databases • Microsoft • SQL Server supports the most recently published standards for ANSI SQL. Sometimes, the versionof SQL implemented in SQL Server referred to as SQL-92.

  4. Transact-SQL • Data Definition Language (DDL) statements, which enable you to createdatabase objects. • Data Manipulation Language (DML) statements, which enable you to queryor modify data. • Data Control Language (DCL) statements, which enable you to determine,set, or revoke users’ permissions to SQL databases and their objects

  5. Microsoft SQL Server 2000 supports two login authenticationmodes : • Windows Authentication mode • Mixed mode

  6. Database • Master – Information about the operation of SQL Server,including user accounts,other SQL servers,environment variables,error messages,databases,storage space allocated to databases,and the tapes and disk drives on theSQL server. • Model – A template for creating new databases. SQL Server automatically copiesthe objects in this database to each new database you create. • Msdb – Information about all scheduled jobs,defined alerts,and operators on yourserver. This information is used by the SQL Server Agent service. • Tempdb –Temporary information. This database is used as a scratchpad by SQLServer. • Northwind, pubs – A sample database for learning SQL Server.

  7. Components of SQL Server • The SQL Server Services

  8. can also create your ownserver groups to organize your SQL servers SQL Server Enterprise Manager

  9. SQL Query Analyzer • Use SQL Query Analyzer to run SQL queries as well as to optimize the performanceof the queries. • A query is simply a command you send to your server. This query can request data from the server, change data, or delete information.

  10. SQL Query Analyzer

  11. Using SQL Query Analyzer

  12. SQL Server Database Structure • the term database refers to a collection of tables and otherdatabase objects such as indexes • A table consists of rows and columns; theserows and columns contain the data for the table. • A database can contain a virtuallyunlimited number of tables; each table can contain a maximum of 1,024columns (fields).

  13. Designing andImplementing Databases • To design, create, and manage databases: • Identify the issues for designing databases • Create and configure databases • Manage databases

  14. Identifying Database Design Issues • Databases andFiles: • A database is a collection of database objects; these objects include tables,indexes, views, and stored procedures. • At a minimum, each database consists of aprimary data file with an extension of .mdf. • In addition to its primary data file,you can optionally configure SQL Server to store a database in a secondary datafile. These files use the extension of .ndf.

  15. Identifying Database Design Issues • Transaction Logs:In addition to a database’s primary data file, you must also create a transactionlog for each database. • SQL Serverautomatically assigns the extension of .ldf to each transaction log file. • SQL Server uses the transactionlog to make it possible to either recover (roll forward) or undo (roll back) atransactionto protect your database from corruption in the event of a servercrash..

  16. Filegroups • When create a database, SQL Server automatically creates a default filegroupthat contains your database’s primary data file

  17. SQL Server uses disk space in 8 KB pages. • SQL Server uses some pages to keeptrack of the space allocated within a database • Within a database, SQL Server allocates space for database objectssuch as tables and indexes in extents. • An extent is a contiguous block of eightpages for a total of 64 KB of disk space. • A database consistsof 16 extents per megabyte.

  18. Creating Databases • Create a database by using: • the CREATE DATABASETransact-SQL statement. • the Create Database Wizard. • SQL Server EnterpriseManager

  19. Using the Create Database Wizard

  20. Creating a Database Using Transact-SQL CREATE DATABASE logical_database_name ON PRIMARY (NAME = logical_file_name, FILENAME = 'path\file_name', SIZE = size, MAXSIZE = maxsize, FILEGROWTH = filegrowth_increment) LOG ON (NAME = logical_file_name, FILENAME = 'path\file_name', SIZE = size, MAXSIZE = maxsize, FILEGROWTH = filegrowth_increment)

  21. Creating a Database Using Transact-SQL • CREATE DATABASE SalesDb • ON • (NAME = SalesDb_dat, • FILENAME = 'c:\data\salesDB_dat.mdf', • SIZE = 10, • MAXSIZE = 50, • FILEGROWTH = 5) • LOG ON • (NAME = 'SalesDb_log', • FILENAME = 'c:\data\salesDB_log.ldf', • SIZE = 5MB, • MAXSIZE = 25MB, • FILEGROWTH = 10%) • GO

  22. Creating a Database Using Transact-SQL • Creating Filegroup • CREATE DATABASE logical_database_name • ON • PRIMARY (NAME = logical_file_name, • FILENAME = 'path\file_name.mdf', • SIZE = size, • MAXSIZE = maxsize, • FILEGROWTH = filegrowth_increment) • FILEGROUP filegroup_name • (NAME = logical_file_name, • FILENAME = 'path\file_name.ndf', • SIZE = size, • MAXSIZE = maxsize, • FILEGROWTH = filegrowth_increment) • LOG ON • (NAME = logical_file_name, • FILENAME = 'path\file_name', • SIZE = size, • MAXSIZE = maxsize, • FILEGROWTH = filegrowth_increment)

  23. ALTER DATABASE • Adding Filegroups to an Existing Database ALTER DATABASE logical_database_name ADD FILEGROUP new_filegroup_name • Adding Files ALTER DATABASE logical_database_name ADD FILE (NAME = logical_file_name, FILENAME = 'path\file_name.ndf', SIZE = size, MAXSIZE = maxsize, FILEGROWTH = filegrowth_increment) TO FILEGROUP filegroup_name

  24. ALTER DATABASE • Configuring Database Options ALTER DATABASE database_name SET option[, status] ALTER DATABASE movies SET READ_ONLY

  25. ALTER DATABASE • Expanding a Database and its Transaction Log ALTER DATABASE database_name MODIFY FILE (NAME = 'logical_name', SIZE = size, MAXSIZE = maxsize, FILEGROWTH = filegrowth_increment) • support unrestricted file growth by using theclause MAXSIZE = UNLIMITED

  26. Deleting a Database • DROP DATABASEdatabase_name.

  27. Using SQL Server EnterpriseManager

More Related