SQL - What is Sargable?

Basically a query is Sargable if it can make use of indexes to improve performance.

What makes a query non-Sargable?
The most common way of making a query non-Sargable is by reference a field in a function in the where clause of a query.

For example:

SELECT ... FROM ...
WHERE Year(myDate) = 2008

This can be better written as:

WHERE myDate >= '01-01-2008' AND myDate < '01-01-2009'

So .. moral of the story ..
Avoid using functions that references a field inside the where clause of your query.

Hibernate Flush vs Commit

Flush syncs your hibernate session state to your database, BUT it still can be rolled back.

One example of where I had to flush the database is when I use HQL to query the database expecting that some of the previous changes I made to already be in the database.

E.g.
Assuming entity.A = 0 before the hibernate session began.

1. entity.setA(10);
2. invoke HQL to getA (select A from entity .. or something like that)

Line 2 will actually return 0.  NOT 10.

entity.A is 10 in the hibernate session, but not in the database.
to sync the current state to the database but still allowing the transaction to be rolled back if something goes wrong, we can perform a flush.

1. entity.setA(10);
2. session.flush()
3. invoke HQL to getA (select A from entity .. or something like that)

Line 3 should now return 10.


How does it work?
Not really sure.. I am guessing database as an extra layer to store flushed changes, before committing..


Is Flush Isolated?
i.e. If in one hibernate transaction I perform a flush, will I be able to see the changes in SSRS?

The answer depends on the database ISOLATION level.  If it's set to READ_COMMITTED, then you wouldn't be able to see it... if it's set to READ_UNCOMMITTED then you probably can..


Warning
Flush can be expensive.. hibernate probably has to figure out all the entities which has been changed/created/deleted and then write it to the database...

I wonder if that means if a flush is called 2x consecutively will the 2nd time be really quick??