Tukukkk

There is a select query that runs repeatedly with the first column in the select clause being the only thing which gets replaced by other column and the rest of the query structure remain the same.
So that this query plan get cached, is there any way that we can parametrize the first column.

Select c1,p,a
From table1,

Select c2,p,a
From table1

Select c3,p,a
From table1

Select c4,p,a
From table1
Every time the query runs ,only the first column is the one changing . Is there any way to optimize this kind of query?

I must admit, that this smells a bit. Might be that there are better approaches, but we do not know your use cases.

You can try this:

DECLARE @mockup TABLE(c1 INT, c2 INT, c3 INT, p VARCHAR(100),a VARCHAR(100));

INSERT INTO @mockup VALUES(1,2,3,'Row 1','blah 1')

                         ,(11,22,33,'Row 2','blah 2')

                         ,(111,222,333,'Row 3','blah 3');



DECLARE @FirstColumn VARCHAR(10)='c3';



SELECT CASE @FirstColumn WHEN 'c1' THEN c1

                         WHEN 'c2' THEN c2

                         WHEN 'c3' THEN c3

                         ELSE NULL END AS DynamicFirstColumn

      ,p

      ,a

FROM @mockup;

The idea is to use a CASE to decide which column is seen in the first place following a parameter. If you want to see a varying column name too (I used DynamicFirstColumn) you can

• create a VIEW for each case or


• use dynamically created SQL.

As a workaround you can include the handed in parameter and return it with the result set. In this case the consumer would know, which column was used...

SELECT CASE @FirstColumn WHEN 'c1' THEN c1

                         WHEN 'c2' THEN c2

                         WHEN 'c3' THEN c3

                         ELSE NULL END AS DynamicFirstColumn

      ,p

      ,a

      ,@FirstColumn --<-- Here we include the source we use above

FROM @mockup