|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Parameterize table name without constructing dynamic query?Hi. We have a series of tables with exactly the same structure. The names of
the tables all start with the same prefix but end with a number. Is there a way to write a normal query (not a dynamically constructed one) against a table where the name of the table is parameterized? E.g. declare @suffix int set @suffix = 10 select * from mytable@suffix McGeeky wrote:
> Hi. We have a series of tables with exactly the same structure. The names of Obvious question: Why?> the tables all start with the same prefix but end with a number. > Is there a way to write a normal query (not a dynamically constructed one) No. But you can create a UNION of all the tables and query that. Take a> against a table where the name of the table is parameterized? > look at the partitioned views topic in Books Online. -- David Portas, SQL Server MVP Whenever possible please post enough code to reproduce your problem. Including CREATE TABLE and INSERT statements usually helps. State what version of SQL Server you are using and specify the content of any error messages. SQL Server Books Online: http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx -- > Obvious question: Why? Not sure why. One table represents data from one depot. The company has manydepots. I presume it was a performance related decision as the tables can grow pretty huge and having one table per depot gives flexibility. UNION is fine for selecting, but how about performing inserts and updates? Show quote "David Portas" <REMOVE_BEFORE_REPLYING_dpor***@acm.org> wrote in message news:1141333330.206816.170690@i40g2000cwc.googlegroups.com... > McGeeky wrote: > > Hi. We have a series of tables with exactly the same structure. The names of > > the tables all start with the same prefix but end with a number. > > Obvious question: Why? > > > Is there a way to write a normal query (not a dynamically constructed one) > > against a table where the name of the table is parameterized? > > > > No. But you can create a UNION of all the tables and query that. Take a > look at the partitioned views topic in Books Online. > > -- > David Portas, SQL Server MVP > > Whenever possible please post enough code to reproduce your problem. > Including CREATE TABLE and INSERT statements usually helps. > State what version of SQL Server you are using and specify the content > of any error messages. > > SQL Server Books Online: > http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx > -- > McGeeky wrote:
> > Obvious question: Why? "Felxibility"!? Forgive me. You obviously meant that ironically in view> > Not sure why. One table represents data from one depot. The company has many > depots. I presume it was a performance related decision as the tables can > grow pretty huge and having one table per depot gives flexibility. of the question on your subject line :-) I've no idea what you count as "pretty huge"? The biggest single table I personally recall was about 200 million rows so maybe yours are bigger than that, in which case you certainly should be considering partitioning. In SQL Server 2005 you also have the option to create a partition scheme for the table. Under either solution you won't need to parameterize the table name. -- David Portas, SQL Server MVP Whenever possible please post enough code to reproduce your problem. Including CREATE TABLE and INSERT statements usually helps. State what version of SQL Server you are using and specify the content of any error messages. SQL Server Books Online: http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx -- The table structure is what it is for historic reasons. No chance that it
will be changed. There would be at least 200 million rows between all 20 tables. Show quote "David Portas" <REMOVE_BEFORE_REPLYING_dpor***@acm.org> wrote in message news:1141334153.935942.295430@u72g2000cwu.googlegroups.com... > McGeeky wrote: > > > Obvious question: Why? > > > > Not sure why. One table represents data from one depot. The company has many > > depots. I presume it was a performance related decision as the tables can > > grow pretty huge and having one table per depot gives flexibility. > > "Felxibility"!? Forgive me. You obviously meant that ironically in view > of the question on your subject line :-) > > I've no idea what you count as "pretty huge"? The biggest single table > I personally recall was about 200 million rows so maybe yours are > bigger than that, in which case you certainly should be considering > partitioning. In SQL Server 2005 you also have the option to create a > partition scheme for the table. Under either solution you won't need to > parameterize the table name. > > -- > David Portas, SQL Server MVP > > Whenever possible please post enough code to reproduce your problem. > Including CREATE TABLE and INSERT statements usually helps. > State what version of SQL Server you are using and specify the content > of any error messages. > > SQL Server Books Online: > http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx > -- > You can update a partitioned view, so long as it has a primary key unique
across all tables. If retrofitting the original design is not an option, then at least it can be abstracted and "normalized" through the use of views. Show quote "McGeeky" <a***@anon.com> wrote in message news:u73LJ8jPGHA.720@TK2MSFTNGP14.phx.gbl... > The table structure is what it is for historic reasons. No chance that it > will be changed. There would be at least 200 million rows between all 20 > tables. > > -- > McGeeky > http://mcgeeky.blogspot.com > > > "David Portas" <REMOVE_BEFORE_REPLYING_dpor***@acm.org> wrote in message > news:1141334153.935942.295430@u72g2000cwu.googlegroups.com... >> McGeeky wrote: >> > > Obvious question: Why? >> > >> > Not sure why. One table represents data from one depot. The company has > many >> > depots. I presume it was a performance related decision as the tables > can >> > grow pretty huge and having one table per depot gives flexibility. >> >> "Felxibility"!? Forgive me. You obviously meant that ironically in view >> of the question on your subject line :-) >> >> I've no idea what you count as "pretty huge"? The biggest single table >> I personally recall was about 200 million rows so maybe yours are >> bigger than that, in which case you certainly should be considering >> partitioning. In SQL Server 2005 you also have the option to create a >> partition scheme for the table. Under either solution you won't need to >> parameterize the table name. >> >> -- >> David Portas, SQL Server MVP >> >> Whenever possible please post enough code to reproduce your problem. >> Including CREATE TABLE and INSERT statements usually helps. >> State what version of SQL Server you are using and specify the content >> of any error messages. >> >> SQL Server Books Online: >> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx >> -- >> > > > Is there a way to write a normal query (not a dynamically constructed one) If you're not against creating identical tables with different names, why > against a table where the name of the table is parameterized? are you against using dynamic SQL? So as to make the syntax far more readable. I suppose the answer to my
question is that it is not possible to parameterize only the table name of a query in Transact SQL? Show quote "Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message news:OfcWt2jPGHA.2012@TK2MSFTNGP14.phx.gbl... > > Is there a way to write a normal query (not a dynamically constructed one) > > against a table where the name of the table is parameterized? > > If you're not against creating identical tables with different names, why > are you against using dynamic SQL? > > > So as to make the syntax far more readable. I suppose the answer to my Correct, SQL Server must understand the object name when it parses the > question is that it is not possible to parameterize only the table name of > a > query in Transact SQL? statement. The only way to get around this without changing the schema in any way is to use dynamic SQL (in which case SQL Server doesn't need to understand anything about the statement until it tries to actually run it -- when you better pass it a valid table name). However, I must agree with David that there are better solutions. Even 200 million rows is manageable, and does not necessarily require breaking out into multiple identical tables. Even if the data is too large (or you need to query in multiple ways that indexes won't help), there are other ways to do it. Leaving it as is just for the sake of not changing it is silly, imho. We used to store programs/code/data on punch cards, audio cassette tapes, floppy disks, etc. We learned better ways of doing things, and changed our ways, instead of finding ways to prevent from having to change. Thanks Aaron and JT for your ideas.
I agree that the structure is not ideal. I am not involved in the database design in anyway, but I will mention to the DBAs some of the options you have both proposed. My argument, as the designer of a system that is to load data in to their tables, is that my job will be more complex and therefore cost more if they do not alter their table structures. Show quote "Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message news:ua43pDkPGHA.208@tk2msftngp13.phx.gbl... > > So as to make the syntax far more readable. I suppose the answer to my > > question is that it is not possible to parameterize only the table name of > > a > > query in Transact SQL? > > Correct, SQL Server must understand the object name when it parses the > statement. The only way to get around this without changing the schema in > any way is to use dynamic SQL (in which case SQL Server doesn't need to > understand anything about the statement until it tries to actually run it -- > when you better pass it a valid table name). > > However, I must agree with David that there are better solutions. Even 200 > million rows is manageable, and does not necessarily require breaking out > into multiple identical tables. Even if the data is too large (or you need > to query in multiple ways that indexes won't help), there are other ways to > do it. > > Leaving it as is just for the sake of not changing it is silly, imho. We > used to store programs/code/data on punch cards, audio cassette tapes, > floppy disks, etc. We learned better ways of doing things, and changed our > ways, instead of finding ways to prevent from having to change. > > That's the spirit! :-)
Show quote "McGeeky" <a***@anon.com> wrote in message news:ehS6fJkPGHA.3164@TK2MSFTNGP11.phx.gbl... > Thanks Aaron and JT for your ideas. > > I agree that the structure is not ideal. I am not involved in the database > design in anyway, but I will mention to the DBAs some of the options you > have both proposed. My argument, as the designer of a system that is to > load > data in to their tables, is that my job will be more complex and therefore > cost more if they do not alter their table structures. > > -- > McGeeky > http://mcgeeky.blogspot.com > > > "Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in > message > news:ua43pDkPGHA.208@tk2msftngp13.phx.gbl... >> > So as to make the syntax far more readable. I suppose the answer to my >> > question is that it is not possible to parameterize only the table name > of >> > a >> > query in Transact SQL? >> >> Correct, SQL Server must understand the object name when it parses the >> statement. The only way to get around this without changing the schema >> in >> any way is to use dynamic SQL (in which case SQL Server doesn't need to >> understand anything about the statement until it tries to actually run > it -- >> when you better pass it a valid table name). >> >> However, I must agree with David that there are better solutions. Even > 200 >> million rows is manageable, and does not necessarily require breaking out >> into multiple identical tables. Even if the data is too large (or you > need >> to query in multiple ways that indexes won't help), there are other ways > to >> do it. >> >> Leaving it as is just for the sake of not changing it is silly, imho. We >> used to store programs/code/data on punch cards, audio cassette tapes, >> floppy disks, etc. We learned better ways of doing things, and changed > our >> ways, instead of finding ways to prevent from having to change. >> >> > > You can wrap a view around the partitioned tables. When new tables are
added, then you can execute DROP VIEW.. CREATE VIEW .. and add the new table reference to the UNION. http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_06_17zr.asp http://msdn.microsoft.com/library/default.asp?URL=/library/techart/PartitionsInDW.htm http://www.microsoft.com/technet/prodtechnol/sql/2005/spdw.mspx I don't know if this would apply to your specific situation but... If the purpose of this table partioning is to seperate daily / monthly operational data from historical / reporting data, then you may also want to consider at this point implementing only 2 partitioned tables; one operational one historical. The advantage of this is that you are dealing with a fixed number of tables and there is no need for dynamic SQL or occasionally re-creating views. For example: CREATE VIEW Sales_ITD AS SELECT * FROM Sales_Current UNION ALL SELECT * FROM Sales_History After each reporting cycle, new transactions can be inserted into history and then deleted from operations. The operational system would need access only to a subset of data in Sales_Current, and the reporting system could bang away at Sales_History with no deadlocking or (only if inception to date reporting is needed) view_Sales_ITD. Show quote "McGeeky" <a***@anon.com> wrote in message news:OKr0qvjPGHA.1580@TK2MSFTNGP09.phx.gbl... > Hi. We have a series of tables with exactly the same structure. The names > of > the tables all start with the same prefix but end with a number. > > Is there a way to write a normal query (not a dynamically constructed one) > against a table where the name of the table is parameterized? > > E.g. > > declare @suffix int > > set @suffix = 10 > > select * from mytable@suffix > > -- > McGeeky > http://mcgeeky.blogspot.com > > > >> If the purpose of this table partitioning is to seperate daily / monthly operational data from historical / reporting data ..<< No, in products that have serious support for partitions, the goal isparallelism. if you want to see the current data versus the history, then use a VIEW for the current data and epend on the clustering on the temporal columns to make the curretn data fast. Yes, clustering on transaction date would be advisable with either one
consolidated table or multiple partitioned tables. However, in a similar situation from my past, I found that index seeks and querying of current transactions were getting progressively longer as more historical transactions were accumulated in a large clustered table; even when I defragmenting indexes and extents on occasion. Partitioning out the current transactions yielded the results I was looking for, and it required practically no additional maintenace so long as I kept it simple with only the 2 tables (current and historical). There are two more considerations in favor of partitioning: 1. Applications that require querying of the historical transactions in mass or even in total (ex: refreshing OLAP cubes) or querying that is at least partially non-indexed (data mining or ad-hoc user defined queries / pivot tables), sometimes acquire page or even table locks, which would result in at least occasional blocking of the transactional system. 2. Another advantage of partitoning tables is that they can be split into seperate file groups and physical disk drives for better I./O performance. What we are really starting to talk about here is not just splitting transactions in one virtual table, but the broader concept of archiving historical data out of the OLTP system and into a data warehousing system. Show quote "--CELKO--" <jcelko***@earthlink.net> wrote in message news:1141434533.177625.13680@j33g2000cwa.googlegroups.com... >>> If the purpose of this table partitioning is to seperate daily / monthly >>> operational data from historical / reporting data ..<< > > No, in products that have serious support for partitions, the goal is > parallelism. > > if you want to see the current data versus the history, then use a VIEW > for the current data and epend on the clustering on the temporal > columns to make the curretn data fast. > >> We have a series of tables with exactly the same structure. The names of the tables all start with the same prefix but end with a number. <<This design flaw is called attribute splitting. You have taken one set of entities and put them into multiple tables when they should be in one table. I usually see this when an old programmer is mimicking a mag tape file system. They re-discover the IBM tape labels as table names. How many old programmers from the DASD days are still coding? What you are
actually seeing are young computer science majors who are still learning from old text books. That great hair is the result of too many late nights and weekends spent debugging denormalized databases. Show quote "--CELKO--" <jcelko***@earthlink.net> wrote in message news:1141409122.508263.97140@z34g2000cwc.googlegroups.com... >>> We have a series of tables with exactly the same structure. The names of > the tables all start with the same prefix but end with a number. << > > This design flaw is called attribute splitting. You have taken one set > of entities and put them into multiple tables when they should be in > one table. > > I usually see this when an old programmer is mimicking a mag tape file > system. They re-discover the IBM tape labels as table names. >  |
|||||||||||||||||||||||
Other interesting topics