April 12, 2009 at 3:14 am
is there a way to make a query on a Dynamic table name like this :
Select * from Messag2008+@MonthName
but withought using a dynamic query which will be activated by Exec?
Thanks
Peleg
April 12, 2009 at 4:00 am
peleg k (4/12/2009)
is there a way to make a query on a Dynamic table name like this :
Select * from Messag2008+@MonthNamebut withought using a dynamic query which will be activated by Exec?
No.
To do dynamic table names requires dynamic SQL, using EXEC or sp_executesql. In this case, since there are only 12 month names, you could use an IF statement to run the correct query
IF @MonthName = 'Jan'
SELECT <Column List> FROM Messag2008Jan
IF @MonthName = 'Feb'
SELECT <Column List> FROM Messag2008Feb
....
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 12, 2009 at 7:50 am
peleg k (4/12/2009)
is there a way to make a query on a Dynamic table name like this :
Select * from Messag2008+@MonthNamebut withought using a dynamic query which will be activated by Exec?
Thanks
Peleg
Hey Peleg,
Yes. Sort of.
Take a look at CREATE/DROP SYNONYM.
You will still need to use dynamic execution of some sort to create and drop the synonym, but your regular SQL will be fine, e.g. SELECT * FROM dbo.synCurrentMonthTable.
Cheers,
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 12, 2009 at 8:06 am
OK
Thanks both of you
April 12, 2009 at 8:19 am
Be careful with the synonym approach - it can only work if all queries using the procedure will use the current month table. If some look to different months, then the procedure can only be called by one process at a time.
Another approach is to create a view with all of the tables unioned:
CREATE VIEW dbo.Messag2008 AS
SELECT {columns} FROM dbo.Messag200801
UNION ALL
SELECT {columns} FROM dbo.Messag200802
UNION ALL
SELECT {columns} FROM dbo.Messag200803
...
GO
Then, select from the view with:
SELECT {columns} FROM dbo.Messag2008 WHERE month = '01';
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
April 12, 2009 at 8:36 am
Jeffrey Williams (4/12/2009)
Be careful with the synonym approach - it can only work if all queries using the procedure will use the current month table.
Hey Jeffrey,
The last time I used synonyms in this way, it was part of the implementation of a sliding-window maintenance procedure, sitting on top of a partitioning scheme (obviously!) For some reason, I had it in my head that this was what the OP was doing too. I don't know why.
The synonym approach is, like many things, a horses-for-courses thing. There is nothing to say a synonym has to point to the 'current' month for example, or that there can be only one synonym. The OP didn't provide enough detail on the problem at hand to know which approach may be best suited. It's just another tool in the box, and you are right to advise caution.
Finally, the partitioned view you showed would benefit from appropriate CHECK constraints, just for anyone that doesn't know that already 🙂
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 12, 2009 at 8:37 am
Another option if you're using Enterprise edition is to drop the idea of multiple tables and consider partitioned tables.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 12, 2009 at 8:42 am
This is not a serious post - don't do this!
Yet another option (though not a good one!) would be to construct a table valued function to return the correct data based on a @MonthName input parameter.
:eeek:
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 12, 2009 at 8:51 am
Paul White (4/12/2009)
Yet another option (though not a good one!) would be to construct a table valued function to return the correct data based on a @MonthName input parameter.
How would you do that?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 12, 2009 at 9:00 am
GilaMonster (4/12/2009)
How would you do that?
I wouldn't 😉
But if I did (excuse the rushed code, it's 3am):
create table jan (a int)
create table feb (b int)
insert jan values (1)
insert feb values (2)
go
create function dbo.f (@month as varchar(30)) returns @t table (z int)
as
begin
insert @t (z)
select a from jan where @month = 'January'
union all
select b from feb where @month = 'February'
return
end
go
select *
from dbo.f ('January')
select *
from dbo.f ('February')
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 12, 2009 at 9:11 am
Definitely not a good idea. Personally it's not something I would even suggest here as someone might use it without realising why it's a bad idea.
Multi-statement table valued function that returns lots of rows (lots = anything over about 100) perform terribly because the table variables that they use to return values don't have statistics and hence the optimiser makes very bad decisions about optimal execution plan.
http://sqlinthewild.co.za/index.php/2008/08/12/views-or-functions/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 12, 2009 at 9:25 am
GilaMonster (4/12/2009)
Definitely not a good idea. Personally it's not something I would even suggest here as someone might use it without realising why it's a bad idea.Multi-statement table valued function that returns lots of rows (lots = anything over about 100) perform terribly because the table variables that they use to return values don't have statistics and hence the optimiser makes very bad decisions about optimal execution plan.
Sigh. Yes I know. I have edited my previous post to make it clearer, just in case.
I only posted the code because you asked.
It's also possible to read all possible data into XML and run an XQuery... 😀
/P
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 12, 2009 at 9:42 am
Paul White (4/12/2009)
It's also possible to read all possible data into XML and run an XQuery... :-D/P
Paul - I'd quit now while I still had the chance 😀
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
April 12, 2009 at 9:53 am
Paul White (4/12/2009)
It's also possible to read all possible data into XML and run an XQuery... 😀
If you're going to make suggestions as to how something can be done, please either show some code or reference somewhere that does. Just saying 'It can be done using X' doesn't help anyone reading this, unless they happen to be very familiar with X, which, considering that some of your suggestions are rather way out is not likely.
That's why I asked for code for the suggestion of functions.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 12, 2009 at 10:13 am
GilaMonster (4/12/2009)
Paul White (4/12/2009)
It's also possible to read all possible data into XML and run an XQuery... 😀If you're going to make suggestions as to how something can be done, please either show some code or reference somewhere that does. Just saying 'It can be done using X' doesn't help anyone reading this, unless they happen to be very familiar with X, which, considering that some of your suggestions are rather way out is not likely.
That's why I asked for code for the suggestion of functions.
I post in my own style ok? Sometimes tongue-in-cheek, sometimes with lots of detailed examples, sometimes without.
Your style clearly differs, but that's just how it is I'm afraid.
If 'way out' is your way of saying creative and interesting, then thanks.
/P
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply