Results 1 to 11 of 11
  1. #1
    Join Date
    Dec 2006
    Posts
    6

    Unanswered: uniqueidentifier

    as above, how can i use this datatype to generate a running number for my userID ? i tried with newid() but it returns a unique 32bit.

  2. #2
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    uniqueidentifier is the sql name for what everyone else calls a GUID. and newid() doesn't return a 32bit anything. a guid is 128bits.

    I think you want to use an identity column for your userid. try this:
    Code:
    declare @t table (id int identity, name varchar(20))
    insert into @t (name) values ('name1')
    insert into @t (name) values ('name2')
    insert into @t (name) values ('name3')
    insert into @t (name) values ('name4')
    insert into @t (name) values ('name5')
    insert into @t (name) values ('name6')
    select * from @t

  3. #3
    Join Date
    Dec 2006
    Posts
    6
    Quote Originally Posted by jezemine
    uniqueidentifier is the sql name for what everyone else calls a GUID. and newid() doesn't return a 32bit anything. a guid is 128bits.

    I think you want to use an identity column for your userid. try this:
    Code:
    declare @t table (id int identity, name varchar(20))
    insert into @t (name) values ('name1')
    insert into @t (name) values ('name2')
    insert into @t (name) values ('name3')
    insert into @t (name) values ('name4')
    insert into @t (name) values ('name5')
    insert into @t (name) values ('name6')
    select * from @t
    i forgot to add im using the vs.net sql2005. im not too sure about the codes but isit setting the datatype as INT and set the Is Identity to yes? I remember that this does generate a running number in numbers, if i were to export the database to another, there might be collision. correct me if im wrong

  4. #4
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    there is nothing globally unique about an int, so you could certanly get collisions between values in identity columns in different tables, in the same or different databases. so if it's a requirement that your id be globally unique, you can't use int.

    The only type in sql that is guaranteed to be globally unique are uniqueidentifiers, which are guids.

    Since you are using 2005, if you want to use this for a pk column, then you should look at the newsequentialid() function. it will give you the same behavior as identity for ints. it's good to use on a clustered pk because it keeps your table from becoming fragmented. read more here:

    http://msdn2.microsoft.com/en-us/library/ms189786.aspx

  5. #5
    Join Date
    Dec 2006
    Posts
    6
    Quote Originally Posted by jezemine
    there is nothing globally unique about an int, so you could certanly get collisions between values in identity columns in different tables, in the same or different databases. so if it's a requirement that your id be globally unique, you can't use int.

    The only type in sql that is guaranteed to be globally unique are uniqueidentifiers, which are guids.

    Since you are using 2005, if you want to use this for a pk column, then you should look at the newsequentialid() function. it will give you the same behavior as identity for ints. it's good to use on a clustered pk because it keeps your table from becoming fragmented. read more here:

    http://msdn2.microsoft.com/en-us/library/ms189786.aspx
    thanks for ur input i will be using it as PK and also for referrencing. so i think if its too unique i might be having a problem in the later stage.

    when i try to insert a data into the table, i rec this error msg below:

    the NEWSEQUENTIALID() built-in function can only be used in DEFAULT expression for a column of type 'uniqueidentifier' in a CREATE TABLE or ALTER TABLE statment. It canot be combined with other operations to form a complex scalar expression.
    Last edited by LPate; 12-06-06 at 00:44.

  6. #6
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    did you read the link I posted? they have an example there of how it's used.

  7. #7
    Join Date
    Dec 2006
    Posts
    6
    Quote Originally Posted by jezemine
    did you read the link I posted? they have an example there of how it's used.
    yes. they showed it used when creating tables. i do not know how to get into the codes from vs.net2005

  8. #8
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    what version of sql server are you running? if you don't have any sort of client tools installed, you ought to install Management Studio Express. it's free, and then you can run any sort of sql script.

    I don't know how you would go about building any sort of database app without at least a tool to execute queries...

    get mgmt studio express here:

    http://msdn.microsoft.com/vstudio/express/sql/download/

  9. #9
    Join Date
    Dec 2006
    Posts
    6
    Quote Originally Posted by jezemine
    what version of sql server are you running? if you don't have any sort of client tools installed, you ought to install Management Studio Express. it's free, and then you can run any sort of sql script.

    I don't know how you would go about building any sort of database app without at least a tool to execute queries...

    get mgmt studio express here:

    http://msdn.microsoft.com/vstudio/express/sql/download/
    it is a in-built ms sql server 2005

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by LPate
    so i think if its too unique i might be having a problem in the later stage.
    "too unique"???

    either it's unique or it isn't
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Dec 2006
    Posts
    6
    Quote Originally Posted by r937
    "too unique"???

    either it's unique or it isn't
    hahaaaa

Posting Permissions

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