data types

I'm reading Sams TY SQL in 10 minutes and on the examples he uses CHAR for
practically everything, id's, addresses etc.
I upsized a small dbase from access and when I looked at a table, the
datatypes were a lot different. I noticed all ID fields were int and
nvarchar was used on most other fields. In the reserved words glossary, I
didn't see nvarchar. I guess it's missing a few types from the list.
I did a search for dbase schemas on google and found the Duwamish Book
example on MSDN. That dbase also uses nvarchar and int as the upsized access
dbase did.
Just curious why I should use nvarchar instead of char etc.
ThanksThe n in nvarchar indicates that it's for unicode. If you don't plan to use
unicode, you don't need to use nvarchar, and you can use varchar instead.
nvarchar is documented in the SQL Server Books Online.
--
Linchi Shea
linchi_shea@.NOSPAMml.com
"James" <no@.email.com> wrote in message
news:%23e1oE4TiDHA.1456@.TK2MSFTNGP11.phx.gbl...
> I'm reading Sams TY SQL in 10 minutes and on the examples he uses CHAR for
> practically everything, id's, addresses etc.
> I upsized a small dbase from access and when I looked at a table, the
> datatypes were a lot different. I noticed all ID fields were int and
> nvarchar was used on most other fields. In the reserved words glossary, I
> didn't see nvarchar. I guess it's missing a few types from the list.
> I did a search for dbase schemas on google and found the Duwamish Book
> example on MSDN. That dbase also uses nvarchar and int as the upsized
access
> dbase did.
> Just curious why I should use nvarchar instead of char etc.
> Thanks
>|||> I upsized a small dbase from access and when I looked at a table, the
> datatypes were a lot different. I noticed all ID fields were int and
> nvarchar was used on most other fields.
You shouldn't be using varchar unless you know you'll need to support
foreign alphabets / unicode characters.
For a description of the (n)(var)char/(n)text datatypes, see
http://www.aspfaq.com/2354
For an exhaustive discussion of differences between Access and SQL Server,
see http://www.aspfaq.com/2214
For some other issues with upsizing, see http://www.aspfaq.com/2182
> In the reserved words glossary,
Of a SAMS book? You should be using Books Online for this kind of thing,
IMHO. Also, see http://www.aspfaq.com/2080|||Thanks

Data Type Conversion Problem

I am not sure if this is the right place to post, although I am new to SQL Server.

I upsized a database from MS Access to SQL Server 2005 on my local machine. I am trying to change the primary key from int to uniqueidentifier, but I get this error: "conversion from int to uniqueidentifier is not supported on the connected database server". I have tried googling around to see if anyone has posted an answer, but haven't found a solution. I have also tried detaching the database, but that doesn't help either.

Can someone help me understand what the error is meaning by "connected database server", and how I can make it possible to change the data type?

Thank you.

Ben

Hi Ben,

uniqueidentifier is a GUID, that is a 128 bit value. To guarantee uniqueness they are created by a system call. So there is no way how an int could be transformed to be a uniqueidentifier.

Why do you want to change that column?

--
SvenC

|||

Sorry, I should have been more clear. There is no data in the database. I upsized just the schema, triggers, and such, but not the data. I was hoping to convert the primary key from int to the uniqueidentifier.

Is this possible?

|||

I think you have to manually do the conversion: delete the current PK column (remove the PK first) and create a new column with data type uniqueidentifier.

If your schema is large with foreign key constraints this might be a lot of work. You might try to script the database to a .sql file and change the data type with a text editor. Then create the database from that modified sql file.

--
SvenC

|||I didn't think about making a script and modifying the script. Thank you for that suggestion. I will give that a try.