SQL Server Improvement article

Microsoft SQL Server2008 Review improves

A Architecture of the Microsoft SQL Server system

The 1.Microsoft SQL Server2008 consists of 4 main parts, namely 4 services:

Database engine, Analysis Services, Reporting Services, Integration Services. Such as:

2.Microsoft SQL Server2008 provides two types of databases: the system database and the user database.

System databases include: Master, model, msdb, resource, and tempdb databases;

The user instance database includes: AdventureWorks, AdventureWorksDW, AdventureWorksDW2008, AdventureWorksLT, AdventureWorksLT2008 and other databases.

The Master database is the most important system database that records server configuration information, login account information, database file information, SQL Server initialization information, and so on.

Model He is a database of services related to SQLServerAgent. The system records information about jobs, alarms, operators, scheduling, which can be used as automated system operations.

Tempdb is a staging database that stores intermediate data and results during a query.

3.OLTP and OLAP

OLTP is the main application of the traditional relational database, mainly basic, daily transaction processing, such as bank transaction. OLAP is the main application of Data Warehouse system, supports complex analysis operations, focuses on decision support, and provides intuitive and understandable query results.

4. The database object types mainly include:

database diagrams, tables, views, synonyms, programmability, Service Broker, storage, security, and so on.

5.SQL Server profilter (photo)

It is primarily used for tools that capture Sqlserver2008 events from the server.

6. Database Engine Tuning Advisor:

This tool can help users analyze workloads, and suggest features that create efficient indexing. He is a master of optimization, as much as possible to improve system efficiency.

7. DBA for Database administrators

Responsibility: To ensure the normal and efficient operation of the system.

Two Managing Security

1. Manage Login Names

Include: Create logins, set password policies, view login information, modify and delete logins, and more.

Logon policy: Windows Authentication and SQL Server Authentication logon.

Example: Create login Lin from Windows go//Creating a Windows logon name

Create login Lin with password= ' sa '//Creating SQL Server Login

1. Manage Database Users

A database user is a database-level principal, a mapping of logins in a database, and an actor who performs operations and activities in a database.

Create a user Lin with the corresponding SA login in a database:

Use A

Create user Lin from login sa [with default_schema= schema name] Go

3. Management structure

A schema is a collection of database entities that form a single namespace.

If a user does not have a schema name, the default is the DBO schema.

1) sql:create schema schema name for creating schemas

2) Creating a schema belongs to a user: Create schema schema name authorization user name

3) Create a schema belonging to a user to create a table at the same time: Creation schema name authorization user name CREATE TABLE table name (...)

4. Database Roles (permissions)

A database role is a database-level principal and a collection of database users.

1) Creating a simple role: Create role name

2) Creating a role with owner: Create role name Authorization user name

3) Add members to the role: sp_addrolemember (' Lin ',...)

5. Administrative authority (decentralization and release of authority)

Authorization: Grand statement

Right of release: Revoke statement

Deny: Deny statements can be denied by denying permissions by inheriting others ' permissions.

1) grant the CREATE DATABASE permission to a LIN User: Grant CREATE table to Lin [WITH GRANT option indicates that this permission can be transferred to others]

2) Recycle Lin's query permission on a table: Revoke select on student from Lin go

6. Microsoft SQL Server2008 built-in encryption mechanism

Two kinds of symmetric and asymmetric encryption. The key is divided into public and private keys.

Symmetric encryption:

Three. Managing Databases

1. Characteristics of database files and filegroups:

The database file is divided into: primary database. mdf, secondary database. ndf, log file. ldf;

1) The primary database. MDF contains the startup information for the database and points to other files in the database. Use

The user data and objects can be stored in this file or in a secondary data file.

Each database has a primary data file. The recommended file name extension for primary data files is. mdf.

2) secondary data files are optional and user data is defined and stored by users. By placing each file on a different disk drive, the secondary file can be used to spread the data across multiple disks. Also, if the database exceeds the maximum size of a single Windows file, you can use a secondary data file so that the database can continue to grow. The recommended file name extension for secondary data files is. ndf.

3) Log file

Filegroup: is a collection of files.

Note: ① a file or filegroup can only be used for one database, not for multiple ② a file can only be a member of one filegroup, cannot be a member of multiple filegroups ③ the data information and log information of the database cannot be placed in the same file or filegroup. Should be separated ④ log files can never be part of any filegroup.

1. Administrative unit: The minimum physical unit of management is in pages, and the size of each page is 8kb.extend 64kb.
2. Define database: Create DATABASE name

Modify database: ALTER DATABASE name

1. Expand the Database method:

1) Expand the database by adding new documents.

Example: ALTER DATABASE name add File (name= ", filename=", size= ", filegrowth=")

2) Expand the database by enlarging the size of the database file

For example: ALTER DATABASE name modify file (name= ', size= ' new value ')

6. Three main methods of shrinking the database:

1) Set the database to shrink automatically; auto_shrink

2) Shrink the size of the entire database: DBCC SHRINKDATABASE

3) Shrinking data file: DBCC SHRINKFILE

7. Managing Database Snapshots:

Concept: A transient state of a database.

1) syntax for creating a DB Snapshot: Create Database snapshot name on (name= ", filename=", as snapshot of the source database name)

2) Delete snapshot: Drop database snapshot name

8.RAID technology-Redundant Array of independent disks:

RAID is a disk system that can synthesize multiple disk drives into a single disk array to provide high performance, high reliability, and low cost.

1) Fault tolerance level: RAID0 (data parallelism), RAID1 (device Mirroring), RAID5 (parity information parallel storage).

Four T-SQL language

4 features of the 1.t-sql language: integration (definition, manipulation, control, transaction management language and Oneness), two ways of using (interactive and embedded into a high-level language), non-procedural language (telling what to do), and similar human language, easy to understand and master.

5 features of the 2.t-sql language: Data definition language, data manipulation language, data Control language, transaction management language, and additional language elements.

3. Focus on Additional language elements:

1) Identifier formatting rules: Rule One, the first character ①unicode the standard defined letter A-Z, A-z② underscore _, symbol @, number sign #; an identifier that begins with a symbol @ represents a local variable, and begins with a two-symbol @@ 开头 represents some of the built-in functions. Start with # to indicate a temporary table or temporary stored procedure, # #开头标识符表示全局临时对象.

Rule number two: ① is the same as the first character rule, with more numbers.

Rule three: cannot be a reserved word;

Rule four: Embed spaces and other special characters are not allowed.

Split identifier: "", []

4. Variables and constants:

Variables define variables at the start of the @ such as: DECLARE @name varchar

Set a value for a variable with set

For constants you need to use ' to use '.

1. Control Flow Language:

Begin...end, break, Goto, continue, If...else, while, return, waitfor (suspending batch processing, stored procedures, execution of transactions).

1. The wrong capture language:

TRY...CATCHJ constructs and @ @ERROR functions, error_number (returns the error number).

1. Data type:

1. Aggregate functions and built-in functions:

SUM returns all the values of the selected result set and
Max returns the maximum value for all values in the selected result set
Min Returns the minimum value for all values in the selected result set
AVG Returns the average of all values in the selected result set
COUNT returns the number of rows in the selected result set

Built-in functions:

Convert CONVERT (varchar (10), 123) Results return "123" Reload data type
Datalength datalength (' 12 China ') results return 6 Returns the number of bytes for any data type. "China" contains 4 bytes, "12" contains two bytes, so returns 6.

Date and Time functions:

-Time function

Select GetDate () as currenttime;

Select getUTCDate () as Utctime;

Select Day (' 2015-04-30 01:01:01 ');

SELECT MONTH (' 2015-04-30 ') as Monthvalue;

SELECT year (' 2015-04-30 '), year (' 1997-07-01 ');

Selectdatename (year, ' 2015-04-30 01:01:01 ') as Yearvalue,

Datename (quarter, ' 2015-04-30 01:01:01 ') as Quatervalue,

Datename (dayofyear, ' 2015-04-3001:01:01 ') as Dayofyearvalue,

Datename (Day, ' 2015-04-30 01:01:01 ') Asdayvalue,

Datename (week, ' 2015-04-30 01:01:01 ') Asweekvalue,

Datename (Weekday, ' 2015-04-30 01:01:01 ') as Weekdayvalue,

Datename (Hour, ' 2015-04-30 01:01:01 ') Ashourvalue,

Datename (minute, ' 2015-04-30 01:01:01 ') as Minutevalue,

Datename (Second, ' 2015-04-30 01:01:01 ') as secondvalue;

SELECT DATEPART (year, ' 2015-04-30 01:01:01 '),

DATEPART (month, ' 2015-04-30 01:01:01 '),

DATEPART (dayofyear, ' 2015-04-3001:01:01 ');

SELECT DATEADD (year,1, ' 2015-04-30 01:01:01 ') Asyearadd,

DATEADD (month, 2, ' 2015-04-3001:01:01 ') as Weekdayadd,

DATEADD (hour,2, ' 2015-04-30 01:01:01 ') as Houradd;

Ranking function:

• --Create a test data table
• DECLARE @table Table (name VARCHAR (4), score INT)
• INSERT INTO @table
• SELECT ' Zhang San ', 129 UNION all
• SELECT ' John Doe ', 137 UNION All
• SELECT ' King II ', 137 UNION All
• SELECT ' Xiaoming ', 126 UNION All
• SELECT ' small Six ', 126 UNION All
• SELECT ' small white ', UNION all
• SELECT ' Little black ', 124 UNION all
• SELECT ' West Big ', 123 UNION all
• SELECT ' small ', UNION all
• SELECT ' senior ', UNION all
• SELECT ' Tang Dynasty ', UNION all
• SELECT ' A-ni ', 99
• --Take a look
• --select * from @table
• SELECT name, score,
• Row_number () over (ORDER by result DESC) as [Row_number],
• Rank () over (ORDER by result DESC) as [rank],
• Dense_rank () over (ORDER by result DESC) as [Dense_rank],
• NTILE (6) over (ORDER by result DESC) as [NTILE]
• From @table

Five Database tables

1. Type of table: Normal table, partitioned table, temporary table, system table.

2. Creating Tables: Create TABLE table name

3. Computed columns: Use the PERSISTED keyword to specify that the computed data is saved by the column, so the column is a physical column, and the data in that column changes when the data in the other columns that the column depends on is changed.

4. Create temporary tables: #本地临时表只能由创建着使用, # #全局临时表, which can be used for all users during their lifetime.

Example: Create TABLE # #student (...)

5. Adding columns and deleting columns

Alert Table table name Add/drop column_name data type

6. Modify the data type of the column

ALTER TABLE table_name ALTER COLUMN column_name NEW_TYPE_NAME data type

7. Create and modify identifier columns:

Identity (seed, increment)

8.ROWGUIDCOL column

Uname Unqiueidentityfierdefault NEWID () This is a ROWGUIDCOL column.

10. Create a partitioned table step:

1). Create a partition function that specifies how to partition.

2) create partition scheme, specify partition function partition in file ancestral position.

3) Create a table that uses the partitioned table scheme.

For example:

Creating a partition function: Create partition function SALEAMOUNTPF (INT)

As rang left forvalues (10,100,1000,10000)

Creating a partition scheme: create partition Schema Saleamountps

As Partition SALEAMOUNTPF

to (Salefg1,salefg2,...)

Creating a partitioned Table: Create TABLE Salept (

Serolid int, saleamountint, saledate datetime, Saleperson varchar on SALEAMOUNTPF (saleamount)

)

Six Manipulating data

1. Inserting data using INSERT statements

Insert into table name (Field column ...) Values (Value ...)

In the case of Unicode data, you should use the N character before the quotation marks of the character data.

Character data needs to be quoted, and numeric data can be inserted directly.

Null values are provided: Nulls, default.

Using default values to provide a row to the table is all the defaults.

Use the bulk INSERT statement to insert data in batches in a user-specified format.

2. Updating data using the UPDATE statement

Update table Name set Column_name=expression,....

Where Query criteria

3. Delete data using the DELETE statement

Delete from table_name where delete condition

The delete operation simply deletes the data in the table and does not break the table's structure. The deleted data is recorded in the log.

The Truncate table statement deletes the data in the table, and the space occupied by the data and indexes in the table is immediately freed, and the changes to this data are not recorded in the days.

4. Change column headings = and AS

5.order by sorting: You can sort by using multiple columns, and if the first column has the same value, sort by the second column, and each column can specify ascending or descending.

TOP: Returns the specified number of rows of data. Used in pagination.

6.like for fuzzy matching% _ [] [^]

7.Compute sentence: When using aggregate function to get the result set is all aggregated value, no detail value, the solution is yo oh that compute sentence.

Two kinds of forms: one with by words and one without by words.

Example: Select ... from table name order by sort field computesum (field)

Select ... from table name order by sort field computesum (field) by field

8. Grouping technology: GROUP BY clause

is divided into unconditional groupings:

Select field ... from table name where condition group By column name

Conditional grouping:

Select field ... from table name where condition group By column name having grouping filter criteria

Rollup get the individual combinations of each group and the cube keyword to get any combination between the groups.

9. Connection technology: cross-linking i.e. Cartesian product crosses join, INNER join inner join with on specify connection conditions press, left outer connection to outer JOIN, outer connection right outer join, full OUTER join fully outer JOIN

10. Sub-query technology: Where, group BY, having

Seven Index

1. Types and characteristics of indexes

Advantages of building an index:
1). Greatly accelerate the retrieval speed of data;
2). Create a unique index that guarantees the uniqueness of each row of data in a database table;
3). Accelerate the connection between the table and the table;
4). When you use grouping and sort clauses for data retrieval, you can significantly reduce the time to group and sort in queries.
Index Type:
Depending on the capabilities of your database, you can create four indexes in the Database Designer: unique, non-unique, primary key, and clustered. Although a unique index helps locate information, for best performance results, it is recommended that you use a primary KEY or a unique constraint instead.
Unique index:
A unique index is one that does not allow any two rows to have the same index value. When duplicate key values exist in existing data, most databases do not allow the newly created unique index to be saved with the table. The database may also prevent the addition of new data that will create duplicate key values in the table. For example, if the employee's last name in the employee table A unique index is created on (lname), no two employees can have the same surname.
Non-unique index:
A non-unique index is a relative unique index that allows any two rows to have an index of the same index value. When duplicate key values exist in existing data, the database is allowed to save the newly created index with the table. The database does not prevent the addition of new data that will create duplicate key values in the table.
Primary KEY index:
Database tables often have a column or combination of columns whose values uniquely identify each row in the table. This column is called the primary key of the table. Defining a primary key for a table in a database diagram automatically creates a primary key index, which is a specific type of unique index. The index requires that each value in the primary key be unique. When using primary key indexes in queries, It also allows for fast access to data.
Clustered index (also known as clustered index):
In a clustered index, the physical order of rows in a table is the same as the logical (indexed) Order of the key values. A table can contain only one clustered index. If an index is not a clustered index, the physical order of the rows in the table does not match the logical order of the key values. Clustered indexes typically provide faster data access than nonclustered indexes.

2. Creating an index: CREATE INDEX statement

Create [Unique] [clustered clustered |nonclustered non-clustered] index index_name on table_or_view_name (column name) [Include (Column ...) Inclusive]

Example: Create uniqueclustered index index_xxx on tabl_xxx (...)

3. Maintain indexes: Dbccshow_statistics commands and graphical tools. View Index Fragmentation information: sys.dm_db_index_physical_stats system functions and using graphical tools.

To be Continued ... Updated tomorrow.

SQL Server Improvement article