Strings vs Identifiers

3

June 20, 2018 by Kenneth Fisher

A common mistake, and one I make frequently myself is to use a string in place of an identifier, or vise-versa. So to start, let’s have some definitions, shall we?

String

a linear sequence of characters, words, or other data.

Identifier

a sequence of characters used to identify or refer to a program or an element, such as a variable or a set of data, within it.

And because I always find examples fairly useful.

CREATE LOGIN [this is an identifier] 
	WITH PASSWORD = 'this is a string';  
GO

You’ll notice that both identifiers and strings are a sequence of characters. The big difference being that one is a place to contain data (string) and the other is the name of an object (identifier). In fact identifiers are strings that identify the names of objects. Which is, of course, one of the sources of confusion. It helps though that within SQL Server (actually, most if not all programming languages) strings are a bit more strongly defined.

A string within SQL Server is still a sequence of characters but it must be delimited with single (or depending on the settings double) quotes. An identifier, on the other hand, may or may not have any delimiters around it depending on what the name is. Identifiers with spaces, special characters or emojis require delimiters, otherwise it’s optional. If it does you will use square brackets ([]) or double quotes (again depending on that setting).

Now, if you look at the CREATE LOGIN statement again. You’ll notice that the object (well technically principal) name is an identifier while the password is a string. So when you need to reference or name a new table, column, schema, database, stored procedure, function, credential, login, user, role, etc, etc you are using an identifier. Cases where you are using a path, a password, putting data into a character column or variable then you are using a string.

You wouldn’t believe how often I end up using a string for that login name. Hopefully having laid it out now I’ll cut back on that particular mistake some.

3 thoughts on “Strings vs Identifiers

  1. Hi Ken. If you, or anyone else, is interested in the specific rules by which Identifiers — both regular (i.e. non-delimited) and delimited — work (i.e. min / max lengths, valid / invalid characters / etc), I posted the complete list here: https://SqlQuantumLeap.com/reference/completely-complete-list-of-rules-for-t-sql-identifiers

    There are links in that page to posts containing the full / actual list of valid characters for regular identifiers, as well as to the research to find all of those rules.

  2. […] Kenneth Fisher explains the difference between a string and an identifier: […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 2,432 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: