OK - maybe my Google-fu is off today but I'm trying to find out if using a sequence table will provide values that are as unique as the GUID is supposed to be.The reason is that I'm building a DW from the ground up (they won't let me expense my booze which I think is grossly unfair ) and rather than using GUID it ~seems~ like a sequence table would give better results. My concern is that the values won't be unique across tables.
Does anyone have a script that can add a GUID Column to multiple Tables in a Database.
For example when a table has an Identity column like EmployeeID, a column called EmployeeGUID is added to the table. When a table has an Identity column like CustomerID, a column called CustomerGUID is added to the table. When a table has an Identity column like OrderID, a column called OrderGUID is added to the table.
I intend to implement replication in SQL Server 2005, so i want to add new GUID columns to all the table in my database before implementing replication
The SQL script below adds a column to a single table in a database. But i need a script that can add a column to all the tables in a database.
Thanks
USE ProductDB
ALTER TABLE dbo.Employees Add EmployeeGUID UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL DEFAULT NEWSEQUENTIALID()
Hi, I am migrating a project from Oracle to SQLServer and must use the same DDL. The entities have compound primary keys. In Oracle I ensure the compound key is unique by using a sequence table to generate one of the values for the compound key. Does SQL Server have sequence tabels or does anybody know a way to get it do something similar? Thanks
I'm new to sql server 2005. Previously I used Access. I've noticed that in sql server when a record is inserted into the database table, the data does not get entered sequentially. I looked at the id column and saw that the order numbers are mixed up. Is this something that is common in sql server, and how can I change this. I need the data to be ordered in the order that it was inserted into the database.
I have a procedure which updates a sequence number in a table such as the one below.
Seq Sequence_Id
------ ------------------ NextNum 1
This is the procedure ...
create procedure DBO.MIG_SYS_NEXTVAL(@sequence varchar(10), @sequence_id int) as begin
update mig_sys_sequences set @sequence_id = sequence_id = sequence_id + 1 where seq = 'CSN'
return(@sequence_id) end
The purpose of this is to generate a sequential number each time the procedure is called. This number would then be used in a number of different tables to allocate a unique id so that the id is unique across the different tables.
1). What is the most efficient way of allocating these unique ids? The tables that I plan to update will already be populated with data.
2). How would I call the above procedure from an UPDATE statement?
Hi all, In my project i will have the data in a collection of objects, I need to update series of tables with foreign key relations Right now my code looks like this foreach(object obj in Objects){ int accountId=Account.Insert(obj.accountOpenDate,obj.accountName);//this will update the accounts table and returns account id which is a Identity column in Acccounts table int DebtId=Debt.Insert(accountd,obj.debtamount,obj.debtbalance); this will update the Debts table and returns DebtId ///series of tables like above but all the relevant data comes from obj and in the Insert Methods i am using stored procedures to Insert the data into table } The no of objects varies from 1000 to 1 milliion,, with this approach its taking more time to update all the data. Please let me know if any alternative procedure to handle this kind of scenario.
When I select a fieldname in a db as a primary key this will keep the uniqueness of that fieldname, but how is that work when I select 2 fieldnames (in Enterprise Manager) and click on the Key it will mark them as my primary key, but I can insert duplicates values in either fieldname.
this violates the constraint if i give the two teams on the same date..how to give the condition such that it checks for the violation of condition if the team names are interchanged.. for eg
if i give the values as Insert Into Games values('chelsea',1,'arsenal',2,'17-1-2000'); and Insert Into Games values('arsenal',1,'chelsea',2,'17-1-2000');
the above should violate the constraint ..how to do it
I've been working on an SSIS package trying to load some data and the archive sequence is faulty. I've been trying to load a few tables created in a previous sequence into a local archive file and I've been getting the error "Could not find a part of the path."
The results aren't telling me what it's finding last and so I don't know where to start.
And the source DOES have data in it. It's something between the source and the destination.
My company is upgrading one of our products; this involves some data migration and a question about existing indexes with uniqueness constraints has been raised. Here is the issue:
We have a database table with two particular columns that that we index with a uniqueness constraint. My question is: is the uniqueness constraint case sensitive?
For instance, if I have a database that is using a collation of SQL_Latin1_General_CP1_CS_AS. (case sensitive) and I insert data in these two columns as follows:
test col2test
Test col2test
would this uniqueness constraint be violated even though I'm using a case sensitive collation? I've done tests on this issue and I can't seem to insert this 2nd column; I'm just wanting to be certain that this is the case 100% of the time.
Hi, Can anyone tell me the order in which uniqueness constraints on indexes are enforced vs. when triggers are executed ? I have a unique constraint on an index and a trigger on the column on which the same index has been created. When a row is inserted, the trigger checks if the value for that column already exists in the table - if not, it inserts the row as is, else it gets the max() val of the column (based on another key column) and increments it by one, then does the insert. Creating an index across the two works fine, but if I set the Unique Values property for the index, subsequent inserts bomb out - yet there aren't any duplicates in the final table, as the trigger ensures this. Anyone got any ideas on this? My deduction is that the uniqueness constraint gets enforced before the trigger gets executed, but at the same time this *seems* illogical, as the row has not been inserted into the table at the point where the trigger is executed.
Hi,First of all my apologies if you have seen this mail already but I amre-sending as there were some initial problems.This query is related to defining indexes to be unique or not andconsequences thereof.Some documented facts that I am aware of include1. Defining uniqueness allows optimiser to create optimal plans egselect based on keys in such an index allows the optimiser to determineat most only one row will be returned2. Defining uniqueness ensures that rule (business/Primary key) isenforced, regradless of how the data is entered.We have many cases where non unique indexes are defined. The approach todate has been that even though we are aware of some of the benefitsoffered by defining uniqueness , we have chosen not to add keys to nonunique indexes such that they become unique. The primary reason for thiswas that we did not want to make the keys comprising the indexesunnecessarily large and therefore ensuing consequences when DMLstatements are performed.However, I have concerns that having highly duplicate indexes can haveperformance impacts , including deadlocking. I am also aware Sybase usedto store duplicate values in overflow pages and therefore there wereperformance consequences. Could SQL 2000 have the same behaviour ?Thanking you in advancePuvendran*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
So, I have this idea of a database design I was hoping to get some input on. I usually go through the process of creating a separate column for identity, like an auto-increment integer which serves as a context specific ID that easily can be passed around. However one of the short-comings I've come to realize is that the integer is just that, unique to the context and requires context information to make sense. I'm pounding the idea of creating a separate table whose sole purpose is to provide global (database wide) unique IDs. As such each row in a table can still have an ID but instead of being tied to a specific context it will always make sense within the application. A foreign key constraint can ensure that and a relation. Now, with a global ID which I can create FKs to, I don't have to create a separate table for relations between entities and can have relations through these global IDs and I think that's a good thing because if there are many relations between many entities they don't have to be defined more than once. There is also a single "hub" where all relations fit which will allow me to access most information through some relatively simple joins. My concerns are performance and Linq to SQL implication. I'm worried that Linq to SQL might start pulling an excessive amount of data and I'm worried that the practicality of such a design might hurt the long running of the application development process. But I'm surprisingly interested in what it could mean for a more ubiquitous view of data and tagging. Basically a way of any data to have relations to any other data through this yet simple design decision. If you think this is a good idea or see no particular problem with, please let me know! If you think there's problems with my idea, please motivate your critic but do tell me what I should look out for, or if you know of any better approach to my idea of being able to look at the data through some more common mechanism (which applies to all tables and the entire database mostly from a programming perspective).
I want to make sure that usernames and folder names (that are stored in the database) are all unique when created by the admin through an admin control panel.
How would I code and use an Index to make sure that the 2 columns are unique?
I've looked at examples on the net but nothing is telling me how I can create an index through Enterprise Manager? Thanks
I want to make sure that usernames and folder names (that are stored in the database) are all unique when created by the admin through an admin control panel.
How would I code and use an Index to make sure that the 2 columns are unique?
I've looked at examples on the net but nothing is telling me how I can create an index through Enterprise Manager? Thanks :)
I"m trying to use a BULK INSERT command to insert data into a table from a file. There is a UNIQUE Index that is being violated and the BULK INSERT fails.
I do not want to drop or disable the index, however, i also do not want to load 'duplicate' records so i keep the CHECK_CONSTRAINTS parameter.
Is there a way to have the duplicate records outputed to the ERRORFILE ?
Is there a system table column to query to determine whether an index is defined as unique in SQL Server 2000? It is easy to find this info in SQL Server 2005, but I don't see a valid column for this on dbo.sysindexes.
All- I have what i'm sure is a question with a simple answer: Supposing I want to modify the select command below to force the display of records with only unique combinations of the two fields person_id and act_session_id (bolded). How would I do this. Note that neither is a key field. (Key field headcount_id was added for completeness.) Thanks! SELECT hd.headcount_id, hd.person_id, hd.act_session_idFROM headcount as hd
I am reading "SQL Server Query Performance Tuning Distilled",on page 104 it talks about one of the index design recommendationswhich is to choose the column that has very high selectivity of valuesinstead of a column that has very few selectivity of values.My question is if I have currently indexes on my tables that have1, 2, 3, 4, ... values only on thousands of rows, are these nonclusteredindexes pretty much useless indexes that I should get rid of?And I know that pretty much the number of selectivity values willalways remain very low.Thank you
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
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?
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]
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.
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!
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
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
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)
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?
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?
Hi,Is there any replace for "Select @@identity" that could return "justinserted" GUID as a primary key?Has anyone tested what's faster, working with Guid or Autonumber ?
