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.
Tag: how to
How to Use the IDENTITY() Property in SQL Server
When you use T-SQL to create (or alter) a table in SQL Server, you have the option of using the IDENTITY() property to create an identity column.
An identity column contains an automatically incrementing identification number. The IDENTITY() property can be used with the CREATE TABLE and ALTER TABLE statements.
How to Use the IDENTITY() Function in SQL Server
In SQL Server, you can use the IDENTITY() function to insert an identity column into a new table.
However, this function is not to be confused with the IDENTITY() property, which is used with the CREATE TABLE and ALTER TABLE statements.
The IDENTITY() function is used only in a SELECT statement with an INTO table clause. So you can use it when transferring data from one table to another, for example.
Set Key/Value Pairs in the Session Context in SQL Server (sp_set_session_context)
In SQL Server, you can use the sp_set_session_context stored procedure to set key/value pairs in the session context.
These key/value pairs then remain in your session context until your connection to SQL Server closes. You can use these values in stored procedures and other T-SQL code throughout the life of your session.
This method for maintaining session state was first introduced in SQL Server 2016. Prior to that release, you would need to use SET CONTEXT_INFO, which provides a similar, but much more limited way of storing session state.
How to Delete Files in SQL Server 2019
In SQL Server 2019, you can use the sys.xp_delete_files stored procedure to delete a file on the file system.
This stored procedure was introduced in SQL Server 2019, and it can be used in conjunction with sys.xp_copy_file and sys.xp_copy_files (both of which were also introduced in SQL Server 2019), which enable you to copy files.
Prior to SQL Server 2019, you would need to use xp_cmdshell, which spawns a Windows command shell and passes in a string for execution. The new stored procedures introduced in SQL Server 2019 allow you to copy and delete files without relying on xp_cmdshell.
New Way to Copy Files in SQL Server 2019
Two new stored procedures introduced in SQL Server 2019 are sys.xp_copy_file and sys.xp_copy_files, which enable you to copy files.
Prior to SQL Server 2019, you would need to use xp_cmdshell, which spawns a Windows command shell and passes in a string for execution. The new stored procedures introduced in SQL Server 2019 allow you to copy files without relying on xp_cmdshell.
How to Use FILEPROPERTY() in SQL Server
In SQL Server, you can use the FILEPROPERTY() function to return the property value for a specified database file. The returned value is either 1 or 0 (or NULL if the input is invalid).
To use it, provide the file’s logical file name and the property value that you want returned.
How to Use FILEGROUPPROPERTY() in SQL Server
In SQL Server, you can use the FILEGROUPPROPERTY() function to return the filegroup property value for a specified name and filegroup value. The returned value is either 1 or 0 (or NULL if the input is invalid).
To use it, provide the filegroup name and the property value that you want returned.
Use FILEGROUP_ID() to Return a Filegroup’s ID in SQL Server
In SQL Server, you can use the FILEGROUP_ID() function to return the ID of a filegroup, based on its name.
To return the filegroup ID, simply pass its name to the function.
Use FILEGROUP_NAME() to Return the Name of a Filegroup in SQL Server
In SQL Server, you can use the FILEGROUP_NAME() function to return the name of a filegroup, based on its ID.
To return the filegroup name, simply pass its ID to the function.