CASE statement, an expression to evaluate different conditions to return a scalar value, when a condition is met. If none of the condition evaluated to TRUE then expression from ELSE block will be returned. Although ELSE block is optional. Above all, the CASE statement have two formats Simple CASE & Searched CASE.

Sometimes, we need to pause the execution of a query to simulate different scenarios. Waiting for a desired amount of time or executing it at a specified clock time. Helpful to pause a query, batch or a stored procedure. WAITFOR statement can be used for delaying a query or execute a query at a specified […]

SQL Server supports ANSI SET operators. Including UNION, UNION ALL, EXCEPT and INTERSECT. Generally, SET operators used to combine multiple query result sets into a single result set. In an early post on tech-recipes, we have learned the use of UNION and UNION ALL operators.

Sending email is a routine task needed in any application. Emails used for sending an alert, message, query result, attachment or any other notification to users. SQL Server uses the Database Mail component to configure and send emails. Using this back end component rather than a frontend application for sending email is hassle-free. Giving more […]

SQL Server agent job is a windows service based scheduler. Agent job is used to schedule and automate SQL scripts, administrative tasks, SSIS packages and various other tasks using SQL agent job tool. This component is integrated along with SSMS and available under Object explorer as SQL Server Agent.

Transaction is an essential part of the database. No database design is complete without handing transaction behaviour correctly. Transaction defines that all the set of operations has to succeed or fail as a unit. Partial success or failure results in the unexpected output. Transaction is needed to maintain integrity in the database.

A conditional statement IF…ELSE included in many programming languages. IF…ELSE works on boolean expression resulting in TRUE or FALSE. Besides, different actions can be performed on boolean expression results. IF…ELSE in SQL Server is used to check expression and based on the results (TRUE or FALSE) execute another SQL statements block.

BCP (bulk copy program) – a command line utility that bulk copies data between SQL Server database tables and data files. Using BCP, we can export data from a database table, view or a query to data files, generally text delimited files. Moreover, using BCP to import data into SQL Server database table from data […]

Using a keyboard shortcut saves time and effort. Every smart developer should know a handful of keyboard shortcuts to increase productivity. SQL Server includes numerous keyboard shortcuts for developers to increase efficiency. Important to learn and try them out to speed up the development. You can save a lot of time and efforts by assigning […]

SQL Server has added support for various string functions. Coupled with manipulation and pattern finding features. String function takes a string as input and returns numeric value or another string as an output. Depends on the type of function used. Usually while working with string, we need to find the first occurrence of a character […]

Using SELECT and SET statement for variable assignments is a common practice in SQL Server. I did not pay more attention to SELECT vs SET difference, until I met with surprising logical errors. In production code using SELECT over SET, produced unanticipated results. Both are used interchangeably for variable assignment. Importantly, understanding when to use […]

SQL Server agent job is a windows service based scheduler. Agent job is used to schedule and automate SQL scripts, administrative tasks, SSIS packages and various other tasks using SQL agent job tool. This component is integrated along with SSMS and available under Object explorer as SQL Server Agent.