Sybase ASE
All versions
Last updated: 18 December 2011
ASE Quiz Questions 2011
This page contains some quiz-type ASE questions to test your ASE knowledge.
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 2011
As you no doubt know, running BCP-in to copy data from a file into a database table will not fire an insert trigger on that table.
This month's question: if I told you that BCP-out can in fact cause a trigger to fire, would you know how that could work? (assuming you'd believe me in the first place, of course).

Go here for the answer.

October 2011
I was recently contacted by an ASE user who had a requirement to store data in ASE after hashing it with SHA-256.
As of version 15.0.2, ASE provides the built-in functions hash() and hashbytes() are available, but these support only SHA and SHA-1 (as well as MD5), but not SHA-256. For some reason, other hashing algorithms were not acceptable to use.
Eventually though, this ASE user found a way to use SHA-256 inside ASE.

How would you have solved this problem?

Go here for the answer.

August 2011
As you probably know, it is possible to protect an ASE role with a password: in order to activate the role you have to specify the password (i.e. set role supervisor_role with passwd MyS3cret on).
Recently a question came up from a security-conscious user, if you could also put passwords on system roles like sa_role and sso_role.

The answer is: yes, you can do that. However, it is a really bad idea. Why?

Go here for the answer.

May 2011
As a DBA, sometimes you may want to know the schema of a #temp table that a particular session is currently using. Running sp_help would be the logical first thing to try, but that doesn't work for #temp tables.
It is possible to figure out the details of a #temp table by using the trick described here, which involves dumping tempdb and loading it into another database. However, this can take some time when the temporary database in question is big.

Is there a quicker way to get the schema of someone else's #temp table?

Go here for the answer.

April 2011
Let's say you need to do some OLAP-style processing in ASE, such as calculating the 6-month exponentially weighted moving average over some monthly sales data.
Since ASE does not provide OLAP functions, you'd have to manually write a workaround to implement this requirement with the existing ASE features. That's not impossible, but such workarounds quickly become complex, slow and clumsy.

Now, let's assume you also have Sybase IQ available (with all required licenses in place).
IQ 15.1 (or later) happens to provide the analytic function exp_weighted_avg() -- exactly what you need. You could of course copy the ASE data into IQ and then run that OLAP query in IQ instead of in ASE. But having two copies of the data is not convenient, and introduces new complexities like keeping both copies synchronised.

How can you implement the required OLAP functionality in ASE without storing any ASE data in IQ?

Go here for the answer.

March 2011
When using a raw device for an ASE database, you have to make sure you're using the character device, not the block device. This is because data could potentially be lost in case of a sudden system failure when the block device is used.
Sometimes it happens that a DBA inadvertently picks the wrong device type. On some platforms, an error message will then be raised, but other platforms will not.

How can you verify whether a particular raw device is a character device or a block device?

Go here for the answer.

February 2011
This month's quiz question is about the following valid SQL statement.
Even if you haven't been drinking, you probably get this feeling there's something odd about this piece of SQL code.
The question: what's odd about it, and why is this statement still valid?
1> select difference from space
2> where datalength between lockscheme and pagesize
3> group by right(stuff,left)
4> having ceiling(floor) < floor(ceiling)
5>    and night = day
6> go
 The difference between *what* ?

(1 row affected)
Go here for the answer.

January 2011
Most ASE users are well aware that updating a row causes that row, or that row's data page, to be exclusively locked for the duration of the transaction. Such an exclusive lock blocks all access to that row from other sessions.
However, there are two exceptions to this rule.
The first is with "transaction isolation level 0": a select statement at level 0 will read rows being modified whose transaction is not yet committed (note that isolation level zero is not without risks -- but that's a topic for another time).

What is the other exception? (which, incidentally, is totally risk-free)

Go here for the answer.

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