Category Archives: Sql Server

Creating Insert scripts in Sql Server 2008 R2

There were times when I wanted to experiment something in my project and needed a separate local DB with the real time data. It used to take lot of time to achieve it. but with Sql Server 2008 R2 management studio itself provides this feature handy.

This feature is built into the Generate Script utility, but the functionality is turned off by default and must be enabled when scripting a table.

Here are the steps to create Insert Data script using SQL Management Studio 2008 R2. You do not need any pugins or third party tools for this.

  1. Right Click on Database name like ‘Master’
  2. Select Tasks -> select Generate Scripts
  3. Next select the tables or object to which you want to generate script
  4. Go to set scripting options ,then click on advance button you find different options with catogories.
  5. In the General category: go to type of data to script
  6. we find 3 options for that
    1. Schema Only
    2. Data Only (really useful)
    3. Schema and Data
    4. Select the appropriate option and click on OK.

You will then get the create table statement and all of the INSERT statements for the data straight out of SSMS.

for a full discussion thread over this refer to the

For more insights refer to

http://stackoverflow.com/questions/982568/what-is-the-best-way-to-auto-generate-insert-statements-for-a-sql-server-table

Hope it helps!!

Advertisements

Parameter sniffing in Sql server

Hi All,

I work for a project which involves very huge data reporting using SSRS. Business users reported a bug which was very difficult to analyze.
Whenever there was a new report added to report server and a certain user requests report data for smaller territory like New Jersey to see his sales figures for his territory. The report renders very quick. But whenever a user requests for a bigger territory like Texas it takes more time almost 10 times (phew!).

when i started looking into the issue and found something interesting which i have only read in my academic days.

SP was like this.

CREATE PROCEDURE TerritorySales
@TerritoryId int
AS
BEGIN
SELECT sale
FROM dbo.SaleDetails
WHERE TerritoryId = @TerritoryId
END

Even though all looks fine, there is a catch here. @TerritoryId is the input parameter which is used directly into the where condition. So here is what happens with Sql server.

1. developer executes the stored proc. Sql server compiles the SP and save it.
2. User calls the SP with certain parameters (say TerritoryId which is smaller). it sure will have less data.
3. Sql server creates an execution plan which is optimized for this input parameter (smaller territory) and saves it.
4. Next time a user call the same SP for a bigger territory (more data). Sql server still uses the same execution plan which was saved earlier (optimized for small data). It takes unusually more time.

Solution to this problem (called Parameter sniffing) is very simple.

1. Assign the input parameter to a local variable.
2. use that local variable in the sql query.

CREATE PROCEDURE TerritorySales
@TerritoryId int
AS
BEGIN
DECLARE @i_TerritoryId int

SET @i_TerritoryId = @TerritoryId

SELECT sale
FROM dbo.SaleDetails
WHERE TerritoryId = @i_TerritoryId
END

Even though it all looks one and the same thing, it surely make a difference in certain data intensive queries.

I hope it helps.

Praveen