Square Brackets in SQL Server 2008

In this article I describe square brackets in SQL 2008.
  • 1366

Introduction

In this article I describe square brackets in SQL 2008. Square brackets are used if there are special characters or keywords in the table column name or if there is space between column name. These can be used around databases, tables or views. For example, your table name would like Employee Details then it will give error message like "Incorrect syntax near 'Details'". Square brackets add consistency. Some square brackets related problems are discussed below with the help of examples.

Example

1.  Space between Table Name

Lets take a practical example. Lets create a table and its name is like Employee Details i.e. space between Employee and Details. Type following code:

create table Employee Details
(
      id int,
      name varchar(15),
      city varchar (15)
)

Now execute whole code.

Output:

Space_between_TableName.jpg

Now put the Employee Details under Square Brackets. And again execute whole code.

Output:

TableName-with-squareBrackets.jpg

Table is created successfully.

2. Column Name as SQL keyword

Another problem is, if the column name is same as predefined SQL keyword, then we can put column name under square brackets. For example, We create a table named Student Details and take "from" (SQL keyword) as a column name and execute below code.

create table [Student Details]
(
      Roll Number int,
      Name varchar(10),
      from varchar (10)--here from is SQL keyword
)

An error will occur, like "Incorrect syntax near the keyword 'from'".

Output:

ColumnName-as-SQLkeyword.jpg

Now put the "from" column name in square brackets. And again execute whole code:

Output:

SQLkeyword-asColumnName-with-squareBrackets-.jpg

3. Column Name having special character

Sometimes column name include special character. For example:

create table [StudentDetail]
(
      Roll_Num[ber int,
      Name varchar(10),
      [from] varchar (10)--here from is SQL keyword
)

While executing above code error message is displayed "Incorrect syntax near 'varchar'".

Output:

ColumnName-with-SpecialCharacter.jpg

Now put the  Roll_Num[ber in square brackets. And again execute whole code.

create table [StudentDetail]
(
      [Roll_Num[ber] int,
      Name varchar(10),
      [from] varchar (10)--here from is SQL keyword
)

Output:

ColumnName-with-SpecialCharacter-and-squareBracket.jpg


© 2013 dotNetheaven. All rights reserved.