SQL Server Development Guide

SQL Server Database Design

First, the necessity of database design
Second, what is the database design
Third, the importance of database design
Iv. Data Model
Entity-Relationship (E-R) data model
Entities (Entity)
Properties (Attribute)
Relationship (Relationship)
V. Database design Steps
1. Demand Analysis phase
2. Outline design Stage
3. Detailed design Stage
VI. Standardization of Database design
Frequently-occurring problems in database design
Specification Design
Normalization and performance relationships

SQL Server Database Foundation programming

Go Batch processing statement
Using, switching databases
Create, delete database
Basic data types
Exact number Type
Approximate number types
Date Time Type
String type
Unicode string type
Binary string type
Determine if a table or other object and column exists
Create, delete tables
Add a field to a table, modify a field, delete a field
adding, removing constraints
Inserting data
Querying, modifying, and deleting data
Backing up data, tables
Querying table information with stored procedures

SQL Server Transact-SQL programming

Variable
1. Local variables (locally Variable)
2. Global Variables (Variable
Output statement
Logical control Statements
1. If-else Judgment Statement
2. While...continue...break Loop statement
3. Case
4. Other statements

SQL Server T-SQL advanced query

Basic Common Queries
Nested subqueries
# from (SELECT ... table) example
# in, no in clause query example
# exists and NOT EXISTS clauses query example
# Some, any, all clause query example
Aggregate queries
1. Distinct Remove Duplicate data
2, compute and COMPUTE by summary query
3, Cube Summary
Sort function
# Row_number function
# Rank function function
# Dense_rank function
# Partition by group clause
# Ntile Average sort function
Set operation
1, Union, and Union all do union operations
2, intersect the intersection operation
3, except to reduce the set operation
Formula Table Expression
Connection Query
1. Simplified connection Query
2. Left Join
3. Right Join
4, INNER join inside connection
5. Cross join across joins
6, self-connected (the same table for connection query)
Function
1. Aggregation function
2. Date Time function
3. Mathematical functions
4. Meta-data
5. String functions
6. Security function
7. System functions
8. Configuration function
9. System Statistic function
10. User-defined function

SQL Server Indexes and views

Index
1. What is an index
2. Index classification
3. Create an index
4. The appropriate column to create the index
5. Columns that are not suitable for index creation
View
1. What is a view
2. CREATE VIEW guidelines
3. Create a View
4. Modify the View
5. Encrypted view

SQL Server Stored Procedures

The concept of stored procedures
1, the advantages of stored procedures
A, stored procedure allows standard component-type programming
B, the stored procedure can achieve faster execution speed
C, stored procedures to reduce network traffic
D, stored procedures can be used as a security mechanism to make full use of
System stored Procedures
User-defined stored procedures
1. Create a grammar
2. Create a stored procedure with no parameters
3. Modifying stored Procedures
4. Stored procedure with parameter
5. Parameter stored procedure with wildcard characters
6. Stored procedure with output parameters
7. Do not cache stored procedures
8. Encrypt stored procedures
9. Stored procedure with cursor parameters
10. Paging Stored Procedure
Raiserror

SQL Server transactions, exceptions, and cursors

Transaction
1, the characteristics of the business
Atomic Nature
Consistency
Isolation of
Durability
2, the mode of the transaction
A. Show transactions
B. Auto COMMIT Transaction
C, implicit transactions
3. Transaction processing
A, BEGIN TRANSACTION statement
B, COMMIT TRANSACTION statement
C, ROLLBACK TRANSACTION statements
4. Examples of transactions
Abnormal
# Example: Handling error messages with exceptions
# example: An exception can handle error messages
# example: A transaction that cannot be committed
# example: Handling Exception Log information
Cursor
1, the actual function of the cursor
2, the basic operation of the cursor
A. Defining cursors
B. Open the cursor
C. Retrieving cursors
D. Close the cursor
E, delete cursors
3. Cursor Operation example

SQL Server triggers

What is a trigger
DML triggers are divided into:
1. After trigger (trigger later)
A, insert trigger
B, UPDATE trigger
C, delete trigger
2. Instead of trigger (previously triggered)
Create a Trigger
# Create Insert Type Trigger
# Create a Delete type trigger
# Create an update type trigger
# Update Updates column-level triggers
# instead of type triggers
# Create instead OF triggers
# Show custom Message RAISERROR
# Modify Trigger
# Enable, disable triggers
# Query for trigger information created
# example, validating insert data
# example, action log

SQL Server Database Security

SQL Server Development Guide