Sybase ASE
All versions
Last updated: 19 December 2010
ASE Quiz Questions 2010
 
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 2010
This month's quiz question would make a fine after-Christmas-dinner discussion topic at the Secret Society of SQL Specialists - but use at your own risk in less nerdy settings!
Anyway, the topic of this question was brought up by a Sybase user who tried to understand what he was seeing; let's call him 'OP'.

OP ran into the following situation.
The two select queries below select identical results, but they return the results in a different order. Yet, in both cases, the order by clause is identical, namely: order by t.B. This felt strange to OP, and he suspected ASE might be at fault here. After all, the sort order is clearly defined to be according to column 'B' in table 't', right?

Check out the example below, and run it in your own ASE server to see how it behaves.
Then, before looking at the answer, decide on your verdict: is ASE correct here, or is this a bug?

This is a stripped-down version of OP's problem:
1> create table t (A int, B char(2))
2> insert into t (A, B) values(1, 'az')
3> insert into t (A, B) values(2, 'bb')
4> go

-- query #1
1> select A, reverse(B) as C from t order by t.B
2> go
 A           C
 ----------- ---
           1  za
           2  bb

(2 rows affected)


-- query #2
1> select A, reverse(B) as B from t order by t.B
2> go
 A           B
 ----------- ---
           2  bb
           1  za

(2 rows affected)
Made up your mind?
Then go here for the answer.

November 2010
Being lazy can be a virtue. I prefer to type as few keystrokes as I can when working with ASE -- not only because it's quicker, but also to avoid carpal tunnel syndrome from striking again.
So, what's the smallest number of keystrokes you have to type to enable the configuration parameter 'allow updates to system tables' ? -- something I need to type pretty often.

Go here for the answer.

October 2010
This month's quiz question starts with the answer. The challenge for the reader (that's you) is to figure out the question.

Recently I found myself deleting rows from a multi-million row table in the following way:
1> delete MyTable where rand2()*100 < 1
2> go 225
So, why would I do such a thing? (Clearly, to remove rows -- no points for that. Why am I doing it this way ?)

Go here for the answer.
And go here for an update to the answer.

September 2010
As you know, the procedure cache is an important shared resource in ASE: to run a query, we need to have a query plan, which in turn needs some space in the procedure cache in order to exist in the first place. If you don't have enough procedure cache space, your query runs into a 701 error:
Msg 701, Level 17, State 3
Server 'SYBASE', Line 1
There is not enough procedure cache to run this procedure, trigger, or SQL batch. 
Retry later, or ask your SA to reconfigure ASE with more procedure cache.
Some time ago, the question came up how to generate a 701 error at will. The context was the need to see what the actual impact of a 701 error on an existing application would be, and how to determine afterwards that this error condition had taken place.
So, the question: how can you reduce the amount of available stored procedure cache in a controlled manner so that a 701 error will occur?

Go here for the answer.

August 2010
Let's say you need to guarantee that a certain database table (let's call it 't') is always updated in a particular way, for example in conjunction with other tables or subject to certain business rules. The classic way to achieve this guarantee is to code a stored procedure (let's call it 'p'; no prizes for original naming today) that performs the update of t with all required processing and error checking. By granting execute permission on procedure p but not granting insert/update/delete permissions on table t itself, you can guarantee that non-table-owners cannot update table t other than through the stored procedure p.

This is all well-known stuff.
But it's only one half of the guarantee, if you really care about it.

So let's go one step further: how can you guarantee that table t is only updated through that same stored procedure p, even by the table's owner? (let's assume the table's object owner also has sa_role, so he's the all-might database owner too). Nothing stops the table owner from updating table directly t, ignoring stored procedure p.

How can you still guarantee that even the table owner doesn't update table t directly, but uses procedure p instead ?

Go here for the answer.

July 2010
As you know (I'm assuming), when a client connects to the ASE server, it sets a bunch of connection attributes. These include the username and password, the servername being connected to, the network packet size to be used, and possibly many more.
For some of these connection attributes, their values are visible once connected to ASE: for example, the hostname, application name and network packet size are available in master..sysprocesses, in columns hostname, program_name and network_pktsz, respectively.
It can be useful to specify a meaningful value for the application name in your application. Not only does this allow you (as well as code in a login trigger) to recognise your application, but also some ASE features use the application name. For example, resource limits and user-defined temporary databases can be bound to applications with a specific name.

Unfortunately, not all applications set meaningful values in these attributes. If you have a third-party application which you'd like to connect to ASE using a specific network packet size, or specify a particular application name, this isn't easy since you cannot modify the compiled code of that application.
Yet, there is a way to specify custom values for such connection properties, even when you're unable to modify the application itself.

How?

Go here for the answer.

May 2010
Here is an issue I stumbled over last week.
As you probably know, ASE 15 has increased the maximum length for many identifiers from 30 to 255 bytes. This applies to names of tables, column, stored procedures etc.
I was writing a small piece of SQL to run a particular test, and as it happened, the length of a name of one of the tables exceeded 30 bytes.
The reason I noticed the identifier was longer than 30 bytes was that this error message was raised:
1> create table this_is_a_kinda_long_table_name (a int)
2> go
Msg 103, Level 15, State 205
Server 'SYB155', Line 1
The identifier that starts with 'this_is_a_kinda_long_table_nam' is too long. 
Maximum length is 30.
Now that's strange!
I double-checked I wasn't accidentally connected to a 12.x server (and I wasn't, this was ASE 15.5). But if this was ASE 15.x, then a 31-character table name should be allowed....

What's going on here?

Go here for the answer.

April 2010
Here's one from the Crazy Tricks Department: We're always about making SQL queries run faster -- but what about the opposite? What about making a query run slower?
So, let's just say you want to slow down a query against a particular table; how would you achieve that?
Apart from the technical aspect, here's question #2: what *valid* reason (i.e. not for just messing up systems) could you have for wanting to slow down, rather than speed up, a query?

Go here for the answer.

March 2010
I never know in advance what my next quiz question will be about, but this week the topic came to me easily. I wasted an evening trying to solve a wacky problem while getting some SQL to run on a system (a separate box) that was being prepared for demo purposes.
This turned out to be one of those cases where it's proven true that there's always something left to learn (as I mentioned last month).

The problem first surfaced when using sp_autoformat to format the contents of a table (if you don't know sp_autoformat, you should get acquainted -- try running 'sp_autoformat sysusers' as an example of what it does)
sp_autoformat is supposed to format each column as narrow as possible, given the actual data values it contains, trimming off trailing spaces from [var]char columns, among other things.
My problem was that sp_autoformat was formatting all [var]char columns with a bunch of spaces at the end, which is exactly what should NOT happen. Worse, it really messed up the formatting of some output that was supposed to fit on an 80-character window.

After checking my own SQL code was not at fault, I zoomed in on what sp_autoformat was doing (try adding the @trace=4 parameter). It seemed sp_autoformat was doing everything right, yet every column was still formatted longer than the longest value it contained. Weird.

Eventually I could simplify the problem and reproduce it without sp_autoformat, as follows:
1> select substring('abc', 1, 3)
2> go

 ------
 abc

(1 row affected)
Can you spot the problem? I'm doing a substring() whose result should be 3 characters long, but the resulting column is in fact 6 characters.
This seemed to defy all logic. What on earth is going on here?

Go here for the answer.

February 2010
In 2010, it's 21 years ago I got involved in what turned out to be my first project involving ASE (then named Sybase SQL Server).
After so many years, I liked to think I knew what was worth knowing about that very first thing you encounter when entering into the world of ASE: the isql utility.

Turns out, I was wrong.

While doing some performance benchmarking recently, I ended up staring at some strange-looking results that just couldn't be right.
In essence, this was a comparison between a stored proc and isql both doing the same 10,000 simple selects (this was not what was I was testing, but where I ended up after digging into the results).
The stored proc looked like this:
create proc p
as
  declare @cnt int
  set @cnt = 0

  while @cnt < 10000
  begin
	  select * from my_tab
	  set @cnt = @cnt + 1
  end
go
The stored procedure was then kicked off with a straightforward isql script:
exec p
go 
The isql-based test just took the following simple script as input:
select * from my_tab
go 10000
(I've written about the 'go 10000' approach before; see the May 2005 quiz question for more).

Now, the strange thing was that, even though in both cases the exact same query was executed 10,000 times, the test with the stored procedure was about 4 times slower than than the test with the 'go 10000' script. That made no sense, since the stored proc test should be faster, or at least equal, since there's less processing overall.
Here's why: the 'go 10000' test sends the query to the server 10,000 times, while the stored proc test send the 'exec p' to the server only once. Otherwise, both tests perform the same select query 10,000 times, and produce the same 10,000 result sets.
So logically, the 'go 10000' test should be slower than the stored proc test since it has to do more client-server roundtrips. But what I was seeing is that it was actually 4 times faster than the stored proc test.

The statement cache was enabled (so there was no optimizer overhead anywhere), and I had verified that the query plans and I/O counts were identical. The table had 100 rows and 20 columns, and the data was static. There were no other users in the server.

What was going on?

Go here for the answer.

January 2010
You have a SQL script file containing tables, data, stored procedures etc., which are an essential part of the applications running on your ASE system. Periodically, as changes to your applications are implemented, the contents of this script changes too, and it needs to be re-run (with 'isql') against the ASE database for the application.
When running this script, you're assuming a database named 'PROD' to exist, since this is the place where the tables, stored procedures (etc) are located. However, you need to be 100% sure that this database indeed exists, and also that it can be accessed (it could inacessible due to having been marked 'suspect' for example, or it could have been put in single-user mode or in dbo-use-only mode).
So, if for some reason, there is no database named 'PROD', or it cannot be used, the script should not run, but raise an error message indeed.
This is less trivial to implement than it sounds.

The question: How can you conditionally abort execution of your SQL script somewhere in the middle of the script when database 'PROD' cannot be accessed?

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 www.sypron.nl/quiz2010.html