Tag: tables

What is a Heap in SQL Server?

Posted on by Ian

In SQL Server, a heap is a table without a clustered index. Unlike tables with clustered indexes, which sort data in a specific order, heaps store data in no particular order. That’s because the clustered index is what determines how the table is stored and sorted (it’s sorted on the clustered index’s key column).

If there’s no clustered index, then data is initially stored in the order in which the rows are inserted, although the database engine may change this in order to store the rows more efficiently.

Continue reading →

Using STRING_TO_TABLE() in PostgreSQL

Posted on by Ian

In PostgreSQL, we can use the string_to_table() function to return a set of rows, each containing a part of the string. The string is split based on the specified delimiter.

If we specify a null delimiter, then each character becomes a separate row in the output. If the delimiter string is empty, then the whole string is returned in a single row.

We also have the option of turning a specific substring into null if required.

Continue reading →

Check if Table Exists in SQL

Posted on by Ian

With SQL we can use various methods to check whether or not a table (or other object) exists in the database. The method we use will often depend on the RDBMS we’re using, as well as the task we’re trying to undertake.

There’s usually a reason we’re trying to check for the existence of a table, and often the syntax we use will be tied to that reason. For example the ...IF EXISTS clause is a handy addition to the DROP TABLE statement, and the ...IF NOT EXISTS clause can often be used with the CREATE TABLE statement.

Other times we may simply want to see if the table exists without performing any immediate actions against that table. In such cases, we would need to run code specifically to see if the table exists.

Below are examples of code we can use in each of the above scenarios.

Continue reading →

DROP TABLE IF EXISTS in SQL

Posted on by Ian

In SQL, we can use the DROP TABLE IF EXISTS statement to drop a table only if it exists.

While it may seem obvious that we can only drop a table if it exists (i.e. we can’t drop a table that doesn’t exist), there’s a good reason for using this statement.

The reason we put an IF EXISTS clause into a DROP TABLE statement is to prevent any errors that would occur if the table doesn’t exist.

Continue reading →

PostgreSQL SHOW TABLES Equivalent (psql)

Posted on by Ian

MySQL and MariaDB have a SHOW TABLES statement, which outputs a list of tables and views in a database. PostgreSQL doesn’t have a SHOW TABLES statement, but it does have a command that produces a similar result.

In Postgres, you can use the \dt command to show a list of tables. This is a psql command (psql is the interactive terminal for PostgreSQL).

Continue reading →