I've never worked with views before though I have a vaugue idea what they're for :)
I have a table that is quite large and growing fast and my primary use for the data is to run lots of aggregate functions on it.
See my previous post for an example:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=63748
Queries like that are taking 10 seconds to run.
So what I'm looking for is a way to store the results of a query like that, run the actual query maybe once an hour, and any time a user needs that data they get the results of the last run.
My first thought was to just have "summary" tables that get updated by a Stored Procudure that runs on a schedule under "Jobs". Is there a better way?
Thanks!
Edit: Some of the "summary" data would be based on date, so the summarized data would only need to be processed once. Don't know if that is something to consider.
I am looking to create a constraint on a table that allows multiplenulls but all non-nulls must be unique.I found the following scripthttp://www.windowsitpro.com/Files/0.../Listing_01.txtthat works fine, but the following lineCREATE UNIQUE CLUSTERED INDEX idx1 ON v_multinulls(a)appears to use indexed views. I have run this on a version of SQLStandard edition and this line works fine. I was of the understandingthat you could only create indexed views on SQL Enterprise Edition?
Write a CREATE VIEW statement that defines a view named Invoice Basic that returns three columns: VendorName, InvoiceNumber, and InvoiceTotal. Then, write a SELECT statement that returns all of the columns in the view, sorted by VendorName, where the first letter of the vendor name is N, O, or P.
This is what I have so far,
CREATE VIEW InvoiceBasic AS SELECT VendorName, InvoiceNumber, InvoiceTotal From Vendors JOIN Invoices ON Vendors.VendorID = Invoices.VendorID
I created a query, which makes use of a temp table, and I need the results to be displayed in a View. Unfortunately, Views do not support temp tables, as far as I know, so I put my code in a stored procedure, with the hope I could call it from a View....
I compared view query plan with query plan if I run the same statementfrom view definition and get different results. View plan is moreexpensive and runs longer. View contains 4 inner joins, statisticsupdated for all tables. Any ideas?
I had given one of our developers create view permissions, but he wants to also modify views that are not owned by him, they are owned by dbo.
I ran a profiler trace and determined that when he tries to modify a view using query designer in SQLem or right clicks in SQLem on the view and goes to properties, it is performing a ALTER VIEW. It does the same for dbo in a trace (an ALTER View). He gets a call failed and a permission error that he doesn't have create view permissions, object is owned by dbo, using both methods.
If it is doing an alter view how can I set permissions for that and why does it give a create view error when its really doing an alter view? Very confusing.
CREATE VIEW dbo.vwFeat AS SELECT dbo.Lk_Feat.Descr, dbo.Lk_Feat.Price, dbo.Lk_Feat.Code, dbo.SubFeat.SubNmbr FROM dbo.Lk_Feat INNER JOIN dbo.SubFeat ON dbo.Lk_Feat.Idf = dbo.SubFeat.Idt
When ever I open using SQL Entreprise manager to edit it by adding or removing a field i inserts Expr1,2.. and I don t want that. The result I get is:
SELECT dbo.Lk_Feat.Descr AS Expr1, dbo.Lk_Feat.Price AS Expr2, dbo.Lk_Feat.Code AS Expr3, dbo.SubFeat.SubNmbr AS Expr4 FROM dbo.Lk_Feat INNER JOIN dbo.SubFeat ON dbo.Lk_Feat.Idf = dbo.SubFeat.Idt
I don t want Entreprise manager to generate the Expr fields since I use the real fields in my application. Thanks for help
I was looking through our vendors views, searching for something Ineeded for our Datawarehouse and I came across something I do notunderstand: I found a view that lists data when I use it in t-sql,however when I try to use the statement when I modified the view (viaMS SQL Server Management Studio) I can not execute the statement. I getThe column prefix 'dbo.tbl_5001_NumericAudit' does not match with atable name or alias name used in the query.Upon closer inspection, I found two ON for the inner join, which I dontthink is correct.So, how can the view work, but not the SQL that defines the view?SQL Server 2000, up to date patches:SELECT dbo.tbl_5001_NumericAudit.aEventID,dbo.tbl_5001_NumericAudit.nParentEventID,dbo.tbl_5001_NumericAudit.nUserID,dbo.tbl_5001_NumericAudit.nColumnID,dbo.tbl_5001_NumericAudit.nKeyID,dbo.tbl_5001_NumericAudit.dChangeTime,CAST(dbo.tbl_5001_NumericAudit.vToValue ASnVarchar(512)) AS vToValue, dbo.tbl_5001_NumericAudit.nChangeMode,dbo.tbl_5001_NumericAudit.tChildEventText, CASEWHEN nConstraintType = 3 THEN 5 ELSE tblColumnMain.nDataType END ASnDataType,dbo.tbl_5001_NumericAudit.nID,CAST(dbo.tbl_5001_NumericAudit.vFromValue AS nVarchar(512)) ASvFromValueFROM dbo.tbl_5001_NumericAudit WITH (NOLOCK) LEFT OUTER JOINdbo.tblColumnMain WITH (NoLock) INNER JOIN---- Posters comment: here is the double ON--dbo.tblCustomField WITH (NoLock) ONdbo.tblColumnMain.aColumnID = dbo.tbl_5001_NumericAudit.nColumnID ONdbo.tbl_5001_NumericAudit.nColumnID =dbo.tblCustomField.nColumnID LEFT OUTER JOINdbo.tblConstraint WITH (NOLOCK) ONdbo.tblCustomField.nConstraintID = dbo.tblConstraint.aConstraintID AND(dbo.tblConstraint.nConstraintType = 4 ORdbo.tblConstraint.nConstraintType = 9 ORdbo.tblConstraint.nConstraintType = 3)UNION ALLSELECT aEventID, nParentEventID, nUserID, nColumnID, nKeyID,dChangeTime, CAST(CAST(vToValue AS decimal(19, 6)) AS nVarchar(512)) ASvToValue,nChangeMode, tChildEventText, 5 AS nDataType,nID, CAST(CAST(vFromValue AS decimal(19, 6)) AS nVarchar(512)) ASvFromValueFROM dbo.tbl_5001_FloatAudit WITH (NOLOCK)UNION ALLSELECT aEventID, nParentEventID, nUserID, nColumnID, nKeyID,dChangeTime, CAST(vToValue AS nVarchar(512)) AS vToValue, nChangeMode,tChildEventText, 2 AS nDataType, nID,CAST(vFromValue AS nVarchar(512)) AS vFromValueFROM dbo.tbl_5001_StringAudit WITH (NOLOCK)UNION ALLSELECT aEventID, nParentEventID, nUserID, nColumnID, nKeyID,dChangeTime, CONVERT(nVarchar(512), vToValue, 121) AS vToValue,nChangeMode,tChildEventText, 3 AS nDataType, nID,CONVERT(nVarchar(512), vFromValue, 121) AS vFromValueFROM dbo.tbl_5001_DateAudit WITH (NOLOCK)
A colleague of mine has a view that returns approx 100000 rows in about 60 seconds.
He wants to use the data returned from that view in an OLE DB Source component.
When he selects the view from the drop-down list of available tables then SSIS seems to hang without any data being returned (he waited for about 15 mins).
He then changed the OLE DB Source component to use a SQL statement and the SQL statement was: SELECT * FROM <viewname>
In this instance all the data was returned in approx 60 seconds (as expected).
This makes no sense. One would think that selecting a view from the drop-down and doing a SELECT *... from that view would be exactly the same. Evidently that isn't the case.
I need to create a view that will look at payments made by clients and show me the clients that made payments last year but not this year. tblPayments (ClientID, PmtID, PmtDate, PmtAmt) How would I set the criteria for this?
How do I set up a sql statement to use as a view? I want to use two tables but they are both from different databases. From the Offices database I want to use a table called Office Code and only the Name column The other database is called Library and I want to use a table called Requestors and join it to others. I've set the stored procedure up that I want to use to get most of the data besides the data in the offices database. Create view GetDataview select requestors.officeCode, requestors.Fname + ' ' + requestors.Lname as [Name], Titles.title as Title from requestors join libraryrequest on libraryrequest.requestorid = requestors.requestoridjoin Titles on Titles.Titleid = libraryrequest.Titleidwhere officecode <> 0order by OfficecodeGO How do I add the database offices, table officecodes to this view to use the Name column? I hope this makes sense
I am trying to write a view to retrieve data from two different servers.. create view as select c1 from ser1.database1.dbo.table1 union select 1 from ser2.database2.dbo.table1
First it is not accepting to write it from EM, hence I wrote it from QA, the problem when I am trying to execute it is giving following error
i need to create a view for a table which is residing on another remote server how to do this could u guide me with the process and give me an example.
I have a view which I am supposed to run manually first thing tomorrow morning but I will not be in tomorrow. Is it possible for me to schedule it as a job to run tomorrow.
When I ran this SQL Query, I got an unexpected result.
//Sql Query
Code:
SELECT distinct Year FROM vw_EMD_Options_Pricing WHERE (Year >= '2003') AND (Year <= '2008')
The view is the "vw_EMD_Options_Pricing". The request I got is the year that go from 1981 to 2008. This is for MS-SQL 2000. Why does the SQL Query does that?
I have a database with a view to another table in another database which I created a form out of. I was wondering when if this rule applies "Data Entry" property of the form is set to True. The default value for this property is "False" which means that Access opens the form and shows the existing records. However, if set to True, the Data Entry property of the form specifies that the form will only show a blank record.
The reason I am asking is because you cant see the records after you close the form and reopen it again.
I have 2 databases, db1, db2 in the SQL Server. In db1, I created the view to link one table from db2. When I run it, it is just read-only, I want to edit/insert data in the view. Can we edit/insert the data in the view from db1(which linked table from db2)?
When I write a sql joining more than views how will be executed?. Does SQL Server execute each view and join the result set or interpret into one final sql and execute?
Hi, I have a view in my database which returns: ID Date Area 1010712008F 1020712008F 1030712008F
I should change this view’s output in such a way that for each Id I should have 3 record: IDDateAreaType 1010712008FLO 1010712008FPO 1010712008FSO 1020712008FLO 1020712008FPO 1020712008FSO 1030712008FLO 1030712008FPO 1030712008FSO
Hey Guys, I am trying to get 5 days back date and in my table the following 3 records are there but my query is not pulling. Could you please tell me what is wrong with my query? Thanks
select * from dbo.tblTransaction WHERE Performance_dm >= dateadd(day, datediff(day, 0, getdate()), - 5) AND Performance_dm < dateadd(day, datediff(day, 0, getdate()), -4) order by Performance_dm
These are the records in the table but it is not pulling. How should i pull these records please? 2007-01-28 00:01:00.000 2007-01-28 00:01:00.000 2007-01-28 00:01:00.000
I have tried Books online and have queried this forum for assistance in creating a SQL view and have spent much time trying to solve on my own.
I am working with only one table:
Sales: SOP10200 SOPTYPE ITEMNMBR ITEMDESC QUANTITY 3 Widget special 4 3 Widget special 7 2 Gadget special 1 2 Widget special 12 3 Gadget special 5
I want to create a view that summarizes the data based upon SOPTYPE and ITEMNMBR as follows:
ITEM_NUMBER ITEM_DESC QTY_INVOICED QTY_ORDERED Widget special 11 12 Gadget special 5 1
I am struggling whether to use a select statement or a CASE statement.
I am able to summarize one SOPTYPE as follows: CREATE VIEW [dbo].[Tec_SOP10200_INVOICE_Qty] AS SELECT ITEMNMBR as ITEM_NUMBER, ITEMDESC as ITEM_DESC, SUM(QUANTITY) AS QTY_INVOICED FROM SOP10200 WHERE SOPTYPE = 3 GROUP BY ITEMNMBR, ITEMDESC
But I need more than one soptype in same view
I tried this CASE statement - Create tec_sales_view as SELECT SOP10200.ITEMNMBR as ITEM_NUMBER , SOP10200.ITEMDESC as ITEM_DESC , SOP10200.QTY1 'QTY_INVOICED' , SOP10200.QTY2 'QTY_ORDERED' , (CASE WHEN SOP10200.SOPTYPE = 3 THEN SUM(QUANTITY) QTY1 ELSE 0) (CASE WHEN SOP10200.SOPTYPE = 2 THEN SUM(QUANTITY) QTY2 ELSE 0) END as QTY_Type FROM SOP10200 GROUP BYITEMNMBR, ITEMDESC, QTY1, QTY2
But I get error message doesn't recognize QTY1 (invalid column name) Thank you in advance for any assistance.......
I am doing a monthly report to export to an excel Doc from SQL 2000using views.With my design it works great until it is the first of the month. As thereport alwayschecks from yesterday to the first of the current month. One view is below.The date statement checks between the first of month and current date -1Can anyone help with with this?MikeCREATE VIEW dbo.GUTURASSELECT COUNT(*) AS GUTURFROM dbo.ChevWHERE ([Deal Date] BETWEEN DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0)AND GETDATE() - 1) AND ([N/U] = 'U') AND (([Sales Type] = 'R') or ([SalesType] = '3'))
Hi friend ! i want to ask you about view. will the view data beupdated automatically when we insert or update the physical table orit is updated when we execute the view ?
I have a table tblElectronic that recieves data electronically. Itcontains fields: filenumber, status, date, qualifier and comments. Thequalifier field is either 100 or 101, meaning the file is confirmed orestimated, respectively. I then join tblElectronic to another table(tblFile) in a view. I am having trouble building the view. The fieldQualifier in tblElectronic can have the value 100 or 101.filenumber statusDate Qualifier Comments1111xxxx01/01/01 100Comments1111xxxx01/01/01 101Comments1112xxxy01/01/01 101Comments1113xyxy01/01/01 100 CommentsI want to use the record where qualifier = 100 in my view, except in thecase where 101 is the only qualifier that exists, meaning it has notbeen confirmed yet.So my view should pull the rows:filenumberstatusdate qualifiercomments1111xxxx01/01/01 101Comments1112xxxy01/01/01 101Comments1113xyxy01/01/01 100 CommentsI have tried case statements but to no avail. Here is my view:SELECT tblFile.Filenumber, tblFile.DataofFile, tblElectronic.status,tblElectronic.date, tblElectronic.commentsFROM tblFile inner jointblElectronic on tblFile.filenumber = tblElectronic.filenumberwhere tblElectronic.qualifier = ??????Not sure what to put here, since I want it to be where qualifier = 100unless that doesn't exist, in which case I want it to be where qualifier= 101.Thanks for the help.Rubia*** Sent via Devdex http://www.devdex.com ***Don't just participate in USENET...get rewarded for it!
