The Wiert Corner – irregular stream of stuff

Jeroen W. Pluimers on .NET, C#, Delphi, databases, and personal interests

  • My badges

  • Twitter Updates

  • My Flickr Stream

  • Pages

  • All categories

  • Enter your email address to subscribe to this blog and receive notifications of new posts by email.

    Join 1,616 other followers

Archive for the ‘Database Development’ Category

SQL Server 8060 row size query limit

Posted by jpluimers on 2019/06/19

Every now and then you bump into a limit you did not know it existed before:

Cannot create a row of size 8209 which is greater than the allowable maximum row size of 8060.

This is while querying a few tables having a combined column size of more than 8060. Which means it is not just about single tables being too wide.

Related:

–jeroen

Posted in Database Development, Development, SQL, SQL Server | Leave a Comment »

Firebird CASE construct

Posted by jpluimers on 2019/06/12

Reminder to self: sort out why a while ago I had trouble with a nested [WayBackCASE construct causing parsing anomalies in 2.5.8.

–jeroen

Posted in Database Development, Development, Firebird, Software Development | Leave a Comment »

Testing for NULL and equality in practice

Posted by jpluimers on 2019/05/30

Great article: Testing for NULL and equality in practice [WayBack]

Most important table piece I learned:

 Testing (in)equality of A and B in different Firebird versions

Test type Firebird version
<= 1.5.x >= 2.0
Equality
A = B or A is null and B is null
A is not distinct from B
Inequality
A <> B
or A is null and B is not null
or A is not null and B is null
A is distinct from B

–jeroen

Posted in Database Development, Development, Firebird, SQL | Leave a Comment »

sql – How to Select Every Row Where Column Value is NOT Distinct – Stack Overflow

Posted by jpluimers on 2019/05/22

Since I always forget that IN and EXISTS have very different performance characteristics: [WayBack] sql – How to Select Every Row Where Column Value is NOT Distinct – Stack Overflow

Less slow

select emailaddress,customername from customers where emailaddress in
(select emailaddress from customers group by emailaddress having count(*) > 1)

Slow

select emailaddress,customername from Customers a where exists
(select emailaddress from customers c where a.customerName != c.customerName and a.EmailAddress = c.EmailAddress)

–jeroen

Posted in Database Development, Development, Software Development, SQL | Leave a Comment »

UPDATE OR INSERT

Posted by jpluimers on 2019/04/25

I totally forgot that Firebird as of 2.5 supports [WayBackUPDATE OR INSERT, so as a reminder to self:

UPDATE OR INSERT INTO
   {target} [({column_list})]
   VALUES ({value_list})
   [MATCHING ({column_list})]
   [RETURNING {values} [INTO {variables}]]

{column_list}      ::=  colname    [, colname   ...]
{value_list}       ::=  value      [, value     ...]
{ret_values}       ::=  ret_value  [, ret_value ...]
{variables}        ::=  varname    [, varname   ...]

–jeroen

Posted in Database Development, Development, Firebird | Leave a Comment »

 
%d bloggers like this: