SQLDaily
@sqldaily.bsky.social
Daily Oracle SQL tips from the Oracle Developer Advocates for SQL
In PL/SQL
EXCEPTION WHEN OTHERS THEN
Catches all errors
Filling your code with this without reraising the error hides unexpected issues
So the compiler raises PLW-6009 to warn you
If you reraise in custom logging procedures use
PRAGMA SUPPRESSES_WARNING_6009
To skip this warning
EXCEPTION WHEN OTHERS THEN
Catches all errors
Filling your code with this without reraising the error hides unexpected issues
So the compiler raises PLW-6009 to warn you
If you reraise in custom logging procedures use
PRAGMA SUPPRESSES_WARNING_6009
To skip this warning
November 17, 2025 at 12:02 PM
In PL/SQL
EXCEPTION WHEN OTHERS THEN
Catches all errors
Filling your code with this without reraising the error hides unexpected issues
So the compiler raises PLW-6009 to warn you
If you reraise in custom logging procedures use
PRAGMA SUPPRESSES_WARNING_6009
To skip this warning
EXCEPTION WHEN OTHERS THEN
Catches all errors
Filling your code with this without reraising the error hides unexpected issues
So the compiler raises PLW-6009 to warn you
If you reraise in custom logging procedures use
PRAGMA SUPPRESSES_WARNING_6009
To skip this warning
Get free access to Oracle AI Database at freesql.com
This browser-based tool enables you to run and share #SQL statements
Even better, you can now connect to your schema from clients on your machine using SQL*Net
So you can run SQL from your favourite IDE
This browser-based tool enables you to run and share #SQL statements
Even better, you can now connect to your schema from clients on your machine using SQL*Net
So you can run SQL from your favourite IDE
November 14, 2025 at 12:01 PM
Get free access to Oracle AI Database at freesql.com
This browser-based tool enables you to run and share #SQL statements
Even better, you can now connect to your schema from clients on your machine using SQL*Net
So you can run SQL from your favourite IDE
This browser-based tool enables you to run and share #SQL statements
Even better, you can now connect to your schema from clients on your machine using SQL*Net
So you can run SQL from your favourite IDE
Macros in Oracle #SQL enable you to define expression templates as text
At parse time the database merges these with the parent query, replacing parameters with the actual values passed
Macros come in two types
Scalar - use in SELECT, WHERE, ORDER BY, etc.
Table - use in FROM
At parse time the database merges these with the parent query, replacing parameters with the actual values passed
Macros come in two types
Scalar - use in SELECT, WHERE, ORDER BY, etc.
Table - use in FROM
November 10, 2025 at 12:02 PM
Macros in Oracle #SQL enable you to define expression templates as text
At parse time the database merges these with the parent query, replacing parameters with the actual values passed
Macros come in two types
Scalar - use in SELECT, WHERE, ORDER BY, etc.
Table - use in FROM
At parse time the database merges these with the parent query, replacing parameters with the actual values passed
Macros come in two types
Scalar - use in SELECT, WHERE, ORDER BY, etc.
Table - use in FROM
Oracle Database 23ai becomes Oracle AI Database 26ai when you apply the Oct 2025 Release Update
This adds a GRAPHQL table function that
Accepts #GraphQL queries
Maps fields to database tables and columns
Returns JSON
e.g.
SELECT * FROM GRAPHQL ( ' tab { col1 col2 } ' )
This adds a GRAPHQL table function that
Accepts #GraphQL queries
Maps fields to database tables and columns
Returns JSON
e.g.
SELECT * FROM GRAPHQL ( ' tab { col1 col2 } ' )
![GRAPHQL table function (23.26)
Use in the FROM clause; accepts GraphQL query as a string and returns JSON
Maps GraphQL fields to database tables and columns; returns the results as a one column named DATA
Example query
-- Get an array of employees in IT
select * from graphql ( '
hr.departments (
department_name: "IT"
) { department_id hr.employees [
{ first_name, last_name }
]
}'
);
-- Maps to a query like
select json_object (
'department_id' : department_id,
'employees' : (
select json_arrayagg (
json_object ( … )
)
from hr.employees e
where e.department_id
= d.department_id
)
) data
from hr.departments d
where department_name = 'IT';](https://cdn.bsky.app/img/feed_thumbnail/plain/did:plc:blmnab373idbogizdn742doc/bafkreicicluxqtdxf7326m22haokjophccchqfcczt3w6nnpnoe7mhcdai@jpeg)
October 23, 2025 at 11:03 AM
Oracle Database 23ai becomes Oracle AI Database 26ai when you apply the Oct 2025 Release Update
This adds a GRAPHQL table function that
Accepts #GraphQL queries
Maps fields to database tables and columns
Returns JSON
e.g.
SELECT * FROM GRAPHQL ( ' tab { col1 col2 } ' )
This adds a GRAPHQL table function that
Accepts #GraphQL queries
Maps fields to database tables and columns
Returns JSON
e.g.
SELECT * FROM GRAPHQL ( ' tab { col1 col2 } ' )
Apply the Oct 2025 Release Update and Oracle Database 23ai becomes Oracle AI Database 26ai
This adds the QUALIFY clause
QUALIFY filters window functions after grouping
Like the HAVING clause does for aggregate functions
SELECT ... FROM ...
QUALIFY fn OVER ( ... ) > ...
This adds the QUALIFY clause
QUALIFY filters window functions after grouping
Like the HAVING clause does for aggregate functions
SELECT ... FROM ...
QUALIFY fn OVER ( ... ) > ...
October 20, 2025 at 11:02 AM
Apply the Oct 2025 Release Update and Oracle Database 23ai becomes Oracle AI Database 26ai
This adds the QUALIFY clause
QUALIFY filters window functions after grouping
Like the HAVING clause does for aggregate functions
SELECT ... FROM ...
QUALIFY fn OVER ( ... ) > ...
This adds the QUALIFY clause
QUALIFY filters window functions after grouping
Like the HAVING clause does for aggregate functions
SELECT ... FROM ...
QUALIFY fn OVER ( ... ) > ...
Got unaggregated columns you want to include in the SELECT list, but not the GROUP BY?
You can use ANY_VALUE, e.g.:
SELECT c1, ANY_VALUE ( c2 ), COUNT ...
FROM ...
GROUP BY c1
This returns the value from a random row (it's optimized to return the first) => non-deterministic
You can use ANY_VALUE, e.g.:
SELECT c1, ANY_VALUE ( c2 ), COUNT ...
FROM ...
GROUP BY c1
This returns the value from a random row (it's optimized to return the first) => non-deterministic
October 16, 2025 at 11:02 AM
Got unaggregated columns you want to include in the SELECT list, but not the GROUP BY?
You can use ANY_VALUE, e.g.:
SELECT c1, ANY_VALUE ( c2 ), COUNT ...
FROM ...
GROUP BY c1
This returns the value from a random row (it's optimized to return the first) => non-deterministic
You can use ANY_VALUE, e.g.:
SELECT c1, ANY_VALUE ( c2 ), COUNT ...
FROM ...
GROUP BY c1
This returns the value from a random row (it's optimized to return the first) => non-deterministic
Define which prior rows to include in window functions with
ORDER BY ... [ frame ] ... PRECEDING
The frame states which sort keys to include (UNBOUNDED = all)
ROWS - Strict row count
RANGE - Logical value offset; only valid for numbers & datetimes
GROUPS - Unique values count
ORDER BY ... [ frame ] ... PRECEDING
The frame states which sort keys to include (UNBOUNDED = all)
ROWS - Strict row count
RANGE - Logical value offset; only valid for numbers & datetimes
GROUPS - Unique values count
October 14, 2025 at 11:02 AM
Define which prior rows to include in window functions with
ORDER BY ... [ frame ] ... PRECEDING
The frame states which sort keys to include (UNBOUNDED = all)
ROWS - Strict row count
RANGE - Logical value offset; only valid for numbers & datetimes
GROUPS - Unique values count
ORDER BY ... [ frame ] ... PRECEDING
The frame states which sort keys to include (UNBOUNDED = all)
ROWS - Strict row count
RANGE - Logical value offset; only valid for numbers & datetimes
GROUPS - Unique values count
Oracle Database 21c enhanced FOR LOOPs in PL/SQL to enable you to use
Custom increments (BY)
Lists of values to loop through
Conditions to skip the loop body (WHEN)
Expressions to set the next value (REPEAT WHILE)
Mutable iterands to set their value inside the loop (MUTABLE)
Custom increments (BY)
Lists of values to loop through
Conditions to skip the loop body (WHEN)
Expressions to set the next value (REPEAT WHILE)
Mutable iterands to set their value inside the loop (MUTABLE)
October 9, 2025 at 11:02 AM
Oracle Database 21c enhanced FOR LOOPs in PL/SQL to enable you to use
Custom increments (BY)
Lists of values to loop through
Conditions to skip the loop body (WHEN)
Expressions to set the next value (REPEAT WHILE)
Mutable iterands to set their value inside the loop (MUTABLE)
Custom increments (BY)
Lists of values to loop through
Conditions to skip the loop body (WHEN)
Expressions to set the next value (REPEAT WHILE)
Mutable iterands to set their value inside the loop (MUTABLE)
You can subtract one year from a date in Oracle #SQL with either
ADD_MONTHS ( dt, -12 )
- INTERVAL '1' YEAR
But take care - these handle 29th Feb differently:
ADD_MONTHS => 29th Feb -> 28th Feb in previous year
INTERVAL => 29th Feb -> 29th Feb in previous year => error!
ADD_MONTHS ( dt, -12 )
- INTERVAL '1' YEAR
But take care - these handle 29th Feb differently:
ADD_MONTHS => 29th Feb -> 28th Feb in previous year
INTERVAL => 29th Feb -> 29th Feb in previous year => error!
October 6, 2025 at 11:03 AM
You can subtract one year from a date in Oracle #SQL with either
ADD_MONTHS ( dt, -12 )
- INTERVAL '1' YEAR
But take care - these handle 29th Feb differently:
ADD_MONTHS => 29th Feb -> 28th Feb in previous year
INTERVAL => 29th Feb -> 29th Feb in previous year => error!
ADD_MONTHS ( dt, -12 )
- INTERVAL '1' YEAR
But take care - these handle 29th Feb differently:
ADD_MONTHS => 29th Feb -> 28th Feb in previous year
INTERVAL => 29th Feb -> 29th Feb in previous year => error!
Index scalar values in #JSON arrays stored in Oracle Database with
CREATE MULTIVALUE INDEX ... ON t ( t.json_data.array. )
The optimizer can use these indexes when searching the array with
JSON_EXISTS ( t.json_data, ' $.array? ( @. == ... ) '
CREATE MULTIVALUE INDEX ... ON t ( t.json_data.array.
The optimizer can use these indexes when searching the array with
JSON_EXISTS ( t.json_data, ' $.array? ( @.
![JSON enhancements
JSON data type: a new data type for binary JSON representation making JSON reads & writes faster
Multi-value index for JSON enables fast searches within JSON arrays
Example:
CREATE TABLE json_data ( data JSON );
CREATE MULTIVALUE INDEX j_array_i ON json_data j ( j.data.arr.number() );
INSERT INTO json_data VALUES ( JSON ( '{ arr : [1,2,3] }' ) );
SELECT * FROM json_data j
WHERE JSON_EXISTS ( j.data, '$.arr?(@.number() == 1)' );
---------------------------------------------------------
| Id | Operation | Name |
---------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| JSON_DATA |
| 2 | INDEX RANGE SCAN (MULTI VALUE) | J_ARRAY_I |
---------------------------------------------------------](https://cdn.bsky.app/img/feed_thumbnail/plain/did:plc:blmnab373idbogizdn742doc/bafkreicgpkd75k3hddqwcg5yayhtxbyaa2moikgcpzhdr4jzynlwq6sv7e@jpeg)
October 2, 2025 at 11:03 AM
Index scalar values in #JSON arrays stored in Oracle Database with
CREATE MULTIVALUE INDEX ... ON t ( t.json_data.array. )
The optimizer can use these indexes when searching the array with
JSON_EXISTS ( t.json_data, ' $.array? ( @. == ... ) '
CREATE MULTIVALUE INDEX ... ON t ( t.json_data.array.
The optimizer can use these indexes when searching the array with
JSON_EXISTS ( t.json_data, ' $.array? ( @.
Get values from the initial/nth/final row with
FIRST_VALUE => inital
NTH_VALUE => Nth
LAST_VALUE => final
The default window ends at the current value =>
NTH_VALUE ( v, N ) is null for rows before N
LAST_VALUE gives the final value with the same sort key, not the very last row
FIRST_VALUE => inital
NTH_VALUE => Nth
LAST_VALUE => final
The default window ends at the current value =>
NTH_VALUE ( v, N ) is null for rows before N
LAST_VALUE gives the final value with the same sort key, not the very last row
September 29, 2025 at 11:01 AM
Get values from the initial/nth/final row with
FIRST_VALUE => inital
NTH_VALUE => Nth
LAST_VALUE => final
The default window ends at the current value =>
NTH_VALUE ( v, N ) is null for rows before N
LAST_VALUE gives the final value with the same sort key, not the very last row
FIRST_VALUE => inital
NTH_VALUE => Nth
LAST_VALUE => final
The default window ends at the current value =>
NTH_VALUE ( v, N ) is null for rows before N
LAST_VALUE gives the final value with the same sort key, not the very last row
Oracle Database has added many #SQL features in RUs like
Non-positional INSERT
GROUP BY ALL
TIME_BUCKET function
But how do you find these?
In the New Features Guides!
Some need you to up COMPATIBLE - @mikedietrichde.com the impact this has
buff.ly/5i5aPnu
Non-positional INSERT
GROUP BY ALL
TIME_BUCKET function
But how do you find these?
In the New Features Guides!
Some need you to up COMPATIBLE - @mikedietrichde.com the impact this has
buff.ly/5i5aPnu
September 26, 2025 at 11:02 AM
Oracle Database has added many #SQL features in RUs like
Non-positional INSERT
GROUP BY ALL
TIME_BUCKET function
But how do you find these?
In the New Features Guides!
Some need you to up COMPATIBLE - @mikedietrichde.com the impact this has
buff.ly/5i5aPnu
Non-positional INSERT
GROUP BY ALL
TIME_BUCKET function
But how do you find these?
In the New Features Guides!
Some need you to up COMPATIBLE - @mikedietrichde.com the impact this has
buff.ly/5i5aPnu
Create blockchain tables in Oracle Database with
CREATE BLOCKCHAIN TABLE ... ( ... )
NO DROP UNTIL n DAYS IDLE
NO DELETE UNTIL n DAYS AFTER INSERT
HASHING USING SHA2_512 VERSION "v1"
These
Allow INSERT
Disallow UPDATE
Disallow DELETE/DROP until you wait N days as defined
CREATE BLOCKCHAIN TABLE ... ( ... )
NO DROP UNTIL n DAYS IDLE
NO DELETE UNTIL n DAYS AFTER INSERT
HASHING USING SHA2_512 VERSION "v1"
These
Allow INSERT
Disallow UPDATE
Disallow DELETE/DROP until you wait N days as defined
September 25, 2025 at 11:02 AM
Create blockchain tables in Oracle Database with
CREATE BLOCKCHAIN TABLE ... ( ... )
NO DROP UNTIL n DAYS IDLE
NO DELETE UNTIL n DAYS AFTER INSERT
HASHING USING SHA2_512 VERSION "v1"
These
Allow INSERT
Disallow UPDATE
Disallow DELETE/DROP until you wait N days as defined
CREATE BLOCKCHAIN TABLE ... ( ... )
NO DROP UNTIL n DAYS IDLE
NO DELETE UNTIL n DAYS AFTER INSERT
HASHING USING SHA2_512 VERSION "v1"
These
Allow INSERT
Disallow UPDATE
Disallow DELETE/DROP until you wait N days as defined
Combine rows by value in #SQL with
GROUP BY col
And this returns one row for each value in col
You can then use aggregate functions to get totals for the rows in each group, e.g.
COUNT - number of rows
AVG - numeric mean
MIN/MAX - smallest/largest
LISTAGG - list of values
GROUP BY col
And this returns one row for each value in col
You can then use aggregate functions to get totals for the rows in each group, e.g.
COUNT - number of rows
AVG - numeric mean
MIN/MAX - smallest/largest
LISTAGG - list of values
September 22, 2025 at 11:02 AM
Combine rows by value in #SQL with
GROUP BY col
And this returns one row for each value in col
You can then use aggregate functions to get totals for the rows in each group, e.g.
COUNT - number of rows
AVG - numeric mean
MIN/MAX - smallest/largest
LISTAGG - list of values
GROUP BY col
And this returns one row for each value in col
You can then use aggregate functions to get totals for the rows in each group, e.g.
COUNT - number of rows
AVG - numeric mean
MIN/MAX - smallest/largest
LISTAGG - list of values
With Schema Annotations in Oracle Database, you can document your objects by adding key-value metadata to
Tables
Columns
Indexes
(Materialized) Views
With the clause
ANNOTATIONS ( <key1> <value1>, <key2> <value2>, ... )
Added in release 23ai, this has been backported to 19.28
Tables
Columns
Indexes
(Materialized) Views
With the clause
ANNOTATIONS ( <key1> <value1>, <key2> <value2>, ... )
Added in release 23ai, this has been backported to 19.28
September 19, 2025 at 11:01 AM
With Schema Annotations in Oracle Database, you can document your objects by adding key-value metadata to
Tables
Columns
Indexes
(Materialized) Views
With the clause
ANNOTATIONS ( <key1> <value1>, <key2> <value2>, ... )
Added in release 23ai, this has been backported to 19.28
Tables
Columns
Indexes
(Materialized) Views
With the clause
ANNOTATIONS ( <key1> <value1>, <key2> <value2>, ... )
Added in release 23ai, this has been backported to 19.28
Take care when filtering the INNER table in OUTER JOINs
e.g. the right table in a LEFT OUTER JOIN
Do this in the WHERE clause => it's an INNER JOIN
To preserve the OUTER JOIN, filter the inner table in the ON clause
The pic shows outer joins ON number col with inner filters
e.g. the right table in a LEFT OUTER JOIN
Do this in the WHERE clause => it's an INNER JOIN
To preserve the OUTER JOIN, filter the inner table in the ON clause
The pic shows outer joins ON number col with inner filters
September 15, 2025 at 11:02 AM
Take care when filtering the INNER table in OUTER JOINs
e.g. the right table in a LEFT OUTER JOIN
Do this in the WHERE clause => it's an INNER JOIN
To preserve the OUTER JOIN, filter the inner table in the ON clause
The pic shows outer joins ON number col with inner filters
e.g. the right table in a LEFT OUTER JOIN
Do this in the WHERE clause => it's an INNER JOIN
To preserve the OUTER JOIN, filter the inner table in the ON clause
The pic shows outer joins ON number col with inner filters
Convert rows to a comma-separated list of de-duplicated values in Oracle #SQL with
LISTAGG ( DISTINCT val, ', ' ) WITHIN GROUP ( ORDER BY ... )
LISTAGG was introduced in 11g Release 2
The DISTINCT clause was added in 19c
LISTAGG ( DISTINCT val, ', ' ) WITHIN GROUP ( ORDER BY ... )
LISTAGG was introduced in 11g Release 2
The DISTINCT clause was added in 19c
September 11, 2025 at 11:01 AM
Convert rows to a comma-separated list of de-duplicated values in Oracle #SQL with
LISTAGG ( DISTINCT val, ', ' ) WITHIN GROUP ( ORDER BY ... )
LISTAGG was introduced in 11g Release 2
The DISTINCT clause was added in 19c
LISTAGG ( DISTINCT val, ', ' ) WITHIN GROUP ( ORDER BY ... )
LISTAGG was introduced in 11g Release 2
The DISTINCT clause was added in 19c
Get the top-N rows in Oracle #SQL with
FETCH FIRST n ROWS ONLY
You can include all rows with the same sort value as the Nth with
FETCH FIRST n ROWS WITH TIES
Or get a fraction of the rows with
FETCH FIRST n PERCENT ROWS [ ONLY | WITH TIES ]
FETCH FIRST n ROWS ONLY
You can include all rows with the same sort value as the Nth with
FETCH FIRST n ROWS WITH TIES
Or get a fraction of the rows with
FETCH FIRST n PERCENT ROWS [ ONLY | WITH TIES ]
September 8, 2025 at 11:03 AM
Get the top-N rows in Oracle #SQL with
FETCH FIRST n ROWS ONLY
You can include all rows with the same sort value as the Nth with
FETCH FIRST n ROWS WITH TIES
Or get a fraction of the rows with
FETCH FIRST n PERCENT ROWS [ ONLY | WITH TIES ]
FETCH FIRST n ROWS ONLY
You can include all rows with the same sort value as the Nth with
FETCH FIRST n ROWS WITH TIES
Or get a fraction of the rows with
FETCH FIRST n PERCENT ROWS [ ONLY | WITH TIES ]
Get values from the previous or next row in #SQL with
( val ) OVER ( ORDER BY ... )
Where is either
LAG - previous
LEAD - next
By default these look one row forward/back
Use the second parameter to get the value from N rows before/after the current
Where
LAG - previous
LEAD - next
By default these look one row forward/back
Use the second parameter to get the value from N rows before/after the current
September 4, 2025 at 11:02 AM
Get values from the previous or next row in #SQL with
( val ) OVER ( ORDER BY ... )
Where is either
LAG - previous
LEAD - next
By default these look one row forward/back
Use the second parameter to get the value from N rows before/after the current
Where
LAG - previous
LEAD - next
By default these look one row forward/back
Use the second parameter to get the value from N rows before/after the current
The row numbering functions in #SQL are
ROW_NUMBER => Consecutive integers starting at one
RANK => Rows with the same sort value have the same number; the next row after ties has the same value as ROW_NUMBER
DENSE_RANK => Like RANK, but numbers are consecutive after ties
ROW_NUMBER => Consecutive integers starting at one
RANK => Rows with the same sort value have the same number; the next row after ties has the same value as ROW_NUMBER
DENSE_RANK => Like RANK, but numbers are consecutive after ties
September 1, 2025 at 11:02 AM
The row numbering functions in #SQL are
ROW_NUMBER => Consecutive integers starting at one
RANK => Rows with the same sort value have the same number; the next row after ties has the same value as ROW_NUMBER
DENSE_RANK => Like RANK, but numbers are consecutive after ties
ROW_NUMBER => Consecutive integers starting at one
RANK => Rows with the same sort value have the same number; the next row after ties has the same value as ROW_NUMBER
DENSE_RANK => Like RANK, but numbers are consecutive after ties
Practice your Oracle #SQL skills with free quizzes on Oracle Dev Gym
SQuizL - complete the statement using the clues provided
Speed SQL - race against the clock to guess the missing keywords
Weekly DB - a new multiple-choice question every week
buff.ly/05p9QME
SQuizL - complete the statement using the clues provided
Speed SQL - race against the clock to guess the missing keywords
Weekly DB - a new multiple-choice question every week
buff.ly/05p9QME
August 29, 2025 at 11:01 AM
Practice your Oracle #SQL skills with free quizzes on Oracle Dev Gym
SQuizL - complete the statement using the clues provided
Speed SQL - race against the clock to guess the missing keywords
Weekly DB - a new multiple-choice question every week
buff.ly/05p9QME
SQuizL - complete the statement using the clues provided
Speed SQL - race against the clock to guess the missing keywords
Weekly DB - a new multiple-choice question every week
buff.ly/05p9QME
Limit what can call a PL/SQL unit with accessor lists
Only units with a name in the list can call the unit
e.g.:
PROCEDURE prc ACCESSIBLE BY ( another_proc, some_fn )
Can only be run via units named ANOTHER_PROC or SOME_FN
This can give fine-grained access to package units
Only units with a name in the list can call the unit
e.g.:
PROCEDURE prc ACCESSIBLE BY ( another_proc, some_fn )
Can only be run via units named ANOTHER_PROC or SOME_FN
This can give fine-grained access to package units
August 28, 2025 at 11:02 AM
Limit what can call a PL/SQL unit with accessor lists
Only units with a name in the list can call the unit
e.g.:
PROCEDURE prc ACCESSIBLE BY ( another_proc, some_fn )
Can only be run via units named ANOTHER_PROC or SOME_FN
This can give fine-grained access to package units
Only units with a name in the list can call the unit
e.g.:
PROCEDURE prc ACCESSIBLE BY ( another_proc, some_fn )
Can only be run via units named ANOTHER_PROC or SOME_FN
This can give fine-grained access to package units
Use the right data type when defining #database columns, e.g.
Numbers => numeric type
Datetimes => date/timestamp type
Using the wrong data type leads to implicit type conversions which can:
Prevent index use => slow SQL
Be invalid => runtime errors
Choose wisely!
Numbers => numeric type
Datetimes => date/timestamp type
Using the wrong data type leads to implicit type conversions which can:
Prevent index use => slow SQL
Be invalid => runtime errors
Choose wisely!
August 25, 2025 at 11:01 AM
Use the right data type when defining #database columns, e.g.
Numbers => numeric type
Datetimes => date/timestamp type
Using the wrong data type leads to implicit type conversions which can:
Prevent index use => slow SQL
Be invalid => runtime errors
Choose wisely!
Numbers => numeric type
Datetimes => date/timestamp type
Using the wrong data type leads to implicit type conversions which can:
Prevent index use => slow SQL
Be invalid => runtime errors
Choose wisely!
You can combine the PARTITION BY and ORDER BY clauses in #SQL window functions
This splits the rows into groups for each partitioning value
Then gets the running total within each group. By default, this is rows with a sort value less than or equal to the group's current row
This splits the rows into groups for each partitioning value
Then gets the running total within each group. By default, this is rows with a sort value less than or equal to the group's current row
August 22, 2025 at 11:02 AM
You can combine the PARTITION BY and ORDER BY clauses in #SQL window functions
This splits the rows into groups for each partitioning value
Then gets the running total within each group. By default, this is rows with a sort value less than or equal to the group's current row
This splits the rows into groups for each partitioning value
Then gets the running total within each group. By default, this is rows with a sort value less than or equal to the group's current row
The ORDER BY clause of #SQL window functions sorts the rows
The function then returns the running total with the window
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
This includes all rows with a sort value less than or equal to the current row's value
The function then returns the running total with the window
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
This includes all rows with a sort value less than or equal to the current row's value
August 21, 2025 at 11:01 AM
The ORDER BY clause of #SQL window functions sorts the rows
The function then returns the running total with the window
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
This includes all rows with a sort value less than or equal to the current row's value
The function then returns the running total with the window
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
This includes all rows with a sort value less than or equal to the current row's value