The Wiert Corner – irregular stream of stuff

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

  • My work

  • 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,811 other followers

Archive for the ‘Database Development’ Category

Visual Representation of SQL Joins – CodeProject

Posted by jpluimers on 2017/08/02

I thought I posted a reference to this a long time ago, but didn’t.

It’s one of the things I show when explaining joins to people. Sometimes I need it myself too (:

The article explains these in greater detail:

  • INNER JOIN
  • LEFT JOIN
  • RIGHT JOIN
  • OUTER JOIN
  • LEFT JOIN EXCLUDING INNER JOIN
  • RIGHT JOIN EXCLUDING INNER JOIN
  • OUTER JOIN EXCLUDING INNER JOIN

Note:

  • the opposite of INNER JOIN is not OUTER JOIN. It’s OUTERJOIN EXCLUDING INNER JOIN
  • the opposite of OUTER JOIN is empty set.

But the diagram is usually speaks for itself.

–jeroen

Source: Visual Representation of SQL Joins – CodeProject

Read the rest of this entry »

Posted in Access, Database Development, DB2, Development, Firebird, InterBase, MySQL, OracleDB, PostgreSQL, SQL, SQL Server | Leave a Comment »

Firebird 3.0 – Monitor Need to monitor you firebird 3.0 database? This just works…

Posted by jpluimers on 2017/07/20

Interesting tool as it just works magically:

Firebird 3.0 – MonitorNeed to monitor you firebird 3.0 database? We have the right tool for you!

–jeroen

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

Firebird: reset statistics for all indices using `set statistics index :index_name` – via: What is index selectivity?

Posted by jpluimers on 2017/07/12

A while ago, I needed to reset the index statistics for all indices in one our databases: a Firebird database.

I hadn’t done work like this in a while, so I made the cardinal mistake of Googling and using the first matching StackOverflow answer that seemed a perfect fit.

Boy was I wrong (:

In sql – My firebird query plan does not use correct index – Stack Overflow  I found the accepted answer (no less!) has this neat block to update index statistics for all indices:

    EXECUTE BLOCK
    AS
      DECLARE VARIABLE IDX VARCHAR(31);
    BEGIN
      FOR
        SELECT rdb$index_name FROM rdb$indices
        WHERE NOT rdb$index_name LIKE 'RDB$%'
        INTO :idx
      DO BEGIN
        EXECUTE STATEMENT 'update statistics ' || :idx
        WITH AUTONOMOUS TRANSACTION; 
      END
    END

The code consistently failed with this error:

    Script Error:
    
    Dynamic SQL Error
    SQL error code = -104
    Unexpected end of command - line 1, column 19

When I removed the lines inside the do beginend block, then the outer statements execute fine.

Incidentally, column 19 is right at the space at the end of after the 'update statistics '. Which meant that either the string concatenation was wrong, or the statement itself.

And yes, I should have Googled the Firebird documentation instead, as What is index selectivity? has the correct statement that recalculates statistics for all indexes:

set term !! ;

EXECUTE BLOCK AS
declare variable index_name VARCHAR(31);
BEGIN
for select RDB$INDEX_NAME from RDB$INDICES into :index_name do
execute statement 'SET statistics INDEX ' || :index_name || ';';
END!!

set term ; !!

–jeroen

Source: What is index selectivity?

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

Firebird: creating indices in scripts after checking for their existence

Posted by jpluimers on 2017/07/11

A few notes when making scripts that create indexes based if they do (not) exist (yet):

–jeroen

Read the rest of this entry »

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

Firebird; testing for NULL and equality in practice: IS [NOT] DISTINCT FROM

Posted by jpluimers on 2017/06/14

Boy, I totally missed that this was introduced back then in Firebird 2.0:

 In Firebird 2 and higher, you test for null-encompassing equality with DISTINCT. This has already been discussed, but here’s a quick recap. Two expressions are considered:
  • DISTINCT if they have different values or if one of them is NULL and the other isn’t;
  • NOT DISTINCT if they have the same value or if both of them are NULL.

[NOT] DISTINCT always returns true or false, never NULL or something else. Examples:

if (A is distinct from B) then...
if (Buyer1 is not distinct from Buyer2) then...

Splendid!

Source: Testing for NULL and equality in practice

–jeroen

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

 
%d bloggers like this: