Sql-server – SQL Server 2012 Execution Plan

First of all: stored procedures in SQL Server are NOT "pre-compiled" or anything.

Just like an ad-hoc SQL query, a stored procedure is analyzed when it's first used, an execution plan is determined and cached.

When the stored procedure is executed again, then that pre-existing execution plan is reused. Same applies to a properly parametrized SQL ad-hoc query.

The point is: as long as the actual SQL statement is IDENTICAL (down to the last comma or space), then a possibly pre-existing, cached execution plan is reused and the whole step of determining an execution plan can be skipped. If the SQL statement text varies - even by a single space - the whole process of analyzing and coming up with an execution plan for that query is run again.

I don't know about how Java handles it's prepared statements, but in C# using ADO.NET, when you run a query something like

SELECT * FROM user_master WHERE user_name = @userName

and you supply a value for @userName (e.g. 'vicky.thakor'), then the SQL Server will NOT received what you seem to think it will receive - the parameter is NOT replaced in the query text - instead, SQL Server will execute this as

EXEC sp_executesql @stmt, N'@userName', @userName = 'vicki.thakor'

SQL Server will receive a parametrized query and a list of parameters and their values.

Therefore, if you call this same query again, with a different value for @userName, the actual SQL statement text is identical and the cached execution plan is reused - only the value of the parameter has changed.

This might help you to create simple dynamic query for partitioned tables using COALESCE function of SQL Server

Step1: Create function to split tokens

CREATE FUNCTION [dbo].[fnSplit]
(
@List VARCHAR(8000),
@Delimiter CHAR(1) = ','
)
RETURNS @Temp1 TABLE
(
ItemId INT IDENTITY(1, 1)
NOT NULL
PRIMARY KEY,
Item VARCHAR(8000) NULL
)
AS BEGIN

DECLARE @item VARCHAR(4000),
@iPos INT

SET @Delimiter = ISNULL(@Delimiter, ',')
SET @List = RTRIM(LTRIM(@List))

-- check for final delimiter
IF RIGHT(@List, 1) <> @Delimiter 
-- append final delimiter
SELECT @List = @List + @Delimiter

-- get position of first element
SELECT @iPos = CHARINDEX(@Delimiter, @List, 1)

WHILE @iPos > 0
BEGIN
-- get item
SELECT @item = LTRIM(RTRIM(SUBSTRING(@List, 1, @iPos - 1)))
IF @@ERROR <> 0 
BREAK
-- remove item form list
SELECT @List = SUBSTRING(@List, @iPos + 1, LEN(@List) - @iPos + 1)
IF @@ERROR <> 0 
BREAK
-- insert item
INSERT @Temp1
VALUES ( @item )
IF @@ERROR <> 0 
BREAK
-- get position pf next item
SELECT @iPos = CHARINDEX(@Delimiter, @List, 1)
IF @@ERROR <> 0 
BREAK
END
RETURN
END


GO

Step 2: Set parameter value @varyears and execute query

DECLARE @varyears VARCHAR(200)
SET @varyears = '2012, 2011, 2013 ,2014'


DECLARE @combinedString VARCHAR(MAX)

SELECT  @combinedString = COALESCE(@combinedString + ' UNION ALL ', '')
        + CHAR(13) + ' Select ID, sum(amount) TotalAmount, ''' + item
        + ''' as [Year] FROM CustomerTransaction' + item
        + '_tbl  group by ID ' + CHAR(13) --group by clause added to your query for using aggregate function 
FROM    dbo.fnSplit(@varyears, ',')


EXEC ( @combinedString )