Register

If this is your first visit, please click the Sign Up now button to begin the process of creating your account so you can begin posting on our forums! The Sign Up process will only take up about a minute of two of your time.

Results 1 to 6 of 6
  1. #1
    Senior Member raspberryh's Avatar
    Join Date
    Aug 2004
    Location
    Erie, PA
    Posts
    882
    Member #
    7208
    Liked
    1 times
    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
    choosy developers choose gif!
    website | paintings | blog

  2.  

  3. #2
    ACW
    ACW is offline
    Member
    Join Date
    Jul 2004
    Posts
    82
    Member #
    6554
    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>
    Affordable Business Web Site Hosting by Geo Redundant Hosting

  4. #3
    Senior Member raspberryh's Avatar
    Join Date
    Aug 2004
    Location
    Erie, PA
    Posts
    882
    Member #
    7208
    Liked
    1 times
    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
    choosy developers choose gif!
    website | paintings | blog

  5. #4
    ACW
    ACW is offline
    Member
    Join Date
    Jul 2004
    Posts
    82
    Member #
    6554
    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).
    Affordable Business Web Site Hosting by Geo Redundant Hosting

  6. #5
    WDF Staff smoseley's Avatar
    Join Date
    Mar 2003
    Location
    Boston, MA
    Posts
    9,729
    Member #
    819
    Liked
    205 times
    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!

  7. #6
    Senior Member raspberryh's Avatar
    Join Date
    Aug 2004
    Location
    Erie, PA
    Posts
    882
    Member #
    7208
    Liked
    1 times
    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
    choosy developers choose gif!
    website | paintings | blog


Remove Ads

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
All times are GMT -6. The time now is 11:22 PM.
Powered by vBulletin® Version 4.2.3
Copyright © 2019 vBulletin Solutions, Inc. All rights reserved.
vBulletin Skin By: PurevB.com