In SQL Server environments, two of the many string functions at our disposal are LEFT() and SUBSTRING().
These functions do a similar thing, but there are differences. This article looks at some of the main differences between these functions.
Tag: functions
LEFT() vs SET TEXTSIZE in SQL Server: What’s the Difference?
You may be familiar with the SET TEXTSIZE statement in SQL Server, that enables you to limit the amount of data returned in a SELECT query.
Perhaps you’re finding that its results are exactly the same as LEFT() when running a specific query. Which begs the question: Is there a difference between these two options?
What is @@TEXTSIZE in SQL Server?
In SQL Server, the @@TEXTSIZE configuration function returns the current value of the TEXTSIZE option.
No argument is required. You can simply use it in a SELECT statement to return the current TEXTSIZE value.
The TEXTSIZE value specifies the size of varchar(max), nvarchar(max), varbinary(max), text, ntext, and image data returned by a SELECT statement. This value can be set using SET TEXTSIZE.
Return the Increment Value of an Identity Column in SQL Server
In SQL Server, you can use the T-SQL IDENT_INCR() function to return the increment value of an identity column. This is the increment value specified when creating the identity column.
Return the Original Seed of an Identity Column in SQL Server
In SQL Server, you can use the T-SQL IDENT_SEED() function to return the original seed of an identity column. This is the original seed value specified when creating the identity column.
IDENT_CURRENT vs @@IDENTITY vs SCOPE_IDENTITY in SQL Server: What’s the Difference?
In SQL Server, if you ever need to return the value created in an identity column, you have several options. Each of these options, although similar, do a slightly different thing.
Use SCOPE_IDENTITY() to Return the Last-Inserted Identity Value in the Same Scope (SQL Server)
In SQL Server, you can use the T-SQL SCOPE_IDENTITY() function to return the last identity value inserted into an identity column in the same scope.
A scope is a module (stored procedure, trigger, function, or batch). If two statements are in the same stored procedure, function, or batch, they are in the same scope.
Note that it returns the last identity value generated in any table in the current session. This is in contrast to the IDENT_CURRENT() function, which returns the last-inserted identity value for a given table, regardless of which session it’s in.
SCOPE_IDENTITY() is very similar to @@IDENTITY in that they both return the last-inserted identity value in the current session. The difference is that SCOPE_IDENTITY() is limited to the current scope, whereas @@IDENTITY is not limited to a specific scope.
Use @@IDENTITY to Return the Last-Inserted Identity Value in SQL Server
In SQL Server, you can use the T-SQL @@IDENTITY system function to return the last-inserted identity value in the current session.
Note that it returns the last identity value generated in any table in the current session. This is in contrast to the IDENT_CURRENT() function, which returns the last-inserted identity value for a given table.
The SCOPE_IDENTITY() function is very similar to @@IDENTITY in that it also returns the last-inserted identity value in the current session. The difference is that SCOPE_IDENTITY() is limited to the current scope.
Use IDENT_CURRENT() to Return the Current Identity Value on an Identity Column in SQL Server
In SQL Server, you can use the T-SQL IDENT_CURRENT() function to return the last identity value generated for a specified table or view on an identity column. The last identity value generated can be for any session and any scope.
IDENTITY() vs IDENTITY() in SQL Server: What’s the Difference?
It might seem strange, but T-SQL has an IDENTITY() property and an IDENTITY() function, each of which serve a (similar, but) different purpose.
- The
IDENTITY()property creates an identity column in a table. An identity column contains an automatically incrementing identification number. This property is used with theCREATE TABLEandALTER TABLEstatements. - The
IDENTITY()function however, is used only in aSELECTstatement with anINTOtable clause to insert an identity column into a new table.
There’s also the SQL-DMO Identity property that exposes the row identity property of a column, but I won’t cover that here. Microsoft advises that this property will be removed in a future version of SQL Server, and that you should avoid using it in new development work.