Ultra Newbie - Primary Key and STORAGE

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Ultra Newbie - Primary Key and STORAGE

Author

Topic

tonypelosi
Starting Member

6 Posts

Posted - 2006-08-14 : 10:06:29

I'm trying to learn SQL with SAMS Teach Yourself SQL in 24 hours and using SQL 2000 Server but because the book is a generic SQL code I'm finding some of the examples don't work for me. Firstly is there a STORAGE command in MS SQL as when I tried to create my table and appended:


STORAGE
 (INITIAL 20M
 NEXT 1M);

All I got was an error and couldn't figure how to get round it.

Secondly, I created my table via:


CREATE TABLE TBL_EMPLOYEE
(EMP_ID		CHAR(9)		NOT NULL,
EMP_NAME	VARCHAR(40)	NOT NULL,
EMP_ST_ADDR	VARCHAR(20)	NOT NULL,
EMP_CITY	VARCHAR(15)	NOT NULL,
EMP_ST		CHAR(2)		NOT NULL,
EMP_ZIP		INTEGER		NOT NULL,
EMP_PHONE	INTEGER		NULL,
EMP_PAGER	INTEGER		NULL);

but can't figure out how to now add a Primary Key. I added a line to my QA window:


ALTER TABLE TBL_EMPLOYEE ADD CONSTRAINT EMP_ID PRIMARY KEY;

And when I parse it, it says it complete's successfully but then when I try and execute it errors.

Can you see what I'm doing wrong / should I give up now?

Thanks

nr
SQLTeam MVY

12543 Posts

Posted - 2006-08-14 : 10:08:20

You need to tell it the columns to use

ALTER TABLE TBL_EMPLOYEE ADD CONSTRAINT pk_TBL_EMPLOYEE PRIMARY KEY (EMP_ID)

Have a look in bol (books on-line) for the syntax.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

tonypelosi
Starting Member

6 Posts

Posted - 2006-08-14 : 10:15:18

Thanks.

I'm not really used to coding so I'm getting a bit lost in some of the BOL stuff.

I had a look in there intially for the STORAGE command mentioned previously but there wasn't anything so I guess that's a dud for what I'm doing?

nr
SQLTeam MVY

12543 Posts

Posted - 2006-08-14 : 10:21:22

That sounds like it's the initial space reserved and increments to increase.
In sql server this is controlled by the database attributes - a table can use whatever is available within the database.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

tonypelosi
Starting Member

6 Posts

Posted - 2006-08-14 : 10:24:59

Yes it is, I was just wondering if there was a way to code it with MS' SQL or if it was only possible via the Enterprise Manager.

You wouldn't happen to know if there are any better books out there for me to learn from?

nr
SQLTeam MVY

12543 Posts

Posted - 2006-08-14 : 11:17:52

I would advise against using enterprise manager - especially if you are trying to learn.
Everything it does is available via t-sql.
For the storage stuff have a look at the create database statement.

Learning sql from a generic book isn't a bad idea. Hopefully it'll get across set based concepts and you'll learn more from things that don't work than from things that do.
Best is to read forums like this, try to answer questions (don't worry about being wrong - you'll be corrected) and ask about things you don't understand.

Ken Henderson is highly thought of and I like Joe Celko (he's not t-sql but very good for concepts - it's worthwhile doing a search and reading some of his vitriolic posts).

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

tonypelosi
Starting Member

6 Posts

Posted - 2006-08-14 : 11:43:25

Sounds interesting.

So for my original CREATE TABLE statement how would I have implemented the initial size of the table and the growth of the table. I did have a look at the BOL for STORAGE but couldn't find anything and obviously don't know where to look for an alternative as my SQL knowledge is currently just above that of an AOL user.

Expect more stupid questions soon!

nr
SQLTeam MVY

12543 Posts

Posted - 2006-08-14 : 11:46:56

You can't. You can only set those values for a database (well database file) not for a table.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources