SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Wizard bronze trophy cydewaze's Avatar
    Join Date
    Jan 2006
    Location
    Merry Land, USA
    Posts
    1,096
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Question about data types

    I'm moving from MS Access to MSSQL, and I have a question about data types.

    In my Access database, I had tables with columns of the following data types:

    Text (for short text entries)
    Memo (for long text entries)
    Number (numeric entries)
    Date/Time
    Autonumber (auto-incrementing numeric field)
    Yes/No

    When MSSQL was installed, ALL my databases (12 in total) were combined into one huge database. When this happened, a few things changed.

    1) ALL text fields, whether "Text" or "Memo" were converted to "nvarchar".
    2) SOME numeric fields were changed to "int" while others were changed to "float".
    3) ALL primary keys were lost, and at least one autonumber field stopped auto numbering.
    4) Yes/No fields were changed to "bit".
    5) Date/Time were retained as "datetime".

    So my questions are...

    1) Is nvarchar suitable for handling the old Memo field length? We have some tables with quite a lot of data in those fields, and I don't want anything cut off.

    2) What's the difference between "int" and "float", and why were some numeric fields converted to one type and others to the other type?

    3) Is it normal to lose primary keys when importing from Access to MSSQL? I sort of need those keys!

    We're re-doing the import in another week or so, so if there's anything I need to change, that'll be a good time to do it.
    <cfset myblog = "http://cydewaze.org/">

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,268
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    1. NVARCHAR can work, depending on how large it is, if it's NVARCHAR(MAX) it can hold 2 gigabytes

    you should use VARCHAR instead of NVARCHAR if you don't really need unicode support


    2. INTEGER and FLOAT are quite different, one hold integers the other holds floating point numbers

    as for why one or the other, maybe some of your access fields had decimal places?

    in any case, DECIMAL is usually better than FLOAT, which is only an approximate datatype


    3. no, it isn't normal

    i'd complain long and loud about this
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Wizard bronze trophy cydewaze's Avatar
    Join Date
    Jan 2006
    Location
    Merry Land, USA
    Posts
    1,096
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Thanks Rudy. I'd just go along and change the column types and set primary keys, but there are over 100 tables, and each have many columns. I'd be there all day, and it's something that I think should be retained on import.

    As for the float thing, some of the new float fields used to be autonumber primary keys, so those wouldn't have had decimals.
    <cfset myblog = "http://cydewaze.org/">

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,268
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    your import procedure is bad, and the guys who wrote it should feel bad

    autonumber to float... that's ridiculous
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Wizard bronze trophy cydewaze's Avatar
    Join Date
    Jan 2006
    Location
    Merry Land, USA
    Posts
    1,096
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    It was their first ever time touching MSSQL. Hopefully the second time will be more successful, and I'm going to ask if I can watch.
    <cfset myblog = "http://cydewaze.org/">

  6. #6
    SitePoint Wizard bronze trophy cydewaze's Avatar
    Join Date
    Jan 2006
    Location
    Merry Land, USA
    Posts
    1,096
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    So, looking at MSSQL data types, it looks like for primary keys that auto increment, I'd want to use:

    • int
    • primary key
    • identity


    and I wouldn't have to use "unique" as the identity would handle this.
    <cfset myblog = "http://cydewaze.org/">

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,268
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by cydewaze View Post
    So, looking at MSSQL data types, it looks like for primary keys that auto increment, I'd want to use:
    correct

    Quote Originally Posted by cydewaze View Post
    and I wouldn't have to use "unique" as the identity would handle this.
    actually, it`s PRIMARY KEY that handles uniquness

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Wizard bronze trophy cydewaze's Avatar
    Join Date
    Jan 2006
    Location
    Merry Land, USA
    Posts
    1,096
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    This is worse than I thought. NONE of the primary keys NOR the auto-increment properties have been retained on ANY of the columns! The new database as it is is USELESS to me.

    I've inquired about the import routine, and was told "It's all automated". I'll have to twist some arms tomorrow at work to figure out who's in charge of the actual importing, and see what the heck they're using.
    <cfset myblog = "http://cydewaze.org/">


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •