Database-Data base design/use easy to ignore details

First, design

1, the data type as far as possible to use the digital type, the digital comparison is much faster than the character type

2, the data type is as small as possible, the forecast can meet the future demand

3, the field is not allowed to be NULL when the table is built as far as possible, unless necessary, you can use not null+default instead

When you need to query the data, you often need to add a condition in the where conditions are not NULL, such a condition not only increases the additional overhead, but also has a great impact on the performance of the query, it is possible that because of this query condition causes your query to become very slow And a more important question is that allowing empty data may cause your query results to appear inaccurate.

----If the shape field can be assigned a value of 0, the character type can be assigned null (here just to suggest) here the null and NULL are not the same meaning--add the Reshape field can write alter TABLE table_name ADD  column_name  INT Not NULL default (0)--Add character field can write alter TABLE table_name ADD  column_name  NVARCHAR (") NOT NULL default (')

4, less with the text and image, binary field read and write is relatively slow, and, read the method is not much, most of the cases it is best not to

5, add a unique constraint to the self-increment column separately when building the self-increment column

If you want to ensure that the ID is unique, only set self-increment not, you need to set a primary KEY or a unique constraint to the field

6, building the index

The initial index design can be based on functional and performance requirements, where the index needs to be designed based on projected data volumes and queries

A, according to the amount of data to determine which tables need to increase the index, the small amount of data can only the primary key

B, depending on the frequency of use to determine which fields need to be indexed, select frequently as a join condition, filter criteria, aggregate query, sorted fields as candidates for the index field

C, combining frequently appearing fields together to form a combined index, the field order of the combined index is the same as the primary key, also need to put the most commonly used fields in front, the low repetition rate of the field in front

D, a table do not add too many indexes, because the index affects the speed of inserts and updates

After indexing, not every query uses an index, and the efficiency of indexing can vary greatly when using indexes. As long as we do not enforce the specified index in the query statement, the selection and use of the index is an automatic choice of SQL Server optimizer, and it is selected based on the conditions of the query statement and the statistics of the related table, which requires us to write the SQL statement as far as possible to make the optimizer use the index.

Second, the Code

1, query criteria do not use computed columns

Example: The query condition is year (createdate) =2014

Optimization: createdate>= ' 20140101 ' and createdate<= ' 20141231 '

Reason: Use computed column queries, which are searched by [Index Scan] Method

Do not use computed columns, go to index Lookup

In most cases, the query performance of index lookups is higher than the index scan, especially when the database queried is not very large, the time spent on index lookups is much less than the time of index scan, related knowledge [aggregation, nonclustered, heap index]

　　

2, avoid using count (*) When grouping statistics

The If object_id (' customer ') is not a null drop table [Customer]gocreate table [customer] (CId int not null,name nvarchar); OIF object_id (' order ') is not a null drop table [Order]gocreate table [order] (OId int NOT NULL, CUSID int.); Goinsert into Cu Stomer values (1, ' millet '), (2, ' rice '), (3, ' mini ') insert into [Order] values (2,2), (3,null), (4,1)

　　

--for example, to count each customer's order volume--Using the COUNT (*) Select Cid,count (*) from the customer left join [order] on Customer.cid=[order]. CUSID GROUP BY CId

　　

The actual situation cusid=3 there is no order, the number should be 0, but the result is 1,count () The field is left connected to the right of the table fields, if you are using the Main Table field results page is wrong.

--The correct method is to use the Count (cusid) Select Cid,count (CUSID) from the Customer left join [order] on Customer.cid=[order]. CUSID GROUP BY CId

3, sub-query table plus table alias

4, use when querying *

The query must not use "*" instead of a field to query, no matter how many fields you query, even if the field too many can not walk the index also avoids the additional consumption of parsing "*".

Query field values list The fields you want, avoid unnecessary fields, the more fields you query, the more expensive it is, and the likelihood that queries will not go through the index because there are more than one field listed.

5. Benefits of using Stored procedures

1. reduce network traffic. Calling a stored procedure with a low number of rows may not be very different from the network traffic that calls the SQL statement directly, but if the stored procedure contains hundreds of rows of SQL statements, the performance is definitely much higher than a single call to the SQL statement.
2. execution speed is faster. There are two reasons: first, when the stored procedure is created, the database has been parsed and optimized once. Second, once the stored procedure is executed, a copy of the stored procedure cache plan is maintained in memory so that the next time the same stored procedure is executed, it can be called directly from memory.
3. stronger adaptability: Because the stored procedure accesses the database through the stored procedure, As a result, database developers can make any changes to the database without altering the stored procedure interface, and these changes do not affect the application.
4. fabric work: The coding of the application and the database can be done independently, without suppressing each other.
5. better encapsulation portability.
6. security, which prevents certain types of SQL injection attacks.

By default, the stored procedure returns the number of rows affected by each statement in the procedure. If you do not need to use this information in your application (most applications do not need it), use the SET NOCOUNT on statement in the stored procedure to terminate the behavior. This removes one or more round trips between the client and the server, depending on the number of statements that affect the rows that are contained in the stored procedure. While this is not a big problem, it can adversely affect the performance of high-traffic applications.

6, determine if a query has a value

IF not exists/if EXISTS better than COUNT (*)

7, understand the difference between truncate and delete

Truncate operation not logged delete log operation

The main reason is because the truncate action does not activate the trigger because the truncate operation does not log delete operations for each row, so when you need to delete the data from a table you need to consider whether it should be done as a log delete operation rather than as a personal habit . .

8, understanding of the business

Xact_abort

---Query whether there are open transactions Select Xact_state () DBCC Opentran not queried to have open transactions when SET Xact_abort is on, the entire transaction terminates and rolls back if the Transact-SQL statement produces a run-time error. When SET Xact_abort is OFF, sometimes only the Transact-SQL statement that produces the error is rolled back, and the transaction continues processing. If the error is severe, the entire transaction may be rolled back even if SET xact_abort is OFF. OFF is the default setting. Compilation errors, such as syntax errors, are not affected by SET Xact_abort.

9, Order of conditional fields

For fields that are frequently used as queries in the first place, the other fields are arranged according to the actual field order of the table, so that your query statements tend to be more likely to go through the index.

10, avoid sorting with long-byte fields

Order by Id is better than order by Createtime

Database-Data base design/use easy to ignore details