Dynamic tables VS. Static Tables

[ManicQin]

What is the tradeoff of building my tables dynamicly, I have a maximum tables which means that the maximum dynamic tables = the static tables DB.

[exterminator]

What is the tradeoff of building my tables dynamicly, I have a maximum tables which means that the maximum dynamic tables = the static tables DB.

By dynamic tables I assume you are referring to tables that are created by the queries on the fly. These are usually used as intermediate tables or worktables. Some queries also make temporary worktables for the various manipulations with the data to provide you with the resultset. Practically, work tables and temp tables are very much needed (prepared out of various joins etc.). And there is no real need of them once the query has finished since the information that is stored in them is intermediate and we can easily deduce them again when they are queried for (they could have longer life depending upon situations and needs and frequency of usage). So this is what exactly is the scenario where you might find the need of those.

As for trade-off, I don't really understand what you are comparing it with? With static tables? They are not comparable to a lot extent because they both serve very different purposes. Static tables comprise the schema of your database but dynamic tables do not. Hope I did not confuse you and understood you well. Regards.

[trends]

Hi,

You mean to say abt the Dynamic tables.Considering sql server 7.0 there we are using the temporary tables this will be saved in the tempdb.But in sql server 2000 there is a data type called table.This is more efficient than using the temp table.tyr this

[ManicQin]

Thank you both for your answers but apperntly I didnt explain my self right.
When I mean dynamic tables I'm talkin about tables that are created by code in RUN-TIME and not short term temp tables (who are self created by the DB).
the problem is that I know that i will need these tables and I know what tables i will be creating.
Think about creating a survey with one million Q's BUT they are all optional.
Every Table is representing a question (by assumption).
because the questions are optional I cant predict which Questions will be answered.
If from the start I will create ALL the tables i will have a hugh wasted DB.
if i create a table only when i need it (first time someone answered the Q) i will start with a slim DB.
two more things 1) from time to time i query a filled survey.
2) after a survey was queried i delete it from the tables and when a table is empty i delete A table!

By tradeoff i mean: Does a Dynamic Table (NOT A TEMP RUN-TIME created one) takes more space in the memory/H.D. and Is it slower. (Not optimized)

[srinika]

Rather than creating a table (or a few related tables) for a "question", can't u create a record for each "question" and related data [like answer(s), importance, ...]

then u can delete the records when u don't need those.

[ManicQin]

ever heared the word theoretical?
offcourse that the solution for the problem is ridiculous it was just an example...

[ManicQin]

thanks.

and again the solution was only to explain why should i need dynamic tables. it is a bad bad bad bad solution and you can stop analyzing it couse it's only a exsample

[exterminator]

ever heared the word theoretical?
offcourse that the solution for the problem is ridiculous it was just an example...

Even your theoretical example is not a very good one. What you provided as an example is practically very very incorrect and when you are designing a database you need to think of the practical issues that you might face. If you are not considering them, you are going to land up with a mess almost always. Theory is not always going to provide you the best performance.

As far as your query is considered. Yes, you can create database tables on the fly and not in the tempDB (the temp tables that you are thinking). There are hash-tables also, but they are also created in the tempdb and are there for the given session for a user. I think you don't need them. You would be simply calling another stored procs or something like a DDL quer with a "CREATE TABLE..." to create databases on the fly. They would remain there in the database until you do a "DROP TABLE....". There is no problem with that. You can go ahead with this. But seriously speaking, what you are thinking of is still a little bit vague in my mind. And if you are really trying to do something like you explained in your last post, you should follow what srinika suggested and that would be:
Create a table consisting of questions - and if the details of users taking the survey is not needed then you could simply put a numeric fields for all the answers to a question. And as and when a user selected an answer for a particular question what you could do is simply update the count with 1. Like below:

QUESTION_ID  	QUESTION   	OPT_A 	OPT_B 	OPT_C 	OPT_D
1   		"What is..."  	100     131    	23      45
2               "Where is."   	22      436    	22      78

Something like this. It depends upon a lot of things. You may not have simple options (A, B, C, D) as answer, you might be having text answers as well. In that case you will probably need an ANSWERS table and link it to the QUESTIONS TABLE using a foriegn key in QUESTION_ID (there are many possibilities). Once you are sure of what exactly you need, fire away your doubts so that you could get better help. Hope this helps. Regards.

[ManicQin]

thanks exterminator for your unproductive replay.
I'm really happy that you took your time to teach me DB 101.

[erickwidya]

thanks exterminator for your unproductive replay.

that's not a good manner..we only try to help u here

for the tempTable..

Does a Dynamic Table (NOT A TEMP RUN-TIME created one) takes more space in the memory/H.D. and Is it slower. (Not optimized)

Temporary Tables
SQL Server supports temporary tables. These tables have names that start with a number sign (#). If a temporary table is not dropped when a user disconnects, SQL Server automatically drops the temporary table. Temporary tables are not stored in the current database; they are stored in the tempdb system database.

There are two types of temporary tables:

Local temporary tables
The names of these tables begin with one number sign (#). These tables are visible only to the connection that created them.

Global temporary tables
The names of these tables begin with two number signs (##). These tables are visible to all connections. If the tables are not dropped explicitly before the connection that created them disconnects, they are dropped as soon as all other tasks stop referencing them. No new tasks can reference a global temporary table after the connection that created it disconnects. The association between a task and a table is always dropped when the current statement completes executing; therefore, global temporary tables are usually dropped soon after the connection that created them disconnects.

Many traditional uses of temporary tables can now be replaced with variables that have the table data type.

as it said there that temporary table is dropeed imediately if user not explicitly drop the temporary table..so the performance should not decrease much since the memory is reallocated again after it dropped

PS:just my opinion..

and if u mean dynamic tables is not like the quoted then the answer is YES for all ur question above

good luck