Databases
A database consists of a collection of tables that contain data and other objects, such as views, indexes, stored procedures, and triggers, defined to support activities performed with the data. The data stored in a database is usually related to a particular subject or process, such as inventory information for a manufacturing warehouse.
SQL Server can support many databases. Each database can store either interrelated orunrelated data from other databases. For example, a server can have one database that stores personnel data and another that stores product-related data. Alternatively, one database can store current customer order data, and another related database can store historical customer orders used for yearly reporting.
Before you create a database, it is important to understand the parts of a database and how to design these parts to ensure that the database performs well after it is implemented.
Parts of a Database
A database consists of a collection of tables that stores a specific set of structured data. A table contains a collection of rows (referred to as records or tuples) and columns (referred to as attributes). Each column in the table is designed to store a certain type of information (for example, dates, names, dollar amounts, or numbers). Tables have several types of controls (constraints, rules, triggers, defaults, and customized user data types) that ensure the validity of the data. Tables can have indexes similar to those in books that allow rows to be found quickly. Declarative referential integrity (DRI) constraints can be added to the tables to ensure that interrelated data in different tables remains consistent. A database can also store procedures that use Transact-SQL programming code to perform operations with the data in the database, such as storing views that provide customized access to table data.
For example, you create a database named MyCoDB to manage the data in your company. In the MyCoDb database, you create a table named Employees to store information about each employee, and the table contains columns named EmpId, LastName, FirstName, Dept, and Title. To ensure that no two employees share the same EmpId and that the Dept column contains only valid numbers for the departments in your company, you must add constraints to the table. Because you want to be able to quickly find the data for an employee, based on the employee ID or last name, you define indexes. You will have to add a row of data to the Employees table for each employee, so you create a procedure named AddEmployee, which is customized to accept the data values for a new employee and performs the operation of adding the row to the Employees table. You may need a departmental summary of employees, in which case you define a view called DeptEmps that combines data from the Departments and Employees tables and produces the output. This illustration shows the parts of the MyCoDB that is created.
Creating a Database
To create a database determine the name of the database, its owner (the user who creates the database), its size, and the files and filegroups used to store it.
Before creating a database, consider that:
* Permission to create a database defaults to members of the sysadmin and dbcreator fixed server roles, although permissions can be granted to other users.
* The user who creates the database becomes the owner of the database.
* A maximum of 32,767 databases can be created on a server.
* The name of the database must follow the rules for identifiers.
Three types of files are used to store a database:
• Primary files
These files contain the startup information for the database. The primary files are also used to store data. Every database has one primary file.
• Secondary files
These files hold all the data that does not fit in the primary data file. Databases do not need secondary data files if the primary file is large enough to hold all the data in the database. Some databases may be large enough to need multiple secondary data files, or they may use secondary files on separate disk drives to spread the data across multiple disks.
• Transaction log
These files hold the log information used to recover the database. There must be at least one transaction log file for each database, although there may be more than one. The minimum size for a log file is 512 kilobytes (KB).
Important Microsoft® SQL Server™ 2000 data and transaction log files must not be placed on compressed file systems or a remote network drive, such as a shared network directory.
When a database is created, all the files that comprise the database are filled with zeros to overwrite any existing data left on the disk by previously deleted files. Although this means that the files take longer to create, this action prevents the operating system from having to fill the files with zeros when data is written to the files for the first time during usual database operations. This improves the performance of day-to-day operations.
It is recommended that you specify a maximum size to which the file is permitted to grow. This prevents the file from growing, as data is added, until disk space is exhausted. To specify a maximum size for the file, use the MAXSIZE parameter of the CREATE DATABASE statement or the Restrict filegrowth (MB) option when using the Properties dialog box in SQL Server Enterprise Manager to create the database.
After you create a database, it is recommended that you create a backup of the master database.
A database consists of a collection of tables that contain data and other objects, such as views, indexes, stored procedures, and triggers, defined to support activities performed with the data. The data stored in a database is usually related to a particular subject or process, such as inventory information for a manufacturing warehouse.
SQL Server can support many databases. Each database can store either interrelated orunrelated data from other databases. For example, a server can have one database that stores personnel data and another that stores product-related data. Alternatively, one database can store current customer order data, and another related database can store historical customer orders used for yearly reporting.
Before you create a database, it is important to understand the parts of a database and how to design these parts to ensure that the database performs well after it is implemented.
Parts of a Database
A database consists of a collection of tables that stores a specific set of structured data. A table contains a collection of rows (referred to as records or tuples) and columns (referred to as attributes). Each column in the table is designed to store a certain type of information (for example, dates, names, dollar amounts, or numbers). Tables have several types of controls (constraints, rules, triggers, defaults, and customized user data types) that ensure the validity of the data. Tables can have indexes similar to those in books that allow rows to be found quickly. Declarative referential integrity (DRI) constraints can be added to the tables to ensure that interrelated data in different tables remains consistent. A database can also store procedures that use Transact-SQL programming code to perform operations with the data in the database, such as storing views that provide customized access to table data.
For example, you create a database named MyCoDB to manage the data in your company. In the MyCoDb database, you create a table named Employees to store information about each employee, and the table contains columns named EmpId, LastName, FirstName, Dept, and Title. To ensure that no two employees share the same EmpId and that the Dept column contains only valid numbers for the departments in your company, you must add constraints to the table. Because you want to be able to quickly find the data for an employee, based on the employee ID or last name, you define indexes. You will have to add a row of data to the Employees table for each employee, so you create a procedure named AddEmployee, which is customized to accept the data values for a new employee and performs the operation of adding the row to the Employees table. You may need a departmental summary of employees, in which case you define a view called DeptEmps that combines data from the Departments and Employees tables and produces the output. This illustration shows the parts of the MyCoDB that is created.
Creating a Database
To create a database determine the name of the database, its owner (the user who creates the database), its size, and the files and filegroups used to store it.
Before creating a database, consider that:
* Permission to create a database defaults to members of the sysadmin and dbcreator fixed server roles, although permissions can be granted to other users.
* The user who creates the database becomes the owner of the database.
* A maximum of 32,767 databases can be created on a server.
* The name of the database must follow the rules for identifiers.
Three types of files are used to store a database:
• Primary files
These files contain the startup information for the database. The primary files are also used to store data. Every database has one primary file.
• Secondary files
These files hold all the data that does not fit in the primary data file. Databases do not need secondary data files if the primary file is large enough to hold all the data in the database. Some databases may be large enough to need multiple secondary data files, or they may use secondary files on separate disk drives to spread the data across multiple disks.
• Transaction log
These files hold the log information used to recover the database. There must be at least one transaction log file for each database, although there may be more than one. The minimum size for a log file is 512 kilobytes (KB).
Important Microsoft® SQL Server™ 2000 data and transaction log files must not be placed on compressed file systems or a remote network drive, such as a shared network directory.
When a database is created, all the files that comprise the database are filled with zeros to overwrite any existing data left on the disk by previously deleted files. Although this means that the files take longer to create, this action prevents the operating system from having to fill the files with zeros when data is written to the files for the first time during usual database operations. This improves the performance of day-to-day operations.
It is recommended that you specify a maximum size to which the file is permitted to grow. This prevents the file from growing, as data is added, until disk space is exhausted. To specify a maximum size for the file, use the MAXSIZE parameter of the CREATE DATABASE statement or the Restrict filegrowth (MB) option when using the Properties dialog box in SQL Server Enterprise Manager to create the database.
After you create a database, it is recommended that you create a backup of the master database.
0 comments :
Post a Comment