CREATE TABLE with multiple-column primary key?

Is it possible to issue the CREATE TABLE command and specify a multiple-colu
mn primary key?
If so, what is the syntax? I've checked BOL and as far as I can tell, you ma
y only select
a single column as the primary key *within the CREATE TABLE command*; ALTER
TABLE must be used
for multiple-column primary keys.
For example (this does *not* work):
CREATE TABLE #TEMPProcedures (ProcedureID int NOT NULL, ProcedureSuffix int
NOT NULL
PRIMARY KEY ProcedureID, ProcedureSuffix)
Thanks in advance --
CarlOnly a matter of a comma and a parenthesis. Below work fine:
CREATE TABLE #TEMPProcedures
(ProcedureID int NOT NULL
,ProcedureSuffix int NOT NULL
,PRIMARY KEY (ProcedureID, ProcedureSuffix))
I prefer to name all my contraints...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Carl Imthurn" <nospam@.all.com> wrote in message news:uIvG984GGHA.1032@.TK2MSFTNGP12.phx.gbl
..
> Is it possible to issue the CREATE TABLE command and specify a multiple-co
lumn primary key?
> If so, what is the syntax? I've checked BOL and as far as I can tell, you
may only select
> a single column as the primary key *within the CREATE TABLE command*; ALTE
R TABLE must be used
> for multiple-column primary keys.
> For example (this does *not* work):
> CREATE TABLE #TEMPProcedures (ProcedureID int NOT NULL, ProcedureSuffix in
t NOT NULL
> PRIMARY KEY ProcedureID, ProcedureSuffix)
> Thanks in advance --
> Carl
>|||Thanks Tibor - that worked.
And, your comment about naming all constraints is well taken.
Carl
Tibor Karaszi wrote:

> Only a matter of a comma and a parenthesis. Below work fine:
> CREATE TABLE #TEMPProcedures (ProcedureID int NOT NULL
> ,ProcedureSuffix int NOT NULL
> ,PRIMARY KEY (ProcedureID, ProcedureSuffix))
> I prefer to name all my contraints...

Create table in schema with Enterprise Manager

How can I specify the schema I want the table to be created in with
Enterprise Manager 2005?
It justs asks for a table name and if you put a schema name in it just
treats it as part of the table name
Paul
Paul Hatcher (PaulHatcher@.discussions.microsoft.com) writes:
> How can I specify the schema I want the table to be created in with
> Enterprise Manager 2005?
> It justs asks for a table name and if you put a schema name in it just
> treats it as part of the table name
CREATE TABLE schemaname.tbl
Let the graphic tools be. They are some funny toys that have ended up in
the wrong place. And, whatever, never use them to change your tables.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Create indexes - own File Group

With help of others on this group, I've been learning and researching
about indexes; an area I neglected.
I see I can specify which filegroup I wish to create an index, which
the default is Primary.
I have more than one drive in my SQL server where I put data and logs
on their own logical raid groups.
My databases are SIMPLE, so they dont use much, if any logs (none as I
understand).
I was thinking of adding an additional file to my database and use it
solely for the indexes.
Any thoughts?

SQL Server 2005 Enterprise x64 SP2
8 disk SAS Raid 1+0 w/ 512mb ram w/ battery backup.
Thanks,
Rob
"rcamarda" <robert.a.camarda@.gmail.comwrote in message
news:1173725223.934126.197140@.30g2000cwc.googlegro ups.com...

Quote:

Originally Posted by

With help of others on this group, I've been learning and researching
about indexes; an area I neglected.
I see I can specify which filegroup I wish to create an index, which
the default is Primary.
I have more than one drive in my SQL server where I put data and logs
on their own logical raid groups.
My databases are SIMPLE, so they dont use much, if any logs (none as I
understand).
I was thinking of adding an additional file to my database and use it
solely for the indexes.
Any thoughts?
>

Yes, I've done this and putting your non-clustered indexes in its own group
can provide an improvement. Especially for rebuilds and the like.

HOWEVER, your database most definitely DOES use the logs. SIMPLE simply
means that as soon as transactions are complete, the log is truncated so it
won't grow. But it is definitely being used.

Keep in mind that w/o logs, you seriously hamper your disaster recovery
options. Since you're running SQL Server Enterprise, that makes me suspect
this is more than just a little test site.

Quote:

Originally Posted by

SQL Server 2005 Enterprise x64 SP2
8 disk SAS Raid 1+0 w/ 512mb ram w/ battery backup.
Thanks,
Rob
>

--
Greg Moore
SQL Server DBA Consulting
Email: sql (at) greenms.com http://www.greenms.com|||Greg,
Its a data warehouse. I have tools that could rebuild an empty
database pretty quickly (few hours) and I have daily backups. Builds
take about 2 hours, then its read only the rest of the day.
I use enterprise because of our licensing.

Thanks for the info!

Quote:

Originally Posted by

>
Yes, I've done this and putting your non-clustered indexes in its own group
can provide an improvement. Especially for rebuilds and the like.
>
HOWEVER, your database most definitely DOES use the logs. SIMPLE simply
means that as soon as transactions are complete, the log is truncated so it
won't grow. But it is definitely being used.
>
Keep in mind that w/o logs, you seriously hamper your disaster recovery
options. Since you're running SQL Server Enterprise, that makes me suspect
this is more than just a little test site.
>

Quote:

Originally Posted by

SQL Server 2005 Enterprise x64 SP2
8 disk SAS Raid 1+0 w/ 512mb ram w/ battery backup.
Thanks,
Rob

>
--
Greg Moore
SQL Server DBA Consulting
Email: sql (at) greenms.com http://www.greenms.com

CREATE INDEX ..using b-tree algorithm

Is it possible to specify an algorithm like B-TREE in create index syntax of MSSQL Server.
In MySQL i have the option to specify this in create table syntax .
Its very urgent.Is this the same question as this:
http://www.dbforums.com/showthread.php?t=1617754
?

If so please don't post dupe threads.

CREATE DATABASE

Is there any way to learn default settings for path where new database
will be created on server machine if I do not specify the path explicitly,
just running on client machine:
CREATE DATABASE [testdb]This is decided by either the
Default Data Directory (Right click server | Properties | Database Settings)
If that is empty then you will still be using the default dir (DATA) from
install time
In my case this would be
D:\Microsoft SQL Server\MSSQL\Data
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Setup
--
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Vlad Gonchar" <VladG@.Frogware.com> wrote in message
news:OaoBReefDHA.1764@.TK2MSFTNGP09.phx.gbl...
> Is there any way to learn default settings for path where new database
> will be created on server machine if I do not specify the path explicitly,
> just running on client machine:
> CREATE DATABASE [testdb]
>
>