Sybase ASE
All versions
Last updated: 21 January 2007
ASE Quiz Questions: answers January - June 2003
 
This page contains answers to the ASE quiz questions from 2003.
More ASE quiz questions: 2013 - 2012 - 2011 - 2010 - 2009 - 2008 - 2007 - 2006 - 2005 - 2004 - 2003 - 2002 - 2001
For a randomly selected quiz question, click here.



December 2003
In an ASE 12.5 database, there is a normal user table (i.e. not a proxy table) called xmasquiz, with a single column a. This table contains a fixed set of rows, and is not modified in any way. Yet, when running the query select * from xmasquiz, a different set of rows seems to be returned each time. How do you explain this behaviour?

Answer:
The table contains 100 single-column rows, with values 1..100. By using an access rule containing a rand() function, a sequence of values is returned between randomly chosen lower and upper bounds.
You can reproduce this as follows:
create table xmasquiz (a int)
go

create access rule xmasrule 
as
@a between floor(rand()*70) 
   and 30 + ceiling(rand()*70)
go

sp_bindrule xmasrule, 'xmasquiz.a'
go

-- Now insert values 1..100
set rowcount 100
select n=identity(3) into #t
from syscolumns
set rowcount 0
insert xmasquiz select n from #t
go

-- Now each select statement will return a 
-- different set of values!
select * from xmasquiz
go
Access rules were introduced in ASE 12.5. When querying a table with an access rule, the access rule is invisibly appended to the query's where-clause, so it affects the query's result set. The idea behind access rules is to allow a specific user access to only specific rows in a table, for example only the rows 'owned' by that user. In this setup, each row would contain a column with an indication of the 'owner' of that row, and the access rule would match this column to the executing user's login ID or database user ID. The application of access rules shown here is probably not how the feature was originally intended... but it works!
Using access rules requires enabling the configuration parameter enable row level access, which requires the ASE_ASM option to be licensed; this option is enabled in the free ASE Developer's Edition.

In the T-SQL code above, note that the essential part is the use of rand(). Multiplying it by 70 and adding 30 is only a cosmetic tweak to create a nice variety in returned result sets.
Note that it is equivalent to create two access rules, one as @a >= floor(rand()*70) and the other as @a <= 30 + ceiling(rand()*70), and bind both rules to column a.

Prize winners:
Congratulations to Alex Vickers, Manoher Gocher and Paul Barker who were the first to come up with a perfect reproduction; they'll receive a free copy of one of my books.

Only a small number of replies were actually completely or partly correct, identifying at least 'access rules' at the key to this problem. Some people suggested using the millisecond part of getdate() in the where-clause; while this is an interesting idea, it would lead to different result sets than with rand(), since milliseconds are always a multiple of 3 in ASE. Also, it is difficult to use the millisecond part for determining both the upper and the lower boundary of the result set since the evaluation of the different built-in functions in the where-clause will almost always take place in the same millisecond, so they'll have identical values.
Some others correctly aimed at access rules plus rand(), but felt a login trigger part had to be part of the solution as well. Close, though!

The majority of the replies suggested things like the use of parallel query processing and/or missing out an order-by clause (wrong: this would not affect the number of rows in the result set), mapping the table to an OS file by means of ASE_XFS or to a different table (wrong: the question stated a proxy table was not involved), or a randomised 'set rowcount' (wrong: the first rows in the result set would still be the same every time). Other attempts to explain this problem involved the absence of any indexes or the use of resource limits.
Lastly, an interesting (though also incorrect) solution was proposed by a few people, namely that column a is a Java datatype returning a random series of values. While many surprising things can indeed be done with a Java class as a column datatype, it still returns just one value for each row, so this would not explain the varying number of rows returned every time.

Anyway, I hope you enjoyed this question. A happy and database-problem-free 2004 to everyone!
How would you rate this ASE quiz question?
Great    OK    Average    Boring     
November 2003
Suppose you're an ASE DBA, and you have been hired to replace another DBA who left abruptly. Once you've managed to log into the ASE server (see the question for September 2003 for more on that), one of the things you'll want to know about this server is which configuration parameters have been set to non-default values (i.e. which parameters have been changed by the DBA). How can this be determined most efficiently?

Answer:
Perhaps the most obvious way is to run the sp_configure command, print out all output and go over it with a yellow marker. However, this involves quite a few pages of paper, so a more elegant (and environmentally friendly) method would be nice. Also, keep in mind that there are a few hundred configuration parameters in ASE while only a relatively small number tend to have a non-default setting.
A quicker way of answering this question is to run the following query:
select name = substring(name,1,35), 
      current_value = substring(cur.value2,1,15), 
      default_value = substring(defvalue,1,15)
from master..syscurconfigs cur, 
     master..sysconfigures con
where con.config = cur.config
and cur.value2 <> defvalue 
-- the next line excludes read-only parameters,
-- but this works only for ASE 12.5+
and cur.type in ('static', 'dynamic')  
order by name
This query extracts the non-default parameters directly from the system tables.
Note that this query looks only at the actual run-time value for each parameter. It won't display static parameters for which the 'config value' has already been changed, but ASE has not yet been restarted.
Also note that this query always displays the configuration parameter configuration file, even though it is effectively read-only (its value is defined by the -e command-line flag when the ASE server is started). Should you want to exclude this parameter, add the predicate and (cur.config <> 114) to the where-clause.

(Incidentally, there is yet another way of finding the non-default configuration parameters: on the command line, run grep -v DEFAULT YOURSERVER.cfg. However, this will not show the default values for these parameters, which is useful information).
How would you rate this ASE quiz question?
Great    OK    Average    Boring     
October 2003
Syntax errors normally cause the execution of SQL code to be aborted. However, syntax errors can also have unexpected side-effects. Can you see the difference between the following batches, which both contain incorrect syntax?
-- batch 1
insert MyTable   
vlaues (1)      -- should have been 'values'
select * from MyTable
go

-- batch 2
inset MyTable   -- should have been 'insert'
select Col from YourTable
select * from MyTable
go
(Hint: there are no tricks with the tables themselves: MyTable and YourTable both exist and contain a single int column named Col)

Answer:
When executing these batches, the first batch is not executed at all: due to the syntax error at vlaues, the statement is found to be invalid and the entire batch is aborted. This means that the select statement is not executed anymore. This is the normal, expected behaviour. However, the second batch is not aborted: both select statements appear to be executed! Isn't that surprising?

The clue is that the second batch does not contain a syntax error, technically speaking. Although inset is spelled where insert was meant, ASE interprets inset as the name of a stored procedure: as documented, the first (non-keyword) identifier found in a batch is automatically supposed to be a stored procedure, even when there is no exec[ute] keyword preceding it. Since the stored procedure inset does not exist (well, it might exist, but let's just assume it doesn't), ASE issues a warning and continues with the next statement (MyTable is interpreted as a parameter to the would-be procedure inset). Technically, the syntax is correct here: it's just that the procedure inset does not exist, and this is considered less severe than a syntax error.

In the first batch, ASE sees that vlaues is a misspelled SQL keyword, and abort the batch due to this syntax error. The difference with the second batch is that - in the first batch - ASE already knows we're in an insert statement, and vlaues cannot be part of that statement since a proper keyword is expected in this position.

In the second batch, note how the original insert-select is not executed, while the select statement constituting the second part of the insert-select is. Also note that this error cannot be intercepted at run-time by checking @@error following the insert-select: since the select is still executed successfully, @@error will be 0, just as when the insert-select had been executed successfully.
The misspelled keyword in the second batch may also lead to surprises (or problems) for the client application, since the server now returns two result sets instead of one.

The problem in the second batch, where something completely different is executed than expected, can be avoided by encapsulating the statements in a stored procedure. In this case, create procedure will be the first statement in the batch, and when insert is misspelled as inset in the body of the procedure, this will result in a true syntax error, causing procedure creation to fail. The clue here is that a stored procedure execution inside a stored procedure must be preceded by the exec[ute] keyword, since the stored proc's name is not the first identifier in the batch anymore ('cos that's create procedure). Hence, there is no ambiguity about the meaning of a misspelled keyword as in a T-SQL batch.

The problems resulting from the ambiguity described above are an argument in favor of putting production code, no matter how simple, in the form of stored procedures, rather than in a script file with individual T-SQL batches.

Still, there is opportunity for error even with stored procedures when using execute-immediate, since the command string submitted to execute-immediate is treated as a T-SQL batch. Hence, the following procedure will behave exactly the same as the second batch above:
create procedure MyProc
as
exec('inset MyTable select Col from YourTable \
select * from MyTable')
go
More than anything else, these examples illustrate why proper testing of all SQL code is essential, and why a close inspection of the test result is required. In my opinion, the worst errors are those that go unnoticed -- the cases above may well qualify.
How would you rate this ASE quiz question?
Great    OK    Average    Boring     
September 2003
Suppose you're an ASE DBA, and you have been hired to replace a DBA who was fired rather abruptly. Your first task is to change the production ASE server's sa password. However, you don't know the current sa password to start with -- and nobody else knows it either (let's assume the fired DBA isn't available for questions).
You know that a new sa password can be generated by starting ASE with the command-line option -p, but this requires that you shut down the server first.

What is the most safe and elegant way of stopping ASE without knowing the ASE password?

Answer:
Strange as it may sound, it is possible to shut down ASE without knowing any password at all.
On Unix, this is done by sending Unix signal 15 to the ASE server, with the Unix command kill -15 pid (kill -TERM pid can be used instead; pid is the Unix process ID of the ASE server). When ASE receives this signal, it performs the equivalent of an immediate shutdown with nowait. As such, this isn't really a 'kill', but rather an urgent shutdown request -- the important thing is that the ASE server itself can still do its own shutdown. Note that not everybody can kill just any Unix process: special priviliges are needed for this.
Signal 15 is a much gentler way of stopping an ASE server than the more commonly used kill -9 pid (equivalent to kill -KILL pid). With kill -9, Unix kills the ASE Unix process instantly, without giving ASE an opportunity to shut itself down as with signal 15. kill -9 is a very rude way to terminate ASE, and this should only be used as a very last resort method when kill -15 does not work.
Also, database corruptions have occasionally been reported as a result of kill -9. Although these are probably rare, it illustrates that kill -9 is not recommended unless there is really no alternative.

On NT, the Unix signals don't exist. However, when the ASE server is running as an NT service (as is usually the case), it can be shut down by stopping the NT service. There are various ways to do this, such as using the NT service manager applet in the Control Panel, Sybase Central, or running the net stop SYBSQL_servername command in a DOS box. In most cases, ASE will shut down fine with the equivalent of a regular shutdown (although in rare cases it may not -- in that case, either shutdown the NT host itself and ensure the ASE server doesn't auto-start; or see section 3.5 of my Tips & Tricks book [below] for a stronger way of killing the ASE NT process).
These actions all require NT 'Administrator' privilige.
When ASE is running in the foreground on NT (i.e. in a DOS box), the server can be stopped by closing the DOS box window; this is equivalent to a shutdown with nowait.


Much more can be said about this topic. For example, there are a number of other reasons why kill -15 may sometimes be useful. Also, an equivalent of the Unix kill -9 for NT actually exists (useful since the regular NT kill command isn't capable of killing NT services).
These issues are covered in section 3.5 of my book "Tips, Tricks & Recipes for Sybase ASE" -- see there for more details (check out this book -- apart from being useful for you, it'll allow me to continue writing ASE quiz questions and maintian this web site!).
How would you rate this ASE quiz question?
Great    OK    Average    Boring     
August 2003
When creating a table, a name and a datatype are usually specified for each column, i.e:
create table MyTable (MyColumn int)
However, in some cases, it is not necessary to specify both a name and a datatype, and the latter can be omitted. Do you know when this is possible?

Answer:
Although a datatype must always be specified for a column in the create table and alter table statements, the exception is the timestamp datatype. A column declared with the name 'timestamp' does not need to be accompanied by a datatype specification, as this defaults to 'timestamp'. Therefore, the following is valid T-SQL syntax:
create table MyTable (MyColumn int, timestamp)
as is this:
create table MyTable (timestamp)
The same applies when adding columns to an existing table with alter table:
create table MyTable (MyColumn int)
alter table MyTable add timestamp null
In fact, since timestamp is not a T-SQL reserved word (it's the name of a datatype), the following is valid as well:
create table timestamp (timestamp)
Note, BTW, that a column named timestamp doesn't have to be of the timestamp datatype:
create table MyTable (timestamp int)

(NB: timestamp columns are special in some other ways as well. My latest book "Tips, Tricks & Recipes for Sybase ASE" discusses some of those aspects. For those who don't own this book, I'll probably do another quiz question about timestamp columns in the coming months)
How would you rate this ASE quiz question?
Great    OK    Average    Boring     
July 2003
As every ASE DBA knows, changing the value of a static configuration parameter requires an ASE restart for the change to become effective.
There is a particular static configuration parameter that requires not one, but two ASE restarts before its value is effectively changed. Do you know which one?

Answer:
The configuration parameter 'enable cis' is static, but cannot immediately be changed by a single ASE restart. The reason is that, since version 12.0, this parameter depends on another configuration parameter, namely 'enable xact coordination'.
By default, both configuration parameters are enabled (i.e. set to 1); both are static parameters as well.
Before disabling 'enable cis', 'enable xact coordination' must be disabled first. sp_configure simply refuses to disable 'enable cis' until 'enable xact coordination' is disabled and ASE is restarted. Only then can 'enable cis' be disabled; another ASE restart is needed to make the latter change effective. Hence, two ASE restarts are needed to disable 'enable cis'.

Interestingly, once both parameters are disabled, they can both be enabled and activated with the same, single ASE restart. Apparently sp_configure is less demanding with respect to the dependency on 'enable xact coordination' in this case.

I have to say I cannot really see a good reason why these parameters shouldn't be allowed to be disabled together as well. After all, changing the value of a static configuration parameter does not have any effect until the next ASE restart, so there should not be any harm in disabling 'enable cis' and 'enable xact coordination' together. But, as mentioned earlier, sp_configure does not allow this.
My guess is that this behaviour is an engineering oversight...

Note that there is in fact a workaround that allows changing both parameters with a single ASE restart: shutdown ASE; manually change both parameters in the configuration file for the ASE server; and restart ASE.
How would you rate this ASE quiz question?
Great    OK    Average    Boring     


June 2003
Since version 11.9, ASE supports datapages and datarows locking as well as the classic allpages lock scheme. Data-only locking (the collective name for datapages and datarows), abbreviated DOL, is functionally identical to allpages (APL) but offers better concurrency and some additional functionality that APL doesn't have (such as reorg rebuild table_name index_name).

The question: which functionality cannot be implemented for a DOL table (or in a clumsy way at best), while this is easy for an APL table?

Answer:
Warning: this is going to be rather technical, and possibly a bit confusing, so brace yourself...

We tend to think of APL as just a functional subset of DOL: any functionality you can implement with APL tables, you can also do with DOL tables. For most things that is indeed true, but there is one aspect of APL that does not have a direct functional equivalent in DOL.
This difference concerns the handling of duplicate rows in tables with a non-unique clustered index. First, let's get the terminology straight: a duplicate row occurs when all columns in a row have the same value (provided there are no text, image, identity or timestamp columns). In ASE, duplicate rows have historically been relevant for tables with a non-unique clustered index, due to the way rows are stored in a a classic APL clustered index.

Now, for APL tables with a non-unique clustered index, duplicate rows are not allowed to be inserted (or created by an update) by default, though this can be allowed with the allow_dup_row option for the non-unique clustered index. The ignore_dup_row option can be used to silently discard inserted duplicate rows without aborting the transaction (as happens by default).
For DOL tables, things are a bit different. allow_dup_row and ignore_dup_row can be used for non-unique clustered indexes on DOL tables as well, but they have an effect only when the index is created. When inserting into (or updating) a DOL table, allow_dup_row and ignore_dup_row do not have any effect (as opposed to APL tables, where they do).

The difference in functionality between APL and DOL tables lies in the case where you want to automatically reject or ignore inserted duplicate rows for a table with more than 31 columns. For APL tables, this can be done by creating a non-unique clustered index on an arbitrary column, which will reject inserted duplicate rows; to ignore these rows, specify the ignore_dup_row option for the clustered index. However, for a DOL table, such an index does not have any effect on inserted duplicate rows at all (see above).
For DOL tables with 31 columns or less, you can still get the same effect by creating a unique index (either clustered or nonclustered) on all columns of the table, and specify the ignore_dup_key option. However, you cannot create an index on more than 31 columns, so for DOL tables with more than 31 columns, this workaround is not available.
One of the factors in this difference in behaviour is that the ignore_dup_row (and allow_dup_row) option for a non-unique clustered index are not related to the columns included in the index, but to the entire row. In other words, the treatment of duplicate rows has nothing to with the actual columns in the non-unique clustered index. This may look a bit awkward, since the ignore_dup_key option on a unique nonclustered index applies only to the combined columns in the index (but then, the difference between ignore_dup_row and ignore_dup_key can indeed be a bit confusing).

Now, there is actually another workaround for this problem. In an insert/update trigger, a check can be added to verify whether there are any duplicate rows. If so,the trigger can delete 'm (when resulting from an insert) or reject 'm (when resulting from an update). However, this solution doesn't qualify as easy: you'll need to join all columns of the base table to the inserted table in the trigger - and remember we're talking about more than 31 columns here...
Also, the trigger 'solution' is nowhere near as fast as the ignore_dup_row option. With a non-unique clustered index, duplicate rows are detected immediately when they are inserted, directly at the start of the insert operation. The trigger-based solution will detect the duplicates only after they have been inserted. When there are additional nonclustered indexes, or many duplicates in multi-row inserts, the difference in performance can be significant.

By now, you're probaby wondering why you should bother about this whole thing in the first place. Ignoring duplicate rows by means of the ignore_dup_row option is a handy way of filtering out duplicates rows -- especially because you don't have to specify all columns in the index. I have used this feature various times as an efficient way of removing duplicates from a table: create a new table with the same columns, add a non-unique clustered index with ignore_dup_row, and simply insert all rows into this new table. When the insert is completed, all duplicate rows will have been removed.

Removing duplicate rows may not be an everyday requirement, but it isn't academic either. Anyway, if nothing else, were you already aware of the difference between ignore_dup_key and ignore_dup_row?

How would you rate this ASE quiz question?
Great    OK    Average    Boring     
May 2003
As we all know, probably through finding out the hard way, you cannot create a table inside a trigger since DDL is not allowed in a transaction by default. To allow this anyway, you need to enable the database option ddl in tran.

The question: how can you create a table from within a trigger without using ddl in tran ?
(hint: this is already possible since at least version 10, so it doesn't involve CIS...)

Answer:
The answer is surprisingly simple: just commit inside the trigger, and no transaction will be active anymore. Then you're free to create pretty much any table you want.
It goes like this:
create proc mytrigproc 
as
  select @@trancount "@@trancount in proc"
  create table tempdb..xxx (xxx_column1 int)
  select * from tempdb..xxx
go

create table mytab (a int)
go

create trigger mytrigger on mytab for insert
as
  while @@trancount > 0 commit
  exec mytrigproc
go
When inserting a row, table tempdb..xxx will be created automatically:
1> insert mytab values(1)
2> go
 @@trancount in proc
 -------------------
                   0

(1 row affected)

 xxx_column1
 -----------

(0 rows affected)
Let's look at the technical details involved in this trick:
  • First, the trigger commits up until the outermost transaction. Yes, you can do this in a trigger! (though it's not a good idea)
  • Then the trigger executes the stored proc. ASE actually checks whether a trigger contains a create table or select...into statement, and raises an error message during create trigger. But when you put the table creation in a stored proc, ASE won't notice.
  • Obviously, when inserting another row, the table already exists (but that's not the point here)
Warning:
Though nothing technically stops you from doing this kind of thing, your sense of responsibility as a DBA should. Needless to say, this sort of trick destroys any guarantee of transactional integrity: after an insert into this trigger, no rollback is possible anymore. Therefore: don't use this in your code!

(Frankly, this is one of those "solutions without a problem" -- I wouldn't know how this trick could be practically useful. But it's a nice one buy itself...)
How would you rate this ASE quiz question?
Great    OK    Average    Boring      
April 2003

It is common knowledge that select * will display all columns in a table.
But is this always true? Is it possible to create 'hidden' columns?

Answer:
The answers are 'no' and 'yes', respectively.
Before getting into details, let's review some basics. Check out this code:
1> create table mytable (a int)
2> go
1> select * from mytable
2> go
 a
 -----------

(0 rows affected)

1> select count(*) from syscolumns 
2> where id = object_id("mytable")
3> go

 -----------
           2

(1 row affected)
So, according to syscolumns, there are two columns in this table, although we created only one column. Eh ... ?

Don't question your sanity: you'll see this behaviour when the database option auto identity (or unique auto_identity index) is enabled for the current database. As documented, this will add a column named SYB_IDENTITY_COL to each table created without an identity column -- which is why there are two columns in the table, even though we specified only one in the create table statement. Also documented is that this column won't be visible when using select *.

So far, there's nothing special (you did know all this, didn't you?). The fun comes when you create a non-identity column named SYB_IDENTITY_COL: any column with this name just won't be visible with select *.

To explicitly create a column named SYB_IDENTITY_COL, we'll have to do a little trick, because you cannot simply create a column with this name:
1> create table mytable2 (a char(5), SYB_IDENTITY_COL int)
2> go
Msg 7368, Level 15, State 2
Server 'SYBASE', Line 1
Can not create column by name 'SYB_IDENTITY_COL'. This 
name has special meaning when 'auto_identity' option is ON.
We can work around this by renaming the column instead (note: run this in a database where auto identity is not enabled, or you'll get a 'column already exists' message):
1> create table mytable2 (a char(5), b int)
2> go
1> sp_rename 'mytable2.b', SYB_IDENTITY_COL
2> go
Column name has been changed.
(return status = 0)

1> select * from mytable2
2> go
 a
 -----

(0 rows affected)
We can insert rows into this table in the normal way, but the renamed column simply won't be included by select *:
1> insert mytable2 values ("Row 1", 1)
2> insert mytable2 values ("Row 2", 2)
3> insert mytable2 values ("Row 3", 3)
4> go
(1 row affected)
(1 row affected)
(1 row affected)

1> select * from mytable2
2> go
 a
 -----
 Row 1
 Row 2
 Row 3

(3 rows affected)
When renaming the column back, it will be included again:
1> sp_rename 'mytable2.SYB_IDENTITY_COL', b
2> go
Column name has been changed.
(return status = 0)

1> select * from mytable2
2> go
 a     b
 ----- -----------
 Row 1           1
 Row 2           2
 Row 3           3

(3 rows affected)
So you can indeed create a 'hidden' column in the sense that it won't be included in the results of select *, by simply renaming it to SYB_IDENTITY_COL. Note that the column can always be selected simply by naming it explicitly in the select list.
This trick will work for columns of any datatype. The reason that it works at all, is that SYB_IDENTITY_COL is not a T-SQL reserved word (try renaming a column to 'trigger' instead...). This also means that SYB_IDENTITY_COL is case-sensitive, and becomes an ordinary column name, without any special behaviour, when it's not fully in uppercase.

Frankly, I wouldn't immediately know a good application of this trick -- perhaps except for driving your collegues mad...

How would you rate this ASE quiz question?
Great    OK    Average    Boring      
March 2003

The ASE system stored procedures are usually named quite logically.
What do sp_reportstats, sp_clearstats, sp_flushstats, sp_modifystats and sp_object_stats have in common?

Answer:
Mostly, just the first and last part of their names. There is more similarity between the names of these procedures than between their functionality:
  • sp_reportstats reports the amount of CPU and I/O spent by each login. sp_clearstats resets the counters for these statistics.
    These two procedures are very old -- they were already there when I first worked with ASE in 1989. They are very much remnants of the mainframe era, where individual CPU seconds were charged to your project budget. My guess is that these stored procedures are hardly ever used by anyone today (in fact, did anyone ever use these procs at all?).
  • sp_flushstats has absolutely nothing to do with the two procedures mentioned above. sp_flushstats was introduced in 11.9, and explicitly flushes the in-memory statistics for a specific table to systabstats (these statistics are things like the rowcount, cluster ratio information, etc.; not the histogram & density information generated by update statistics). This flushing is normally done automatically by the housekeeper task, but in case the housekeeper is disabled, you can force the data to be flushed.
  • sp_modifystats allows you to manipulate the density statistics in sysstatistics, which were generated by update statistics.
    Using this procedure makes sense only when you have a very good understanding of the ins and outs of the ASE query optimizer. Typically, you'll be the kind of person who has read everything Eric Miner has written.
    This procedure was introduced in later versions of 11.9, and new functionality was added in 12.5.
  • sp_object_stats is in a completely different area. It will tell you on which tables the most lock contention occurs. This procedure is extremely useful when diagnosing concurrency problems.
    Unlike the 4 procedure above, which you may never use, sp_object_stats is a must-know for anyone who's interested in ASE performance. This procedure was introduced 11.9.
How would you rate this ASE quiz question?
Great    OK    Average    Boring      
February 2003

Recently, I was writing some SQL code where I needed to do X%100 ('X modulo 100'), where X was of the numeric(10) datatype. Because -unfortunate but true- ASE allows the modulo operator only on int, smallint or tinyint datatypes, I had to implement this function myself.
This should have been a simple, 30-second job: the following expression is equivalent to X%100:
select @X - ((@X/100)*100)
However, this did not work as expected: this expression always returns 0 for numeric datatypes. Why?

Answer:
Though mathematically correct, this expression always produces 0 as a result:
1> declare @X numeric(10)
2> select @X = 123
3> select @X - ((@X/100)*100)
4> go

 ------------------------------------
                        0.00000000000
On the other hand, the logic cannot be completely wrong: on an int datatype, the expression performs as expected (although we don't need it here because the modulo operator works for an int):
1> declare @X int
2> select @X = 123
3> select @X - ((@X/100)*100)
4> go

 -----------
          23

So the question is: why does this expression work fine for an int , but not for a numeric without decimals?
Obviously, I had overlooked something. I tried to narrow the problem down by simplifying the expression:
1> declare @X numeric(10)
2> select @X = 123
3> select (@X/100)*100, @X/100
4> go

------------------ -----------------
   123.00000000000     1.23000000000

My expectation had been that 123/100 = 1, truncated to an integer, because @X is defined to have no decimals (its scale is 0). However, these queries clearly show that this was an incorrect assumption...

After some head-scratching, a vague remembrance emerged about ASE having some rules about the scale and precision of numeric expressions. I actually had to look it up in the ASE documentation, and things started to become clear...

First, is it important to understand that the datatype of an expression is not necessarily the same as the datatypes of the terms in the expression. When an expression involves a numeric value and an int, the expression's result will be numeric.
Second, when performing a division involving numeric data, the quotient will also be numeric, with a scale (=#decimals) that is usually greater than that of the individual terms. So even though @X is a numeric(10) (i.e. with a scale of 0), the result of @X/100 has a datatype of numeric(21,11) Thus, the quotient is not truncated to an integer, but remains 1.23000000000. In my original code, this value is then multiplied by 100 again, so the final result is always 0.
In contrast, when @X is an int and no numeric value is involved, the quotient will also be an int, and therefore all decimals will immediately be truncated, and my original code works as expected.

So, finally, I rewrote my expression for doing a modulo on numeric value like this:
1> declare @X numeric(10)
2> select @X = 123
3> select @X - (floor(@X/100)*100)
4> go

 -------------------------
                        23
The floor() function converts the numeric quotient to an int, truncating the decimals. Overall, it took me about an hour to get to this point, and understand why...

If you're interested in the detailes, the story about datatype conversion rules, precision and scale, is described in the first chapter of the ASE Reference Manual, under "Datatype of mixed-mode expressions" -> 'Determining precision and scale".
I've always found this one of those 'boring' chapters in the ASE docs; the last time I read this material was when preparing for my first ASE 11.0 certification exam, years ago. Well, it wasn't as boring as I thought...

Lesson learned (1): don't expect calculations on int data to behave identically on numeric data!

Lesson learned (2): don't dismiss any part of the ASE documentation as 'boring'!

Update: As of ASE 1.2.5.4 and 15.0.2, the modulo operator works on all numeric datatypes, including 'numeric', 'money', 'float' etc.

How would you rate this ASE quiz question?
Great    OK    Average    Boring      
January 2003
A close and trusted friend, who works at a different company, has given you the source code for some handy DBA stored procedures which he's written himself. He assures you that the SQL code is valid Transact-SQL. Still, when executed in your ASE 12.5 server, his code causes many syntax errors. Your conclusion is that it is not Transact-SQL-compliant.
Is your friend a liar, a terrible programmer, or is your own judgement flawed?

Answer:
In fact, you may both be right -- the clue is in the definition of "Transact-SQL". But first, a bit of history.

A long time ago, Microsoft was a partner of Sybase, reselling the Sybase database server as "Microsoft SQL Server" for OS/2 and NT. In 1994, the partnership ended, and Microsoft went its own way with a copy of the source code. This led to a situation where Sybase was selling a product called "Sybase SQL Server" while Microsoft had a competing product called "Microsoft SQL Server". To distinguish itself better from MS, Sybase renamed its product to "Adaptive Server Enterprise" in 1997. Today, "SQL Server" is commonly associated with Microsoft's database product.

Against this background, both ASE and MS SQL Server have an SQL implementation called "Transact-SQL". Due to the common heritage, these SQL versions are quite similar. However, over the years, some incompatibilities have emerged as well. This means that you can have SQL code which is valid Transact-SQL in MS SQL Server, but not valid Transact-SQL in ASE (or the other way around).
As an example, the following Transact-SQL is valid in MS SQL server, but not in ASE:
select stdev(age) from students
-- stdev() is an aggregate to calculate standard deviation

Likewise, the following Transact-SQL works in ASE but not in MS SQL server:
begin tran
lock table MyTable in exclusive mode
-- explicitly locks a table
(these are just two examples; more incompatible features exist)

The question was aiming at this, somewhat confusing, situation. Therefore, whenever "Transact-SQL" is mentioned, check whether this concerns the Sybase or the Microsoft flavour.


It is not surprising that "Transact-SQL" and "SQL Server" are trademarks ("Transact-SQL" is even a registered trademark). However, it is interesting that these trademarks have actually long been owned by Sybase, not by Microsoft.
It is interesting that Microsoft has apparently chosen to stick with "Transact-SQL" rather than renaming it to something like "Microsoft SQL". I can only guess for the reasons why, but could it be that "Transact-SQL" has earned such a solid reputation that Microsoft doesn't want to throw this away?

It may be hard to admit for the Microsoft fans, but both Transact-SQL and SQL Server were not developed by Microsoft, but acquired from Sybase...

How would you rate this ASE quiz question?
Great    OK    Average    Boring      


More ASE quiz questions: 2013 - 2012 - 2011 - 2010 - 2009 - 2008 - 2007 - 2006 - 2005 - 2004 - 2003 - 2002 - 2001
For a randomly selected quiz question, click here.


 
 This document is located at www.sypron.nl/quiz2003a.html