Database usage Specifications

1. If the first digit of the database/table name is a number, you need to add "[]"; for specification, do not use numbers to start

2. If there is no business column combination when the primary key, you can generate the self-increment ID when the primary key

A. The primary key is not required, but the primary key is necessary. First, ensure that the integrity of the table, such as the uniqueness of the data, when inserting two rows of identical data, no primary key is indistinguishable. Second, improve efficiency (when there is no primary key, it is inserted in the order of input)

B. There must be a primary key in some databases.

C Business Column as primary key/GUID primary key/time + machine number + self-increment ID primary key

3. Note the length limit for database fields and table names. Oracle is 30 characters

4. The foreign key must be the primary key of the other table

5.dbo: Database name. Schema name. Table name. The database name before version 05. User name. Table name

[Email protected] Custom variable @@ 系统 variable cursor cursor

7. Use Top/where to control SQL execution returned result collection

8. Note the coding rules of the database

9.EXEC Executing stored procedures

10. Cursors: Cursor c is select. From..

11. Cursor and temporal table comparison: Memory/hard disk, data volume two considerations

12. Reasons for not using goto: You can use if else/while instead to improve code quality and process

13.SET NOCOUNT on: not returned. Rows are affected

14. Whether the business logic is encapsulated in the stored procedure:

A. why writing stored procedures is required during the development process

B. Yes: Fast execution, security (masking developer privileges)

C. No: This is the basic use of the Internet, banking/telecommunications and other use of the article.

I. Business logic to program processing to reduce database resource consumption

II. Not conducive to layering specifications and maintenance

III. Easy migration (masking specific SQL differences, such as SQL Server and MySQL)

15. It is best to have return information. You can add return 1 if you don't have anything.

16.Union all, from: Where and so on aggregation write/read out

17. Index: The most practical way is to look at the implementation plan to find bottlenecks, according to the actual situation to optimize the design

A. Reference article SQL Index one step

B. Clustered index/Nonclustered index: Whether it is a physical sort

C. Nonclustered index row locators are

I. Pointer to row (file identifier + page number + line ordinal generation)

II. The value of the clustered index keyword for the row.

D.sql SERVER uses B-tree structure, nonclustered index is a new entity (similar glossary)

E. In principle the columns appearing on the WHERE clause need to be indexed: otherwise, they will be queried in the table.

F. Avoid calculations or use of functions on indexed columns in the WHERE condition: This causes the index to not be used

G. Ensure that index ordering and order by sentence consistency

H. Data repeat column High field do not create an index: no meaning

I.text, varchar (max) does not create an index

J. Foreign keys and fields used for table joins need to be indexed separately: If the foreign key column is missing an index, a query from the associated child table can only select a full table scan for the sub-table

K. Frequently changed columns do not create an index: maintenance costs are too high

Usage specifications

1. Using Pascal to name

2.ConstDict, constdictvalues as a system reserved field

3. Each entity table must contain four fields of CreateDate, CreateBy, Modifydate, modifyby

4. Script Header specification: Refer to what others have written.

A. Author/creation time/modifier/modification time/corresponding system module/description

B. Parameter annotations

C.transaction transaction Annotations

5. Stored Procedure specification:

A. Process name, author, function description, creation date, maintenance record, use case

6. Script naming: Number _ Database Name _ script function

A. When the scripting database is in a sub-library situation, the DBA executes its entire library without the need to develop processing

7. Stored procedure naming: schema name. module Name _ Functional semantics

8. Dependent conditions using by+ conditions

Naming law:

Hungarian nomenclature:

1. Each variable name is preceded by a number of characters representing the data type.

2. Variable name = attribute + Type + Object description

Hump nomenclature: Mixed-case descriptions of objects, such as username

Pascal named: The first capital of the hump named

Database usage Specifications