referential integrity

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.
OllieMost 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.googlegroups.com...
> On Jun 20, 3:27 pm, "news.microsoft.com" <ollie_ric...@.hotmail.com>
> wrote:
> 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
>sql

referencing system/CLR assemblies in SQLCLR

Throughout the course of this book and even before it I have come across conflicting information regarding how SQLCLR attempts to resolve system/CLR assembly references. For example, prior to my latest read thourgh April BOL 2005, I thought SQLCLR attempted to resolve these references implicity for you via the local machine's GAC. Yet here is what I found while trying to help another person in this forum yesterday in BOL...

Assembly Validation

SQL Server performs checks on the assembly binaries uploaded by the CREATE ASSEMBLY statement to guarantee the following:

The assembly binary is well formed with valid metadata and code segments, and the code segments have valid Microsoft Intermediate language (MSIL) instructions.

The set of system assemblies it references is one of the following supported assemblies in SQL Server: Microsoft.Visualbasic.dll, Mscorlib.dll, System.Data.dll, System.dll, System.Xml.dll, Microsoft.Visualc.dll, Custommarshallers.dll, System.Security.dll, System.Web.Services.dll, and System.Data.SqlXml.dll. Other system assemblies can be referenced, but they must be explicitly registered in the database.

For assemblies created by using SAFE or EXTERNAL ACCESS permission sets:

The assembly code should be type-safe. Type safety is established by running the common language runtime verifier against the assembly.

The assembly should not contain any static data members in its classes unless they are marked as read-only.

The classes in the assembly cannot contain finalizer methods.

The classes or methods of the assembly should be annotated only with allowed code attributes. For more information, see Custom Attributes for CLR Routines.

Besides the previous checks that are performed when CREATE ASSEMBLY executes, there are additional checks that are performed at execution time of the code in the assembly:

Calling certain Microsoft .NET Framework APIs that require a specific Code Access Permission may fail if the permission set of the assembly does not include that permission.

For SAFE and EXTERNAL_ACCESS assemblies, any attempt to call .NET Framework APIs that are annotated with certain HostProtectionAttributes will fail.

COULD SOMEONE PLEASE GIVE ME THE DEFENITE ANSWER ON HOW THIS WORKS!

And as a side note even if there is nothing more "to it" then these few lines in the CREATE ASSEMBLY topic in BOL, I think both this forum and the others prove that more information needs to be in BOL regarding this topic. How it relates to the subset of the .Net framework we have access to, HPAs, and CAS permissions.|||

Hi Derek,

Where have you found conflicting statements on the SQL CLR assembly loading process? If it's in BOL or in another MS reference, let me know or file a bug on connect.microsoft.com so it can be corrected.

The passage from BOL above is accurate: only the system assemblies on the "Supported .NET Framework Libraries" list can be implicitly referenced from the GAC; all others must be created explicitly through CREATE ASSEMBLY.

I think that CAS and HPA are also covered pretty thoroughly in BOL. "CLR Integration Code Access Security" describes all the permissions available under each permission set and you can use a tool such as PermView to see which ones are requested by your code. "Host Protection Attributes and CLR Integration Programming" provides the same treatment for HPAs, along with listing every class or method in the supported system assemblies that can't be used in SAFE or EXTERNAL_ACCESS.

Actually, I just noticed that "CLR Integration Programming Model Restrictions" does contain an error: the custom attributes it lists are not explicitly disallowed in the UNSAFE permission set (although they are still not recommended and may not work as expected). I'll file a bug to get this fixed.

Steven

Referencing AS columns

I'm just converting my Access database to SQL Server and have come across a number of differences. In Access I was able to do the following:
SELECT ZNew = Max( ..
ZNew2 = [ZNew] - Price
FROM ...
Can I reference ZNew in line 2 above, or do I need to duplicate the 'Max(' line? Help would be appreciated. Thanks.Try:

SELECT ZNew = Max([ZNew] - Price)
FROM ...

blindman|||Did you mean ZNew2 = Max([ZNew] etc. ...
The first line ZNew = Max( .. is an extensive CASE evaluation.
Nice not to have to repeat it in the ZNew2 expression.|||Can I reference ZNew in line 2 above, or do I need to duplicate the 'Max

No, seems to me that you must specify all the syntax :

SELECT ZNew = Max( ...),
ZNew2 = Max(...) - Price
FROM ...|||If you are using a case function you need to show us your statement.

blindman|||Here's the code:
SELECT TCode, ZValue = Max(CASE WHEN TCode = 'AAA' AND TValue > 1000 THEN 1000
WHEN TCode = 'BBB' AND TValue > 500 THEN 500 ELSE TValue END) ,
ZNew2 = ZValue - TKgValue|||You haven't included your FROM clause, so I can't tell if ZValue exists in an underlying table as well as being constructed from your case clause. If your tables have a field called ZValue in them, that is the value that will be used when you try to calculate ZNew2. Otherwise, I think you will get an error stating that SQL Server can't find field ZValue. You cannot create it and then reference it in the same statement, so you will have to repeat your case statement.

There are ways to avoid repeating the CASE statement, such as this method using nested queries:

SELECT TCode,
ZValue,
ZValue - TKgValue ZNew2
FROM (SELECT TCode,
Max(CASE WHEN TCode = 'AAA' AND TValue > 1000 THEN 1000
WHEN TCode = 'BBB' AND TValue > 500 THEN 500
ELSE TValue END) ZValue,
TKgValue
From YourTableReferencese) ZValueSubquery

blindman|||The Value does not exist and a nested query will not work in this case, so I'll just have to repeat the CASE statement. Thanks.|||I think your code would be easier to maintain, (and may run faster) if you use the nested query approach.

blindman

Reference report outside of current project

Hi, I am migrating across some reports from 2000 and some of the
reports reference reports outside of the current project when a person
clicks on a value.
In the report designer under Navigation -> Jump to report the report
has the following value
\parentFolder\Report Name
Under 2005, when I try to do this I get the following error
Item names cannot contain the following reserved characters ;?:@.&=+$,
\*<>|"
So, how can I navigate to a report outside the current folder/
project?
Thanks for any help
MarkusOn May 1, 8:52 pm, Markus...@.gmail.com wrote:
> Hi, I am migrating across some reports from 2000 and some of the
> reports reference reports outside of the current project when a person
> clicks on a value.
> In the report designer under Navigation -> Jump to report the report
> has the following value
> \parentFolder\Report Name
> Under 2005, when I try to do this I get the following error
> Item names cannot contain the following reserved characters ;?:@.&=+$,
> \*<>|"
> So, how can I navigate to a report outside the current folder/
> project?
> Thanks for any help
> Markus
You can either add the report to the current project (via right-
clicking the Reports folder in the Solution Explorer and select Add ->
Existing Item) -or- you can access the outside report via Jump to URL
(assuming you have already uploaded it to the Report Server). Hope
this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||On May 2, 12:30 pm, EMartinez <emartinez...@.gmail.com> wrote:
> On May 1, 8:52 pm, Markus...@.gmail.com wrote:
>
>
> > Hi, I am migrating across some reports from 2000 and some of the
> > reports reference reports outside of the current project when a person
> > clicks on a value.
> > In the report designer under Navigation -> Jump to report the report
> > has the following value
> > \parentFolder\Report Name
> > Under 2005, when I try to do this I get the following error
> > Item names cannot contain the following reserved characters ;?:@.&=+$,
> > \*<>|"
> > So, how can I navigate to a report outside the current folder/
> > project?
> > Thanks for any help
> > Markus
> You can either add the report to the current project (via right-
> clicking the Reports folder in the Solution Explorer and select Add ->
> Existing Item) -or- you can access the outside report via Jump to URL
> (assuming you have already uploaded it to the Report Server). Hope
> this helps.
> Regards,
> Enrique Martinez
> Sr. Software Consultant- Hide quoted text -
> - Show quoted text -
Hi Enrique, I need to pass parameters between the reports so I doubt
that I will be able to use the "Jump to URL"
So, there is no way of climbing up a directory like I did in 2000/
2003?
Thanks
Markus