Best Practice for retrieving last record

Hi there, very sorry if this is the wrong forum to post this in.

I want to know what is the BEST practice, the correct Microsoft way of doing this:

basically, lets say I am inserting a new record into SQL. simple customer record:

firstname

lastname

address

city

postcode

password

dateOfRegistration (SQL has this value and the default value is the getdate())

That's all very well. I want to know how I can get the recordID for this and return that back from the caller (returning is easily done) -

You cannot really after this insertion, perform a SELECT statement to get the LAST record entered, as there maybe several records that could all be inserted at the same time by coincidence. It's not the best way of going about this.

I want to know what is the best way of getting the just inserted record's recordID - I was thinking about using date and time, manually inputting them and then using that to retrieve the last record/current inserting record but again its not the best way of going about doing this.

what is the best way?

Many thanks for your help!

Hi,

If your recordID is an Identity column of your table, you could use the SCOPE_IDENTITY function.

INSERT INTO Customer (Fname,Lname,Address,City,PostCode,PWD,DOfReg)

VALUES ('John','Smith','123 drwerwr','Montreal','X1X 1X1''***','2006-03-31')

SELECT SCOPE_IDENTITY()

This will retreive the last RecordId inserted

|||

cool - had no idea such a thing existed but then again i am still learning the great SQL Server

so please tell me technically, how does the SCOPE_IDENTITY() work? in technical terms - what does it do in process?

|||

From BOL

SCOPE_IDENTITY and @.@.IDENTITY return the last identity values that are generated in any table in the current session. However, SCOPE_IDENTITY returns values inserted only within the current scope; @.@.IDENTITY is not limited to a specific scope.

HTH,

Eric

|||you can add a timestamp field to your table then order by it desc and select the top 1|||

Don't know about using a time stamp ...

The timestamp value would change on update as well.. and in a multi-user application you can't garantee that the last record inserted is yours if you use a timestamp.

|||Hi,

if you are using SQL Server 2005 you can use the new keyword OUTPUT within the insert command. By the new keyword you can directly get a value back which can represent the identity or whatever column:

Look for the output clause in the BOL: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/41b9962c-0c71-4227-80a0-08fdc19f5fe4.htm

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de
|||

I am using SQL Server 2000 but also interested in the cool stuff of SQL Server 2005 (on the side)

Currently using the SCOPE_IDENTITY() in SQL Server 2000

the suggestion about sorting the results in ASC order and select the last one - well, it may be logical to do so but too much effort in terms of performance and ineffeciency :-)

any other best way? :-)

|||

Using identity values for PRIMARY KEY values is fine, and a practice I use all of the time. But, it should not be your only unique criteria on the table.

For example, consider your table:

firstname, lastname, address, city, postcode, password, dateOfRegistration

What if two rows were inserted with the same firstname, lastname, address, city, postcode, password, dateOfRegistration. Most likely this would be in error right? Well if haven't added a UNIQUE constraint to the data, what happens when you look them up? How will you as a human tell them apart, much less the stupid, just do what you programmed it to computer.

So at the very least add a UNIQUE constraint on all of these columns.

Then, if you don't have SCOPE_IDENTITY() (An instead of trigger will invalidate SCOPE_IDENTITY() if you ever need one, for example) you just just say:

select rowId
from tablename
where firstname = @.firstname
and lastname = @.lastname

etc. Don't give up all control of the data/situation to SQL Server, though use the tools like scope_identity to make life easier. :)

|||

Many thanks Louis i appreciate this

Well the unique check would be the address in my case which the stored procedure checks to see if it exists, if it does it returns a value (like -1 or whatever) otherwise it continues and returns the ID of the record which is inserting.

Thank-you!

|||If you had the unique constraint there it would probably make this operation faster too.|||

awesome!!!!!

Performance is the key baby, LOVE SQL

thank-you Louis :-D

Best practice for Add, Edit records into database with lots of fields ?

What's the best practice for adding / editing a record into a database with lots of fields ?
I am not talking about the mechanics of it, as there are a lot of trivial examples using ADO.NET, stored procs, etc.

Deleting is easy, you just pass in (a few) primary key/keys to uniquely identify the record.

But in the real world when you have, say, a table with 100 fields! Do you code the INSERT sproc by hand, with 100 parameters... then call it with your ADO.NET code ? sounds like a lot of work to me...

What about updating! That's even worst, sometimes you may need to update only 3 or 4 fields, but using sprocs you would have to pass the whole 100 parameters in again, and "update" the whole record (when in fact you are only changing 3 or 4 fields).

With the update i could write different sprocs targeting only the fields i wish to update, but that sounds like duplicating work, vs having one generic update proc.

Sometimes i just feel like bypassing sprocs and having inline sql as it would be less work... but i know it is untidy.. and more potential to be buggy.

So come on guys (and gals)... let's hear your thoughts on how you would handle the insert / update scenarios when you have lots of fields ? Northwind examples are too trivial :-)

Hmm... more than one week and no comments, better file this one in the too hard basket... guess no one is willing to comment, or is the subject taboo? Where are all the certified professionals and / or other opinionated people... :-)

Or it could be there are no alternatives.

Best method: TOP 1 or DISTINCT or MAX

'TOP 1' or 'DISTINCT' or 'MAX'
Any sugestions on which is better to use if I need to select a record that has the highest value - could be a INT or sometimes a DATETIME.Distinct will not get you a max value, if you use top make sure you use the order by.

HTH|||To select the entire record, use TOP 1 on a sorted recordset. To get just the highest value for the field, use MAX().|||[blindman]: someone else suggested that TOP is more efficient then MAX is that true?|||I don't know. It probably depends on a lot of factors and makes little difference either way.|||Best way to test this is to use the "set stistics IO on" command to check your logical IO (number of times you hit a page)|||Originally posted by rhigdon
Best way to test this is to use the "set stistics IO on" command to check your logical IO (number of times you hit a page)

I used "SET STATISTICS IO ON" command and got a line for each table in query...

Table 'tblUser'. Scan count 1, logical reads 2, physical reads 2, read-ahead reads 0.
Table 'ctsJrn_Location'. Scan count 7, logical reads 14, physical reads 2, read-ahead reads 0.
Table 'ctsIndex'. Scan count 8, logical reads 16, physical reads 0, read-ahead reads 0.

Can anyone tell me what does each count of 'reads' mean?

Thanks,
Lito|||Scan count - number of times data or clustered index pages were scanned;
Logical reads - total number of records read from cache (I think);
Physical reads - total number of pages read from disk (I think);
Read-ahead reads - number of pages optimizer chose to read ahead (I think)

But the point is, you want to minimize the first 2 indicators. And, BTW, scan count does not always mean that the actual scan occurred. It just means that the optimizer had to look at data/clustered index pages of the corresponding table so many times.|||Originally posted by rdjabarov
... But the point is, you want to minimize the first 2 indicators...

What range should those indicators be in, are mine ok?|||It depends on number of rows the tables have vs. number of rows returned.|||but is there a ratio?

I am selecting one row from 9 joint tables with approx. 18k records each|||Then your numbers are actually good.|||The only counter I truly look at is logical IO as it is the number of times a page is hit (not number of pages) the lower you canb get this the better. The problem with physical and read-aheads is they can be optimistic and not exactly accurate.

HTH|||your physical reads should be zero or as close to zero as possible.
this means that you are reading pages from disk into memory.. that is something that you want as little of as possible.

you will want logical reads to be as low as possible as well but those numbers are based on the actual work that SQLSVR had to perform to retrieve your query. so the number is academic based on your query, statistics, indexing etc.

typically you should only retrive the rows that you need in a query result, so if the question is which would be the best query to perform? so if you want to just get one row the logical answer would be an aggregate function

Select Max(Col1) as 'MAXNUM' from table2
this will retrieve a scalar value for you (one row one column)
ex
MAXNUM
=====
100

as far as distinct and top, are concerned
DISTINCT does not give you a max value, it removes duplicates from the columns gueried which i guess you could then sort decending to get the largest value
""select distinct state from table2 order by state Desc""
ex
STATE
====
TX
GA
FL
CA

TOP 'n' is designed to return an restricted set of values
""select TOP 5 col1 from table2 order by col1 desc""

COL1
====
5
4
3
2
1

your best method here would be to run the query with each of the different types of commands
view the stats io and compare all three.|||Thank you all for your comments and sugestions, this helped me alot. Learn something new every day...

Lito

Before Update/Delete Trigger

Is there a way to create a trigger that will keep a user from updating or deleting a record? Thanks, JeremyRead about INSTEAD OF triggers in BOL|||Hi JCScoobyRS,

Originally posted by JCScoobyRS
Is there a way to create a trigger that will keep a user from updating or deleting a record? Thanks, Jeremy

h, why don't you revoke the user the UPDATE and DELETE permission?|||Very good idea BUT I'm trying to help a buddy out that needed the ability described in my first post. Is there a way? I'll check with him to see if that will work but I wouldn't mind an answer anyways. Thanks for your help, Jeremy|||Originally posted by JCScoobyRS
Very good idea BUT I'm trying to help a buddy out that needed the ability described in my first post. Is there a way? I'll check with him to see if that will work but I wouldn't mind an answer anyways. Thanks for your help, Jeremy

In this case you should go for INSTEAD OF triggers|||Okay...that sounds good. Here is an example of what I need to do:

I'm trying to prevent the UPDATE and DELETE on a table after a certain field has been entered(not null). Here is the trigger right now:

CREATE TRIGGER TRANSACTION_REPORTEE ON [dbo].[TRAVAUX_COMMANDE]
FOR UPDATE, DELETE
AS
IF [dbo].[TRAVAUX_COMMANDE].[Id_Transaction_GL] IS NOT NULL
BEGIN
RAISERROR ('Impossible de modifier une ligne reporte',10,1)
ROLLBACK TRAN
END

This is what my buddy has. Is there anyway to take what he has here and revise it with your idea in it for testing? Thanks alot, Jeremy

Before Delete

I have 2 databases "Law","Rules" .. the second have tables which is linked
to the first one... so i want to deny Deleting of Record from first if it
has a child record in the other database...
I notice that there is no "Before Delete" trigger in sql server so how could
i control deleteing records from first database..
Second.. how could i roll-back Delete or update operation?
Did you consider a Foreign key Constraint for that ? If it is not
applicable you can do a ROLLBACK within a trigger and raise an error to
show up the error to the user.
http://groups.google.de/group/micros...18307e92ac868c
HTH, Jens Suessmeyer.
|||Instead of trying ot use a trigger, how about applying a foreign key
constraint instead? Then when you try to delete a row from the first
table you'll get an error if there's a dependent row in the second
table. It'll also be much faster than using a trigger.
On Sat, 8 Oct 2005 16:16:51 +0200, "Islamegy" <Islamegy@.Private.4me>
wrote:

>I have 2 databases "Law","Rules" .. the second have tables which is linked
>to the first one... so i want to deny Deleting of Record from first if it
>has a child record in the other database...
>I notice that there is no "Before Delete" trigger in sql server so how could
>i control deleteing records from first database..
>Second.. how could i roll-back Delete or update operation?
>
|||hi,
bradsbulkmail@.comcast.net wrote:[vbcol=seagreen]
> Instead of trying ot use a trigger, how about applying a foreign key
> constraint instead? Then when you try to delete a row from the first
> table you'll get an error if there's a dependent row in the second
> table. It'll also be much faster than using a trigger.
> On Sat, 8 Oct 2005 16:16:51 +0200, "Islamegy" <Islamegy@.Private.4me>
> wrote:
have you tried something like
SET NOCOUNT ON
CREATE DATABASE a
CREATE DATABASE b
GO
USE a
CREATE TABLE dbo.m (
Id int NOT NULL PRIMARY KEY ,
Descr varchar (10) NOT NULL
)
GO
USE b
GO
CREATE TABLE dbo.d (
ID int NOT NULL PRIMARY KEY ,
IdRif int NOT NULL
CONSTRAINT fk_d_m FOREIGN KEY
REFERENCES a.dbo.m (Id) ,
Descr varchar (10) NOT NULL
)
GO
USE master
GO
DROP DATABASE a
DROP DATABASE b
?
the actual result is
Server: Msg 1763, Level 16, State 1, Line 1
Cross-database foreign key references are not supported. Foreign key
'a.dbo.m'.
Server: Msg 1750, Level 16, State 1, Line 1
Could not create constraint. See previous errors.
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply