rev.one technologies

{ Powered By .Net }

Indexing SQL Server views

I have a base table with customer data. I needed to create two columns of data that were derived from the base table. The only way I could do it was to create a view. I thought, what a great opportunity to use a new SQL Server 2000 view and indexes. So, I created the view, and started to create the indexs, but was immediatly given an error. The problem? I had a dateadd function in one of the new columns. That was a big NO NO! There are a lot of prohibitors when it comes to creating a view and trying to index it. In fact, I can't really see any way to index a view in SQL Server 2000 that isn't almost a mirror of the table.

I found an interesting article on indexing views. Take a look, it was an eye opener.

http://www.sqlteam.com/item.asp?ItemID=1015

--Thom

Posted: Feb 11 2003, 06:26 PM by blog | with 5 comment(s)

Comments

Phil Scott said:

DATEADD should be allowed because it is a deterministic function. You sure you don't mean GETDATE?
# February 11, 2003 12:57 PM

Phil Scott said:

If I used indexed views it is usually when I need to do multiple joins. And you can get away with most functions, but GETDATE is the killer. BOL has a big list Deterministic and Nondeterministic Functions if you do a search for, well, "Deterministic and Nondeterministic Functions." Most of them are things like USER_NAME() or other functions I don't use.

The real kicker for me and indexed joins is that you need enterprise edition for them. I don't need distributed partition views (another cool thing to look at w/ 2k), or any of the other "enterprise stuff," but indexed views would definitely help some of the data I have. Sigh...
# February 11, 2003 1:01 PM

Thom Allen said:

Phil, your right, I meant GETDATE. In any event, I am sure I could figure out a better way to do what I want, it was just a little frustrating at first.
# February 11, 2003 1:43 PM

Phil Scott said:

I teach some SQL Server classes, and I've made a nice little view that will conform to their giant list of specifications and connection options, and then I'll create my little clustered index. And the thing won't use it. I've know learned to check to see if the server is enterprise or not. It will let you create the index, but it just won't let you use the index. Pretty messed up, huh?

Oh, and you might try doing an indexed view on everything that DOESN'T need getdate, and build an UDF or another view on top of that view that incorporates GETDATE.
# February 11, 2003 1:57 PM

Thom Allen said:

That is a great idea. Thanks for the info.
# February 11, 2003 4:54 PM
Leave a Comment

(required) 

(required) 

(optional)

(required)