Click here to Skip to main content
15,077,377 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hello Everyone,

I am having a doubt and need your opinion on that.

I am having two tables

1) TenantDetails

SQL
CREATE TABLE [dbo].[TenantDetails](
    [TenantId] [int] IDENTITY(1,1) NOT NULL,
    [FirstName] [varchar](20) NULL,
    [LastName] [varchar](20) NULL,
    [DOB] [date] NULL,
    [Occupation] [varchar](20) NULL,
    [Organisation] [varchar](50) NULL,
    [Address] [varchar](50) NULL,
    [City] [varchar](20) NULL,
    [State] [varchar](20) NULL,
    [ountry] [varchar](20) NULL,
    [PhoneNo] [varchar](13) NULL,
    [MobileNo] [varchar](13) NULL,
    [IDType] [varchar](30) NULL,
    [IDNumber] [varchar](20) NULL,
    [TenantStatus] [bit] NOT NULL,
    [CreatedOn] [datetime] NOT NULL,
    [CreatedBy] [varchar](20) NOT NULL,
    [ModifiedOn] [datetime] NULL,
    [ModifiedBy] [varchar](20) NULL,
PRIMARY KEY CLUSTERED
(
    [TenantId] ASC
)
)


2) TenentRoomMapping


SQL
CREATE TABLE [dbo].[TenantRoomMapping](
    [TenantId] [int] NOT NULL,
    [RoomId] [int] NOT NULL,
    [CheckinDate] [date] NOT NULL,
    [CheckOutDate] [date] NULL,
    [Status] [bit] NOT NULL,
    [SecurityDeposity] [int] NOT NULL,
    [Remarks] [varchar](max) NULL,
    [CreatedOn] [datetime] NOT NULL,
    [CreatedBy] [varchar](20) NOT NULL,
PRIMARY KEY CLUSTERED
(
    [TenantId] ASC,
    [RoomId] ASC,
    [Status] ASC
)
)
ALTER TABLE [dbo].[TenantRoomMapping]  WITH CHECK ADD FOREIGN KEY([RoomId])
REFERENCES [dbo].[Rooms] ([RoomID])


ALTER TABLE [dbo].[TenantRoomMapping]  WITH CHECK ADD FOREIGN KEY([TenantId])
REFERENCES [dbo].[TenantDetails] ([TenantId])



And My requirement is -

when I add a new tenant's details in TenantDetails, I will assign a room to that tenant.

In this case I am using two SPs.

1) Usp_AddTenant (this Sp is inserting record in tenant details and returning the id of last added record (TenantId, which is auto generated and primary key))

2) Usp_AssignRoom( this is inserting record in RoomId(passed from front end), TenantId(returned from usp_Addtenant), and other details)

Code and functionality wise everything working fine.

i doubt is in SP "Usp_AddTenant"

it looks like...

create proc Usp_AddTenant
(
inputparameters,
@out int output
)
as
begin

insert statement ....

set @out= (select Max(TenantId) from TenantDetails(nolock) where Createdby= @createdby)

end



this application will run on intranet and multiple users will be there.. so my question is
which way should use for getting id of last inserted record in tenant details

set @out= (select Max(TenantId) from TenantDetails(nolock) where Createdby= @createdby)

note : createdby is the userid of user logged in
or

set @out= (SELECT SCOPE_IDENTITY())

or

set @out= SELECT IDENT_CURRENT('TenantDetails')

I have doubt that there should not be mixing of tenant id in case when two users are working on same page from diff. system.

I hope I have provided all the required info. and if still you want any other info do let me know...

Thanks in advance....
Posted
Updated 24-Jul-13 0:47am
v3

The best way is to use @@IDENTITY[^]

Please, read this article: SCOPE_IDENTITY (T-SQL)[^] to understand the difference between @@IDENTITY and SCOPE_IDENTITY.
   
v2
Comments
Adarsh chauhan 24-Jul-13 7:06am
   
Thanks Maciej ...

I already gone through this link.. but my question is a bit different...

I mean there are two users let's say A and B
both are using same page...
both will insert records... at the same time SP usp_AddTenant will be called...
so if i use @@IDENTITY or scope_identity... it should not provide the id of last inserted id of record inserted by other user...
if it does that wrong room no will get assigned to tenant...

so should i use (select Max(TenantId) from TenantDetails(nolock) where Createdby= @createdby) in my case..?? or @@IDENTITY, SCOPE_IDENTITY, and IDENT_CURRENT will work right for my case??
Maciej Los 24-Jul-13 7:30am
   
In my opinion it's not possible to insert data in the same time. Even if you think that 2 users call SP in the same time, there are miliseconds between both calls. Yes, i think that @@identity or Scope_Identity should works perfect in your case.
Herman<T>.Instance 24-Jul-13 7:33am
   
I believe that too. No 2 inserts on the same time but Always 1 after another.
Adarsh chauhan 24-Jul-13 7:36am
   
ya you are right.. sorry my sentence conveyed wrong message.. by that didn't mean that they will be on same time.. i was asking that whether id will be returned session wise or table wise...
Adarsh chauhan 24-Jul-13 7:31am
   
Thanks a lot, I got my answer..
I used all the cases...
select case(which i was using),@@Identity and Scope_identity() is working fine for me... but Ident_current() would not work right in my case..

Thanks for help... :)
Adarsh chauhan 24-Jul-13 7:34am
   
yup you are right.. there will b diff. b/w both transaction..
but i was confused that @@identity or Scope_Identity will return the max id from the table as ident_current does..
now there is no confusion.. as @@identity or Scope_Identity work on session wise...
Thanks again..
Maciej Los 24-Jul-13 7:39am
   
You're welcome ;)
You could also modify the primary key to
PRIMARY KEY CLUSTERED
(
[TenantId] DESC
)



When you do a select top 1 from TenantDetails(nolock) where Createdby= @createdby, you will Always have the most recent one
   
Comments
Adarsh chauhan 24-Jul-13 7:00am
   
that is not a prob. if i use where Createdby= @createdby i will get the desired output...
i mean if i dont use where clause..
like suppose at the same time two users are using same app. respectively ids generated are 1,2 i dont want it to happen that id generated by 1st user's trans. returned to 2nd or vise versa..
Herman<T>.Instance 24-Jul-13 7:07am
   
using MAX(tenantID) is slower than TOP 1. If you get a real large volume in your tables TOP 1 is the first row from table. Add Index on created By too!
Adarsh chauhan 24-Jul-13 7:10am
   
thats fine i will use Top 1 order by desc... this will do my task..
but my question is...
(select Top 1 TenantId from TenantDetails(nolock) where Createdby= @createdby Order by TenantId desc) would be better in my case or @@IDENTITY, SCOPE_IDENTITY, and IDENT_CURRENT will work right ??
Herman<T>.Instance 24-Jul-13 7:13am
   
@@IDENTITY is best use. This gives the value from your current sql insert statement. I do not know the performance of @@IDentity in case of high volumes. That is why I use TOP 1.
Herman<T>.Instance 24-Jul-13 7:16am
   
By the way Have you tested the situation ? Then you can tell teh differences by experience
Adarsh chauhan 24-Jul-13 7:29am
   
thanks for your response.. I used all the cases...

select case,@@Identity and Scope_identity() is working fine for me... but Ident_current() will not work right in my case... I got the answer...
and about performance wise i cant say as right now my app is in development phase data is not huge enough to test performance... Thanks alot

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900