Drop...create vs ALTER

sql

prompt-engineering

best-practices

performance

byNikita Barsukov·Dec 4, 2024

Need to update a SQL object? Choose **ALTER**. This maintains data and integrity by in-place(renovation-style) modification. If it's a complete rebuild pursuit, go with **DROP...CREATE**, but be aware of potential data loss, and dependency issues.

Example - Modifying a table column:

/*
Are we operating? No, it's merely a small incision
*/
ALTER TABLE MyTable ALTER COLUMN MyColumn NewDataType;

ALTER's usefulness lies in being specific and preservative, while DROP...CREATE is a sweep-style change, which is risk-prone if object relationships are established.

Object existence: Pre-check

One of the first rules of thumb is to pre-check the existence of objects before you make your move. Here is a basic pattern to follow:

/*
Let's knock first to see if anyone is in before we rebuild the house!
*/
IF OBJECT_ID('MyObject', 'P') IS NOT NULL
  ALTER OBJECT...
ELSE
  CREATE OBJECT...

You might find CREATE OR ALTER handy as it simplifies the script by automatically handling object existence. Please note that it's available from SQL Server 2016 SP1 onwards.

Safety First: Preserve Permissions

The DROP...CREATE approach could be like an unexpected memory loss, where you forget to preserve all set permissions on an object. You will want to script out permissions to reapply them in the aftermath of object recreation.

Maintaining the Metadata

Choose ALTER to keep extended properties and dependencies in a safe haven while making modifications. This is crucial when you're operating in an interlinked system where objects influence each other.

Performance aspect

DROP...CREATE could be a heavyweight contender, heavily impacting your system resources, especially if used on large objects. In contrast, surgical precision is a quality of ALTER, making system changes less intensive as it targets only the required components.

The convenience of CREATE OR ALTER

From SQL Server 2016 SP1 onwards, CREATE OR ALTER became an addition to our SQL vocabulary, providing the convenience of either modifying an existing object or creating a new one if it doesn't exist, all without writing conditional logic.

Advanced use cases: Wildlife of ALTER

In knotty scenarios, such as significant refactorings, you might need to navigate through the wildlife of ALTER - altering multiple aspects of an object within a single transaction to ensure what goes together, stays together (atomic updates).

The great debate: DROP...CREATE vs ALTER

There's no one ring to rule them all when it comes to using DROP...CREATE vs ALTER. The community houses varying opinions, offering valuable insights into the advantages and pitfalls of each approach. Dive deep, and gather wisdom from the global discussion.

Adopting CREATE OR ALTER in real life

The CREATE OR ALTER approach can do wonders in simplifying code management and maintenance, making your SQL life a breeze. Here's a quick example:

/*
CREATE OR ALTER - It's like a wizard,
either creates a new potion or tweaks an existing one!
*/
CREATE OR ALTER PROCEDURE MyProcedure AS
BEGIN
  -- Your magical SQL logic here
END