primary key autoincrement question.

I have a table in my sqlserver 2000 that has a field IDNO. i want this
field to be my primary key. however i don't want this field to use the
autoincrement feature. when i access this table from vb.net and try to
add a record this field is autoincrementing. how can i disable the
autoincrement of this field yet serves this as my primary key?

thanks in advancego to design, select your id make shure that there is a key symbol next to
it (if not right click and set primary key)
this makes it the primary key the identity is something else -->
autoincrementing set it to false and there you go :)

hope it helps

eric

"jaYPee" <hijaypee@.yahoo.com> wrote in message
news:8vl470phkea0fueivehemm3h882ousjg8f@.4ax.com...
> I have a table in my sqlserver 2000 that has a field IDNO. i want this
> field to be my primary key. however i don't want this field to use the
> autoincrement feature. when i access this table from vb.net and try to
> add a record this field is autoincrementing. how can i disable the
> autoincrement of this field yet serves this as my primary key?
> thanks in advance|||Thank you for the reply. however i can't find an autoincrementing
properties under column properties in order to set it to false.

under column properties of IDNO field i have i only see this
properties:

Description
Default Value
Precision
Scale
Identity
Identity Seed
Identity Increment
Is RowGuid
Formula
Collation

i presume before and until now that i have to set the identity to "no"
but still in my vb.net app when i add record the IDNO field still
increment to the last value + 1.

don't know where can i set the autoincrement to "false"

thanks again

On Tue, 6 Apr 2004 09:41:21 +0200, "EricJ"
<ericReMoVe@.ThiSbitconsult.be.RE> wrote:

>go to design, select your id make shure that there is a key symbol next to
>it (if not right click and set primary key)
>this makes it the primary key the identity is something else -->
>autoincrementing set it to false and there you go :)
>hope it helps
>eric
>"jaYPee" <hijaypee@.yahoo.com> wrote in message
>news:8vl470phkea0fueivehemm3h882ousjg8f@.4ax.com...
>> I have a table in my sqlserver 2000 that has a field IDNO. i want this
>> field to be my primary key. however i don't want this field to use the
>> autoincrement feature. when i access this table from vb.net and try to
>> add a record this field is autoincrementing. how can i disable the
>> autoincrement of this field yet serves this as my primary key?
>>
>> thanks in advance|||these are the ones you are after

> Identity
> Identity Seed
> Identity Increment

yust set the identity to false the rest will follow :)

eric

"jaYPee" <hijaypee@.yahoo.com> wrote in message
news:aos470d3p2gup57707rmeib2907453sn69@.4ax.com...
> Thank you for the reply. however i can't find an autoincrementing
> properties under column properties in order to set it to false.
> under column properties of IDNO field i have i only see this
> properties:
> Description
> Default Value
> Precision
> Scale
> Identity
> Identity Seed
> Identity Increment
> Is RowGuid
> Formula
> Collation
> i presume before and until now that i have to set the identity to "no"
> but still in my vb.net app when i add record the IDNO field still
> increment to the last value + 1.
> don't know where can i set the autoincrement to "false"
> thanks again
> On Tue, 6 Apr 2004 09:41:21 +0200, "EricJ"
> <ericReMoVe@.ThiSbitconsult.be.RE> wrote:
> >go to design, select your id make shure that there is a key symbol next
to
> >it (if not right click and set primary key)
> >this makes it the primary key the identity is something else -->
> >autoincrementing set it to false and there you go :)
> >hope it helps
> >eric
> >"jaYPee" <hijaypee@.yahoo.com> wrote in message
> >news:8vl470phkea0fueivehemm3h882ousjg8f@.4ax.com...
> >> I have a table in my sqlserver 2000 that has a field IDNO. i want this
> >> field to be my primary key. however i don't want this field to use the
> >> autoincrement feature. when i access this table from vb.net and try to
> >> add a record this field is autoincrementing. how can i disable the
> >> autoincrement of this field yet serves this as my primary key?
> >>
> >> thanks in advance|||>> I have a table in my SQL Server 2000 that has a field [sic] IDNO. I
want this field [sic] to be my primary key. <<

You need to read a book on SQL and RDBMS. Rows are not records; fields
are not columns; tables are not files; there is no sequential access
or ordering in an RDBMS, so "first", "next" and "last" are totally
meaningless.

What does this table model in the real world? Look at the real world
and ask what the key is. It cannot ever be the internal state of the
hardware in which your model resides. You are still thinking that
there are rcord numbers, like a sequential file system in an RDBMS --
you even use the terminology of a sequential file system.

This is totally wrong. There is no "Magic, Universal
one-size-fits-all" way to get a key. Building a data model is work.

Primary Key / Referntial Integrity Question

Hi,

I am supporting an application that was converted from ACCESS to SQL
Server 2000. My question focuses on two particuliar tables. The
parent table has 14000 rows while child table has over 9 million rows.
Referential integrity is set up between the two tables. The parent
table has a composite primary key of:
CustomerId (int)
LocationId (int)
ProductId (int)

The child table has a composite primary key of:
CustomerId (int)
LocationId (int)
ProductId (int)
InvTime (datetime)

A constraint between the parent and child table consists of:
CustomerId
LocationId
TankId

I have been asked to write an application that will allow users to
update the Customerid and LocationId columns of the parent table. My
program works fine when you update just one parent record but if you
try and update 30 parent records I get a timeout error. I believe the
bottleneck is the constraint between the parent and child tables.
Outside of re-designing the tables (NOTE: I was not the original
DBA/Developer), does anyone have any suggestions I can do to improve
performance for my app?

ThanksRodney King () writes:
> I am supporting an application that was converted from ACCESS to SQL
> Server 2000. My question focuses on two particuliar tables. The
> parent table has 14000 rows while child table has over 9 million rows.
> Referential integrity is set up between the two tables. The parent
> table has a composite primary key of:
> CustomerId (int)
> LocationId (int)
> ProductId (int)
> The child table has a composite primary key of:
> CustomerId (int)
> LocationId (int)
> ProductId (int)
> InvTime (datetime)
> A constraint between the parent and child table consists of:
> CustomerId
> LocationId
> TankId
> I have been asked to write an application that will allow users to
> update the Customerid and LocationId columns of the parent table. My
> program works fine when you update just one parent record but if you
> try and update 30 parent records I get a timeout error. I believe the
> bottleneck is the constraint between the parent and child tables.
> Outside of re-designing the tables (NOTE: I was not the original
> DBA/Developer), does anyone have any suggestions I can do to improve
> performance for my app?

I assume that the referential integrity is set up with ON UPDATE CASCADE?

First all, are there any triggers on the tables? In such case, what
do they do?

Next, what are the indexes on the tables? Obviosly the PKs are indexes,
but are the clustered or non-clustered?

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks for responding.
1) To answer your questions I have full Delete/Update cascade on my
constraint.

2) There are no DB triggers and here are the indexes on my tables:

3) Indexes are as follows:
Master table:
Primary Key, Unique, Nonclustered index on:
CustomerId,LocationId,ProductId

Child table:
Nonclustered index on:
CustomerId,LocationId,ProductId

Nonclustered index on:
MinNumber

Primary Key, Unique, Nonclustered index on:
ProductId,InvTime

When I run the following query in SQL Query Analyzer:

Update MasterTable set Customerid=23,LocationId=34 where CustomerId=44
and LocationId=788 and ProductId=91022

My Explain plan tells me that a full table scan would be performed
against my child table. However, if I perform the same statement
against my Child table, the Explain plan uses my Nonclustered index of
CustomerId,LocationId and ProductId

Can you tell me what's going on?

Thanks
On Wed, 8 Dec 2004 20:52:38 +0000 (UTC), Erland Sommarskog
<esquel@.sommarskog.se> wrote:

>Rodney King () writes:
>> I am supporting an application that was converted from ACCESS to SQL
>> Server 2000. My question focuses on two particuliar tables. The
>> parent table has 14000 rows while child table has over 9 million rows.
>> Referential integrity is set up between the two tables. The parent
>> table has a composite primary key of:
>> CustomerId (int)
>> LocationId (int)
>> ProductId (int)
>>
>> The child table has a composite primary key of:
>> CustomerId (int)
>> LocationId (int)
>> ProductId (int)
>> InvTime (datetime)
>>
>> A constraint between the parent and child table consists of:
>> CustomerId
>> LocationId
>> TankId
>>
>> I have been asked to write an application that will allow users to
>> update the Customerid and LocationId columns of the parent table. My
>> program works fine when you update just one parent record but if you
>> try and update 30 parent records I get a timeout error. I believe the
>> bottleneck is the constraint between the parent and child tables.
>> Outside of re-designing the tables (NOTE: I was not the original
>> DBA/Developer), does anyone have any suggestions I can do to improve
>> performance for my app?
>I assume that the referential integrity is set up with ON UPDATE CASCADE?
>First all, are there any triggers on the tables? In such case, what
>do they do?
>Next, what are the indexes on the tables? Obviosly the PKs are indexes,
>but are the clustered or non-clustered?|||Rodney King () writes:
> Thanks for responding.
> 1) To answer your questions I have full Delete/Update cascade on my
> constraint.
> 2) There are no DB triggers and here are the indexes on my tables:
> 3) Indexes are as follows:
> Master table:
> Primary Key, Unique, Nonclustered index on:
> CustomerId,LocationId,ProductId
> Child table:
> Nonclustered index on:
> CustomerId,LocationId,ProductId
> Nonclustered index on:
> MinNumber
> Primary Key, Unique, Nonclustered index on:
> ProductId,InvTime

Do I read this right that there are no clustered indexes? While an update
to the clustered index would be more costly, locating the rows will be
faster.

The problem now is that the optimizer estimates that using the non-clustered
index on the table will be more expensive than a table scan. This is not
that strange as it sounds. If you need to access 50% of the rows in a
table, this is lot more expensive to do through a non-clustered index
than a table scan. (Because you will need to access many pages more
than once.) The optimizer therefor tends to be somewhat conservative
in its use of nonclustered indexes.

If you don't want to change the index to a clustered index, an UPDATE
STATISTICS WITH FULLSCAN on the tables may help, but I would not really
count on it.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

'PRIMARY' filegroup is full.

What should I do to resolve the error listed below?
Thanks,
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1105: [Microsoft][ODBC SQL
Server Driver][SQL Server]Could not allocate space for object '(SYSTEM table
id: -63607320)' in database 'DALLAS_REPT' because the 'PRIMARY' filegroup is
full.
[Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been
terminated.
Expand your primary data files in the user dbs and tempdb and make sure you
have auto shrink turned off.
Andrew J. Kelly SQL MVP
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:F8B46691-23CC-4ECA-AD90-C0634CB31BD1@.microsoft.com...
> What should I do to resolve the error listed below?
> Thanks,
>
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1105: [Microsoft][ODBC
> SQL
> Server Driver][SQL Server]Could not allocate space for object '(SYSTEM
> table
> id: -63607320)' in database 'DALLAS_REPT' because the 'PRIMARY' filegroup
> is
> full.
> [Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been
> terminated.
>

PRIMARY file group is full

Hi there,

I've just run some DTS packages on my test sqlserver (Which has limited
hard disk space and memory) and all the tasks have failed, due to
'PRIMARY' file group is full

Is there a query or script I can run to resolve this problem??

M3ckon

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!m3ckon (anonymous@.devdex.com) writes:
> I've just run some DTS packages on my test sqlserver (Which has limited
> hard disk space and memory) and all the tasks have failed, due to
> 'PRIMARY' file group is full
> Is there a query or script I can run to resolve this problem??

Could send a query to whomeever holds you money to get more hardware.

The message says that SQL Server cannot autogrow the database, because
there is not space on the disk. By default SQL Server grows by 10%, so
if your database is 20 GB, SQL Server tries to grow 2 GB. If you think
you can fit your data into these 2 GB, then you lower that number to
squeeze out the rest from your disk.

Else you will have to try to free up some disk space. If the log for
the database is on the same disk, and you could live with simple
recovery, you could change to simple recovery, and then issue
BACKUP LOG db WITH TRUNCATE_ONLY. You have also have to do a DBCC
SHRINKFILE on the log. But before you do any of this, check how big
your log is.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Pricing Model

Can any one provide me some details on the pricing of SQL server ?
I have seen a software maker selling Client-Server application with a SQL
Server as backend for only $300!.
Also, is there a small version of the SQL server that can be used as a db ?
TIA.
Well it depends on what you want to do...
By "small version" I take it that you mean a database that you never expect
to grow beyond 2 GB -> and have less than 10 users. If that is the case,
then MSDE might be your solution:
For more info, see:
http://www.microsoft.com/sql
=-Chris
"exBK" <exBK@.discussions.microsoft.com> wrote in message
news:21CDEE5C-DE2F-4AD7-B683-1A1AC2B30A09@.microsoft.com...
> Can any one provide me some details on the pricing of SQL server ?
> I have seen a software maker selling Client-Server application with a SQL
> Server as backend for only $300!.
> Also, is there a small version of the SQL server that can be used as a db
?
> TIA.
|||Thanks for your reply. I am looking for exactly what you are describing
below. So is MSDE free ?
"Christopher Conner" wrote:

> Well it depends on what you want to do...
> By "small version" I take it that you mean a database that you never expect
> to grow beyond 2 GB -> and have less than 10 users. If that is the case,
> then MSDE might be your solution:
> For more info, see:
> http://www.microsoft.com/sql
> =-Chris
> "exBK" <exBK@.discussions.microsoft.com> wrote in message
> news:21CDEE5C-DE2F-4AD7-B683-1A1AC2B30A09@.microsoft.com...
> ?
>
>

Pricing Model

Can any one provide me some details on the pricing of SQL server ?
I have seen a software maker selling Client-Server application with a SQL
Server as backend for only $300!.
Also, is there a small version of the SQL server that can be used as a db ?
TIA.Well it depends on what you want to do...
By "small version" I take it that you mean a database that you never expect
to grow beyond 2 GB -> and have less than 10 users. If that is the case,
then MSDE might be your solution:
For more info, see:
http://www.microsoft.com/sql
=-Chris
"exBK" <exBK@.discussions.microsoft.com> wrote in message
news:21CDEE5C-DE2F-4AD7-B683-1A1AC2B30A09@.microsoft.com...
> Can any one provide me some details on the pricing of SQL server ?
> I have seen a software maker selling Client-Server application with a SQL
> Server as backend for only $300!.
> Also, is there a small version of the SQL server that can be used as a db
?
> TIA.|||Thanks for your reply. I am looking for exactly what you are describing
below. So is MSDE free ?
"Christopher Conner" wrote:

> Well it depends on what you want to do...
> By "small version" I take it that you mean a database that you never expec
t
> to grow beyond 2 GB -> and have less than 10 users. If that is the case,
> then MSDE might be your solution:
> For more info, see:
> http://www.microsoft.com/sql
> =-Chris
> "exBK" <exBK@.discussions.microsoft.com> wrote in message
> news:21CDEE5C-DE2F-4AD7-B683-1A1AC2B30A09@.microsoft.com...
> ?
>
>