[thelist] MSsql question

[Paul Cowan]

Nan wrote:
> CREATE TABLE [jfy_user].[assistance] (
> 	[assistance_id] [int] IDENTITY (1, 1) NOT NULL ,
> 	[assistance] [varchar] (15) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
>
> Is this just creating a table in the db jfy.user called
> assistance with the fields assistance_id and assistance?

Well, possibly (if you mean jfy_user, not jfy.user). It's most
likely doing that, though it may also be creating a table
called assistance in the current database, under the user jfy_user.

(CREATE TABLE [database].[table] is shorthand for
CREATE TABLE [database].[user].[table], where 'user' will either
be the current user, or the default 'database owner' role (dbo).)


> What is COLLATE doing to this query? And why are there
> brackets around everything?

Collation determines the character set and the sort order for the
column. For example, in Danish, a word starting with the character
'ø' (lowercase-o-with-a-slash-through-it, if it doesn't show up
on your machine) is sorted at the end of the alphabet; in some
other language (Norwegian or something), it might come in the
middle with the other o-based characters.

The brackets are... well, they're for safety's sake. If you
have a column name which contains spaces ([First Name]), or is
a SQL reserved word ([ORDER]), the brackets will make it be
interpreted as a column name rather than as an invalid input.

It's probably not a totally bad idea to always surround identifiers
with square brackets anyway -- makes it clear what they are.

Cheers,

Paul