The Wiert Corner – irregular stream of stuff

Jeroen W. Pluimers on .NET, C#, Delphi, databases, and personal interests

  • My badges

  • Twitter Updates

  • My Flickr Stream

  • Pages

  • All categories

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

    Join 2,419 other followers

SQL Server, [] brackets, keywords and special characters

Posted by jpluimers on 2021/07/06

A few links for my archive:

  • [WayBack] sql server – What is the use of the square brackets [] in sql statements? – Stack Overflow answer by Michael Haren:

    The brackets are required if you use keywords or special chars in the column names or identifiers. You could name a column [First Name] (with a space)–but then you’d need to use brackets every time you referred to that column.

    The newer tools add them everywhere just in case or for consistency.

     

  • [WayBack] tsql – What characters are valid in an SQL Server database name? – Stack Overflow answer by Scott Munro:

    Delimited names – surrounded by square brackets or double quotes (if QUOTED_IDENTIFIER is set to ON) – can contain basically anything other than the delimiters themselves. It is even possible to use the delimiters within the name with some escape logic. Note though that it is only the closing escape character that must be escaped. In the first example below, the single instance of the opening escape character in the name does not need to be escaped whereas the closing escape character does have to be escaped (by replacing the single instance with two). I guess the logic here is that whatever code that is parsing these statements is looking for a closing escape character and has is not interested in nested opening escape characters.

    • [Test[Test] -> Test[Test
    • [Test]]Test] -> Test]Test

    The following is a description of the rules surrounding non delimited (nonquoted) identifier names in SQL Server 2012. It is an extract from the document Guide to Migrating from MySQL to SQL Server 2012.

    Schema Object Names

    In SQL Server 2012, an object name can be up to 128 characters long.

    Nonquoted identifier names must follow these rules:

    • The first character must be alphanumeric, an underscore (_), an at sign (@), or a number sign (#).
    • Subsequent characters can include alphanumeric characters, an underscore, an at (@) sign, a number sign, or a dollar sign.
    • The identifier must not be a Transact-SQL reserved word. Guide to Migrating from MySQL to SQL Server 2012 8
    • Embedded spaces or special characters are not allowed.

    Identifiers that start with @ or a number sign have special meanings. Identifiers starting with @ are local variable names. Those that start with a number sign are temporary table names.

    To quote an identifier name in Transact-SQL, you must use square brackets ([]).

  • [WayBack] Database Identifiers – SQL Server | Microsoft Docs:
    1. Classes of Identifiers
    2. Rules for Regular Identifiers
    3. See Also

–jeroen

 

 

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 )

Connecting to %s

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

 
%d bloggers like this: