Preventing Deadlock: primary

Showing posts with label primary. Show all posts

Monday, March 26, 2012

primay key

How do you assign a primary key to a column in SQL 2005? Thanks.Hi
CREATE TABLE dbo.Test (c INT NOT NULL PRIMARY KEY)
"00KobeBrian" <a@.b.com> wrote in message
news:eVQgpKz8GHA.2248@.TK2MSFTNGP04.phx.gbl...
> How do you assign a primary key to a column in SQL 2005? Thanks.
>|||00KobeBrian wrote:
> How do you assign a primary key to a column in SQL 2005? Thanks.
ALTER TABLE tbl ADD CONSTRAINT tbl_pk PRIMARY KEY (col);
--
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
--|||Sorry. How do you do that in SQL Server Management Studio?
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1161239072.297334.114770@.b28g2000cwb.googlegroups.com...
> 00KobeBrian wrote:
>> How do you assign a primary key to a column in SQL 2005? Thanks.
> ALTER TABLE tbl ADD CONSTRAINT tbl_pk PRIMARY KEY (col);
> --
> 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 Oct 19, 11:48 am, "00KobeBrian" <a...@.b.com> wrote:
> Sorry. How do you do that in SQL Server Management Studio?
> "David Portas" <REMOVE_BEFORE_REPLYING_dpor...@.acm.org> wrote in messagenews:1161239072.297334.114770@.b28g2000cwb.googlegroups.com...
>
> > 00KobeBrian wrote:
> >> How do you assign a primary key to a column in SQL 2005? Thanks.
> > ALTER TABLE tbl ADD CONSTRAINT tbl_pk PRIMARY KEY (col);
> > --
> > 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
> > -- Hide quoted text -- Show quoted text -
right click on table - select modify table.
select column you want to set as primary key and right click and select
option to set primary key
Regards
Amish Shah
http://shahamishm.tripod.com|||Can anyone please help?
"00KobeBrian" <a@.b.com> wrote in message
news:%23w0gVq08GHA.788@.TK2MSFTNGP05.phx.gbl...
> Sorry. How do you do that in SQL Server Management Studio?
>
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:1161239072.297334.114770@.b28g2000cwb.googlegroups.com...
>> 00KobeBrian wrote:
>> How do you assign a primary key to a column in SQL 2005? Thanks.
>> ALTER TABLE tbl ADD CONSTRAINT tbl_pk PRIMARY KEY (col);
>> --
>> 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 mean like SQL 2000. All I do is open up the Enterprise Manager and click
the key icon in the top to the column of a table and then the primary index
get assigned. How can I do it similiarly in SQL 2005? There got to be a way,
right? Thanks.
"00KobeBrian" <a@.b.com> wrote in message
news:%23w0gVq08GHA.788@.TK2MSFTNGP05.phx.gbl...
> Sorry. How do you do that in SQL Server Management Studio?
>
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:1161239072.297334.114770@.b28g2000cwb.googlegroups.com...
>> 00KobeBrian wrote:
>> How do you assign a primary key to a column in SQL 2005? Thanks.
>> ALTER TABLE tbl ADD CONSTRAINT tbl_pk PRIMARY KEY (col);
>> --
>> 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
>> --
>|||Right click mouse on the column...
"00KobeBrian" <a@.b.com> wrote in message
news:e4leM28AHHA.3316@.TK2MSFTNGP02.phx.gbl...
>I mean like SQL 2000. All I do is open up the Enterprise Manager and click
>the key icon in the top to the column of a table and then the primary index
>get assigned. How can I do it similiarly in SQL 2005? There got to be a
>way, right? Thanks.
>
> "00KobeBrian" <a@.b.com> wrote in message
> news:%23w0gVq08GHA.788@.TK2MSFTNGP05.phx.gbl...
>> Sorry. How do you do that in SQL Server Management Studio?
>>
>> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
>> news:1161239072.297334.114770@.b28g2000cwb.googlegroups.com...
>> 00KobeBrian wrote:
>> How do you assign a primary key to a column in SQL 2005? Thanks.
>> ALTER TABLE tbl ADD CONSTRAINT tbl_pk PRIMARY KEY (col);
>> --
>> 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
>> --
>>
>|||00KobeBrian wrote:
> I mean like SQL 2000. All I do is open up the Enterprise Manager and click
> the key icon in the top to the column of a table and then the primary index
> get assigned. How can I do it similiarly in SQL 2005? There got to be a way,
> right? Thanks.
>
On Server Management Studio, create the table and right click on the
left one column. Select "Set Primary Key" from the popup menu.
--
Arto Viitanen, CSC Ltd.
Espoo, Finland|||Hi Guys,
I cannot find "Set Primary Key" from the popup menu. It only has
new column, modify, rename, delete, refresh and properties. In properties
page, the primary key field is grey out. I cannot change from no to yes.
Please help. Thanks.
"Arto Viitanen" <arto.viitanen@.csc.fi> wrote in message
news:4552da27$0$8957$ba624cd0@.newsread.funet.fi...
> 00KobeBrian wrote:
>> I mean like SQL 2000. All I do is open up the Enterprise Manager and
>> click the key icon in the top to the column of a table and then the
>> primary index get assigned. How can I do it similiarly in SQL 2005? There
>> got to be a way, right? Thanks.
> On Server Management Studio, create the table and right click on the
> left one column. Select "Set Primary Key" from the popup menu.
> --
> Arto Viitanen, CSC Ltd.
> Espoo, Finland|||Can anyone please help? Thanks.
"00KobeBrian" <a@.b.com> wrote in message
news:ubG6kP9AHHA.996@.TK2MSFTNGP02.phx.gbl...
> Hi Guys,
> I cannot find "Set Primary Key" from the popup menu. It only
> has new column, modify, rename, delete, refresh and properties. In
> properties page, the primary key field is grey out. I cannot change from
> no to yes. Please help. Thanks.
> "Arto Viitanen" <arto.viitanen@.csc.fi> wrote in message
> news:4552da27$0$8957$ba624cd0@.newsread.funet.fi...
>> 00KobeBrian wrote:
>> I mean like SQL 2000. All I do is open up the Enterprise Manager and
>> click the key icon in the top to the column of a table and then the
>> primary index get assigned. How can I do it similiarly in SQL 2005?
>> There got to be a way, right? Thanks.
>>
>> On Server Management Studio, create the table and right click on the
>> left one column. Select "Set Primary Key" from the popup menu.
>> --
>> Arto Viitanen, CSC Ltd.
>> Espoo, Finland
>|||00KobeBrian wrote:
> Can anyone please help? Thanks.
>
The best answer is the one already given: use ALTER TABLE ADD
CONSTRAINT. That way if it doesn't work you'll get an error message
that will explain why it won't work. I recommend you do not use the
Enterprise Manager / Management Studio interface to modify a production
system. The GUI can be unreliable in some respects and doesn't give you
as much control as using the SQL syntax.
As for the reason why you can't create a PK using the GUI - there could
be several possibilities. Maybe the table already has a PK (you can't
create another) or maybe the column(s) is/are nullable or of a datatype
that isn't valid for a Primary Key.
--
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
--|||Thanks for the sharing. I understand it is better to issue a command. On
the other hand, I just found out that set primary key icon is grey out. I
don't know if there is any problem with my database or not and want to find
out. Do you have any idea? Thanks.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1163119406.397364.220710@.b28g2000cwb.googlegroups.com...
> 00KobeBrian wrote:
>> Can anyone please help? Thanks.
> The best answer is the one already given: use ALTER TABLE ADD
> CONSTRAINT. That way if it doesn't work you'll get an error message
> that will explain why it won't work. I recommend you do not use the
> Enterprise Manager / Management Studio interface to modify a production
> system. The GUI can be unreliable in some respects and doesn't give you
> as much control as using the SQL syntax.
> As for the reason why you can't create a PK using the GUI - there could
> be several possibilities. Maybe the table already has a PK (you can't
> create another) or maybe the column(s) is/are nullable or of a datatype
> that isn't valid for a Primary Key.
> --
> 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
> --
>|||If its greyed out, then another column is already chosen as a PK.
Its a bit confusing to explain the GUI issues, may be a screenshot will
help.
"00KobeBrian" <a@.b.com> wrote in message
news:u3WPMpGBHHA.3560@.TK2MSFTNGP03.phx.gbl...
> Thanks for the sharing. I understand it is better to issue a command. On
> the other hand, I just found out that set primary key icon is grey out. I
> don't know if there is any problem with my database or not and want to
> find out. Do you have any idea? Thanks.
>
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:1163119406.397364.220710@.b28g2000cwb.googlegroups.com...
>> 00KobeBrian wrote:
>> Can anyone please help? Thanks.
>>
>> The best answer is the one already given: use ALTER TABLE ADD
>> CONSTRAINT. That way if it doesn't work you'll get an error message
>> that will explain why it won't work. I recommend you do not use the
>> Enterprise Manager / Management Studio interface to modify a production
>> system. The GUI can be unreliable in some respects and doesn't give you
>> as much control as using the SQL syntax.
>> As for the reason why you can't create a PK using the GUI - there could
>> be several possibilities. Maybe the table already has a PK (you can't
>> create another) or maybe the column(s) is/are nullable or of a datatype
>> that isn't valid for a Primary Key.
>> --
>> 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
>> --
>

Primarykey Fields

I'm try to get table columns name & primary key to generat Dlete/Insert
script to all my database tables..
I was able to get list of all user tables from sysobject and columns list
from syscolumns..
Now i need to know the primary key column (For delete Statments)..
How could i detrmine which column is primarykey or composite key'
thanxA couple of suggestions:
First, you may be trying to reinvent the wheel; have you looked at
using SQL-DMO or SCPTXFR to script out your database? May save you a
lot of time and energy.
Second, wherever possible, use the INFORMATION_SCHEMA views rather than
the system tables to query this type of information; look at the
following queries i nthe pubs database as an example:
SELECT *
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE TABLE_NAME = 'authors'
SELECT *
FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
WHERE TABLE_NAME = 'authors'
Note that the identification of a primary key is a constraint on the
table, not a property of a column.
Of course, this only rings true for SQL Server 2000; no clue about
SS2005.
HTH,
Stu|||Islamegy (NULL_Islamegy_NULL@.yahoo.com) writes:
> I'm try to get table columns name & primary key to generat Dlete/Insert
> script to all my database tables..
> I was able to get list of all user tables from sysobject and columns list
> from syscolumns..
> Now i need to know the primary key column (For delete Statments)..
> How could i detrmine which column is primarykey or composite key'
> thanx
Here is a query that lists the PK columns for all tables in a database.
There is a restriction that the query as written will not cover keys
with more than 10 columns, but this is easy to address.
select o.name,
MAX(CASE ik.keyno WHEN 1 THEN c.name END) +
coalesce(MAX(CASE ik.keyno WHEN 2 THEN ', ' + c.name END), '') +
coalesce(MAX(CASE ik.keyno WHEN 3 THEN ', ' + c.name END), '') +
coalesce(MAX(CASE ik.keyno WHEN 4 THEN ', ' + c.name END), '') +
coalesce(MAX(CASE ik.keyno WHEN 5 THEN ', ' + c.name END), '') +
coalesce(MAX(CASE ik.keyno WHEN 6 THEN ', ' + c.name END), '') +
coalesce(MAX(CASE ik.keyno WHEN 7 THEN ', ' + c.name END), '') +
coalesce(MAX(CASE ik.keyno WHEN 8 THEN ', ' + c.name END), '') +
coalesce(MAX(CASE ik.keyno WHEN 9 THEN ', ' + c.name END), '') +
coalesce(MAX(CASE ik.keyno WHEN 10 THEN ', ' + c.name END), '')
from sysobjects o
join sysindexes i on i.id = o.id
join sysindexkeys ik on i.id = ik.id
and i.indid = ik.indid
join syscolumns c on ik.id = c.id
and ik.colid = c.colid
join sysobjects pk ON i.name = pk.name
AND o.id = pk.parent_obj
group by o.name
order by o.name
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanx so much for this query..
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns976FD9D836B2FYazorman@.127.0.0.1...
> Islamegy (NULL_Islamegy_NULL@.yahoo.com) writes:
> Here is a query that lists the PK columns for all tables in a database.
> There is a restriction that the query as written will not cover keys
> with more than 10 columns, but this is easy to address.
> select o.name,
> MAX(CASE ik.keyno WHEN 1 THEN c.name END) +
> coalesce(MAX(CASE ik.keyno WHEN 2 THEN ', ' + c.name END), '') +
> coalesce(MAX(CASE ik.keyno WHEN 3 THEN ', ' + c.name END), '') +
> coalesce(MAX(CASE ik.keyno WHEN 4 THEN ', ' + c.name END), '') +
> coalesce(MAX(CASE ik.keyno WHEN 5 THEN ', ' + c.name END), '') +
> coalesce(MAX(CASE ik.keyno WHEN 6 THEN ', ' + c.name END), '') +
> coalesce(MAX(CASE ik.keyno WHEN 7 THEN ', ' + c.name END), '') +
> coalesce(MAX(CASE ik.keyno WHEN 8 THEN ', ' + c.name END), '') +
> coalesce(MAX(CASE ik.keyno WHEN 9 THEN ', ' + c.name END), '') +
> coalesce(MAX(CASE ik.keyno WHEN 10 THEN ', ' + c.name END), '')
> from sysobjects o
> join sysindexes i on i.id = o.id
> join sysindexkeys ik on i.id = ik.id
> and i.indid = ik.indid
> join syscolumns c on ik.id = c.id
> and ik.colid = c.colid
> join sysobjects pk ON i.name = pk.name
> AND o.id = pk.parent_obj
> group by o.name
> order by o.name
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx

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, Indexes and Foreign Key - Best Place for them

Hello
I have a database with two data files PRIMARY and INDEXES.
To beef up performance I would like to move as much as I
can out of PRIMARY into Index so I would like to know the
best place to keep my Primary, Foreign and Indexes.
For instance, is it better to keep my Primary Keys in the
PRIMARY filegroup or move it to the INDEXES filegroup ?
Thanks
JWhat makes you think that you would get much if any benefit out of doing
this?
Keeping data in different filegroups doesn't necessarily do anything for
performance unless those filegroups are on differnet spindles. (ie physical
disks). Even then... most databases rarely have a need for different
filegroups. Instead, it's normally just as good for performance to simply
create multiple files within a single filegroup. Generally, I don't use
seperate filegroups unless I want a different backup strategy for difference
data sets.
--
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:0a0e01c3a206$20fe0e10$a501280a@.phx.gbl...
> Hello
> I have a database with two data files PRIMARY and INDEXES.
> To beef up performance I would like to move as much as I
> can out of PRIMARY into Index so I would like to know the
> best place to keep my Primary, Foreign and Indexes.
> For instance, is it better to keep my Primary Keys in the
> PRIMARY filegroup or move it to the INDEXES filegroup ?
> Thanks
> J|||Thankyou for your post.
As I understand it, it is due to the read write heads of
SQL server only one head is allowed at one time per data
file.
Having more than one increases performance, though having
too many slows it.
According to the MCP course it is recommended that you
take your indexes out, and put them in a separate data
file, as then you will be able to ge immediatly from one
file to another.
Thanks
J
>--Original Message--
>What makes you think that you would get much if any
benefit out of doing
>this?
>Keeping data in different filegroups doesn't necessarily
do anything for
>performance unless those filegroups are on differnet
spindles. (ie physical
>disks). Even then... most databases rarely have a need
for different
>filegroups. Instead, it's normally just as good for
performance to simply
>create multiple files within a single filegroup.
Generally, I don't use
>seperate filegroups unless I want a different backup
strategy for difference
>data sets.
>--
>Brian Moran
>Principal Mentor
>Solid Quality Learning
>SQL Server MVP
>http://www.solidqualitylearning.com
>
>"Julie" <anonymous@.discussions.microsoft.com> wrote in
message
>news:0a0e01c3a206$20fe0e10$a501280a@.phx.gbl...
>> Hello
>> I have a database with two data files PRIMARY and
INDEXES.
>> To beef up performance I would like to move as much as I
>> can out of PRIMARY into Index so I would like to know
the
>> best place to keep my Primary, Foreign and Indexes.
>> For instance, is it better to keep my Primary Keys in
the
>> PRIMARY filegroup or move it to the INDEXES filegroup ?
>> Thanks
>> J
>
>.
>|||Julie
Seems to be some confusion here. You say data files, but
it sounds like you are talking about file groups. I agree
with Brian, in that do not create multiple file groups
unless you know you need them.
If you are using multiple physical disks, SQL Server
usually does a good job of striping the tables across the
disks. If you do have one of more large tables that are
very active it can be a benefit to put the non-clustered
indexes in a seperate filegroup. Providing that filegroup
is on different physical drives. I would advise against
doing it as a matter of course, only do it if you can
prove it is an issue.
Hope this helps
John|||Thankyou both for your responses, it looks as if I have my
wires crossed somewhere.
J
>--Original Message--
>Julie
>Seems to be some confusion here. You say data files, but
>it sounds like you are talking about file groups. I agree
>with Brian, in that do not create multiple file groups
>unless you know you need them.
>If you are using multiple physical disks, SQL Server
>usually does a good job of striping the tables across the
>disks. If you do have one of more large tables that are
>very active it can be a benefit to put the non-clustered
>indexes in a seperate filegroup. Providing that filegroup
>is on different physical drives. I would advise against
>doing it as a matter of course, only do it if you can
>prove it is an issue.
>Hope this helps
>John
>.
>

Primary versus unique keys

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

|||

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 role

Hi,
Can someone please explain to me when I Configue the Log Shiiping, there
is an option called "Allow database to assume primary role", what does that
really mean and imply?
Thanks
EdI'm new to log shipping as well, but I read somewhere that option
allows the two databases to reverse roles when you activate the target
as a main database. In other words, you can start log shipping back to
the original server.
No clue as to how to configure it, but I hope it points you in the
right direction.
Stu

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.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

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_intidts

drdaddr
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_intidts

drdaddr
> 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
>

Monday, March 26, 2012

primay key

primay key

primay key

Primarykey Fields

PrimaryKey and Index

PrimaryKey and Index

PrimaryKey and Index

Primary/Foreign/Identity keys & Encryption

Primary, Indexes and Foreign Key - Best Place for them

Primary versus unique keys

primary role

Primary Keys: Best Practice: uniqueidentifier or?

Primary Keys: Best Practice: uniqueidentifier or?

Primary keys, indexes and speed

Primary Keys with Transactional Replication

Primary Keys When importing data

Primary keys lost after DTS

primary keys have gotten out of sequence

primary keys have gotten out of sequence

Preventing Deadlock

Blog Archive

About Me