Char limit to create a view
-
Hi all,
When i try to create a view with partition tables i had a problem with the limit of chars.
I had declared a string variable with 8000 (size) to join all the tables (selected by cursor) to create a view with those tables. See the example:
create view view01 as
select * from table01
union all select * from table02
union all select * from table03
union all select * from table04
....
But...i have 74 tables now and i intend to add until the limit (126 tables inside the view) and create script to recreate the view everyday at 00:30 am.
How can i create this view, better, how can i create this "string" to create this view? The size of this string will have more than 4000 chars.
Thanks a lot.
Fabiano
-
Do you create a table every day?
Sure you need separate table for each day data?
If yes, you better stay with Excel.
In SQL Server you need 1 table with "Date" column. Having clustered index on this column.
Then you may select data for any date range using simple query without all those stupid dynamic views.
_____________
Code for TallyGenerator -
Fabiano,
What is the pattern of you table names? And are they in the same database or different databases? I have the same problem with some 3rd party software at work and it's easy to solve but I need to know what the pattern of your table names are...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
Hi all,
First of all, sorry about my English
Well, i think that my words aren't correct. Let's try again.
I have more or less 100 distincts data inside the table. I read that if you take on table wich has millions of records and your select is lazy, try to use "partitionary tables". With this, take one field data and create a new table every day with the partition. Like:
table_DDMMYYY ok?
Now, well, i had created 100 tables with each data ok? I want to create a view to with those 100 tables, like:
select * from table_DDMMYYYY
union all
select * from table_DDMMYYYY
union all
select * from table_DDMMYYYY
union all
select * from table_DDMMYYYY
...
OK?
Well, with my script, i had declared one variable nvarchar(4000) to concatenate all the sentences (SQL sentences) but i can't. It working like:
select * from table_DDMMYYYY
union all
select * from table_DDMMYYYY
union all
select * from table_DDM
Could you see the difference with that sentence with the other one above? The string is not complete.. That is my problem. Now I solved with the "top 30" after the "select" word. With only 30 record i know that it works but this is not what i want.
Again, thank you so much for help me.
Fabiano
-
you have millions of rows per day, but does that justify one table per day ?
to answer your question
create several varchar(4000) and concat them in a dynamic exec like
exec (@str1 + @str2 + @str3 ... )
* Noel -
Fabiano,
Ok... that's about 40 characters per select including the UNION ALL... that's only 100 tables if you use VARCHAR(4000) or NVARCHAR(4000). So, use VARCHAR(8000) and just use EXEC... don't use sp_ExecuteSQL which requires NVARCHAR.
Now, the real trick is to automate this puppy... it's simple when you know how
- --===== Declare local variables
DECLARE @SQLCreate VARCHAR(8000) --Holds the CREATE VIEW line
DECLARE @SQLSelectList VARCHAR(8000) --Holds a SELECT * with a union all for each table
- --===== Set the CREATE VIEW in the dynamic SQL
SET @SQLCreate = 'IF OBJECT_ID(''yourview'') IS NOT NULL DROP VIEW yourview'+CHAR(13)
+ 'CREATE VIEW yourview AS'+CHAR(13)
- --===== Create a list of 126 (Today +125) SELECT * with a union all for each table using a little PFM
SELECT @SQLSelectList = ISNULL(@SQLSelectList + ' UNION ALL'+CHAR(13),'')
+ 'SELECT * FROM '+Table_Name
FROM Information_Schema.Tables
WHERE Table_Name >= 'Table_'+REPLACE(CONVERT(VARCHAR(10),GETDATE()-125,104),'.','')
- --===== All set... execute the dynamic SQL to drop and recreate the view
EXEC (@SQLCreate+@SQLSelectList)
There are a couple of things wrong with your whole setup... first, I agree with the others that have stated that it's just not necessary to split your tables like this... I mean, what the heck!
You're putting them back together with a view!!! 
And this also means that you will have to create indexes for each table as they are created instead of having just one table to index. It's a maintenance nightmare!Second, if you absolutely have to do it this way because of some requirement you have to meet for your boss or it's actually 3rd party shortsightedness that created this mess, then at least you or the 3rd party should use ISO (yyyymmdd) formatting (CONVERT format #112) to create the date part of each file name so that it's sortable.
You will need to replace the word "yourview" throughout the code with what you actually want to name the view.
Last but not least, I have not tested this code exactly the way it's written... but this is the same type of code I use to meet a 3rd party requirement. If you have problems with the code, post back.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions - --===== Declare local variables
-
Hi, Thank you so much. I'll verify and test
I think that is important to do because with just on table our query stant for more or less 3 minutes and all of ours reports are by web (Internet Explorer, etc.) well, the "time out" is our enemy.
I thought to change all the applications to client/server but it will be hard, months (maybe one year) of word will be spend and i don't have time
for this. By the way, thanks for everything.
Regards
Fabiano
-
> I read that if you take on table wich has millions of records and your select is lazy, try to use "partitionary tables"
What you read is not true.
Just not to say "crap".
Clustered index on datetime column will do that partitioning much better than all those tables.
Insert all data from your 100 tables into one, set up the index and try your SELECT.
You'll see that not "time out" but lack of knowledge about SQL Server mechanics is your real enemy.
_____________
Code for TallyGenerator -
Heh, heh, heh.... leave it to Serqiy to just say it like it is
Splitting the tables like this is, in fact, "crap" --Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
My mate in University would say:
"It's not true. Make a note - I didn't say 'crap' "
_____________
Code for TallyGenerator
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply