Question: How to Invalidate Procedure Cache in SQL Server?
SQL SERVER – Different Methods to Know Parameters of Stored Procedure
Suppose you have a stored procedure with some input parameters. There are two methods to know the list of parameters defined in a stored procedure. Let us create the following stored procedure CREATE PROCEDURE TEST_PROCEDURE ( @CUST_ID INT, @YEAR INT ) AS SELECT @CUST_ID,@YEAR Now to know the parameters used…
Read MoreSQL SERVER – Is XP_CMDSHELL Enabled on the Server?
I am not a big fan of using command line utilities in general. But from time to time I do explore and play around with command line tools that make my life easier. Having said that, when working with SQL Server, I do often give out the recommendation of not trying to use the native xp_cmdshell commands with SQL Server. Even the SQL_Server_2012_Security_Best_Practice_Whitepaper talks about xp_cmdshell and recommends to avoid the same if it is possible. These are basic building blocks of security that I highly recommend my customers from time to time.
Interview Question of the Week #013 – Stored Procedure and Its Advantages – How to Create Stored Procedure
There are some questions which are so old that they should not be asked in the interview and gets old. Here is one of the questions I have spotted so many times in the interview that if co-interviewer asks to the candidate, I often feel bored (well, I was caught…
Read MoreSQL SERVER – System procedures to know SQL Server Version
There are several ways to know the version of SQL Server. But did you know that there are two system procedures through which you can know the version? They are SP_SERVER_INFO and EXEC XP_MSVER EXEC sp_SERVER_INFO If you execute the above, you get a result set with informations about the…
Read MoreSQL SERVER – What are the Different Ways to Script Procedure with T-SQL
Ever wondered how a simple task can be done multiple ways. You can script a stored procedure in many ways. You can use SSMS tool and use Generate Script option. However, if you want to do it in T-SQL, there are four ways. Let us create this simple procedure CREATE…
Read MoreSQL SERVER – How to use Procedure sp_user_counter1 to sp_user_counter10
There are many performance counters available in SQL Server which can be used to monitor various parameters of SQL Server engine. Have you ever been into a situation where you want to see value in performance counter for a query which you have returned? Imagine a situation where you want…
Read More