The SELECT statement is probably the most commonly used statement in SQL Server. Most of the time this statement is run against a view or directly against a table to retrieve rows of tabular data.
But views and tables aren’t the only objects you can run a SELECT statement on. The SELECT statement can also be used on other objects such as rowset functions, OPENXML, and user-defined functions.
This article provides an example of selecting data via a table-valued function.
In SQL Server, a table-valued function (TVF) is a user-defined function that returns a table. This is in contrast to a scalar function, which returns a single value.
You can invoke a table-valued function in the same way that you can query a table. For example, you can use it in a SELECT statement. In some cases, table-valued functions can also be used to update, delete, and insert data.
In SQL Server, the @@SERVERNAME configuration function returns the name of the local server that is running SQL Server.
No argument is required. You can simply use it in a SELECT statement to return the server name.
A widely known limitation of computed columns in SQL Server is that they can’t access data from other tables. That is, your expression can use columns in the same table, but not from other tables.
But this is only half-true. While you can’t reference another table’s column directly within your expression, you can invoke a user-defined function. And therefore, you could create a user-defined function that performs the calculation you need, then simply call that function as your computed column’s expression.
Here’s an example to demonstrate.
Continue readingIn SQL Server, the @@MAX_CONNECTIONS configuration function returns the maximum number of simultaneous user connections allowed on an instance of SQL Server. The number returned is not necessarily the number currently configured.
No argument is required. You can simply use it in a SELECT statement to return the maximum number of simultaneous user connections allowed on the current server.
In SQL Server, the @@MAX_PRECISION configuration function returns the precision level used by decimal and numeric data types as currently set in the server.
No argument is required. You can simply use it in a SELECT statement to return the precision level of the current server.
In SQL Server, you can use the CRYPT_GEN_RANDOM() function to return a cryptographic, randomly-generated number. The number is generated by the Cryptographic Application Programming Interface (CAPI).
CAPI is a Microsoft Windows platform specific application programming interface included with Microsoft Windows operating systems that provides services to enable developers to secure Windows-based applications using cryptography.
The CRYPT_GEN_RANDOM() function accepts two arguments: the length (required), and a seed (optional).
The return value is varbinary(8000).
This article presents two ways to return a list of user-defined functions in a SQL Server database.
In SQL Server, you can use the SQL_VARIANT_PROPERTY() function to return base data type information from a sql_variant value.
The function accepts two arguments: the sql_variant value, and the property for which information is to be provided.
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.