Getting A Unique ID In SQL Server - Other Than A GUID

May 1, 2006

In Access, newID returns a unique for the table. In SQL Server, newid()
returns a GUID - unique in the world. I do not need or desire that
complexity. Is there a way to get a simple unique int on the table in
SQL Server?

Brad Eck
http://www.sitesdynamic.com
http://www.basketsetcetera.com

*** Sent via Developersdex http://www.developersdex.com ***

View 1 Replies


ADVERTISEMENT

Is A GUID Always Unique?

Jul 20, 2005

Greetings All, I have read many upon many articles here regarding GUIDdata types and uniqueness. There have been many opinions regardingthe effectiveness of GUID's and when they should/should not be used.However, every article strongly implies, if it does not state itoutright, that GUID's are always unique. My question is this, whathappens if you have a database that uses GUID's and the NIC is changedout on the box? From what I understand the MAC address of the NIC isused as part of the algorithm to generate a GUID. If you change outthe NIC after generating 1 billion GUID's do you run the chance ofgenerating a duplicate GUID?I look forward to your insightfulness on this issue.Regards, Louis.

View 4 Replies View Related

Are GUID Trully Unique?

May 8, 2006

Let's say that I have a database with 2 tables named 'TableA' and 'TableB'. Each one of those two tables contains the two fields 'MyKey' and 'MyData'. The field 'MyKey' has the type 'uniqueidentifier' and its default value is 'getnewid()'.

Now I would like to consider a third table named 'TableC' with two fields 'KeyRef' and 'MetaData'. The type of 'KeyRef' is 'uniqueidenfier' and corresponds to an element of either 'TableA' or 'TableB'.

If the GUID identifiers are trully unique then there is no issue to proceed that way (I am not consering perf matters here). On the contrary, if GUID are not unique, then there is a collision possibility.

My question is simply: can I consider the GUID to be unique in MS SQL Server (2000 or 2005).

Thanks in advance,
Joannès

View 16 Replies View Related

Do I Need To Verify That NewID() Returns A Unique GUID?

Oct 26, 2007

 I'm migrating a web based system to SQL server.  I'm planning on using the SQL server function NewID() to create unique keys for many of my records in many different tables.  I'm just wondering if NewID() is guaranteed to return a value that does not already exist in my database.  I mean obviously once you have a certain number of records (a hell of a lot) you'd be breaking the odds to never come up with a duplicate. Do I need to make sure the result of NEWID() doesn't already exist?Thanks 

View 1 Replies View Related

Storing GUID (global Unique Identifier)

Feb 9, 2005

Function GUID() As String
GUID = System.GUID.NewGuid().ToString()
End Function

GUID is always 36 characters, for example:

1737be72-fe96-4c3c-b455-3730b049bef9

What the best way to store this in a MS SQL database? I'm thinking of using a fixed length string (char). Is there a better way than to just store this 36 character field?

View 2 Replies View Related

For Unique ID Across Space And Time, Is There Any Alternatives To GUID?

Dec 11, 2007

I've googled everything about GUID, seems there is no good thing to say about it. Here's my scenario, a purchase order (PO) application:



We want to have a centralized database with remote sites connected to it

Some of the sites are without connection, they will have their own servers with scheduled replication to the centralized database.
The design is something like this:


Each PO will have many revisions

Each revision will have many PO line items

Each PO line item will have many Delivery Schedules
In the past i used int IDENTITY as transaction ID in revision and line item tables.

transaction ID in revision table is FK to line item table, and transaction ID line item table is FK to Delivery Schedules table.

This work well in standalone database.

Now that we need to merge replicates, int IDENTITY produced in remote DB will conflict with IDENTITY produced in central DB.

I'm thinking of using GUID to replace int IDENTITY.

Question:



How bad is my decision?

Can't GUID size indexing problem be solved with partitioning?


Can you suggest other alternatives to GUID, based on the above scenario?
Thanks in advance

View 4 Replies View Related

How To Convert SQL Unique Identifier In A Csv File To A SSIS GUID Variable

Aug 21, 2007

Here's a problem description I read on another post but I have the same issue:

I am trying to Import Data from a csv file into a SQL Sever table. The Data being imported has a Unique Identifier in it but it is being considered as DT_WSTR datatype in SSIS



When i directly try to import, it gives an error:



The conversion returned status value 2 and status text "The value could not be converted because of a potential loss of data.".



I Tried to use Data Conversion to convert it to DT_GUID and import into SQL Server table but it again throws the above error.
Anyone has a clue?

Thanks!

View 3 Replies View Related

SQL Server 2012 :: Failing On Update With Unique Index Error (Not Unique)

Jul 5, 2015

This index is not unique

ix_report_history_creative_id

Msg 2601, Level 14, State 1, Procedure DFP_report_load, Line 161
Cannot insert duplicate key row in object 'dbo.DFP_Reports_History' with unique index 'ix_report_history_creative_id'.

The duplicate key value is (40736326382, 1, 2015-07-03, 67618862, 355324).
Msg 3621, Level 0, State 0, Procedure DFP_report_load, Line 161

The statement has been terminated.

Exception in Task: Cannot insert duplicate key row in object 'dbo.DFP_Reports_History' with unique index 'ix_report_history_creative_id'. The duplicate key value is (40736326382, 1, 2015-07-03, 67618862, 355324).

The statement has been terminated.

View 6 Replies View Related

How To Generate Guid In Sql Server

Jun 11, 2008

Dear Expert,
Iam new to sql server2000

I have table Table1 in that table I want to generate unique guid in one column.I want the code for that one kindly help me.

View 2 Replies View Related

How To Generate GUID In SQL Server

Apr 19, 2007

Hello,



I want to create unik primary key for my database.

Somebody advice me to use the GUID.

I'm not sure on how it can be created. I have to create it on my application side or database side?



Kindly advice.



Best regards,
Tee Song Yann

View 7 Replies View Related

SQL Server 2008 :: Random GUID And New SequentialID

Mar 20, 2015

I have a DB using random GUID values as a PK. I know it should not be that way but it is third party and I am trying to persuade them to do something else, like a sequential GUID using NEWSEQUENTIALID. My question is this. Since I have GUID values already in the database, if we change the table definition from simply uniquidentifier to NEWSEQUENTIALID what is to keep NEWSEQUENTIALID from generating a GUID that already exists and creating a PK violation?

View 3 Replies View Related

Populate GUID Column In Table B With Values From GUID Column In Table A

Mar 4, 2008


How do I update the OrderGUID column in Table B with Values from OrderGUID column in Table A. I have already populated the OrderGUID column in Table A using NEWSEQUENTIALID(). Now I need to populate the OrderGUID column in Table B with Matching GUID values from the OrderGUID Column in Table A.

Does any one have a script to accomplish this task. thanks

View 4 Replies View Related

What Is The Difference Between A UNIQUE INDEX And A UNIQUE CONSTRAINT?

Sep 22, 2004

A UNIQUE INDEX must inherently impose a unique constraint and a UNIQUE CONSTRAINT is most likely implemented via a UNIQUE INDEX. So what is the difference? When you create in Enterprise Manager you must select one or the other.

View 8 Replies View Related

Unique Constraint Vs Unique Index In MS SQL 2000

Jul 20, 2005

HelloWhat should I use for better perfomance sinceunique constraint always use index ?ThanksKamil

View 5 Replies View Related

Unique Constraint And Unique Index, What's The Difference?

Jun 24, 2006

What's the difference in the effect of the followings:
CREATE UNIQUE NONCLUSTERED INDEX
and
ALTER TABLE dbo.titles ADD CONSTRAINT
titleind UNIQUE NONCLUSTERED

I found there're two settings in Indexs/Keys dialog box of the management studio, Is Unique, and Type. The DDL statements above are generated by setting Is Unique to yes plus Type to Index, and just Type to Unique Key, respectively. What's the difference between them?

View 1 Replies View Related

Unique Index Vs Unique Constraint

Mar 7, 2001

Hi everyone,
I need urgent help to resolve this issue...
As far as the performance goes which one is better..
Unique Index(col1, col2) OR Unique constraint(col1, col2) ?
Unique constraint automatically adds a unique index
and unique index takes care of uniqueness then whats the use of unique constraint ?

Which one do one use ?

thanks
sonali

View 4 Replies View Related

Unique Constraint Vs Unique Index

Jan 20, 2006

BOL says a unique constraint is preferred over a unique index. It also states that a unique constraint creates a unique index. What then is the difference between the two, and why is a constraint preferred over the index?

View 2 Replies View Related

Unique Index Vs Unique Constraints

Mar 26, 2008



hi team,
.Can i create umique constraint with out unique index.when i am creating a unique constraint sql creates a unique index (default) can i have only unique constraint ?

View 12 Replies View Related

How To Select Unique Row When Entire Row Not Unique?

Mar 12, 2008

I am having a problem trying to figure out the best way to get the results I need. I have a table of part numbers that is joined with a table of notes. The table of notes is specific to the part number and user. A row in the notes table is only created if the user has entered notes on that part number. I need to create a search that grabs all matches on a keyword and returns the records. The problem is that it currently returns a row from the parts table with no notes and a separate row with the notes included if they had created an entry. It seems like this should be easy but it eludes me today.
Here is the code



Code Snippet
create procedure SearchPartKeyword
(
@Keyword varchar(250) = null,
@Universal_Id varchar(10) = null
)
as
select p.PartNumber, p.Description, p.ServiceOrderable, n.MyNotes, p.LargestAssembly, p.DMM,
p.Legacy, p.Folder, p.Printer
from Parts p inner join notes n on p.PartNumber = n.Identifier
where n.Universal_ID = @Universal_ID and p.Description like @Keyword
union
select p.PartNumber, p.Description, p.ServiceOrderable, '' as MyNotes, p.LargestAssembly,
p.DMM, p.Legacy, p.Folder, p.Printer
from Parts p
where p.Description like @Keyword





and the results:
PartNo Description SO Notes LA DMM Legacy Folder Printer
de90008 MAIN BOARD 1 DGF1 114688 0 0 0
de90008 MAIN BOARD 1 I love this part Really I do DGF1 114688 0 0 0

This could return multiple part numbers and If they have entered notes I want the row with the notes

Thank You
Dominic Mancl

View 1 Replies View Related

What 's Difference Between Unique Key And Unique Index

Nov 13, 2007

What 's difference between Unique key and unique index in SQL server 2005?

View 9 Replies View Related

GUID's

Feb 1, 2001

we are currently using id's as primary key and replication is not part of our project.
will this be a problem if we decide to do replication? will microsoft generate an identifier then.
what is the advantages of using GUID now or doing it latter?

View 1 Replies View Related

GUID

May 26, 2006

Hello,

I'm working on a smart client app that has an offline sql express store and needs to work with several types of central databases (support for multiple products - ms sql, DB2 etc)

While trying to put together some offline functionality that needs the user to create records on the offline sql express data store, we've run into the need of being able to uniquely identify records so replicating the data in the offline store back into the primary database should not be a problem.

The data created offline spans many tables and involves several tables with relation ships - FK etc...Clearly not a simple case of store and forward.

We dont want to get into the mess of performing key replacement during a synch job with the server. Thats way too much trouble.

GUID seems like a good choice, but as always we have several stake holders having different opinions. And with databases other than MS SQL we will have to store them as strings.

To cut to the chase - can we not hash a GUID to get an integer while retaining atleast the same likelyhood of producing unique ids ? [no drop]

Thanks,

Aviansh



View 13 Replies View Related

Get Unique Number In Sql Server

Nov 21, 2001

Hi,

I am new to sql server, in oracle one can get a unique number as:

create sequence my_number
increment by 1 start with 1;

select my_number.nextval from dual;

Is there any similiar mechanism in sql server? if so, how to do it?

THanks in advance,

bb

View 3 Replies View Related

Unique Index In Sql Server

Mar 4, 2008

What is unique index in sql server 2k?
plz some one help me out, thanx

Rahul Arora
07 Batch
NCCE Israna,


######################
IMPOSSIBLE = I+M+POSSIBLE

View 2 Replies View Related

Profile GUID

Apr 13, 2007

I am using a SqlDatasource and need to set a SelectParamter to the ProviderUserKey (The GUID of the user when Profiles are enabled)
 Can anyone tell me whether it is possible and How?
I am currently using the session state to store it in and then using the session=... to get the value into the parameter.
Is there a direct way of passing this value into a SelectParameter when using a SqlDataSource?
Thanks in advance.

View 3 Replies View Related

How To Specify A Guid In A WHERE Clause

Sep 25, 2007

Hi Everyone,
  I'm trying to create a SQL Delete statement using a string builder and the WHERE clause uses a Guid. Here is the code:stb.Append("DELETE FROM UserRights WHERE UserIDPtr = ");
stb.Append(TargetUserID);The resulting string is:  "DELETE FROM UserRights WHERE UserIDPtr = e01549fb-edf5-4668-de8b-b13dd5661a6e"
When I try to do an ExecuteNonQuery() using the string as the CommandText, I get an error.
Invalid column name 'e01549fb',Invalid column name 'edf5',Invalid column name 'de8b',Invalid column name 'b13dd5661a6e'
It is also strange that '4668' did not show up as an invalid column name, but I don't think that is relavent to this issue.
Can someone show me (or point me to an article) about using Guid's in a text string as a SQL command? Thanks in advance!

View 2 Replies View Related

GUID Issues

May 5, 2008

My database is using the membership store for all the user information. I added a tabel "Skills" with 3 fields "SkillID (GUID)" "SeekerID (GUID, This is the UserId)" and "SkillName ( Varchar(MAX))"
on the page i have a ListView setup to display the SkillName fields based on the SeekerID.
The original code was     Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
          ' Get a reference to the currently logged on user          Dim CurrentUser As MembershipUser = Membership.GetUser
          ' Determine the currently logged on user's UserId value
          Dim SeekerID As Guid = CType(CurrentUser.ProviderUserKey, Object)
     End Subthat kept returning string to GUID conversion errors, so i had to change  Dim SeekerID As Guid = CType(CurrentUser.ProviderUserKey, Object) to Dim SeekerID As Object = CType(CurrentUser.ProviderUserKey.ToString(), Object) that appears to be working now. On the same page however, I want to insert records into the table, I tried 2 options both of which have a different problem.
     Option 1: Use a textbox (ID = NewSkill) and a button with the following code on it:
          Protected Sub SetNewSkill_Click(ByVal sender As Object, ByVal e As System.EventArgs)               Dim CurrentUser As MembershipUser = Membership.GetUser
               Dim SeekerGUID As Object = CType(CurrentUser.ProviderUserKey, Object)
 
               Dim NewSkill As TextBox = CType(FindControl("NewSkill"), TextBox)               Dim connectionString As String = ConfigurationManager.ConnectionStrings("QJSdatabase").ConnectionString()
               Dim insertSql As String = "INSERT INTO Skills(SeekerID, SkillName)VALUES(@SeekerGUID, @NewSkill)"               Using myConnection As New SqlConnection(connectionString)
                    myConnection.Open()                    Dim myCommand As New SqlCommand(insertSql, myConnection)
                    myCommand.Parameters.AddWithValue("@SeekerGUID", SeekerGUID)                    myCommand.Parameters.AddWithValue("@NewSkill", NewSkill.Text.Trim())
                    myCommand.ExecuteNonQuery()
                    myConnection.Close()
               End Using
          End Sub
 
     Problem: myCommand.Parameters.AddWithValue("@NewSkill", NewSkill.Text.Trim())  gets outlined and returns the error: Object reference not set to an instance of an object
 
     Option 2: Use a DetailsView linked to a SQLDataSource on the page
           <asp:DetailsView ID="DetailsView1" runat="server" Height="50px" Width="210px"
               AutoGenerateRows="False" DataSourceID="SqlDataSource2" DefaultMode="Insert">                    <Fields>
                         <asp:BoundField DataField="SkillName" HeaderText="Add Skill:" SortExpression="SkillName" />
                         <asp:CommandField ShowInsertButton="True" />
                         <asp:TemplateField InsertVisible="False">
                              <EditItemTemplate>
                                   <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
                              </EditItemTemplate>
                              <ItemTemplate>
                                   <asp:Label ID="Label1" runat="server"></asp:Label>
                              </ItemTemplate>
                    </asp:TemplateField>
               </Fields>
          </asp:DetailsView>
           <asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:database %>"
               DeleteCommand="DELETE FROM [Skills] WHERE [SkillID] = @SkillID"                InsertCommand="INSERT INTO [Skills] ([SeekerID], [SkillName]) VALUES (@SeekerGUID, @SkillName)"
               SelectCommand="SELECT SkillName FROM [Skills]"
               UpdateCommand="UPDATE [Skills] SET [SkillName] = @SkillName WHERE [SkillID] = @SkillID">
                    <DeleteParameters>
                         <asp:Parameter Name="SkillID" Type="Object" />
                    </DeleteParameters>
                    <InsertParameters>
                         <asp:Parameter Name="SeekerGUID" Type="Object" />
                         <asp:Parameter Name="SkillName" Type="String" />
                    </InsertParameters>
                    <UpdateParameters>
                         <asp:Parameter Name="SkillName" Type="String" />
                         <asp:Parameter Name="SkillID" Type="Object" />
                    </UpdateParameters>
            </asp:SqlDataSource>
 
            http://junk.icore-studios.com/junk/Codeissues/postresumeerror.html
The @SeekerGUID is being generated by the same pageload code as the first chunk i gave (I have 2 variables SeekerID is the GUID converted to string to work with the ListView filter and SeekerGUID is the GUID)
 
Ultimatly getting either option to work would be fine. Though I think the second would be preferable because I think it'd be easier to replicate later on.
Thanks in advance for your time and any help

View 3 Replies View Related

GUID Troubles.

Apr 17, 2004

Hi,

I am having a hard time updating a database row using a UNIQUEIDENTIFIER. I retrieve the row into a datagrid and then use the GUID as a parameter to a stored procedure, but it doesn't update. If I run the query in SQL Analyser ... it works. Any ideas ? Here's my stored proc ... I tried passing a varchar and doing the conversion in the SP ... no go !! I am using MApplicationBlockD.


CREATE PROCEDURE spScanUpdate
@id varchar (100),
@name varchar (75)
AS
DECLARE @GUID_ID as uniqueidentifier
SELECT@GUID_ID = CAST ( @id as uniqueidentifier )

UPDATE tScan
SET name = @name
WHEREid = @GUID_ID
GO

View 5 Replies View Related

GUID Vs Integer

Apr 17, 2006

I use MS SQL Server 2005...Is there a structural advantage/disadvantage with using GUID as oposed to an integer?(also I use the sqltableprofileprovider and it doesnt seem to work with uniqueidentifiers)

View 6 Replies View Related

A GUID Question

Sep 26, 2007

does SQL Server have the equivalent of the Identity or Scope_Identity functions that can be used to return the last GUID PK?

View 5 Replies View Related

GUID Performance

Apr 11, 2008

I have a database with tables with GUID's als primary keys.

Because of the uniqueness GUID's are a performance hit on INSERT records.

So I wanted to use NEWSQUENTIALID() to make INSERTS faster.

Does NEWSQUENTIALID() increase performance as well with SELECT statements? Or is the benefit between NEWID() and NEWSQUENTIALID() only when INSERTing records?



Henri
~~~~
There's no place like 127.0.0.1

View 3 Replies View Related

Loving GUID's

Apr 29, 2008

I'm reluctant to admit I'm in love with uniqueidentifiers AKA guid's.

However, this love comes with a cost because of the size and thus SQL Cache and thus IO reads (and fragmented index pages and so on)

My love has to do with generic programming and keeping multiple database updateable (adding core functionality with the flexibility to let users add and change data in tables that have core data in it as well). Also merging databases is easy or relate different entities by storing a metadata link table.

So, what I'm trying to do is making the disadvantages as small as possible.

So I'm using NEWSEQUENTIAL Id's and retrieve new made records with OUTPUT as TABLE.

So I changed the default CLUSTERED INDEX as NON-CLUSTERED.

I'm not fond on usings numerics for some primary keys and uniqueidentifiers for others.

What else can I do to keep using GUID's as primary keys with as less disadvantages as possible?

Henri
~~~~
There's no place like 127.0.0.1

View 13 Replies View Related

GUID As Primary Key

Mar 13, 2008

I have a table profiles and a table resumes.
The id's are currently int indentity fields.
I was thinking of using a GUID as ID.

Why would i do this ?

In my webapplication I have i.e. this page
DeleteProfile.aspx?profileId=1

I don't want to give the user the ability to change this to f.e.
DeleteProfile.aspx?profileId=3

A user will almost possibly never guess a certain GUID, right ?

Is this a good idea, or not ?

View 6 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved