Question about data types

Databases
1

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.

2
  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

  1. 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

  1. no, it isn’t normal

i’d complain long and loud about this

3

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.

4

your import procedure is bad, and the guys who wrote it should feel bad

autonumber to float… that’s ridiculous

5

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.

6

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.

7

correct

actually, it`s PRIMARY KEY that handles uniquness

:slight_smile:

8

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.