These videos beat even the paid content. I have got few Udemy courses, paid few hundred dollars for some bootcamps, but these free videos are by far the best I came across. Thanks a lot Venkat Sir for the amazing content!!!!!!
Thank you Venkat, it's very valuable that you try to explain every and each concept so anyone can understand it. Even after 6 years, your videos are still unique and perfect.
It was a bit difficult topic, but I think I comprehend it, thanks to You, Venkat. I hope you receive back all of the kindness and help you gave us in your life!
Thanks a lot, Venkat for these video tutorials. :-) This is the only tutorial that is the best for SQL which I came across after browsing and listening to various video tutorials online. Really great work! Thanks once again
Thank you very much for your interest in pragimtech. com videos. Sure, please feel free to leave a comment, and I shall respond as soon as I can. Good Luck.
In Simple words : Scope_Identity returns the value from the table that got affected due to the last command. @@identity returns the value from the table that got TRIGGERED due to the last command you gave. (In the video, The second table that got triggered due to the command you gave on the first table) IDENT_CURRENT('Tablename') returns the value from the table you specified.
Excellent explanation, I do not speak English yet, but your pronunciation is very good and I have learned a lot with your courses. Congratulations on your excellent work
Ya venkat sir thanq you so much once again.I will do my best to share these videos because i am going through each and every video of yours sql server playlists.All these are very clearly explained with proper English and all.Thank you once again.And if i have any doubts can i ask you sir ?
Hi, Thank you very much for taking time to give feedback. I am really glad you found these videos useful. If you want to receive email alerts, when new videos are uploaded, please subscribe to my youtube channel. May I ask you for a favour. I want these tutorials to be helpful for as many people as possible. Please free to share the link with your friends and family who you think would also benefit from them. Good Luck.
Hi venkat, i am learning daily sql from this videos. It been more understandable and flexible to learn, but i have few doubts is it still using same course or any changes happen for time being because it been 9years could you please provide me some information please
Hi Nitya, if you are providing a value to be inserted into the table, then why don't you retain that value in a variable, which can be used later. I hope this answers your question.
The session is the database connection. The scope is the current query or the current stored procedure. A situation where the scope_identity() and the @@identity functions differ, is if you have a trigger on the table. If you have a query that inserts a record, causing the trigger to insert another record somewhere, the scope_identity() function will return the identity created by the query, while the @@identity function will return the identity created by the trigger. So, normally you would use the scope_identity() function.
Hi Venkat, One question, when you say for the @@IDENTITY to work for same session and ANY scope, would that mean it can sometimes also show the same result as that of SCOPE IDENTITY in the given examples? Or is @@Identity working on the last updated Identity value in that session across all scopes? Thanking in anticipation.
Hi Venkat , Thanks for the detailed explanation . I have one doubt , how the scope identity knows for which table the user need to know the last identity number .
Very Very Thanx for your respond.Suppose I am entering data into the table manually rather than auto generating the column value and if i want to access the last value of that particular column then how to retrieve that?
Hi venkat, i got a question like this .. I need to inesert row from 1 to 11 after that 19th row,after that the sequence contiunue like normal means 20,21....and so on. For above question i got something like this, identity(1,1) i will give till 11 i will insert after that make "Insert_Identity" on and i wll insert 19th recored. But after this i want row should start from 20 how can i get 20 . Because last Identity generated column value will be 11 right. So i want help in this...Thank U!! for ur great teaching...
One more way is using output clause. Insert into test1 output inserted.ID values ('X') This query will return you last inserted id. No need to execute two separate queries for insert & retrieval.
Hi Venkat, that's an amazing work, thank you very much. Also, I went on your website, et I can't click on any "View Contents" in the DVD page. I have Firefox and Adblock.
As far as i understood: @@identity generates the last generated identity in the same database. scope_identity generates the last generated identity in the same table (the table your last command had used)
Ok 1) Scope_identity will return identity values generated within the same stored proc. 2)@@identity will return values generated outside the stored proc also ( best example is the one with trigger in the video ) Extra information - Scope_Identity is more commonly used than @@identity
actually its like when you execute SCOPE_IDENTITY it will show only the affected row in that table and when you execute @@IDENTITY it will show the last affected row of another table which was affected due to last insert statement [ correct me if i am wrong ]
Hello Mr.Venkat ScopeIdentity :return last identity value in scope and session but when I was created the trigger last identity value is when trigger fired but the value that return is last identity value in first table which have a trigger. Can you calrify please?
I'm watching your videos and I found them too good for studies but the 8th part of SQL server, I didn't get it. There is a difficulty in understanding the scope, ident from the videos. please help me sir.
I have a doubt regarding @@IDENTITY function. Eg: If we extend the example shown in the video, that is if our trigger inserts a row in two different tables say Test2 and Test3. In that case, what will @@IDENTITY return?
The last Trigger which u create will be considered by @@IDENTITY func. Ex: First u created Trigger 1 (some value inserted in Table 2 if we insert in Table 1)and then you created Trigger 2 (some value inserted in Table 3 if we insert in Table 1). The Last created Trigger which is Trigger T2 as it will be Technically below the Trigger 1 in terms of lines in code. Now if we insert in Table 1, Three rows wil be effected and if u run @@IDENTITY, it will show the Table 3's value.
Please how can I return the identity column and use as a variable e.g. insert a record into the products table and return the product id (set as identity column); then pass the new product id inserted in products table to stock entry table?
Hi Venkat, i have 10 records in table and when i use DBCC then it will be reset to 0 then when i try to enter new record it is error the statement have been terminated because of deplication, how to overcome it again to move on
When I try to create a table via syntax ( Create Table Test1) it shows me "Msg 102, Level 15, State 1, Line 1 Incorrect syntax near 'Test1'." ....How can this be a syntax error?
Scope is the range of effectiveness of commands. So if you tell SQL: INSERT INTO sample1, then its scope is sample1, because it affects only on sample1. But the triggers reaches for sample2, too, so the trigger goes into another scope. Scope is a fence. SCOPE_IDENTITY is too short to see over the fence, @@IDENTITY is enough tall to see over the fence to the neighbor's garden, IDENT_CURRENT sits on the rooftop and sees both of the gardens (sampe1 and sample2), but is interested only in one of them.
can you help me? how can i separate 1 column result the negative into positive number and put it in different column? like for example i have a result sum of 2 different column 10000 -2000 9983 20394 9323232 -2222 like that? how to i separate the negative and the positive? and get the sum of it?
+Ekaj Zerep This query will separate the positive and the negative number into separate columns select test_id2 as A1, null as A2 from identity_test_tab where test_id2>0 union select null as A1,test_id2 as A2 from identity_test_tab where test_id2<0 This query will get you the sum select (select sum(test_id2) from identity_test_tab where test_id2>0) as test_id_pos,(select sum(test_id2) from identity_test_tab where test_id2<0) as test_id_neg from dual
He just mentioned it at the end . It can be used to get the Id for the customer ( since the database has internally managed incrementing the Identity coloumn. )
Your explanation is good but this videos are definitely not for beginner's and you choose the wrong way of teaching which is, each and every video is connected to another which is very bad. you are good for only experienced candidates.
These videos beat even the paid content. I have got few Udemy courses, paid few hundred dollars for some bootcamps, but these free videos are by far the best I came across. Thanks a lot Venkat Sir for the amazing content!!!!!!
is that ok to watch this videos now
Thank you Venkat, it's very valuable that you try to explain every and each concept so anyone can understand it. Even after 6 years, your videos are still unique and perfect.
It was a bit difficult topic, but I think I comprehend it, thanks to You, Venkat. I hope you receive back all of the kindness and help you gave us in your life!
man, this series is so good that i'm actually watching all the ads
Me too :-) Unbelievable! Adblocker switched off
Its 2024 and these videos are still gold. I would totally donate if I could.
Thanks a lot, Venkat for these video tutorials. :-) This is the only tutorial that is the best for SQL which I came across after browsing and listening to various video tutorials online. Really great work! Thanks once again
Thank you very much for your interest in pragimtech. com videos. Sure, please feel free to leave a comment, and I shall respond as soon as I can. Good Luck.
sir your all video tutorials are excellent., thanx for providing these video series lecture..
In Simple words :
Scope_Identity returns the value from the table that got affected due to the last command.
@@identity returns the value from the table that got TRIGGERED due to the last command you gave. (In the video, The second table that got triggered due to the command you gave on the first table)
IDENT_CURRENT('Tablename') returns the value from the table you specified.
That tutorials are simply amazing!!!
I love your series. I can't find even better than this..
Hi Venkat are simple and easy to learn.Do you provide similar kind of video tutorials for MSBI?
Thank you sir. Wonderful explanation. Was confused first, but watched twice. Then got it.
Thanks Venkat.. m happy that I have landed on the very right video to understand everything so clearly.. you have made it so interesting
kudvenkat you are the best teacher that I ever found. I really love you for this 😍😍😍
Thank you so much sir and much love from Afghanistan, these videos and slides are so beneficial.
Thank you for this video! I didn't know about any of these functions and used "select max(ID) " query to return user ID in my application)
You are Awesome Venkat..!!
Thanks for sharing your knowledge with everyone.
Excellent explanation, I do not speak English yet, but your pronunciation is very good and I have learned a lot with your courses.
Congratulations on your excellent work
Best SQL Server Tutorial on the net!!
thank you so much this is a great job
Ya venkat sir thanq you so much once again.I will do my best to share these videos because i am going through each and every video of yours sql server playlists.All these are very clearly explained with proper English and all.Thank you once again.And if i have any doubts can i ask you sir ?
chala chala thanks meeku.
Dear Sir,
Thanks a lot for all of your great tutorials..
Regards
Abhishek
Thank you very much, i actually made correction into my code thanks to this video .
Hi Nitya, can you please rephrase your question. If it is not an identity column, how and what are you using to auto generate the values?
Thank you so so much Venkat you are the best...
Very nicely explained. Thank you!
Hi, Thank you very much for taking time to give feedback. I am really glad you found these videos useful. If you want to receive email alerts, when new videos are uploaded, please subscribe to my youtube channel. May I ask you for a favour. I want these tutorials to be helpful for as many people as possible. Please free to share the link with your friends and family who you think would also benefit from them. Good Luck.
kudvenkat Thank you so much .Can you please upload interview questions tricky and scenario based interview questions from 4-6 year .Net developer
awesome videos , awesome course, Thank YOU!!!
Thank You Sir
Very Help full Video..Very Nice Sir
Hi venkat, i am learning daily sql from this videos. It been more understandable and flexible to learn, but i have few doubts is it still using same course or any changes happen for time being because it been 9years could you please provide me some information please
very nice explanation sir! my pranams!
Did not just taught me about Identities but also Triggers
Hi Nitya, if you are providing a value to be inserted into the table, then why don't you retain that value in a variable, which can be used later. I hope this answers your question.
Venkat..You are Superb...
your explanation is clear.. intresting
thanks for this video kudvenkat(bro) ;-)
it was very useful for me
thank you, i wish u do on SPSS/SAS FULLY
Thanks for nice explanation with examples
The session is the database connection. The scope is the current query or the current stored procedure.
A situation where the scope_identity() and the @@identity functions differ, is if you have a trigger on the table. If you have a query that inserts a record, causing the trigger to insert another record somewhere, the scope_identity() function will return the identity created by the query, while the @@identity function will return the identity created by the trigger.
So, normally you would use the scope_identity() function.
Thanks @Ninad for the sesssion and scope explanation , its very helpful for me
Very nice tutorial buddy, million thanks to you
Hi Venkat,
One question, when you say for the @@IDENTITY to work for same session and ANY scope, would that mean it can sometimes also show the same result as that of SCOPE IDENTITY in the given examples?
Or is @@Identity working on the last updated Identity value in that session across all scopes?
Thanking in anticipation.
Thanks nicely xplained..If it would not be an identity column then how will we retrieve the last genrated value..please reply?
Hi Venkat , Thanks for the detailed explanation . I have one doubt , how the scope identity knows for which table the user need to know the last identity number .
Very Very Thanx for your respond.Suppose I am entering data into the table manually rather than auto generating the column value and if i want to access the last value of that particular column then how to retrieve that?
Hi venkat, i got a question like this .. I need to inesert row from 1 to 11 after that 19th row,after that the sequence contiunue like normal means 20,21....and so on.
For above question i got something like this, identity(1,1) i will give till 11 i will insert after that make "Insert_Identity" on and i wll insert 19th recored. But after this i want row should start from 20 how can i get 20 . Because last Identity generated column value will be 11 right. So i want help in this...Thank U!! for ur great teaching...
Very detailed. Got a bit complicated, but that's fine. Keep it up!
Venkat, Thanks a million but I do not understand what are you mean of Scope
Hi Venkat ,
we will be very thankful to you .. if you show us how to debug a complex procedure using
sql server debugger .
One more way is using output clause.
Insert into test1 output inserted.ID values ('X')
This query will return you last inserted id. No need to execute two separate queries for insert & retrieval.
Hi Venkat, that's an amazing work, thank you very much.
Also, I went on your website, et I can't click on any "View Contents" in the DVD page. I have Firefox and Adblock.
Thank you for this valuable video.
If the Id or autonumber field was not the primary key.
How do we get it.
I tried many time but failed
Hi, still confused about "across any scope" for @@identity, can you explain a little more? thanks!
yeah , same here
As far as i understood: @@identity generates the last generated identity in the same database. scope_identity generates the last generated identity in the same table (the table your last command had used)
don't quote me on that
Ok
1) Scope_identity will return identity values generated within the same stored proc.
2)@@identity will return values generated outside the stored proc also ( best example is the one with trigger in the video ) Extra information - Scope_Identity is more commonly used than @@identity
actually its like when you execute SCOPE_IDENTITY it will show only the affected row in that table and when you execute @@IDENTITY it will show the last affected row of another table which was affected due to last insert statement [ correct me if i am wrong ]
Hello Mr.Venkat
ScopeIdentity :return last identity value in scope and session but when I was created the trigger last identity value is when trigger fired but the value that return is last identity value in first table which have a trigger.
Can you calrify please?
I'm watching your videos and I found them too good for studies but the 8th part of SQL server, I didn't get it. There is a difficulty in understanding the scope, ident from the videos. please help me sir.
Are you sure that one query-window = One Session or is this just for demonstration purposes?
You're awesome .
which one is better scope_identity or current_ident
Thanks sir
Thank you very much sir..
I have a doubt regarding @@IDENTITY function. Eg: If we extend the example shown in the video, that is if our trigger inserts a row in two different tables say Test2 and Test3. In that case, what will @@IDENTITY return?
The last Trigger which u create will be considered by @@IDENTITY func.
Ex: First u created Trigger 1 (some value inserted in Table 2 if we insert in Table 1)and then you created Trigger 2 (some value inserted in Table 3 if we insert in Table 1).
The Last created Trigger which is Trigger T2 as it will be Technically below the Trigger 1 in terms of lines in code.
Now if we insert in Table 1, Three rows wil be effected and if u run @@IDENTITY, it will show the Table 3's value.
Please how can I return the identity column and use as a variable e.g. insert a record into the products table and return the product id (set as identity column); then pass the new product id inserted in products table to stock entry table?
I’m facing quite difficulty identity and default constant part basically I’m not understand these lecture please help me what should i do ?
Thanks a lot sir
nitya, you can order DESC the identity column and then from top(1) result to see the value of this column
Nice one :)
Thanks Venkat
thanks a lot sir
please can you explain difference between DBCC CHECKIDENT ( table_name )
and
DBCC CHECKIDENT ( table_name, RESEED )
What is mean by scope ?
why we need last generated identity column value Boss?
what is scope Venkat? thanks Sonia
For which table scope_identity() gives max identity value?
Hi Venkat, i have 10 records in table and when i use DBCC then it will be reset to 0 then when i try to enter new record it is error the statement have been terminated because of deplication, how to overcome it again to move on
okay.. but how to do an insert using the last identity at another table to make a relation?
what's the propose of this video?
previously i created tblPerson1 with 9 records and now with Test1 i created 2 records but when i use SCOPE_IDENTITY() its showing 2 not 9 why??
Thanx sir..
I have a doubt, at 10:37 the result of SCOPE_IDENTITY should be 3 right but it showed the output as 2. Someone please clear my doubt
When I try to create a table via syntax ( Create Table Test1) it shows me "Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'Test1'." ....How can this be a syntax error?
you have not put Columns
If the session expires, then we are getting a Null value for the scope_identity and @@identity function. Is there a solution for this?
The table must have Identity column
Ex:
create table Test1 (id int identity, Name nvarchar(20))
In real life where we use scope identity ?
What exactly scope means in this context
Scope is the range of effectiveness of commands. So if you tell SQL: INSERT INTO sample1, then its scope is sample1, because it affects only on sample1.
But the triggers reaches for sample2, too, so the trigger goes into another scope.
Scope is a fence. SCOPE_IDENTITY is too short to see over the fence, @@IDENTITY is enough tall to see over the fence to the neighbor's garden, IDENT_CURRENT sits on the rooftop and sees both of the gardens (sampe1 and sample2), but is interested only in one of them.
can you help me? how can i separate 1 column result the negative into positive number and put it in different column?
like for example i have a result sum of 2 different column
10000
-2000
9983
20394
9323232
-2222
like that? how to i separate the negative and the positive? and get the sum of it?
+Ekaj Zerep
This query will separate the positive and the negative number into separate columns
select test_id2 as A1, null as A2 from identity_test_tab where test_id2>0
union
select null as A1,test_id2 as A2 from identity_test_tab where test_id2<0
This query will get you the sum
select (select sum(test_id2) from identity_test_tab where test_id2>0) as test_id_pos,(select sum(test_id2) from identity_test_tab where test_id2<0) as test_id_neg from dual
I get NULL when I execute
select SCOPE_IDENTITY()
select @@IDENTITY
Could somebody please explain why?
o ya, got it!
That's cool but what's the purpose of getting it?
He just mentioned it at the end . It can be used to get the Id for the customer ( since the database has internally managed incrementing the Identity coloumn. )
how to change persistentConnections confg's value to true in phpmydadmin
how to do auto-completion in SSMS?
ctrl + space
why it returns null with me !!
The table must have Identity column
Ex:
create table Test1 (id int identity, Name nvarchar(20))
could not get the difference between all 3 properly!!!!
IT doesnot work I am getting null value waste example
I'm getting only NULL values in the result
yeah that's happen with me too !!
You probably are disconnected from the session. The same happened to me when I closed by laptop. It asked me to reconnect to the database.
May be not entered any values on table..
I can't understand the explanation. We want some more clear and slow explanation
But why?
Exactly!
IT SHOWS NULL WHILE I RUN IT
Is there any sample project in asp.net mvc , please provide to learn .
You did not clearly mention what you mean by a scope.
The scope is the current query or the current stored procedure.
thanks , now I understand
Your explanation is good but this videos are definitely not for beginner's and you choose the wrong way of teaching which is, each and every video is connected to another which is very bad.
you are good for only experienced candidates.
This was confusing...
Thank you so much for the nice explanation along with examples