Database Development Standards
CodeReviews
QuickTips
FileExtensions
Joins
Lockingand Optimisation Tips
Writingdynamic sql
ReferentialIntegrity Constraints
Definingviews
Definingstored procedures
Callingstored procedures
Transactions
NestedTransactions
Identitycolumns
AllocatingIds
SoftwareConfiguration
Deadlocks(link)
Style
TableAccess Orders and Deadlock Prevention
DDLReviewer Guidelines
Code Reviews
Ensure that you get your changes reviewed before the DDL package is beingbuilt. The group to send your review requests to is*GT Global EQTG DDL Reviews.Please try and submit the changes for review at least a day before the packageis being built (preferably when you check the file into VSS).
The current reviewer can be found on the DDL Review Rota, which can be foundhere.
Quick Tips
- is null -- this is the correct way to check for null
= null - if exists (select 1 from .. where ..)
if exists (select * from .. where ..) - select b from t where a = 'A' -- single quotes are easier to handle in code than double quotes
select b from t where a = "A" - exec foo
foo -- less clear + doesn't work if there are earlier commands in the batch - Try to avoid writing SQL which uses Sybase-specific features
- Follow sp_rename with sp_recompile otherwise references not updated
- Sybase DBA team good source for ideas and solutions
- List insert columns explicitly, e.g. insert trade (id, quantity) select tradeId, quantity from ..
- If you want to pass in a list of items to a stored procedure, you should typically create a temporary table and populate it, rather than having arbitrarily long lists of parameters
- Don't use drop object and grant x on objectY to z style statements to drop objects and set their permissions. Use theDatabase Object Permissions equivalents.
- When calling one stored procedure from within another, ensure that you pass parameters by name. This gives a clearer indication of what each parameter is doing, and also allows parameter re-ordering. (Note that it excludes parameter renaming, but assuming that you're changing the parameter name in the internal stored procedure, you'd probably need to change the calling stored procedures anyway, so this shouldn't be a big deal.)
- @@error is reset after each SQL statement is executed, so it is not possible to check after a sequence of statements whether any of them have gone wrong - you need to check after each statement.
File Extensions
You need to ensure that you follow the guidelines for filename extensions:
bddfl | bind statements for rules |
bdrul | command grant/revokes |
dfl | defaults |
idx | index |
keys | primary/foreign keys |
rul | rules |
sp | stored proc |
tbl | table creation scripts |
trg | trigger |
typ | user defined types |
typdfl | default binding for user types |
typrul | rule binding to user types |
ubddfl | unbind defaults |
ubdrul | unbind rules |
vw | views |
sql | insert/delete statements, or anything not covered by the other script types. |
Joins
Use ANSI-92 standard join syntax, rather than Sybase Transact-SQL joins.ANSI joins work as follows:
left_table [inner | left [outer] | right [outer]] join right_tableonleft_column_name = right_column_name
Avoid Transact-SQL syntax, which joins in the where clause using:
left_column_name [*= | = | =*] right_column_name
ANSI joins are preferred as it makes it clearer which conditions are relatedto joining the table, and which conditions are used to filter rows. It is alsocompletely portable to any ANSI-compliant database engine.
Locking and Optimisation Tips
- When joining three or more tables by a common column, explicitly list all the joins, e.g. B.id = A.id and C.id = A.id and C.id = B.id. This gives the optimiser the best chance of finding the most optimal query plan. NB the JTC (Join Transitive Closure) setting is Off by default at both the server and session level. Exceptionally you may not want to do this, e.g. if forcing the query plan, running dynamic sql or stored procs 'with recompile'.
- set showplan on -- shows you the query plan - avoid table scans through large tables
- Think about order of access when locking tables with a view to reducing chance of dead-locking.
- Make sure that orderrd results sets are done in the same direction to cut down the chances of deadlocks
- Try to minimise the transaction window. Sometimes you can significantly reduce the time you lock a table for by preparing the update in a temporary table first.
- Isolation levels
- 'read uncommited' allows dirty reads. Your select will notblock and the data you read may be part of an update that is laterrolled back. Be sure you understand the consequences before using this option.See Reference Manual.
- The default isolation level for Sybase is 'read commited'. This avoidsdirty reads by blocking until external updates to data that it istrying to read are either committed or rolled back.
- You can use the holdlock option on selects to lock the data you haveread. You may need to do this if you intend to read or update that data laterand you need it not to have changed in the meanwhile. Obviously this is not agood thing to do from a locking and performance point of view, but it may benecessary.
Writing dynamic sql
Reading or writing data - avoid using dynamic sql. An appropriatelynamed stored procedure will encapsulate functionality much better, allowre-use and makes dependencies much clearer.
You can also go back later and add things in rather than be forced to writea trigger.
Defining tables
All tables should be created with suitable index(es).
Referential Integrity Constraints
Please apply referentialintegrity constraints to your new tables. The m ain benefit is betterquality data. The main c ost is a performance cost for inserts/updates to thereferencing table and deletes/updates to the referenced table.Youshould therefore exercise care before adding constraints to existing tables.
Referential Integrity constraintnames must be unique. The following naming convention should be used:fk_<referencing_table_name>_<column>. Sofk_userGroups_databaseGroupId contrains the databaseGroupId column in theuserGroups table to the set of values defined in another table column.
Still under discussion:Referential Integrity creation scripts located in/called$/SQL/RI/<referencing_table_name>_RI.sql. Scripts to drop referentialintegrity constraints called$/SQL/RI/<referencing_table_name>_RI_drop.sql
Defining views
Unless there is an overidding reason you should create a storedprocedure in preference to a view. This will allow you to accomodate futurechanges more easily.
In particular with views you are restricted to a single select statement- you cannot use unions or temporary tables.
Defining storedprocedures
- Put SQL in stored procedures rather than inlining. This is partularly true for updates - there is always a strong likelihood of adding audit or debugging code to stored procedures, which is easier than modifying and deploying code.
- For audit requirement, please try to use the stored procedure "createAuditTrail"
- parameters - carry out pre-checks on the parameters passed in
- error-checking - consider what you want to happen in the event of error after every single SQL statement, especially table update statements and stored proc calls.
- return codes - return 0 for success, positive number for success code, -100, -101, .. for error codes - different one for each error within the procedure. Codes 0 to -99 are reserved - see Reference Manual.
Calling stored procedures
- return status - check the return status after calling a stored procedure and handle appropriately
- parameters - dynamic SQL which calls stored procedures should enclose any string parameters in quotes. Consideration should be given to the possibility the string will itself contain quotes.
- Nesting level - be aware of the limit (currently 16) and do not write recursive procedures - use iteration instead.
Transactions
- Before using transactions, consider if there are any good solutions which do not require you to use transactions
- Do what you can before you start the transaction, quite often you can put things into temporary tables first and then start the transaction
- If you need to begin a transaction, consider the possibility you may already be running in a transaction and use save tran if appropriate
- Within a transaction, you should check after each statement as to whether it has succeeded or not. Unless there's a specific reason not to do so, the transaction should be rolled back if an error is encountered at any point.
Nested Transactions
There are a few things to note when using transactions. If you have not usedtransactions before then it is well worth reading this section and the SybaseReference Manual.
Commands
begin tran, save tran, commit tran, rollback tran
Each command can be followed by a transaction name. This is not alwayssignificant. The checkpoint command is not described here. Please refer to itsuse in the Reference Manual if using sp_dboption within a transaction.
begin tran
Each begin tran increments @@trancount. This system variable keeps a trackof transaction nesting depth and is initially zero.
commit tran
Each commit tran decrements @@trancount. When @@trancount reaches zero thewhole transaction is commited.
rollback tran
If rollback tran is encountered at any point before the whole transactionis commited, the whole transaction is unwound and @@trancount reset tozero. The only exception is when you have specified rollback to aparticular Save Point (see Save Points section). The followingdemonstrates that commit tran unwinds the whole transaction:
begin tran
insert Tmp select 'This gets rolled back also'
begin tran
insert Tmp select 'This gets rolled back'
rollback tran
commit tran -- @@trancount is already zero so this is notmeaningful
go
Note special rules apply to triggers. Please refer to the Reference Manualsection on Transactions within Triggers.
Save Points
You specify a Save Point if you may need to partially unwind a transaction. Thefollowing demonstrates this.
begin tran
insert Tmp select 'This gets saved'
save tran InnerTransaction1
insert Tmp select 'This gets rolled back'
rollback tran InnerTransaction1
commit tran
go
Practical Example
Say you had to write a stored procedure that could be called from a variety ofdifferent places. You don't know whether it will be called from within atransaction and whether if you can't complete your bit, the wholetransaction should be unwound. Here's how you might write it:
create proc queueTrade (@id int) as
begin
declare @trncnt int
declare @rows_affected int, @err int
-- begin transaction
select @trncnt = @@trancount
if @trncnt = 0 begin tran
else save tran t1
-- mark the trade as queued, thereby also locking it
update trade set queued = 1 where id = @id and queued =0
select @err = @@error, @rows_affected = @@rowcount
if @err <> 0 or @@rowcount <> 1
begin
rollback tran t1
return -100
end
-- queue the trade
insert outQ select id, data from trade where id = @id
select @err = @@error, @rows_affected = @@rowcount
if @err <> 0 or @@rowcount <> 1
begin
rollback tran t1
return -101
end
-- commit transaction
if @trncnt = 0 commit tran
return 0
end
An alternative way of doing this is:
begin tran
save tran t1
..
if some_error
begin
rollback tran t1
commit tran -- thismatches the begin tran to reduce @@trancount back to its former level (noterollback to a savepoint does not do this)
return -100
end
commit tran
return 0
Identity columns
- There are many cases where using an identity column can cause problems later on so be wary.
- A good alternative is to use a normal (non-identity) column and using a stored procedure to allocate ids. See Allocating Ids below.
- Possible problems include: Not being able to modify the values later, not being able to bulk allocate the ids in advance of usage, the 'identity burning set factor' can create large gaps in ids unless you're careful.
Allocating Ids
- When creating a numeric column which simply needs to increment with each row (e.g. trade id in trade table), you needa way of allocating ids that works when you have several instances of an application simultaneously trying to insert to the table.
- The best way to do this is to have a counters table which holds key, nextId pairs. A stored procedure then takes the key and the number of ids requested. It then begins a transaction, increments the relevant counter (which locks the row or table as well), returns the new counter value-the number of ids requested, ie the first id in the block allocated, commits and returns.See stored procedure CDM_unique.
Software Configuration
- One of the many ways available to you when configuring your software is through data held in the database.
- You should first decide the different configurations you want to be available and how they interact. For instance do different users require different configuration? In this case you may find the RMSgroups/RMSgroupDescriptions table meets your needs.
- The CDM_system_variables table is available for global configuration variables. Examples of usage include:
- server status, e.g. MyServer.Status = Stopped | Starting | RunningOK | Unprocessed Transactions | Stopping
- configuration variable to indicate phase of operation for a sub-system,e.g. MySubSystem.Mode = Disabled | Evaluation | Parallel | Live
- configuration variable to indicate mode of operation for asub-system, e.g. EQRms.EquitySwap.Teddies.Enabled = N | Y orEQRms.EquitySwap.Teddies.Implementation = Disabled | Default | Super Ted
Example code to retrieve CDM_system_variables data:
EQString mode = EQSystemConstants::stringVariable("MySubSystem.Mode");
- Do NOT use the isLondon(), isTokyo() ... functions. This makes it impossible to use functionality developed by another region. Even for seemingly location-related issues such as timezones, there are better solutions.
Style
- One object per file except indexes/constraints may be in table file
- Indentation - indent each block by four characters
- Tabs - avoid using tab characters as this causes problems when running the script through sqsh
- Statements - split long statements as in the example below
selectntLogin = U1.detail, name = U2.detail
fromSECuserDetails U1, SECuserDetails U2
whereU1.type = 'NTLN'
and U1.detail like 'd%'
and U2.userId = U1.userId
and U2.type = 'NAME'
orderby U1.detail
Where the select columns become difficult toread, please split these also:
selectntLogin = U1.detail
, description = case when U2.detail = U3.detail then U2.detail
else U2.detail + ', GNAM ' + U3.detail
end
, ..
..
- Case - the first word/abbreviation in a column name or variable should be all lower case. Subsequent words should start with a capital letter / abbreviations all upper case, e.g. extractDDIPrices, but ddiAltIds.
- Style - when making changes to existing files, maintain the style within the file unless you are changing the whole file, e.g. case used for keywords.
- Select Fields - list all fields explicitly rather than using select * and qualify them all when writing multi-table queries, e.g. selectU.detail, E.f_name from ..
- Comments - consider writing a comment at the top of each block of code to say what it does. Avoid adding the following: change log, date/time stamps, author (these are in source control); lots of pretty *'s and indentation (hard to maintain).
- Parameters - explain input/output parameters to stored procedures and give typical usage examples.
- Parameter/variable types - parameters and variables in stored procedures should match the column definition to which they are to be compared.
- Use @@error, @@rowcount and @@sqlstatus in your error handling. Also use @@identity.
- System dependencies - avoid writing SQL which may break when the dataserver is upgraded - avoid accessing system tables directly if possible.
- Undocumented functionality - avoid writing SQL which depends on observable but undocumented functionality, e.g. use an order by clause if you need data to be in a particular order even if it appears to work without it.
- Null - when checking for null use is null, is not null or isnull() rather than the standard equality/inequality operators.
- Quotes - use single quotes rather than double quotes.
- Queries - always try to use a set-based solution before implementing a temporary-table based or cursor-based solution.
Table Access Orders and Deadlock Prevention
Deadlocks occur in a database when two (or more) transactions are waitingfor resources held by the other(s). The best way to avoid this scenariooccurring is to access all resources in the same order. The DDL Review teamhave divised the following table order to which all queries must adhere:
ETSfinancialInstrument |
ETSalternativeIdentifier |
ETSderivative |
ETScompoundSecurity |
ETScompoundSecurityList |
PositionDescription |
Position |
Trade |
TradeCounterparty |
TradeExtraData |
TradeAltSym |
To what this applies:
Explicit Transactions, both declared on the connection and with in the query.
- All locks that are acquired are held for the duration of the transaction, thus all tables must be accessed in order.
- Minimising the length of the transactions reduces section of sql that ordering critical. For this reason it can be preferable to declare transactions in the sql script as opposed to on the connection.
- For long transactions, making changes into temporary tables outside of the transaction then flushing them into the actual tables within the transaction can be a very effective method for avoiding locking. Use this approach with caution however as it lowers the effective isolation level.
Single Queries.
- For most real world examples single queries can be viewed as individual transactions, in particular the join order in the FROM clause.
- As each table in a join clause is added a shared lock is typically acquired on it. For this reason join orders should also follow the same order described here. The join order describes the most natural way these tables will be joined so the optimiser will typically choose this order, but in some cases it won't. In such cases either using SET FORCE PLAN ON or breaking the the query into several smaller ones using temporary tables and variables may be necessary. Check the query plan to be sure.
DDL Reviewer Guidelines
Process
- file extension is correct
- one object per file except indexes/constraints may be in table file
- both current and new version under Source Control
- one-time scripts under Source Control ($/SQL/Scripts/<Year>/<Project> for London team)
- dropObjectAndPermissions / createDefaultObjectPermissions used
- Table alteration scripts should be able to be run multiple times (in case the package needs to be applied multiple times to the dataserver)
Developer-friendliness
- description of object at top of file
- clear, well commented code
- consistent use of case
- minimal code duplication
Quick checks
- select * not used
- insert columns listed explicitly, e.g. insert trade (id, quantity) select tradeId, quantity from ..
- != null, <> null not used; = null not used except for assignment
- no tabs (Find in Visual Studio supports finding tabs)
- return only used to return status (<= -100 for an error, >= 0 for success)
- suitable type used in variable declarations
- Join Transitive Closure attained explicitly, e.g. A.id = B.id and B.id = C.idand C.id = A.id
- appropriate use of >, >=, like, datediff, dateadd, ..
- use of ANSI-92 join syntax rather than Transact-SQL join syntax.
- ensure parameters are passed by name when calling inner stored procedures
- If a set rowcount x has been used, check whether it needs to apply to the whole stored procedure, or just part of it. (e.g. does the author want to limit the number of rows returned for all parts of the stored proc, or just for the first statement). If joining the initial returned set of rows in another statement against a different table, the set rowount may prevent the correct number of rows being affected in subsequent statements.
More subjective checks
- SQL is efficient
- global fit
- good use of transactions and error-handling
- appropriate use of temporary tables, cursors