Newbie question about initial table size

Hi all,

I've worked with informix for a very long time and this is my first aproach to sql server. I have an extremely simple design for a "small" database and at this moment I'm creating the tables, in informix I can assign a first extent and next extent size to the creation of the table so if your volume and growth analisys is good you can basically be sure that you will allways have contigous space on disk for your table. I'm readin BOL to see if I have that feature here but can't seem to find anything similar. Does that mean that my table data will be "fragmented" all over the primary and secondary files every time I load into them? Would it be a good practice to simulate the extents by creating a secondary file for each table with the size I require?

Any coments will be greatly appreciated :)

Luis TorresSpecifying that the db grow in reletively large chunks (so that the file doesn't grow very often) can reduce it's fragmentation. A seperate file for very large tables or for tables that get updated a lot is a good idea. Also you can set up amaintenance plan to run (daily, weekly, etc..) that can reorganize (optimize) the data & indexes.|||The size allocation for tables is done on the extent level. It means that if the last page of the initial extent is filled, a logically contiguous set of 8x8K pages is allocated for the new data. There may be fragmentation between the extents, and depending on your RAID level and array architecture the physical continuity of pages, but "logically" each extent is comprised of 8 continuous pages sitting in a row ;)|||Thanks pshisbey and rdjabarov for your comments, they are greatly appreciated :)

Luis Torres

Newbie question about indexes

Hi ,

I have following statement :

SELECT * FROM Table WHERE Col1=@.Var1 AND Col2=@.Var2 ... AND ColN=@.VarN

How should I design indexes for best performance ?
(
Add one index on columns Col1 till ColN
or add N indexes, first for column Col1, second for Col2, ...
)

Thanks, for your suggestions

How many rows do you expect it to return?
Do you prefer retrieval speed over update speed?
Do you have other queries that might benefit from individual indexes?

|||Strictly from your query perspective, you will only need one index with the key (col1...colN). However, this index will be useless if say col1 is missing from your where clause. So you need to evaluate full set of queries that you plan to run on this table. Also, as Eric points out, you will need to evaluate the cost of updating indexes if you have lots of updates/inserts/deletes
Thanks|||

Hi,

I expect to return c. 100 rows. (Would indexing strategy differs if I have return whole table ?)
Retrieval speed is priority.
I have no other queries for this table.

Thank you

|||Here's what the optimizer guys have to say:

One index should be fine if he always has every column in the WHERE condition and he is doing equality matching.

This will give two main plan options:

1. index lookup + fetch
2. table scan

It will be a cost-based decision.

Newbie needing help ASAP with width issue in sql report.

I am building a Sql Report in VS.net 2006 I have run into an issue I have 258 colums it am using the wizard table generator in design mode. the problem is the screen will on allow 160 inches wide I need to mak this larger to 300 inches the purpose of the report is to sort data and them save to csv format is there a better way or can some on tell me how to increase the size of the width the properties tab will not allow me to explain pass 160 inches. Some one please help me thanksWhy not tackle the job another way ? Have a look at http://www.codeproject.com/useritems/filehelpers.asp "An easy to use .NET library to read/write strong typed data from fileswith fixed length or delimited records (CSV). Also has support toimport/export data from different data storages (Excel, Acces,SqlServer, MySql)"|||The issue I am having is a task was given to me to pull data from an invoice module out of SQL and creat a 288 field output of data in a csv format. The problem is we on have about 100 fields data the rest have to be blank and all this has to be in an order so . I was going to build a report in th sql reports in my business intell create the headers for all 288 files and create the expression in the correct placement create several sort paramerters then I was going to just save as csv format this would create the csv file with the correct order and blank fields separated by the commas. the problem is I dont know how to creat empty fields on an output to csv.|||

Suppose your table FRED has three fields A, C and D and you want to output 5, use:

SELECT A, ' ' AS B, C, ' ' AS D, E FROM FRED

That will create 2 empty fields - you can readily extend the technique to create 188 blank fields.

|||

Thank youy this makes sense. The only question is I may need it to look like this

"john", "Smith","","","","","","","404","555-5555","123 Main Street","","completed",

If I under stand the your code I can select A,B,' ' AS C,' ' AS D,' ' AS E, ' ' AS F, ' ' AS G, ' ' AS H,I,J,K,' ' AS L,M FROM TABLE

|||

>>The only question is I may need it to look like this: "john", "Smith","","","","","","","404","555-5555","123 Main Street","","completed",
The quoting takes places aroung each value, whether it is occupied or empty.

>>If I under stand the your code I can select A,B,' ' AS C,' ' AS D,' ' AS E, ' ' AS F, ' ' AS G, ' ' AS H,I,J,K,' ' AS L,M FROM TABLE

Yes! I put a space between the quote marks for clarity; you will not need to do this.

Newbie need help for database design

Hi,

I am designing a table for classrooms with many features. Following is
the detail of the table (let's say the name of table is CRoom):

1. Room Name (key)
2. Building name (foreign key)
3. Capacity
4. Chairs
5. T-arm chairs
6. Tables
7. Desks
8. Lectern
9. 35 mm slide projector
10. Dual 35 mm slide projector
11. 3/4" video player
12. 1/2" video player
13. Chalkboard
14. Markerboard
.....

My questions are:
a. Should I put everything in the same table?
or
b. If there is chairs in a classroom then there will be no T-arm
chairs there,
vice versa. So should I create a sepearte table for chairs and
t-arm chairs
and use the key for this as a foreign key in the CRoom table?
c. For number 11 and 12, some classrooms have only 3/4" video player,
some
classrooms have only 1/2" video player and some classrooms have
both.
Should I create another table for them and set value 1 for 3/4"
video player
2 for 1/2" video player and 3 for both? Then use the value 1, 2, 3
in the
CRoom table to represent the three differnt situations. If this is
not
proper, then how should I deal with this one?

Thanks a lot in advance.>> I am designing a table for classrooms with many features. <<

Some of the attributes are part of the room itself, like square
footage, maximum allowed seating, and chalkboards that are bolted to
the walls. You can put those attributes into the Classrooms table.
Things that can be moved around (slide projector, video player) would
be in other tables and assigned to classrooms by an inventory location
table.

Newbie Design Question

I am a newbie in BI and have the following design question:

I have a scenario where I have a Sales fact table linked to a customer dimension. The customers some customers attributes might change and I would like to keep history of those attributes, such as Marital Status.
One way is to store the Marital Status as attribute in the customer dimension and enter multiple records for each customer whenever the marital status changes, but I should then add date and status fields to the customer dimension.
Another way is to store the Marital Status as a separate dimension and link it directly to the fact table. While maintaining only one record for each unique customer in the customer dimension, and in this case no Date and Status fields are needed on customer.
What is the best way of implementing this scenario? Are there any pros cons for each one, Or Maybe there is a better way of solving it?

And suppose I use the first method where multiple customer records are kept, while processing to get the sales done for people that are married, some processing should be done to only consider each customer once (Only Status is Active). This is through MDX I suppose.

I am sure this issue is typical and basic but just wanted to get some guidelines.

Appreciate your help,
Grace

Hi Grace,

These design issues are often discussed as part of the "Slowly Changing Dimension" techniques in dimensional modelling, for example in the Kimball article below. Typically these technique ensure that only the appropriate row corresponding to a dimension member is considered (one way is by using surrogate keys):

http://www.intelligententerprise.com/db_area/archives/1999/990308/warehouse.jhtml

>>

When A Slowly Changing Dimension Speeds Up
Ralph Kimball

...

>>

|||

Thanks Deepak for the guidance.

Newbie - Report Design help.

Hi folks,
New to RS, but have this really annoying colleague b*tching about
coversheets and TPS reports. (Sorry, off-topic).
Anyways, I've been ranting about the wonders of SQL RS to him, and then
he turns up wanting me to actually do a real report for him. In the
sample below, I'm using the Northwind database.
Basic idea is: You select a year in a combobox and run the report.
The report then shows the quanties sold for each product for that year
in one column, and in the next column it shows the equivalent data for
the previous year.
Tables used in this sample: Orders, OrderDetails and Products.
Hope someone can help me. :-)
-McWawa, the next President of The United States of America.
---
Select Year: 1999 |
2000 |
2001 | <- these are in a combobox
2002 |
2003 |
Products | 2001* | 2000**
---
Produkt A | 145 | 84
Produkt B | 200 | 129
Produkt C | 101 | 88
.. | ... | ...
.. | ... | ...
Produkt Z | 140 | 31
* = The Year selected in the combobox
** = The Year BEFORE the on selected in the combobox
---What exactly do you need help with?
This is a fairly straightforward report. Just create a parameter to allow
the user to select a year, run your query based upon the parameter, and
populate your simple table with the results.
What specifically are you having a problem with?
"McWawa" <n@.dachanche.ever> wrote in message
news:uuStdiczGHA.3512@.TK2MSFTNGP04.phx.gbl...
> Hi folks,
> New to RS, but have this really annoying colleague b*tching about
> coversheets and TPS reports. (Sorry, off-topic).
> Anyways, I've been ranting about the wonders of SQL RS to him, and then he
> turns up wanting me to actually do a real report for him. In the sample
> below, I'm using the Northwind database.
> Basic idea is: You select a year in a combobox and run the report.
> The report then shows the quanties sold for each product for that year in
> one column, and in the next column it shows the equivalent data for the
> previous year.
> Tables used in this sample: Orders, OrderDetails and Products.
> Hope someone can help me. :-)
> -McWawa, the next President of The United States of America.
>
> ---
> Select Year: 1999 |
> 2000 |
> 2001 | <- these are in a combobox
> 2002 |
> 2003 |
>
> Products | 2001* | 2000**
> ---
> Produkt A | 145 | 84
> Produkt B | 200 | 129
> Produkt C | 101 | 88
> .. | ... | ...
> .. | ... | ...
> Produkt Z | 140 | 31
>
> * = The Year selected in the combobox
> ** = The Year BEFORE the on selected in the combobox
>
> ---|||> This is a fairly straightforward report. Just create a parameter to allow
> the user to select a year, run your query based upon the parameter, and
> populate your simple table with the results.
> What specifically are you having a problem with?
Well, I think my problems may be caused by my lack of tsql experience.
If you (or anyone) could point me in the right direction with the query, I
think I could piece the report together from that.
McWawa|||Solved it through my query. :-)