Index View problem for sub query / derived table

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 2005 Forums

Transact-SQL (2005)

Index View problem for sub query / derived table

Author

Topic

avijit_mca
Posting Yak Master

109 Posts

Posted - 2010-03-31 : 05:15:36

Hi,
I want to create index view.

Problems:
View contain sub query.so index creation not happining.

Again to solve this one .. i hv used derived table instead of sub query.
still this is not working.
Any alternative method.

Regards,
avijit

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-03-31 : 05:26:30

Post the query.

There are 10 types of people in the world, those that understand binary, and those that don't.

avijit_mca
Posting Yak Master

109 Posts

Posted - 2010-03-31 : 05:47:55

with Derive table

create VIEW [dbo].[v1]
WITH SCHEMABINDING
AS
SELECT nvarchar1, nvarchar2, nvarchar3 AS Name, nvarchar4 AS Add1, nvarchar5 AS city, nvarchar6 AS add2, nvarchar7 AS email, nvarchar8 AS website,
nvarchar9 AS ownername, nvarchar10 AS ownername2, nvarchar11, nvarchar12, nvarchar13, nvarchar14, nvarchar15, nvarchar16, nvarchar17,
nvarchar18, nvarchar19, nvarchar20 AS T_id, nvarchar21 AS P_id, nvarchar22, float1, float2, float3
FROM dbo.AllUserData
,
(SELECT tp_ID
FROM dbo.AllLists
WHERE (tp_Title = 'Registration Detail') ) list
where

tp_ListId=list.tp_ID

with Subquery

CREATE VIEW [dbo].[AccessingBody]
WITH SCHEMABINDING
AS
SELECT nvarchar1, nvarchar2, nvarchar3 AS Name, nvarchar4 AS Add1, nvarchar5 AS city, nvarchar6 AS add2, nvarchar7 AS email, nvarchar8 AS website,
nvarchar9 AS ownername, nvarchar10 AS ownername2, nvarchar11, nvarchar12, nvarchar13, nvarchar14, nvarchar15, nvarchar16, nvarchar17,
nvarchar18, nvarchar19, nvarchar20 AS T_id, nvarchar21 AS P_id, nvarchar22, float1, float2, float3
FROM dbo.AllUserData
WHERE (tp_ListId =
(SELECT tp_ID
FROM dbo.AllLists
WHERE (tp_Title = 'Registration Detail')))

Regards,
avijit

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-03-31 : 05:58:59

Try this:


SELECT nvarchar1, nvarchar2, nvarchar3 AS Name, nvarchar4 AS Add1, nvarchar5 AS city, nvarchar6 AS add2, nvarchar7 AS email, nvarchar8 AS website,
nvarchar9 AS ownername, nvarchar10 AS ownername2, nvarchar11, nvarchar12, nvarchar13, nvarchar14, nvarchar15, nvarchar16, nvarchar17,
nvarchar18, nvarchar19, nvarchar20 AS T_id, nvarchar21 AS P_id, nvarchar22, float1, float2, float3
FROM dbo.AllUserData a
INNER JOIN dbo.AllLists list
	ON a.tp_ListId=list.tp_ID
WHERE list.tp_Title = 'Registration Detail'

There are 10 types of people in the world, those that understand binary, and those that don't.

avijit_mca
Posting Yak Master

109 Posts

Posted - 2010-03-31 : 07:02:34

thats real good idea.

But could u pls tell me which query will be the best performance wise
& why?

Regards,
avijit

Kristen
Test

22859 Posts

Posted - 2010-04-01 : 05:59:45

Duplicate question of: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=142358

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources