To find the data type of an expression in SQL Server, pass it to SQL_VARIANT_PROPERTY or describe the whole query with sys.dm_exec_describe_first_result_set. This post tests both on a percentage calculation, shows how precision and scale come out, and explains why 2147483647 plus 1 fails.
SQL SERVER – FCB::Open Failed – TEMPDB Files Fail to be Created with Error: “CREATE FILE Encountered Operating System Error 3”
SQL Server would not start: FCB::Open failed because TempDB files could not be created, with operating system error 3. Here is how I fixed it.
Does NOLOCK Really Applies No Lock? – Interview Question of the Week #209
Does NOLOCK really applies no lock? No. NOLOCK takes a Sch-S, or schema stability, lock. Here is a simple script to see it for yourself.
SQL SERVER – FIX: Msg 8180 – Statement(s) Could not be Prepared. Deferred Prepare Could not be Completed
A linked server query with OPENQUERY gave Msg 8180: Statement(s) could not be prepared. Deferred prepare could not be completed. Here is what I learned and the fix.
How to Move Log File or MDF File in SQL Server? – Interview Question of the Week #208
Question: How to Move Log File or MDF File in SQL Server? Here are the steps I use to move a busy LDF or MDF file to a quieter drive.
SQL SERVER – FIX: Msg 9514 – XML Data Type is Not Supported in Distributed Queries
Querying a table with an XML column over a linked server fails with Msg 9514: XML data type is not supported in distributed queries. Here is the fix.
Clear the Plan Cache for a Single Database in SQL Server
To clear the plan cache for one database, use ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE in that database. This post tests it on two small databases, compares it with DBCC FLUSHPROCINDB and with freeing a single plan, and explains why DBCC FREEPROCCACHE is a bad habit.