Impact of empty tables on database size and performance

Hello All,

When creating my database I have modeled some of the tables after the Adventureworks sample database.

There are some fields or entire tables in Adventureworks that I do not see an imediate use for, however; I would hate to ommit them to find out later they would have been benificial. (.eg territory table).

In general terms what would the impact be on size and performance of a database which contains tables or fields that do not contain data.

Thanks for your help!

Blair:

There will be little impact from tables that are not used provided these tables are small and do not grow. If the tables are large then they can impact your storage requirements and the size and speed of your backups.

Fields that are not used are a different story -- especially if the fields are populated with large amounts of "spectator" data. These "spectator" fields will then impact the amount of space required for their tables and will have an impact on the amount of time required for a "table scan". This becomes amplified to some extent if these fields participate in any indexes. If your fields are all null the amount of bloating will not be as great.

Another problem that can occur if you leave in fields that have at the moment no use is that in the future it can become tempting for somebody to start "using" these fields in ways that are not planned. Eliminating unused fields can stop this kind of "cobbling" before it takes place.

I would suggest that you are better off with a well designed, well thought out database in which each column has a specific meaning with a specific intended use.

Images in DataBase or Server Folder?

hi everyone,

I'm currently developing a website

and have a lot of images to use there(obviously :P),

until now I have in some tables in my sql server db where exists a column which stores the path to: 'image folder + filename'

And my question is: what are the advantages of using binary images in database instead of this approach to the problem, i.e., having images stored in a server folder.

Thanks in advance.

If you store the image in the table, your queries against the table could get slower. your DB size could grow much larger. The adv is if you backup/restore you dont need to explicitly move your images or worry about updating the path if yuu just stored the path value. If you store the images on a shared drive, you can make them accessible from any production server so if you had to move your db's you just update the path with an UPDATE statement. This can save considerable time when backing up or restoring.

These are some obvious advantages/disadvantages. What works for you may not work for others. so you need to investigate thoroughly (by sufficient testing) which approach suits you best.

|||thanks for the answer.sql

Image and tables

Is it possible to save jpgs in a table if the DataType is set to varbinary(MAX), also how would I save the images to the table.

Thanks

Hi,

yes that possible, but depends on the used coding language, would be nice if you could provide some more information about that.

-Jens Suessmeyer.

http://www.sqlserver2005.de

|||SQL Server backend with Access VBA code|||

Hi,

sorry for picking up your issue this late, but sometime posts get out of sight in my mailbox and the weekend is preferable my time to pick them all up. Have a look at some sample code here:

http://www.freevbcode.com/ShowCode.Asp?ID=1802

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

IIF Statment to compare two values in joined tables

Hi

I'm trying to compare two varchars to check if they are the same, if they are the same then the color must turn red, if not then they must remain black

SELECT *

from members m, client c

where C.ClientID = m.ClientID

AND c.ClientID in (87,86)

AND m.email in ('dassd@.fdskjh.com','asdfas@.sdfd.net', etc...)

my results will give me two of the same email addresses but with different ClientID's, now when it

finds the same email it needs to make them both "RED"

Please help, any advice would be helpful

Kind Regards

Carel Greaves

Two ways occur to me:

- add a COUNT(c.ClientId) and GROUP BY m.email to your sql query, then check that count when displaying the data in your report - if the count is greater than 1 then you can colour the email field red

- use a row group in your report, grouping by email address, then have the other fields following - but if you use this approach you won't need to colour the email addresses red, as it will be instantly obvious which addresses are assigned to more than one client

ignoring keys while Truncating tables

Hello everyone.

I'm working with a customers business application which is developed in MSSQL. The system has over 170 tables and there is no documentation by those who created it. Now the problem is that I have to write script that deletes all the data in all the tables but since there are foreign keys defined in the tables I can't delete the data. Ofcourse I can figure it out eventually by testing back and forth in which order I have to delete the data in the tables but since there are over 170 tables that could take a very long time.

Does anyone now how I can solve this?? is there for examaple a way to make SQL server to ignore the foreign key lookup? What can I do?? is there any way which I can see in what order I should delete the data in the tables?

appritiate any help or comments.

Thanks.

\Homan1. script out your deletes and run the script 170 times, eventualy you will clear out all the tables.

2. use Enterprise Manager to generate a diagram of all the tables in the database. This would tell you the exact order you would need to follow to get everything deleted.

3. in Enterprise Manager and Query Analyzer you can look up the dpendencies of any object. In EM right click on any object, select all tasks, dependencies, in QA press "F8" to show the object browser, select and object drill down till you see dependencies.

4. use sp_depends on all 170 tables/view to figure out the parents and or child relationships.|||If you are going to have to reload the database you are going to want to know your foreign keys.|||For reference of cascade delete refer to SQL TEam (http://www.sqlteam.com/item.asp?ItemID=8595) link.

Refer to this Code (http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=641&lngWId=5) and modify to accomplish the task.

HTH|||disable your fk checking before deleting data, and re-enable them after.