how to – Page 177

Set Key/Value Pairs in the Session Context in SQL Server (sp_set_session_context)

February 14, 2020January 13, 2020 by Ian

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

February 14, 2020January 2, 2020 by Ian

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

February 14, 2020January 2, 2020 by Ian

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.

How to Use FILEPROPERTY() in SQL Server

February 14, 2020December 30, 2019 by Ian

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

February 14, 2020December 29, 2019 by Ian

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

February 14, 2020December 28, 2019 by Ian

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

February 14, 2020December 27, 2019 by Ian

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.

Return All Filegroups for the Current Database in SQL Server

October 15, 2020December 26, 2019 by Ian

In SQL Server, you can query the sys.filegroups system catalog view to return a list of all filegroups for the current database.

This view contains a row for each data space that is a filegroup. In other words, your results contain one row per filegroup.

6 Ways to Check if a Table Exists in SQL Server (T-SQL Examples)

February 14, 2020December 9, 2019 by Ian

This article offers five options for checking if a table exists in SQL Server. Most options involve querying a system view, but one of the options executes a system stored procedure, and another involves a function.

I also include some simple IF statements that can be modified to suit your circumstance.

Use DB_ID() to Return the ID of a Database in SQL Server

February 14, 2020November 25, 2019 by Ian

In SQL Server, you can use the DB_ID() function to return the ID of the current database, or another specified database.

The way it works is, you pass the name of the database as an argument, and then the function will return the ID of that database. If you don’t pass a name it will return the ID of the current database.