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.

Image in SQL2000

Hello!
What function in SQL SERVER 2000 to test an image field if empty or not? Tried NoT NULL but no success.
I want to return only items with pictures...
THanksDid you try DATALENGTH function?sql

IIF Issues

I am trying to format dates that are in string format. When I use:
=IIF(Fields!MyDate.Value = "","EMPTY","STRING")
I get EMPTY and STRING right where they should appear. When I use:
=IIF(Fields!MyDate.Value = "","",Format((Convert.ToDateTime(Fields!MyDate.Value)), "MMM. d, yyyy"))
I get #Error for all of the empty strings and correctly formatted dates for
everything else... what am I doing wrong?Try inserting spaces( i used 5 spaces)...I had the same problem, it does not
seem to like "".
~aparna
"MER78" wrote:
> I am trying to format dates that are in string format. When I use:
> =IIF(Fields!MyDate.Value = "","EMPTY","STRING")
> I get EMPTY and STRING right where they should appear. When I use:
> =IIF(Fields!MyDate.Value => "","",Format((Convert.ToDateTime(Fields!MyDate.Value)), "MMM. d, yyyy"))
> I get #Error for all of the empty strings and correctly formatted dates for
> everything else... what am I doing wrong?|||IIF always evaluates two sides of an expression, which is where the error
occurs. You can create a custom code function that has a regular If
statement, and call it like so:
=code.MyFunctionName(Fields!MyDate.Value)
Right-click on the report background and click Properties to get to the code
area.
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"MER78" <MER78@.discussions.microsoft.com> wrote in message
news:02AA454F-2678-4E0E-A910-F639933B6788@.microsoft.com...
>I am trying to format dates that are in string format. When I use:
> =IIF(Fields!MyDate.Value = "","EMPTY","STRING")
> I get EMPTY and STRING right where they should appear. When I use:
> =IIF(Fields!MyDate.Value => "","",Format((Convert.ToDateTime(Fields!MyDate.Value)), "MMM. d, yyyy"))
> I get #Error for all of the empty strings and correctly formatted dates
> for
> everything else... what am I doing wrong?|||YUCK... that's so much more of a hassle... what's the point of creating
branching code if it executes all cases?
"Jeff A. Stucker" wrote:
> IIF always evaluates two sides of an expression, which is where the error
> occurs. You can create a custom code function that has a regular If
> statement, and call it like so:
> =code.MyFunctionName(Fields!MyDate.Value)
> Right-click on the report background and click Properties to get to the code
> area.
> Cheers,
> '(' Jeff A. Stucker|||I've had success using = nothing
Hope this helps.
"MER78" wrote:
> YUCK... that's so much more of a hassle... what's the point of creating
> branching code if it executes all cases?
> "Jeff A. Stucker" wrote:
> > IIF always evaluates two sides of an expression, which is where the error
> > occurs. You can create a custom code function that has a regular If
> > statement, and call it like so:
> >
> > =code.MyFunctionName(Fields!MyDate.Value)
> >
> > Right-click on the report background and click Properties to get to the code
> > area.
> >
> > Cheers,
> >
> > '(' Jeff A. Stucker|||I've had success using = nothing
hth
"MER78" wrote:
> YUCK... that's so much more of a hassle... what's the point of creating
> branching code if it executes all cases?
> "Jeff A. Stucker" wrote:
> > IIF always evaluates two sides of an expression, which is where the error
> > occurs. You can create a custom code function that has a regular If
> > statement, and call it like so:
> >
> > =code.MyFunctionName(Fields!MyDate.Value)
> >
> > Right-click on the report background and click Properties to get to the code
> > area.
> >
> > Cheers,
> >
> > '(' Jeff A. Stucker|||I tend to agree with you. But that's how IIF was designed eons ago for
VB/VBScript, not really for branching, but inline evaluation and population
of data.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"MER78" <MER78@.discussions.microsoft.com> wrote in message
news:9925D007-E1CD-47C6-8A48-FE06F826CDD1@.microsoft.com...
> YUCK... that's so much more of a hassle... what's the point of creating
> branching code if it executes all cases?
> "Jeff A. Stucker" wrote:
>> IIF always evaluates two sides of an expression, which is where the error
>> occurs. You can create a custom code function that has a regular If
>> statement, and call it like so:
>> =code.MyFunctionName(Fields!MyDate.Value)
>> Right-click on the report background and click Properties to get to the
>> code
>> area.
>> Cheers,
>> '(' Jeff A. Stucker

IIF and empty fields

Hi, apologies if this is a silly mistake on my part, but... I'm having trouble with working out if a field is empty

I've been trying to use "SELECT newfield = IIF(IsEmpty(table.field), 0, 1) ... " to return a bit value of whether the string field has something in it..

The syntax check says that IsEmpty is not a valid function - but I dont understand how it can be... am I missing something here?

I also tried using IIF(table.field = '', 0, 1) and that returns "invalid syntax near ="

Please help because its really confusing me, ThanksHi,

IsEmpty and IIF are Analysis Server functions, so you can't use it in reqular T-SQL. I don't seen any indication in your message that your using OLAP, so I'll assume you're using regular T-SQL with relational data.

What do you consider to be empty? A null value? Or what is commonly considered to be no data for a particular datatype (empty string for strings, zero for numerics, etc.)?

If nulls, consider using the ISNULL function. If the value is null it returns the value of your choice, otherwise the non-null value. The NULLIF function can help in some cases.

But if empty is an empty string or zero, etc., you can compare those values. Or use the CASE block, which lets you return different values based on various conditions.

I suspect that you'll find what you need in these T-SQL features.

Don|||ok, thanks a lot. explains why its not working anyway :)