According to the MSDN Books online COALESCE() returns the first Non NULL value. Let's understand this with an example.
Consider the Employees Table below. Not all employees have their First, Midde and Last Names filled. Some of the employees has First name missing, some of them have Middle Name missing and some of them last name.
Now, let's write a query that returns the Name of the Employee. If an employee, has all the columns filled - First, Middle and Last Names, then we only want the first name.
If the FirstName is NULL, and if Middle and Last Names are filled then, we only want the middle name. For example, Employee row with Id = 1, has the FirstName filled, so we want to retrieve his FirstName "Sam". Employee row with Id = 2, has Middle and Last names filled, but the First name is missing. Here, we want to retrieve his middle name "Todd". In short, The output of the query should be as shown below.
We are passing FirstName, MiddleName and LastName columns as parameters to the COALESCE() function. The COALESCE() function returns the first non null value from the 3 columns.
SELECT Id, COALESCE(FirstName, MiddleName, LastName) AS Name
FROM tblEmployee
Consider the Employees Table below. Not all employees have their First, Midde and Last Names filled. Some of the employees has First name missing, some of them have Middle Name missing and some of them last name.
Now, let's write a query that returns the Name of the Employee. If an employee, has all the columns filled - First, Middle and Last Names, then we only want the first name.
If the FirstName is NULL, and if Middle and Last Names are filled then, we only want the middle name. For example, Employee row with Id = 1, has the FirstName filled, so we want to retrieve his FirstName "Sam". Employee row with Id = 2, has Middle and Last names filled, but the First name is missing. Here, we want to retrieve his middle name "Todd". In short, The output of the query should be as shown below.
We are passing FirstName, MiddleName and LastName columns as parameters to the COALESCE() function. The COALESCE() function returns the first non null value from the 3 columns.
SELECT Id, COALESCE(FirstName, MiddleName, LastName) AS Name
FROM tblEmployee
Nicely explained . Thanks
ReplyDeleteHii Sir , I antedated one interview recently and they asked to me how to get the Last updated Record in a table , I also search in Google but i did not find proper explanation , Now a day this question asking
ReplyDeleteand some where i asked from the interviewer how to get , they was telling by using time stamp , please guide me it will help me Thank u
As Best in my Knowledge By Using Identity.
DeleteWhat SCOPE_IDENTITY is
SCOPE_IDENTITY is:
SCOPE_IDENTITY returns the last IDENTITY value inserted into an IDENTITY column in the same scope.
SCOPE_IDENTITY returns the last identity value generated for any table in the current session and the current scope.
A scope is a module; a Stored Procedure, trigger, function, or batch.
Thus, two statements are in the same scope if they are in the same Stored Procedure, function, or batch.
The SCOPE_IDENTITY() function will return the NULL value if the function is invoked before any insert statements into an identity column occur in the scope.
What IDENT_CURRENT is
IDENT_CURRENT is:
IDENT_CURRENT returns the last identity value generated for a specific table in any session and any scope.
IDENT_CURRENT is not limited by scope and session; it is limited to a specified table.
What @@IDENTITY is
@@IDENTITY is:
@@IDENTITY returns the last identity value generated for any table in the current session, across all scopes.
After an INSERT, SELECT INTO, or bulk copy statement completes, @@IDENTITY contains the last identity value generated by the statement.
If the statement did not affect any tables with identity columns, @@IDENTITY returns NULL.
If multiple rows are inserted, generating multiple identity values, @@IDENTITY returns the last identity value generated.
The @@IDENTITY value does not revert to a previous setting if the INSERT or SELECT INTO statement or bulk copy fails, or if the transaction is rolled back.
Differences
The differences between them are:
SCOPE_IDENTITY, IDENT_CURRENT, and @@IDENTITY are similar functions in that they return values inserted into IDENTITY columns.
SCOPE_IDENTITY and @@IDENTITY will return the last identity values 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. A scope is a module; a Stored Procedure, trigger, function, or batch.
Whenever you create a table, you add a column(RID) with the datatype timestamp or rowversion.It generate special value that is unique within a given
Deletedatabase. Value is set by the database
itself automatically every time the record
is either inserted or updated,even though
the timestamp column wasn’t referred to
by the UPDATE statement (you’re actually
not allowed to update the timestamp fi eld
directly).Then write a query like (Select * from where RID=(Select MAX(RID) from ) )
COALESCE() FUNCTION WORKING IN SSMS 2017 ?
ReplyDelete