SitePoint Sponsor

User Tag List

Results 1 to 13 of 13
  1. #1
    SitePoint Zealot
    Join Date
    Nov 2006
    Posts
    193
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    sql 2005 uniqueidentifier

    In 2005 I seen a lot of examples using uniqueidentifier type. Is there any difference using this then using an int auto number for my customerid?

    Its guranteed to create a unique id each time I create a new record into that table, and I can use it as a primary key and a forgien key in another table?

    I read msdn but it doesnt say a hole lot on how its created. I need to know which one is a better solution for my new program.

    Does it always return the same 36 byte string formatted in the same way?

    What is GUID I have seen that used too.

    Thanks.

  2. #2
    SitePoint Author silver trophybronze trophy
    wwb_99's Avatar
    Join Date
    May 2003
    Location
    Washington, DC
    Posts
    10,649
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    GUID stands for Globally UniqueIdentifier. It is effectively garunteed to be unique, especially within the scope of the application. You can easily use them as a primary key or a foreign key or whatever. (Actually, you can use most data types as a primary or foreign keys).

    I like to use them for many situations. The biggest advantage is that you can generate the ID of your object within the program rather than having to insert it into the database before creating related items. Biggest disadvantage is that, if a user needs to say, search by id, a GUID is pretty unintelligible. Wheras an integer key is manageable.

  3. #3
    SitePoint Zealot
    Join Date
    Nov 2006
    Posts
    193
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Can you please tell me how to generate it inside my application? I thought the database done it like an auto number.

    I like it too because auto numbers will run out sooner or later. If records are added and deleted they arent returned to the database.

    I could still use a SELECT statement to pull out the id even if it is guid..?

  4. #4
    ALT.NET - because we need it silver trophybronze trophy dhtmlgod's Avatar
    Join Date
    Jul 2001
    Location
    Scotland
    Posts
    4,836
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    For information on the Guid, look here: http://msdn2.microsoft.com/en-us/lib...stem.guid.aspx

    And yeah, you can still do SELECT with a Guid.

  5. #5
    SitePoint Author silver trophybronze trophy
    wwb_99's Avatar
    Join Date
    May 2003
    Location
    Washington, DC
    Posts
    10,649
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    I really would not worry about integers running out, unless you plan on having 32 million records in your table. Then you can always upgrade to bigint.

    Anyhow, making a GUID in code is easy:

    Code:
    Guid g=Guid.NewGuid();

  6. #6
    SitePoint Wizard
    Join Date
    Jul 2003
    Location
    Kent
    Posts
    1,921
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by wwb_99 View Post
    I really would not worry about integers running out, unless you plan on having 32 million records in your table. Then you can always upgrade to bigint.

    Anyhow, making a GUID in code is easy:

    Code:
    Guid g=Guid.NewGuid();
    You'd need more records than that!
    In sql server express 2005, int goes up to 2,147,483,647
    bigint goes up to a massive 9,223,372,036,854,775,807

  7. #7
    SitePoint Zealot
    Join Date
    Nov 2006
    Posts
    193
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    9,223,372,036,854,775,807

    The day that I have that many customers I will retire.

    I was told there is a free version of SQL 2005 that you can use if your users don't have the money to get SQL Server installed. But where do you get it from, and what are the limitations? I was told is was limited to like 2GB database... is that a realistic database size for a application that will use the database constantly, like a cash register.

    I don't know how to get around the limitations. I need a database for a cash register program I am making but I don't want it to reach 2GB and then they wont be able to make any more sales transactions, and there is no other cheap solution because all the SQL database cost a lot of money. I think even MySQL costs money to run the actual server in a production server.

    Also how will GUID work when I have the same application stored on different computers, like a cash register that is installed on 5 checkout lanes, and they all go to the same database. If I use a GUID for each transaction as a kind of serial number will they still be unique to each transaction or is there a chance that two of them might have the same GUID?

    I still don't know how the GUID is generated.

  8. #8
    SitePoint Wizard
    Join Date
    Jul 2003
    Location
    Kent
    Posts
    1,921
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    free version sql server 2005 = sql server express 2005.

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

    4Gb max size.
    Ask someone who knows how much data goes through a cash till per year.

  9. #9
    SitePoint Zealot
    Join Date
    Nov 2006
    Posts
    193
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    is there a way I can get the size of the database so that I can warn the user to run a maintenance/archive utility once it reaches 3.5GB or so?

    I can also archive records that havent been access in a long time, like the transaction journal to another database or to a XML file. The file will be slow but if information hasnt been recalled in 3 months then most likely it wont be needed again, if often. It can safely be moved to the file system.

    I'm not sure how much records you can get into 4GB.

  10. #10
    SitePoint Zealot
    Join Date
    Nov 2006
    Posts
    193
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    So will my GUIDs still be unique on different computers? My program will be installed on different computers but they will all access the same database.

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,323
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by wwb_99 View Post
    Biggest disadvantage is that, if a user needs to say, search by id, a GUID is pretty unintelligible.
    but this scenario should never occur -- one never shows the user the value of any surrogate key, whether it be integer, guid, string, or whatever

    if you do let your users search by a surrogate key, it's your fault

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

  12. #12
    SitePoint Author silver trophybronze trophy
    wwb_99's Avatar
    Join Date
    May 2003
    Location
    Washington, DC
    Posts
    10,649
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    If you need a free db, check out postgres. That said, you probably want to stick with MSSQL, mainly because Postgres + .NET is a poorly documented combination. Whereas MSSQL + .NET are is a very, very smooth integration.

    I really would not worry about users hitting the 4gb limit--it is a quite lot of data if you are not storing binaries. And, if someone is clearing that much at the register, they should be able to upgrade to full-blown Sql Server. Your application will never even know the difference.

    GUIDs are designed to be globally unique. Collisions are technically possible but so rare as to never be an issue insofar as I have heard. You are talking about 1 in a few billion or so chance here. You have significantly higher chance of hitting the lottery twice in a week.

    All that said, I would use an integer ID here. Rudy has a very valid point, but people are going to have to deal with this accounting data. And people see a GUID and go GAH!. But they see a nice, easy transaction number and they carry on. At least that has been my experience.

    @Rudy:

    Very valid point. But very tricky to make to meet with reality. Sometimes things do get dumped to paper, and sometimes users need a hard id based lookup.

  13. #13
    SitePoint Zealot
    Join Date
    Nov 2006
    Posts
    193
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by wwb_99 View Post
    And, if someone is clearing that much at the register, they should be able to upgrade to full-blown Sql Server. Your application will never even know the difference.
    Your right I never thought about that. It makes since. I will make sure I mention that in my sales documentation.

    When I create a GUID I always have another ID that the user sees. The only thing I would use them for is the same I used auto numbers for. I store them in secret inside a collection or in the ValueMember property of a list control or in a data grid control. The user sees a nice ID, but then when they click the item in the list and click "show order details" or something the code grabs that GUID and passes it into a sproc to pull back the guranteed correct record behind the scene. I can use it as a primary key and it helps relationships and organize my database.

    My program prints a barcode with a special 24 digit serial number at the bottom of the receipt which they can scan and it automatically executes that number against a SELECT sproc to recall an old transaction for a refund or what ever. If they do have to type it in its a 5-3-4-6-6(24) code.

    StoreID(5)-TerminalID(5)-TransactionID(4)-Date(000000)-Time(000000)
    Its guranteed to be unique and will always be 24 bytes. If the store ID or terminal ID is less then 5 bytes its preceeded with 0's until its 5 bytes but the number cant be all 0's otherwise its not unique. StoreID, Terminal, and Transaction are unique to each register and stored on a file on that machine, but are referenced as "registered" in the database as they are created in the admin control panel so no two registers or stores can ever have the same ID. The store can be anywhere in the world as long as they have the connection string to access the database. The registers dont have to be in the same building. I like it because you can buy something at one store and recall the transaction for a refund at a different store.


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
  •