Indexing Views Derived from 3 Tables

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

Transact-SQL (2008)

Indexing Views Derived from 3 Tables

Author

Topic

tomrippity
Starting Member

37 Posts

Posted - 2012-02-03 : 10:35:53

I have a view that is created by joining 3 seperate tables to one another.

I would like to be able to index this view, as it "can" be a resource hog, which isn't a problem now, but will as our record sets become larger.

Is there a way to create a unique column in the view so that I can use it for indexing? Currently, because there is no unique, I can't index so I'm stuck.

Below is the code I am using to create my view right now... Its a very basic join, which gives me exactly what I want.




SELECT     'cccc66' AS bgcolor, '#cccc66' AS barcolor, county_district, 'MISSOURI' AS district_name, map_index, Accountable, Reportable, '' AS type, '' AS category, content_area, grade_level, year
FROM         dbo.Achievement_Level
WHERE     summary_level = 'State'

UNION

SELECT DISTINCT '#6699cc' AS bgcolor, '' AS barcolor, a.COUNTY_DISTRICT, b.DISTNAME, a.MAP_INDEX, a.ACCOUNTABLE, a.REPORTABLE, a.TYPE, a.CATEGORY, a.content_area,  a.grade_level, a.year
FROM dbo.District_Disaggregate AS a INNER JOIN
dbo.District_Summary AS b ON a.COUNTY_DISTRICT = b.COUNTY_DISTRICT

UNION

SELECT     '&H6699cc' AS bgcolor, '' AS barcolor, County_District, District_Name, MAP_Index, Accountable, Reportable, 'Total' AS Type, 'Total' AS Category, content_area, grade_level, year
FROM  achievement_level
WHERE     summary_level = 'District' AND Type = 'Total' AND Category = 'Total'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-03 : 11:00:20

use ROW_NUMBER function or NEWID() function to generate one.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

tomrippity
Starting Member

37 Posts

Posted - 2012-02-03 : 11:05:40

quote:
Originally posted by visakh16

use ROW_NUMBER function or NEWID() function to generate one.

The problem I am having is that I am not allowed to use Unionsto create the view if I want to index. Also, I've read the NEWID() suggestion on some of the other forums, and I don't believe this will resolve the issue, assuming I could get past the "UNIONS" road block.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-03 : 11:12:30

why you think it wont serve the purpose? It will generate a unique number which can be used as your index

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Kristen
Test

22859 Posts

Posted - 2012-02-03 : 11:41:18

Include the PKs from the underlying table(s)? Probably the child-most table will do (if it will be unique, if not include the PKs from sufficient other table(s) to remove any ambiguity)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-03 : 11:56:05

only issue is you need to make sure joins will still ensure pk combination from tables as unique ie no 1- to-many relationships

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Kristen
Test

22859 Posts

Posted - 2012-02-03 : 12:01:02

At worst combination of all tables' PKs will be unique - but anywhere that is a strict parent-one-child-table relationship then the PKs from the Parent can be left out.

Actually won't the PKs for all tables that are "leaf" tables in the JOINs be unique?

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2012-02-03 : 12:02:20

how many rows are we talking about here? also could you dump the value of this view ahead of time into a flat table with it's own index and have the view use that flat table?
if this is for reporting purposes a dimension like table might be very beneficial imho

If you don't have the passion to help people, you have no passion

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-03 : 12:10:53

quote:
Originally posted by Kristen

At worst combination of all tables' PKs will be unique - but anywhere that is a strict parent-one-child-table relationship then the PKs from the Parent can be left out.

Actually won't the PKs for all tables that are "leaf" tables in the JOINs be unique?

oh ok..i misunderstood
yeah...if all columns of even child table pk are included that would be fine, but depends on number of tables involved in join also (here its 3 so i dont think that will cause an issue)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Kristen
Test

22859 Posts

Posted - 2012-02-03 : 13:10:48

Yeah, that was my thinking. Hopefully the three tables don't have multi-part keys of a dozen columns each !!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources