Temporary stored procedures start with one # sign for a local one and two for a global one, and both live in tempdb. This post creates each kind on a demo database, shows who can call them and when they disappear, and explains which database their object names resolve in. It ends with the habit that makes them useful for testing a rewrite.
Asterisk Instead of a Number: A SQL Server Puzzle
This puzzle assigns a nine digit number to a varchar(8) variable and gets an asterisk instead of a number. The answer is an implicit conversion that does not fit. The post tests which data types behave this way, which ones raise an error, and how to size the variable.
Clear Procedure Cache for One Database in SQL Server
To clear procedure cache for one database, run ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE inside that database. This post tests it on two demo databases, shows that the other database keeps its plans, and shows how to remove a single plan by its plan handle. It also covers the cost and the lighter alternatives.
The sp_ Prefix: Finding Stored Procedures Named the Risky Way
Find user procedures with the sp_ prefix across databases, understand the lookup risk, and move callers through a synonym safely.
SQL SERVER – xp_cmdshell and Net Use ERROR: The Local Device Name is Already in Use
Mapping a share with net use through xp_cmdshell, I hit “The local device name is already in use”. Here is what causes this error and how to solve it.
How to Identify Session Used by SQL Server Management Studio? – Interview Question of the Week #151
An interview candidate asked us: how do you identify session IDs that SQL Server Management Studio is using? Here is a simple answer with sp_who2.
SQL SERVER- New DMF in SQL Server 2017 – sys.dm_os_file_exists. A Replacement of xp_fileexist
SQL Server 2017 adds a new DMF, sys.dm_os_file_exists, a replacement of xp_fileexist that suits both Windows and Linux. Here is how to use it.