February 24, 2012 at 1:43 pm
I have a sql server table named raw17. Is there a way to create a new table that increments the number in the table name by 1. Example, the new table becomes raw18, the next new table after that becomes raw19, etc.
Any ideas would be appreciated.
February 24, 2012 at 2:06 pm
Pretty sparse on details here...there are several ways you could do this. Seems to me you going to be forced to use dynamic sql to create all your tables with this schema so the easiest would be to have a control table. It probably doesn't need anything more than a single int column with 1 row. This would simply contain either the last one created or the number for the next one.
I certainly hope you don't too many tables all named basically the same thing. That could easily become a total nightmare to maintain.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 24, 2012 at 2:24 pm
instead of multiple tables, why not a single table with your 1-19 etc indicators as a column value in that table? wouldn't that be easier to use and maintain?
why do you feel you need to break the data out into separate tables, that i assume have the same identical structure/columns?
Lowell
February 24, 2012 at 2:43 pm
quick simple example if you are still using that route:
--build some sample tables
create table raw4 (id int,somedata varchar(30) )
create table raw14(id int,somedata varchar(30) )
create table raw7 (id int,somedata varchar(30) )
--a counter to capture the new value
declare NextCounter int
SELECT
--the last table plus one will be the new table index
NextCounter = MAX(TableNum) + 1
FROM (SELECT
name,
--replacing the name "raw" leaves just the table number
--converting it to an int lets us sort it correctly
CONVERT(INT, REPLACE(name, 'raw', '')) AS TableNum
FROM sys.tables
WHERE name LIKE 'RAW%')X
print NextCounter --prints 15
declare @sql varchar(2000)
SET @sql = 'create table raw' + convert(varchar,NextCounter) + '(id int,somedata varchar(30) )'
print @sql
--uncomment to really start building the table(s)
--exec(@sql)
Lowell
February 25, 2012 at 11:51 am
CELKO (2/25/2012)
Lowell (2/24/2012)
instead of multiple tables, why not a single table with your 1-19 etc indicators as a column value in that table? wouldn't that be easier to use and maintain?why do you feel you need to break the data out into separate tables, that i assume have the same identical structure/columns?
Let me echo that. A table models either a set of entities or a relationship. For example, Integers are a set; the idea of different sets of integers is absurd.
But if you don't understand that a table is not a file, you mimic mag tapes and put sequential labels on them liek we did in the old days. This is teh wrong way to use SQL.
Actually, there's a valid reason for doing such a thing as what the OP wants to do. It makes maintaining indexes much easier and quicker and it allows for some of those "non Ninja" programmers you once spoke to me of to make mistakes without necessarily crippling the server with a performance-bomb. It's called "table partitioning" and in the Standard Edition of SQL Server, it's done using multiple tables with a particular constraint to limit what can go into each table (usually by date range) and requires the use of a "partitioned view".
To continue your mag tape analogy, what worked well then works well now. Instead of sequentially loading a dozen tapes to find the data for a given month, partitioned views allow for the equivalent of loading just the tape for the given month you want.
In comparison, the method you just seconded smacks of being a "dreaded" EAV. 😛
Not all of the "old ways" are bad, Joe. You just have to learn how to use them in a new world. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
February 25, 2012 at 12:02 pm
gary.morey (2/24/2012)
I have a sql server table named raw17. Is there a way to create a new table that increments the number in the table name by 1. Example, the new table becomes raw18, the next new table after that becomes raw19, etc.Any ideas would be appreciated.
Gary,
The code that Lowell wrote will do what you want. Before you jump in and start using that, though, I'd strongly recommend that you lookup "Partitioned Views" (if you have the SQL Server Standard Edition) and "Partitioned Tables" (if you have the SQL Server Enterprise Edition). There are some extreme advantages to using either but you have to know what you're doing to take advantage of the technologly. As I mentioned in my post to Celko, these structures can drastically cut maintenance time and be much more forgiving to people who write code than if all the data is in a single table but they do have to be setup correctly.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 25, 2012 at 12:05 pm
Jeff Moden (2/25/2012)
Actually, there's a valid reason for doing such a thing as what the OP wants to do. It makes maintaining indexes much easier and quicker and it allows for some of those "non Ninja" programmers you once spoke to me of to make mistakes without necessarily crippling the server with a performance-bomb. It's called "table partitioning" and in the Standard Edition of SQL Server, it's done using multiple tables with a particular constraint to limit what can go into each table (usually by date range) and requires the use of a "partitioned view".
Although i didn't explain as well as you, Jeff, that was what i wanted to double check with the original poster on; there's certainly reasons to do that, which is why i provided the code example in my second post, but there might be a logic hole where he's making the work harder on himself than he needs to do;
Hopefully he'll post back with some of the biz logic why he might need to do that.
Lowell
February 25, 2012 at 12:16 pm
CELKO (2/25/2012)
Have you looked at the indexing on partitioned tables? I attended a DB2 talk a few weeks ago on their implementation. Basically, each partition gets it own index on the partitioning columns, so if you select on that value, you get the effect of a single table for that value.But you can index across all the rows on non-partition values, too. Really nice stuff.
I agree. It's great to have such choices. It's been awhile since I've had to partition a table in SQL Sever but it also had the choice of using partitioned indexes to match the table partitions or not. Because of the maintenance implications, I always chose to use partitioned indexes based on the partitioned column.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 27, 2012 at 7:08 am
Thank you all for your posts. I believe that I have all of the information that I need to proceed. You may consider this topic closed.
February 27, 2012 at 8:28 am
Jeff Moden (2/25/2012)
gary.morey (2/24/2012)
I have a sql server table named raw17. Is there a way to create a new table that increments the number in the table name by 1. Example, the new table becomes raw18, the next new table after that becomes raw19, etc.Any ideas would be appreciated.
Gary,
The code that Lowell wrote will do what you want. Before you jump in and start using that, though, I'd strongly recommend that you lookup "Partitioned Views" (if you have the SQL Server Standard Edition) and "Partitioned Tables" (if you have the SQL Server Enterprise Edition). There are some extreme advantages to using either but you have to know what you're doing to take advantage of the technologly. As I mentioned in my post to Celko, these structures can drastically cut maintenance time and be much more forgiving to people who write code than if all the data is in a single table but they do have to be setup correctly.
i decided to look up 'partitioned views' and got this link:
http://msdn.microsoft.com/en-us/library/ms190019.aspx
it explains a scenario where you have multiple tables, one for each month of a specific year. These tables are then UNION'd together in a view.
When querying that view, specifying only information from certain tables in the WHERE clause, It states: 'The SQL Server query optimizer recognizes that the search condition in this SELECT statement references only rows in the May1998Sales and Jun1998Sales tables. Therefore, it limits its search to those tables.'
If i had a similar situation, but the individual tables were split by a column called 'DateChnaged':
select * from year2010
union all
select * from year2011
etc
and in the WHERE clause i said:
WHERE DateChanged between '2010-01-01' and '2010-08-01'
would it recognise that i am only needing info from the year2010 table and ignore everything else?
February 27, 2012 at 4:21 pm
It depends on what the "partitioning constraint/column" is setup as. If you have your tables setup by month and year, then your scenario would only look at 8 tables. If you have your tables setup by year (as your view seems to indicate), then your scenario would only look at 1 table (which would have more rows in it because it covers all 12 months) for 2010 and the 2011 table would be mostly ignored.
Of course, that will work like that only if you've setup the "partitioning constraint/column" correctly. It's not difficult to do.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 28, 2012 at 4:32 am
thanks Jeff.
i've used this link as my reference:
http://msdn.microsoft.com/en-us/library/aa933141(v=sql.80).aspx
so i've set a CheckConstraint on 6 of the 7 tables that are mentioned in my View:
table2005
table2006
table2007
table2008
table2009
table2010
([ChangedDate]>='2008-01-01 00:00:00' AND [ChangedDate]<='2008-12-31 23:59:59')
obviously, the year in the CheckConstraint changes depending on which table its against.
The 7th table contains data from the year 2011 onwards, so currently i dont want to put a constraint on it.
In the view, the tables are UNION All'd.
Have i covered everything, or missed something out? i'm interested to see how this will increase performance of my query.
February 28, 2012 at 6:49 am
davidandrews13 (2/28/2012)
The 7th table contains data from the year 2011 onwards, so currently i dont want to put a constraint on it.
You must include the constraint on the 7th table or it won't necessarily work as a partitioned view.
Make no mistake, partitioned views are NOT a performance panacea and I apologize if I made you think that. You'll see a major increase in performance only if you had table scans before and only if the date references in the WHERE clauses, etc, are SARGable (can use a SEEK). If you had index seeks everywhere, you might not see a performance gain. If your code requires you to look at all rows across all dates, you won't see a performance increase there, either.
Code that was properly doing seeks before will see some increase in performance because the B-Tree will likely be smaller but it won't be an Earth shattering gain.
The larger benefit will be in how you maintain the indexes on the tables. Since the tables prior to 2011 will have few, if any, inserts or other modifications, the indexes won't ever become fragmented and the statistics won't ever need to be updated. It's a huge time saver for nightly maintenance.
If you ever end up archiving by date, the partitioned views make that a snap as well because things are already separated by date. You just don't call the table in the view anymore and then you can move the table to an archive database. You could also build a different partitioned view across the archived tables and the current tables if you ever need to do such a thing.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 28, 2012 at 6:53 am
davidandrews13 (2/28/2012)
([ChangedDate]>='2008-01-01 00:00:00' AND [ChangedDate]<='2008-12-31 23:59:59')obviously, the year in the CheckConstraint changes depending on which table its against.
Also, your check constraint is incorrect. In theory, it's possible that something in the last second of 2008 could be in the wrong table using the constraint you've given. The correct constraint for 2008 would be as follows...
(ChangedDate >= '2008' AND ChangedDate < '2009')
Yeah... using year only is a shortcut for the first of the given year.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 28, 2012 at 7:14 am
Jeff Moden (2/28/2012)
davidandrews13 (2/28/2012)
The 7th table contains data from the year 2011 onwards, so currently i dont want to put a constraint on it.You must include the constraint on the 7th table or it won't necessarily work as a partitioned view.
Make no mistake, partitioned views are NOT a performance panacea and I apologize if I made you think that. You'll see a major increase in performance only if you had table scans before and only if the date references in the WHERE clauses, etc, are SARGable (can use a SEEK). If you had index seeks everywhere, you might not see a performance gain. If your code requires you to look at all rows across all dates, you won't see a performance increase there, either.
Code that was properly doing seeks before will see some increase in performance because the B-Tree will likely be smaller but it won't be an Earth shattering gain.
The larger benefit will be in how you maintain the indexes on the tables. Since the tables prior to 2011 will have few, if any, inserts or other modifications, the indexes won't ever become fragmented and the statistics won't ever need to be updated. It's a huge time saver for nightly maintenance.
If you ever end up archiving by date, the partitioned views make that a snap as well because things are already separated by date. You just don't call the table in the view anymore and then you can move the table to an archive database. You could also build a different partitioned view across the archived tables and the current tables if you ever need to do such a thing.
i see.. I'm still learning how to read the execution plans so that i can get my queries running more efficiently.
I got my code that uses the View to use Index Seeks instead of a Table Scan, before setting up the CheckConstrants, but it now seems to have a 'Key Lookup' which has a cost of 43%.
I guess the reason for the Key Lookup is that i'm brining back all columns where i'm actually only including a few of the columns in the Index.
If nothing else, your advice will help me to be more knowledgeable, but i'm sure i see a slight performance increase. maybe thats just my imagination 🙂
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply