When creating items in SQL Server we aren’t able to create multiple with the same name, for example two tables named dbo.Sales. Similarly we couldn’t create a table and view with the same name.
However it isn’t the case that everything we create within a database has to be named uniquely. It can depend on the type of item we’re creating and how naming for that type of item is scoped, i.e. within what collection of items must it’s name be unique.
It can be beneficial to understand naming scope when looking to create or recreate similar objects where a conflict may occur, for example creating a replica of a table.
Schemas
A schema is a security feature to help organise databases by creating logical collections of objects which we can then apply specific permissions against. They are scoped at a database level and so each schema name must be unique within the same database.
Schemas are also the foundation which we build our objects upon (see below) so a new database will contain the default schema – dbo – and we can add further if we require.
Objects
When we use the term object in SQL Server it isn’t referring to ‘one thing’ (like we may do in .net for example), it’s a specific type of thing which resides in our databases. There is quite a list of these but crucially this includes tables, views, stored procedures, and primary and foreign keys.
These objects will belong to a schema (by default, dbo) and must be unique within a specific database and schema. We can therefore have a view and table named the same if they are in different schemas.
Below is an example script to create objects, some of which will succeed and others fail:
/* Will create a new table */
CREATE TABLE dbo.TestObj (
ID INT IDENTITY PRIMARY KEY,
Number INT,
SomeText VARCHAR(100)
);
GO
/* Will fail due to table existing */
CREATE VIEW dbo.TestObj
AS
SELECT *
FROM sys.objects
GO
/* Will fail due to table existing */
CREATE PROCEDURE dbo.TestObj AS
BEGIN
SELECT *
FROM sys.objects;
END
GO
/* Succeed due to different schema */
CREATE TABLE NewSchema.TestObj (
ID INT IDENTITY PRIMARY KEY,
Number INT,
SomeText VARCHAR(100)
);
GO
When the object already exists we will receive the error below, however this message doesn’t indicate the schema of the object or the type of the existing entry:
There is already an object named ‘TestObj’ in the database.
Details of the objects in a database can be found from the catalog view sys.objects and you can find more details about this and additional types of objects in the online documentation.
Indexes
Unlike the objects that we’ve already looked at indexes have a much more granular scope. The name of an index only has to be unique to the object it’s built on. We could therefore have similar indexes covering the same columns on a different table and using the same name.
Below is an example to create a number of indexes, some of which will succeed and one will fail:
/* Create initial index */
CREATE INDEX Number
ON dbo.TestObj (Number);
/* Fails due to existing index */
CREATE INDEX Number
ON dbo.TestObj (ID);
/* Succeed due to different object */
CREATE INDEX Number
ON NewSchema.TestObj (ID);
When the index fails to be created we’d see a similar error message to the one below:
The operation failed because an index or statistics with name ‘Number’ already exists on table ‘dbo.TestObj’.
It’s worth noting that if an index was created as part of a primary key constraint, the index name itself can be reused against other tables, however we couldn’t create a primary key on another table with the same name due to primary keys being considered an object and therefore must be unique in a schema.
More details regarding indexes can be found in the catalog view sys.indexes and the accompanying online documentation.
Triggers
I thought it was worth putting triggers into their own section here as they aren’t as clear cut as the ones above. Typically we either use a DDL or a DML trigger and both of these are scoped differently.
A DML trigger which would be used to catch INSERT, UPDATE or DELETE commands is specific to the object it is created against and so is schema-bound and will be present in the sys.objects view along with other objects.
However DDL triggers operate at the database level and are not assigned to a specific schema. Due this this they aren’t present in the sys.objects catalog view. Instead the can be found in the view sys.triggers. This view also contains DML triggers so is a composite for all types of triggers within the database.
Wrap up
Here we’ve looked at the naming scope for objects within SQL Server. As a general rule indexes are unique to an object, an object is unique to a schema, and a schema is unique to a database. The list of types of objects is quite lengthy but generally could be thought of as anything which isn’t an index, at least in terms of day-to-day usage for most folks.
I’ve seen issues with the scope catch folks out when trying to recreate objects when making large schema changes to a table for example. I know I’ve fallen fowl of these before so hopefully this brief post can help others avoid that trap too.
Andy Brownsword 