MDP Consulting

Programming Standards Manual

SQL SERVER
 Programming Standards

Table of contents

1 INTRODUCTION.1
1.1 Description. ....................................................................................................1
1.2 Objective of the Standardss .............................................................................1
1.3 Responsible Person....................................................................................1
2 DATABASE.3
2.1 General Policies..........................................................3
2.1.1 Step From Development to Production...............................................................3
2.2 Test the database objects...............................................................5
2.3 Programming recommendations ..............................................................5
3 DATABASE DESIGN.6
3.1 General Information
3.2 Rules for Naming Tables ..........................................................................8
3.3 Rules for Naming Fields........................................................................9
3.4 Rules for Naming Stored Proceduress ....................................10
3.5 Rules for Naming Functions......................................................11
3.6 Rules for naming Trigger. .......................................................................12
3.7 Rules for naming Jobs............................................................................12
3.8 Rules for naming VIEWS. .
3.9 Format for documenting objects. ...............................................................13
4 RECOMMENDATIONS
4.1 TIP for programming in T-SQL
4.1.1 Lecturea ................................................................17
4.1.2 Joins
4.1.3 Where ......................................................................................................18
4.1.4 General ................................................................................................19
4.1.5 Transactionalityd....................................................................................19

Management and Administration - IT Page: 2

 Programming Standards

1 Introduction

1.1 Description.
The present standards manual includes the technical standards.
applicable to logical design, physical design, and construction of the database and system
security under the Microsoft SQL environment and development tools
Microsoft Visual Studio.

The content of the manual must evolve in accordance with the needs of
application development, the level of implementation of development methodologies and
the practice itself, which is why its main purpose is to serve as the framework document that
At all times, collect the norms that are considered updated.
applicable.

The content of the standards object, the personnel is indicated below.

responsible for each of the areas where the defined standards are organized and
finally, the overall organization and the typographic standards commonly
used throughout the manual.

1.2 Objective of the Standards

The objective or purpose of defining and applying the Standards is to

following:
Ensure the application of a minimum level of quality in the systems of
information.
Provide a consistent approach and a common language in environments with
multiple development teams.
Getting the components of information systems to be more
easy to understand and maintain.
Facilitate the onboarding process for new development staff.

1.3 Responsible Person

It is the responsibility of each member of the work teams to

develops the application consequent to the norms contained in this Manual, thus
as well as its maintenance, depending on the new circumstances or needs.
In particular, the correct interpretation, application, and corresponding maintenance,
according to the areas to the following people:

Database Administrator
Physical Design of the Database
Index
Clusters
Database

Management and Administration - IT Page: 1

 Programming Standards
Table Space
Rollback Segment
Synonyms

Analyst
Logical Database Design
Tables
Fields
Table restriction
Views
Materialized Views
Sequences

Application Module Design

Modulo
Parameters
Data Structure
Triggers
Use of Data
Interfaces

Security Systems
Users
Groups and Hierarchies
Privileges
Audit
Programmer
T-SQL Language
ASP-NET Language
Report Services

Management and Administration - IT Page: 2

 Programming Standards

2 Databases

2.1 General Policies

2.1.1 Development to Production Pass

The DBA or responsible person will review compliance with the characteristics.
what each object (defined in the document) should have, for example
name format, primary keys, relationships between tables, types of
data, among others.

The deployment to production must be planned, if it is a module.

new, inform the responsible or DBA via an email with the
less than 8 hours notice to make a plan for the pass.

If it is an old module, inform via an email to

responsible or DBA preferably at least 1 hour in advance
and immediately, depending on the urgency to
proceed to evaluate the new objects created for database registration
of data.

All new database objects that were created without having

informed the responsible person or DBA, will be deleted without prior notice
communication, and the systems analyst will be informed with
Copy the management by email indicating the action taken.

The person in charge or DBA is obligated to monitor the

performance of the new objects created in production at least
for a period of 2 weeks, to ensure the availability of the
systems maximizing response times for the user
final.

The Systems Analyst, before requesting production release, is in the

obligation to have carried out all the relevant tests for the case,
carefully reviewing the response time, errors of
programming, impact on other systems, among other controls that
each analyst finds it appropriate to carry out.

If the new objects intended to be put into production do not meet

with what is specified in the document, the System Analyst is in the
obligation to make the suggested changes or improvements by the
responsible or DBA.

If it concerns ancient objects in the database, where the analyst

the system detects that a bad programming practice was used
(For example: SELECT * FROM table_name), the analyst can
make the change without prior communication, all you have to do is

Management and Administration - IT Page: 3

 Programming Standards
specify according to format the changes made to the object, and it
must ensure that the functionality is correct, subsequently to the
The DBA will receive an email sent by the server indicating the change in
the object and this must be under the responsibility of reviewing.

The production deployment should be done in the early hours of the day, from
Monday to Friday from 8 am to 3 pm at the latest, for the purpose of
ensure the presence of the system analyst in case there is a
incident.

The programming of stored procedures, functions and

Triggers should maintain an order in coding using TABS,
they must be differentiated from each other visually
streamline the understanding of the implemented logic.

Example:

. Avoid executing queries in the production environment, for that

there will be a QA environment with a daily update frequency,
The non-compliance will be reported to the management.

Management and Administration - IT Page: 4

 Programming Standards

2.2 Test the database objects

The responsible person or DBA is obliged to conduct a test on the objects.

that will go into production, which must meet the following
specifications:

The execution of a stored procedure that is of type INSERT or

UPDATE should not take longer than 1 second, preferably it has to
be zero.

The execution of a stored procedure that is of type SELECT is not

It should take more than 2 seconds, preferably it has to be zero.

If it is a stored procedure that will be executed by a

JOBS, and it will be executed periodically during the day, this should not pass
of the 3 seconds.

If it is a stored procedure that will be executed by a

JOBS, and it will be executed only once a day, it should not exceed the
10 seconds and it must be scheduled at night, where the
the number of connected users is minimal.

The database objects must be within a scope of

business (Table 002), if the new module belongs to an area that is not
registered, the DBA is obligated to register the new data.

2.3 Programming Recommendations

Do not use the statement 'SELECT * FROM TableName', it is recommended.

mention the columns you wish to view.

One should not perform a "SELECT COUNT (*) FROM TableName", it is

It is advisable to use the primary key column for counting.
SELECT COUNT (PrimaryKey) FROM TableName

There are cases where a query is made to the same table within the
SELECT
SELECT column1, (SELECT column5 FROM Table1 WHERE
column9=522) as column5, column6 FROM Table1 column9=522
it is advisable to use a LEFT JOIN.

For the use of temporary tables, the amount of data that it should hold
it should not exceed 5000 records, because this object makes use
exclusively from memory.

Reduce the use of cursors, unless it is the last resort.

Management and Administration - IT Page: 5

 Programming Standards
If the query is a combination of several tables, a first one must be generated.
SELECT with the main table and get the required amount of data and
subsequently proceed to relate them to the other tables.

Reserved names will always be written in UPPER CASE and the

table attributes in lowercase.

3 Database Design

3.1 Generalities

Database objects have a generic nomenclature, identifying

the type of object, the business scope, and the name of the object.

Nomenclature:

Object Type

The names of database objects should be in singular and

in lowercase.

Example:

tb_ges_cliente, (it is wrong to put tb_ges_clientes)

In case the object name is a compound name, it must be

to use camel case, this must start with a lowercase letter
AndTheFollowingWordsStartWithCapitalLettersWithoutAnySeparation.

Example:

tb_ges_daily_balance

Object names should not contain articles or pronouns.

Example:
The table of 'Customer Transactions' would be called the
next form:

tb_clientTransaction

I exclude the article and each word was placed in

lowercase.

Management and Administration - IT Page: 6

 Programming Standards
The chosen name should be as descriptive as possible, avoiding
ambiguous terms or those that are subject to different interpretations, the
The total size of the object's name must not exceed 32 characters.

Related objects should be separated with the symbol _ and named.

using the names in reverse order, following a logical sequence of
phrase.

Example:

Table 'Customer Address'

tb_customer_address

Related to the table tb_ges_cliente

Except for compound words that use camel case notation.

(example: daily balance the blank space is nullified and the following
word starts with a capital letter, do not use the symbol '_' to separate the
words) .

Using the symbol_in object names identifies dependency.

For example, the tb_ges_cliente_direccion is related to the table
tb_client_customer and detail the customer's addresses (one to many).

idea
Object Type Description
Object
Destination table, whose source is originated
Td SQL Table
from an external data source (ETL)
Base configuration or master table
Tb SQL Table
internal features of the application
Grouping table, originated as a
You SQL Table summary of one or more destination tables, for
to optimize the issuance of reports.
Temporary table, used to store
temporarily the data in a
Tt SQL Table processing or generating reports,
the content can be deleted in
cleaning process.
Intermediate table, image of a table
external source, used to speed up the
You SQL Table
load and use stored procedures
you transform the data in the ETL process
Tk SQL Table Backup Tables
Pk SQL Index Primary index
Ak SQL Index Alternate index

Management and Administration - IT Page: 7

 Programming Standards
Type of
SQL Object Description
Object
Fk SQL Constraints Foreign index
Dg SQL Diagram Entity-relationship diagrams
SQL Store
up Stored Procedures
Procedure
Fn SQL function Functions
Jo SQL JOB Execution schedule jobs
Rp [Link] FILE Report File (SSRP)
Ap [Link] FILE Program file (aspx)
The [Link] FILE ETL File (SSIS)
File such as Excel sheets or files
Fi FILE
texts
Sc SQL Script Command execution script file

Scope of
Description
Business
ges Management
gen General procedures and functions
seg Security
tar ETL execution tasks
Systems for the use of documentation and procedure
sis general development utilities or
maintenance.
ren Profitability
with Monitoring of compliance indicators
crab Management notebook
pre Budget system (bifopres)
spr Proposal follow-up
Any other business area must be registered in
*
the table of tb_seg_aplicacion

3.2 Rules for Naming Tables

The tables must be created according to their conceptual representation in the

business scope that allows them to be categorized and facilitates their location, using the
general nomenclature defined in paragraph 3.1:

The number of columns in a table must not exceed 32.

columns, this will prevent having columns that are only valid for
some records and the loading of the data in a query will be more
fast.

Every table must have one or more 'PRIMARY KEY' fields.

Management and Administration - IT Page: 8

 Programming Standards

The key fields must be located at the beginning of the table definition
(they must be the first).

Every relationship between tables must be implemented using constraints.

(foreign keys) with referential integrity.

3.3 Rules for Naming Fields

The fields are open and it should be as explicit as possible, use dashes
to separate words and not exceed 32 characters, do not use characters
specials like accented words, eñe or symbols, the names must
to be in singular.

There are prefixes that summarize the first word of the field, they are 3 characters long.
what indicates the function of the field and the other characters that express the detail,
always written in lowercase, this standard must prevail since the
it will be used in the development environment and will help reduce time
in the monitoring control.

Prefix Description
can Amount
cod Code
of the Description
directory Address
is State
fec Date
grp Group
idea Identifier
imp Amount
name Name
num Number
for Percentage
tip Type
tot Total
var Variation

The fields must be in the form XXX_<field>, that is

Examples:

Customer code.
Customer address.

Management and Administration - IT Page: 9

 Programming Standards

3.4 Rules for Naming Stored Procedures

The name of the stored procedure must indicate with the identifier of
up procedure.

The name of the stored procedure must indicate the business scope to
Which procedure is being affected.

The name of the stored procedure should indicate the action that it is going to
generate, the permitted actions are as follows:

Prefix Action Description

self Listar, Buscar Show a list of the data.
cud Create Register the data in DB.
Carry out various processes, for example.
pro Process
average calculation
Show a list of the data that
rep List
they are a source for a report

Table 003: Actions for the SP

If the procedure refers to a single table, it must have the

same name of the table, to which the action is added within scope
of business and the name.

Example:

The table is called tb_gen_balanza and we are going to create the SP for the record.
the name of the SP will be: up_gen_upd _balanza

If the procedure refers to several tables, the SP must have the

business scope of the main table, then the action is specified
followed by the name of the main table and then the abbreviated name of the
event.

Example:

They ask us to visualize debtor clients, and the clients table is called
td_customer, the SP should be called up_ges_sel_debtorCustomers.

There will be some exceptions where the procedure refers to

several tables, the SP must have the business scope 'ges', then it
specify the action and then the abbreviated name of the event according to
the interpretation of the systems analyst.

Example:

They ask us to visualize indebted clients and how many invoices were issued, the SP
it should be named up_ges_sel_deudaCantidadFactura.

Management and Administration - IT Page: 10

 Programming Standards

The number of characters in the name of an SP must be less than or equal to

32 characters.

The number of columns to return from a stored procedure must be less than or equal to 32.
columns.

The relationship between words in the name of an SP must begin with

lowercase and should not be separated by hyphens, the separation of
Words start with a capital letter (camel case).

Example: up_ges_sel_debtInvoiceAmount.

3.5 Rules for Naming Functions.

You must identify the business field where the function will be used, if
it is a general function, using the 'gen' scope.

The name of a FUNCTION must indicate the action that it is going to

generate, the permitted actions are as follows:

Action Description
salt Show a list of the
data.
Used for functions
SQL Table-Value Type
cal Returns a result of
an operation.
Table 004: Actions for the Functions

The name of the function must start with the prefix "fn" in lowercase,
followed by a hyphen, the action to be generated is added (previous box)
and then a name is added that will describe the event in more detail
And it must start with a capital letter.

Example:

It is necessary to list the states of a field and it is required to be

through a function, the name must be: fn_ges_sel_estado.

It is necessary to add two numbers and it must be

through a function, the name must be: fn_gen_cal_sumNumber.

The number of characters in a FUNCTION name must be

less than or equal to 32 characters.

Management and Administration - IT Page: 11

 Programming Standards
The number of columns to return from a function must be less than or
equal to 16 columns.

The relationship between words in a function's name has to start

with uppercase and should not be separated by hyphens.
Example: fn_gen_cal_sumNumber.

3.6 Rules for Naming Triggers.

In the name of a Trigger, the action in which it will be executed must be indicated.
the permitted actions are the following:

Action Description
ins It will be executed when an INSERT is performed.
upd It will be executed when an UPDATE is performed.
It will be executed when a DELETE is performed.
cud It will be executed when an insert, update or
delete.

The name of the Trigger must start with the prefix 'tr' in lowercase,
followed by a dash, then the identifier of the business area (the
same as used in the table), followed by a hyphen, then the action that
finally perform and the name of the table (without the business prefix).

Example:

A trigger needs to be created for the table tr_ges_balanza when be

insert, the name must be: tr_ges_ins_balanza.

It is necessary to create the trigger for the table tb_vta_factura when himself
delete the data, the name must be: tr_vta_del_factura.

3.7 Rules for naming Jobs.

The name of a JOBS must start with the prefix "jb", followed by a
script, and then add the affected application code and finally
A name that briefly describes its objective.

Example:

It is necessary to create the jobs to delete the null records from the table
tb_vta_invoice, the name could be:
jb_ removeNullRecordFact.

The number of characters in a JOBS name must be less than or

equals 32 characters.

Management and Administration - IT Página: 12

 Programming Standards

3.8 Rules for naming VIEWS.

The name of a view must start with the prefix 'vw' in lowercase.
followed by a hyphen, then the name of the business sector, and
finally the name that briefly describes the objective of
same, remember that they must be in singular.

Example:

A view needs to be created to list the customer records with

invoice the name could be: vw_ges_clienteNuevo.

The number of characters in the name of a view must be greater than

32 characters.

The relationship between words in the name of a view has to start

in lowercase and should not be separated by hyphens.

Example: vw_ges_clienteVinculado

3.9 Format for documenting objects.

Stored procedures, functions, and triggers must have

obligatorily a detail of its reason for existence, this format
will allow detailed knowledge of the changes that were made to the
objects over the course of time.

The format to follow is the following, and must be after the word
of the Object.

-- =============================================
-- Author: jmieses
Create date: 06/02/2012
Update the Application master
-- Updates: 06/02/2013 fcossio @001 I add the field
-- application_tip
-- =============================================

It must be specified in the object code the item number where

the change or increase of functionality was made.

-- START @001
SELECT * FROM tt_test_trial
-- FINAL @001

Management and Administration - IT Page: 13

 Programming Standards

4 Recommendations

The recommendations shown below have been provided by the team of

Total Management Systems SAC and collections by BANBIF:

The optimization of the programs depends on who develops the module and must
rely on the statement 'SET EXPLAIN' to see the execution plan of the queries.

Any changes to the DB (field, table, index, etc.) must be reported to a

the person in charge is the DBA, who will in turn notify the rest of the staff.
It is advisable to use an email for control.

When there is a need to completely delete all the content of a table, one
It is recommended to use the TRUNCATE TABLE command for these cases.
The command "DELETE FROM tablename" should not be used because it makes a
sequential traversal of the table to delete it.

Integer vs. Smallint. Advantage: Storage size and maximum range. In the case
The Integer has greater storage because it is 4 bytes while the smallint only
it is 2 bytes.

Varchar vs Char. Advantage: The storage of a char makes the DB engine

fill in the blanks until the length is complete, in the varchar type it does not
fill in the blanks.

Decimal vs Float. Advantages: Decimal is a proprietary data type of the engine, the
Arithmetic operations with this type are first processed by the engine, then passed to the
processor, while operations with a float, integer, and smallint are performed
directly the processor, however the advantage of Decimal is the length in the
decimals, that is, it already performs the rounding function when storing, which is why it is more
used.

Creation of indexes. Advantages: Minimizing the number of indexes in a table optimizes

access to data and indexing time. In a primary DB architecture and
secondary, when a re-indexing operation occurs, at the end, all pages
Indexes are placed in the replication buffer for the secondary database to receive them.
It is important to reach an agreement at the time of designing the indexes to avoid that
During programming, there is redundancy in the indices. Avoid using indices with fields.
of Char type whose length is large. It is important to consider the volume of
Table information. It is used in filters, Joins, Order By, Group By.

Primary keys. Advantages: Primary keys create a UNIQUE type index,

reinforces information integrity. By definition of integrity, primary keys
no nulls accepted.

Foreign keys. Advantages: Strengthens the integrity of the information. It relies on

indices (duplicate type) in the tables. By not using: A lot of foreign keys in a table,
generates too many indexes.

Management and Administration - IT Página: 14

 Programming Standards

Default values. Advantages: Reinforces data integrity by making

default values should be stored and not NULL values. The database has control over it.
of data and not the application.

Transactions. Advantages: Integrity and consistency of the data, the server guarantees
that the operations carried out within the limits of the transaction are complete
on the disk, or in case of a failure during its execution, the database is
will restore to the point before the execution of the transaction. The time that is
keep the record updated within the transaction must be minimal and thus achieve
greater concurrency and availability. Consistent replication. Not using: When the occurrence of a
interruption of a data update, it cannot be ensured how much of the operation
It was carried out, even in a single record operation, it is not possible to know if the data
it was updated on the disk correctly, or if the involved indices were updated
correctly. There is even the possibility of leaving the pages of the disk
inconsistent. In a replicated environment it is not guaranteed that the secondary server
reflect the changes made on the main server. In read-only processes, no
to use transactions.

Short transactions. Advantages: The less time a record is kept

blocked, greater availability of data for the rest of the users, which implies
higher level of concurrency. Transactions remain open for a longer time
in the transaction log, delaying backup activity, and therefore less
availability of space in the logs to write more transactions, causing this
a Long Transaction, in which case the process is aborted by the handler,
and depending on how full the logs are, activity could be suspended for the
rest of the users, while this process performs the rollback.

Indexes. Advantages: In an online transaction environment, access is important.

directly to the data, the blocking of records individually and their direct update
provides a higher level of availability of the rest of the records. The presence of
indices can help reduce response time. By not using: It can cause
a sequential reading, which is not beneficial if it is about an environment
transactional, where minimal response times are required.

Select the necessary columns in a SELECT statement. Advantages: The

the handler has the ability to read through the index pages when the
mentioned columns are those that participate in a particular index, this type of
reading is the fastest and most efficient. By not using: The internal buffers used by the
clients are of a fixed size. The more data is selected, the more context switches occur.
will be required to send the data from the server to the client. Filling the buffers involves
greater traffic between the client and the server process, which has a direct impact
in the performance.

TODO query must access the tables using indexes (unless the SQL decides not to)
use them). This implies that EVERY table, including temporary ones, must have an Index
Clustered. Optionally, one or more Non-Clustered indexes can be placed.

Management and Administration - IT Page: 15

 Programming Standards
In the WHERE clause never use functions on columns. The function must be
about the constant with which it will be compared.

Example
Do not use: WHERE CONVERT(VARCHAR(10), fec_poliza, 121) = '20051201'
Use: WHERE fec_poliza = CONVERT(DATETIME, "20051201", 121)

In the WHERE clause, never use operators with columns from a table. The
Operators should be placed over the constant with which it will be compared.

Example
Do not use: WHERE num_poliza + 600 = @variable_poliza
Use: WHERE num_poliza = @variable_poliza - 600

All temporary tables must be deleted, there should be no tables with tt_ in the databases.
Production Data.

Each Stored Procedure must include in the header the author's name, the date, and a
brief description of the objective of the SP.

Avoid dynamic queries by concatenating values separated by commas, such as

Example:
DELETE FROM td_ges_direccion WHERE cod_direccion IN (#1, #2, #3,..#N)

Instead of this, place the values in a table in such a way that it can be done.
crosses with other tables, such as for

Example:

DELETE td_ges_direccion
FROM DIRECTIONS, #tt_ges_temporal
WHERE td_ges_direccion.cod_address =
tt_ges_temporal.address_code

Management and Administration - IT Page: 16

 Programming Standards

4.1 TIP for programming in T-SQL

4.1.1 Reading

Avoid using SELECT *. Always read only the columns you need. With this
You avoid taking a lot of unnecessary data to the client, so it
it decongests the network and the client feels that it is faster.

For queries of record lists, use the TOP n operator. With this
we avoid bringing many records to the client. The network is also unclogged
and the customer feels that it is faster.

Instead of SET ROWCOUNT n, use TOP n.

If you use the UNION operator and you are sure that both Queries DO NOT have
duplicate records, so it's better to use UNION ALL, to avoid that
implicitly the DISTINCT operator is used.

Avoid using SELECT ... INTO table_name. This will block system tables.
Instead of this, first create the tables and then rewrite the statement.
like INSERT INTO table_name SELECT ...

If you are going to read data from a single table, avoid doing so using views that use
other tables.

4.1.2 Joins

Write Joins in ANSI format (use the JOIN .. ON clause). This ensures you
to write down all the restrictions without the possibility of forgetting any restriction.

Avoid using the same table more than once in a single query. To improve this, use
temporary tables.

Prefers to use a Join instead of a sub-query.

For example, instead of using:

SELECT member_number, first_name, last_name, room_number

FROM members
WHERE room_number
IN (SELECT rooms.room_number FROM rooms)

Management and Administration - IT Page: 17

 Programming Standards

Use
SELECT member_number, first_name, last_name, room_number
FROM members m
INNER JOIN rooms r
ON m.room_number = r.room_number

Instead of using a statement with many Joins where the involved tables are
great, better create a temporary table with the data from the main table (codes)
and then update this table by joining with the secondary tables.

Instead of using the IN clause together with a sub-query, use a statement

JOIN.

For example, instead of using

SELECT publisher_name
FROM tb_gen_publisher
WHERE cod_publisher IN
(SELECT cod_publisher FROM tb_gen_title)

Use:

SELECT publisher_name
FROM tb_gen_publisher
INNER JOIN tb_gen_title
ON tb_gen_title.cod_publisher = tb_gen_publisher.cod_publisher

4.1.3 Where

Avoid using functions on columns in the WHERE clause

For example, instead of using

SELECT member_number, first_name, last_name

FROM members
WHERE DATEDIFF(year, dateofbirth, GETDATE()) > 21

Usa:

SELECT member_number, first_name, last_name

FROM members
WHERE dateofbirth < DATEADD(year,-21,GETDATE())

If you use LIKE in the WHERE clause, try to use at least 3 characters.
forward as "abc%"

If you use LIKE in the WHERE clause, avoid using the % operator at the beginning: "%abc"

Wherever possible, use the BETWEEN clause instead of IN.

Management and Administration - IT Página: 18

 Programming Standards

For example, instead of using

SELECT customer_number, customer_name
FROM customer
WHERE customer_number in (1000, 1001, 1002, 1003, 1004)

USA
SELECT customer_number, customer_name
FROM customer
WHERE customer_number BETWEEN 1000 and 1004

Avoid using concatenations in the WHERE clauses

Avoid using the operators OR, NOT IN, NOT BETWEEN, <>, NOT EXISTS, NOT
LIKE, LIKE '%ABC'

If a query has one or more OR operators, consider rewriting the query in several.
Queries and combine the results using the UNION operator. Remember to use
UNION ALL, if possible.

4.1.4 General

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 field.
identity.

Consider that the MIN and MAX functions can use indexes. If possible, create
indexes on the columns on which these functions are used.

When using temporary tables, consider creating indexes for better performance.
performance of your Queries.

Review each query of a stored procedure to ensure that it does not perform a 'table scan'.
(non-indexed search). To study this, activate the "Show Query Plan" directive in
the "Query Analyzer".

Try to ensure that each query uses an index on a column that has a high
dispersion.

Use derived tables whenever possible instead of temporary tables. This is more
reasonable if the information you would store in the temporary table is going to be used
Once. But if you plan to use this data many times within a stored procedure.
Procedure, then it's better a temporary table.

4.1.5 Transactionality

Management and Administration - IT Page: 19

 Programming Standards
Never break a transaction into two transactions that are invoked.
consecutively from the client. This means that if the second transaction fails, the
the database became corrupted.

Make sure your transactions are small, meaning they access the least.
number of pages in the database.

Avoid declaring a single large transaction for batch processes. It's better to have
various small transactions and handle reprocesses.

Avoid using nested transactions.

Management and Administration - IT Page: 20