|
|
Posted by Stu on 12/20/05 14:21
To be honest, I don't know. I never considered doing it the other way,
because conceptually, by using the partiitioned view, my application is
only dealing with one entitiy. It just seemed easier to manage than
building intelligence into my INSERT and UPDATE statement to determine
where the data belongs. I just INSERT into the view, and the check
constraints handle the slicing and dicing. If I need to archive a
day's worth of data, I copy the appropriate table to my archive, drop
the table, and rebuild the view.
Of course, having re-read this thread, I'm not 100% sure that
partitioned views will support the number of tables you need. One of
the limitations of using my method of dynamically building the view is
that the view statemnt is a varchar(8000) statement; if there an
excessive number of tables, the view may not be built correctly. I use
a similar method to the code snippet you posted above; the base SQL
statement you posted has about 40 characters in. 40 * 251 = 10040, so
the snippet you suggest will probably run into the same issue.
I have seen some tricks to get around this limitation of dynamic SQL
(like EXEC (@sql1 + @sql2), but haven't tried them; there are solutions
to every problem, however. The questions I would ask myself would be:
1. How often am I going to add new tables to this structure?
2. What's the easiest way to manage interaction with the data?
3. What's the differentiation between the slices?
4. How often will I need to retrieve information from multiple slices
at once?
Just thinking aloud.
Navigation:
[Reply to this message]
|