How many records mostly can a table has?

Hi:
In SQL Server 2000, how many records a table can have at most? I need to
create a table in that there are 3 fields: one is identity int (a key), one
is varchar(18) and another is bit.But I am not sure if I can store 10424128
records in a table? Is it too big? If not, or not good, any idea to handle
big records?
Thanks
Q.>> But I am not sure if I can store 10424128 records in a table? Is it too
There is no documented limit on the number of rows in a table. Merely a
million rows, is not a big deal at all.
However, if this table is well thought out, you might want to include a
UNIQUE NOT NULL constraint on the VARCHAR(18) column or on a combination of
the VARCHAR(18) column and the bit column to prevent duplications.
Anith|||There is no set limit on the number of rows in a table. 100s of
millions of rows are very common. Your table is actually quite small in
total size so you shouldn't have a problem in principle, even on entry
level hardware.
As regards performance, that is affected by factors other than total
size, such as the number of concurrent users and the number, size and
type of transactions being performed.
David Portas
SQL Server MVP
--|||In your case specifically, the limit to your table will sure be 2,147,483,64
7
records. Because you have identity column that has int datatype. And thats
the limit of int datatype.
"Quentin Huo" wrote:

> Hi:
> In SQL Server 2000, how many records a table can have at most? I need to
> create a table in that there are 3 fields: one is identity int (a key), on
e
> is varchar(18) and another is bit.But I am not sure if I can store 1042412
8
> records in a table? Is it too big? If not, or not good, any idea to handle
> big records?
> Thanks
> Q.
>
>
>

how many field is rational in one table

Hi there,
I have a program whose database is in SQL.
My fields numbers are more than 250 and as I normalized it one of my tables
has 250 fields.
Is that rational and possible to have 250 fields in only one table and is
that affects the speed of my program?
Thanks in advance
RedhIt is always advisible to have less number of fields (columns).
It is easy to maintenance and write simple queries if columns are less.
Normalization is a technique to reduce redudancy.
Reducing the columns in a table increases the performance of the system
because you will not be using all the 250m columns in one query.
Try to split the table further. Hope this answers your question
best Regards,
Chandra
---
"redha" wrote:

> Hi there,
> I have a program whose database is in SQL.
> My fields numbers are more than 250 and as I normalized it one of my table
s
> has 250 fields.
> Is that rational and possible to have 250 fields in only one table and is
> that affects the speed of my program?
> Thanks in advance
> Redh
>
>|||Is it necessary to have 250 columns in a single table? Otherwise Look
for normalization
Madhivanan|||No 250 fields in one table would definitely cause you performance issues. I
n
my experience having taken flat file (AS400) data and broken it apart into
understandable data in SQL Server I know the seriousness of having too many
fields in one table. You want to take segments of the data that does not fi
t
the normaliziation process and extract that data into smaller subset tables
that are easily managed. When all the data for all departments for instance
is tacked onto one record in a large table when you go to pull data in or ru
n
major processes you are going to have a lot of lag time because even if the
table is indexed the process still has to run through every record and
caching each records data in memory to return you results sets. I would
suggest taking the data and identify what information is related to what and
breaking it down so that you dont run into major issues.
Hope this helps.
"redha" wrote:

> Hi there,
> I have a program whose database is in SQL.
> My fields numbers are more than 250 and as I normalized it one of my table
s
> has 250 fields.
> Is that rational and possible to have 250 fields in only one table and is
> that affects the speed of my program?
> Thanks in advance
> Redh
>
>|||First of all, columns are not fields; totally different concepts!
Next, there is no "magic number" of columns in a normalized table. But
from experience, 250 columns sounds like a design problem. Are you
sure that you are in 3NF now? If so, look for 4NF problems and MVDs.

How many columns?

In a SQL Serevr table maximum how many columns(or fields) are possible?Originally posted by subhasishray
In a SQL Serevr table maximum how many columns(or fields) are possible?

1024

How is inheritence like data best done in SQL

If you have several entities that have many common properties but a few
have a few unique fields to them how do you design your tables?

DO you make a seperate table for each entity even though they have many
common fields or is there a way to do an OO type thing where you have a
common table for all and somehow tack on the unique fields?

Just unsure whats possible and what's best.

Thanks for any input.On 18 Oct 2005 08:03:59 -0700, wackyphill@.yahoo.com wrote:

>If you have several entities that have many common properties but a few
>have a few unique fields to them how do you design your tables?
>DO you make a seperate table for each entity even though they have many
>common fields or is there a way to do an OO type thing where you have a
>common table for all and somehow tack on the unique fields?
>Just unsure whats possible and what's best.
>Thanks for any input.

The standard way I've always seen and often do is to have a "base" table with
the common fields, and a 1-to-1 relationship to tables with fields for the
specific case. There's even a symbol for this used on database diagrams.

Here's an example

address
address_id
country
country_subdivision
city
postal_code

street_address
address_id
street_name
street_number

postal_address
address_id
postal_box

Every address has an "address", and every address will have either a
"street_address" or a "postal_address", but not both.|||Ok, so is the idea is to remember to always do outer joins w/ the
address table to get all the info available?|||On 18 Oct 2005 08:36:39 -0700, wackyphill@.yahoo.com wrote:

>Ok, so is the idea is to remember to always do outer joins w/ the
>address table to get all the info available?

Once you have the structure, there are lots of options for how to retrive data
from it. An outer join to each and every "child" table is one option, or you
can add an address type column, and have the client do a second query to
retrieve the details of an address from the appropriate place.|||(wackyphill@.yahoo.com) writes:
> If you have several entities that have many common properties but a few
> have a few unique fields to them how do you design your tables?
> DO you make a seperate table for each entity even though they have many
> common fields or is there a way to do an OO type thing where you have a
> common table for all and somehow tack on the unique fields?
> Just unsure whats possible and what's best.

Basically as Steve says.

One has to be a little careful, and not overdo it. If it's only one or
two extra columns, maybe it's better to keep them in the main table.
Or let several "subclasses" share a table.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||On Tue, 18 Oct 2005 22:06:57 +0000 (UTC), Erland Sommarskog
<esquel@.sommarskog.se> wrote:

> (wackyphill@.yahoo.com) writes:
>> If you have several entities that have many common properties but a few
>> have a few unique fields to them how do you design your tables?
>>
>> DO you make a seperate table for each entity even though they have many
>> common fields or is there a way to do an OO type thing where you have a
>> common table for all and somehow tack on the unique fields?
>>
>> Just unsure whats possible and what's best.
>Basically as Steve says.
>One has to be a little careful, and not overdo it. If it's only one or
>two extra columns, maybe it's better to keep them in the main table.
>Or let several "subclasses" share a table.

I concure with that. My example, in fact, is a case where having 3 tables
instead of optional fields is usually overkill.

How I do to eliminate duplicate rows?

Hello,
I need to eliminate the duplicated rows in sql server 2000, but the duplicate is only for some fields of the row. However, I need all the fields of the row. For example, I have the next structure:
Id_type, number_type, date, diagnosis, sex, age, city

After many analysis I get many rows where the tree first field are repeated, so I need to leave only one but with the all another fields. This is because I need only the first time when the diagnosis appear.

How I can do it?

Thank you very much.

Regards,
Angela

As I understand you issue, when there are rows that have the same values for (ID_Type, Number_Type, Date), you wish to keep ONLY one (1) row, and it doens't matter which one of the duplicated rows is kept.

What if the non-duplicated fields is different, i.e., different diagnosis, or different sex, or different age, or different city (if that could happen)?

There are several methods to accomplish this task. First, a little more information is useful:

Version of SQL Server?

Are there other tables that have foreign key relationships to this table?

Approximately how much data is in the table (rows)?

Are there periods of time when no one is using the table?

Send this information and we can better assist you.

|||Hi Arnie, thanks you for your response.

Well, the problem is the information is the very bad quality .... so, I suppose that I get one row to the first time that some diagnosis appear to the pacient, but with data this not happen. So

I have found that to the same ID_Type, Number_Type, Date and same diagnosis exists rows that they have different sex or age or any other field, so I need to select only one, because I need the first time that this diagnosis appears...

Let me to response the questions:

Version of SQL Server?

R: Sql server 2000

Are there other tables that have foreign key relationships to this table?

R: yes, because some fields are only codes

Approximately how much data is in the table (rows)?

R: this table have 25 millions of rows... so much...

Are there periods of time when no one is using the table?

R: yes, this table is to datamining exercise.

I appreciate so much your help.

|||This kb should help:
http://support.microsoft.com/kb/139444|||

Here is an article that provides a bit more detailed instructions that the kb article.

http://www.sql-server-performance.com/rd_delete_duplicates.asp

One issue that neither article touches on is the size of your table. If there are many duplicates, attempting to work on the entire table could be a major struggle for your server due to the amount of Transaction Log activity and locks that will be required. You may find it more efficient to work with batches of, say 50,000 rows at a time. If there is a large amount of delete activity, there may be some Transaction Log issues that would have to be addressed.

|||

Thaks a lot, both articles are very nice....

Regards,

Angela

How I can get list of tables?

Hi friends,

How I can get list of tables and list of fields within those tables in SQL server.

Thnak a lot.

Check the thisarticlefrom 4guyfromrolla website

Regards

|||Rather than select data out of the sysobjects table, which is notguaranteed to be forwards/backwards compatible with different SQLServer versions, I encourage you to use the INFORMATION_SCHEMAviews which are supposed to work with each SQL Serverversion. In particular, look into the TABLES and/or COLUMNS views.|||

tmorton wrote:

I encourage you to use the INFORMATION_SCHEMA
views  which are supposed to work with each SQL Server
version.

Actually, I think you are supposed to use the system stored procedures. For instance, to list all tables in databse execsp_tables etc.|||

Thanksa lot for your kind and useful info.