Getting with SQL
Share This Class:
Overview
To review strategies and guidelines to follow in order to achieve a better development of applications in SQL Server platform. To encourage analysts and developers to apply an approach oriented to the optimisation of Interinsurance applications through the use of standards and good practices.
1. Table names should be singular, should not use blank spaces in the name and should be related to the data to be stored.
2. Trigger names must follow the ut_<name> standard.
3.View names must follow the vw_<name> standard.
4.Stored procedures names must follow the usp_<name> standard, do not use sp_which is an error.
5.Indexes are named considering the table they are related to and the purpose of the index.
-Primary keys use the text « PK » as a suffix or prefix, as deemed appropriate.
-Foreign keys use the text « FK » as suffix or prefix, as appropriate.
-Clustered indexes use the suffix or prefix « IDX » as appropriate
Documentation
1.Attach comments at the beginning of stored procedures at the end of the stored procedure
/***************************************************************************************************
Purpose: <Description>.
Created by:
Date created:
Modified by:
Last Modified: <Description>
Fecha modificación
****************************************************************************************************/
Read
1.Avoid using SELECT *. Always read only the columns you need. This avoids bringing a lot of unneeded data to the client, so it relieves network congestion and the client feels it is faster.
2. For queries of lists of records, use the TOP n operator. This avoids bringing a lot of records to the client. It also relieves network congestion and the client feels it is faster.
3. Instead of SET ROWCOUNT n, use TOP n.
4. If you use the UNION operator and you are sure that both queries do NOT have duplicate records, then use UNION ALL, to avoid implicitly using the DISTINCT operator.
5. Avoid using SELECT … INTO table_name. This will lock tables in the system. Instead, create the tables first and then rewrite the statement as INSERT INTO table_nameSELECT ….
6. If you are going to read data from a single table, avoid doing so using views that use other tables.
7. Avoid using select statements with operations on the fields you are going to get.
Example:
1. Do not use the select … form. into table_name, you must first create the table and then execute insert into table_name (field1,field2…) select column3, column4 from t2
2. Specify the columns to insert informationSayInsert into Table values (…)Must sayInsert into Table (field1,field2, …) values (…)
Joins
3. Write joins in ANSI format (use the JOIN .. ON clause). This ensures that you write all constraints without the possibility of forgetting a constraint.
4.Avoid using the same table more than once in a single query. To improve this, use temporary tables
5.Prefer to use a join instead of a sub-query.
It says
SELECT member_number, first_name, last_name, room_number
FROM members
WHERE room_number IN (SELECT rooms.room_number FROM rooms)
Must say
SELECT member_number,first_name, last_name, room_number
FROM members m
INNER JOIN rooms r ON m.room_number = r.room_number
6.In case you decide to use nested queries, always qualify the fields of the subquery with the corresponding table
It says
1. Avoid using functions on columns in the WHERE clause For example instead of using
General
1.Avoid using hardcode
2.Avoid using cursors. Instead use temporary tables with an integer field identity(1,1) which you can sweep sequentially. Don’t forget to index the identity field.
3.Consider that the MIN and MAX functions can use indexes. If possible, create indexes on the columns on which these functions are used.
4.When using temporary tables, consider creating indexes on them to increase the performance of your queries.
5.Check every query of a stored procedure and make sure it doesn’t do a table scan. To study this, enable the « Show Query Plan » directive in the Query Analyzer.
6.Try to make each query use an index on a column that has a high dispersion.
7. If possible, use derived tables instead of temporary tables. This is more reasonable if the data you would store in the temporary table is going to be used once. But if this data is going to be used many times within a stored procedure, then a temporary table is better.
If you are going to update, insert or delete very many records in a single statement, it is better to do it in small parts.
Transactionality
1. Never break a transaction into two transactions that are invoked consecutively from the client. This causes the database to be corrupted if the second transaction fails.
2. Keep your transactions small, i.e. access as few pages of the database as possible.
3. Avoid declaring a single large transaction for batch processes. It is better to have several small transactions and handle reprocessing.
What people are saying
