Question about delimiter characters for SQL Server 2008 queries and truncate syntax.

I'm new to SQL, so I apologize if this is a dumb question.

When do I need to use the brackets '[' and ']' around schema and table names? Could I just use them always?

Now, suppose I did the following to create a table

CREATE TABLE [dbo].[table1] ([ID] VARCHAR(5))

This worked. I verified it by running a select statement. Suppose time goes on and I added a bunch of rows. Now if I want remove all the rows, should I be using delete or truncate? It seems I should be doing this

TRUNCATE [dbo].[table1]

But I keep getting incorrect开发者_开发百科 syntax near 'dbo' or incorrect syntax near keyword TRUNCATE when I do this via jtds jdbc driver.

What am I missing with the syntax?

TRUNCATE [dbo].[table1]

should be

TRUNCATE TABLE [dbo].[table1]

brackets are used to allow for invalid column names like then ones that start with a number or have spaces in them

example

CREATE TABLE [ ]([ ] INT)
INSERT [ ] VALUES(1)

SELECT [ ] FROM [ ]

you can always use brackets, they are not required but guard against problematic column names

I think you wanted TRUNCATE TABLE ... there is no TRUNCATE on its own.

TRUNCATE TABLE [dbo].[table1];

The [] are required around table and column names that are reserved words, or include spaces. MS tends to put them around everything, but most of the time they are not necessary.

And as Aaron said, you want TRUNCATE TABLE.

In answer to the fist part of your question:

When do I need to use the brackets '[' and ']' around schema and table names? Could I just use them always?

Use brackets when the names clash with SQL keywords (usually you can see this by the colours in your IDE) and Yes - you can always use them.

Truncate Table and Delete From are not the same though they often appear to do the same thing.

Truncate Table requires some heightened permissions and Truncate Table "Removes all rows from a table without logging the individual row deletions. TRUNCATE TABLE is similar to the DELETE statement with no WHERE clause; however, TRUNCATE TABLE is faster and uses fewer system and transaction log resources."

This means that Truncate always removes all of the rows in a table and cannot be rolled back as part of a transaction.

Delete From is almost always what you want to do as part of transactional processing. Truncate Table is appropriate while developing and testing as a standalone, adhoc command. If you feel an urge to put Truncate Table in a stored procedure, check yourself and see if Delete might be more appropriate.