Solved

Team foundations (SSDT) Index generating constraint

Posted on 2013-01-04
15
360 Views
Last Modified: 2013-01-08
I am working with SSDT 2012 and Team Foundations 2012 source control. I have created an index on my dev server and I am attempting to update the Project in TFS.  One issue I ran into is that SS creates a constraint and FK which I did no realize until now. But I am trying to find out in I need to include the constraints in  the check-in or will SS be smart enough to build them when the Prod server is updated.  I'm kinda new to TFS with SQL  Server So any help/ advise would be appreciated.
0
Comment
Question by:rschmehl
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 7
15 Comments
 
LVL 15

Expert Comment

by:David L. Hansen
ID: 38744154
I wouldn't worry about losing the PK or FK constraints in TFS.  Those sorts of constraints are pretty high priority in any database design.  To verify this, upload the project to TFS then pull it down to another computer (if one is available).  At that point you can check to make sure those constraints are in-tact.
0
 

Author Comment

by:rschmehl
ID: 38744225
So you are saying that if I remove them (Exclude them) in the TFS update / checkin that  when the code is generated in Prod they will be rebuild?    
I do not have any way yet to see if what you suggust is true, although I  think it is.
0
 
LVL 15

Expert Comment

by:David L. Hansen
ID: 38744237
Why exclude them?  I thought you just wanted to store the project in TFS.
0
Turn Insights into Action

Communication across every corner of your business is essential to increase the velocity of your application delivery and support pipeline. Automate, standardize, and contextualize your communication processes with xMatters.

 

Author Comment

by:rschmehl
ID: 38744394
OK, when I do a Compare ( Dev ss to Project ) and get the differences I go though the list and exclude every thing , then i go bak through and add the index I want.  after that I do an update, which has been showing me things which I did not know where they were coming from.  O, and when I check the index I also check the table to add it.    This may be wrong or I may be missing saomething.
0
 
LVL 15

Expert Comment

by:David L. Hansen
ID: 38744520
It seems that your question is more of..."do I need those constraints?"  If you want them, then include them explicitly.  If not, then exclude them.  My guess is that the design you've given SSDT requires these constraints even though you didn't expressly add them yourself.  May I ask which tables and fields are getting those constraints?
0
 

Author Comment

by:rschmehl
ID: 38744625
To answer your question, Yes,  When I added the indexes to my dev SS and tested I did not realize constrants were added by SS.   (Just did not think about it )  but yes in TFS at the update level I ADD the Table and Index and TFS  Includes the Constraint  in the create code.  As to your question on which tables it is the one(s) I want to add the new index too.

I'm fairly new to DBA world and especially TFS, as if you couldn't tell.  But yes , do i need the constraints, when i tested the load process in my Dev envorinment I had no issues with data.  Am I missing a key concept or something?
0
 
LVL 15

Expert Comment

by:David L. Hansen
ID: 38744711
Are you familiar with Primary Key and Foreign Key concepts?
0
 

Author Comment

by:rschmehl
ID: 38744780
yes
0
 

Author Comment

by:rschmehl
ID: 38744790
When I added the Indexes I did not notice the contraints being put on the DB.  So when I went to team to promote the indexes to stage ( which I have not dooe in a while) I noticed the constraints in the code. )
 So I know they are there to preserve referential integraty but my concern is messing up TFS.  do i add the constraint or do I hope TFS is smart enough to create themwhen it promotes the code?
0
 

Author Comment

by:rschmehl
ID: 38750924
Did i say something wrong?
0
 
LVL 15

Expert Comment

by:David L. Hansen
ID: 38751206
No, not at all.  I'm sorry.  Just keeping up with my day job and had a crazy busy weekend.  Back now and I hope I didn't delay you too much.

So, If we understand how the constraints will work and agree with the system for putting them in, then I'd formally add the constraints yourself.  That way, we won't even need to worry about what TFS will do (it will of course just preserve what you hand it).  You'll just know that those constraints will now be part of your overall design.
0
 

Author Comment

by:rschmehl
ID: 38751526
Thank you, me too,  But I think I'm seeing daylight on this issue   I only added indexes on my dev server and when i compared to the project to do my update I excluded everything and then just added the index and table  Since then i have noticed that someone before me added those constraints. So when iexcluded them it messed me up and now i need to rollback a few items.   but I saw all kinds of stuff in cyber land on this including auto generated constraints,grants .  Im off sick today but i will check tommorow.  I know this may sound confusing to you, as it does to me  So let me just ask one more thing to clarify :   if all i want to do(after testing) is add the indexes I should be able to do this without having to add constraints ????.  similar to adding missing indexes script ?   I truly appreciate your help.  you get all the marbles.  thanks
0
 
LVL 15

Accepted Solution

by:
David L. Hansen earned 500 total points
ID: 38751671
Sure. You can do that.  Add the constraints and put it into TFS.  Then when you add indexing you'll be able to do so without effecting the constraints or the integrity of the system.
0
 

Author Closing Comment

by:rschmehl
ID: 38755623
Thank you  appreciate the advise.
0
 
LVL 15

Expert Comment

by:David L. Hansen
ID: 38755641
Thanks for the points.  Feeling better yet?
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

691 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question