|
|
|||||||||||||||||||||
|
This page contains some quiz-type ASE questions to test your
ASE knowledge; a little diversion for those moments when
there's nothing better to do. Please note that these questions are not intended as test questions for ASE certification exams: they'll rather be about strange, obscure, bizarre or little-known ASE features. Also, these might well be solutions for which we haven't found a problem yet... (but it might help your certification exam preparations anyway...). I'll try to add a new question every month. 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 goThe stored procedure was then kicked off with a straightforward isql script: exec p goThe 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: 2010 - 2009 - 2008 - 2007 - 2006 - 2005 - 2004 - 2003 - 2002 - 2001 |
|||||||||||||||||||||
|
