Immutable pure-SQL timestamp->epoch conversion

postgresql-12

PeterVandivierPeterVandivier

7Yes, you can: just convert to `timestamp` at UTC. This is not what you might expect.

Jack Douglas

Why does adding a TOP allow the SQL Server query optimizer to find a plan that can honor a FORCESEEK hint?

sql-server

Joe ObbishJoe Obbish

12Although the query text contains an `APPLY`, the optimizer rewrites this to a regular join using the `ApplyHandler` rule early in the compilation process (during simplification). The optimizer prefers joins over applies because it has more transformations for joins[^1](For background on the difference between join and apply, see my article [Apply versus Nested Loops Join](https://www.sql.kiwi/2019/06/apply-versus-nested-loops-join.html)).

Paul White

7To supplement Paul's answer, ironically, the optimizer is capable of producing apply plan for the original query, if `OUTER APPLY` is used instead of the `CROSS APPLY`

i-one

Missed optimisations for ScalarGbAggToTop with MAX(nullable_column)

sql-server

Martin SmithMartin Smith

12You're quite right about the reason for the separate `NULL` and `NOT NULL` branches.

Paul White

Is there a risk of race conditions when adding a new connection that will only make atomic inserts?

mysql

SigmaSigma

Why does only one update work in this query?

postgresql-12

Jack DouglasJack Douglas

6It [is documented](https://www.postgresql.org/docs/12/queries-with.html#QUERIES-WITH-MODIFYING) that this is not supported for data-modifying statements in `with` clauses:

Jack Douglas

Why does my query not insert any records

postgresql-12

AnonymousAnonymous

3`SAMPLE` is a different table from `"SAMPLE"`. PostgreSQL lower-cases unquoted table names to conform (its interpretation of) to the SQL standard.

PeterVandivier

Handling Erroneous Data Entry in SQL

sql-server-2012

DS ChainsDS Chains

6[`TRY_CONVERT`](https://docs.microsoft.com/en-us/sql/t-sql/functions/try-convert-transact-sql?view=sql-server-ver15) is what you are after:

Jack Douglas

How do you insert rows into mutually-referencing tables in an SQL function?

postgresql-12

Jack DouglasJack Douglas

5I'm not sure this is a sensible general solution as I suspect it may cause concurrency issues, but if you choose a dummy value that will never be present in the table you can insert that, and then update it away again, in a pure `sql` function:

Jack Douglas

Confused about STRING_SPLIT

sql-server-2019

Humble noviceHumble novice

11`STRING_SPLIT` [is a table-valued function](https://docs.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql?view=sql-server-ver15):

Jack Douglas

Why is there an extra predicate with an IN clause?

sql-server-2019

ForrestForrest

9The extra predicate on `#B` is added during query simplification by the "implied predicate" mechanism.

Martin Smith