CODEWITHSUNDEEP
c#sqlsql-servert-sql
artvolk
artvolk

Reputation: 9518

How to quote and reference SQL Server table and field names

Good day!

Despite the fact LINQ2SQL and ADO.NET Entity Framework exists there were situations when I need to revert to plain old DataSet (not typed) and friends.

While writing SQL for SqlCommand:

I use to use this syntax:

SqlCommand command = new SqlCommand("SELECT [Field1], [Field2] FROM [dbo].[TableName]", connection);

May be there is a better way?

Thanks in advance!

Upvotes: 0

Views: 2572

Answers (3)

gbn
gbn

Reputation: 432210

Is it needed to quote field and table names with []?

No, unless you have a table called [foo bar] (with space), or your column/table (and database name even) starts with a number, or has non-alphanumeric characters.

See MSDN

Is it good to prefix table names with [dbo]

Absolutely yes. It allows plan re-use, simply, because object references are now qualified

Upvotes: 2

Pranay Rana
Pranay Rana

Reputation: 176896

There is no need to use [] when you are querying your data form the database.

you use [] when there is space of alias name like 

SELECT Field1 as [First Name], Field2 as [Last Name] FROM [dbo].[TableName]

because it gives syntax error

dbo is the schema name that with which your table and the user attached

here is more detail answer A database schema is a way to logically group objects such as tables, views, stored procedures etc. Think of a schema as a container of objects.

You can assign a user login permissions to a single schema so that the user can only access the objects they are authorized to access.

Schemas can be created and altered in a database, and users can be granted access to a schema. A schema can be owned by any user, and schema ownership is transferable.

You can also read full article on : http://www.quackit.com/sql_server/sql_server_2008/tutorial/sql_server_database_schemas.cfm

Upvotes: 1

Will Marcouiller
Will Marcouiller

Reputation: 24132

You only need to quote fields if they represent reserved keywords or contains illegal characters such as in "Field #1". Both the " " (space) and "#" characters are normally illegal, so you would have to use the square brackets quotes.

As for the schema name, I would recommend not to write it, as if someday the schema name of the table you want to access is not dbo, your code might break.

Upvotes: 0

Related Questions