There is a common need where we are required to generate a report from a database and send that report to group of people. In this article we will see as how we can use SSIS to design a work flow to first create an excel report and then send in an Email. [More]

Packages help in executing many different tasks at once and there are times when there are functionality requirement where package needs to run on a particular event invoked by the end user and we want to handle this by calling a stored procedure. Below is a small demonstration as how we accomplish this scenario using stored procedure using xp_cmdshell. [More]

Row Constructor is new feature to SQL Server 2008 that allows insertion of multiple rows of data at once. Say we create a table row_constructor create table row_construct (ID int identity(1,1) not null primary key , type varchar(20) not null default 'N/A' , name varchar(100) not null default 'N/A' )   insert row_construct (type,name) values ('A', 'Garments') insert row_construct (type,name) values ('B', 'Sports Equipments') insert row_construct (type,name) values ('C', 'Cosmetics') insert row_construct (type,name) values ('A', 'Swim Wears') insert row_construct (type,name) values ('A', 'Sports Garments')   --but with the help of row constructors we can insert the same data using single insert statement as below   insert row_construct (type,name) values ('A', 'Garments'),('B', 'Sports Equipments'),('C', 'Cosmetics'),('A', 'Swim Wears'),('A', 'Sports Garments'); --we can also write the same as insert row_construct select type, name from (values ('A', 'Garments'),('B', 'Sports Equipments'),('C', 'Cosmetics'),('A', 'Swim Wears'),('A', 'Sports Garments')) as rowtables (type, name);   --Limitations: --We cannot select from other table in values clause as below it will only give "Incorrect syntax near the keyword 'select'."   insert row_construct select type, name from ( values (select 'A', 'Garments'),('B', 'Sports Equipments'),('C', 'Cosmetics'),('A', 'Swim Wears'),('A', 'Sports Garments') ) as rowtables (type, name);   insert row_construct (type,name) values (select 'A', 'Garments'),('B', 'Sports Equipments'),('C', 'Cosmetics'),('A', 'Swim Wears'),('A', 'Sports Garments');   --We can insert from multiple tables only by using UNION/UNION ALL clause as below   insert row_construct select 'A', 'Garments' union select 'B', 'Sports Equipments' union select 'C', 'Cosmetics'

Say we have a stored procedure that we want to copy across multiple databases. One way would be to create the script and execute in all the databases. But if Databases are many it can be time consuming and efforts to change Database name every time before executing the script will be more. One better way is writing a script that can create the Database object in selected Databases or all Databases at once.
[More]

In this article we will see the way execution order works in SQL server, mainly when TOP, Union and order by clause are utilized in a query.

Say we have an Employee table

create table Employees

(EmployeeID int identity(1,1) NOT NULL

,EmployeeName varchar(50) NOT NULL default 'N/A'

,Department varchar(50) NOT NULL default 'N/A'

,Salary float NOT NULL default 0) [More]

Performance is something every Developer struggles with at some point or other. In order to do optimization we can see query plan. This article will demonstrate as how we can create the function that will show us the query plan and how we can use it to see plans of objects that are highly used

[More]

In this article we will see as how we can query the result from relational table when the input parameter is sending data in the form of string separated by a delimiter. [More]

Table Valued Parameters are new to SQL Server 2008. They allow us to pass table variable to stored procedure. The same function can be accomplished by temporary tables, but table valued provide better performance and complete dataset in a table can be passed to stored procedure. [More]

In this article, we will see how we can increase the memory of SQL Server that it can use. Usually, default setting is 2147483647 bytes i.e. approximately 2.15 GB that SQL Server can use, but it may be required to increase the ram utilization as some of the query operations can be memory intensive and we want to avoid any Timeouts.

[More]

Suppose we want to keep handy a function that will calculate and give date going backward or forward any number of days from current date. So say, we want to get a date for 100 days prior to current date we can keep this function handy. [More]