Database Systems Help

**raspberryh** · March 6 '06, 01:40 PM (#1)

Hi,
I am used to working on a Unix server using SQL... But right now the site I'm working on is on a Microsft Server using Microsoft SQL... I never really knew if there was much of a difference, but my query which was working fine on the Unix server is giving me errors on the SQL server. It's a create table query. Maybe someone can see the problem right away?

Code:

CREATE TABLE jobs (
  id int(11) NOT NULL auto_increment,
  title varchar(255) NOT NULL default '',
  description mediumtext NOT NULL,
  qualifications mediumtext NOT NULL,
  dateadded int(8) NOT NULL default '0',
  acceptingthru int(8) NOT NULL default '0',
  contact varchar(255) NOT NULL default '',
  phone varchar(255) NOT NULL default '',
  email varchar(255) NOT NULL default '',
  address mediumtext NOT NULL,
  PRIMARY KEY  (id)
) TYPE=MyISAM AUTO_INCREMENT=6 ;

And the error I get is this:
Line 2: Incorrect syntax near 'auto_increment'

I have tried looking up stuff on auto_increment for MS SQL but can't find anything.

Thanks,
Heather

ACW · March 7 '06, 06:27 AM (#2)

Try this...

Code:

CREATE TABLE [jobs] (
  [id] [int] IDENTITY (1, 1),
  title varchar(255) NOT NULL default '',
  description text NOT NULL,
  qualifications text NOT NULL,
  dateadded [datetime] NOT NULL default '0',
  acceptingthru [datetime] NOT NULL default '0',
  contact varchar(255) NOT NULL default '',
  phone varchar(255) NOT NULL default '',
  email varchar(255) NOT NULL default '',
  address text NOT NULL,
  CONSTRAINT [PK_jobs] PRIMARY KEY  CLUSTERED([id])
)

Note that I had to change some data types and it will auto increment the id field by one. Also, brackets ([]) around the table and column names are optional but it is good to use so that MS SQL don't confuse it with a keyword.

Let us know how it turns out.
<edit>grammar</edit>

**raspberryh** · March 7 '06, 10:06 AM (#3)

Hey ACW,
Thanks for your reply! So, if brackets tell it that the word is not being used as a keyword, I was wondering why there are brackets around [int] and [datetime]?
Also, I wanted dateadded and acceptingthru to be int fields of size 8 - is that not possible in Microsoft SQL? I already have all the code written (from when it was on the old servers) and I'd have to rewrite all the code if I changed that. Anyways, I like manipulating the numbers myself rather than using the premade date-time functions, I don't know why, I just do.
Thanks,
Heather

ACW · March 7 '06, 11:27 AM (#4)

Quote:

Originally Posted by raspberryh

Hey ACW,
Thanks for your reply! So, if brackets tell it that the word is not being used as a keyword, I was wondering why there are brackets around [int] and [datetime]?

You got me on that one. My statement was based on experience with other commands, such as SELECT statements. Complex queries that use keywords as a table or column name will fail without the brackets so it is a good practice to use them. I hardly ever use SQL to create a table and use Enterprise Manager (EM) instead. I decided to let EM generate the SQL to create the table for me and oddly enough it put brackets on both the names and data types...

Code:

CREATE TABLE [dbo].[jobs] (
	[id] [int] IDENTITY (1, 1) NOT NULL ,
	[title] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL ,
	[description] [text] COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL ,
	[qualifications] [text] COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL ,
	[dateadded] [int] NOT NULL ,
	[acceptingthru] [int] NOT NULL ,
	[contact] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL ,
	[phone] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL ,
	[email] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL ,
	[address] [text] COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL , 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

Quote:

Originally Posted by raspberryh

Hey ACW,
Also, I wanted dateadded and acceptingthru to be int fields of size 8 - is that not possible in Microsoft SQL? I already have all the code written (from when it was on the old servers) and I'd have to rewrite all the code if I changed that. Anyways, I like manipulating the numbers myself rather than using the premade date-time functions, I don't know why, I just do.
Thanks,
Heather

You should be able to use [int] instead of [datetime]. I'm not sure what int(8) in MySQL (8 bytes or 8 digits?) but in MS SQL you just use int to make an 8 byte integer. The only drawback I see in not using the biult in datetime or smalldatetime is loosing the ability to use some of the built in date functions (although you might be able to convert your int to datetime right in the query).

smoseley · March 8 '06, 02:30 AM (#5)

Some answers and suggestions:

MS-SQL and MySQL are variations on ANSI-92 SQL. Each fills in the gaps with some of their own unique functions and datatypes. Microst SQL Server uses bytes as the basis for all datatypes, so everything increases in increments of powers of 2. You can store something as a varchar(255), for example, but it uses 256 bytes to do so. Microsoft SQL int datatype is the same as a Long Int or Long datatype in most programming languages - it's 4 bytes of signed data (something like 32 billion + and -). In MS-SQL, a datetime or an int using Julian Dates (look it up on Google) is most efficient. To store dates as MMDDYYYY, you should probably use a varchar(8), but then you're using 8 bytes alphanumeric vs. 4 bytes numerical. You can see the inefficiency.

Here's my suggestion for the design of your table:

Code:

CREATE TABLE jobs (
	id int NOT NULL IDENTITY (1, 1) PRIMARY KEY,
	title varchar(128) NOT NULL,
	description varchar(4096) NOT NULL,
	qualifications varchar(2048) NOT NULL,
	date_created datetime NOT NULL DEFAULT GETDATE(),
	date_expires datetime NOT NULL DEFAULT GETDATE(),
	contact_name varchar(128) NOT NULL,
	contact_phone varchar(128) NOT NULL,
	contact_email varchar(128) NOT NULL,
	contact_address varchar(1024) NOT NULL
);
GO

Notice I'm using powers of 2 for all the variable data sizes. This is to maximize efficiency of storage. Also, you're limited to just over 8000 bytes per row, so you don't want to use up your space. I changed the text data fields to varchars, because text is stored as binary data and significantly slows you down. You don't want to use it unless you need more than 8000 bytes (characters) of data per row.

The GETDATE() function is like NOW() or CURRENT_TIMESTAMP - it fills the date field with the current date and time values.

GO is a required batch execution command in MS-SQL (MySQL executes after every semicolon). If you're creating tables that have dependencies upon eachother (FOREIGN KEY / REFERENCES for example) you'll need to put a GO between each CREATE statement. It's a good habit to get into.

Finally, I changed some of yoru column names for clarity. Ideally, you should have contacts in a separate table referenced by jobs (to follow 3rd normal form), but I won't get picky there.

That's about that.... have fun!

**raspberryh** · March 9 '06, 10:22 PM (#6)

Hey Transio,
Thanks for your reply. Wow, I have never thought about everything in so much depth when creating a table for a database. I really like the way you analyze everything to maximize efficiency. Thanks so much for all your suggestions!
Heather

Database Systems Help

unix to microsoft - same sql query?