Some considerations in SQL

zhaozj2021-02-16  123

Be careful

Visit your table in a certain order. If you lock in the table a, lock Store on the table B, then lock them in this order during all stored procedures. If you (inadvertent), you can lock the table B in a stored process, lock the table a, which may cause a deadlock. If the lock order is not well designed in advance, the deadlock is not easy to find.

Use parameter query

Sometimes, I saw a problem like this in the 9CBS technology forum: "SELECT * from a where a.id = 'a'b, because the single query has an exception, what should I do?", And the popular answer is: use Two single quotes instead of single quotes. This is wrong. This will not be cured, because you will encounter such problems in other characters, let alone causing serious bugs, in addition to this, so that SQL Server's buffer system cannot play a role. Use the parameters to query, the bottom of the suction, these issues are all unsatisfactory.

Use large data volume when program encoding

Programmers used in the development of test databases general data volume is not large, often is that the amount of data of the end user is large. Our usual practice is wrong. The reason is very simple: now the hard drive is not very expensive, but the performance problem is to be noticed when it has been ignored?

Don't ignore the issue of simultaneously modifying the same record

Sometimes, two users will modify the same record at the same time, so that the latter modifier has modified the previous modifier operation, some updates will be lost. Handling this situation is not very difficult: Create a TimeStamp field, check it before writing, if allow, incompatible with modification, if there is a conflict, prompt the user.

Learn to analyze inquiry

SQL Server query analyzer is your good partner, how you can understand how the query and index affect performance.

Use reference integrity

Define the primary, unique constraints, and foreign keys, which can save a lot of time.

转载请注明原文地址:https://www.9cbs.com/read-8752.html

New Post(0)