SQL Server Development Guide

SQL Server database design

I. Necessity of Database Design
2. What is database design?
Iii. Importance of Database Design
Iv. Data Model
Entity-relationship (E-R) Data Model
Entity)
Attribute)
Relationship)
5. Database Design Steps
1. Demand analysis stage
2. Outline Design Stage
3. Detailed design phase
Vi. database design standardization
Common Problems in Database Design
Standard Design
Standardization and Performance

 

SQL Server database BASIC Programming

Go batch processing statement
Use and switch Databases
Create and delete Databases
Basic Data Type
Exact numeric type
Approximate numeric type
Date and Time Type
String type
Unicode string type
Binary string type
Determine whether a table or other objects and columns exist
Create and Delete tables
Add, modify, and delete fields to a table
Add or delete Constraints
Insert data
Query, modify, and delete data
Back up data and tables
Query table information using Stored Procedures

 

SQL Server Transact-SQL programming

Variable
1. Local variable)
2. Global Variables
Output statement
Logical control statement
1. If-else judgment statement
2. While... Continue... Break loop statement
3. Case
4. Other statements

 

SQL Server T-SQL advanced Query

Basic common Query
Nested subquery
# From (select... Table) Example
# In, not in Clause query example
# Exists and not exists clause query examples
# Some, any, all clause query example
Aggregate Query
1. Remove duplicate data from distinct
2. Summary query of compute and compute
3. cube Summary
Sorting Function
# Row_number Function
# Rank function
# Dense_rank Function
# Partition by grouping clause
# Ntile average sorting Function
Set operation
1. Union and Union all operations
2. Intersect intersection calculation
3. Reduce set operation with distinct T
Expression of a public table
Connection Query
1. Simplified connection Query
2. Left join
3. Right join right join
4. Inner join
5. Cross join
6. Self-join (query connections for the same table)
Function
1. Aggregate functions
2. Date and Time Functions
3. mathematical functions
4. Metadata
5. String Functions
6. Security Functions
7. system functions
8. Configure Functions
9. System statistical functions
10. User-Defined Functions

 

SQL Server indexes and views

Index
1. What is an index?
2. Index classification
3. Create an index
4. Suitable columns for index creation
5. Columns not suitable for index creation
View
1. What is a view?
2. Create a view Criterion
3. Create a view
4. Modify the view
5. Encrypted View

 

SQL Server Stored Procedure

Concept of Stored Procedure
1. Advantages of Stored Procedures
A. stored procedures allow standard component programming
B. Fast execution of Stored Procedures
C. reduce network traffic through stored procedures
D. stored procedures can be fully utilized as a security mechanism
System stored procedures
Custom Stored Procedure
1. Create a syntax
2. Create a stored procedure without Parameters
3. Modify the Stored Procedure
4. Stored Procedure with Parameters
5. Stored Procedures with wildcard Parameters
6. Stored Procedure with output parameters
7. Non-Cache stored procedures
8. Encrypted storage process
9. Stored Procedures with cursor Parameters
10. Paging Stored Procedure
Raiserror

 

SQL Server transactions, exceptions, and cursors

Transactions
1. Features of transactions
Atomicity
Consistency
Isolation
Durability
2. transaction mode
A. Display transactions
B. automatically submit the transaction
C. Implicit transactions
3. Transaction Processing
A. Begin transaction statement
B. Commit transaction statement
C. rollback transaction statement
4. Transaction example
Exception
# Example: handling error messages with exceptions
# Example: error messages that can be processed by exceptions
# Example: transactions that cannot be submitted
# Example: handling abnormal log information
Cursor
1. Actual cursor Functions
2. Basic cursor operations
A. Define a cursor
B. Open the cursor.
C. Search cursor
D. Close the cursor.
E. delete a cursor
3. cursor operation example

 

SQL Server triggers

What is a trigger?
DML triggers are divided:
1. After trigger (triggered later)
A. Insert trigger
B. Update trigger
C. Delete trigger
2. Instead of trigger (previously triggered)
Create a trigger
# Create an insert trigger
# Create a delete trigger
# Create an update trigger
# Update a column-Level Trigger
# Instead of Type trigger
# Create an instead of trigger
# Display custom message raiserror
# Modify a trigger
# Enable or disable a trigger
# Querying created trigger information
# Example: Verify the inserted data
# Example: Operation Log

 

SQL Server database security