Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

Foreign Keys and the query optimizer in SQL Server 2K8

Go down

Foreign Keys and the query optimizer in SQL Server 2K8 Empty Foreign Keys and the query optimizer in SQL Server 2K8

Post  Jeff Smith 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

Back to top Go down

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

Post  Jeff Smith 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

Back to top Go down

Back to top

- Similar topics

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