Views or not? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Views or not?

Author

Topic

karuna
Aged Yak Warrior

582 Posts

Posted - 2005-04-13 : 15:46:36

I import some datas in dts to a table, this is a temporarytable for me. After importing tables I check for valid records and mark them in a column. I created a view for this table where it shows only valid records. After this I populate other tables using this view.If I make any changes in my temptable, my queries behave very wierd doesnt give the desired output. Looks like my view is not aware of the changes in the temp table.

At that point I have delete the view and recreate it, then things works fine.Ofcourse this erratic behaviour is happening today only.

So should I still use the view or I write one more select statement which fetches my valid records from my temp table?

Karunakaran
___________
NOTHING IS IMPOSSIBLE, IMPOSSIBLE JUST TAKES LONGER...

X002548
Not Just a Number

15586 Posts

Posted - 2005-04-13 : 15:51:50

It would be helpful if we saw the DDL...

And when you say a temp table do mean #temp or ##Temp or a permanent temp table

Brett

8-)

EDIT: Well it can't be a local temp table

CREATE TABLE #myTemp99(Col1 int, Col2 int)
GO

CREATE VIEW myView AS SELECT Col2, Col1 FROM #myTemp99
GO

DROP TABLE #myTemp99
GO

karuna
Aged Yak Warrior

582 Posts

Posted - 2005-04-13 : 15:58:08

Its a permanent temp table or rather call it as a stagging table?. I truncate that table only at the end of the whole dts.
I cannot post a full structure of the table here...:( Client Issues...
I create the view as
create view tempview as select * from mytemptable where validrecord = 1

Karunakaran
___________
NOTHING IS IMPOSSIBLE, IMPOSSIBLE JUST TAKES LONGER...

X002548
Not Just a Number

15586 Posts

Posted - 2005-04-13 : 16:02:19

First you should always list out the columns in your view. DO NOT use SELECT *

That's what views are for, to isoloate changes to the database.

If you want to see the new changes I would suggest you drop and recreate the view...using column names....when you change the staging table

It's just good practice, plus it may be that the view is not seeing the changes...

Brett

8-)

X002548
Not Just a Number

15586 Posts

Posted - 2005-04-13 : 16:06:29

Yup, that's exactly it...


USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myTable99(Col1 int IDENTITY(1,1), Col2 Char(1))
GO

INSERT INTO myTable99(Col2)
SELECT 'a' UNION SELECT 'b' UNION ALL SELECT 'c'
GO

CREATE VIEW myView99 AS SELECT * FROM myTable99
GO

SELECT * FROM myView99
GO

TRUNCATE TABLE myTable99
GO

ALTER TABLE myTable99 ADD Col3 datetime DEFAULT(GetDate())
GO

INSERT INTO myTable99(Col2)
SELECT 'a' UNION SELECT 'b' UNION ALL SELECT 'c'
GO

SELECT * FROM myTable99

SELECT * FROM myView99
GO

SET NOCOUNT ON
DROP VIEW myView99
DROP VIEW myTable99
GO

Brett

8-)

karuna
Aged Yak Warrior

582 Posts

Posted - 2005-04-13 : 16:24:34

Brett, Thanks for the info on views.

Regarding the other post
http://sqlteam.com/forums/topic.asp?TOPIC_ID=48368: I too understand it will be a guessing game to say something when there is no data or any idea how the table structure is...Atleast I'll try to post part of structure and datas in reference to the problem.

Karunakaran
___________
NOTHING IS IMPOSSIBLE, IMPOSSIBLE JUST TAKES LONGER...

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-04-13 : 17:45:31

If You create Your views WITH SCHEMABINDING You will get a warning when You change table structures.
As for the question, is it not mostly asking whether it's better to create a view or to write an
extra line of sql.
I prefer the latter, unless the line will appear in many places.

rockmoose

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources