SP_EXECUTESQL vs EXECUTE/EXEC

Common Properties

• The Transact-SQL statements in the sp_executesql or EXECUTE string are not compiled into an execution plan until sp_executesql or the EXECUTE statement are executed. The strings are not parsed or checked for errors until they are executed. The names referenced in the strings are not resolved until they are executed.
• The Transact-SQL statements in the executed string do not have access to any of the variables declared in the batch that contains the sp_executesql or EXECUTE statement. The batch containing the sp_executesql or EXECUTE statement does not have access to variables or local cursors defined in the executed string.
• If the executed string has a USE statement that changes the database context, the change to the database context only lasts until sp_executesql or the EXECUTE statement completes.

Comparison SP_EXECUTESQL vs EXECUTE/EXEC

sp_executesql gives you the possibility to use parameterised statements, EXECUTE does not. Parameterised statements gives no risk to SQL injection and also gives advantage of cached query plan. The sp_executesql stored procedure supports parameters. So, using the sp_executesql stored procedure instead of the EXECUTE statement improve readability of your code when there are many parameters are used. When you use the sp_executesql stored procedure to executes a Transact-SQL statements that will be reused many times, the SQL Server query optimizer will reuse the execution plan it generates for the first execution when the change in parameter values to the statement is the only variation.

sp_executesql can be used instead of stored procedures to execute a Transact-SQL statement a number of times when the change in parameter values to the statement is the only variation. Because the Transact-SQL statement itself remains constant and only the parameter values change, the SQL Server query optimizer is likely to reuse the execution plan it generates for the first execution.

Use SP_EXECUTESQL rather than EXEC(), it has better performance and improved security.

sp_executesql [ @statement= ] statement
[ 
  { , [ @params = ] N'@parameter_name data_type [ OUT | OUTPUT ][ ,...n ]' } 
     { , [ @param1= ] 'value1' [ ,...n ] }
]

How to: Drop a Database if it exists

Here is the T-SQL code for checking to create if the database exists. This code below drops the database and creates new.

-- Changed database context to 'master'
USE master
GO
-- Check Exists
IF EXISTS(SELECT name FROM sys.databases
WHERE name = 'Angel83')
DROP DATABASE Angel83
GO
-- Create Database
CREATE DATABASE Angel83
GO

SQL SERVER Tweaks

Here are some shortcuts to monitor and admin SQL Server

Determines Server available disk space:
xp_fixeddrives

Determine Log Size Log Space used and status of the available databases

DBCC SQLPERF(LOGSPACE);
GO

Database size and service broker information:

DBCC CHECKDB

Uses to get information of the database work tables to hold intermediate results and for sort operations

How to get Current time and user information:

SELECT
    CURRENT_TIMESTAMP AS TIME,
    USER AS Username,
    SYSTEM_USER AS System_Username,
    CURRENT_USER AS Current_Username,
    SESSION_USER AS Session_Username

Monitoring SQL-Server:

sp_who2

Useful stored procedures:

sp_helpdb -- Displays information about all databases on the server
sp_helpdb ‘databasename’ -- Displays information about a particular database
sp_help objectname -- Displays information about a table
sp_spaceused -- Displays information about the space used in the current database
sp_tables -- Lists the tables in a database
sp_helptext name -- Displays the code used in a particular stored procedure
sp_dboption -- Sets or returns information about database options

Hope this helps.

How to: List all files in a Drive using C#

The code below shows how to use the System.IO.DirectoryInfo function to retreive all the files in the specified location, it also show how to get the extension and other information. Here in this code sample checking for DriveType CD Rom and files in that CD Drive.

Here is a sample code snippet for doing this. Have fun!

System.IO.DriveInfo[] drives = System.IO.DriveInfo.GetDrives();
string strDriverLetter = "";
bool boolDriveIsReady = false;
foreach (System.IO.DriveInfo drive in drives)
{
    if (drive.DriveType == System.IO.DriveType.CDRom)
    {
        Console.WriteLine("Drive Name: " + drive.Name);
        strDriverLetter = drive.Name;
        boolDriveIsReady = drive.IsReady;
        break;
    }
}
if (boolDriveIsReady == true)
{
    boolDriveIsReady = false;
    DirectoryInfo di = new DirectoryInfo(strDriverLetter);
    FileInfo[] exFiles = di.GetFiles("*.exe");
    foreach (FileInfo fi in exFiles)
    {
        Console.WriteLine("File Name: " + fi.Name + "Extension "+ fi.Extension);
        if (fi.Name.ToUpper().Equals("LAUNCH.EXE"))
        {                        
            boolDriveIsReady = true;
            break;
        }
    }
}

How to: Running Windows service at a specified time period.

I am writing an Notification service, for that i need to send reminders based on a time period. This is how I did. Here i am Configuring notification time and service interval time in app.config. For me, notification time is the time when to execute the logic and service interval time will tell the timer when to stop and start. If we want we can make this data driven as well. I have added a timer control. Here is the sample code snippet here for this.

public partial class Service1 : ServiceBase
{
    private System.Timers.Timer timer = null;
    private NotificationClass nc = new NotificationClass();
    private string notificationTime = ConfigurationManager.AppSettings["notificationTime"];
    // Default time to start and stop the timer.
    private string serviceInterval = ConfigurationManager.AppSettings["serviceInterval"];
    public Service1()
    {
        InitializeComponent();
        timer = new System.Timers.Timer(Convert.ToDouble(serviceInterval));
        timer.Elapsed += new System.Timers.ElapsedEventHandler(timer_Elapsed);
    }
    private void timer_Elapsed(object sender, System.Timers.ElapsedEventArgs e)
    {
        // Compare this notification time with your time...
        if (DateTime.Now.ToShortTimeString() == notificationTime)
        {
            this.timer.Enabled = false;
            this.timer.AutoReset = false;
            // Do the logic
            nc.BirthdayReminder();
            this.timer.Start();
        }
    }
    protected override void OnStart(string[] args)
    {
        // TODO: Add code here to start your service.
        timer.AutoReset = true;
        timer.Enabled = true;
        timer.Start();
    }
    protected override void OnStop()
    {
        // TODO: Add code here to perform any tear-down necessary to stop your service.
        timer.AutoReset = false;
        timer.Enabled = false;
    }
}