Foreign Keys and the query optimizer in SQL Server 2K8

View previous topic View next topic Go down

Foreign Keys and the query optimizer in SQL Server 2K8

Post  Jeff Smith on Fri Aug 27, 2010 5:07 pm

I am using SQL Server 2008. 2K8 has a star optimizer to improve the performance of queries. I've read that it improves query performance by 15-30%. Do foreign keys have to be defined on the fact tables in order for the star optimizer to be effective?

In the past, I've never defined the foreign keys as I didn't see any benefit. I plan to define them on the current database (turning off referential integrity) in the near future. I was wondering if defining them would lead to a performance improvement or is the DBS already benefitting from the optimizer?

Jeff Smith

Posts : 471
Join date : 2009-02-03

View user profile

Back to top Go down

Re: Foreign Keys and the query optimizer in SQL Server 2K8

Post  Jeff Smith on Tue Aug 31, 2010 3:07 pm

I found the answer - Foreign Keys do not have to be defined in order for SQL Server 2K8 to use the Star Optimizer, but defining the Foreign Keys on the fact tables will make the optimizer work better.

Jeff Smith

Posts : 471
Join date : 2009-02-03

View user profile

Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum