Hello,
We are in the process of setting up RI (mostly foreign keys, some sp's) on our SQL Server 2000 DBs. One of the questions that has come into play is how bad the performance hit will be once the RI has been put into place. We are using VFP 7 and C# for our front ends, BTW.
I was just wondering if there was anyone out there who has seen any performance difference between an application with RI and one without. If so, how bad of a hit was it, and what type of RI did you use (FKs, SPs, triggers, etc...). If anyone has any advice on how to reduce the performance hit, that would be appreciated as well!
Thanks!
-Justinthe question should not be stated in terms of performance of RI versus performance of not having RI
the question should be performance of RI versus performance of doing the same RI actions in application code
the answer should be, let the database run the RI actions
if you are considering giving up RI, you are saying you are willing to forego having valid data, and i don't believe that's a performance question, but an integrity question
let's consider a (somewhat contrived) analogy -- what if your bank said to you "every time we transfer money from your checking account to your savings account, do you want us to make sure the money actually arrived in the savings account? or would you like to give up that assurance in order to get your card out of the ATM a few milliseconds sooner?"
rudy
http://rudy.ca/|||We recently converted all of our data to SQL Server - currently we have NO RI in place. Some of the RI we have planned to put in place is critical, and will go no matter what the impact - we just want to be prepared. Some of it is somewhat redundant and not as necessary, and the dreaded management wants to weigh the pros and cons of putting this RI into place. This why I am asking the performance question.|||i understand, and sympathize :)
having myself been a clueless pointy-haired boss in the past, i can understand where they're coming from
however, i think you still need to make the point about the purpose of RI, which is to ensure data integrity
now, granted, if it's an existing application which is being modified to use a new database, then you have an interesting situation
presumably the existing app already takes care of RI concerns itself (e.g. not inserting an order for a customer that doesn't exist) so adding RI in these circumstances seems rather pointless
in other words, there's little to be gained (in a bottom-line cost-benefit sense) from adding RI constraints to a database for an app that's already working
when framed in this context, i would suggest setting up the database without RI to start with, taking several volume test performance measures, applying RI, and running the performance tests again
i predict the RI overhead will not be noticeable, or at least no more than a 5% increase
your mileage may vary, since so much depends on the actual tables and relationships involved
rudy|||Thanks for your reply :)
The application was actually written in VFP 6/7 and was designed to use the native FoxPro tables. once some of the DBCs (there are about 30) started nearing the 2 GB limit, we had to 'tweak' (word used by mgmt, it was more like rewrite) the program to be SQL Server compliant. In the midst of all this our DBA ran screaming for the hills, so I recently got the position. The hired guns who did the data conversion neglected to convert the extensive VFP RI on the old DBs to SQL (along with other insignificant things like memos and field descriptions), and now I am stuck in cleanup mode. Since the RI has to be rebuilt from the ground up, mgmt is taking the oppurtunity to sacrifice data integrity for customer-visible speed.
I've been doing some testing on mass updates with RI hooked and then unhooked, and have noticed about a 7% hit for the essential RI. Not a big deal considering we got a 238% speed increase once we converted to SQL Server DBs (the application goes over a WAN). I haven't wriiten the SPs and triggers for the 'nonessential' portion yet, and mgmnt doesn't want me to until they are comfortable it would 'not be a waste of my' + underpaid + 'time.'
Showing posts with label keys. Show all posts
Showing posts with label keys. Show all posts
Wednesday, March 21, 2012
Tuesday, March 20, 2012
Review of DB Design - Normalized, Contraints, Foreign Keys, etc.
First of all, this is my initial thread here on dbforums. I come from the land of Broadband Reports and would like to say, Hello fellow DB enthusiasts. :)
I'm not a novice to relational databases (Access MDBs), but new to implementing a db via SQL SERVER (2000 in this case) and using Access Data Projects.
My partial db schema is as follows:
participants
--DID (pk) char(1)
--LID (fk - schools) char(4)
--studentLast varchar(50)
--studentFirst varchar(25)
Sample Data would be
010191M001 | 5671 | SPARKS | JONATHAN
030495F283 | 5671 | DYLAN | CYNTHIA
=====================================
enrollhist (insert/update trigger for enrollactive)
--EID (pk - autonumber) bigint(8)
--EMID (fk - enrollmode) int(4)
--DID (fk - participants) char(10)
--LID (fk - schools) char(4)
--enrollactive bit(1)
Sample Data would be
38173 | 4 | 030495F283 | 9003 | 0
38266 | 3 | 010191M001 | 5671 | 0
39022 | 6 | 030495F283 | 9003 | 0
39036 | 5 | 030495F283 | 9003 | 0
39044 | 4 | 030495F283 | 5671 | 1
39117 | 4 | 010191M001 | 5671 | 1
=====================================
enrollmode
--EMID (pk) int(4)
--mode varchar(25)
Sample Data would be
1 | RECEIVED
2 | WAITING
3 | PENDING
4 | ENROLLED
5 | DROPPED
6 | TRANSFERRED
10 | ORPHANED
11 | DENIED
=====================================
schools
--LID (pk) varchar(4)
--CTID (fk - caltracks) char(1)
--AID (fk - agencies) char(1)
--SDID (fk - schooldist) char(1)
--COID (fk - countydist) char(1)
--sitename varchar(25)
--sitetitle varchar(75)
Sample Data would be
5671 | 3 | 2 | 1 | 4 | ASCOT | ASCOT AVENUE
9003 | 2 | 1 | 4 | 1 | ROWAN | ROWAN AVENUE
2865 | 1 | 3 | 2 | 3 | BRIGHT | BIRDELEE BRIGHT
=====================================
caltracks
--CTID (pk) char(1)
--legend char(4)
--trktitle varchar(15)
--trkcnt int(4)
Sample Data would be
1 | 9030 | 90/30 | 4
2 | CON6 | CONCEPT-6 | 3
3 | SNGL | SINGLE TRACK | 1
=====================================
agencies
--AID (pk) char(1)
--legend varchar(4)
--agencytitle varvhar(50)
Sample Data would be
1 | CRYS | CRYSTAL STAIRS
2 | MAOF | MEXICAN AMERICAN FOUNDATION
3 | PATH | PATHWAYS
4 | CCRC | CHILD CARE RESOURCE CENTER
5 | CHSC | CHILDREN'S HOME SOCIETY OF CALIFORNIA
==========================================
THE REMAINING "FKs" FROM SCHOOL ARE SIMILAR, as is other tables and their relationships. The design of the foreign keys were made using sql and the keyword "REFERENCES" and "FOREIGN KEY."
My questions are: :confused:
(1) Is the use of FK as a Constraint any different than using an INDEX and how?
(2) Should I Alter the Tables to include CASCADING Up/Down?
(3) Are the use of CHARs Ok for the Keys?
(4) Have I over/under-normalized any of the relationships?not to avoid your good questions, but i'll grab at the low hanging fruit...
the PK of 'participants' won't allow you to have many participants.
256 tops, it would appear.
ditto all your char(1) PKs|||You PKs are each 1 character? Doesn't seem too scalable to me...|||(oddity) Sorry about the TYPO, the PK for table.participants is CHAR(10) as shown as a FK in table.enrollhist
(blindman) The instances when I use CHAR(1) is truly not scalable, but the chances of the keys needing 2+ chars are slim to none. But, in the optimum chance of meeting that slim probability, I would have to agree with you.
I also have an additional question regarding the schema I have for the enrollhist table. Does anyone see any flaws in its design.
The implementation I want is to have the enrollmode and effective date (forgot the column for effective date in the schema listed [effdate datetime]) stored whenever there is a change in the status of the participant. The previous database had each of the modes as a column of the participants table. This is a no-no in my book!
thanks so far...
-bigEz|||Its not a no-no if each state can be achieved at most one time, but since this is probably not the case then your implementation is much more robust.
I'm not a novice to relational databases (Access MDBs), but new to implementing a db via SQL SERVER (2000 in this case) and using Access Data Projects.
My partial db schema is as follows:
participants
--DID (pk) char(1)
--LID (fk - schools) char(4)
--studentLast varchar(50)
--studentFirst varchar(25)
Sample Data would be
010191M001 | 5671 | SPARKS | JONATHAN
030495F283 | 5671 | DYLAN | CYNTHIA
=====================================
enrollhist (insert/update trigger for enrollactive)
--EID (pk - autonumber) bigint(8)
--EMID (fk - enrollmode) int(4)
--DID (fk - participants) char(10)
--LID (fk - schools) char(4)
--enrollactive bit(1)
Sample Data would be
38173 | 4 | 030495F283 | 9003 | 0
38266 | 3 | 010191M001 | 5671 | 0
39022 | 6 | 030495F283 | 9003 | 0
39036 | 5 | 030495F283 | 9003 | 0
39044 | 4 | 030495F283 | 5671 | 1
39117 | 4 | 010191M001 | 5671 | 1
=====================================
enrollmode
--EMID (pk) int(4)
--mode varchar(25)
Sample Data would be
1 | RECEIVED
2 | WAITING
3 | PENDING
4 | ENROLLED
5 | DROPPED
6 | TRANSFERRED
10 | ORPHANED
11 | DENIED
=====================================
schools
--LID (pk) varchar(4)
--CTID (fk - caltracks) char(1)
--AID (fk - agencies) char(1)
--SDID (fk - schooldist) char(1)
--COID (fk - countydist) char(1)
--sitename varchar(25)
--sitetitle varchar(75)
Sample Data would be
5671 | 3 | 2 | 1 | 4 | ASCOT | ASCOT AVENUE
9003 | 2 | 1 | 4 | 1 | ROWAN | ROWAN AVENUE
2865 | 1 | 3 | 2 | 3 | BRIGHT | BIRDELEE BRIGHT
=====================================
caltracks
--CTID (pk) char(1)
--legend char(4)
--trktitle varchar(15)
--trkcnt int(4)
Sample Data would be
1 | 9030 | 90/30 | 4
2 | CON6 | CONCEPT-6 | 3
3 | SNGL | SINGLE TRACK | 1
=====================================
agencies
--AID (pk) char(1)
--legend varchar(4)
--agencytitle varvhar(50)
Sample Data would be
1 | CRYS | CRYSTAL STAIRS
2 | MAOF | MEXICAN AMERICAN FOUNDATION
3 | PATH | PATHWAYS
4 | CCRC | CHILD CARE RESOURCE CENTER
5 | CHSC | CHILDREN'S HOME SOCIETY OF CALIFORNIA
==========================================
THE REMAINING "FKs" FROM SCHOOL ARE SIMILAR, as is other tables and their relationships. The design of the foreign keys were made using sql and the keyword "REFERENCES" and "FOREIGN KEY."
My questions are: :confused:
(1) Is the use of FK as a Constraint any different than using an INDEX and how?
(2) Should I Alter the Tables to include CASCADING Up/Down?
(3) Are the use of CHARs Ok for the Keys?
(4) Have I over/under-normalized any of the relationships?not to avoid your good questions, but i'll grab at the low hanging fruit...
the PK of 'participants' won't allow you to have many participants.
256 tops, it would appear.
ditto all your char(1) PKs|||You PKs are each 1 character? Doesn't seem too scalable to me...|||(oddity) Sorry about the TYPO, the PK for table.participants is CHAR(10) as shown as a FK in table.enrollhist
(blindman) The instances when I use CHAR(1) is truly not scalable, but the chances of the keys needing 2+ chars are slim to none. But, in the optimum chance of meeting that slim probability, I would have to agree with you.
I also have an additional question regarding the schema I have for the enrollhist table. Does anyone see any flaws in its design.
The implementation I want is to have the enrollmode and effective date (forgot the column for effective date in the schema listed [effdate datetime]) stored whenever there is a change in the status of the participant. The previous database had each of the modes as a column of the participants table. This is a no-no in my book!
thanks so far...
-bigEz|||Its not a no-no if each state can be achieved at most one time, but since this is probably not the case then your implementation is much more robust.
Monday, March 12, 2012
Reverse enginnering in VISIO 2002 (problem with sp_primarykey)
I have problem if I want to do reverse engineering in VISIO 2002.
The database uses primary key created with sp_primarykey an foreign keys with sp_foreingnkey
In VISIO - Database - Options - Drivers I had set the DDL script generationas follows:
Preffered version - 6.0
Generate primary key using - sp_primarykey
Generate foregin key using - sp_foreignkey
The process had passed without errors, but the relations didn't showed.
I tried option "Show related tables" but nothing didn't happend.
thanks.That could possibily be because of the loss of dependensies. The happens when you drop and recreate few objects in the database, which does ot re-establish the dependensies.
Try recompiling the objects or establishing the foreign keys again.
Thanks.
The database uses primary key created with sp_primarykey an foreign keys with sp_foreingnkey
In VISIO - Database - Options - Drivers I had set the DDL script generationas follows:
Preffered version - 6.0
Generate primary key using - sp_primarykey
Generate foregin key using - sp_foreignkey
The process had passed without errors, but the relations didn't showed.
I tried option "Show related tables" but nothing didn't happend.
thanks.That could possibily be because of the loss of dependensies. The happens when you drop and recreate few objects in the database, which does ot re-establish the dependensies.
Try recompiling the objects or establishing the foreign keys again.
Thanks.
reverse engineering MS SQL server 2k5
Hi Alan
I can certainly import a database and foreign keys in SQL 2005, for instance
if I choose the northwind database and go through the wizard making sure that
FKs are imported and I choose the option to add the tables later to a
diagram. I can then sekect the customers tables from the tables and views box
and drag it onto the diagram. Then right clicking show related tables will
bring in the orders and customercustomerdemo tables with their foreign keys.
John
"Alain R." wrote:
> Hi,
> I got a DB with more than 100 tables.
> i tried to get it structure by Visio and its reverse engineering method
> but Visio does not draw "links" among tables representing PK and FK
> connections.
> So do you know any other software which could do the job ?
> maybe a simple freeware is enough, it is just to have an overview on
> this database and its objects.
> thanks a lot,
> A.
>
I had that issue also, I installed SP3 for Visio 2003 and it started working
after that
. Thanks!
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"John Bell" wrote:
[vbcol=seagreen]
> Hi Alan
> I can certainly import a database and foreign keys in SQL 2005, for instance
> if I choose the northwind database and go through the wizard making sure that
> FKs are imported and I choose the option to add the tables later to a
> diagram. I can then sekect the customers tables from the tables and views box
> and drag it onto the diagram. Then right clicking show related tables will
> bring in the orders and customercustomerdemo tables with their foreign keys.
> John
> "Alain R." wrote:
|||Hi
I was using Microsoft Office Visio for Enterprise Architects (11.7218.8132)
SP2
John
"Mohit K. Gupta" wrote:
[vbcol=seagreen]
> I had that issue also, I installed SP3 for Visio 2003 and it started working
> after that. Thanks!
> --
> Mohit K. Gupta
> B.Sc. CS, Minor Japanese
> MCTS: SQL Server 2005
>
> "John Bell" wrote:
I can certainly import a database and foreign keys in SQL 2005, for instance
if I choose the northwind database and go through the wizard making sure that
FKs are imported and I choose the option to add the tables later to a
diagram. I can then sekect the customers tables from the tables and views box
and drag it onto the diagram. Then right clicking show related tables will
bring in the orders and customercustomerdemo tables with their foreign keys.
John
"Alain R." wrote:
> Hi,
> I got a DB with more than 100 tables.
> i tried to get it structure by Visio and its reverse engineering method
> but Visio does not draw "links" among tables representing PK and FK
> connections.
> So do you know any other software which could do the job ?
> maybe a simple freeware is enough, it is just to have an overview on
> this database and its objects.
> thanks a lot,
> A.
>
I had that issue also, I installed SP3 for Visio 2003 and it started working
after that
. Thanks!Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"John Bell" wrote:
[vbcol=seagreen]
> Hi Alan
> I can certainly import a database and foreign keys in SQL 2005, for instance
> if I choose the northwind database and go through the wizard making sure that
> FKs are imported and I choose the option to add the tables later to a
> diagram. I can then sekect the customers tables from the tables and views box
> and drag it onto the diagram. Then right clicking show related tables will
> bring in the orders and customercustomerdemo tables with their foreign keys.
> John
> "Alain R." wrote:
|||Hi
I was using Microsoft Office Visio for Enterprise Architects (11.7218.8132)
SP2
John
"Mohit K. Gupta" wrote:
[vbcol=seagreen]
> I had that issue also, I installed SP3 for Visio 2003 and it started working
> after that
. Thanks!> --
> Mohit K. Gupta
> B.Sc. CS, Minor Japanese
> MCTS: SQL Server 2005
>
> "John Bell" wrote:
Reverse Engineering - FK
Hi there --
Can some one clue me in about Foriegn Keys and how I can determine what they
are.
I'm trying to reverse engineer a DB and I see some fields that have
interesting values. And these coumns do not appear to be in the table when I
select * from
The values I see are ...
=(1ABF96C75BA790
=G312296BA577411
$0000960D431B939
Do any these look like they are foriegn keys? And if so, how can I confirm?
Thanks
Mark,
Use sp_help tablename - where tablename is the table in question.
HTH
Jerry
"X-Mark" <X-Mark@.discussions.microsoft.com> wrote in message
news:2EFD8862-0E4B-42AB-882F-ECA69DC484E5@.microsoft.com...
> Hi there --
> Can some one clue me in about Foriegn Keys and how I can determine what
> they
> are.
> I'm trying to reverse engineer a DB and I see some fields that have
> interesting values. And these coumns do not appear to be in the table
> when I
> select * from
> The values I see are ...
> =(1ABF96C75BA790
> =G312296BA577411
> $0000960D431B939
> Do any these look like they are foriegn keys? And if so, how can I
> confirm?
> Thanks
Can some one clue me in about Foriegn Keys and how I can determine what they
are.
I'm trying to reverse engineer a DB and I see some fields that have
interesting values. And these coumns do not appear to be in the table when I
select * from
The values I see are ...
=(1ABF96C75BA790
=G312296BA577411
$0000960D431B939
Do any these look like they are foriegn keys? And if so, how can I confirm?
Thanks
Mark,
Use sp_help tablename - where tablename is the table in question.
HTH
Jerry
"X-Mark" <X-Mark@.discussions.microsoft.com> wrote in message
news:2EFD8862-0E4B-42AB-882F-ECA69DC484E5@.microsoft.com...
> Hi there --
> Can some one clue me in about Foriegn Keys and how I can determine what
> they
> are.
> I'm trying to reverse engineer a DB and I see some fields that have
> interesting values. And these coumns do not appear to be in the table
> when I
> select * from
> The values I see are ...
> =(1ABF96C75BA790
> =G312296BA577411
> $0000960D431B939
> Do any these look like they are foriegn keys? And if so, how can I
> confirm?
> Thanks
Reverse Engineering - FK
Hi there --
Can some one clue me in about Foriegn Keys and how I can determine what they
are.
I'm trying to reverse engineer a DB and I see some fields that have
interesting values. And these coumns do not appear to be in the table when
I
select * from
The values I see are ...
=(1ABF96C75BA790
=G312296BA577411
$0000960D431B939
Do any these look like they are foriegn keys? And if so, how can I confirm?
ThanksMark,
Use sp_help tablename - where tablename is the table in question.
HTH
Jerry
"X-Mark" <X-Mark@.discussions.microsoft.com> wrote in message
news:2EFD8862-0E4B-42AB-882F-ECA69DC484E5@.microsoft.com...
> Hi there --
> Can some one clue me in about Foriegn Keys and how I can determine what
> they
> are.
> I'm trying to reverse engineer a DB and I see some fields that have
> interesting values. And these coumns do not appear to be in the table
> when I
> select * from
> The values I see are ...
> =(1ABF96C75BA790
> =G312296BA577411
> $0000960D431B939
> Do any these look like they are foriegn keys? And if so, how can I
> confirm?
> Thanks
Can some one clue me in about Foriegn Keys and how I can determine what they
are.
I'm trying to reverse engineer a DB and I see some fields that have
interesting values. And these coumns do not appear to be in the table when
I
select * from
The values I see are ...
=(1ABF96C75BA790
=G312296BA577411
$0000960D431B939
Do any these look like they are foriegn keys? And if so, how can I confirm?
ThanksMark,
Use sp_help tablename - where tablename is the table in question.
HTH
Jerry
"X-Mark" <X-Mark@.discussions.microsoft.com> wrote in message
news:2EFD8862-0E4B-42AB-882F-ECA69DC484E5@.microsoft.com...
> Hi there --
> Can some one clue me in about Foriegn Keys and how I can determine what
> they
> are.
> I'm trying to reverse engineer a DB and I see some fields that have
> interesting values. And these coumns do not appear to be in the table
> when I
> select * from
> The values I see are ...
> =(1ABF96C75BA790
> =G312296BA577411
> $0000960D431B939
> Do any these look like they are foriegn keys? And if so, how can I
> confirm?
> Thanks
Reverse Engineering - FK
Hi there --
Can some one clue me in about Foriegn Keys and how I can determine what they
are.
I'm trying to reverse engineer a DB and I see some fields that have
interesting values. And these coumns do not appear to be in the table when I
select * from
The values I see are ...
=(1ABF96C75BA790
=G312296BA577411
$0000960D431B939
Do any these look like they are foriegn keys? And if so, how can I confirm?
ThanksMark,
Use sp_help tablename - where tablename is the table in question.
HTH
Jerry
"X-Mark" <X-Mark@.discussions.microsoft.com> wrote in message
news:2EFD8862-0E4B-42AB-882F-ECA69DC484E5@.microsoft.com...
> Hi there --
> Can some one clue me in about Foriegn Keys and how I can determine what
> they
> are.
> I'm trying to reverse engineer a DB and I see some fields that have
> interesting values. And these coumns do not appear to be in the table
> when I
> select * from
> The values I see are ...
> =(1ABF96C75BA790
> =G312296BA577411
> $0000960D431B939
> Do any these look like they are foriegn keys? And if so, how can I
> confirm?
> Thanks
Can some one clue me in about Foriegn Keys and how I can determine what they
are.
I'm trying to reverse engineer a DB and I see some fields that have
interesting values. And these coumns do not appear to be in the table when I
select * from
The values I see are ...
=(1ABF96C75BA790
=G312296BA577411
$0000960D431B939
Do any these look like they are foriegn keys? And if so, how can I confirm?
ThanksMark,
Use sp_help tablename - where tablename is the table in question.
HTH
Jerry
"X-Mark" <X-Mark@.discussions.microsoft.com> wrote in message
news:2EFD8862-0E4B-42AB-882F-ECA69DC484E5@.microsoft.com...
> Hi there --
> Can some one clue me in about Foriegn Keys and how I can determine what
> they
> are.
> I'm trying to reverse engineer a DB and I see some fields that have
> interesting values. And these coumns do not appear to be in the table
> when I
> select * from
> The values I see are ...
> =(1ABF96C75BA790
> =G312296BA577411
> $0000960D431B939
> Do any these look like they are foriegn keys? And if so, how can I
> confirm?
> Thanks
Friday, March 9, 2012
re-use rowguid, replication fails?
My ROWGUID columns are also my primary keys in many cases. I have
some code that deletes and then reinserts a row that has changes
instead of doing an UPDATE. As such, I re-use the same guid in the
ROWGUID column.
I have found that when I do this, the changes to the row do not make
it back up to the master server during a synchronize, as if the
replication does not recognize that the delete and re-insert has been
done on the row. If I do an update on the row, the change is
recognized and sent back to the master server.
Is this an expected behavior? Is it a no-no to delete a row with
ROWGUID x, then re-insert a new row with the same ROWGUID x?
I can probably whip up a step-by-step example that demonstrates this
behavior if desired.
thanks,
matthew tagliaferri
I take it you are using merge replication. Merge replication uses the
rowguid column to track which row has changed. Its probably not a good idea
to modify this row.
Can you check your conflict tables using the conflict viewer to see if any
conflicts are being logged. If not, please post your repo.
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"matt tagliaferri" <mtagliaf@.cleindians.com> wrote in message
news:2cc74fa4.0411181102.5406ca87@.posting.google.c om...
> My ROWGUID columns are also my primary keys in many cases. I have
> some code that deletes and then reinserts a row that has changes
> instead of doing an UPDATE. As such, I re-use the same guid in the
> ROWGUID column.
> I have found that when I do this, the changes to the row do not make
> it back up to the master server during a synchronize, as if the
> replication does not recognize that the delete and re-insert has been
> done on the row. If I do an update on the row, the change is
> recognized and sent back to the master server.
> Is this an expected behavior? Is it a no-no to delete a row with
> ROWGUID x, then re-insert a new row with the same ROWGUID x?
> I can probably whip up a step-by-step example that demonstrates this
> behavior if desired.
> thanks,
> matthew tagliaferri
|||No conflicts exist. I can look at the table in question on the "master"
and "child" server, and the data in the child server is newer than the
data in the master server.
I spent a bit of time setting up some replication logging, this was
useful only in the fact that it didn't show any replication activity on
the table in question.
My collegue and I are setting up a reproducible example now, I will post
here when it is complete.
thanks for the reply,
matt tagliaferri
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
some code that deletes and then reinserts a row that has changes
instead of doing an UPDATE. As such, I re-use the same guid in the
ROWGUID column.
I have found that when I do this, the changes to the row do not make
it back up to the master server during a synchronize, as if the
replication does not recognize that the delete and re-insert has been
done on the row. If I do an update on the row, the change is
recognized and sent back to the master server.
Is this an expected behavior? Is it a no-no to delete a row with
ROWGUID x, then re-insert a new row with the same ROWGUID x?
I can probably whip up a step-by-step example that demonstrates this
behavior if desired.
thanks,
matthew tagliaferri
I take it you are using merge replication. Merge replication uses the
rowguid column to track which row has changed. Its probably not a good idea
to modify this row.
Can you check your conflict tables using the conflict viewer to see if any
conflicts are being logged. If not, please post your repo.
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"matt tagliaferri" <mtagliaf@.cleindians.com> wrote in message
news:2cc74fa4.0411181102.5406ca87@.posting.google.c om...
> My ROWGUID columns are also my primary keys in many cases. I have
> some code that deletes and then reinserts a row that has changes
> instead of doing an UPDATE. As such, I re-use the same guid in the
> ROWGUID column.
> I have found that when I do this, the changes to the row do not make
> it back up to the master server during a synchronize, as if the
> replication does not recognize that the delete and re-insert has been
> done on the row. If I do an update on the row, the change is
> recognized and sent back to the master server.
> Is this an expected behavior? Is it a no-no to delete a row with
> ROWGUID x, then re-insert a new row with the same ROWGUID x?
> I can probably whip up a step-by-step example that demonstrates this
> behavior if desired.
> thanks,
> matthew tagliaferri
|||No conflicts exist. I can look at the table in question on the "master"
and "child" server, and the data in the child server is newer than the
data in the master server.
I spent a bit of time setting up some replication logging, this was
useful only in the fact that it didn't show any replication activity on
the table in question.
My collegue and I are setting up a reproducible example now, I will post
here when it is complete.
thanks for the reply,
matt tagliaferri
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
Saturday, February 25, 2012
Returning Primary Keys and indexes ?
Hello,
Does anyone know how to return the names of the clustered
and non clustered indexes from a database table ?
Thanks
JJulie
SELECT INFORMATION_SCHEMA.TABLE_CONSTRAINTS.TABLE_SCHEMA as TableOwner,
INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_NAME,
INFORMATION_SCHEMA.TABLE_CONSTRAINTS.TABLE_NAME,
INFORMATION_SCHEMA.KEY_COLUMN_USAGE.COLUMN_NAME,
case
objectproperty(object_id(INFORMATION_SCH
EMA.TABLE_CONSTRAINTS.CONSTRAINT_NAM
E)
,'CnstIsClustKey')
when 1 then 'clustered'
when 0 then 'noneclustered'
end as IsClustered
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS INNER JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE
ON INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_NAME =
INFORMATION_SCHEMA.KEY_COLUMN_USAGE.CONSTRAINT_NAME
WHERE INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_TYPE LIKE 'PRIMARY
KEY'
ORDER BY INFORMATION_SCHEMA.TABLE_CONSTRAINTS.TABLE_NAME
"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:059e01c3de7b$17a85f80$a401280a@.phx.gbl...
I think I owe you a couple of drinks ;)
J
as TableOwner,
(INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_NAM
INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_TYPE
LIKE 'PRIMARY
message
Well , where will we meet?
"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:069801c3de95$bb2874f0$a501280a@.phx.gbl...[QUOTE]
> Thanks (Again) Uri,
> I think I owe you a couple of drinks ;)
> J
>
> as TableOwner,
> (INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_NAM
> INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_TYPE
> LIKE 'PRIMARY
> message
> clustered|||I am not really sure but the provided select only returns
constraint related indexes.
Indexes which are only present for performance are not
returned.
ben brugman
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:u6T2tFo3DHA.2544@.TK2MSFTNGP10.phx.gbl...
objectproperty(object_id(INFORMATION_SCH
EMA.TABLE_CONSTRAINTS.CONSTRAINT_NAM
declare @.sql varchar(8000)
set @.sql = ''
select @.sql = @.sql + case when exists (
select * from information_schema.constraint_table_usage
where constraint_name = sysindexes.name)
then tablename+'.'+ name
else tablename + '.' + name end + char(10)
from (select top 100 percent object_name(id)tablename, name
from sysindexes
where objectproperty(id, 'IsMSShipped') = 0
and indid between 1 and 254
and (status & 64) = 0
order by tablename, indid desc) sysindexes
print @.sql
"ben brugman" <ben@.niethier.nl> wrote in message
news:ej6LMXN4DHA.2528@.TK2MSFTNGP09.phx.gbl...
objectproperty(object_id(INFORMATION_SCH
EMA.TABLE_CONSTRAINTS.CONSTRAINT_NAM
Does anyone know how to return the names of the clustered
and non clustered indexes from a database table ?
Thanks
JJulie
SELECT INFORMATION_SCHEMA.TABLE_CONSTRAINTS.TABLE_SCHEMA as TableOwner,
INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_NAME,
INFORMATION_SCHEMA.TABLE_CONSTRAINTS.TABLE_NAME,
INFORMATION_SCHEMA.KEY_COLUMN_USAGE.COLUMN_NAME,
case
objectproperty(object_id(INFORMATION_SCH
EMA.TABLE_CONSTRAINTS.CONSTRAINT_NAM
E)
,'CnstIsClustKey')
when 1 then 'clustered'
when 0 then 'noneclustered'
end as IsClustered
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS INNER JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE
ON INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_NAME =
INFORMATION_SCHEMA.KEY_COLUMN_USAGE.CONSTRAINT_NAME
WHERE INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_TYPE LIKE 'PRIMARY
KEY'
ORDER BY INFORMATION_SCHEMA.TABLE_CONSTRAINTS.TABLE_NAME
"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:059e01c3de7b$17a85f80$a401280a@.phx.gbl...
quote:|||Thanks (Again) Uri,
> Hello,
> Does anyone know how to return the names of the clustered
> and non clustered indexes from a database table ?
> Thanks
> J
I think I owe you a couple of drinks ;)
J
quote:
>--Original Message--
>Julie
>SELECT INFORMATION_SCHEMA.TABLE_CONSTRAINTS.TABLE_SCHEMA
as TableOwner,
quote:
> INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_NAME,
> INFORMATION_SCHEMA.TABLE_CONSTRAINTS.TABLE_NAME,
> INFORMATION_SCHEMA.KEY_COLUMN_USAGE.COLUMN_NAME,
> case
>objectproperty(object_id
(INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_NAM
quote:
>E)
>,'CnstIsClustKey')
> when 1 then 'clustered'
> when 0 then 'noneclustered'
> end as IsClustered
>FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS INNER JOIN
>INFORMATION_SCHEMA.KEY_COLUMN_USAGE
> ON INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_NAME =
>INFORMATION_SCHEMA.KEY_COLUMN_USAGE.CONSTRAINT_NAME
>WHERE
INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_TYPE
LIKE 'PRIMARY
quote:
>KEY'
>ORDER BY INFORMATION_SCHEMA.TABLE_CONSTRAINTS.TABLE_NAME
>"Julie" <anonymous@.discussions.microsoft.com> wrote in
message
quote:|||Hi, Julie
>news:059e01c3de7b$17a85f80$a401280a@.phx.gbl...
clustered[QUOTE]
>
>.
>
Well , where will we meet?
"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:069801c3de95$bb2874f0$a501280a@.phx.gbl...[QUOTE]
> Thanks (Again) Uri,
> I think I owe you a couple of drinks ;)
> J
>
> as TableOwner,
> (INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_NAM
> INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_TYPE
> LIKE 'PRIMARY
> message
> clustered|||I am not really sure but the provided select only returns
constraint related indexes.
Indexes which are only present for performance are not
returned.
ben brugman
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:u6T2tFo3DHA.2544@.TK2MSFTNGP10.phx.gbl...
quote:
> Julie
> SELECT INFORMATION_SCHEMA.TABLE_CONSTRAINTS.TABLE_SCHEMA as TableOwner,
> INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_NAME,
> INFORMATION_SCHEMA.TABLE_CONSTRAINTS.TABLE_NAME,
> INFORMATION_SCHEMA.KEY_COLUMN_USAGE.COLUMN_NAME,
> case
>
objectproperty(object_id(INFORMATION_SCH
EMA.TABLE_CONSTRAINTS.CONSTRAINT_NAM
quote:|||ben
> E)
> ,'CnstIsClustKey')
> when 1 then 'clustered'
> when 0 then 'noneclustered'
> end as IsClustered
> FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS INNER JOIN
> INFORMATION_SCHEMA.KEY_COLUMN_USAGE
> ON INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_NAME =
> INFORMATION_SCHEMA.KEY_COLUMN_USAGE.CONSTRAINT_NAME
> WHERE INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_TYPE LIKE 'PRIMARY
> KEY'
> ORDER BY INFORMATION_SCHEMA.TABLE_CONSTRAINTS.TABLE_NAME
> "Julie" <anonymous@.discussions.microsoft.com> wrote in message
> news:059e01c3de7b$17a85f80$a401280a@.phx.gbl...
>
declare @.sql varchar(8000)
set @.sql = ''
select @.sql = @.sql + case when exists (
select * from information_schema.constraint_table_usage
where constraint_name = sysindexes.name)
then tablename+'.'+ name
else tablename + '.' + name end + char(10)
from (select top 100 percent object_name(id)tablename, name
from sysindexes
where objectproperty(id, 'IsMSShipped') = 0
and indid between 1 and 254
and (status & 64) = 0
order by tablename, indid desc) sysindexes
print @.sql
"ben brugman" <ben@.niethier.nl> wrote in message
news:ej6LMXN4DHA.2528@.TK2MSFTNGP09.phx.gbl...
quote:
> I am not really sure but the provided select only returns
> constraint related indexes.
> Indexes which are only present for performance are not
> returned.
> ben brugman
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:u6T2tFo3DHA.2544@.TK2MSFTNGP10.phx.gbl...
>
objectproperty(object_id(INFORMATION_SCH
EMA.TABLE_CONSTRAINTS.CONSTRAINT_NAM
quote:
>
Returning Primary Keys and indexes ?
Hello,
Does anyone know how to return the names of the clustered
and non clustered indexes from a database table ?
Thanks
JJulie
SELECT INFORMATION_SCHEMA.TABLE_CONSTRAINTS.TABLE_SCHEMA as TableOwner,
INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_NAME,
INFORMATION_SCHEMA.TABLE_CONSTRAINTS.TABLE_NAME,
INFORMATION_SCHEMA.KEY_COLUMN_USAGE.COLUMN_NAME,
case
objectproperty(object_id(INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_NAM
E)
,'CnstIsClustKey')
when 1 then 'clustered'
when 0 then 'noneclustered'
end as IsClustered
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS INNER JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE
ON INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_NAME =INFORMATION_SCHEMA.KEY_COLUMN_USAGE.CONSTRAINT_NAME
WHERE INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_TYPE LIKE 'PRIMARY
KEY'
ORDER BY INFORMATION_SCHEMA.TABLE_CONSTRAINTS.TABLE_NAME
"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:059e01c3de7b$17a85f80$a401280a@.phx.gbl...
> Hello,
> Does anyone know how to return the names of the clustered
> and non clustered indexes from a database table ?
> Thanks
> J|||Thanks (Again) Uri,
I think I owe you a couple of drinks ;)
J
>--Original Message--
>Julie
>SELECT INFORMATION_SCHEMA.TABLE_CONSTRAINTS.TABLE_SCHEMA
as TableOwner,
> INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_NAME,
> INFORMATION_SCHEMA.TABLE_CONSTRAINTS.TABLE_NAME,
> INFORMATION_SCHEMA.KEY_COLUMN_USAGE.COLUMN_NAME,
> case
>objectproperty(object_id
(INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_NAM
>E)
>,'CnstIsClustKey')
> when 1 then 'clustered'
> when 0 then 'noneclustered'
> end as IsClustered
>FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS INNER JOIN
>INFORMATION_SCHEMA.KEY_COLUMN_USAGE
> ON INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_NAME =>INFORMATION_SCHEMA.KEY_COLUMN_USAGE.CONSTRAINT_NAME
>WHERE
INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_TYPE
LIKE 'PRIMARY
>KEY'
>ORDER BY INFORMATION_SCHEMA.TABLE_CONSTRAINTS.TABLE_NAME
>"Julie" <anonymous@.discussions.microsoft.com> wrote in
message
>news:059e01c3de7b$17a85f80$a401280a@.phx.gbl...
>> Hello,
>> Does anyone know how to return the names of the
clustered
>> and non clustered indexes from a database table ?
>> Thanks
>> J
>
>.
>|||Hi, Julie
Well , where will we meet?
"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:069801c3de95$bb2874f0$a501280a@.phx.gbl...
> Thanks (Again) Uri,
> I think I owe you a couple of drinks ;)
> J
> >--Original Message--
> >Julie
> >SELECT INFORMATION_SCHEMA.TABLE_CONSTRAINTS.TABLE_SCHEMA
> as TableOwner,
> > INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_NAME,
> > INFORMATION_SCHEMA.TABLE_CONSTRAINTS.TABLE_NAME,
> > INFORMATION_SCHEMA.KEY_COLUMN_USAGE.COLUMN_NAME,
> > case
> >objectproperty(object_id
> (INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_NAM
> >E)
> >,'CnstIsClustKey')
> > when 1 then 'clustered'
> > when 0 then 'noneclustered'
> > end as IsClustered
> >FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS INNER JOIN
> >INFORMATION_SCHEMA.KEY_COLUMN_USAGE
> > ON INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_NAME => >INFORMATION_SCHEMA.KEY_COLUMN_USAGE.CONSTRAINT_NAME
> >WHERE
> INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_TYPE
> LIKE 'PRIMARY
> >KEY'
> >ORDER BY INFORMATION_SCHEMA.TABLE_CONSTRAINTS.TABLE_NAME
> >"Julie" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:059e01c3de7b$17a85f80$a401280a@.phx.gbl...
> >> Hello,
> >>
> >> Does anyone know how to return the names of the
> clustered
> >> and non clustered indexes from a database table ?
> >>
> >> Thanks
> >> J
> >
> >
> >.
> >|||I am not really sure but the provided select only returns
constraint related indexes.
Indexes which are only present for performance are not
returned.
ben brugman
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:u6T2tFo3DHA.2544@.TK2MSFTNGP10.phx.gbl...
> Julie
> SELECT INFORMATION_SCHEMA.TABLE_CONSTRAINTS.TABLE_SCHEMA as TableOwner,
> INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_NAME,
> INFORMATION_SCHEMA.TABLE_CONSTRAINTS.TABLE_NAME,
> INFORMATION_SCHEMA.KEY_COLUMN_USAGE.COLUMN_NAME,
> case
>
objectproperty(object_id(INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_NAM
> E)
> ,'CnstIsClustKey')
> when 1 then 'clustered'
> when 0 then 'noneclustered'
> end as IsClustered
> FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS INNER JOIN
> INFORMATION_SCHEMA.KEY_COLUMN_USAGE
> ON INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_NAME => INFORMATION_SCHEMA.KEY_COLUMN_USAGE.CONSTRAINT_NAME
> WHERE INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_TYPE LIKE 'PRIMARY
> KEY'
> ORDER BY INFORMATION_SCHEMA.TABLE_CONSTRAINTS.TABLE_NAME
> "Julie" <anonymous@.discussions.microsoft.com> wrote in message
> news:059e01c3de7b$17a85f80$a401280a@.phx.gbl...
> > Hello,
> >
> > Does anyone know how to return the names of the clustered
> > and non clustered indexes from a database table ?
> >
> > Thanks
> > J
>|||ben
declare @.sql varchar(8000)
set @.sql = ''
select @.sql = @.sql + case when exists (
select * from information_schema.constraint_table_usage
where constraint_name = sysindexes.name)
then tablename+'.'+ name
else tablename + '.' + name end + char(10)
from (select top 100 percent object_name(id)tablename, name
from sysindexes
where objectproperty(id, 'IsMSShipped') = 0
and indid between 1 and 254
and (status & 64) = 0
order by tablename, indid desc) sysindexes
print @.sql
"ben brugman" <ben@.niethier.nl> wrote in message
news:ej6LMXN4DHA.2528@.TK2MSFTNGP09.phx.gbl...
> I am not really sure but the provided select only returns
> constraint related indexes.
> Indexes which are only present for performance are not
> returned.
> ben brugman
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:u6T2tFo3DHA.2544@.TK2MSFTNGP10.phx.gbl...
> > Julie
> > SELECT INFORMATION_SCHEMA.TABLE_CONSTRAINTS.TABLE_SCHEMA as TableOwner,
> > INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_NAME,
> > INFORMATION_SCHEMA.TABLE_CONSTRAINTS.TABLE_NAME,
> > INFORMATION_SCHEMA.KEY_COLUMN_USAGE.COLUMN_NAME,
> > case
> >
>
objectproperty(object_id(INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_NAM
> > E)
> > ,'CnstIsClustKey')
> > when 1 then 'clustered'
> > when 0 then 'noneclustered'
> > end as IsClustered
> > FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS INNER JOIN
> > INFORMATION_SCHEMA.KEY_COLUMN_USAGE
> > ON INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_NAME => > INFORMATION_SCHEMA.KEY_COLUMN_USAGE.CONSTRAINT_NAME
> > WHERE INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_TYPE LIKE 'PRIMARY
> > KEY'
> > ORDER BY INFORMATION_SCHEMA.TABLE_CONSTRAINTS.TABLE_NAME
> > "Julie" <anonymous@.discussions.microsoft.com> wrote in message
> > news:059e01c3de7b$17a85f80$a401280a@.phx.gbl...
> > > Hello,
> > >
> > > Does anyone know how to return the names of the clustered
> > > and non clustered indexes from a database table ?
> > >
> > > Thanks
> > > J
> >
> >
>
Does anyone know how to return the names of the clustered
and non clustered indexes from a database table ?
Thanks
JJulie
SELECT INFORMATION_SCHEMA.TABLE_CONSTRAINTS.TABLE_SCHEMA as TableOwner,
INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_NAME,
INFORMATION_SCHEMA.TABLE_CONSTRAINTS.TABLE_NAME,
INFORMATION_SCHEMA.KEY_COLUMN_USAGE.COLUMN_NAME,
case
objectproperty(object_id(INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_NAM
E)
,'CnstIsClustKey')
when 1 then 'clustered'
when 0 then 'noneclustered'
end as IsClustered
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS INNER JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE
ON INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_NAME =INFORMATION_SCHEMA.KEY_COLUMN_USAGE.CONSTRAINT_NAME
WHERE INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_TYPE LIKE 'PRIMARY
KEY'
ORDER BY INFORMATION_SCHEMA.TABLE_CONSTRAINTS.TABLE_NAME
"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:059e01c3de7b$17a85f80$a401280a@.phx.gbl...
> Hello,
> Does anyone know how to return the names of the clustered
> and non clustered indexes from a database table ?
> Thanks
> J|||Thanks (Again) Uri,
I think I owe you a couple of drinks ;)
J
>--Original Message--
>Julie
>SELECT INFORMATION_SCHEMA.TABLE_CONSTRAINTS.TABLE_SCHEMA
as TableOwner,
> INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_NAME,
> INFORMATION_SCHEMA.TABLE_CONSTRAINTS.TABLE_NAME,
> INFORMATION_SCHEMA.KEY_COLUMN_USAGE.COLUMN_NAME,
> case
>objectproperty(object_id
(INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_NAM
>E)
>,'CnstIsClustKey')
> when 1 then 'clustered'
> when 0 then 'noneclustered'
> end as IsClustered
>FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS INNER JOIN
>INFORMATION_SCHEMA.KEY_COLUMN_USAGE
> ON INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_NAME =>INFORMATION_SCHEMA.KEY_COLUMN_USAGE.CONSTRAINT_NAME
>WHERE
INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_TYPE
LIKE 'PRIMARY
>KEY'
>ORDER BY INFORMATION_SCHEMA.TABLE_CONSTRAINTS.TABLE_NAME
>"Julie" <anonymous@.discussions.microsoft.com> wrote in
message
>news:059e01c3de7b$17a85f80$a401280a@.phx.gbl...
>> Hello,
>> Does anyone know how to return the names of the
clustered
>> and non clustered indexes from a database table ?
>> Thanks
>> J
>
>.
>|||Hi, Julie
Well , where will we meet?
"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:069801c3de95$bb2874f0$a501280a@.phx.gbl...
> Thanks (Again) Uri,
> I think I owe you a couple of drinks ;)
> J
> >--Original Message--
> >Julie
> >SELECT INFORMATION_SCHEMA.TABLE_CONSTRAINTS.TABLE_SCHEMA
> as TableOwner,
> > INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_NAME,
> > INFORMATION_SCHEMA.TABLE_CONSTRAINTS.TABLE_NAME,
> > INFORMATION_SCHEMA.KEY_COLUMN_USAGE.COLUMN_NAME,
> > case
> >objectproperty(object_id
> (INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_NAM
> >E)
> >,'CnstIsClustKey')
> > when 1 then 'clustered'
> > when 0 then 'noneclustered'
> > end as IsClustered
> >FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS INNER JOIN
> >INFORMATION_SCHEMA.KEY_COLUMN_USAGE
> > ON INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_NAME => >INFORMATION_SCHEMA.KEY_COLUMN_USAGE.CONSTRAINT_NAME
> >WHERE
> INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_TYPE
> LIKE 'PRIMARY
> >KEY'
> >ORDER BY INFORMATION_SCHEMA.TABLE_CONSTRAINTS.TABLE_NAME
> >"Julie" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:059e01c3de7b$17a85f80$a401280a@.phx.gbl...
> >> Hello,
> >>
> >> Does anyone know how to return the names of the
> clustered
> >> and non clustered indexes from a database table ?
> >>
> >> Thanks
> >> J
> >
> >
> >.
> >|||I am not really sure but the provided select only returns
constraint related indexes.
Indexes which are only present for performance are not
returned.
ben brugman
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:u6T2tFo3DHA.2544@.TK2MSFTNGP10.phx.gbl...
> Julie
> SELECT INFORMATION_SCHEMA.TABLE_CONSTRAINTS.TABLE_SCHEMA as TableOwner,
> INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_NAME,
> INFORMATION_SCHEMA.TABLE_CONSTRAINTS.TABLE_NAME,
> INFORMATION_SCHEMA.KEY_COLUMN_USAGE.COLUMN_NAME,
> case
>
objectproperty(object_id(INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_NAM
> E)
> ,'CnstIsClustKey')
> when 1 then 'clustered'
> when 0 then 'noneclustered'
> end as IsClustered
> FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS INNER JOIN
> INFORMATION_SCHEMA.KEY_COLUMN_USAGE
> ON INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_NAME => INFORMATION_SCHEMA.KEY_COLUMN_USAGE.CONSTRAINT_NAME
> WHERE INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_TYPE LIKE 'PRIMARY
> KEY'
> ORDER BY INFORMATION_SCHEMA.TABLE_CONSTRAINTS.TABLE_NAME
> "Julie" <anonymous@.discussions.microsoft.com> wrote in message
> news:059e01c3de7b$17a85f80$a401280a@.phx.gbl...
> > Hello,
> >
> > Does anyone know how to return the names of the clustered
> > and non clustered indexes from a database table ?
> >
> > Thanks
> > J
>|||ben
declare @.sql varchar(8000)
set @.sql = ''
select @.sql = @.sql + case when exists (
select * from information_schema.constraint_table_usage
where constraint_name = sysindexes.name)
then tablename+'.'+ name
else tablename + '.' + name end + char(10)
from (select top 100 percent object_name(id)tablename, name
from sysindexes
where objectproperty(id, 'IsMSShipped') = 0
and indid between 1 and 254
and (status & 64) = 0
order by tablename, indid desc) sysindexes
print @.sql
"ben brugman" <ben@.niethier.nl> wrote in message
news:ej6LMXN4DHA.2528@.TK2MSFTNGP09.phx.gbl...
> I am not really sure but the provided select only returns
> constraint related indexes.
> Indexes which are only present for performance are not
> returned.
> ben brugman
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:u6T2tFo3DHA.2544@.TK2MSFTNGP10.phx.gbl...
> > Julie
> > SELECT INFORMATION_SCHEMA.TABLE_CONSTRAINTS.TABLE_SCHEMA as TableOwner,
> > INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_NAME,
> > INFORMATION_SCHEMA.TABLE_CONSTRAINTS.TABLE_NAME,
> > INFORMATION_SCHEMA.KEY_COLUMN_USAGE.COLUMN_NAME,
> > case
> >
>
objectproperty(object_id(INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_NAM
> > E)
> > ,'CnstIsClustKey')
> > when 1 then 'clustered'
> > when 0 then 'noneclustered'
> > end as IsClustered
> > FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS INNER JOIN
> > INFORMATION_SCHEMA.KEY_COLUMN_USAGE
> > ON INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_NAME => > INFORMATION_SCHEMA.KEY_COLUMN_USAGE.CONSTRAINT_NAME
> > WHERE INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_TYPE LIKE 'PRIMARY
> > KEY'
> > ORDER BY INFORMATION_SCHEMA.TABLE_CONSTRAINTS.TABLE_NAME
> > "Julie" <anonymous@.discussions.microsoft.com> wrote in message
> > news:059e01c3de7b$17a85f80$a401280a@.phx.gbl...
> > > Hello,
> > >
> > > Does anyone know how to return the names of the clustered
> > > and non clustered indexes from a database table ?
> > >
> > > Thanks
> > > J
> >
> >
>
Subscribe to:
Posts (Atom)