select t1.data
                from t1, t2
            

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.)

Performance hit and bad result set.

Join the two or more tables which are not connected.

select * from t (index non_existing_index)

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

performance hit

remove the force index or use an existing index

select max(id), name
                from sysobjects
                group by type
            

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.

Bad query.

This is the rule: the only non-aggregated columns appearing in a select list must also appear in the 'group by' clause.

exec myproc @dumbparam = 12

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 a typo error or an evolution of the called stored proc without cleanup of all calling instances.

In most programming languages, passing an unknown parameter would fail. In Transact-SQL, this is a silent error.

Pass the right parameter or suppress the parameter from the call.

select t1.*
                from t1
                left join t2 on t2.i = t1.i
            

The join on t2 is a useless join because no columns from t2 are used elsewhere in the query

Uneeded extra joins slows down queries

Remove the join

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

Proc call failure: error 925 sev 19 Maximum number of used databases for each query has been exceeded. The maximum allowed is 8

Regroup objects in less databases

return
                raiserror 20001 'Error'
            

Unreachable code can never be executed because no code path leads to it

A common cause is inadvertant code modification

Remove unreachable code or fix the premature return/goto/break/continue

exec proc
                @p1 = 1
                @p1 = 2
            

A parameter is passed by name twice

Probably a typo, remove.

select @i = 12

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.

Does not compile

Declare the variable

This is a SQLBrowser general parsing error

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.

Performance hit.

Either add an index on the columns concerned if this join is causing performance problems, reconsider the query, or live with it.

                begin tran
                return
                commit tran
            

It is a bad practice to use return statements within transaction boundaries. In general procs should not change the transaction level

Uneven transaction management

use goto fail statament and test @@trancount and rollback if necessary after the fail label

-- 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
            

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

Unexpected result set

Nest Joins. Note: in non ansi join form, this query fails to run

update table set timestamp=some_non_null_value

updating a timestamp has theoretically no effect as the timestamp will be bumped by Sybase but this is considered as a bad practice

can lead to stack traces in certain ASE implementations

Either insert null, or, better, skip the column. Skipping the column works even if the timestamp is not nullable

select t1.data
                from t1, t2 where t2.i=1
            

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.

Maybe bad performance

Join the two or more tables which are not connected.

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
            

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.

Bad performance

Add convert() or change type of expression

if ( select count(*) from sysobjects where type = 'P' ) > 0
                begin
                print 'Why don't I use if exists()?'
                end
            

It is common to see code using select count(*) > 0 to detect the existence of rows. Beginners often ignore the 'exists (select *' construct.

Performance hit.

Use 'exists' as in

if exists ( select * from sysobjects where type='P' )
                begin
                print 'Yes'
                end
            

The index selection uses an 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

add missing columns to index

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
            

Index creation after table has been used may generate recompilation (15.0.3)

Lengthy Parse and Compile phases

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.

                set basic_optimization on
                -- no closing matching hint found later
            

Unbalanced Optimizer Hint

Probably forgot to close the hint. Good habit is that hints are used in opening/closing pairs

Add closing hint, i.e. set [hint] OFF

Must close forceplan

unwanted forceplan on subsequent queries

set forceplan off

declare @c char(10)
                select @c = name from sysobjects where id = 12345
            

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.

Bad data because of truncation.

Enlarge the type of the recipient variable. Use proper convert to inform the reader that the truncation is desired.

return -6

user stored procedures should not return values in the range [-1,-99] which are values reserved by Sybase

the client will not distinguish Sybase general errors from application errors

the advise is to return 0 to indicate success and [-101, -102, .. ] to indicate failures

                if condition
                begin tran
            

It is a bad practice to place begin tran or commit tran under conditions

Makes transaction management difficult

use begin tran and commit tran unconditionnaly in a procedure

Copy Paste Code is difficult to avoid in T-SQL, but some of it can

Harder code to maintain

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

select c + '' + d from t

An empty string ("") or ('') is actually transformed into a single space character. There is no such thing as an empty string in Sybase

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

As one can not express an empty string in T-SQL, replace the constants by single space. It is more explicit

select * from t(2)

Creates code which is too tight to index order creation

Use index name instead of index number

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
            

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

Wrong data in column

remove naming - although it may help to leave them in very long lists

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

This table will only be accessed through table scan.

Add a unique index.

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

Data quality is poor. Potential performance hit.

Add one or more unique index ASAP

Unbalanced creation and drop of temp tables in source file

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

Drop all temp tables created in a source file

A Unique Index with nullable columns is bad practice

Bad performance because the index is less usable

exec sp_who sa

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

Proc compiles but gives wrong result

Pass either (single) quoted strings or variables, but not naked identifiers

-- pragma acknowledge next defect XYZT comment
                -- pragma dependency select mydb..mytable
            

Unrecognized pragma may be due to defect code change or typo

delete t

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.

Can be a disaster if not intentional. Check the backups!

Add the where clause

select * from tempdb..sysindexes where ...

Using 'tempdb' literal database name fails in case of multiple tempdb

Use db_name(@@tempdbid) to get the current tempdb database name

select * from t1, t2 having t1.c1 *= t2.c1

The HAVING clause does not support TSQL outer joins in ASE 12.0.

V12 migration issue

Change query

select t2.b1,
                (select t2.b2 from t1 where t2.b1 *= t1.a1)
                from t2
            

ASE 12.0 processes correlated subqueries containing outer joins differently.

Query may behave differently.

Change query.

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)
            

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.

Query may behave differently.

Change query.

create proc pVOVR
                @i int
                as
                select @i = 1
            

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.

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.

create proc pVUWR
                @i int out
                as
                return
            

An output parameter is not written to.

Caller does not receive this output param.

• If the parameter passed in is not an output parameter, make it non-output.
• Otherwise, write a value to this variable.

create proc pVRUN
                as
                declare @i int
                select @i
            

A variable is read before being written to.

The value of the variable is always NULL. Use the 'NULL' constant rather than the variable.

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.

select name,id from sysobjects
                union
                select name,id from syscolumns
            

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.

Performance hit.

Example (on 12.5.3)

select name,id from sysobjects
                union
                select name,id from syscolumns
            

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
            

select distinct ...

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.

Bad performance

Verify that the 'distinct' does not hide bad join conditions

select ...
                plan "(hints (g_join (i_scan xnc2_order_allocation order_allocation )
                (i_scan () open_orders)))"
            

Same comment applies as for forceplan

select id from sysobjects (index ncsysobjects)

Forcing the index is only valid in special cases where the optimizer is wrong. Watch out: the optimizer behaviour is not consistent across versions!

Misusing force index clauses may degrade the performance

Assess correct usage of forcing index by

set forceplan on

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!

Misusing or abusing 'forceplan' clauses may degrade the performance

Assess correct usage of 'forceplan'

select t1.data from t1, t2, t3, t4, t5 where ...

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.

Performance hit. Parse and Compile time can become onerous if recompilation happens often.

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!

This is like a join without index but is considered as a warning when one of the table is a temp table

The index selection uses a root portion of the full index. Partial index use can lead to suboptimal performance.

add missing columns to index

set basic_optimization on

Using Optimizer Hint. This is just a marker

Must be done carefully. Behavior may vary across version

Always try to let optimizer free of hints. Only apply when necessary

select * from t where t.c = @declared_variable

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.

Bad Query Plan

If you know that the density of the column is very different from the default value, you can force the index.

select *
                from sysobjects
                at isolation read uncommitted
            

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.

Bad data.

select id from sysobjects
                where name = 'sysobjects'
                or type = 'P'
            

'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.

Table scans. Performance hit.

Assess the need for an 'or'. Cut the query in two using a union can help readability.

select * from t1,t2 where t1.c1 != t2.c2

Joining with 'Not Equal' is rarely needed. It generally yields cartesian products that are further dealt with by using a 'distinct'

Bad IOs and sometimes bad result sets

You can generally replace the construct with a proper join and a 'not in subselect' clause.

Just a marker

Can have performance penalty, especially in joins

Use replication

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"
            

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

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

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

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

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

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

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

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

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

declare @c char(50)

Prefer varchar for strings length > 30, because they are trimmed

Loss of space (padding)

Use varchar

create #t (i int)

Using server defined column nullability is a bad practice because it is not explicit. Specify what you want.

unpredictability if server setting changes

specifiy nullability explicitely

                if some_condition
                begin
                select * from mytable
                end
            

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

Client code is difficult to write

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.

                if some_condition
                begin
                select * into #t
                -- or
                create table #t
                end
            

Conditional table creation are a bad practice. It creates recompilation conditions

Plan recompilation is costly and sometimes buggy

Do not use conditional table creation. Create your temp tables upfront unconditionnaly and use insert/select in the conditional block.

Sub-optimal lock use, misinformation of reader

add 'for read only' clause

Halloween problem

add 'for update' clause

grant exec on proc to public
                grant select on t to public
            

Granting to public violates security rules.

Bad security

Use business roles and grant objects to business roles.

select t1.data
                from t1
                group by t1.data
            

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.

Bad ordering of result set

Add the "order by" clause

create index i1 on t ( c1, c2 )
                create index i2 on t ( c1, c2, c3 )
            

An index is fully included in another index, with columns in the same order.

May slow down updates by unnecessary maintenance of index

Keep the more pertinent index, i.e. the longest

insert t ( i ) values ( 1 )
                -- when t contains more column
            

This is flagged because the use of default values is sometimes not desired.

Put all columns in the insert clause.

select sysstat2 & 0x80 from sysobjects s

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.

Wrong computations if software is run on different platforms

For platform-independent conversions between hexadecimal strings and integers, use the inttohex and hextoint functions rather than the platform-specific convert function.

select t1.data
                from t1
                where i = i
            

A predicate contains the same column on both sides. This is almost always a typo error.

change the typo. maybe an '@' sign was forgotten

select * from table_with_bad_name

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

Inconsistent style

Rename non-compliant items

update t...
                -- no @@error check just after
                or
                exec subproc ...
                -- no return value check just after
            

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.

- messy client error reporting - bad data because processing continues where it should have been stopped

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:
                rollback tran
                return 1
            

select t1.data
                from t1
                where @var1 = 12
            

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.

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

select @var1 from t1

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)

If it is a typo, the query is generally pointless or return a result set when a variable assignment was wanted.

select * from t where column = null

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.

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.

Replace "= null" with "is null" and "!= null" with "is not null"

select * from
                sysobjects s, syscolumns c
                where c.id = s.id
            

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

Replace Transact-SQL joins by ANSI Joins. Use contextual menu 'Convert to ANSI Join' to convert.

select * from
                sysobjects s, syscolumns c
                where c.id *= s.id
            

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

Replace Transact-SQL joins by ANSI Joins. Use contextual menu 'Convert to ANSI Join' to convert.

-- no condition on introduced table
                select *
                from t1
                left join t2 on t2.i = t1.i
                join t3 on t1.i = t2.i
                go
            

It is allowed but not common to not mention the table introduced in the join in the on clause

Could be a typo

Reoder predicates. Check your joins

Proc is called dynamically but does not have the 'Dynamic Ownership Chain' flag.

Permission problems

Flag the proc with 'Dynamic Ownership Chain' using sp_procxmode

most often a typo

bad results because of typo

fix the typo

select max(id) from sysobjects

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.

The client code is too coupled to the column ordering The client code is less readable

Label expressions with properName as in:

select maxid1 = max(id), max(id) as maxid2, max(id) maxid3 from sysobjects

select * from
                sysobjects, syscolumns
                where sysobjects.id = syscolumns.id
            

A good query should use short and meaningful aliases (for instance, not "a,b,c..."), and if possible always use the same alias for the same table throughout all queries.

Code is less easy to read

Use Aliases, a.k.a. Correlation Names.

select t1.*
                from t1
                join t2 on t2.i = t1.i
            

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

Uneeded extra joins slows down queries

If "presence" join is not needed, remove the table from the join

begin tran
                ...
                [no commit/rollback tran]
            

Unbalanced begin tran/commit/rollback is bad practice

Bad transaction handling

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]
            

update sysobjects set id=1 where id=-1

updating a key is considered bad practice

use delete + insert

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 anyway.

Potential 12.5.4 regression

Updated Cursor with 'Distinct/Union/GroupBy/OrderBy/Aggregate/SubQueries/Isolation' should contain 'insensitive' clause. in V12, such cursors would always scan worktables, making updates to base tables innocuous for the cursor

V15 behavior is different. It May not use worktable, so you should declare it 'insensitive'

add 'insensitive' clause

update t1 set i=1
                delete t2
            

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.

Database integrity problems

Add begin tran/commit tran pairs around multiple updates.

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
            

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

code is bigger and harder to read

Remove unnecessary constructs

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
            

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.

Wrong behavior of the code. Can be seen as regression bugs during server upgrade.

Always make sure that the result sets used to feed variables always qualify a single row

select * from this_table /* this_table does not exist in the server */

Occasionally, 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.

Useless Zombie objects in the server.

Destroy the Zombie objects.

declare @i int select @i = 12

A variable is written to and not read after

Write is useless

Remove the write or read the variable later

create proc pVUNU
                as
                declare @i int
                return
            

A variable or parameter is declared and never reused in the proc

Proc unnecessarily bigger

Remove the variable

Correlated SubQuery Marker

create proc p with recompile as ...

Using 'with recompile' can be bad when upgrading server with optimizer changes

Suboptimal plan

Reassess need for recompile when upgrading

This is just a marker for a Derived Table

exec @v

Dynamic execs are difficult to analyze. Avoid if possible!

Code harder to track

Avoid dynamic execs

exec ( 'select 12' )

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.

Code is harder to track

Avoid dynamic sql when possible

exec sp_help 'anobject' with recompile

Using 'with recompile' can be bad when upgrading server with optimizer changes

Suboptimal plan

Reassess need for recompile when upgrading

Marker for read only cursors

select * from sysobjects

This is just a marker for a Result Set

This is just a marker for a Shred Temp Table

SubQuery Marker

Marker for updatable cursors

select * from t

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

Shows Comments Ratio

Shows Cyclomatic Complexity

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.