1 / 54

Chapter Five

Chapter Five. Creating SQL Server 2000 Databases. Objectives. Define the different types of databases SQL Server 2000 supports Create and manage a SQL Server 2000 database Create and manage database objects. Databases. Database

joella
Download Presentation

Chapter Five

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 Five Creating SQL Server 2000 Databases

  2. Objectives • Define the different types of databases SQL Server 2000 supports • Create and manage a SQL Server 2000 database • Create and manage database objects

  3. Databases • Database • Logical collection of data and objects that are used to support maintenance and use of that data • There are two general types of databases: • User-defined databases • Created to store application-specific data • System-defined databases • Collection of databases that store vital system information

  4. Databases • The four system databases in SQL Server 2000: • master database • Stores all system-level information for SQL Server 2000 installation • tempdb database • Used as temporary processing location • model database • Used as template for new database creation • msdb database • Used by SQL Server Agent to store scheduled alerts and jobs information

  5. Databases • In addition to system databases, the pubs and Northwind sample databases are created for every SQL Server 2000 installation • These two databases are referenced extensively in SQL Server Books Online • You can review information about the databases for a given SQL Server 2000 installation by running the sp_helpdb system-stored procedure in SQL Query Analyzer

  6. Files • Files • Physical operating system file that stores a database or transaction log • SQL Server 2000 supports three file types: • Primary data files • Holds the data and system tables for the database • Secondary data files • Allows databases to be expanded onto more than one file • Log files

  7. Files • Information from primary file would be used in following scenarios: • When attaching to an existing database • When restoring master database • When upgrading from SQL Server 7.0 • Regular identifiers • Set of naming rules for objects • Delimited identifiers • Names that do not follow rules for regular identifiers must be delimited with double quotes or square brackets

  8. Filegroups • Filegroups are designed to help administer data files • Primary filegroup • Contains the primary file and any other files not associated with another filegroup • User-defined filegroup • Contains any additional files as specified during database creation or later alteration • Default filegroup • Filegroup where space is allocated when a table or index does not explicitly define a filegroup

  9. Creating a Database Figure 5-1: Viewing database information with SQL Enterprise Manager

  10. Creating a Database Figure 5-2: Viewing database information with SQL Query Analyzer

  11. Creating a Database Figure 5-3: General tab of Database Properties dialog box

  12. Managing Files and Filegroups Figure 5-4: Data Files tab of Database Properties dialog box

  13. Managing Files and Filegroups Figure 5-5: Transaction Log tab of Database Properties dialog box

  14. Creating Databases with T-SQL Statements • You may also create databases by using the CREATE DATABASE T_SQL command • To use this command, open a query window with SQL Query Analyzer

  15. Modifying Databases • Expanding databases • Databases can be configured for automatic file expansion by using growth parameters for the databases • To increase file sizes for databases using SQL Enterprise Manager, right-click the database and choose Properties from the context-sensitive menu • This loads the the Orders Properties dialog box

  16. Modifying Databases Figure 5-6: Orders Properties

  17. Modifying Databases Figure 5-7: Expanding database file size

  18. Modifying Databases • If you would like automatic file growth, check the Automatically grow file check box in the File properties section and specify the various properties associated with automatic file growth • If you prefer to handle the file sizes on your own, uncheck the Automatically grow file check box

  19. Modifying Databases • Shrinking a database • The simplest way to shrink a database is to have it done automatically • Automatic database shrinking occurs as a background process and should not affect normal usage • Since a database set to automatically shrink cannot shrink below its initial size, make sure your initial database size makes sense

  20. Modifying Databases Figure 5-8: Shrink Database dialog box

  21. Modifying Databases Figure 5-9: Shrink Files dialog box

  22. Changing the Default Filegroup • The default filegroup is where new pages are allocated when a table or index does not explicitly define a filegroup • To change the default filegroup, right-click on your database in SQL Enterprise Manager and choose Properties from the context-sensitive menu • This loads the Properties dialog box

  23. Changing the Default Filegroup Figure 5-10: Database filegroups

  24. Configuration Settings Figure 5-11: Database options

  25. Configuration Settings Table 5-1: Database options

  26. Attaching and Detaching Databases • You may move databases from server to server by attaching and detaching • When a database is detached it becomes inaccessible • Once detached, the physical database files may be moved to another server where they can be attached for use with that server

  27. Attaching and Detaching Databases Figure 5-12: Detach Database dialog box Figure 5-13: Attach Database dialog box

  28. Modifying Databases • Databases can be renamed using the sp_renamedb system-stored procedure • To remove a database, right-click the database in SQL Enterprise Manager and choose delete from the context-sensitive menu Figure 5-14: Delete Database dialog box

  29. Database Objects Figure 5-15: Database objects

  30. General Considerations • General guidelines to consider when creating database objects: • Ownership • Object naming • Permissions • Viewing object information • Deleting objects • Renaming objects

  31. Ownership • In general, the user name of the user who creates an object is the object’s owner • The full name of the object is the object name prefaced by the owner name • All references to objects need to specify owner information • Exception to this rule is if the user name you are logged in with owns the object

  32. Object Naming • All database objects created by a user need to follow the rules for identifiers • First letter of the name must be a letter, underscore, at sign (@) or pound sign (#) • Rest of the name must follow first letter rules but may also include numbers • No spaces are allowed • Name cannot be a Transact-SQL reserved word • Names must be less than 128 characters

  33. Permissions • A user needs explicit permissions to create objects in a database • By default, permission to create an object is granted to members of the db_owner and db_ddladmin fixed database roles • In most cases, members of the sysadmin fixed server role can give users explicit permissions to create objects

  34. Permissions Figure 5-16: Database permissions

  35. Viewing Object Information • To view information about an object in the database with SQL Enterprise Manager: • Expand your database by left-clicking on the plus sign next to the database name • Left-click on the object of interest • You can also use the sp_help system-stored procedure in SQL Query Analyzer to gain information about database objects

  36. Deleting Objects • To remove an object from the system with SQL Enterprise Manager: • Expand your database by left-clicking the plus sign next to the database name • Left-click on the object of interest • Right-click to choose Delete from the context-sensitive menu

  37. Deleting Objects Figure 5-17: Drop objects

  38. Renaming Objects • To rename an object with SQL Enterprise Manager: • Expand your database by left-clicking on the plus sign next to the database name • Left-click on the object containing the items to be renamed • Right-click on the object you want to rename in the right-hand panel and choose rename from the context-sensitive menu

  39. Creating Tables Figure 5-18: New tables dialog box

  40. Adding Columns to Database Tables • Extended property • User-defined value that can be attached to almost any object • Precision • Determines the total number of decimal digits that can be stored • Scale • Determines the maximum number for decimal digits to the right of the decimal point • Globally Unique Identifier (GUID) • Value that is unique across all databases in the world

  41. Adding Columns to Database Tables Figure 5-19: Table with multiple-column primary key

  42. Managing Table Properties • Tables tab lets you manage high-level items on your table Figure 5-20: Tables tab of the Table and Index Properties dialog box

  43. Defining Table Relationships • Relationships tab is used to define primary and foreign keys Figure 5-21: Relationships tab of Table and Index Properties dialog box

  44. Managing Table Indexes • Fill factor • Determines how full the index pages will be Figure 5-22: Indexes/Keys tab of Table and Index Properties dialog box

  45. Managing Check Constraints Figure 5-23: Check constraints tab of Table and Index Properties dialog box

  46. Creating and Modifying Database Objects with T-SQL • Tables can also be added using the CREATE TABLE Transact-SQL command • Once a table is created, the ALTER TABLE statement can be used to modify it • Indexes are created using the CREATE INDEX statement

  47. Creating Views in Enterprise Manager Figure 5-24: New View Screen

  48. Creating Views in Enterprise Manager • To add tables to the Diagram Pane, use the Add Table button on the toolbar • This opens the Add Table dialog box Figure 5-25: Add Table dialog box

  49. Creating Views in Enterprise Manager Figure 5-26: One to many join

  50. Creating Views in Enterprise Manager Figure 5-27: Join properties Figure 5-28: View properties

More Related