A handful of MSSQL questions

Civilian_Target

OK - I'm on my own for the first time with MSSQL - which I already hate - and I have a few questions. Amazingly I can't seem to get straightforward answers to these questions on google.

AUTO_INCREMENT - MSSQL does support it apparantly, but how do you make an auto incrementing primary key column. AUTO_INCREMENT as a keyword causes SQL errors.

CREATE statements - MSSQL seems to reserve a load of field names that you can't use in columns, any idea where I can get a list of these. They seem to be the cause of a lot of strange failures in my CREATE statements.

Any other odd behaviour I can expect, my background is DB2 / MySQL?

Kernel32

Civilian_Target wrote:

AUTO_INCREMENT - MSSQL does support it apparantly, but how do you make an auto incrementing primary key column. AUTO_INCREMENT as a keyword causes SQL errors.

CREATE TABLE new_table
(
id_num int IDENTITY(1,1)
)

Civilian_Target wrote:

CREATE statements - MSSQL seems to reserve a load of field names that you can't use in columns, any idea where I can get a list of these. They seem to be the cause of a lot of strange failures in my CREATE statements.

Any other odd behaviour I can expect, my background is DB2 / MySQL?

ADD EXCEPT PERCENT
ALL EXEC PLAN
ALTER EXECUTE PRECISION
AND EXISTS PRIMARY
ANY EXIT PRINT
AS FETCH PROC
ASC FILE PROCEDURE
AUTHORIZATION FILLFACTOR PUBLIC
BACKUP FOR RAISERROR
BEGIN FOREIGN READ
BETWEEN FREETEXT READTEXT
BREAK FREETEXTTABLE RECONFIGURE
BROWSE FROM REFERENCES
BULK FULL REPLICATION
BY FUNCTION RESTORE
CASCADE GOTO RESTRICT
CASE GRANT RETURN
CHECK GROUP REVOKE
CHECKPOINT HAVING RIGHT
CLOSE HOLDLOCK ROLLBACK
CLUSTERED IDENTITY ROWCOUNT
COALESCE IDENTITY_INSERT ROWGUIDCOL
COLLATE IDENTITYCOL RULE
COLUMN IF SAVE
COMMIT IN SCHEMA
COMPUTE INDEX SELECT
CONSTRAINT INNER SESSION_USER
CONTAINS INSERT SET
CONTAINSTABLE INTERSECT SETUSER
CONTINUE INTO SHUTDOWN
CONVERT IS SOME
CREATE JOIN STATISTICS
CROSS KEY SYSTEM_USER
CURRENT KILL TABLE
CURRENT_DATE LEFT TEXTSIZE
CURRENT_TIME LIKE THEN
CURRENT_TIMESTAMP LINENO TO
CURRENT_USER LOAD TOP
CURSOR NATIONAL TRAN
DATABASE NOCHECK TRANSACTION
DBCC NONCLUSTERED TRIGGER
DEALLOCATE NOT TRUNCATE
DECLARE NULL TSEQUAL
DEFAULT NULLIF UNION
DELETE OF UNIQUE
DENY OFF UPDATE
DESC OFFSETS UPDATETEXT
DISK ON USE
DISTINCT OPEN USER
DISTRIBUTED OPENDATASOURCE VALUES
DOUBLE OPENQUERY VARYING
DROP OPENROWSET VIEW
DUMMY OPENXML WAITFOR
DUMP OPTION WHEN
ELSE OR WHERE
END ORDER WHILE
ERRLVL OUTER WITH
ESCAPE OVER WRITETEXT

Source of all this information.... the helpfile.

amen

MS SQL Books On Line is also very good
can be found under help


post you create statements ?
how did you create the sql using an editor or design the table in InterDev/Enterprise manager and then script it ?

stevenmu

I think you can use reserved words by putting square brackets around them [fieldname], you could try just putting all fieldnames in brackets by default

rsynnott

The default escape character for single-quote is another single-quote (many ASP developers seem to ignore this with willful abandon, for some reason), though I think the C-style escape is also supported.

bonkey

Kernel32 wrote:

Source of all this information.... the helpfile.

Thats a bit of an obscure source to be recommending to ppl.....

Civilian_Target

lol! Cheers guys. Yes, I guessed the answer may have been in the helpfile, but while I have remote access to the SQL server, I don't actually have physical access - or a copy of the software myself, so no help file.

Thanks all!

Kernel32

You do appear to have internet access though. The help you get when you install is also available on the interwebby..

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/startsql/portal_7ap1.asp

Civilian_Target

Point. I forgot that MSDN actually existed, and my googling for things seemed to be in vain. The inherent problem with google when it comes to technical terms is that unless you know exactly the key word you're looking for, things become very fudged. But yeah - MSDN is a good tip, might not be a bad plan to put in the charter, preferably in big letters for goons like me:

Need help with a Microsoft app or language -> msdn.microsoft.com
Need help with Java programming -> java.sun.com
Neep help with PHP -> php.net and pear.php.net

Yeah, I just looked and saw its there - but its 3 new windows away!

[EDIT]In fact - its pretty spectacular all the same that I read the FAQ before posting and managed to miss it! I even clicked on the MSSQL link but never followed up by going to MSDN, obviously the penny never dropped[/EDIT]