Design Question: Nested Views And Functions?

Jul 23, 2005

I am working in a project where the business model is complex enough
that many common retrieval functions become difficult to develop and
maintain in single query statements or functions.

I have found the logic is easier to implement (and later modify when
the code is no longer freshly remembered), by implementing the
processing layers in nested views, and functions that call
sub-functions (UDFs), not too unlike object based programming or
non-DBMS procedural languages. In some cases, the views and functions
are nested three deep.

So far, from a design standpoint, this practice is working very well.
Code that would be a recusive mess is reduced to two or three simpler
code blocks. With the proper indexing and query structure, performance
seems to be satisfactory (time will tell).

In MS SQL Server, is there anything which makes this practice unsound?

Examples:

CREATE VIEW vw2 AS SELECT * FROM tbl1 WHERE ...
CREATE VIEW vw3 AS SELECT * FROM vw2 WHERE ...
Application uses: SELECT * FROM vw3

-or-

CREATE FUNCTION udf2 AS SELECT * FROM tbl1 WHERE ...
CREATE FUNCTION udf3 AS SELECT * FROM udf2 WHERE ...
Application uses: SELECT udf3(param) AS value

View 5 Replies


ADVERTISEMENT

Nested Aggregate Functions

Jul 12, 2000

I'm using the data environment of VB 6.0 to create data reports.

In Oracle you can use a nested aggregate function in a select query like MAX(SUM(Field)). Can I do this in SQL? If so, how?

thanx

View 2 Replies View Related

Nested Aggregate Functions

Feb 10, 2007

Hi everyone,

I have a database with the following tables:
- programmer
- software
- studies(name, splace, cost, course)
I would like to find out which school has the max. nr. of students, but my query - SELECT splace FROM studies HAVING count(name) = (SELECT max(count(name)) FROM studies) - doesn't works, becouse MSSQL doesn't supports nested aggregat functions.
What should I do? Please help me, thanks in advance.

View 1 Replies View Related

Nested Views

Oct 16, 2007

When running reports from data, is it faster using nested views approx 4 levels deep, or writing data to a temp tables then running the report?

View 3 Replies View Related

Views Vs Functions

Aug 22, 2007

I wanna know which adventages and disadventages have the command create funtion over create view or if you know a link that explain that

View 7 Replies View Related

Nested Views: How Inefficient?

May 20, 2004

I've been reading around that nested views can be quite inefficient because:

a) Using views in general involves some overheads (getting info from system tables etc.)

b) The optimiser doesn't do anything intelligent with them but just mixes all the joins from each of the nested views into one big, nasty join

If the only way to get the results I need involves writing this "big, nasty" join anyway, does it matter that I'm not putting it directly into a single view, but breaking it into components so that I can also access parts of the join for other purposes?

If the queries process lots of data, are the system overheads really that noticeable?

THANX!,
Angelos

View 7 Replies View Related

Why Nested Views Are Not Recommended?

Jul 23, 2005

I'm reading a book 'Professional SQL Server 2000 Programming' by RobertVieirathere is a recommendation: "stay away from building views based onviews"Why? What's so wrong with nested views?

View 2 Replies View Related

Using Scalar Functions In Views

Sep 19, 2007

I have a view that contains a complex query. A few of the columnscall a function that returns a specific output. I also use a functionto do a join as well.For example:SELECT l.ID, dbo.getStatus(l.ID) AS statusIDFROM tableName ALEFT OUTER JOIN dbo.Status_LKP s ON dbo.getStatus(l.Leg_ID) =s.statusIDFor 800 records, this query takes about 9 seconds. I realize that foreach record, the function is executed on a per row basis, so I amlooking for alternatives.Does anyone know of other ways to accomplish something of the same?Basically I would like to include UDFs in a query and use those UDFsin the where and join clauses.Thanks

View 2 Replies View Related

Nested Views Lock Management

Sep 14, 2007

Hi, I have a question about nested views. Is there a way for the view not to place any locks on the underlying tables? I tried to re-write my views and its nested views with "with (nolock)" but when I view the enterprise manager, I still see exclusive and share lock on the tables. Any help would be appreciated. Thanks.

View 3 Replies View Related

Decrypting Encrypted Views/Sp/Functions.....?

Sep 19, 2006

Hi all,As all of you are aware you can Encrypt your Triggers/Stored Procedures/Views And Functionsin Sql Server with "WITH ENCRYPTION" clause.recently i came across a Stored procedure on the Net that could reverse and decrypt all Encrypted objects.i personally tested it and it really works.That's fine (of course for some body)Now i want to know is it a Known Bug for Sql Server 2000 and is there a permanent solution for Encrypting mentioned objects.Thanks in advance.Best Regards.

View 2 Replies View Related

Views Or Table Valued Functions?

Aug 11, 2004

Hi I am writting Stored Procedures that have to be built on the base of other tables specially created for this purpose, is it better to create these intermediate tables as views or as functions returning tables? I guess views would be lighter on performance as they would not be created on the fly?

View 2 Replies View Related

Table Valued Functions Vs Views

Apr 24, 2008

Are there any disadvantages in respect to performance in using table valued functions instead of using a view.

Thanks...

View 3 Replies View Related

Views / Stored Procedures / Functions

Dec 1, 2005

Hi All,

Novice question. Would someone explain tell me what a view is used for? Also I am confused about the difference between a function and a stored procedure. They both seem like functions to me.

View 7 Replies View Related

Enabling Views, Functions Toggle

Oct 23, 2007

Hello:

Is it possible to standardize sql views or functions to filter data for the user, before they view the report? For example if I have a SQL view that filters for "green, cotton-made socks", can this somehow be an option among others before a user presses "view report?"

A response is greatly appreciated. If this is possible, how ?


Many thanks.

View 1 Replies View Related

Difference Among Store Procedure, Functions And Views

Feb 15, 2006

Hi,

What is the basic difference between

store procedure, Functions and views

Thanks

View 2 Replies View Related

Enabling Views, Functions Through Report Builder?

Oct 23, 2007

I was wondering if it was possible to incorporate sql views or functions that filter data on the top bar for users when they run a report.

a response is greatly appreciated! If so, how?

View 1 Replies View Related

Poor Performance - Nested Views && Complex Joins

Sep 27, 2006

The code below is from a nested view, which I've read should be avoided. I've also noticed GETDATE() is used, which I believe causes GETDATE() to be executed for every record selected (correct me if I'm wrong). I'm also guessing a JOIN containing a UNION against a SELECT statement is not a good idea. What other problems do you notice?

SELECT trans.Entry_Code, trans.D_C, trans.ADP_Security_# ,
trans.TRID, trans.Batch_Code, trans.Last_Money,
null as Shares, Settle_date as Process_Date,
null as Closing_Price, trans.Dwnld_Date, trans.Acnt,
null as Mktval,
cast(Null as varchar(20)) as Cusip_#,
ACT.dbo.account.account_key AS account_key
FROM (SELECT * FROM ADPDBBOOK.dbo.YTD05B
WHERE (DATEDIFF(mm, Process_Date, GETDATE()) <= 15)
UNION
SELECT * FROM ADPDBBOOK.dbo.YTD06B) trans
INNER JOIN ACT_DATA.dbo.account
ON ACT_DATA.dbo.account.account_key = RIGHT(trans.Acnt, 5)
INNER JOIN tbl_Accounts_TransactionalData
ON trans.Acnt = tbl_Accounts_TransactionalData.Acnt

Thanks, Dave

View 9 Replies View Related

Starting Out With : Views, Stored Procedures, Functions, Synonyms

Mar 22, 2006

Hi,Right, i have a fairly good understanding of SQL. However, i have a fairly basic understanding of SQL Server.I know Storedprocedures are really good and i'm starting to use them. I understand they are good for making inserting, updating very easy.However when you look at a SQL Server database you get various folder, this leaves me a little confused with what they are all used for? whats the difference between these?Thanks in advance!sorry for the basic question, i'll try to challange you next time

View 1 Replies View Related

The Query And The Views Or Functions In It Exceed The Limit Of 256 Tables.

Feb 22, 2007

Yes, I do know what this means and why the error is thrown but this is not my question.

I have two servers that are both running Windows Server 2003 and SQL Server 200 SP3. Below are the results from both servers using @@version

Sever 1 (BB)

Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)

Server 2 (Genesis)

Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)

These servers are identical or so it seems. I've got a real ugly query that uses views and a derived table to get results. The problem is the 256 limit message only comes up on one server and on the other (Genesis) the query runs fine. I get the error though it reads a 260 limit on a box with SP4 applied. I've also run the query on a box that is Windows 2003, sql2k and sp4 and the query runs but not on a similar server here. This is all very odd. Please note that the database structure, views, etc are all exactly the same as far as I know.

Any suggestions? There seems to be no pattern between versions of Windows and/or SP levels.

View 1 Replies View Related

Do You Replicate Views, Stored Procedures, And User Functions?

Jul 22, 2007

Greetings,



We have recently begun using transactional replication to keep the data in our SQL Servers synchronized in a geographically dispersed environment. We replicate our tables but we have never replicated views, stored procedures, or user functions in our production systems. We are thinking of doing so but wonder if the overhead of running the replication agents doesn't outweigh the benefits of having replication assist with the occassional change to these design elements.



Is anyone on this forum replicating views, sprocs, and user functions? What has your experience been?



Thanks for any ideas that you share.



BCB

View 4 Replies View Related

A Stored Proc To Search All Procs, Views, Triggers And Functions

Jul 20, 2005

I developed a search stored proc that searches all orsome of Procs, Views, Triggers and functions. Would anyone be interestedto see it posted here?Do you have any suggestions about other places, websites forums ...., toshare something I have developed?Thanks you for your response in advancePLease send me emailJoin Bytes! {Remove ### before responding}

View 1 Replies View Related

Programmatically Extract Stored Procedures, Views, Tables, And Functions

May 21, 2008



Hi Folks,


I am writing a program that transforms a generic MS SQL database to make it compatible with our application. In order to make the transformation, I have to access all the elements of the generic database programmatically.

I know that the Master database contains all this information. Is there a query that allows me to access the "non-system" tables, sps, views, and functions?

For Tables, I need to know the Name(s) of the tables, the column names, the column type, ALLOW Nulls, Primary Key, Identity Seed settings, and Triggers.

For SPs, I need to know the Name(s) and access the SP source code (assuming it's unencrypted).

For Views, I need to know the Name(s) and access the Views Source

For functions, I need to know the Name(s) and access the function source.


I can see various tables in the Master database from management studio, like sys.objects, sys.tables - which look like they have the info I need. However, when I run a query against master like:

select * from sys.objects .. I get an error:

Msg 208, Level 16, State 1, Line 1
Invalid object name 'sys.objects'.



Thank you in advance.



View 13 Replies View Related

SQL Server Admin 2014 :: Script To Find Nested Views In A Database

Oct 7, 2015

Any easy way to find if there are any nested views existing in the database?...using SQL server 2014...

View 1 Replies View Related

SQL Server 2008 :: Create Indexed Views On User Defined Functions?

Jul 14, 2015

I am creating mateialized view but it is failing with error that it can't be schema bound.

The query I am working to create materialized view are having joins with different tables and function.

Is it possible to create Indexed views on user defined functions?

View 2 Replies View Related

ORDER BY Clause Is Invalid In Views / Inline Functions / Derived Tables / Subqueries

Sep 25, 2013

The data I am pulling is correct I just cant figure out how to order by the last 8 numbers that is my NUMBER column. I tried adding FOR XML AUTO to my last line in my query: From AP_DETAIL_REG where AP_BATCH_ID = 1212 and NUMBER is not null order by NUMBER FOR XML AUTO) as Temp(DATA) where DATA is not null

but no change same error.
Output:
1234567890000043321092513 00050020

Select DATA from(
select '12345678'+
left( '0', 10-len(cast ( CONVERT(int,( INV_AMT *100)) as varchar))) +
cast (CONVERT(int,(INV_AMT*100)) as varchar) +
left('0',2-len(CAST (MONTH(DATE) as varchar(2))))+
CAST (MONTH(DATE) as varchar(2)) +
left('0',2-len(CAST (day(CHECK_DATE) as varchar(2)))) +
CAST (day(DATE) as varchar(2))+right(cast
(year(DATE)

[code]....

View 6 Replies View Related

Case Table And Nested Tables Design Problem

Oct 20, 2006

Hi,

I have a problem in design the tables.  My main task is to learn how to give the Match Score.

I have hundreds of dataset and one of them is like this:







Test Record Number: 19
Prospect ID = 254040088233400441105260031881009
Match Score = 95
Input Record Fielding  ( eg wordnumber[Field] ) : 1[1] 2[1] 3[11] 4[11] 5[11]
   Prospect Word = 1 type = 1 match level = 4 input word  = 1 input type  = 1
   Prospect Word = 2 type = 2 match level = 0 input word  = NA input type  = NA
   Prospect Word = 3 type = 3 match level = 4 input word  = 2 input type  = 1
   Prospect Word = 4 type = 11 match level = 4 input word  = 3 input type  = 11
   Prospect Word = 5 type = 13 match level = 4 input word  = 4 input type  = 11
   Prospect Word = 6 type = 14 match level = 4 input word  = 5 input type  = 11







Now I have all my data stored in the DB and I seperated them into 3 tables and their structures are:

1) prospect (id, testrecordnumber, prospectID, matchscore) 

2) inputfieldind (id, prspid, inputword, inputfield) 

3) prospectinfo (id, prspid, prospectword, prospecttype, matchlevel, inputword, inputtype)

and the prspid in table 2 & 3 refers to the prospectID in table 1.What I did was setting:

a) prospect table as case table with id AS key, prospectID AS input & predictable;

b) and the other two as nested tables with inputword/inputfield AS key & input, prospectword/prospecttype/matchlevel/inputword/inputtype AS key & input .

But it shows error for having multiply key columns...

 

And also I am thinking about using the Naive bayes algorithm.  Can I also have some suggestion on this?

 

Thanks

 

 

 

View 3 Replies View Related

Need Some Help With Design Of Tables/views

Jul 23, 2005

I have an applicaton in which I collect data for different parametersfor a set of devices. The data are entered into a single table, eachset of name, value pairs time-stamped and associated with a device.The definition of the table is as follows:CREATE TABLE devicedata(device_idintNOT NULL REFERENCES devices(id),-- id in the devicetabledatetimedatetimePRIMARY KEY CLUSTERED,-- date creatednamenvarchar(256)NOT NULL,-- name of the attributevaluesql_variantNOT NULL-- value)For example, I have 3 devices, and each is monitored for two attributes-- temperature and pressure. Data for these are gathered at say every20 minute and every 15 minute intervals.The table is filled with records over a period of time, and I canperform a variety of SQL queries.I have another requirement which requires me to retrieve the *latest*values of temperature and pressure for each device.Ideally, I'd like to use the data I have collected to get thisinformation, and I suppose I can.What I need is the SELECT statement to do this.I'd appreciate it very much, if someone can help provide that.Conceivably, I could use a SQL server View for making this easier forsome of my users.One alternate technique I thought was to create another table which I*update* with the latest value, each time I *insert* into the abovetable. But it seems like a waste to do so, and introduces needlessreferential integrity issues (minor). Maybe for fast access, that isthe best thing to do.I have requirements to maintain this data for several months/year ortwo, so I am dealing with a large number of samples.Any help would be appreciated.(I apologize if this post appears twice)

View 9 Replies View Related

Tracking Member Views Of Clan Pages (was Table Design Help Needed)

Feb 28, 2005

I am helping a friend with a gamming website. There are pages which displays data from other clans.

Members need to register and login to view full clan details. User who have not logged in can only view partial data about a clan.

I need to keep track of the kind of hits each page received. That is I want to tell say "Clan X" that these particular members viewed your page these many times and on these dates and these members who have not registered have viewed your page these many times and on these dates.

I am using ASP with MS SQL.

I would like some help on designing the table layout that is efficient for keeping track of the visitors for each page.

Any help will be appreciated.

View 14 Replies View Related

DB Design :: Create Views From 2 Tables One Of Which Is Lookup Table That Will Give Column Names

Aug 5, 2015

I am trying a create views that would join 2 tables:

Table 1: Has all the columns need by a view (
Name: Product
Structure: ID, Attribute 1, Attribute 2, Attribute 3, Attribute 4, Attribute 5 etc
Table 2: Is a lookup table that provides the names of columns
Name: lookupTable
Structure: tableName, ColumnName, columnValue
Values: Product, Attribute1, Color
Product, Attribute2, Size
Product, Attribute3, Flavor
Product, Attribute4, Shape

I want to create a view that looks like

ID, Color, Size, Flavor, Shape

View 4 Replies View Related

Are Embedded Views (Views Within Views...) Evil And If So Why?

Apr 3, 2006

Fellow database developers,I would like to draw on your experience with views. I have a databasethat includes many views. Sometimes, views contains other views, andthose views in turn may contain views. In fact, I have some views inmy database that are a product of nested views of up to 6 levels deep!The reason we did this was.1. Object-oriented in nature. Makes it easy to work with them.2. Changing an underlying view (adding new fields, removing etc),automatically the higher up views inherit this new information. Thismake maintenance very easy.3. These nested views are only ever used for the reporting side of ourapplication, not for the day-to-day database use by the application.We use Crystal Reports and Crystal is smart enough (can't believe Ijust said that about Crystal) to only pull back the fields that arebeing accessed by the report. In other words, Crystal will issue aSelect field1, field2, field3 from ReportingView Where .... eventhough "ReportingView" contains a long list of fields.Problems I can see.1. Parent views generally use "Select * From childview". This meansthat we have to execute a "sp_refreshview" command against all viewswhenever child views are altered.2. Parent views return a lot of information that isn't necessarilyused.3. Makes it harder to track down exactly where the information iscoming from. You have to drill right through to the child view to seethe raw table joins etc.Does anyone have any comments on this database design? I would love tohear your opinions and tales from the trenches.Best regards,Rod.

View 15 Replies View Related

SQL Server 2005: CLR Functions Vs SQL Functions

May 26, 2006

I was playing around with the new SQL 2005 CLR functionality andremembered this discussion that I had with Erland Sommarskog concerningperformance of scalar UDFs some time ago (See "Calling sp_oa* infunction" in this newsgroup). In that discussion, Erland made thefollowing comment about UDFs in SQL 2005:[color=blue][color=green]>>The good news is that in SQL 2005, Microsoft has addressed several of[/color][/color]these issues, and the cost of a UDF is not as severe there. In fact fora complex expression, a UDF in written a CLR language may be fasterthanthe corresponding expression using built-in T-SQL functions.<<I thought the I would put this to the test using some of the same SQLas before, but adding a simple scalar CLR UDF into the mix. The testinvolved querying a simple table with about 300,000 rows. Thescenarios are as follows:(A) Use a simple CASE function to calculate a column(B) Use a simple CASE function to calculate a column and as a criterionin the WHERE clause(C) Use a scalar UDF to calculate a column(D) Use a scalar UDF to calculate a column and as a criterion in theWHERE clause(E) Use a scalar CLR UDF to calculate a column(F) Use a scalar CLR UDF to calculate a column and as a criterion inthe WHERE clauseA sample of the results is as follows (time in milliseconds):(295310 row(s) affected)A: 1563(150003 row(s) affected)B: 906(295310 row(s) affected)C: 2703(150003 row(s) affected)D: 2533(295310 row(s) affected)E: 2060(150003 row(s) affected)F: 2190The scalar CLR UDF function was significantly faster than the classicscalar UDF, even for this very simple function. Perhaps a more complexfunction would have shown even a greater difference. Based on this, Imust conclude that Erland was right. Of course, it's still faster tostick with basic built-in functions like CASE.In another test, I decided to run some queries to compare built-inaggregates vs. a couple of simple CLR aggregates as follows:(G) Calculate averages by group using the built-in AVG aggregate(H) Calculate averages by group using a CLR aggregate that similatesthe built-in AVG aggregate(I) Calculate a "trimmed" average by group (average excluding highestand lowest values) using built-in aggregates(J) Calculate a "trimmed" average by group using a CLR aggregatespecially designed for this purposeA sample of the results is as follows (time in milliseconds):(59 row(s) affected)G: 313(59 row(s) affected)H: 890(59 row(s) affected)I: 216(59 row(s) affected)J: 846It seems that the CLR aggregates came with a significant performancepenalty over the built-in aggregates. Perhaps they would pay off if Iwere attempting a very complex type of aggregation. However, at thispoint I'm going to shy away from using these unless I can't find a wayto do the calculation with standard SQL.In a way, I'm happy that basic SQL still seems to be the fastest way toget things done. With the addition of the new CLR functionality, Isuspect that MS may be giving us developers enough rope to comfortablyhang ourselves if we're not careful.Bill E.Hollywood, FL------------------------------------------------------------------------- table TestAssignment, about 300,000 rowsCREATE TABLE [dbo].[TestAssignment]([TestAssignmentID] [int] NOT NULL,[ProductID] [int] NULL,[PercentPassed] [int] NULL,CONSTRAINT [PK_TestAssignment] PRIMARY KEY CLUSTERED([TestAssignmentID] ASC)--Scalar UDF in SQLCREATE FUNCTION [dbo].[fnIsEven](@intValue int)RETURNS bitASBEGINDeclare @bitReturnValue bitIf @intValue % 2 = 0Set @bitReturnValue=1ElseSet @bitReturnValue=0RETURN @bitReturnValueEND--Scalar CLR UDF/*using System;using System.Data;using System.Data.SqlClient;using System.Data.SqlTypes;using Microsoft.SqlServer.Server;public partial class UserDefinedFunctions{[Microsoft.SqlServer.Server.SqlFunction(IsDetermini stic=true,IsPrecise=true)]public static SqlBoolean IsEven(SqlInt32 value){if(value % 2 == 0){return true;}else{return false;}}};*/--Test #1--Scenario A - Query with calculated column--SELECT TestAssignmentID,CASE WHEN TestAssignmentID % 2=0 THEN 1 ELSE 0 END ASCalcColumnFROM TestAssignment--Scenario B - Query with calculated column as criterion--SELECT TestAssignmentID,CASE WHEN TestAssignmentID % 2=0 THEN 1 ELSE 0 END ASCalcColumnFROM TestAssignmentWHERE CASE WHEN TestAssignmentID % 2=0 THEN 1 ELSE 0 END=1--Scenario C - Query using scalar UDF--SELECT TestAssignmentID,dbo.fnIsEven(TestAssignmentID) AS CalcColumnFROM TestAssignment--Scenario D - Query using scalar UDF as crierion--SELECT TestAssignmentID,dbo.fnIsEven(TestAssignmentID) AS CalcColumnFROM TestAssignmentWHERE dbo.fnIsEven(TestAssignmentID)=1--Scenario E - Query using CLR scalar UDF--SELECT TestAssignmentID,dbo.fnIsEven_CLR(TestAssignmentID) AS CalcColumnFROM TestAssignment--Scenario F - Query using CLR scalar UDF as crierion--SELECT TestAssignmentID,dbo.fnIsEven_CLR(TestAssignmentID) AS CalcColumnFROM TestAssignmentWHERE dbo.fnIsEven(TestAssignmentID)=1--CLR Aggregate functions/*using System;using System.Data;using System.Data.SqlClient;using System.Data.SqlTypes;using Microsoft.SqlServer.Server;[Serializable][Microsoft.SqlServer.Server.SqlUserDefinedAggregate (Format.Native)]public struct Avg{public void Init(){this.numValues = 0;this.totalValue = 0;}public void Accumulate(SqlDouble Value){if (!Value.IsNull){this.numValues++;this.totalValue += Value;}}public void Merge(Avg Group){if (Group.numValues > 0){this.numValues += Group.numValues;this.totalValue += Group.totalValue;}}public SqlDouble Terminate(){if (numValues == 0){return SqlDouble.Null;}else{return (this.totalValue / this.numValues);}}// private accumulatorsprivate int numValues;private SqlDouble totalValue;}[Serializable][Microsoft.SqlServer.Server.SqlUserDefinedAggregate (Format.Native)]public struct TrimmedAvg{public void Init(){this.numValues = 0;this.totalValue = 0;this.minValue = SqlDouble.MaxValue;this.maxValue = SqlDouble.MinValue;}public void Accumulate(SqlDouble Value){if (!Value.IsNull){this.numValues++;this.totalValue += Value;if (Value < this.minValue)this.minValue = Value;if (Value > this.maxValue)this.maxValue = Value;}}public void Merge(TrimmedAvg Group){if (Group.numValues > 0){this.numValues += Group.numValues;this.totalValue += Group.totalValue;if (Group.minValue < this.minValue)this.minValue = Group.minValue;if (Group.maxValue > this.maxValue)this.maxValue = Group.maxValue;}}public SqlDouble Terminate(){if (this.numValues < 3)return SqlDouble.Null;else{this.numValues -= 2;this.totalValue -= this.minValue;this.totalValue -= this.maxValue;return (this.totalValue / this.numValues);}}// private accumulatorsprivate int numValues;private SqlDouble totalValue;private SqlDouble minValue;private SqlDouble maxValue;}*/--Test #2--Scenario G - Average Query using built-in aggregate--SELECT ProductID, Avg(Cast(PercentPassed AS float))FROM TestAssignmentGROUP BY ProductIDORDER BY ProductID--Scenario H - Average Query using CLR aggregate--SELECT ProductID, dbo.Avg_CLR(Cast(PercentPassed AS float)) AS AverageFROM TestAssignmentGROUP BY ProductIDORDER BY ProductID--Scenario I - Trimmed Average Query using built in aggregates/setoperations--SELECT A.ProductID,CaseWhen B.CountValues<3 Then NullElse Cast(A.Total-B.MaxValue-B.MinValue ASfloat)/Cast(B.CountValues-2 As float)End AS AverageFROM(SELECT ProductID, Sum(PercentPassed) AS TotalFROM TestAssignmentGROUP BY ProductID) ALEFT JOIN(SELECT ProductID,Max(PercentPassed) AS MaxValue,Min(PercentPassed) AS MinValue,Count(*) AS CountValuesFROM TestAssignmentWHERE PercentPassed Is Not NullGROUP BY ProductID) BON A.ProductID=B.ProductIDORDER BY A.ProductID--Scenario J - Trimmed Average Query using CLR aggregate--SELECT ProductID, dbo.TrimmedAvg_CLR(Cast(PercentPassed AS real)) ASAverageFROM TestAssignmentGROUP BY ProductIDORDER BY ProductID

View 9 Replies View Related

Large Views Vs Multiple Small Views

Sep 6, 2007

Which is more efficient? One large view that joins >=10 tables, or a few smaller views that join only the tables needed for individual pages?

View 1 Replies View Related

Recompiling Views That Reference Other Views

Jun 28, 2007

Hello.

Newbie here. I've only been using SQL for about a year now and have some minor questions about sql objects that reference other objects.



We have some views which reference other views in the joins. I will call one the primary view and the one being referenced in the joins as the secondary view.

Recently we made changes to the secondary view.

After which the primary views which referenced it would not work because of this change and had to be 'refreshed' by using drop/create scripts which essentially just dropped it and recreated the exact same view. I do not recall the exact error message that was returned other than it seemed to suggest that it could no longer see the secondary view since it had been changed. Nothing in the primary view was changed in any way, just the secondary.



Some here where I work have suggested off hand that this was a recompile of the primary view because the contents of the secondary changed.

My questions are:

1. Exactly why did this happen and is there a proper name for it when it does?

2. The same problem does not seem to occur when we have stored procedures referencing views in the joins which had just been changed. Why is that?



Thanks for any help on the matter. I greatly appreciate it.

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved