| Author |
Topic |
|
tmcrouse
Starting Member
12 Posts |
 Posted - 2015-02-03 : 13:52:43
|
| I have not null constraints on my tables in SQL Server, however I don't have any foreign key constraints and think this might be why things are running slow. Example I have a state and business table and the state has state_id as PK then business bus_id as PK. then i have a table that is my main table where data is fed. in this main table i have M_ID as the PKbus_IDstate_idthe bus_id and state_id should be FK, correct? and if they are to be FK, how do I alter to say make these FK?ALTER TABLE Mainalter bus_id,CONSTRAINT Bus_ID FOREIGN KEY;tmc |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2015-02-03 : 13:58:49
|
| Foreign keys do not improve performance. As foreign key columns are often used in joins and where clauses, you should add indexes to them. You can use ALTER TABLE ADD CONSTRAINT to add foreign keys. Then use CREATE INDEX to add indexes to those columns. You can find examples of both in Books Online.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
 |
|
|
tmcrouse
Starting Member
12 Posts |
Posted - 2015-02-03 : 14:40:09
|
| Ok, so I did the following and see it added an FKALTER TABLE anthemq.dbo.performanceguaranteeADD FOREIGN KEY (unique_ID)REFERENCES program (unique_ID);I will check ou the links to see how to add an indextmc |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2015-02-03 : 14:47:31
|
| If the column is unique, use a unique index which has performance benefits.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
tmcrouse
Starting Member
12 Posts |
Posted - 2015-02-03 : 15:18:33
|
| I am confused because I created the FK and they automatically went into the index section. If I go in and create an index, like create unique index QualBuson BusUnit (Bus_ID);It will put in another what looks like a primary key in the index folder and it is named QualBus.That does not seem right to have an FK in index and then another FK that pops up with different name pointing to the same thing. Could it be when I created the FK it automatically did the index?tmc |
|
|
|
tmcrouse
Starting Member
12 Posts |
Posted - 2015-02-03 : 15:32:35
|
| Ok, my SQL Server DBA just said that indexes should not really be used for small databases. He said Tina, your queries run fast, no? He is from India.I said it runs super fast. I can do an outer and inner join on the SQL Server R2 and it runs in less than 5 seconds. He said, then it is not a server or table structure issue. He said he would not suggest indexes because the database is just so small. He suggests it is the front-end Tableau that is having hard times processing because of network, VPN and ISP.tmc |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2015-02-03 : 15:37:50
|
| 5 seconds is an eternity for the apps that I support. We measure performance in microseconds and milliseconds.All databases should have indexes in there regardless of size, though it becomes crucial for larger databases.I can't say if it's the front-end or not, I can only tell you best practices based on what you've told us in this thread.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2015-02-03 : 15:43:01
|
| FKs will appear in the Keys section of the SSMS GUI. The constraints section is for defaults. Keep in mind that an FK and an index pointing to the same column are different objects.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
|
FK constraint