One of the efficient ways of improving performance is partitioning the table that allows storing data in more than one physical file. Consider a scenario where a table contains 1 million rows, when a query is executed against that it might have to scan huge #'s of rows before it returns the result. Separating data in different filegroups will help scanning all the physical files at a same time. [More]

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]

Recently, I came across a situation where I had to produce space used across tables in a database. Now my database contains more than 500 tables and to execute sp_spaceused for every table was time consuming. So below is the faster way that can work. [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]

To offer better manageability and more security for login and user maintenance Microsoft separated roles into Database roles and Application Roles. Application Roles are mainly used by applications and they do not contain any database users. In this article we will see as how we can create an Application Role in SQL server and use it in a web application.
[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]