A very interesting feature of the SQL Server and PostgreSQL databases (and some others, including SQLite) is the partial index (sometimes also called "filtered index"). That's an index that contains only "parts" of the table data. For instance, we can write the following index in SQL Server and PostgreSQL: CREATE INDEX i ON message WHERE … Continue reading How to Emulate Partial Indexes in Oracle
Tag: SQL Server
jOOQ 3.10 Supports SQL Server’s Table Valued Parameters
SQL Server has this nice feature called table-valued parameters (TVP), where users can pass table variables to a stored procedure for bulk data processing. This is particularly nice when the stored procedure is an inline table valued function, i.e. a function that returns a table as well. For instance: CREATE TYPE numbers AS TABLE (i … Continue reading jOOQ 3.10 Supports SQL Server’s Table Valued Parameters
Impress Your Coworkers by Using SQL UNPIVOT!
I've recently encountered a very interesting question on Stack Overflow by an unnamed user. The question was about generating a table of the following form in Oracle, using a table valued function: Description COUNT ------------------- TEST1 10 TEST2 15 TEST3 25 TEST4 50 The logic that should be implemented for the COUNT column is the … Continue reading Impress Your Coworkers by Using SQL UNPIVOT!
The 10 Most Popular DB Engines (SQL and NoSQL) in 2015
About two years ago, we've published this post about the 10 most popular DB engines, where we analyzed the data published by Solid IT on their DB Ranking website. In the meantime, the Solid IT measurement system has found to be a credible source, such that the website has also been cited at Gartner, InfoWorld, … Continue reading The 10 Most Popular DB Engines (SQL and NoSQL) in 2015
10 SQL Articles Everyone Must Read
We've been blogging about Java and SQL for a while now, on the jOOQ blog. Over the years, while researching interesting blog topics, we've discovered a lot of SQL gems in the blogosphere that have inspired our work and our passion for SQL. Today, we're presenting to you a list of 10 articles that we … Continue reading 10 SQL Articles Everyone Must Read
Do You Really Understand SQL’s GROUP BY and HAVING clauses?
There are some things in SQL that we simply take for granted without thinking about them properly. One of these things are the GROUP BY and the less popular HAVING clauses. Let's look at a simple example. For this example, we'll reiterate the example database we've seen in this previous article about the awesome LEAD(), … Continue reading Do You Really Understand SQL’s GROUP BY and HAVING clauses?
Awesome SQL Trick: Constraints on Views
CHECK constraints are already pretty great when you want to sanitize your data. But there are some limitations to CHECK constraints, including the fact that they are applied to the table itself, when sometimes, you want to specify constraints that only apply in certain situations. This can be done with the SQL standard WITH CHECK … Continue reading Awesome SQL Trick: Constraints on Views
Are You Using SQL PIVOT Yet? You Should!
Every once in a while, we run into these rare SQL issues where we'd like to do something that seems out of the ordinary. One of these things is pivoting rows to columns. A recent question on Stack Overflow by Valiante asked for precisely this. Going from this table: +------+------------+----------------+-------------------+ | dnId | propNameId | … Continue reading Are You Using SQL PIVOT Yet? You Should!
SQL Server ALTER TABLE SET DEFAULT
Most databases that support default values on their column DDL, it is also possible to actually alter that default. An Oracle example: CREATE TABLE t ( val NUMBER(7) DEFAULT 1 NOT NULL ); -- Oops, wrong default, let us change it ALTER TABLE t MODIFY val DEFAULT -1; -- Now that is better Unfortunately, this … Continue reading SQL Server ALTER TABLE SET DEFAULT
SQL Server Trick: Circumvent Missing ORDER BY Clause
SQL Server is known to have a very strict interpretation of the SQL standard. For instance, the following expressions or statements are not possible in SQL Server: -- Get arbitrarily numbered row_numbers SELECT ROW_NUMBER() OVER () -- Skip arbitrary rows SELECT a FROM (VALUES (1), (2), (3), (4)) t(a) OFFSET 3 ROWS Strictly speaking, that … Continue reading SQL Server Trick: Circumvent Missing ORDER BY Clause