MSSQL two SQL Server management libraries and tables

As a database developer, you are responsible for creating and managing databases and tables. When creating a table, maintaining the integrity of the data is important to you. To ensure that the data in the table is accurate, consistent, and reliable, SQL Server provides a variety of checks that you can apply to the table to enhance data integrity. SQL Server contains various system databases. This chapter describes the different types of system databases and explains how to manage user-defined databases and manage user-defined tables. Focus ? Managing Databases ? Manage tables ? data types in SQL Server 2008 Preview lessons ? Create a database ? Modify Database ? Deleting a database ? Create a table ? Modify Table ? Delete a table

Identify system databases in SQL Server 2008

SQL Server 2008 has 4 system databases, which are master, Model, Msdb, Tempdb, respectively.

(1) The master database is the most important database for the SQL Server system, and it records all system information for the SQL Server system. These system information includes all login information, system setup information, SQL Server initialization information, and other system databases and user database related information. Therefore, if the master database is unavailable, SQL Server cannot start. In SQL Server 2008, System objects are no longer stored in the master database, but are stored in the resource database.

(2) The model database provides a template for all databases created on the instance of SQL Server. Because tempdb is created every time SQL Server is started, the model database must always exist in the SQL Servers system. When the CREATE DATABASE statement is issued, the first part of the database is created by copying the contents of the model database, and then the remainder of the new database is populated with empty pages. If you modify the model database, all of the databases that you create later inherit those modifications. For example, you can set permissions or database options or add objects, such as tables, functions, or stored procedures.

(3) The msdb database is a proxy service database that provides storage space for its alarms, task scheduling, and recording operators ' operations.

(4) tempdb is a staging database that provides storage space for all temporary tables, temporary stored procedures, and other temporary operations. The tempdb database is used by all databases of the entire system, regardless of which database the user uses, and all temporary tables and stored procedures that they create are stored on tempdb. Each time SQL Server starts, the tempdb database is re-established. When a user disconnects from SQL Server, its temporary tables and stored procedures are automatically deleted.

Storage structure in the database

The storage structure of the database is divided into two kinds: logical storage structure and physical storage structure.

1, the logical storage structure of the database refers to the nature of the database is composed of information, SQL Server database is not only the storage of data, all the information related to data processing operations are stored in the database. In fact, the SQL Server database consists of various database objects, such as tables, views, indexes, which are used to store specific information and support specific functions, constituting the logical storage structure of the database.

2, the physical storage structure of the database is to discuss how the database file is stored on disk. The database is stored on disk as a file, consisting of database files and transaction log files, and a database should contain at least one data file and one transaction log file, which can have multiple second data files. In fact, this sentence can be understood as follows: "There is only one master data file in the database, there is at least one thing log file (that is, there can be more than one but must have), can have more than one second data file (can not)" Then what is the "primary database File" "Second data File" thing log file?

Files and filegroups in the database

The data file can be divided into two forms of the master data file and the secondary data file.

1. Primary database file (Primary)

The master data file is the starting point for the database, and each database has only one master data file. The default suffix for the primary data file name is MDF. Secondary data files are optional, and they can store all the data and objects that are not in the main data file.

2. Second database file (secondary)

The second database file is also called the secondary database file, and the database may have no secondary data files or multiple secondary data files. The default suffix of the secondary data file name is NDF.

3. Transaction log files

A transaction is a unit of work that either completes or is not complete. The Microsoft SQL Server system has transactional capabilities to guarantee consistency and integrity of database operations.

The Microsoft SQL Server system uses the transaction log of the database to implement the functionality of the transaction. The transaction log records all modification operations to the database. The log records sufficient information about the start of each transaction, changes to the data, and cancellation of modifications. With the operation of the database, the log is incremented continuously. For some large operations, such as creating an index, the log is simply the fact that the operation was recorded, not the data that occurred. The transaction log also records the allocation and release of data pages, as well as the commit and rollback of each transaction. This allows the SQL Server system to either resume the transaction or cancel the transaction. The transaction log exists in the form of an operating system file, which is called a log file in the database.

Each database has at least one log file. The suffix of the log file name is LDF by default

4. File groups

For ease of allocation and management, SQL Server allows multiple files to be grouped together and given the group a name, which is the filegroup.

A filegroup is a logical collection of files. To facilitate the management and allocation of data, filegroups can combine a number of specified files. For example, in a database, 3 files (data1.ndf, DATA2.NDF, and DATA3.NDF) are created on 3 different disk drives, and then you specify a filegroup fgroup1 for them. Later, the created table can be explicitly specified on the filegroup fgroup1. Queries for data in this table will be distributed across these 3 disks, so you can improve query performance by performing parallel access.

When you create a table, you cannot specify that the table be placed on a file, and you can specify that the table be placed on a filegroup. Therefore, if you want to place a table on a specific file, you must do so by creating a filegroup.

Defining a Database

Defining a database is simply creating a database and setting database options from scratch.

This section describes the definition of a database in three ways: Creating a database, setting database options, and viewing database information.

Creating a database is the process of determining the database name, file name, data file size, the character set of the database, whether it automatically grows, and how to automatically grow.

In a Microsoft SQL Server instance, you can create up to 32,767 databases. The name of the database must satisfy the system's identifier rules. When naming a database, be sure to make the database name brief and have a certain meaning.

After the database is created, you can use the ALTER DATABASE statement to modify the database as needed. Modifications can include changing the database name, widening the database, shrinking the database, modifying the database file, managing the database filegroup, modifying the character arrangement rules, and so on.

? Modify Database name

Example 2-4 changes the name of the lcbcom database to lgcom

? Expand the Database

In a Microsoft SQL Server system, if the data volume of the database is expanding, you can scale the database as needed.

There are 3 ways to expand the database. The first way is to set the database to autogrow, which can be set when the database is created. The second way is to directly modify the database data file or log file size, the third way is to add new secondary data files or log files in the database.

"Example 2-5" expands the lgcom database by expanding the file

? modifying database files

Users can modify the files specified in the database using the ALTER DATABASE statement as needed. These modifications include increasing the data file, adding the specified file to the specified filegroup, adding the log file, deleting the specified file, and so on.

Operations such as adding data files, modifying specified files, and so on have been discussed, with some examples of other operations related to database files.

"Example 2-6" adds a file to the specified file group

? Managing Filegroups

A filegroup is a logical combination of database data files that can be managed and distributed to improve the efficiency of concurrent use of database files.

The Transact-SQL language does not provide a separate command to manage filegroups, only the ALTER DATABASE statement provides the command to manage filegroups.

Managing tables in a database

Designing a database is actually a table in the design database. When designing tables in a database, the goal is to achieve the design requirements by using as few tables as possible, with as few columns as possible in each table. Reasonable table structure, can greatly improve the whole database data query efficiency.

? Creating tables and modifying tables

This section mainly focuses on creating and modifying tables.

Content includes creating tables, adding and removing columns, modifying the properties of columns, setting identity columns, viewing information for tables, deleting tables, and so on.

In a Microsoft SQL Server 2008 system, you can either use the CREATE TABLE statement or use the visual SQL Server Management Studio Graphics tool. The following focuses on creating tables using the CREATE TABLE statement. The CREATE TABLE statement is a frequently used method of creating tables, and is one of the most flexible and powerful ways to create tables.

Example 2-7 creating a students table using the CREATE TABLE statement

Example 2-8 creating an Orders table with the CREATE TABLE statement

After the table is created, users can modify the structure of the table by using the ALTER TABLE statement as needed. Adding new columns to a table, removing existing columns is a common operation to modify the table structure.

When a user adds a new column to a table, Microsoft SQL Server inserts a data value for that column in the table at the appropriate location in each row of the existing data.

Therefore, when you add a new column to a table, it is a good idea to define a default constraint for the new column, so that the column has a default value. If the new column does not have a default constraint and the table already has additional data, you must specify that the new column allows null values, or the system will generate an error message.

Example 2-10 using the ALTER TABLE statement to increase the columns in a table

You can also change the properties of a column by using the ALTER TABLE statement, in addition to adding new and deleted columns. This section mainly describes how to change the data type of a column. The basic syntax for changing the data type of a column by using the ALTER TABLE statement is as follows:

Alter Tabletable_name ALTER COLUMN COLUMN_NAME New_type_name

In the syntax above, the TABLE_NAME parameter specifies the name of the table to be changed, the ALTER column clause is used to specify the column name to be changed and the new data type name, the column_name parameter specifies the column name, and the New_type_name parameter specifies the new data type name

Example 2-12 using the ALTER TABLE statement to change the data type of a column

The identity column represents a symbol that uniquely identifies each row of data in the table.

In a Microsoft SQL Server 2008 system, you can create two types of identifier columns, the identity column and the ROWGUIDCOL column.

The following is a detailed study of how these two identifier columns are created and modified.

The columns that use the IDENTITY property are identity columns, and there can be at most one identity column in each table.

You need to specify two values when defining an IDENTITY property: Seed value and increment value. Thus, the value of the identity column of the first row in the table is a seed value, and the value of the identity column of the other row is incremented by one increment on the basis of the previous row value.

Example 2-13 using the IDENTITY property

The scope of the IDENTITY property is a table. That is, in a specified table, the value of the IDENTITY property column does not have duplicate values. However, there is a possibility that the same value exists for the identity attribute column between different tables. This behavior must be avoided in a copy operation that merges multiple tables. The ROWGUIDCOL property provided by the Microsoft SQL Server system for the identifier column resolves this issue. The ROWGUIDCOL column is a globally unique identifier column. You can create a maximum of one ROWGUIDCOL column per table.

Example 2-14 using the ROWGUIDCOL column

? Delete a table

Deleting a table is a permanent removal of the structure of the data and tables in the table from the database. Once the table has been deleted, you can no longer restore the table definition.

The delete table can be done using the Droptable statement, which has the following syntax:

Droptable table_name

You cannot use the Droptable statement to delete a table that is referenced by a FOREIGN KEY constraint in another table. When you need to delete a table that has a foreign key constraint reference, you must first delete the FOREIGN KEY constraint before you can delete the table. The owner of the table can delete its own table. When you delete a table, the rules that are bound on the table and the default will lose the bindings. The constraint or trigger that belongs to the table is automatically deleted. If you re-create the table, you must rebind the corresponding rules and defaults, recreate the triggers, and add the necessary constraints.

Practical issues

1. Users with what permissions can use the dropdatabase command to delete a database

2, with what kind of command can modify the name of the database, size.

3. Create a data table with commands

4. Modify a data table with a command

5. How to delete a table

Summary

1. SQL Server's own database

The master database records all server-specific configuration information, including authenticated users, databases, system configuration settings, and remote servers

The TEMPDB database holds the staging database for all temporary tables and stored procedures.

Model database is a template or prototype as a new database

The msdb database supports SQL Server Agent, and the SQL Server Agent includes features that schedule periodic activity

2. The CREATE DATABASE statement is used for creating databases, and it also includes determining the name of the database, the size of the database, and the files used to store the data in the data.

3. The DROP DATABASE statement is used to delete databases.

4. The CREATE TABLE statement is used for creating tables.

5. Alter TABEL statement for modifying tables

6. Drop TABLE statement for deleting tables

MSSQL two SQL Server management libraries and tables