tables

How to Safely Drop and Recreate a Table If It Exists in SQL Server

by

Sometimes you need to rebuild a table from scratch. This will usually involve dropping the table and recreating it. Maybe it’s for a schema update, or maybe it’s for reloading data in a staging environment. Whatever the reason, SQL Server provides a straightforward way to handle this.

While we can certainly go right ahead and drop the table, what if it doesn’t actually exist? In this case we’ll get an error. That is unless we take measures to prevent such an error. Such measures will involve checking for the existence of the table before attempting to drop it.

Read more

Using DROP TABLE IF EXISTS When Working with Temporary Tables in SQL Server

by

In SQL Server, when working with temporary tables, it’s common to ensure a temp table with the same name doesn’t already exist before creating it. This prevents any unwanted errors. SQL Server doesn’t support CREATE TABLE IF NOT EXISTS like some other DBMSs do, so you must explicitly check and drop the table beforehand.

Read more

5 Ways of Checking the Existence of Temporary Tables in SQL Server

by

When working with temporary tables in SQL Server, one of the most common tasks is checking whether the table already exists before creating it. This prevents errors in the event the table already exists, and ensures your scripts run smoothly regardless of previous executions.

In this article, we’ll explore five different approaches to checking for temporary table existence in SQL Server.

Read more

Understanding WITHOUT ROWID Tables in SQLite

by

One feature that sets SQLite apart from most other RDBMSs is the concept of WITHOUT ROWID tables. This is an optimization feature designed to improve performance and reduce storage space for certain use cases.

This article explores what WITHOUT ROWID tables are, how they work, their benefits, and when to use them.

Read more

Find Out if a Table is WITHOUT ROWID in SQLite

by

One of SQLite’s unique features is the WITHOUT ROWID table, which can be used to optimize performance and storage in specific scenarios.

While it’s easy enough to create a WITHOUT ROWID table (just add WITHOUT ROWID to the definition), how to identify a WITHOUT ROWID table might not be so obvious.

In this article, we’ll start by briefly revising what WITHOUT ROWID tables are and how they differ from ordinary tables. Then we’ll look at how to identify these tables by using SQLite’s PRAGMA commands.

Read more

An Introduction to Strict Tables in SQLite

by

SQLite is widely known for its simplicity, flexibility, and lightweight architecture. One feature that sets it apart from most other SQL databases is its dynamic typing system, which allows columns in a table to store data of any type, regardless of their declared type.

While some developers welcome this departure from the traditional SQL approach, others find it extremely problematic, due to its non-enforcement of data types, which could potentially lead to data integrity issues.

Read more

What is a Heap in SQL Server?

by

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.

Read more

Using STRING_TO_TABLE() in PostgreSQL

by

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.

Read more

Check if Table Exists in SQL

by

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.

Read more