|
I created a set of tables in which the primary keys are set to uniqueidentifiers with a default value of newid(), which is the function that generate new guids in Transact-SQL. While I can create a uniqueidentifier variable and initialize it before inserting a row, that would obviate the reason for creating a default value in the first place.
But unlike an identity field, there is no function, that I can think of that would retrieve the most recently created uniqueidentifier. I would like to use this value within the body of a stored procedure but I can't think of any way to get it short of creating it ahead of time and not using the default value mechanism.
I could access the new value in a trigger, but then how would I pass that back to the stored procedure? I don't think that a trigger is the way to do this, but I don't know.
Can anyone show me a way to do this?
|
|
|
|
|
I hate GIUDs
I do not use the default value, instead I create the GUID (in the stored proc) before inserting and therefore already have it for the return select or an out parameter.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I am rather fond of GUIDs.
They are so much better than lowly ints. I can create a hierarchy of various rows and insert them all in one swell foop, rather than doing one, getting its ID, passing that to the next... 
I do set the default value, as you did, but that merely gives me flexibility -- in some cases I may not need to know the ID, in others I may; so I can either set it myself or let it default, depending on need. If you don't want to use the default value, don't set it, big fat hairy deal.
Autoincrement fields are a nasty kludge that never should have been implemented -- as I recall, in Oracle we used "sequences" which allowed us to get the "next value" much like we can get a new GUID. When the company I worked for started to shift from Oracle to SQL Server, we implemented our own sequences, we never used autoincrement fields -- I still never have.
|
|
|
|
|
So, I have one person that hates GUIDS and one that likes them.
I chose them for the absolute certainty that there would be no collisions in IDs between tables and / or systems. My main goal was that I wanted to uniquely identify any object in my application. I won't have that many objects that I could not have used integers, but with integers to do that amoung many tables I would have to devise some sort of function that always delivered a new unique one when creating a row in any table.
If I could build a suitable function I still might do that. I probably could do so by using a single row table with one tuple holding the last integer used, but I have not tried that yet.
One thing I don't understand is why Microsoft created unsigned integers in all its programing languages, but did not provide the same feature in SQL server. I really do not want to use negative values, but the fact that there is no unsigned integer cuts my value pool in half if I choose to start with zero.
I also had some Oracle experience and I think I remember the sequence object. I don't know why Microsoft didn't implement something similar. For my part I would like to see sequences that deliver either unique unsigned integers or unique unsigned doubles. That would handle virtually any system that I can think of as a means of generating unique keys
I have been taught that SQL Server is much faster when dealing with integer keys because it uses integer arithmatic in its underlying calculations. Is this correct? And if so, how much faster would it be?
For now, I have chosen to create the IDs before inserting the rows so I already have them for other purposes. I have tables that serve as links for many to many relationships and that is where I am using the IDs.
My next challenge is to figure out how to maintain data for directed acyclic graphs, the idea of being able to show the relationships between any object in the system to any other object in the system even if that realtionship is a remote relationship like a grandparent of a cousin object, etc. I have found one article on Code project regarding that task, but I am looking for more to see if there are other ways to do this and so that I can understand the process better.
|
|
|
|
|
henry1951 wrote: If I could build a suitable function I still might do that. I probably could do so by using a single row table with one tuple holding the last integer used, but I have not tried that yet.
I think what we* did was create a multi-row table that supported multiple "sequences" -- the app could request the next value for the User sequence, or the Transaction sequence, etc. It also had fields for Min and Max -- if the sequence reached the Max it would wrap around to the Min (I assume that there was a need for that in one of the apps).
* Not that I was involved in that effort at the early stages. Wish I had been -- the idiots put data access in the UI layer  .
henry1951 wrote: unsigned integers
Yeah.
henry1951 wrote: faster when dealing with integer
Well, they're smaller, quicker to compare. But I don't consider that to be a reason to select them over GUIDs -- just don't use strings as keys .
|
|
|
|
|
henry1951 wrote: I chose them for the absolute certainty that there would be no collisions in IDs
Actually it's not an absolute certainty. It's just that the chance of a collision is extremely small.
Be careful using GUIDs as PKs. Significant performance issues can arise (both time and space are wasted) when the clustering index is a GUID and the default in SQLServer is to create a clustering index on the PK. You can get around this by using the newsequentialid()function or by generating your own sequential GUIDs. See this article[^]for a discussion of the issue.
|
|
|
|
|
PIEBALDconsult wrote: in Oracle we used "sequences"
And the practical difference between a ident and a sequence is?
I hate guids b/c I'm lazy, have you ever tried to remember a guid, when monkeying with the data I spend a lot of time chasing down problems in the data, as I;m the developer the numbers are generally nice and small, I can manage to remember 1 or even 2 but a guid, not a chance. I got sick of ctrl c ctrl v every time I want to find a record.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Mycroft Holmes wrote: And the practical difference between a ident and a sequence is?
Sequences aren't tied to a particular table. You can get a value from a sequence without adding a record.
I suppose one could implement a sequence using a table with only an autoincrement column -- and perhaps a trigger to delete the otherwise needless rows. 
Mycroft Holmes wrote: I spend a lot of time chasing down problems in the data
I don't. And I don't write bugs either.
Eventually, your numbers may be high enough that you can't remember them* -- GUIDs simply get you there immediately, that's one of the benefits. Or you could cobble up your own GUIDs during development and testing:
System.Guid g = new System.Guid ( 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 1 ) ;
You could even wrap that around a sequence. You get the best of both worlds.
* For me that's anything with more than three digits.
|
|
|
|
|
PIEBALDconsult wrote: Sequences aren't tied to a particular table. You can get a value from a sequence without adding a record
Interesting, I automatically created a sequence for each table, habit from SQL Server I guess.
90% of my work seems to dragging data from crappily designed legacy systems (not sure if it is the design or the interface) and trying to make sense of the data, reverse engineering someone elses disaster  .
There rare times I do get a transacional app to build I treasure immensly as these are areas that are new and fresh to me.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I have a ASP.NET website it is hosted on IIS 5 and it works fine' I can browse from IIS console itself
but when I open the same in another client machine, I see the page but no database
it says
[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.
I checked the named pipes and tcp/ip on sql server and all the permissions are enabled'
I cant find why it shows up on the server but not on the client
plz hlp
_____________________________________________________
Yea! I could be wrong...
|
|
|
|
|
can you provide code.., how you are conneting to the database..,
Rajesh B --> A Simple Programmer <--
|
|
|
|
|
I do not have the code for this application
it uses an XML file where i have to give the connection details like
<Connection Name="Provider" Value="1" />
<Connection Name="Server" Value="server" />
<Connection Name="Database" Value="db" />
<Connection Name="UserID" Value="user" />
<Connection Name="Password" Value="pwd" />
I do not know how the application uses this
is it a connection problem then so it should not work on the server I think...
_____________________________________________________
Yea! I could be wrong...
|
|
|
|
|
saud_a_k wrote: <Connection Name="UserID" Value="user" />
<Connection Name="Password" Value="pwd" />
I'm not sure whether the sa-account is enabled by default, and it might even be that 'mixed mode authentication' is disabled.
Create a new console-application, somewhat similar to this;
using System;
using System.Data.SqlClient;
static class SqlTester
{
public static int Main (string[] args)
{
using (SqlConnection con = new SqlConnection(args[0])
{
try
{
con.Open ();
Console.WriteLine ("Connected succesfull, your ConnectionString is correct :)");
}
catch (Exception ex)
{
Console.WriteLine ("Something went wrong:\n" + ex.ToString ());
}
}
}
}
That would give you a small console app that you can use to verify the connection-details.
In this specific case, check whether the customers server have;
- an account with given UserID (should be in the same place as the 'sa')
- allowed mixed mode access (not just Windows-authentication)
- whether the servers' name is correct (express versions are called different than full servers, and they might even be using a named instance)
Good luck 
I are Troll 
|
|
|
|
|
Eddy Vluggen wrote: an account with given UserID (should be in the same place as the 'sa')
allowed mixed mode access (not just Windows-authentication)
whether the servers' name is correct (express versions are called different than full servers, and they might even be using a named instance)
I checked all these options but I still get the same error
I also gave the location of another server but still the page shows up on the server but not on the client
_____________________________________________________
Yea! I could be wrong...
|
|
|
|
|
saud_a_k wrote: I checked all these options but I still get the same error
Try the connectionstring below, with the console-application from the previous post;
Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;
If that fails to connect, you'd at least have a more decent error-message to hunt for.
saud_a_k wrote: I also gave the location of another server but still the page shows up on the server but not on the client
If the above method also fails, chances are that there's a connectivity-problem. That means checking if you can 'ping' the clients' server, validating the firewalls and so on. If it's not a connectivity-problem, (security-related problems) then the console-app would generate an error-message indicating where the problem is.
I are Troll
|
|
|
|
|
I'm assuming that the IIS is hosted on the same server as the SQL db? If so, it's making a local connection whereas clients are connecting over the network - based on this make sure that:
1) you have SQL configured to allow the type of connection you are trying to use from the client (IP, pipes, etc.)
2) you have the firewall configured to allow SQL traffic (I've had this problem in the past where the MS firewall didn't automatically add a rule for SQL)
Those are my initial thoughts.
|
|
|
|
|
Hey guys,
I have a SQL Server database in which I want to store history data for a couple of object. However, I don't want the table to keep 'old' data. Therefore I want to have a maximum amount of x (in my case 100) records. Since I have 5 objects available the max numer of records in my table cannot be larger than 500.
The way I accomplish this, is to insert records using a stored procedure. The stored procedure checks the amount of records available for that object. If the amount is larger than 99 it removes (recordamount - 99) records. Then the stored procedure inserts the new value.
Is there a neater way to accomplish this?
Thanks a lot!
Eduard
|
|
|
|
|
Delete from mytable
where ID not in (select top 100 from mytable order bt ID desc)
This works fine and with the minute numbers you are working with it will be Ok, with serious numbers I would have a cleanup job that runs periodically
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I like the idea of a cleanup job that runs periodically. 
|
|
|
|
|
That's more like the reply I was looking for. I didn't know if the sp would perform if the cleanup is integrated. A job it is, thanks! 
|
|
|
|
|
The only problem with a periodic scheduled job is that you may temporarily find yourself with more rows in the table than you are supposed to have.
A The scheduled job runs and leaves 100 rows in the table
B I insert another row: there are now 101 rows in the table
C At some later point, the job runs again and leaves 100 rows in the table
In between points B and C, you have 101 rows in the table. That may not matter but it may be important. Even if the job is running frequently, you cannot guarantee that a query won't "see" the table between B and C and produce a spurious result.
So, depending on the requirement a scheduled job may be OK if you don't need to guarantee that the row count limit will always be enforced, otherwise a stored proc or a trigger may be the best way to go (although I am not a fan of triggers in general).
|
|
|
|
|
This would be a good example of when to *not* use a trigger. Likely your trigger code is going to select on the very table table that fired the trigger in the first place. Ensuring that this doesn't open the door for unlimited recursion could become difficult. If there is a high water mark of how many rows are allowed, making use of a procedure to enforce that is your best bet. I've seen circular logs where the procedure will update existing records, instead of deleting and inserting something new.
Chris Meech
I am Canadian. [heard in a local bar]
In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]
|
|
|
|
|
Well, if your ON INSERT Trigger only deletes records - you aren't going to get a circularity problem.
|
|
|
|
|
Thanks David!
I don't need to guarantee a max number of records.
This[^] post explains what I want my software to do
|
|
|
|
|
Thanks for the reply, but my question was not how to accomplish the removal but wether my solution using a stored procedure was OK.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
