SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Strings vs Identifiers

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.

SQLStudies

My name is Kenneth Fisher and I am Senior DBA for a large (multi-national) insurance company. I have been working with databases for over 20 years starting with Clarion and Foxpro. I’ve been working with SQL Server for 12 years but have only really started “studying” the subject for the last 3. I don’t have any real "specialities" but I enjoy trouble shooting and teaching. Thus far I’ve earned by MCITP Database Administrator 2008, MCTS Database Administrator 2005, and MCTS Database Developer 2008. I’m currently studying for my MCITP Database Developer 2008 and should start in on the 2012 exams next year. My blog is at www.sqlstudies.com.

Comments

Leave a comment on the original post [sqlstudies.com, opens in a new window]

Loading comments...