1-20150303-sqlserver Foundation 1

Beginning SQL Server 2008

system table:
master database, which is mainly used for storing some meta-information of system database objects, can not be deleted. Direct access is not generally recommended, but is provided by the system

Diagram, function, or stored procedure for access
Model database, which provides templates for other databases, you must also reserve the ability to delete
Note that all other databases are at least as large as the model database.

The msdb database, which is provided to the SQL Agent to handle the storage tasks used to perform timed tasks, and other subsystems such as SSIS packages and policy-based management definitions are all
Using the example of msdb
The tempdb database, as a temporary storage place, even if you perform some tasks in other databases, there may still be some intermediate data stored in the tempdb database on the physical storage. Creating tables and data directly in tempdb is not recommended

The ReportServer database, installed with ReportServer installation, is used to store some meta-information for the ReportServer instance. provided separately to

ReportServer use

ReportServerTempDB
Ibid., non-persistent, just temp

AdventureWorks2008, AdventureWorksLT2008, AdventureWorksDW2008, the pubs database, the Northwind database

The data is first caught in the transaction Log and then written to the physical data file

Database objects
1: Table
2: Index, for quick query positioning.
Clustered indexes, each table has only one clustered index, and if the index is clustered, it represents the table one sentence for the actual physical storage of the clustered index. To make an analogy,

If a book is stored, then the clustered index is the row directory, which is the page number of the book.
Nonclustered indexes, each table can have multiple nonclustered indexes, and other key information to locate records, as in the Appendix Index
Any view or table, you must have at least a clustered index before you have a nonclustered index
3: Trigger
A script that is self-running for cud operations and exists only on tables
4: Constraints
As with triggers, the integrity of the data is guaranteed.
5: Filegroups
Divided into primary filegroup and secondary filegroups
6: Chart
ER diagram
7: View
A virtual table, a view consisting of a query logic. Based on security and encapsulation. can have indexed views, multi-table views If you increase the index, there will be a better query

Performance. View aggregation is pre-calculated to speed up the generation of indexes.
Of course, the weakness is that CD operations can be more cumbersome and affect performance more. More overhead.
8: Stored Procedure
Stored procedures can be said to be the programming platform area of a database, where stored procedures generate a single logical unit through T-SQL statements, allowing variables and parameters,
A, save the flow bandwidth
B, pre-compilation, save compile generation time
c, encapsulation processing, can hide complex details, and provide security rights management
D, REUSABLE
9: Custom Function
Custom functions and stored procedures are very similar, in addition to the following aspects
A, except text, ntext, image, cursor, timestamp, can return most of the data types
b, no marginal effect, they do not cross the border, such as changing the form, send email, or change database parameters, etc.
Parameters are all passed by value and can return a table type Oh!
10: Users and Roles

11: Custom Data type
Type of column that maps. NET types to SQL Server? The spatial type (geographic and geometric) added in 2008 is also based on the. NET

Type of assembly ... Pay attention to the loss of performance!
12: Full-text catalog
Accelerated text block queries, where full-text catalogs are stored externally in 2008. Base to mail database engine and storage. Full-text Index ... 、、、

1-20150303-sqlserver Foundation 1