Defect List

QCAR6Cartesian productPerformance
example
select t1.data
                from t1, t2
comment
This happens when two or more tables are not joined. The resulting query is not 'connected'. The number of rows returned by a cartesian product is equal to the multiplication of the number of rows of all the participants. It is very rarely intentional. Sometimes, a 'distinct' clause hides the cartesian product. This is why the 'distinct' clause is tagged as a warning. (In mathematics, given two sets X and Y, the Cartesian product (or direct product) of the two sets, written as X x Y is the set of all ordered pairs with the first element of each pair selected from X and the second element selected from Y.)
consequence
Performance hit and bad result set.
fix
Join the two or more tables which are not connected.




QFIB6Force index with bad indexPerformance
example
select * from t (index non_existing_index)
comment
Using a non existing index number or name is not flagged as an error in ASE but is certainly a mistake due to either a typo or a schema evolution
consequence
performance hit
fix
remove the force index or use an existing index




QBGB6Bad group byMisc
example
select max(id), name
from sysobjects
group by type
comment
When using the 'group by' construct, expressions which occur in the select list must be:
  • either expressions which appear in the group by clause or
  • aggregated expressions
Sybase authorize such constructs but they generally yield unexpected result sets, that are sometimes 'healed' by inapropriate 'distinct' clauses.
consequence
Bad query.
fix
This is the rule: the only non-aggregated columns appearing in a select list must also appear in the 'group by' clause.




MBPA6Not a parameter of this procMisc
example
exec myproc @dumbparam = 12
comment
This happens when you pass a parameter by name which is not a declared parameter of a called procedure. This does not fire a run-time error. However, this represents either either a typo error or an evolution of the called stored proc without cleanup of all calling instances.
consequence
In most progtamming languages, passing an unknown parameter would fail. In Transact-SQL, this is a silent error.
fix
Pass the right parameter or suppress the parameter from the call.




QTJ26Table only appears in outer join clauseMisc
example
select t1.*
from t1
    left join t2 on t2.i = t1.i
comment
The join on t2 is a useless join because no columns from t2 are used elsewhere in the query
consequence
Uneeded extra joins slows down queries
fix
Remove the join




MTDS6Too Many Database SwitchesMisc
example

        
    
comment
ASE (up to 15 so far) has a limitation of database switches in nested objects. For example, if proc A reads table B and table B is in a different database from proc A, this counts for one switch. The call fails when 8 database switches are reached
consequence
Proc call failure: error 925 sev 19 Maximum number of used databases for each query has been exceeded. The maximum allowed is 8
fix
Regroup objects in less databases




MURC6Unreachable CodeMisc
example
return
raiserror 20001 'Error'
            
comment
Unreachable code can never be executed because no code path leads to it
consequence
A common cause is inadvertant code modification
fix
Remove unreachable code or fix the premature return/goto/break/continue




MPTW6Param passed by name twiceVariable
example
exec proc
@p1 = 1
@p1 = 2
comment
A parameter is passed by name twice
consequence
fix
Probably a typo, remove.




VNDC6Variable is not declaredVariable
example
select @i = 12
comment
A variable is not declared. Note that the code won't compile, so you won't find this error in analysis. However, while typing, it is convenient to get this warning before submission.
consequence
Does not compile
fix
Declare the variable




PERR6Parsing ErrorRegular
example
comment
This is a SQLBrowser general parsing error
consequence
fix




QJWI5Join or Sarg Without IndexPerformance
example
comment
A join between two tables or a Sarg on a large table with no potential index to use. Joins normally happen between a primary key and a foreign key. The unique index representing the primary key should be selected.
consequence
Performance hit.
fix
Either add an index on the columns concerned if this join is causing performance problems, reconsider the query, or live with it.




QIMO5Table is inner member of outer join and also regular member of join at the same levelMisc
example
-- bad left join
select t1.i, t3.i
from t1
    left join t2 on t2.i = t1.i
    join t3 on t3.i = t2.i
go
--normal left nested join
select t1.i, t3.i
from t1
    left join t2
        left join t3 on t3.i = t2.i
        on t2.i = t1.i
go
-- non ansi form (fails to execute)
select 1
from t1, t2, t3
where t2.i =* t1.i
    and t3.i = t2.i
go
comment
In the first query we think that we will have as many rows as in t1, but it won't be the case because t2 is also inner joined with t3
consequence
Unexpected result set
fix
Nest Joins. Note: in non ansi join form, this query fails to run




MUTS5Update of Timestamp columnMisc
example
update table set timestamp=some_non_null_value
comment
updating a timestamp has theoretically no effect as the timestamp will be bumped by Sybase but this is considered as a bad practice
consequence
can lead to stack traces in certain ASE implementations
fix
Either insert null, or, better, skip the column. Skipping the column works even if the timestamp is not nullable




QCAS4Cartesian product with single rowPerformance
example
select t1.data
from t1, t2 where t2.i=1
comment
Two or more tables are not joined. (the query is not 'connected') When SQLBrowser detects a 'single row selection', i.e. a sarg qualifying a unique index on some participant tables, it produces this warning, which lessens the cartesian product issue, although it is still bad practice.
consequence
Maybe bad performance
fix
Join the two or more tables which are not connected.




QTYP4Comparison type mismatchPerformance
example
create table type_mismatch(
i smallint, j char(25), k int 
)
go
create index i1 on type_mismatch( i )
go
drop proc typeM1
go
create proc typeM1 @i int as
select * from type_mismatch where i = @i
select * from type_mismatch where i = convert(smallint,@i)
go
comment
When a column is compared with another expression of a type which is wider, e.g. smallint against int, the index will not be selected.
consequence
Bad performance
fix
Add convert() or change type of expression




QCSC4Costly 'select count()', use 'exists()'Performance
example
if ( select count(*) from sysobjects where type = 'P' ) > 0
begin
print 'Why don't I use if exists()?'
end
comment
It is common to see code using select count(*) > 0 to detect the existence of rows. Beginners often ignore the 'exists (select *' construct.
consequence
Performance hit.
fix
Use 'exists' as in
if exists ( select * from sysobjects where type='P' )
begin
    print 'Yes'
end




QPUI4Join or Sarg with Un-Rooted Partial Index UsePerformance
example
comment
The index selection uses a un-rooted portion of the full index. Partial index use can lead to suboptimal performance. This is worse than QPRI as it forces an index scan
consequence
fix
add missing columns to index




QPR14Potential Recompilation: index creation after table has been accessedPerformance
example
create table #t
insert #t -- FIRST insert is OK
-- any statement using #t
create index on #t

-- or

select * into #t
-- any statement using #t
create index on #t
comment
Index creation after table has been used may generate recompilation (15.0.3)
consequence
Lengthy Parse and Compile phases
fix
Create index on temp table *immediately* after first population (with *no* intervening insert/update/select statements). Reason: index creation generates a schema-change event, and causes Sybase to recompile the proc for all subsequent statements at *each* execution. In heavily used procs this can have a much bigger impact than having 100% correct statistics on the table/index.




QHNU4Unbalanced Optimizer HintPerformance
example
                
set basic_optimization on
-- no closing matching hint found later
            
comment
Unbalanced Optimizer Hint
consequence
Probably forgot to close the hint. Good habit is that hints are used in opening/closing pairs
fix
Add closing hint, i.e. set [hint] OFF




QFPU4Unclosed Force planPerformance
example
comment
Must close forceplan
consequence
unwanted forceplan on subsequent queries
fix
set forceplan off




MTYP4Assignment type mismatchMisc
example
declare @c char(10)
select @c = name from sysobjects where id = 12345
comment
This happens when the recipient variable has a type which is incompatible with what you try to put in it. This happens for instance when you assign a long string to a shorter string, or when you assign a float to an int. The default Transact-SQL behaviour is generally to silently ignore these truncations.
consequence
Bad data because of truncation.
fix
Enlarge the type of the recipient variable. Use proper convert to inform the reader that the truncation is desired.




MBRV4Bad return value [-1-99]Misc
example
return -6
comment
user stored procedures should not return values in the range [-1 .. -99] which are values reserved by Sybase
consequence
the client will not distinguish Sybase general errors from application errors
fix
the advise is to return 0 to indicate success and [-101, -102, .. ] to indicate failures




MCPC4Copy Paste CodeMisc
example
comment
Copy Paste Code is difficult to avoid in T-SQL, but some of it can
consequence
Harder code to maintain
fix
Views are an option but sometimes incur performance penalty, Stored procedure can help. Functions are genrally too slow. Pre-processing code at submission time can be an option




MEST4Empty String will be replaced by Single SpaceMisc
example
select c + '' + d from t
comment
An empty string ("") or ('') is actually transformed into a single space character. There is no such thing as an empty string in Sybase
consequence
One common consequence is when trailing spaces of string values are read by client code and the client code forgets to trim() the values. The semantics of string equality are less stringent in T-SQL than in client code: 'abc' and 'abc ' are equivalent in T-SQL, but not in Java for instance
fix
As one can not express an empty string in T-SQL, replace the constants by single space. It is more explicit




QFIN4Force index through index numberMisc
example
select * from t(2)
comment
Creates code which is too tight to index order creation
consequence
Use index name instead of index number
fix




MMCN4Potentially Misleading Column NamingMisc
example
drop table #t
go
create table #t (
    c1 int, c2 int
)
go
insert #t (c1, c2)
    select c2=2, c1=1
go
select * from #t
go
            
comment
Can give the impression that the value will go in the column named after the label whereas the value goes in the column matching the value's position
consequence
Wrong data in column
fix
remove naming - although it may help to leave them in very long lists




MRIT4Return in TransactionMisc
example
begin tran
return
commit tran
            
comment
It is a bad practice to use return statements within transaction boundaries. In general procs should not change the transaction level
consequence
Uneven transaction management
fix
use goto fail statament and test @@trancount and rollback if necessary after the fail label




TNOI4Table with no indexMisc
example
comment
This happens when table has no index at all. The only cases where this is admitted is for special tables which contains exactly one line, in which case they are not relational but just a collection of unrelated global variables. Every relational table must have an identifier, implemented through a unique index. "In a relational database, we never record information about something we cannot identify." [Date95] Read Relational Keys
consequence
This table will only be accessed through table scan.
fix
Add a unique index.




TNOU4Table with no unique indexMisc
example
comment
This happens when a table has no unique index. Any table must have a key, hence a unique index. A table without primary key is not a relational table. "The Key, the whole key and nothing but the key, so help me Codd." Unique indexes are usefull for both data integrity and for the query optimizer to find faster access plans. Read Relational Keys
consequence
Data quality is poor. Potential performance hit.
fix
Add one or more unique index ASAP




MUTT4Unbalanced Temp Table Creation/DropMisc
example
            
comment
Unbalanced creation and drop of temp tables in source file
consequence
It is bad to not drop all temp tables created in a source file. For instance optimized tools which can keep connections opened during a bulk release may fail because of pre-existence of temp tables when trying to create a new one with the same name
fix
Drop all temp tables created in a source file




MINU4Unique Index with nullable columnsMisc
example
comment
A Unique Index with nullable columns is bad practice
consequence
Bad performance because the index is less usable
fix




MUSP4Unquoted String ParameterMisc
example
exec sp_who sa
comment
Although passing a string parameter unquoted is allowed historically by Sybase, this is a bad practice. The main mistake is people thinking they pass a variable while forgetting the '@' sign in front
consequence
Proc compiles but gives wrong result
fix
Pass either (single) quoted strings or variables, but not naked identifiers




MURP4Unrecognized pragmaMisc
example
-- pragma acknowledge next defect XYZT comment
-- pragma dependency select mydb..mytable
comment
Unrecognized pragma may be due to defect code change or typo
consequence
fix




QUDW4Update or delete with no where clauseMisc
example
delete t
comment
This is flagged to avoid mistakes by which the where clause is forgotten. There may be legitimate cases to do masses updates but they are rare, especially on permanent tables. This warning only affects updates to permanent tables. For delete, consider to use truncate which is much faster, but which can not run within a transaction.
consequence
Can be a disaster if not intentional. Check the backups!
fix
Add the where clause




MULT4Using literal database 'tempdb'Misc
example
select * from tempdb..sysindexes where ...
comment
Using 'tempdb' literal database name fails in case of multiple tempdb
consequence
fix
Use db_name(@@tempdbid) to get the current tempdb database name




Q1214V12 Compat #1: Having containing outer joinMisc
example
select * from t1, t2 having t1.c1 *= t2.c1
comment
The HAVING clause does not support TSQL outer joins in ASE 12.0.
consequence
V12 migration issue
fix
Change query




Q1224V12 Compat #2: Correlated subquery with outer joinMisc
example
select t2.b1, 
(select t2.b2 from t1 where t2.b1 *= t1.a1) 
from t2
comment
ASE 12.0 processes correlated subqueries containing outer joins differently.
consequence
Query may behave differently.
fix
Change query.




Q1234V12 Compat #3: Conjunct with inner table and join order independent tableMisc
example
select * from download..T1, download..T2, download..T3
where T1.c1 *= T2.c1
and (T1.c1 = T3.c1)
and (T2.c2 = 100 or T3.c2 = 6)
comment
Pre-ASE 12.0 TSQL outer joins can produce ambiguous results if a conjunct makes reference to an inner table and a join order independent table.
consequence
Query may behave differently.
fix
Change query.




VOVR4Variable is an input param and is overwrittenVariable
example
create proc pVOVR
@i int
as
select @i = 1
comment
A parameter is an input parameter and is overwritten before being read. Remark: this can be legal when one overwrites an input variable based on conditions upon other input variables.
consequence
fix
If the parameter passed is of no value to the proc, make it a variable. Otherwise, do not overwrite the parameter value, unless if conditioned by a test.




VUWR4Variable is an output param and is not written toVariable
example
create proc pVUWR
@i int out
as
return
comment
An output parameter is not written to.
consequence
Caller does not receive this output param.
fix
  • If the parameter passed in is not an output parameter, make it non-output.
  • Otherwise, write a value to this variable.




VRUN4Variable is read and not initializedVariable
example
create proc pVRUN
as
declare @i int
select @i
comment
A variable is read before being written to.
consequence
The value of the variable is always NULL. Use the 'NULL' constant rather than the variable.
fix
Since a variable takes the value NULL when it is created, reading a variable which is in a such state is equivalent to replace it with the constant 'NULL'. So if you wanted to use NULL, use the NULL constant. Otherwise, write to the variable before using it.




QDIS3Check correct use of 'select distinct'Performance
example
select distinct ... 
comment
Select distinct are rarely needed. Not that the construct is useless but it is sometimes used in poor programs to hide cartesian products or other bad joins. This is why this warning exists.
consequence
Bad performance
fix
Verify that the 'distinct' does not hide bad join conditions




QUNI3Check Use of 'union' vs 'union all'Performance
example
select name,id from sysobjects
union
select name,id from syscolumns
comment
To satisfy the relational model, queries using 'union' will always put the data in a worktable, sort the data and remove duplicate rows. This may be useful in some cases, but most of the time, the different parts of a 'union' query return results sets that do not contain identical information, generally because the 'where clauses' are exclusive. The cost of using a 'union' vs 'union all' in such case is an overhead.
consequence
Performance hit.

Example (on 12.5.3)
select name,id from sysobjects
union
select name,id from syscolumns
fix
If the different part of the queries generate independant results, use 'union all' instead of 'union'. Most unions can be replaced with 'union all'.

Example (on 12.5.3)
select name,id from sysobjects
union all
select name,id from syscolumns




QFAP3Force abstract planPerformance
example
select ...
plan "(hints (g_join (i_scan xnc2_order_allocation order_allocation ) 
(i_scan () open_orders)))"
comment
Same comment applies as for forceplan
consequence
fix




QFID3Force indexPerformance
example
select id from sysobjects (index ncsysobjects)
comment
Forcing the index is only valid in special cases where the optimizer is wrong. Watch out: the optimizer behaviour is not consistent across versions!
consequence
Misusing force index clauses may degrade the performance
fix
Assess correct usage of forcing index by




QFPL3Force planPerformance
example
set forceplan on
comment
Forcing the execution plan is only valid in special cases where the optimizer is wrong. Watch out: the optimizer behaviour is not consistent across versions!
consequence
Misusing or abusing 'forceplan' clauses may degrade the performance
fix
Assess correct usage of 'forceplan'




QMTB3From clause with many tablesPerformance
example
select t1.data from t1, t2, t3, t4, t5 where ...
comment
Due to optimizer limitations, queries with 'many' tables should be watched carefully, especially if they are executed vey often. The threshold (maximum number of tables) which drives this defect can be set in the options, in the Analysis section.
consequence
Performance hit. Parse and Compile time can become onerous if recompilation happens often.
fix
Ensure that the query plan is fine. For queries with many tables, you can rely on 'forceplan' to guarantee good execution plans. Watch out! Using 'forceplan' is for experts only! A good plan for a particular set of data may be wrong for another!




QJWT3Join or Sarg Without Index on temp tablePerformance
example
comment
This is like a join without index but is considered as a warning when one of the table is a temp table
consequence
fix




QPRI3Join or Sarg with Rooted Partial Index UsePerformance
example
comment
The index selection uses a root portion of the full index. Partial index use can lead to suboptimal performance.
consequence
fix
add missing columns to index




QHNT3Optimizer HintPerformance
example
set basic_optimization on
comment
Using Optimizer Hint. This is just a marker
consequence
Must be done carefully. Behavior may vary across version
fix
Always try to let optimizer free of hints. Only apply when necessary




QSWV3Sarg with variablePerformance
example
select * from t where t.c = @declared_variable
comment
When the value of a search argument is not known at optimization, the optimizer uses default values for density (10, 25 and 33 percent). The index may or not be selected based on this heuristic.
consequence
Bad Query Plan
fix
If you know that the density of the column is very different from the default value, you can force the index.




QISO3Set isolation levelPerformance
example
select *
from sysobjects
at isolation read uncommitted
comment
Lowering the isolation level may ease certain situations (deadlocks) but must be used with caution as data may be incorrect (written by another process in the middle of your query). You may use this construct when you know by construction that the data was already commited although still in a transaction or that you don't care about reading possible bad data.
consequence
Bad data.
fix




QTLO3Top-Level ORPerformance
example
select id from sysobjects
where name = 'sysobjects'
or type = 'P'
comment
'OR' clauses are rarely adequate in queries, especially if they happen at the top level of the where clause. They may be valid though. This is a warning only.
consequence
Table scans. Performance hit.
fix
Assess the need for an 'or'. Cut the query in two using a union can help readability.




MNEJ3'Not Equal' joinMisc
example
select * from t1,t2 where t1.c1 != t2.c2
comment
Joining with 'Not Equal' is rarely needed. It generally yields cartesian products that are further dealt with by using a 'distinct'
consequence
Bad IOs and sometimes bad result sets
fix
You can generally replace the construct with a proper join and a 'not in subselect' clause.




QAPT3Access to Proxy TableMisc
example
comment
Just a marker
consequence
Can have performance penalty, especially in joins
fix
Use replication




MAW13Ad hoc Warning 1Misc
example
set up in AdHocWarning Panel:
    Database="mercury"
    Name="tax_lot"
    Message="DEPRECATION: tax_lot table should not be used. Use new_tax_lot instead"
    ID="MAW1"
 
Other example: reminder when using a specific table:
    Database="work"
    Name="wrk.*"
    Message="REMINDER: Do not forget the 'spid' condition for this table"
    ID="MAW2"
comment
Ad hoc User Warning 1 to 9 allows to put specific messages to objects/column usage through the Ad-Hoc Warning Messages Option Panel These messages can be deprecation messages or other warning which helps the developer on special usage of such objects...
consequence
fix




MAW23Ad hoc Warning 2Misc
example
comment
Ad hoc User Warning 1 to 9 allows to put specific messages to objects usage through the Ad-Hoc Warning Messages Option Panel These messages can be deprecation messages or other warning which helps the developer on special usage of such objects...
consequence
fix




MAW33Ad hoc Warning 3Misc
example
comment
Ad hoc User Warning 1 to 9 allows to put specific messages to objects usage through the Ad-Hoc Warning Messages Option Panel These messages can be deprecation messages or other warning which helps the developer on special usage of such objects...
consequence
fix




MAW43Ad hoc Warning 4Misc
example
comment
Ad hoc User Warning 1 to 9 allows to put specific messages to objects usage through the Ad-Hoc Warning Messages Option Panel These messages can be deprecation messages or other warning which helps the developer on special usage of such objects...
consequence
fix




MAW53Ad hoc Warning 5Misc
example
comment
Ad hoc User Warning 1 to 9 allows to put specific messages to objects usage through the Ad-Hoc Warning Messages Option Panel These messages can be deprecation messages or other warning which helps the developer on special usage of such objects...
consequence
fix




MAW63Ad hoc Warning 6Misc
example
comment
Ad hoc User Warning 1 to 9 allows to put specific messages to objects usage through the Ad-Hoc Warning Messages Option Panel These messages can be deprecation messages or other warning which helps the developer on special usage of such objects...
consequence
fix




MAW73Ad hoc Warning 7Misc
example
comment
Ad hoc User Warning 1 to 9 allows to put specific messages to objects usage through the Ad-Hoc Warning Messages Option Panel These messages can be deprecation messages or other warning which helps the developer on special usage of such objects...
consequence
fix




MAW83Ad hoc Warning 8Misc
example
comment
Ad hoc User Warning 1 to 9 allows to put specific messages to objects usage through the Ad-Hoc Warning Messages Option Panel These messages can be deprecation messages or other warning which helps the developer on special usage of such objects...
consequence
fix




MAW93Ad hoc Warning 9Misc
example
comment
Ad hoc User Warning 1 to 9 allows to put specific messages to objects usage through the Ad-Hoc Warning Messages Option Panel These messages can be deprecation messages or other warning which helps the developer on special usage of such objects...
consequence
fix




MLCH3Char type with length>30Misc
example
declare @c char(50)
comment
Prefer varchar for strings length > 30, because they are trimmed
consequence
Loss of space (padding)
fix
Use varchar




MUIN3Column created using implicit nullabilityMisc
example
create #t (i int)
comment
Using server defined column nullability is a bad practice because it is not explicit. Specify what you want.
consequence
unpredictability if server setting changes
fix
specifiy nullability explicitely




QCRS3Conditional Result SetMisc
example
if some_condition 
begin
select * from mytable
end
comment
Conditional result sets are a bad practice. When a stored proc returns result set conditionnaly, the client programs must in general introspect the result set metadata to realize what result set it is currently dealing with
consequence
Client code is difficult to write
fix
Do not use conditional result sets. Have your procs always returning the same number of result sets all the time. Note that a result set with 0 rows is perfectly valid.




QCTC3Conditional Table CreationMisc
example
if some_condition 
begin
select * into #t
-- or
create table #t
end
comment
Conditional table creation are a bad practice. It creates recompilation conditions
consequence
Plan recompilation is costly and sometimes buggy
fix
Do not use conditional table creation. Create your temp tables upfront unconditionnaly and use insert/select in the conditional block.




CUNU3Cursor not updated: cursor should contain 'for read only' clauseMisc
example
comment
consequence
Sub-optimal lock use, mis information of reader
fix
add 'for read only' clause




CUUP3Cursor updated: cursor should contain 'for update' clauseMisc
example
comment
consequence
fix
add 'for update' clause




CUSU3Cursor updated through 'searched update': risk of halloween rowsMisc
example
comment
consequence
Halloween problem
fix




MGTP3Grant to publicMisc
example
grant exec on proc to public
grant select on t to public
comment
Granting to public violates security rules.
consequence
Bad security
fix
Use business roles and grant objects to business roles.




QGWO3Group by without order byMisc
example
select t1.data
from t1
group by t1.data
comment
From version 15 onwards, a query with a "group by" will not necessarily return the rows sorted in the order of the "group by". Note that simple selects on DOL tables may not return the rows in the order of the clustered index.
consequence
Bad ordering of result set
fix
Add the "order by" clause




MIIO3Index overlapMisc
example
create index i1 on t ( c1, c2 )
create index i2 on t ( c1, c2, c3 )
comment
An index is fully included in another index, with columns in the same order.
consequence
May slow down updates by unnecessary maintenance of index
fix
Keep the more pertinent index, i.e. the longest




QIWC3Insert with not all columns specifiedMisc
example
insert t ( i ) values ( 1 )
-- when t contains more column
comment
This is flagged because the use of default values is sometimes not desired.
consequence
fix
Put all columns in the insert clause.




MBLI3Integer Value of Binary Literal is Platform DependantMisc
example
select sysstat2 & 0x80 from sysobjects s
comment
The exact form in which you enter a particular value depends upon the platform you are using. Therefore, calculations involving binary data can produce different results on different machines. You cannot use the aggregate functions sum or avg with the binary datatypes.
consequence
Wong computations if software is ran on different platforms
fix
For platform-independent conversions between hexadecimal strings and integers, use the inttohex and hextoint functions rather than the platform-specific convert function.




QPSC3Join on same columnMisc
example
select t1.data
from t1
where i = i
comment
A predicate contains the same column on both sides. This is almost always a typo error.
consequence
fix
change the typo. maybe an '@' sign was forgotten




NCER3Naming Convention ErrorMisc
example
select * from table_with_bad_name
comment
Naming Convention allow procs, triggers, tables, views, columns or variables to share a common naming pattern. Naming Patterns add consistency to the code and also carries information about the named object, for instance columns named tx_something could denote columns of type text.
Such a pattern would be parametrized as tx_[a-z]*
Naming Patterns are given in the form of Regular Expressions in the Preferences Panel.
For more information on Regular Expressions, pls check this page
consequence
Inconsistent style
fix
Rename non compliant items




QPNC3No column in conditionMisc
example
select t1.data
from t1
where @var1 = 12
comment
A predicate contains a condition which does not refer a column. This condition is hence constant for the query. This construct may be valid but may also reveal a typo error.
consequence
fix
If possible get the condition out of the query, with an if like in
if @var1 = 12
select t1.data from t1
This will be easier to read




QNCO3No column in result setMisc
example
select @var1 from t1
comment
The select list contains no column from the tables queried. It can be a typo as in
select @col1 = @col1
from t1
-- better
select col1 = @col1
from t1
(typo on the column name) or another typo as in
select @col1 = col1
from t1
-- better
select col1 = @col1
from t1
(typo on the column data)
consequence
If it is a typo, the query is generally pointless or return a result set when a variable assignment was wanted.
fix




MNER3No Error CheckMisc
example
update t...
-- no @@error check just after
or
exec subproc ...
-- no return value check just after
comment
Some ASE errors rollback the entire batch for you, so the error code checking will not even have a chance to run. However, this is not true for all errors. A good practice is to systematically check for @@error after statements which are likely to produce errors (mainly table writes). Most of the time, it is safer to abort on error as per the template shown in the fix section. SQLBrowser checks for lack of @@error check after Insert,Update,Delete and Truncate statements. SQLBrowser also checks for lack of return value check after Exec statements.
consequence
- messy client error reporting - bad data because processing continues where it should have been stopped
fix
Systematically apply a code template similar to this one:
create proc p
as
    declare @r int
    update t set c=1 where c=2
    if @@error != 0 
    begin
        raiserror 20001 'Update failed'
        goto fail
    end
    exec @r = subproc 
    if @r != 0 goto fail
    return 0
    fail:
    if @@trancount>0 rollback tran
    return 1




MNAC3Not using ANSI 'is null'Misc
example
select * from t where column = null
comment
The legacy Sybase syntax allows to compare values with null using "=" or "!=". When set ansinull is "on" for ANSI SQL compliance, the "=" and "!=" operators do not return results when used with a NULL.
consequence
Different behaviour and result sets can be expected, depending on the "set ansinull" option. Using "is [not] null" consistently will avoid different behaviour regardless of the "set ansinull" option setting.
fix
Replace "= null" with "is null" and "!= null" with "is not null"




QNAJ3Not using ANSI Inner JoinMisc
example
select * from
sysobjects s, syscolumns c
where c.id = s.id
comment
Using ANSI joins has many advantages:
- it provides better readability
- it allows to express more, e.g. SARG on inner table member in outer join
- it allows to tune queries by incrementally adding or removing join portions
consequence
fix
Replace Transact-SQL joins by ANSI Joins. In a future release, a contextual menu 'Convert to ANSI Join' option will be added.




QNAO3Not using ANSI Outer JoinMisc
example
select * from
sysobjects s, syscolumns c
where c.id *= s.id
comment
Using ANSI joins has many advantages:
- it provides better readability
- it allows to express more, e.g. SARG on inner table member in outer join
- it allows to tune queries by incrementally adding or removing join portions
consequence
fix
Replace Transact-SQL joins by ANSI Joins. In a future release, a contextual menu 'Convert to ANSI Join' option will be added.




QONC3On clause does not refer introduced tableMisc
example
-- no condition on introduced table
select *
from t1
    left join t2 on t2.i = t1.i
    join t3 on t1.i = t2.i
go
comment
It is allowed but not common to not mention the table introduced in the join in the on clause
consequence
Could be a typo
fix
Reoder predicates. Check your joins




MDYN3Proc uses Dynamic SQL but is not flagged with 'Dynamic Ownership Chain'Misc
example
comment
Proc is called dynamically but does not have the 'Dynamic Ownership Chain' flag.
consequence
Permission problems
fix
Flag the proc with 'Dynamic Ownership Chain' using sp_procxmode




QRPR3Repeated predicateMisc
example
comment
most often a typo
consequence
bad results because of typo
fix
fix the typo




QNAM3Select expression has no nameMisc
example
select max(id) from sysobjects
comment
Column headers of annonymous expression are null. It is not a good practice as it forces the client code to fetch data by position. A better practice is to identify results by column names rather than by positions.
consequence
The client code is too coupled to the column ordering The client code is less readable
fix
Label expressions with properName as in:
select maxid1 = max(id), max(id) as maxid2, max(id) maxid3 from sysobjects




QNUA3Should use Alias:Misc
example
select * from
sysobjects, syscolumns
where sysobjects.id = syscolumns.id
comment
A good query should use short and meaningful names (not a,b,c), and possibly always use the same alias for the same table in all queries.
consequence
Code is less easy to read
fix
Use Aliases, a.k.a. Correlation Names.




QTJ13Table only appears in inner join clauseMisc
example
select t1.*
from t1
    join t2 on t2.i = t1.i
comment
The join on t2 is a 'presence only' join because no columns from t2 are used elsewhere in the query. It can be valid but may also be unnecessary
consequence
Uneeded extra joins slows down queries
fix
If "presence" join is not needed, remove the table from the join




MUBC3Unbalanced begin tran/commit tranMisc
example
begin tran
...
[no commit/rollback tran]
comment
Unbalanced begin tran/commit/rollback is bad practice
consequence
Bad transaction handling
fix
The good template for transactions, in normal Transact-SQL unchained mode is:
proc P
begin tran
exec ...
if [bad_status] goto fail
update ...
if @@error != 0 goto fail
...
commit tran
return [good_status]
fail:
rollback tran
return [bad_status]




MUPK3Update column which is part of a PK or unique indexMisc
example
update sysobjects set id=1 where id=-1
comment
updating a key is considered bad practice
consequence
fix
use delete + insert




CSBI3Updated Cursor with 'Distinct/Union/GroupBy/OrderBy/Aggregate/SubQueries/Isolation' should contain 'insensitive' clause (V15)Misc
example
comment
in V12, such cursors would always scan worktables, making updates to base tables innocuous for the cursor
consequence
V15 behavior is different. May not use worktable
fix
add 'insensitive' clause




MUUF3Update or Delete with Useless From ClauseMisc
example
comment
consequence
Potential V15 issue. Can yield wrong query plans if table name and table name in from clause are not strictly matching e.g. update t set x=1 from dbo.t. However, useless from clause should be removed anyways.
fix




MUOT3Updates outside transactionMisc
example
update t1 set i=1
delete t2
comment
2 or more statements must be wrapped in a transaction Assuming that the parent proc initiated the transaction is dangerous, especially with Sybase, as nested transactions don't carry any overhead Remark: you can ignore this warning if the transaction mode is "chained", which is not Sybase's default.
consequence
Database integrity problems
fix
Add begin tran/commit tran pairs around multiple updates.




MUTI3Update temptable with identity - 12.5.4 RegressionMisc
example
comment
consequence
Potential 12.5.4 regression
fix




MUCO3Useless CodeMisc
example
create proc useless_code (
    @v int
) as
    begin
        if ( @v = 1 )
            print 'A'    
        if @v = 1 or (( @v=2 and @v=3 ))
        begin
            print 'A'
        end
        begin
            select 12
        end
        return ( 100 )
    end
go
comment
Some syntactical constructs are redundant. For instance putting parenthesis around counditions or placing a begin-end block as the first statement of a stored procedure
consequence
code is bigger and harder to read
fix
Remove unnecessary constructs




QAFM3Var Assignment from potentially many rowsMisc
example
declare @i int
select @i = id from sysobjects -- BAD
select top 17 @i = id from sysobjects -- BAD
set rowcount 2 select @i = id from sysobjects set rowcount 0 -- BAD

select @i = id from sysobjects where id = 2 -- OK
select top 1 @i = id from sysobjects -- OK
set rowcount 1 select @i = id from sysobjects set rowcount 0 -- OK
comment
ASE does not generate an error when an assignment to a variable selects more than 1 row. It is considered bad practice because it relies on an undocumented behavior or else depends on the scanning order (ascending or descending). It is difficult to diagnose (rowcount can be set to 1, the table may have exactly one row, the where clause may qualify exaclty one row although not using a unique index etc...). So this error can be raised when the code still works. However, this form of coding is considered as a bad practice and should be fixed.
consequence
Wrong behavior of the code. Can be seen as regression bugs during server upgrade.
fix
Always make sure that the result sets used to feed variables always qualify a single row




MZMB3Zombie: use of non-existent objectMisc
example
select * from this_table /* this_table does not exist in the server */
comment
Ocasionally, objects are destroyed because they are no longer used. Objects referencing such objects will throw a run-time error at execution. Such calling objects should have been destroyed along with the first ones. The Zombie defect detects such calling objects.
consequence
Useless Zombie objects in the server.
fix
Destroy the Zombie objects.




VNRD3Variable is not readVariable
example
declare @i int select @i = 12
comment
A variable is written to and not read after
consequence
Write is useless
fix
Remove the write or read the variable later




VUNU3Variable is not usedVariable
example
create proc pVUNU
as
declare @i int
return
comment
A variable or parameter is declared and never reused in the proc
consequence
Proc unnecessarily bigger
fix
Remove the variable




MSUC2Correlated Subquery MarkerMisc
example

        
    
comment
Correlated SubQuery Marker
consequence
fix




MCWR2Create Proc With Recompile MarkerMisc
example
create proc p with recompile as ...
comment
Using 'with recompile' can be bad when upgrading server with optimizer changes
consequence
Suboptimal plan
fix
Reassess need for recompile when upgrading




MDRV2Derived Table MarkerMisc
example
comment
This is just a marker for a Derived Table
consequence
fix




MDYE2Dynamic Exec MarkerMisc
example
exec @v
comment
Dynamic execs are difficult to analyze. Avoid if possible!
consequence
Code harder to track
fix
Avoid dynamic execs




MDYS2Dynamic SQL MarkerMisc
example
exec ( 'select 12' )
comment
Using dynamic SQL can be convenient but it defeats the SQLBrowser analysis. Note: You can use the dependency pragma in so that SQLBrowser batch analysis knows that certain objects are used by this proc in order for the depedency analysis to be correct.
consequence
Code is harder to track
fix
Avoid dynamic sql when possible




MEWR2Exec With Recompile MarkerMisc
example
exec sp_help 'anobject' with recompile
comment
Using 'with recompile' can be bad when upgrading server with optimizer changes
consequence
Suboptimal plan
fix
Reassess need for recompile when upgrading




CRDO2Read Only Cursor MarkerMisc
example
comment
Marker for read only cursors
consequence
fix




MRST2Result Set MarkerMisc
example
select * from sysobjects
comment
This is just a marker for a Result Set
consequence
fix




MSTT2Shared Temptable MarkerMisc
example
comment
This is just a marker for a Shred Temp Table
consequence
fix




MSUB2Subquery MarkerMisc
example

        
    
comment
SubQuery Marker
consequence
fix




CUPD2Updatable Cursor MarkerMisc
example
comment
Marker for updatable cursors
consequence
fix




MSTA2Use of Star MarkerMisc
example
select * from t
comment
It maybe useful to track usage of '*' in regular select lists. Since Sybase V12, the stars are expanded in syscomments when the proc is created
consequence
fix




MTR12Metrics: Comments RatioMetrics
example
comment
Shows Comments Ratio
consequence
fix




MTR22Metrics: Cyclomatic ComplexityMetrics
example
comment
consequence
fix




MTR32Metrics: Query ComplexityMetrics
example
comment
Shows Query Complexity. Query Complexity is an arbitrary formula that you can parametrize in the options and which attempts to measure some form of complexity.
consequence
fix