The SQL Booklet

Search SQL Server

Enter two or more characters.

Español

CREATE, ALTER and DROP: Manage the lifecycle of an object

Queries work with objects: named structures defined in the database, such as tables, views, procedures, functions, and indexes. CREATE, ALTER, and DROP represent three moments in their life cycle.

These statements change the schema, the structural definition of the database. Treat them as controlled deployments—changes prepared and applied repeatably—not as casual tests in a shared environment.

The idea

CREATE: add an object

CREATE defines an object that does not yet exist. The definition changes depending on whether the object is a table, view, procedure, function, or another supported type.

CREATE TipoDeObjeto dbo.NombreObjeto
-- Definición;

ALTER: change the definition

ALTER changes an existing object without turning the operation into an automatic drop and recreation.

ALTER TipoDeObjeto dbo.NombreObjeto
-- Nueva definición;

DROP: remove the object

DROP removes the object and its definition. Data held by the object can also be lost, as happens when a table is dropped.

DROP TipoDeObjeto dbo.NombreObjeto;

The exact syntax depends on the type of object.

Example: create a work table

CREATE TABLE dbo.ProductoEtiquetaDemo
(
    IdProducto INT NOT NULL,
    Etiqueta NVARCHAR(50) NOT NULL,
    CONSTRAINT PK_ProductoEtiquetaDemo
        PRIMARY KEY (IdProducto, Etiqueta)
);

The statement fails if an object with that name already exists. For a repeatable script we can check this explicitly, but first we must decide whether finding the object means keeping it, modifying it, or stopping deployment.

Modify the definition

ALTER TABLE dbo.ProductoEtiquetaDemo
ADD FechaRegistro DATETIME2(0) NULL;

ALTER preserves the object, but the change may require validating existing data, dependencies—other objects or applications that use it—space, locks, and application compatibility.

Delete only with explicit intent

DROP TABLE IF EXISTS dbo.ProductoEtiquetaDemo;

IF EXISTS prevents an error when the object is absent; it does not make deletion safe. Before executing, we review dependencies, data that must be preserved, and the possibility of restoration.

CREATE OR ALTER

SQL Server supports CREATE OR ALTER for several programmable objects, such as procedures, views, and functions:

CREATE OR ALTER VIEW dbo.vw_ProductosActivos
AS
    SELECT IdProducto, Nombre, Precio
    FROM dbo.Producto
    WHERE Activo = 1;

This form provides scripts that can be used for both a new installation and an update, without deleting and recreating the object.

What should be checked

A schema change can block queries, invalidate application assumptions, or fail because of dependencies. Test it against a representative copy and accompany it with a deployment and rollback plan, the intended way to return to the preceding state.

We also confirm permissions, the authorizations granted to an account or role. Creating or modifying objects requires different permissions from reading or updating data.