If you’ve got a partitioned table in SQL Server, and you now want to run a query that includes the partition number on each row returned by the query, you can use the $PARTITION system function to do just that.
The $PARTITION function returns the partition number into which a set of partitioning column values would be mapped for any specified partition function.
You can therefore use it in your SELECT statement to return the partition that each row belongs to.
If you’ve previously created a linked server, but you now want to update one or more of its server options, you can do that either via the SQL Server Management Studio GUI or with T-SQL.
If you choose to do it with T-SQL, the sp_serveroption system stored procedure is what you need.
In SQL Server, you can use the FORMAT() function to return the short day name from a date. FORMAT() is a T-SQL function that enables you to format dates and numbers to a specified format.
This function returns its result as a string. Specifically, it returns it as either a nvarchar or null as the case may be.
Continue readingIf you’ve encountered error Msg 7411, Level 16 in SQL Server, it’s because you need to enable “RPC out” on the linked server that you’re trying to execute code on.
Continue readingIn SQL Server, there are a few ways to get metadata of the result set from a query. This includes the data type of the columns returned by a T-SQL query.
In particular, the sys.dm_exec_describe_first_result_set system dynamic management function is a good choice for such a task.
If you encounter error Msg 15123, Level 16, reading “The configuration option ‘Agent XPs’ does not exist“, you were probably trying to execute EXEC SP_CONFIGURE 'Agent XPs' while advanced options are hidden.
This error is easily fixed.
Continue readingIt’s quite easy to encounter error Msg 214, Level 16 when executing stored procedures such as sp_executesql or sp_describe_first_result_set.
Fortunately it’s easy to fix too!
The most common reason for getting this error is that you forgot to prefix your string with N.
Therefore, to fix this issue, try prefixing your string with N.
In this article, I present three options for returning the steps of a SQL Server Agent job when using T-SQL.
Continue readingHere are three T-SQL functions that you can use to extract the month from a date in SQL Server.
Continue readingOne of the great things about database views, is that they allow you to run complex queries without needing to know the underlying database schema.
Yes, it’s true that you need to know the underlying schema when you create the view, but you only need to do that once. Once you’ve created it, you can query that view all day long without needing to remember all the table and column names, etc.
Views typically combine data from multiple tables into a single, virtual table, which makes it kind of like a “black box”. As long as it works as designed, you don’t need to concern yourself with the hidden details.
But what if you do want to check a view for its underlying tables and columns?
Continue reading