When implementing a schema for data warehousing is it common practice not to
implement referential integrity across the tables in the database?

If true is it purely for ETL purposes or does it improve query performance
if the referential integrity is not applied to the tables.

Ollie

Most of the sql server implementations (both OLTP and OLAP) I have seen in
my many years of consulting on the product have not had much if any ref.
integrity in place. RF can provide the optimizer with useful information,
yet it also takes overhead to maintain/enforce. And without it you get
fewer application errors - yet allow in bad data. Most designers/developers
seem to take the easy road . . .

--
TheSQLGuru
President
Indicium Resources, Inc.

"news.microsoft.com" <ollie_riches@hotmail.com> wrote in message
news:uIbwJ7zsHHA.400@TK2MSFTNGP02.phx.gbl...
> When implementing a schema for data warehousing is it common practice not
> to implement referential integrity across the tables in the database?
>
> If true is it purely for ETL purposes or does it improve query performance
> if the referential integrity is not applied to the tables.
>
> Ollie
>

Yes, this is very common in a data warehouse. In OLTP databases, DRI is
VERY important to ensure the referential integrity of the data because the
data is coming in from applications and possibly other places. In a data
warehouse, the ONLY way data should ever get into your warehouse is through
your ETL process(es). Since these processes should always do thorough
checking of the data inclusing references, you can safely remove the DRI
since it can speed up the ETL loads. However, I always include it by
default even in warehouses as an extra safety check and only remove it if
needed for the performance boost (only ever had to do this twice in many
years when it involved loading 10's to 100's of millions of rows a night in
a tight window of time.

"news.microsoft.com" <ollie_riches@hotmail.com> wrote in message
news:uIbwJ7zsHHA.400@TK2MSFTNGP02.phx.gbl...
> When implementing a schema for data warehousing is it common practice not
> to implement referential integrity across the tables in the database?
>
> If true is it purely for ETL purposes or does it improve query performance
> if the referential integrity is not applied to the tables.
>
> Ollie
>

On Jun 20, 3:27 pm, "news.microsoft.com" <ollie_ric...@hotmail.com>
wrote:
> When implementing a schema for data warehousing is it common practice not to
> implement referential integrity across the tables in the database?
>
> If true is it purely for ETL purposes or does it improve query performance
> if the referential integrity is not applied to the tables.
>
> Ollie

I prefer having referential integrity enabled on the development
environment, removing it in production.
It has the benefit of helping ETL developers finding errors very early
and clearly.

Marco Russo
http://www.sqlbi.eu
http://sqlblog.com/blogs/marco_russo

same for me.

yes in dev
no in prod.


"Marco Russo" <marco.russo@loader.it> wrote in message
news:1182449884.703796.298200@n2g2000hse.googlegro ups.com...
> On Jun 20, 3:27 pm, "news.microsoft.com" <ollie_ric...@hotmail.com>
> wrote:
>> When implementing a schema for data warehousing is it common practice not
>> to
>> implement referential integrity across the tables in the database?
>>
>> If true is it purely for ETL purposes or does it improve query
>> performance
>> if the referential integrity is not applied to the tables.
>>
>> Ollie
>
> I prefer having referential integrity enabled on the development
> environment, removing it in production.
> It has the benefit of helping ETL developers finding errors very early
> and clearly.
>
> Marco Russo
> http://www.sqlbi.eu
> http://sqlblog.com/blogs/marco_russo
>