Firebird/InterBase Quiz
This is just for fun - no prizes are offered (except of publishing
your name in big font if you
send me
correct answers :-)
I received lot of answers, but none correct so far :-(
How would you change character set of table's column
(already populated with data containig national characters),
without copying data to different column/table/database/file ? (i.e. do it "in place")
Details:
Sometimes people create new database with character set NONE, populate it with data,
and then decide to change character set of some columns to something more suitable,
like ISO8859_1.
The problems are
- There is no
ALTER command to achieve it directly in SQL.
- When you change character set by modifying system tables, IB/FB will not touch the data;
instead, it will create new format for each modified table
(as with other changes to table structure, like adding new columns etc.).
This is o.k. if the table is empty, but can cause big problems if it is already
populated with data, because
- It is not possible to directly convert string in
NONE character set into string
that is e.g. ISO8859_1 (unless it contains only ASCII characters);
otherwise you get the famous "Cannot transliterate character between character sets" error.
Thus, when just changing character set in system tables old data become unreadable!
(the error will occur during read, when IB/FB tries to convert old data to new format).
When update conflict occur in WAIT transaction, it goes into wait state.
What is it that this transaction waits for ?
Does it wait for offending record (to be "unlocked"),
or for the other transaction (to be committed/rollbacked) ?
Is there any difference at all ?
Suppose we have database with no tables, external tables, UDFs, ...
There is only one procedure with one VARCHAR output parameter:
CREATE PROCEDURE proc
RETURNS (str VARCHAR(10)) AS
BEGIN
...
END
When you execute this procedure, it will return string
consisting of 5 ASCII NUL characters (binary zeroes).
What is in the body of SP ?
SOLUTION
Suppose you can choose between these two select statements
(they will give you the same result sets):
(A)
SELECT *
FROM tab
WHERE x='YES' OR x='NO' OR x='PERHAPS' OR x='MAYBE';
(B)
SELECT *
FROM tab
WHERE x IN ('YES','NO','PERHAPS','MAYBE');
What would you do if you are concerned about speed ?
- Choose (A) because it is always faster than (B)
- Choose (A) because it is equally fast or faster than (B)
- Choose (B) because it is equally fast or faster than (A)
- Choose (B) because it is always faster than (A)
- Test both because IB's optimizer is unpredictable
- Pick arbitrary one because they are equivalent
- Something else ...
(Some kind of evidence is better than just guessing)
SOLUTION
Ivan Prenosil (2002)