Views And Sql Server
Jan 8, 2005
Hi,
I've been using stored procedures to do queries including inner joins etc. I have always known about views but don't understand why they are used, what is there purpose, they seem the same as sql queries, is that true?
Is it more effiicient to use views rather than stored procedures to do your queries?
Really I just want to know why I would ever want to use views instead of stored procedures to do my queries?
Thanks in advance.
View 4 Replies
ADVERTISEMENT
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
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
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
Feb 22, 2007
Hello,
to make a report easier I'm developing it using a view of joined views of joined views.
Is there any significant performance penalty as opposed to just having one big select?
Cheers.
View 1 Replies
View Related
Aug 6, 2004
Need a Clarification in SQL Server 2000.
I would like to know if its possible to create a view of a table residing in a SQL server which is not the one in which we r currently working in
Desc:
1) Say i have 2 SQL servers( different Boxes) A & B
I am working in ' B ', can i have a view of a table residing in SQL server 'A' in my system( Server 'B')
2) Leaving out the network uncertainities, will such a setup give any performance issue when the view is for a table that captures daily transactions running into several
thousands of records
3) Is ther any other Alternative to access tables in another SQL sever without going for Views?
Plz find out and reply ASAP.
View 2 Replies
View Related
Aug 30, 2006
I know that stored procedures(sql server) caches stored procedures in memory where it keeps the compiled execution plan in memory, how does it work with the views does sql server store /cache the views. Just wondering Thanks
View 2 Replies
View Related
Dec 12, 2005
Hi there,First thing off, I'd like to know what Views are useful for? I thing it can speeds select query containing multiple join, but I'm not even sure if that's true.Second, assuming that Views speeds thing up for SELECT query, can it be used by NHibernate when restoring object from their persistence store (in this case, SQL 2k5) ? If not, can they be (views) of any use if we use NHibernate?Third, would it be better to create our crystal report using Views to improve performance?Thanks for anyone who participates in this thread! :-)
View 1 Replies
View Related
May 10, 2000
Hi There,
Is it possible to make views which takes Data from Multiple Servers?
NOTE: The view is very complex because it uses FOUR Diff. statements
joined together with UNION clause.
One more Q.
If my Query Analizer Displays following on execution of query.
" Internal Query Processor Error: The query processor could not produce a query plan."
What should be my approach to rectify the problem.
Any help would really be appreciated.
zak
View 1 Replies
View Related
Apr 25, 2000
I have two databases on my Server - database1 and database2.
Database1 has a view on tables on database2 :
create view myview as select * from database2.dbo.tablename
(because all tables in database2 are owned by dbo)
and myview on database1 is owned by dbo.
when a user on database1 - lets say user1 tries to select rows from myview,
we get an error saying that user1 is not a valid user on database2.
it only allows user1 to select rows from myview if i make user1 a user on database2
as well, which is not what i want.
Can someone please point me in the right direction.
i would like to be able to select rows from a view which is based on tables in another database.
thanks
simran
View 5 Replies
View Related
Feb 27, 2006
not sure if this is by default or restricted,
but I can't view the sql server logs using ctp
an error is generated saying the tsql could not be retrieved or something, very long message to post here
is this normal?
TIA
--------------------
keeping it simple...
View 5 Replies
View Related
Apr 3, 2006
Hi,I am working with several tables and views. My goal is to create a viewwith critical reporting data from these tables and views. I havemanaged to get the majority of data but am having difficultly with thefinal step.For a record in the master dataview, add additional record informationby appending data from another view based on the first 2 letters of thedocument number and the document number i.eDocument numberSC11111DN22222SI33333For SC11111 look up data in Sales Credit table/view for doument SC11111and append to that lineFor DN22222 look up data in Delivery Note table/view for documentDN22222and append to that lineFor SI33333 look up data in Sales Invoice table/view for documentSI33333 and append to that lineAny help appreciated.Thank you in advance,Raj
View 1 Replies
View Related
Jul 20, 2005
Be careful when implementing views (from SQL Server 97/2K). SQL Serverstores the metadata on the view at creation (or the last time it wassaved). This means if you have:SELECT * FROM table1it will put all the fields of table1 in the view's metadata. If youthen change table1 and add (for example) another field, this fieldwill not be visible in the view until you open it in design view andclick save (to update it).
View 1 Replies
View Related
Jun 28, 2006
Can i show my view data on a web page is it possible.How can i show the data if it is too large.
View 1 Replies
View Related
Oct 15, 2007
hi all,
i had a view in my project, i am inserting a record in to that view(View contains a identity key for a column), with in stored procedure i am inserting a record i am not passing the identity key value to the insert statement. The record is getting inserted,Based on the identity key(i am getting the identity key value with Scope_Identity()) and with that value i am inserting records into another two tables.In this scenario every thing is working fine.
but now i am trying to place a trigger(instead of insert trigger) on the view, when i placed,it is not inserting record into first table,so i am not able to get the identity value of that record and process failed.
how can this achived, let me know.
View 2 Replies
View Related
May 6, 2005
Hi,
I have two servers that i want to create a SQL RS report on.
On one server there is an HR database with our staff details, on the other server there is a database of assets.
In order to report on the assets assigned to each user i am thinking that i will have to :
1) link the servers
2) create a view in the HR database exposing the fields needed
3) create a view in the assets database exposing the assets information joined to the view from the other server
4) create my reports on the view on the assets server.
is this right or am i barking up the wrong tree?
View 9 Replies
View Related
Jul 2, 2007
Please help.
I created a linked server with Oracle Provider for OLE DB to Oracle Database from SQL Server 2000 SP 4. Afterwards I created Views from this linked server and granted SQL Server users "select" access to the Views.
Unfortunately when they try to access the views the following error message was generated (see the attached image - error.gif).
Can someone please explain to me what is wrong?
Thanks
Victor
View 7 Replies
View Related
May 12, 2008
Thanks in advance in reading this post ! I'm facing a situation in sql server 2000 sp4 with partitioned views.
I have a partition views that joins about 10 tables, in each table there is a check constraint.
For example, if a exec a select count(*) from VIEW where col1 = '20080101' , it goes for the table that has data for '20080101' .
If I exec a select col1,col2,col3,col4 from VIEW where col1='20080101', it goes to all tables and make an index seek.
I want the beaviour of query 1, beause it is just looking on 1 table and not one the 10.
Thanks in advance !
View 3 Replies
View Related
Jul 23, 2005
If column1 in SQL Server column is text: 19980701What is the syntax in the select statement to convert it to a datelike: 07/01/1998Thanks for any helpRbollinger
View 1 Replies
View Related
Jul 20, 2005
Do not trust values returned by materialized views under SQL Serverwithout frequently checking underlying tables!!!I already posted this message under microsoft.public.sqlserver.serverand I'm amazed nobody from Microsoft answered about this problem. Byinserting lots of data into our two main tables for about 30 minutes,we can fail our materialized view that performs a count_big on thosetwo tables.Executing (after of course having stopped inserting rows in our twotables)[color=blue]> SELECT SUM(field1+field2+field3) FROM MatView option(expand views)[/color]DOES NOT RETURN the same value than:[color=blue]> SELECT SUM(field1+field2+field3) FROM MatView with (noexpand)[/color]The second call - using the materialized view - returns a smallernumber (as if counts were lost during our bulk insert)As our data has to be accurate, we cannot use Materialized viewsanymore. This problem does not occur when the amount of data insertedis smaller. Rebuilding the clustered index on the view fixes theproblem; do we have to constantly be rebuilding the index to keep theview synchronize !?!!?!Is there a way to tell that our view is not synchronized? Justcomparing values returned by our view does not work for us as data isconstantly been inserted.System: SQL server 2000 SP3 Enterprise EditionVincent LIDOU
View 6 Replies
View Related
Feb 24, 2006
Hi,
I read that views can't be published with SQL Server 2005 replication. Is this planned for the future? If not what alternatives are there for this?
Simple collecting the data needed in a new table ain't a solution for us (memory consuming). And joining the data on the PPC ain't a good solution either (memory and time consuming). We only want to pull the data.
Greets,
Ivo Klerkx
View 3 Replies
View Related
Jan 30, 2008
SQL 2005 SP2 in Managemrnt Studio...
I have a linked server set-up pointing to an instance that has several databases. In one catalog I have no tables or views being listed for the catalog. So I select a view, add the permissions that should allow the view to show in the list but it does not. And I did refresh and I did even restart management studio.
BTW: I can exec the view using a 4 part name.
Any one have an idea why the view don't show in the list?
Thanks much
chuck
View 5 Replies
View Related
Dec 19, 2006
I have to come up with a SSIS package to tranform data from oracle database to our sql server database. This oracle db is managed by a third party , I have been given 10 views from oracle so I can extarct information I need. How should I design my SSIS package? Do I have to have 10 different data flows or there is an eaier way to run my 10 select statements from these views?
Is there any article that can give me some ideas on how to design SSIS packages to extract information from oracle?
Thanks a lot
View 11 Replies
View Related
Nov 20, 2006
Cordial greetings,
Again i need help with a couple of issues in migrating from Oracle to SQL Server 2005. I need the equivalent sentence in SQL Server of the following sentence:
CREATE OR REPLACE VIEW IBA_MPDATOSGENERALES AS (
SELECT IBA_MPPROCEDXLOC.IDPROCEDENCIALOCALIZACION, IBA_MPREQUERIMIENTOS.APLAZADOREQ, IBA_MPACCIONESXREQ.FECHAFINALAXR, IBA_USUARIOS.NOMBREUSU
FROM IBA_MPPROCEDXLOC, IBA_MPREQUERIMIENTOS, IBA_MPACCIONESXREQ, IBA_USUARIOS
WHERE IBA_MPPROCEDXLOC.IDPROCEDENCIALOCALIZACION = IBA_MPREQUERIMIENTOS.IDPROCEDENCIALOCALIZACION
AND IBA_MPREQUERIMIENTOS.IDREQUERIMIENTO = IBA_MPACCIONESXREQ.IDREQUERIMIENTO(+)
AND IBA_MPACCIONESXREQ.USERNAME = IBA_USUARIOS.USERNAME
AND IBA_MPACCIONESXREQ.TIPOACCIONAXR = 'S'
AND IBA_MPACCIONESXREQ.CERRADAAXR = 'N'
AND NOT IBA_MPACCIONESXREQ.FECHAFINALAXR IS NULL
UNION
SELECT IBA_MPPROCEDXLOC.IDPROCEDENCIALOCALIZACION, IBA_MPREQUERIMIENTOS.APLAZADOREQ, IBA_MPACCIONESXREQ.FECHAFINALAXR, IBA_USUARIOS.NOMBREUSU
FROM IBA_MPPROCEDXLOC, IBA_MPREQUERIMIENTOS, IBA_MPACCIONESXREQ, IBA_USUARIOS
WHERE IBA_MPPROCEDXLOC.IDPROCEDENCIALOCALIZACION = IBA_MPREQUERIMIENTOS.IDPROCEDENCIALOCALIZACION
AND IBA_MPREQUERIMIENTOS.IDREQUERIMIENTO = IBA_MPACCIONESXREQ.IDREQUERIMIENTO(+)
AND IBA_MPACCIONESXREQ.USERNAME = IBA_USUARIOS.USERNAME
AND IBA_MPACCIONESXREQ.TIPOACCIONAXR = 'N'
AND IBA_MPACCIONESXREQ.CERRADAAXR = 'N'
AND NOT IBA_MPACCIONESXREQ.FECHAPROXEJECUCIONAXR IS NULL
UNION
SELECT IBA_MPPROCEDXLOC.IDPROCEDENCIALOCALIZACION, IBA_MPREQUERIMIENTOS.APLAZADOREQ, IBA_MPACCIONESXREQ.FECHAFINALAXR, IBA_USUARIOS.NOMBREUSU
FROM IBA_MPPROCEDXLOC, IBA_MPREQUERIMIENTOS, IBA_MPACCIONESXREQ, IBA_USUARIOS
WHERE IBA_MPPROCEDXLOC.IDPROCEDENCIALOCALIZACION = IBA_MPREQUERIMIENTOS.IDPROCEDENCIALOCALIZACION
AND IBA_MPREQUERIMIENTOS.IDREQUERIMIENTO = IBA_MPACCIONESXREQ.IDREQUERIMIENTO(+)
AND IBA_MPACCIONESXREQ.USERNAME = IBA_USUARIOS.USERNAME
AND (IBA_MPACCIONESXREQ.CERRADAAXR = 'S'
OR (IBA_MPACCIONESXREQ.CERRADAAXR = 'N' AND ((IBA_MPACCIONESXREQ.TIPOACCIONAXR = 'N' AND IBA_MPACCIONESXREQ.FECHAPROXEJECUCIONAXR IS NULL) OR (IBA_MPACCIONESXREQ.TIPOACCIONAXR = 'S' AND IBA_MPACCIONESXREQ.FECHAFINALAXR IS NULL))))
)
I know that (+) is for outer joins (left or right). I also know that UNION may mean FULL OUTER JOIN. but this query? The real query is a lot more complex, well actually there are just more fields needed from more tables but with this exmple is enough. The bottom line is that i need data from various tables, most of them involved in a JOIN clause. Also check out that in SQL Server doesnt exists the REPLACE word or does it?
Thank you in advance,
Fernando Martinez
View 5 Replies
View Related
Jan 25, 2006
I am using the import/export wizard to import all the objects from one database into another.My problem is that any views that use User Defined Functions are failing because the view is being created before the UDF.I have searched for this error, and found the following microsoft article:http://support.microsoft.com/kb/300272/en-usThe article says that I should upgrade to the latest service pack. The problem with this is that I already have the latest service pack When I run the following:SELECT @@VersionI get:Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005 23:18:38 Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4) Could anyone please help me solve this problem.Jag
View 1 Replies
View Related
Sep 14, 2004
I wonder if anyone can help me -
I am trying to write a little application which recompiles any views dependent on a given view.
I have a script which will identify any views containing a given text string, but I now need a way to force SQL Server to refresh its cache info (or recompile) the views.
I have to admit that I am unclear on exactly how the caching works; i have tried using sp_recompile, but this does not seem to do the job.
Does anyone have any ideas?
View 1 Replies
View Related
Feb 21, 2005
hi,
how can i drop views and recreate again?
thinks ...
View 2 Replies
View Related
Apr 22, 2007
Hi,
I'm getting some unexpected behaviour from my SSIS packages when targeting tables that are being referenced by Indexed Views. There's two separate issues:
1. When writing into a pair of tables with a SuperType / SubType relationship concurrently with a pair of SS destinations I'm getting deadlocks between the two. Removing the index on the view that references both of these fixes the problem.
2. Much odder, I'm getting some extremely long waits (10 times longer than the whole package should take to run!) from an SS Destination adapter even when there's no data in the flow for it to bulk insert. Again, removing the indexed views that reference the destination table fixes the problem.
The views aren't mine, and (apparently) are required by the reporting app (BO), so removing them isn't really an option. I realise that there's quite a lot of overhead to maintaining indexed views, but unfortunately, the project is on a very tight timeline, so I can't look into it in as much detail as I'd like.
I was wondering if anyone's experienced any similar issues, or would have any ideas as to where to start investigating?
Thanks a lot
Mark
View 2 Replies
View Related
Jun 21, 2006
Previously in Sql Server 2000, we would be in enterprise manager, you'ddouble click on a view, and a nice little dialog box opened with the t-sqlstatetments, there was also a check sql syntax and apply and cancel buttons.Not exactly query anaylizer, just a quick lightweight dialog box. Is thisfeature still around? Seems like I have to go into the query anaylizer likemode to edit a view now. I am a total newbie to version 2005. Are there anyoptions I can set to make it behave the old way? All feedback isappreciated.TIA,~CK
View 2 Replies
View Related
Apr 13, 2007
I am using SQL Server Management Studio and Visual Studio 2005 and I am a newbie.
Once connected to a remote server and database I am able to use SQL Server Management Studio to modify a table and save it. When I modify a stored procedure or create a new one and attempt to save it I get my local dialog box to save to my system with a new name €œSQLQuery6.sql.€? How do I cause the procedure to be saved to the remote database? If anyone can help this or where I can find it I will appreciate it. Thanks in advance.
View 4 Replies
View Related
Oct 10, 2006
How to get the list of catalog views sys.* (sys.objects, sys.columns ....) in sql server 2005
select * from sys.objects where objectproperty(object_id, 'IsSystemTable') = 1
did not return the list as I've expected
thanks
View 4 Replies
View Related
Feb 29, 2008
I think I read somewhere that sql server 2005 Compact Edition (3.1) does not support views. Does anyone know if views were added to 3.5?
View 3 Replies
View Related
Sep 12, 2006
We are in the process of supporting two databases (Oracle 10g, SQL Server 2005) for our application.
I want to know what is the equivalent Tables/Views in SQL Server for the Oracle System tables dba_tab_comments, dba_tab_cols
Thanks in advance
View 4 Replies
View Related
