SQL Create table script from DBMS contains [ ]

Question

If some one does right click on a given Table in a Database using SQL Server management Studio from Microsoft and create script table to query window, it displays the create table code in the window. I notice something like the following

CREATE TABLE [dbo].[Login](
[UserId] [int] NOT NULL,
[LoginName] nvarchar COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
etc
)

Is this how it appears in other DBMS too ?
Is this something specific to DBMS used ?
Is it just a fancy view only ?

Why are "[ ]" used around column names and table names. Simple TSQL definition would look something like
CREATE TABLE table_name (
Column#1 Datatype NOT NULL,
Column#2 Datatype NOT NULL UNIQUE,
-- etc...
)

Please dont mind my silly questions.

Thanks in advance,
Balaji S

Answer 1

Those are identifier quotes. They mark the contents as a database identifier (column name, table name, etc) and allow spaces, special characters, and reserved words to be used as identifiers. Usually none of those appear in identifiers so the ID quotes are, strictly speaking, unnecessary. When the text cannot be parsed because of special characters or reserved words, the ID quotes are required.

It's easier for automated tools to simply always use the ID quotes than figure out when you could get away without them.

Different database products use different characters for ID quotes. You often see the back-tick (`) used for this.

Answer 2

Just to add to Larry Lustig's excellent answer, SQL Server's 'create script' function spits out SQL code whose first priority is to be parser-friendly and that's why it always uses its preferred delimited identifier characters being square brackets, rather than say the double quotes used by the SQL-92 standard (SQL Server code can support the Standard delimited identifier by the use of SET QUOTED_IDENTIFIER ON but I don't think this can be specified as an option for output). Being easy on the human eye (use of whitespace, line breaks, etc) is merely a secondary consideration.