StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p><em>Measure, don't guess!</em> This is something you should be doing yourself, with production-like data. <em>We</em> don't know the make-up of your data and that makes a big difference.</p> <p>Having said that, I wouldn't do it either way. I'd create another column, <code>column3</code> to store <code>column1</code> if non-NULL and <code>column2</code> if <code>column1</code> is NULL.</p> <p>Then I'd have an insert/update trigger to populate that column correctly, index it and use the screaming-banshee-speed:</p> <pre><code>select t.column3 as [result] from t </code></pre> <p>The <em>vast</em> majority of databases are read more often than written and it's better if this calculation is done as few times as possible (i.e., when the data changes, not every time you select it). If you want your databases to be scalable, don't use per-row functions.</p> <p>It's perfectly valid to sacrifice disk space for speed and the triggers ensure that the data doesn't become inconsistent.</p> <p>If adding another column and triggers is out of the question, I'd go for the <code>or</code> solution since it can often be split into two parallel queries by the smarter DBMS engines.</p> <p>An alternative, which MarkB gave but since deleted his answer so I'll have to go hunting for <em>another</em> good answer of his to upvote :-), is to use <code>UNION ALL</code>. If your DBMS isn't quite smart enough to recognise <code>OR</code> as a chance for parallelism, it may be smart enough to recognise <code>UNION ALL</code> in that context, something like:</p> <pre><code>select column1 as c from t where column1 is not NULL union all select column2 as c from t where column1 is NULL </code></pre> <p>But again, it depends on both your database and your data. A smart DBA would put the whole thing in a stored procedure so they could swap in a new method seamlessly should the data change its properties.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload