PrimaryKey and Index

All,
To my understanding, the primary key is automatically indexed.
But in Enterprise Mananger (Design table/ indexes keys). It shows:
(x) Create UNIQUE
O Constraint <-- this is select
O Index
and you can NOT change the selection.
So I am confused that if an index is created or not. Do I need to
create the (unique) index on the primary key column so I can search on
this column with best performance.
Thanks
John
Primary Keys are automatically indexed. you do not have to create the index
manually.
Note: This is NOT the case for Foreign Keys
Note2: By Default SQL Server will create Primary Keys as "Clustered"
indexes. You need to be careful as the Clustered index may be better placed
on another column or columns.
Cheers,
Greg Jackson
PDX, Oregon
|||A Primary Key is a Constraint, which is an element of your logical data
model, rather than an Index, which is a feature of your table's physical
implementation. Behind the scenes the result is the same - the PK constraint
is implemented as a unique index and you don't need to create another index
explicitly.
David Portas
SQL Server MVP
|||Thanks for the clarification from Greg and David.
How about the Unique Contraint? I think it is just like PK, isn't it?
|||In My mind, a unique constrain is mainly there for you alternate keys, where a primary key
constraint is there for your primary key.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Q. John Chen" <nonospam@.wowway.com> wrote in message
news:8488de58.0406170656.4e4dd32b@.posting.google.c om...
> Thanks for the clarification from Greg and David.
> How about the Unique Contraint? I think it is just like PK, isn't it?
|||A Unique Constraint is applied to force Non PKey Items Unique.
Pkey is defined for referential integrity purposes, etc.
Cheers
GAJ
|||Thank for your response.
My question is that whether an index will be automatically created for
a Unique constraint. So I don't have create index for the performance
reason.
Thanks again.
John.
"Jaxon" <GregoryAJackson@.hotmail.com> wrote in message news:<#$zxjzIVEHA.3656@.TK2MSFTNGP11.phx.gbl>...
> A Unique Constraint is applied to force Non PKey Items Unique.
> Pkey is defined for referential integrity purposes, etc.
>
> Cheers
>
> GAJ
|||On 21 Jun 2004 09:35:50 -0700, Q. John Chen wrote:

>Thank for your response.
>My question is that whether an index will be automatically created for
>a Unique constraint. So I don't have create index for the performance
>reason.
>Thanks again.
>John.
Hi John,
If you define a UNIQUE constraint, SQL Server will indeed create an index
that is used for enforcing the constraint, but can also be used to
optimize queries.
The index created will be nonclustered by default, but you can override
this if you prefer a clustered index - check BOL for details.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

PrimaryKey and Index

All,
To my understanding, the primary key is automatically indexed.
But in Enterprise Mananger (Design table/ indexes keys). It shows:
(x) Create UNIQUE
O Constraint <-- this is select
O Index
and you can NOT change the selection.
So I am confused that if an index is created or not. Do I need to
create the (unique) index on the primary key column so I can search on
this column with best performance.
Thanks
JohnPrimary Keys are automatically indexed. you do not have to create the index
manually.
Note: This is NOT the case for Foreign Keys
Note2: By Default SQL Server will create Primary Keys as "Clustered"
indexes. You need to be careful as the Clustered index may be better placed
on another column or columns.
Cheers,
Greg Jackson
PDX, Oregon|||A Primary Key is a Constraint, which is an element of your logical data
model, rather than an Index, which is a feature of your table's physical
implementation. Behind the scenes the result is the same - the PK constraint
is implemented as a unique index and you don't need to create another index
explicitly.
David Portas
SQL Server MVP
--|||Thanks for the clarification from Greg and David.
How about the Unique Contraint? I think it is just like PK, isn't it?|||In My mind, a unique constrain is mainly there for you alternate keys, where
a primary key
constraint is there for your primary key.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Q. John Chen" <nonospam@.wowway.com> wrote in message
news:8488de58.0406170656.4e4dd32b@.posting.google.com...
> Thanks for the clarification from Greg and David.
> How about the Unique Contraint? I think it is just like PK, isn't it?|||A Unique Constraint is applied to force Non PKey Items Unique.
Pkey is defined for referential integrity purposes, etc.
Cheers
GAJ|||Thank for your response.
My question is that whether an index will be automatically created for
a Unique constraint. So I don't have create index for the performance
reason.
Thanks again.
John.
"Jaxon" <GregoryAJackson@.hotmail.com> wrote in message news:<#$zxjzIVEHA.3656@.TK2MSFTNGP11.p
hx.gbl>...
> A Unique Constraint is applied to force Non PKey Items Unique.
> Pkey is defined for referential integrity purposes, etc.
>
> Cheers
>
> GAJ|||On 21 Jun 2004 09:35:50 -0700, Q. John Chen wrote:

>Thank for your response.
>My question is that whether an index will be automatically created for
>a Unique constraint. So I don't have create index for the performance
>reason.
>Thanks again.
>John.
Hi John,
If you define a UNIQUE constraint, SQL Server will indeed create an index
that is used for enforcing the constraint, but can also be used to
optimize queries.
The index created will be nonclustered by default, but you can override
this if you prefer a clustered index - check BOL for details.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

PrimaryKey and Index

All,
To my understanding, the primary key is automatically indexed.
But in Enterprise Mananger (Design table/ indexes keys). It shows:
(x) Create UNIQUE
O Constraint <-- this is select
O Index
and you can NOT change the selection.
So I am confused that if an index is created or not. Do I need to
create the (unique) index on the primary key column so I can search on
this column with best performance.
Thanks
JohnPrimary Keys are automatically indexed. you do not have to create the index
manually.
Note: This is NOT the case for Foreign Keys
Note2: By Default SQL Server will create Primary Keys as "Clustered"
indexes. You need to be careful as the Clustered index may be better placed
on another column or columns.
Cheers,
Greg Jackson
PDX, Oregon|||A Primary Key is a Constraint, which is an element of your logical data
model, rather than an Index, which is a feature of your table's physical
implementation. Behind the scenes the result is the same - the PK constraint
is implemented as a unique index and you don't need to create another index
explicitly.
--
David Portas
SQL Server MVP
--|||Thanks for the clarification from Greg and David.
How about the Unique Contraint? I think it is just like PK, isn't it?|||In My mind, a unique constrain is mainly there for you alternate keys, where a primary key
constraint is there for your primary key.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Q. John Chen" <nonospam@.wowway.com> wrote in message
news:8488de58.0406170656.4e4dd32b@.posting.google.com...
> Thanks for the clarification from Greg and David.
> How about the Unique Contraint? I think it is just like PK, isn't it?|||A Unique Constraint is applied to force Non PKey Items Unique.
Pkey is defined for referential integrity purposes, etc.
Cheers
GAJ|||Thank for your response.
My question is that whether an index will be automatically created for
a Unique constraint. So I don't have create index for the performance
reason.
Thanks again.
John.
"Jaxon" <GregoryAJackson@.hotmail.com> wrote in message news:<#$zxjzIVEHA.3656@.TK2MSFTNGP11.phx.gbl>...
> A Unique Constraint is applied to force Non PKey Items Unique.
> Pkey is defined for referential integrity purposes, etc.
>
> Cheers
>
> GAJ|||On 21 Jun 2004 09:35:50 -0700, Q. John Chen wrote:
>Thank for your response.
>My question is that whether an index will be automatically created for
>a Unique constraint. So I don't have create index for the performance
>reason.
>Thanks again.
>John.
Hi John,
If you define a UNIQUE constraint, SQL Server will indeed create an index
that is used for enforcing the constraint, but can also be used to
optimize queries.
The index created will be nonclustered by default, but you can override
this if you prefer a clustered index - check BOL for details.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Primary/Foreign/Identity keys & Encryption

Hi all!

I'm just getting my feet wet with how encryption works in SQL 2005. With regards to the encryption of primary / foreign keys, I'm not entirely clear on the best approach. Below are three examples of typical table structures I currently have:

== Customers table ==
CustomerID (PK, int, Identity)
CustomerName (varchar)

== Orders table ==
OrderID (PK, int, Identity)
CustomerID (int, foreign key)
CreditCardNumber (varchar)

== OrderDetails table (1 to Many) ==
OrderID (PK/FK, int)
ItemNumber (PK, int)
ItemDescription (varchar)

The Customers and Orders tables use identity values as their primary keys. From what I can tell, CustomerID in the Customers table cannot be encrypted and OrderID in the Orders table cannot be encrypted because they are identity values. In these cases, would it be safer (in terms of security) to create a separate, meaningless identity key column in the Customers table and then remove the identity attribute from CustomerID so I can encrypt CustomerID?

Similarily in the OrderDetails table, OrderID and ItemNumber form a composite key. These values are important in that I don't want them to be tampered with. Am I better off creating a separate identity key column which becomes the table's primary key ... then encrypt both the OrderID and ItemNumber columns in this table?

Any ideas are appreciated.

Thank you,
Ben

Hey Ben,

You have the principle behind encryption correct.

The tricky thing is when you use encryption on keys, a lot of the value of having these keys goes away. For eample, because encryption is non-deterministic, you won't be able to use OrderID as a FK in the OrderDetails table (unless if you encrypt once and then insert into both tables, but this leaks information. Then again, this might be acceptable in your application). You can still use PK, but they will behave differently. For example, because encryption is non-deterministic, just having the primary keys no longer guarantees that the columns will be unique. If you try to insert Encrypted("id1") and then Encrypted("id1") again, you actually end up with two different cipher values so the the table will allow both inserts.

Security basically destroys information (well encrypted data is indistinguishable from random data) while the point of using keys is to preserve information for reference. Consider, for example, the difficulties you will encounter attempting to do searches or joins on CustomerID and OrderID if they are encrypted (Laurentiu has a good blog entry on this here: http://blogs.msdn.com/lcris/archive/2005/12/22/506931.aspx).

You can also check Laurentiu's blog for an example of creating an application using encryption: http://blogs.msdn.com/lcris/archive/2005/12/16/504692.aspx this doesn't completely solve your problems, but it might be useful in seeing one way to apply encryption.

Please let us know if you would like more information or have further questions.

Sung

sql

Primary versus unique keys

Hi

What impact is there in using a unique index instead of a primary key?

How does this impact performance?

How does this impact data file size?

Does it impact anything else?

Thanks

Hi,

http://www.mssqlcity.com/FAQ/General/primary_vs_unique_constraints.htm

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

|||

Hi

It that the only difference?

Is there any impact on Replication, Database Mirroing (SQL 2005), or any other features?

Thanks

|||

There is an impact on transactional replication (all options) since it requires a primary key (unique indexes do not work). No impact to either merge or snapshot replication.

Database Mirroring and other features don't care.

Primary Keys: Best Practice: uniqueidentifier or?

I've searched the posts and library. Associates have different ideas on the topic. So what is your opinion?

For SQL 2005, what is the recommended data type for a primary key?

Hi,

tihs is normally not a thing of best practice. I am always a friend of using natural Primary Keys rather than artifical produced keys. Uniqueidentifiet is normally NOT a natural key.

Using GUIDs or not is a religious question, which I decided for me is a worse practice, because storing GUID is often more expensive than storing natural keys. Primary keys should always be designed to use the less amount of data that joining will not be expensive. If you have no natural key, I am always a friend of using integer value (differnent scale based on the requirement, e.g. for a table of male/female you won′t need a bigint, though there won′t be much more genders)

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||Unless you need a guid for a reason(networked based uniqueness) then I suggest using an Integer with the identity attribute, this will autogenerate keys like autonumber does in access. THis assumes of course that you can not follow Jens advice of finding a natural key.

Primary Keys: Best Practice: uniqueidentifier or?

I've searched the posts and library. Associates have different ideas on the topic. So what is your opinion?

For SQL 2005, what is the recommended data type for a primary key?

Hi,

tihs is normally not a thing of best practice. I am always a friend of using natural Primary Keys rather than artifical produced keys. Uniqueidentifiet is normally NOT a natural key.

Using GUIDs or not is a religious question, which I decided for me is a worse practice, because storing GUID is often more expensive than storing natural keys. Primary keys should always be designed to use the less amount of data that joining will not be expensive. If you have no natural key, I am always a friend of using integer value (differnent scale based on the requirement, e.g. for a table of male/female you won′t need a bigint, though there won′t be much more genders)

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||Unless you need a guid for a reason(networked based uniqueness) then I suggest using an Integer with the identity attribute, this will autogenerate keys like autonumber does in access. THis assumes of course that you can not follow Jens advice of finding a natural key.sql

Primary keys, indexes and speed

My table consists of several key fields (i.e. used in a complex primary key)
and some other fields where I do aggregation:
CREATE TABLE t
(
id INT NOT NULL,
ts DATETIME NOT NULL,
k1 INT NOT NULL,
k2 INT NOT NULL,
...
a1 FLOAT NOT NULL,
a2 FLOAT NOT NULL,
...
PRIMARY KEY (id, ts, k1, k2)
)
A SELECT query I execute on this table looks like:
SELECT k1, k2, SUM(a1) AS s1, SUM(a2) AS s2 FROM t
WHERE id = SomeNumber AND ts > StartDate AND ts <= EndDate
GROUP BY k1, k2 ORDER BY s1 DESC
As you see, id and ts are used in WHERE and k1 and k2 - in GROUP.
Do I need to add some more indexes to improve speed of retrieving
or above PRIMARY KEY is enough?It seems to be a good candidate for an indexed view ,isn't it? Read about it
in the BOL
"Tumurbaatar S." <nospam_tumur@.magicnet.mn> wrote in message
news:e5qJD2oOFHA.1884@.TK2MSFTNGP15.phx.gbl...
> My table consists of several key fields (i.e. used in a complex primary
key)
> and some other fields where I do aggregation:
> CREATE TABLE t
> (
> id INT NOT NULL,
> ts DATETIME NOT NULL,
> k1 INT NOT NULL,
> k2 INT NOT NULL,
> ...
> a1 FLOAT NOT NULL,
> a2 FLOAT NOT NULL,
> ...
> PRIMARY KEY (id, ts, k1, k2)
> )
> A SELECT query I execute on this table looks like:
> SELECT k1, k2, SUM(a1) AS s1, SUM(a2) AS s2 FROM t
> WHERE id = SomeNumber AND ts > StartDate AND ts <= EndDate
> GROUP BY k1, k2 ORDER BY s1 DESC
> As you see, id and ts are used in WHERE and k1 and k2 - in GROUP.
> Do I need to add some more indexes to improve speed of retrieving
> or above PRIMARY KEY is enough?
>|||Have you tried it yet? Or are you just guessing? Do you have a lot of
other columns? Since you have this as the clustered index, if it is the
ONLY index involved in any query, it might be enough.
Either way, you should test it out and see what happens. A lot depends on
what else you do with the table, because it can be too costly to add a bunch
of indexes to a table to speed up one query and hurt the others.
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"Tumurbaatar S." <nospam_tumur@.magicnet.mn> wrote in message
news:e5qJD2oOFHA.1884@.TK2MSFTNGP15.phx.gbl...
> My table consists of several key fields (i.e. used in a complex primary
> key)
> and some other fields where I do aggregation:
> CREATE TABLE t
> (
> id INT NOT NULL,
> ts DATETIME NOT NULL,
> k1 INT NOT NULL,
> k2 INT NOT NULL,
> ...
> a1 FLOAT NOT NULL,
> a2 FLOAT NOT NULL,
> ...
> PRIMARY KEY (id, ts, k1, k2)
> )
> A SELECT query I execute on this table looks like:
> SELECT k1, k2, SUM(a1) AS s1, SUM(a2) AS s2 FROM t
> WHERE id = SomeNumber AND ts > StartDate AND ts <= EndDate
> GROUP BY k1, k2 ORDER BY s1 DESC
> As you see, id and ts are used in WHERE and k1 and k2 - in GROUP.
> Do I need to add some more indexes to improve speed of retrieving
> or above PRIMARY KEY is enough?
>|||Tumurbaatar S.,
What about the execution plan, can you post it?
set showplan_text on
go
SELECT k1, k2, SUM(a1) AS s1, SUM(a2) AS s2 FROM t
WHERE id = SomeNumber AND ts > StartDate AND ts <= EndDate
GROUP BY k1, k2 ORDER BY s1 DESC
go
set showplan_text off
go
AMB
P.S. Your last name remind me a student in my classroom, when I was in
college (studying Physics), he was from Mongolia.
"Tumurbaatar S." wrote:

> My table consists of several key fields (i.e. used in a complex primary ke
y)
> and some other fields where I do aggregation:
> CREATE TABLE t
> (
> id INT NOT NULL,
> ts DATETIME NOT NULL,
> k1 INT NOT NULL,
> k2 INT NOT NULL,
> ...
> a1 FLOAT NOT NULL,
> a2 FLOAT NOT NULL,
> ...
> PRIMARY KEY (id, ts, k1, k2)
> )
> A SELECT query I execute on this table looks like:
> SELECT k1, k2, SUM(a1) AS s1, SUM(a2) AS s2 FROM t
> WHERE id = SomeNumber AND ts > StartDate AND ts <= EndDate
> GROUP BY k1, k2 ORDER BY s1 DESC
> As you see, id and ts are used in WHERE and k1 and k2 - in GROUP.
> Do I need to add some more indexes to improve speed of retrieving
> or above PRIMARY KEY is enough?
>
>|||Since ts predicate is a range, I'd try changing the clustered PK index to
put ts first, instead of second... That might make a substantial
improvement...
"Tumurbaatar S." wrote:

> My table consists of several key fields (i.e. used in a complex primary ke
y)
> and some other fields where I do aggregation:
> CREATE TABLE t
> (
> id INT NOT NULL,
> ts DATETIME NOT NULL,
> k1 INT NOT NULL,
> k2 INT NOT NULL,
> ...
> a1 FLOAT NOT NULL,
> a2 FLOAT NOT NULL,
> ...
> PRIMARY KEY (id, ts, k1, k2)
> )
> A SELECT query I execute on this table looks like:
> SELECT k1, k2, SUM(a1) AS s1, SUM(a2) AS s2 FROM t
> WHERE id = SomeNumber AND ts > StartDate AND ts <= EndDate
> GROUP BY k1, k2 ORDER BY s1 DESC
> As you see, id and ts are used in WHERE and k1 and k2 - in GROUP.
> Do I need to add some more indexes to improve speed of retrieving
> or above PRIMARY KEY is enough?
>
>|||Yes, it is. But my application and db is designed to
work with any (at least, with many) OLEDB/ADO compliant
databases. And indexed view, I think, is MS SQL feature.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:ehYzaRpOFHA.2132@.TK2MSFTNGP14.phx.gbl...
> It seems to be a good candidate for an indexed view ,isn't it? Read about
> it
> in the BOL
>
> "Tumurbaatar S." <nospam_tumur@.magicnet.mn> wrote in message
> news:e5qJD2oOFHA.1884@.TK2MSFTNGP15.phx.gbl...
> key)
>|||Yes, I tried. My server app works (24/7) and regularly adds new records.
And other, client, app retrieves these records to analyze. No update/delete
happens on the table. Only insert and select. And no many variants
of select, just one I posted above.
"Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
news:e47gILqOFHA.2252@.TK2MSFTNGP15.phx.gbl...
> Have you tried it yet? Or are you just guessing? Do you have a lot of
> other columns? Since you have this as the clustered index, if it is the
> ONLY index involved in any query, it might be enough.
> Either way, you should test it out and see what happens. A lot depends on
> what else you do with the table, because it can be too costly to add a
> bunch of indexes to a table to speed up one query and hurt the others.
> --
> ----
--
> Louis Davidson - drsql@.hotmail.com
> SQL Server MVP
> Compass Technology Management - www.compass.net
> Pro SQL Server 2000 Database Design -
> http://www.apress.com/book/bookDisplay.html?bID=266
> Blog - http://spaces.msn.com/members/drsql/
> Note: Please reply to the newsgroups only unless you are interested in
> consulting services. All other replies may be ignored :)
> "Tumurbaatar S." <nospam_tumur@.magicnet.mn> wrote in message
> news:e5qJD2oOFHA.1884@.TK2MSFTNGP15.phx.gbl...
>|||Ok, I will try.
"CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
news:36D28CF4-7839-4F8B-ACDC-D4D23DA61AA9@.microsoft.com...
> Since ts predicate is a range, I'd try changing the clustered PK index to
> put ts first, instead of second... That might make a substantial
> improvement...
>
> "Tumurbaatar S." wrote:
>|||On Tue, 12 Apr 2005 14:25:52 +0800, Tumurbaatar S. wrote:

>See attachment. There's an actual table structure, a query I used and
>a resulting plan.
Hi Tumurbaatar,
I think the query will be executed at about the maximum possible speed.
The clustered index that is created because of the PRIMARY KEY
constraint is optimal for this query. (Note - near the end of this
message, I'll discuss one possible improvement).
Allow me to use an analogy to explain: imagine that you have a phone
book of a large town, were all people are listed in alphabetic order of
the surname. Further, people with the same surname are ordered by first
name. In database terms, the phone book is a table with clustered index
on (Surname, FirstName).
Now suppose you are given the task of finding all people with Surname
'Smith' and whose first name starts with H, I or J. That's not hard -
you quickly flip to the page where the Smith's are listed, skip to the
first whose name starts with H, then start scanning all names - and you
stop as soon as you encounter a Smith whose first name starts with K.
But what if your task had been to find all people with first name 'John'
and surname starting with K, L, or M? In that case, you'd have to scan a
much larger proportion of the phone book (the complete listing of all
surnames starting with K, L, or M, to be precise). That's why I don't
think that CBretana's suggestion will improve the speed of your query.

>By the way, I understood nothing there.
The execution plan, you mean? Okay, take it easy. The best way to
interpret an execution plan is from innermost to outermost. This plan is
quite straightforward, actually :-)
The last line says "Clustered Index S". That is the process I
described above (in the phone book analogy) - the database uses the
index' structure to go straight to the first row with intid = 1 and ts >
'2005-4-1 00:00', then starts processing rows until it is past the last
row with intid = 1 and ts <= '2005-4-1 00:30'.
The line before that is a sort step. This is used to satisfy the group
by clause - after the sort, all rows that need to be grouped together
are together. (There are more strategies the optimizer can use for a
group by, but in this case the optimizer expects the sorting to be the
fastest).
The Stream Aggregate step takes the sorted output and calculates one row
from each group. If you inspect the entire line on your plan, you'll see
the three aggregate expressions you used in the query appear here. Note
that each result is given a name (Expr1002 through Expr1004).
And the final step is another sort step - this time to satisfy the ORDER
BY you specified. Note that the plan says to order by [Expr1004] - and
if you check the Stream Aggregate step, you'll see that Expr1004 is
exactly the name given to the expression you use in the ORDER BY.
I promised a possible improvement. I can't say if this works or not. The
idea is to eliminate one of the sort steps, at the cost of having to
scan a much bigger part of the table. If only a small number of rows in
your table satisfy the criteria in the WHERE clause, you can expect to
see performance plummetting. But if the WHERE clause is not very
selective and you were already reading most of your data anyway, then
this suggestion might help:
Change the table definition to:
CREATE TABLE dilink
(
intid SMALLINT NOT NULL,
ts SMALLDATETIME NOT NULL,
dr INT NOT NULL,
daddr INT NOT NULL,
ib FLOAT NOT NULL,
ob FLOAT NOT NULL,
CONSTRAINT dilink_intidtsdrdaddr
PRIMARY KEY (dr, daddr, intid, ts),
CONSTRAINT dilink_intidts
FOREIGN KEY (intid, ts)
REFERENCES didata (intid, ts) ON DELETE CASCADE
)
The only thing I changed is the order of columns in the primary key
constraint. This will als affect the order of columns in the index that
gets created for this constraint. The effect will be that the database
now has to scan the whole table to find the rows that match the WHERE
clause, but that the rows found will already be in order of dr, daddr
so that there is no need to sort in order to satsify the GROUP BY.
I must add that I *expect* this version to suck. It will only help you
if your table has very unusual data distribution!
If performance of your query is really critical, you'd be better advised
to change this query into an indexed view. That will result in instant
results when you want to see this data - but at the price of slower
inserts, updates and deletes (as SQL Server has to do extra work to keep
the indexed view current).
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Many thanks!
The most important criteria is a database size, SELECT speed
is the second one, so may be I will keep a current structure.
Thanks again!
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:kgao51t7i2cv1simi0o9p975bv01m9cbuv@.
4ax.com...
> On Tue, 12 Apr 2005 14:25:52 +0800, Tumurbaatar S. wrote:
>
> Hi Tumurbaatar,
> I think the query will be executed at about the maximum possible speed.
> The clustered index that is created because of the PRIMARY KEY
> constraint is optimal for this query. (Note - near the end of this
> message, I'll discuss one possible improvement).
> Allow me to use an analogy to explain: imagine that you have a phone
> book of a large town, were all people are listed in alphabetic order of
> the surname. Further, people with the same surname are ordered by first
> name. In database terms, the phone book is a table with clustered index
> on (Surname, FirstName).
> Now suppose you are given the task of finding all people with Surname
> 'Smith' and whose first name starts with H, I or J. That's not hard -
> you quickly flip to the page where the Smith's are listed, skip to the
> first whose name starts with H, then start scanning all names - and you
> stop as soon as you encounter a Smith whose first name starts with K.
> But what if your task had been to find all people with first name 'John'
> and surname starting with K, L, or M? In that case, you'd have to scan a
> much larger proportion of the phone book (the complete listing of all
> surnames starting with K, L, or M, to be precise). That's why I don't
> think that CBretana's suggestion will improve the speed of your query.
>
> The execution plan, you mean? Okay, take it easy. The best way to
> interpret an execution plan is from innermost to outermost. This plan is
> quite straightforward, actually :-)
> The last line says "Clustered Index S". That is the process I
> described above (in the phone book analogy) - the database uses the
> index' structure to go straight to the first row with intid = 1 and ts >
> '2005-4-1 00:00', then starts processing rows until it is past the last
> row with intid = 1 and ts <= '2005-4-1 00:30'.
> The line before that is a sort step. This is used to satisfy the group
> by clause - after the sort, all rows that need to be grouped together
> are together. (There are more strategies the optimizer can use for a
> group by, but in this case the optimizer expects the sorting to be the
> fastest).
> The Stream Aggregate step takes the sorted output and calculates one row
> from each group. If you inspect the entire line on your plan, you'll see
> the three aggregate expressions you used in the query appear here. Note
> that each result is given a name (Expr1002 through Expr1004).
> And the final step is another sort step - this time to satisfy the ORDER
> BY you specified. Note that the plan says to order by [Expr1004] - and
> if you check the Stream Aggregate step, you'll see that Expr1004 is
> exactly the name given to the expression you use in the ORDER BY.
>
> I promised a possible improvement. I can't say if this works or not. The
> idea is to eliminate one of the sort steps, at the cost of having to
> scan a much bigger part of the table. If only a small number of rows in
> your table satisfy the criteria in the WHERE clause, you can expect to
> see performance plummetting. But if the WHERE clause is not very
> selective and you were already reading most of your data anyway, then
> this suggestion might help:
> Change the table definition to:
> CREATE TABLE dilink
> (
> intid SMALLINT NOT NULL,
> ts SMALLDATETIME NOT NULL,
> dr INT NOT NULL,
> daddr INT NOT NULL,
> ib FLOAT NOT NULL,
> ob FLOAT NOT NULL,
> CONSTRAINT dilink_intidtsdrdaddr
> PRIMARY KEY (dr, daddr, intid, ts),
> CONSTRAINT dilink_intidts
> FOREIGN KEY (intid, ts)
> REFERENCES didata (intid, ts) ON DELETE CASCADE
> )
> The only thing I changed is the order of columns in the primary key
> constraint. This will als affect the order of columns in the index that
> gets created for this constraint. The effect will be that the database
> now has to scan the whole table to find the rows that match the WHERE
> clause, but that the rows found will already be in order of dr, daddr
> so that there is no need to sort in order to satsify the GROUP BY.
> I must add that I *expect* this version to suck. It will only help you
> if your table has very unusual data distribution!
>
> If performance of your query is really critical, you'd be better advised
> to change this query into an indexed view. That will result in instant
> results when you want to see this data - but at the price of slower
> inserts, updates and deletes (as SQL Server has to do extra work to keep
> the indexed view current).
>
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)

Primary Keys with Transactional Replication

I am pretty new to replication and have been setting it up in a test
environment using the test databases delivered during the sql install
(northwind and pubs).
I noticed that when I would setup Transactional Replicational (NON –
updateable subscriber) that the primary keys would NOT come over with tables
to the subscriber. But, if I set up Transactional Replication with
Updateable Subscriber, the primary keys would come over with the tables on
the subscriber. Am I missing something here? Or, is this indeed how it
works?
Hi Janet,
As Paul mentioned, transactional replication typically (or traditionally)
replicates the primary key as just a unique index. Assuming that you are
using a SQL2000 publisher, you can enable the 0x8000 (PKUKAsContraints)
article schema option so primary key will be replicated as primary key. The
behavior that you saw for updateable subscriber was our attempt to
"out-smart" the user as updateable subscriptions requires primary key
constraint (not just the index) at the subscriber to work properly.
-Raymond
"Janet" <Janet@.discussions.microsoft.com> wrote in message
news:A81443F2-9BEF-40B2-9236-3D91DD44D9BC@.microsoft.com...
>I am pretty new to replication and have been setting it up in a test
> environment using the test databases delivered during the sql install
> (northwind and pubs).
> I noticed that when I would setup Transactional Replicational (NON -
> updateable subscriber) that the primary keys would NOT come over with
> tables
> to the subscriber. But, if I set up Transactional Replication with
> Updateable Subscriber, the primary keys would come over with the tables on
> the subscriber. Am I missing something here? Or, is this indeed how it
> works?
>
>

Primary Keys When importing data

I am creating a stored procedure for our SQL server which does the following:
1. Drops a table called 'Teachers'

drop table [users_dd].[dbo].[Teachers]

2. Recreates that table ready for new data like so...

CREATE TABLE [users_dd].[dbo].[Teachers] (
[username] char (30) NOT NULL,
[SL_Teachcode] nvarchar (12) NULL,
[DueDate] smalldatetime DEFAULT GetDate(),
[ID] INT IDENTITY (1,1) PRIMARY KEY NOT NULL)

3. The last step attempts to import new data. I have used the import/export data wizard to create this query to import new data.

select [users].[username], [users].[SL_Teachcode]
from [users]
where [users].[Staff_teaching]=1
order by [users].[SL_Teachcode]

The last step returns an error because the ID field will not accept Nulls. Of course I don't want it to have nulls as this is to be the primary key for this table. What I want is an automatically generated number there but how do I make that happen? I thought it was all in the definition of the field, ie [ID] INT IDENTITY (1,1) PRIMARY KEY NOT NULL). It seems there is something I missing here however.

Can anybody tell me what I doing wrong (or not doing)?

Murrayuse some thing like:

insert into [users_dd].[dbo].[Teachers] (
[username],].[SL_Teachcode])
select [users].[username], [users].[SL_Teachcode]
from [users]
where [users].[Staff_teaching]=1
order by [users].[SL_Teachcode]

tel us if it works
i try it on my side , it works.
Ridwan,,,|||If you do not need transactional recovery during this load you might do one or a combination of the folowing.

Instead of delete the table with corresponding rows, you might truncate it first and then delete it, less logged and faster. If you do drop the table, you can use select into instead of insert as that is minimally logged and faster as well.

HTH|||SELECT INTO is not faster and is not recommended because of concurrency implications. Truncating and then deleting just doesn't make sence. Why not just TRUNCATE?|||select into is not faster than insert into? I beg to differ, as inserts are fully logged they will take longer. They will also bloat your transaction log and if you have to reallocate space on a drive that could really hurt performance and slow things down. With select into there is no question of drive space allocation and it will run faster. Just for kicks I just ran a select into statement for 1.4 million rows, it took 26 seconds. For that same structure and table an insert into took 2 minutes and 54 seconds.

Now, obviously the argument would be the log and possibly the database file was growing in this time span so to make it an even playing field I truncated the table and did it again with no hard drive growth. The insert into only took 15 seconds without the hard drive growth but after a truncate and a drop table the select into only took 9 seconds.

So, I do not see any situation where the insert into is as fast as the select into and you get the bonus of knowing the hard drive will not have to reallocate space for the log file.

Yes, I meant truncate then DROP the table, not delete. Truncate with delete does not make sense, agreed.|||yup, blurred it out too fast :o

rhigdon, you're absolutely right, - did a 1M-row select into in 20 seconds and insert has been running for the past 6 minutes (Personal edition).

Now you got me experimenting with increased preallocated trx. log.

But I also noticed that we're assuming that recovery mode is Simple. Will it be the same difference in speed if I have it set to Full? Well, I'm still waiting on my insert :)|||No problem :)

I would assume so about the full and simple, you can watch that t-log grow by playing with the dbcc sqlperf('logspace') before and after inserts or select into's.|||BTW, do you have your auto-grow settings enabled? Most of our apps here got converted from 6.5 to either directly 2K or to 7.0 then 2K. Since in 6.5 world we didn't have this luxury of auto-growing, once discovered, - we first got relieved, but then started noticing that for 150+ servers we will have to implement space utilization monitors based on activity within each database. And then - react really quickly once the size reaches the disk limitation. So instead we performed a 2-month space growth analysis and pre-estimated potential annual growth for each database. Then we pre-allocated space on both data and trx. log devices and turned off auto-growth feature. Now we can tell with the level of accuracy to +/- 10MB how much disk space will be needed for any given database by the beginning of the next year.|||Thanks Ridwan,
I tried that code and worked fine. I think I went wrong by using the import/exprot data wizard to construct my query. As soon as I did it 'by hand' it worked fine.
Interesting conversation about the difference between select into and insert into. In this example there will never be more than about 90 -100 records so speed is not a big issue to this task as it takes less than a second to run anyhow.
Thank you all for your input... I hope I can return the favour someday.
Murray|||rdjabarov - that sounds pretty interesting, yes, we use auto-grow for everything. I do have a script that uses some stored procs that runs daily to check for free space and alert me if it gets low though.

Primary keys lost after DTS

after performing export/dts from our database server to our hosting server,
the primary keys of each table in the destination server are lost. using the
dts wizard, i use the third option (copy database objects and data) and i di
d
make sure that the copy primary and foreign keys option is on. Can anyone
tell me how to solve the problem?
BTW, i did experience an error during the DTS process. "SetUser permission
denied." but still all database objects were transfered including the
contents except the primary keys. Does it had something to do with the
database account im using to connect to the server, which i think is limited
?In my own view is most clear generate scripts for each kind object (tables,
sp) and then PASTE into QA window and run them.
-Create the intended scripts and create the tables
-Transfer all the data(it's causing errors because of the table dependencies
but no problem at all, retry again all those ended in error)
--
current location: alicante (es)
"ads" wrote:
> after performing export/dts from our database server to our hosting server
,
> the primary keys of each table in the destination server are lost. using t
he
> dts wizard, i use the third option (copy database objects and data) and i
did
> make sure that the copy primary and foreign keys option is on. Can anyone
> tell me how to solve the problem?
> BTW, i did experience an error during the DTS process. "SetUser permission
> denied." but still all database objects were transfered including the
> contents except the primary keys. Does it had something to do with the
> database account im using to connect to the server, which i think is limited?[/col
or]

primary keys have gotten out of sequence

This is a multi-part message in MIME format.
--=_NextPart_000_0013_01C66A18.613BDBB0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
In Enterprise Manager, my primary keys used to be all in sequence. = After deleting a few pk and adding new ones, they are no longer in = sequence.
In Enterprise Manager, I would like to be able to view "Return all rows" = with the primary keys in numerical order.
What is the best way to accomplish this?
Thank you in advance,
TR
--=_NextPart_000_0013_01C66A18.613BDBB0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
In Enterprise Manager, my primary keys = used to be all in sequence. After deleting a few pk and adding new ones, they = are no longer in sequence.

In Enterprise Manager, I would like to = be able to view "Return all rows" with the primary keys in numerical = order.

What is the best way to accomplish this?

Thank you in advance,
TR
--=_NextPart_000_0013_01C66A18.613BDBB0--Timothy Ross wrote:
> In Enterprise Manager, my primary keys used to be all in sequence. After deleting a few pk and adding new ones, they are no longer in sequence.
> In Enterprise Manager, I would like to be able to view "Return all rows" with the primary keys in numerical order.
> What is the best way to accomplish this?
> Thank you in advance,
> TR
Tables have no inherent order. The only way to fix the order you see is
to use an ORDER BY clause in your SELECT statements. Enterprise Manager
won't do that for you - you need to write your own query. Preferably
use Query Analyzer instead because it's a much more powerful tool.
You'll need to familiarize yourself with SQL syntax first but that's
not so hard for the basic stuff. For example:
SELECT col1, col2, col3, ...
FROM your_table
ORDER BY col1 ;
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||David - Thank you for your quick response.
I understand that I can use query analyzer for sorting. I have actually
used Microsoft Access because this will allow sorting very easily.
I also understand that the primary keys really do not need to be displayed
in any particular order.
I have been told there is a command that will re-order my primary keys
sequentially so that I can view them in Enterprise Manager in the correct
order - it has something to do with INDEX on the column, but I haven't found
any information on how to accomplish his.
CREATE INDEX indexname tablename (column) -- doesn't reorder the primary key
the way that I want it to - I was wondering if there was a way to accomplish
this.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1146170512.392795.12240@.i40g2000cwc.googlegroups.com...
> Timothy Ross wrote:
>> In Enterprise Manager, my primary keys used to be all in sequence. After
>> deleting a few pk and adding new ones, they are no longer in sequence.
>> In Enterprise Manager, I would like to be able to view "Return all rows"
>> with the primary keys in numerical order.
>> What is the best way to accomplish this?
>> Thank you in advance,
>> TR
> Tables have no inherent order. The only way to fix the order you see is
> to use an ORDER BY clause in your SELECT statements. Enterprise Manager
> won't do that for you - you need to write your own query. Preferably
> use Query Analyzer instead because it's a much more powerful tool.
> You'll need to familiarize yourself with SQL syntax first but that's
> not so hard for the basic stuff. For example:
> SELECT col1, col2, col3, ...
> FROM your_table
> ORDER BY col1 ;
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||In Enterprise Manager, I just went into "Table indexes and properties" --
verified that my primary key was under the "Column name" and the "Order" was
"Ascending". I then checked "Create as CLUSTERED"
My primary key column is now reordered numerically.
TR
"Timothy Ross" <ross_timothy@.hotmail.com> wrote in message
news:u6ZSyxjaGHA.508@.TK2MSFTNGP02.phx.gbl...
> David - Thank you for your quick response.
> I understand that I can use query analyzer for sorting. I have actually
> used Microsoft Access because this will allow sorting very easily.
> I also understand that the primary keys really do not need to be displayed
> in any particular order.
> I have been told there is a command that will re-order my primary keys
> sequentially so that I can view them in Enterprise Manager in the correct
> order - it has something to do with INDEX on the column, but I haven't
> found any information on how to accomplish his.
> CREATE INDEX indexname tablename (column) -- doesn't reorder the primary
> key the way that I want it to - I was wondering if there was a way to
> accomplish this.
>
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:1146170512.392795.12240@.i40g2000cwc.googlegroups.com...
>> Timothy Ross wrote:
>> In Enterprise Manager, my primary keys used to be all in sequence.
>> After deleting a few pk and adding new ones, they are no longer in
>> sequence.
>> In Enterprise Manager, I would like to be able to view "Return all rows"
>> with the primary keys in numerical order.
>> What is the best way to accomplish this?
>> Thank you in advance,
>> TR
>> Tables have no inherent order. The only way to fix the order you see is
>> to use an ORDER BY clause in your SELECT statements. Enterprise Manager
>> won't do that for you - you need to write your own query. Preferably
>> use Query Analyzer instead because it's a much more powerful tool.
>> You'll need to familiarize yourself with SQL syntax first but that's
>> not so hard for the basic stuff. For example:
>> SELECT col1, col2, col3, ...
>> FROM your_table
>> ORDER BY col1 ;
>> --
>> David Portas, SQL Server MVP
>> Whenever possible please post enough code to reproduce your problem.
>> Including CREATE TABLE and INSERT statements usually helps.
>> State what version of SQL Server you are using and specify the content
>> of any error messages.
>> SQL Server Books Online:
>> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
>> --
>|||Timothy Ross wrote:
> In Enterprise Manager, I just went into "Table indexes and properties" --
> verified that my primary key was under the "Column name" and the "Order" was
> "Ascending". I then checked "Create as CLUSTERED"
> My primary key column is now reordered numerically.
>
You can create a clustered index but all that does is to fix the order
of data as it is stored. This doesn't guarantee that the data will
always be returned in the same order. Although in some cases it may
appear to give that effect you shouldn't rely on it.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||David - I agree that if I delete some of my primary keys and add some new
ones that I would have to do this again to re-order them - it will not be
automatic.
TR
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1146174590.856030.154560@.j73g2000cwa.googlegroups.com...
> Timothy Ross wrote:
>> In Enterprise Manager, I just went into "Table indexes and properties" --
>> verified that my primary key was under the "Column name" and the "Order"
>> was
>> "Ascending". I then checked "Create as CLUSTERED"
>> My primary key column is now reordered numerically.
> You can create a clustered index but all that does is to fix the order
> of data as it is stored. This doesn't guarantee that the data will
> always be returned in the same order. Although in some cases it may
> appear to give that effect you shouldn't rely on it.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||Timothy
This is NOT what David is saying.
There is absolutely no guarantee what order the rows from a table will be
returned, with a clustered index or without one. The only way to be sure
that you get the data in the order you want it is to request it to be sorted
with ORDER BY.
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Timothy Ross" <ross_timothy@.hotmail.com> wrote in message
news:eeUdtakaGHA.1192@.TK2MSFTNGP04.phx.gbl...
> David - I agree that if I delete some of my primary keys and add some new
> ones that I would have to do this again to re-order them - it will not be
> automatic.
> TR
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:1146174590.856030.154560@.j73g2000cwa.googlegroups.com...
>> Timothy Ross wrote:
>> In Enterprise Manager, I just went into "Table indexes and
>> properties" --
>> verified that my primary key was under the "Column name" and the "Order"
>> was
>> "Ascending". I then checked "Create as CLUSTERED"
>> My primary key column is now reordered numerically.
>>
>> You can create a clustered index but all that does is to fix the order
>> of data as it is stored. This doesn't guarantee that the data will
>> always be returned in the same order. Although in some cases it may
>> appear to give that effect you shouldn't rely on it.
>> --
>> David Portas, SQL Server MVP
>> Whenever possible please post enough code to reproduce your problem.
>> Including CREATE TABLE and INSERT statements usually helps.
>> State what version of SQL Server you are using and specify the content
>> of any error messages.
>> SQL Server Books Online:
>> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
>> --
>|||Not sure what you are talking about - I did order the rows -- verified that
my primary key was under the "Column name" and the "Order" was
"Ascending"
This re-ordered my primary key column numerically.
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:e761cnkaGHA.3720@.TK2MSFTNGP03.phx.gbl...
> Timothy
> This is NOT what David is saying.
> There is absolutely no guarantee what order the rows from a table will be
> returned, with a clustered index or without one. The only way to be sure
> that you get the data in the order you want it is to request it to be
> sorted with ORDER BY.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "Timothy Ross" <ross_timothy@.hotmail.com> wrote in message
> news:eeUdtakaGHA.1192@.TK2MSFTNGP04.phx.gbl...
>> David - I agree that if I delete some of my primary keys and add some new
>> ones that I would have to do this again to re-order them - it will not be
>> automatic.
>> TR
>> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
>> news:1146174590.856030.154560@.j73g2000cwa.googlegroups.com...
>> Timothy Ross wrote:
>> In Enterprise Manager, I just went into "Table indexes and
>> properties" --
>> verified that my primary key was under the "Column name" and the
>> "Order" was
>> "Ascending". I then checked "Create as CLUSTERED"
>> My primary key column is now reordered numerically.
>>
>> You can create a clustered index but all that does is to fix the order
>> of data as it is stored. This doesn't guarantee that the data will
>> always be returned in the same order. Although in some cases it may
>> appear to give that effect you shouldn't rely on it.
>> --
>> David Portas, SQL Server MVP
>> Whenever possible please post enough code to reproduce your problem.
>> Including CREATE TABLE and INSERT statements usually helps.
>> State what version of SQL Server you are using and specify the content
>> of any error messages.
>> SQL Server Books Online:
>> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
>> --
>>
>|||I guess I misunderstood what you were talking about. I don't see how you can
have the primary key column NOT in order if you use ORDER BY when retrieving
the data.
But if you're satisfied, that's fine (for now).
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Timothy Ross" <ross_timothy@.hotmail.com> wrote in message
news:eozjgWtaGHA.3720@.TK2MSFTNGP03.phx.gbl...
> Not sure what you are talking about - I did order the rows -- verified
> that my primary key was under the "Column name" and the "Order" was
> "Ascending"
> This re-ordered my primary key column numerically.
>
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:e761cnkaGHA.3720@.TK2MSFTNGP03.phx.gbl...
>> Timothy
>> This is NOT what David is saying.
>> There is absolutely no guarantee what order the rows from a table will be
>> returned, with a clustered index or without one. The only way to be sure
>> that you get the data in the order you want it is to request it to be
>> sorted with ORDER BY.
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> www.solidqualitylearning.com
>>
>> "Timothy Ross" <ross_timothy@.hotmail.com> wrote in message
>> news:eeUdtakaGHA.1192@.TK2MSFTNGP04.phx.gbl...
>> David - I agree that if I delete some of my primary keys and add some
>> new ones that I would have to do this again to re-order them - it will
>> not be automatic.
>> TR
>> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
>> news:1146174590.856030.154560@.j73g2000cwa.googlegroups.com...
>> Timothy Ross wrote:
>> In Enterprise Manager, I just went into "Table indexes and
>> properties" --
>> verified that my primary key was under the "Column name" and the
>> "Order" was
>> "Ascending". I then checked "Create as CLUSTERED"
>> My primary key column is now reordered numerically.
>>
>> You can create a clustered index but all that does is to fix the order
>> of data as it is stored. This doesn't guarantee that the data will
>> always be returned in the same order. Although in some cases it may
>> appear to give that effect you shouldn't rely on it.
>> --
>> David Portas, SQL Server MVP
>> Whenever possible please post enough code to reproduce your problem.
>> Including CREATE TABLE and INSERT statements usually helps.
>> State what version of SQL Server you are using and specify the content
>> of any error messages.
>> SQL Server Books Online:
>> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
>> --
>>
>>
>|||On Thu, 27 Apr 2006 16:52:04 -0400, "Timothy Ross"
<ross_timothy@.hotmail.com> wrote:
>I have been told there is a command that will re-order my primary keys
>sequentially so that I can view them in Enterprise Manager in the correct
>order - it has something to do with INDEX on the column, but I haven't found
>any information on how to accomplish his.
If you have a clustered index (or primary key) on the table, then the
physical and logical order will be the same.
But even then, a select statement without an order-by does not seem to
guarantee things will display in order. Just why not, I do not really
understand. May have to do with reuse of freed pages and order of
insertion, so the logical and physical *page* order are not the same,
irrespective of the logical and physical *row* orders.
Josh|||> But even then, a select statement without an order-by does not seem to
> guarantee things will display in order. Just why not, I do not really
> understand. May have to do with reuse of freed pages and order of
> insertion, so the logical and physical *page* order are not the same,
> irrespective of the logical and physical *row* orders.
SQL Server is free to return data in any sequence (e.g. most efficient
manner) unless the query contains ORDER BY. For example, there is a
'merry-go-round' scan feature that allows multiple concurrent queries to
'piggyback' on a scan already in progress. Once the later queries retrieve
the last row, the scan resumes at the beginning and continues until the
initially retrieved row is encountered. This could result in rows returned
in an arbitrary order.
It's good to have an understanding of the physical implementation for
performance analysis. However, you can rely only on the result described by
the SQL query.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:gb88529oa6vkkdom9dgbtv9gfhhkh16u7e@.4ax.com...
> On Thu, 27 Apr 2006 16:52:04 -0400, "Timothy Ross"
> <ross_timothy@.hotmail.com> wrote:
>>I have been told there is a command that will re-order my primary keys
>>sequentially so that I can view them in Enterprise Manager in the correct
>>order - it has something to do with INDEX on the column, but I haven't
>>found
>>any information on how to accomplish his.
> If you have a clustered index (or primary key) on the table, then the
> physical and logical order will be the same.
> But even then, a select statement without an order-by does not seem to
> guarantee things will display in order. Just why not, I do not really
> understand. May have to do with reuse of freed pages and order of
> insertion, so the logical and physical *page* order are not the same,
> irrespective of the logical and physical *row* orders.
> Josh
>sql

primary keys have gotten out of sequence

In Enterprise Manager, my primary keys used to be all in sequence. After de
leting a few pk and adding new ones, they are no longer in sequence.
In Enterprise Manager, I would like to be able to view "Return all rows" wit
h the primary keys in numerical order.
What is the best way to accomplish this?
Thank you in advance,
TRTimothy Ross wrote:
> In Enterprise Manager, my primary keys used to be all in sequence. After
deleting a few pk and adding new ones, they are no longer in sequence.
> In Enterprise Manager, I would like to be able to view "Return all rows" w
ith the primary keys in numerical order.
> What is the best way to accomplish this?
> Thank you in advance,
> TR
Tables have no inherent order. The only way to fix the order you see is
to use an ORDER BY clause in your SELECT statements. Enterprise Manager
won't do that for you - you need to write your own query. Preferably
use Query Analyzer instead because it's a much more powerful tool.
You'll need to familiarize yourself with SQL syntax first but that's
not so hard for the basic stuff. For example:
SELECT col1, col2, col3, ...
FROM your_table
ORDER BY col1 ;
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||David - Thank you for your quick response.
I understand that I can use query analyzer for sorting. I have actually
used Microsoft Access because this will allow sorting very easily.
I also understand that the primary keys really do not need to be displayed
in any particular order.
I have been told there is a command that will re-order my primary keys
sequentially so that I can view them in Enterprise Manager in the correct
order - it has something to do with INDEX on the column, but I haven't found
any information on how to accomplish his.
CREATE INDEX indexname tablename (column) -- doesn't reorder the primary key
the way that I want it to - I was wondering if there was a way to accomplish
this.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1146170512.392795.12240@.i40g2000cwc.googlegroups.com...
> Timothy Ross wrote:
> Tables have no inherent order. The only way to fix the order you see is
> to use an ORDER BY clause in your SELECT statements. Enterprise Manager
> won't do that for you - you need to write your own query. Preferably
> use Query Analyzer instead because it's a much more powerful tool.
> You'll need to familiarize yourself with SQL syntax first but that's
> not so hard for the basic stuff. For example:
> SELECT col1, col2, col3, ...
> FROM your_table
> ORDER BY col1 ;
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||On Thu, 27 Apr 2006 16:52:04 -0400, "Timothy Ross"
<ross_timothy@.hotmail.com> wrote:
>I have been told there is a command that will re-order my primary keys
>sequentially so that I can view them in Enterprise Manager in the correct
>order - it has something to do with INDEX on the column, but I haven't foun
d
>any information on how to accomplish his.
If you have a clustered index (or primary key) on the table, then the
physical and logical order will be the same.
But even then, a select statement without an order-by does not seem to
guarantee things will display in order. Just why not, I do not really
understand. May have to do with reuse of freed pages and order of
insertion, so the logical and physical *page* order are not the same,
irrespective of the logical and physical *row* orders.
Josh|||> But even then, a select statement without an order-by does not seem to
> guarantee things will display in order. Just why not, I do not really
> understand. May have to do with reuse of freed pages and order of
> insertion, so the logical and physical *page* order are not the same,
> irrespective of the logical and physical *row* orders.
SQL Server is free to return data in any sequence (e.g. most efficient
manner) unless the query contains ORDER BY. For example, there is a
'merry-go-round' scan feature that allows multiple concurrent queries to
'piggyback' on a scan already in progress. Once the later queries retrieve
the last row, the scan resumes at the beginning and continues until the
initially retrieved row is encountered. This could result in rows returned
in an arbitrary order.
It's good to have an understanding of the physical implementation for
performance analysis. However, you can rely only on the result described by
the SQL query.
Hope this helps.
Dan Guzman
SQL Server MVP
"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:gb88529oa6vkkdom9dgbtv9gfhhkh16u7e@.
4ax.com...
> On Thu, 27 Apr 2006 16:52:04 -0400, "Timothy Ross"
> <ross_timothy@.hotmail.com> wrote:
> If you have a clustered index (or primary key) on the table, then the
> physical and logical order will be the same.
> But even then, a select statement without an order-by does not seem to
> guarantee things will display in order. Just why not, I do not really
> understand. May have to do with reuse of freed pages and order of
> insertion, so the logical and physical *page* order are not the same,
> irrespective of the logical and physical *row* orders.
> Josh
>

Primary Keys and Transactional Repl

I have read through the materials and still don't feel like I have a
definitive answer to the following: does (one way) transactional replication
require a primary key on all articles/tables?
THx.
Abosolutely!
Hilary
"CLM" <CLM@.discussions.microsoft.com> wrote in message
news:BB9CFBC7-A26E-4152-978E-AE4BA56F47A4@.microsoft.com...
>I have read through the materials and still don't feel like I have a
> definitive answer to the following: does (one way) transactional
> replication
> require a primary key on all articles/tables?
> THx.
|||ANY transactional replication(1 way, immediate,queued) requires PK's

Primary keys ...

I was setting up replication and discovered that explicitly defined primary
keys are required on the tables being replicated. We currenty have defined
these on exactly zero of our tables (legacy thing - you get the picture). I
told the developers I needed them. They asked me if it would break anything
if we put them on. I'm thinking that as long as we don't define explicit
PK/FK relationshiops bewteen the tables we should be OK.
I would appreciate any insight on the matter.
Bob Castleman
DBA PoseurOne idea would be to add a UniqueIdentifier field and set it to be the
RowGuidCol and the PK (along with a default of NewId()). By doing this, you
avoid SQL adding this column for you anyway for replication.
Thomas
"Bob Castleman" <nomail@.here> wrote in message
news:uNvZICqRFHA.2528@.TK2MSFTNGP10.phx.gbl...
>I was setting up replication and discovered that explicitly defined primary
>keys are required on the tables being replicated. We currenty have defined
>these on exactly zero of our tables (legacy thing - you get the picture). I
>told the developers I needed them. They asked me if it would break anything
if
>we put them on. I'm thinking that as long as we don't define explicit PK/FK
>relationshiops bewteen the tables we should be OK.
> I would appreciate any insight on the matter.
> Bob Castleman
> DBA Poseur
>|||We already have unique IDs on the tables, they were just never explicitly
defined within SQL Server as PKs. We may at some point start defining PK/FK
relationships and referential integrity so wouldn't make sense to use our
current IDs? I am more concerned about unintended side effects. I can't see
how defining a PK on table would cause a problem, but I need to make sure.
Bob
"Thomas" <replyingroup@.anywhere.com> wrote in message
news:eMO7hZqRFHA.244@.TK2MSFTNGP12.phx.gbl...
> One idea would be to add a UniqueIdentifier field and set it to be the
> RowGuidCol and the PK (along with a default of NewId()). By doing this,
> you avoid SQL adding this column for you anyway for replication.
>
> Thomas
>
> "Bob Castleman" <nomail@.here> wrote in message
> news:uNvZICqRFHA.2528@.TK2MSFTNGP10.phx.gbl...
>|||As long as the data is actually unique, it shouldn't be a problem.
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"Bob Castleman" <nomail@.here> wrote in message
news:%23PkbQfqRFHA.648@.TK2MSFTNGP14.phx.gbl...
> We already have unique IDs on the tables, they were just never explicitly
> defined within SQL Server as PKs. We may at some point start defining
> PK/FK relationships and referential integrity so wouldn't make sense to
> use our current IDs? I am more concerned about unintended side effects. I
> can't see how defining a PK on table would cause a problem, but I need to
> make sure.
> Bob
> "Thomas" <replyingroup@.anywhere.com> wrote in message
> news:eMO7hZqRFHA.244@.TK2MSFTNGP12.phx.gbl...
>|||Why should I consider using an auxiliary numbers table?
http://www.aspfaq.com/show.asp?id=2516
use northwind
go
select
identity(int, 1, 1) as number
into
number
from
sysobjects as a cross join sysobjects as b
go
declare @.sql nvarchar(4000)
declare @.s datetime
declare @.e datetime
declare @.i int
declare @.datepart varchar(15)
set @.s = '2005-01-01T12:00:00.000'
set @.e = '2005-01-01T13:00:00.000'
set @.i = 15
set @.datepart = 'minute'
set @.sql = N'
select
right(convert(varchar(35), dateadd(' + @.datepart + N', number, ''' +
convert(varchar(25), @.s, 126) + N'''), 100), 7) as colA
from
number as n
where
number % ' + ltrim(@.i) + N' = 0
and dateadd(' + @.datepart + N', number, ''' + convert(varchar(25), @.s, 126)
+ N''') < cast(''' + convert(varchar(25), @.e, 126) + N''' as datetime)'
print @.sql
exec sp_executesql @.sql
go
drop table number
go
The Curse and Blessings of Dynamic SQL
http://www.sommarskog.se/dynamic_sql.html
AMB
"Bob Castleman" wrote:

> I was setting up replication and discovered that explicitly defined primar
y
> keys are required on the tables being replicated. We currenty have defined
> these on exactly zero of our tables (legacy thing - you get the picture).
I
> told the developers I needed them. They asked me if it would break anythin
g
> if we put them on. I'm thinking that as long as we don't define explicit
> PK/FK relationshiops bewteen the tables we should be OK.
> I would appreciate any insight on the matter.
> Bob Castleman
> DBA Poseur
>
>|||Sorry, wrong place.
AMB
"Alejandro Mesa" wrote:
> Why should I consider using an auxiliary numbers table?
> http://www.aspfaq.com/show.asp?id=2516
> use northwind
> go
> select
> identity(int, 1, 1) as number
> into
> number
> from
> sysobjects as a cross join sysobjects as b
> go
>
> declare @.sql nvarchar(4000)
> declare @.s datetime
> declare @.e datetime
> declare @.i int
> declare @.datepart varchar(15)
> set @.s = '2005-01-01T12:00:00.000'
> set @.e = '2005-01-01T13:00:00.000'
> set @.i = 15
> set @.datepart = 'minute'
> set @.sql = N'
> select
> right(convert(varchar(35), dateadd(' + @.datepart + N', number, ''' +
> convert(varchar(25), @.s, 126) + N'''), 100), 7) as colA
> from
> number as n
> where
> number % ' + ltrim(@.i) + N' = 0
> and dateadd(' + @.datepart + N', number, ''' + convert(varchar(25), @.s, 12
6)
> + N''') < cast(''' + convert(varchar(25), @.e, 126) + N''' as datetime)'
> print @.sql
> exec sp_executesql @.sql
> go
> drop table number
> go
>
> The Curse and Blessings of Dynamic SQL
> http://www.sommarskog.se/dynamic_sql.html
>
> AMB
>
> "Bob Castleman" wrote:
>|||If that's the case, then just declare those columns as the PK. You can decla
re a
PK without having to declare FK (although it helps with data integrity).
The only problems you might encounter by setting a given column(s) as the PK
are:
1. If the data is not unique or contains nulls
or
2. If the the app that writes the data expects that it can fill in duplicate
or
null data on one pass even if it changes it to be non-nullable and unique in
another pass. Obviously, this won't work as SQL will prevent any nulls or
duplciate values from ever being written.
Thomas
"Bob Castleman" <nomail@.here> wrote in message
news:%23PkbQfqRFHA.648@.TK2MSFTNGP14.phx.gbl...
> We already have unique IDs on the tables, they were just never explicitly
> defined within SQL Server as PKs. We may at some point start defining PK/F
K
> relationships and referential integrity so wouldn't make sense to use our
> current IDs? I am more concerned about unintended side effects. I can't se
e
> how defining a PK on table would cause a problem, but I need to make sure.
> Bob
> "Thomas" <replyingroup@.anywhere.com> wrote in message
> news:eMO7hZqRFHA.244@.TK2MSFTNGP12.phx.gbl...
>

Primary Keys - Not Replicating

It appears that when performing a push replicaton of the transactional type
that the primary keys are not being created. In table design they show up as
clustered unique indexes type = index instead of unique constraints/ type =
primary
the clustered unique indexes are really the functionally equivalent of
primary keys (with the exception that a unique index will allow a single
null).
To replicate PK's as PK's right click on publication your publication,
select properties, click on the articles tab, and in the browse button,
click the three ellipses to the right of the article. In the snapshot tab,
click Include DRI.
"scovillesm" <scovillesm@.discussions.microsoft.com> wrote in message
news:4667423F-9248-446C-8EAB-04A67AC8E78E@.microsoft.com...
> It appears that when performing a push replicaton of the transactional
type
> that the primary keys are not being created. In table design they show up
as
> clustered unique indexes type = index instead of unique constraints/ type
=
> primary

Primary Keys

HI ,

I accidently removed the primary keys from my table by mistake. Is there anyway ,That i can get the PK's back to what is used to be. Need Help pls..... When I try "resetting" the PK I kep getting this error:

'table_name' table
- Unable to create index 'PK_tablename'.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]CREATE UNIQUE INDEX terminated because a duplicate key was found for index ID 1. Most significant primary key is '1'.
[Microsoft][ODBC SQL Server Driver][SQL Server]Could not create constraint. See previous errors.
[Microsoft][ODBC SQL Server Driver][SQL Server]Warning: The table 'tDetail' has been created but its maximum row size (12521) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes.
[Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been terminated.Before recreating the index you should handle all the rows that do not stand in the index condition (create duplicate- the Error root cause) .

You should do it by:
1. delete those rows
2. group by those rows
3. change Id of those rows|||Thanx for the help..but how do i do that?|||Thnx for the help...but how do I do that ...do I first delete all duplicates form the server and then re-assign the primary keys?

Yes !
1. first delete
2.re-assign the primary keys|||Originally posted by eschapir
Thnx for the help...but how do I do that ...do I first delete all duplicates form the server and then re-assign the primary keys?

Yes !
1. first delete
2.re-assign the primary keys

thanx

Primary Keys

Hi everyone,
Does someone knows how can I drop a primary key (that I don't know the name) from a table in one sql statement.
Thanks,
FadyEnterprise Manager ?|||I know I can use enterprise manager and I know that I can get the name of the primary key from sysobjects in a query but I need to know if I can in one sql statement (using alter table ... drop constraint as example) drop the primary key for a certain table.|||One of the odder requests seen on this forum.

To do it as a single statement you would need to EXECUTE an SQL String that you construct on the fly from the schema tables.

And I can't believe there would be a good reason for doing this.

primary keys

"Violation of PRIMARY KEY of restriction 'PK_Approve_Overtime'. The overlapping key cannot be inserted in object 'Dbo.Approve_Overtime'. The statement was ended."

can soemone explain to me why i have this kind of error?

i have this two tables. approve_overtime table has a primary key id_no and application_input table with a primary key of id_no!

all the values from of application_input will be stored also in approve_overtime.

sometimes the datas can be stored.sometimes it cannot and produces an error!

what do u think?

hmmm pls help!

Check if you are wanting to insert duplicate id_no into approve_overtime ?

primary keys

I am using the IDENTITY feature to generate the values fo rmy primary keys. I understand that primary keys need to be unique.

If I undestand correctly then they need to be uniquely identified in all table with in the sam database.

I am not sure if when I create a primary key using this feature. Do I need to change the seed and increment values in order to have primary keys for each table that are truely unique? This seems like the way to make all of the PK values in a database unique.

-Thanks to anyone that may be able to help

First off, a reminder that just setting an identity column doesn't make a row unique to the user. Having values:

Identity Name

1 Fred

2 Fred

Will make the two rows indistiguishable. So an Alternate Key on Name would make this far more ideal.

And yes, if you truly need your integer identity index keys to be unique in a database, a combination of seed and a check constraint will work:

create table first
(
firstId int identity(1,1) check (firstId between 1 and 100)
)
create table second
(
secondId int identity(101,1) check (secondId between 101 and 200)
)
go
insert into first default values;
insert into first default values;
insert into second default values;
insert into second default values;

select *

from first

select *

from second

firstId

--

1

2

secondId

--

101

102

Another method is to just use a guid for your keys. Of course guids are much larger and unwieldly to work with, but they are another way to go.

A big question is why do you need the values to be unique in the database? Just having them unique in the table they are in is generally good enough. It will definitely make it easier to work with since you won't have to be concerned with ranges of values later, especially if the data grows greater than initially expected.

|||

Primary key is the Domain Integrity Constraint. Here the Domain means a single table. Primary key insures the uniqueness of the table (single domain). It never validates other tables. Practically your understanding about PK is not possible. Bcs the number of records in each table is non-deterministic values. You can't make any assumption to accommodate this. Think about performance if i have more tables in my database.

But, UNIQUEIDENTIFIER typed values are used to identify the unique value across the table/database/server. Since it use the GUID on every generation the GUID must be unique.

|||

Thanks for the info. I need to do some studying on domain constraints and the different things that can be used to maintain integrity in this type of situation.

-Thanks

|||

Thanks for the info. I need to do some studying on domain constraints and the different things that can be used to maintain integrity in this type of situation.

-Thanks for your help