How many columns is ok?

Hello!

I have a database table containing about 40 columns, mostly bit data type, some nvarchar and smallint columns...

Is it ok to have so many columns in one table and how does the number of columns affect database perfomance? Wouldn't it be better to split this table?

Thank You!
JannoPossible? Yes. OK. Not really.

My guess is that you need to work on normalizing your database. For example, all those bit datatypes might be better served off in their own table, with a vertical not horizontal, design. This would much more easily allow you to add traits (bits) in the future.

What is the nature of the table, and what is being stored in all of those BIT columns?|||Pjmcb,

Basically it's a databese for flat rental site. Those BIT columns are used to store True/False type values.

In case I'm going to store those values verticaly in a separate table... could you please explain me how does it exactly works - I'm a SQL Server newbie...

Ads Table structure:
------------
AdIDint
AdTypesmallint
UserIDnvarchar
Activebit

AdDetails Table structure:
------------
AdIDint
Areasmallint
StreetNamenvarchar
Neighbourhoodnvarchar
Citynvarchar50
PostCodenvarchar15
AvailableFromdatetime
Rentmoney8
MinimumStaysmallint
Depositmoney8
PropertyTypenvarchar
Floorsmallint2
Parkingbit1
AdditionalCostmoney
Bathroomssmallint2
Bedroomssmallint2
Gasbit1
Cablebit1
TVbit1
Phonebit1
Closetbit1
Furnishedbit1
AirConditioningbit1
Bathbit1
Fireplacebit1
Balconybit1
Elevatorbit1
Washerbit1
KitchenAccessbit1
Storagebit1
Gardenbit1
Poolbit1
Gymnbit1|||You might have a second table, and call it, say Traits.

In this table, you might include each of the bit traits, along with an ID for each trait.

Then, you could have a join table to link each particular trait with each type of apartment. You would only have records in the join table for those traits that applied to a specific apartment.

You might have a second table for manetary items, with a similar join table, and a third with rooms or similar information.

With your join tables for these last two items, you could include additional information. For example, you might have:

Rooms
--
RoomID
RoomName
RoomDesc

(which might contain: Bedroom; Bathroom; Closets; Laundry Room; Kitchen; Living Room)

AdRooms
--
AdID
RoomID
RoomCount

Obviously, I'm throwing this out off the top of my head. But when you need to run an ad featuring an apartment with a SunRoom, all you need to do is add a record to your Rooms, table, and assign that value to your new apartment. You don't have to go in and change your database design.

Your retrieval of data might be a bit more complicated (actually, I would argue that ultimately, once you get comfortable with SQL, it will be easier), but growing the application will be much easier.|||I completely agree in principle with normalizing this, but it does depend on how the data is being used. if every single ad always lists each of those categories with a yes/no, it may make sense to leave it denormalized. bits don't hurt you that much as far as performance, the table is still relatively narrow. normalizing does make maintenance much better, as you point out, when you want to add additional attributes. it also makes the query more complex, although not necessarily slower. so those are just some tradeoffs I would keep in mind.

cs