Monday, November 27, 2006

Do You Use Partitioned Views Or Indexed Views In SQL Server 2000

I am in the process of interviewing people for a position at work. The people I have interviewed so far have between 8 and 10 years SQL experience. I noticed one thing; none of these people knew what partition views or horizontal partitioning is or what is involved with creating an index view (the SET options, SCHEMABINDING etc etc). I must say that I was surprised that people who worked with SQL Server since 1998 didn’t know about isolation levels, audit trails, difference between a unique constraint and primary key and the list goes on

So here is my question to you: Do you use partitioned views or indexed views?

I must admit that I have created indexed views only twice on a production box. Partitioned views I am using a lot more since the table that we had grew to 180 million rows and you can query it on a combination of columns. Creating partitioned views is not a big deal but it involves some maintenance if you add years or whatever your partitioned column is. Then you have to create does tables and add them to the view. Luckily SQL Server 2005 has partitioned functions and this makes it a lot easier

So is this an unreasonable question? It’s not like I asked the difference between a conversion and a cycle deadlock is it?
at

4 comments:

  1. Anonymous10:55 PM

    I think it's a perfectly acceptable question to ask a candidate. Personally, I didn't use indexed views much in 2000 (but I did from time to time), and I can't remember ever using a partitioned view. But I knew exactly what they were and how to create them. And in 2005 projects I've been using indexed views a lot more -- performance is much improved... so candidates SHOULD know about them, IMO.

    ReplyDelete
  2. Anonymous2:42 AM

    I agree with Adam.
    But Indexes views and partitioned views are used best with very large tables. and how many databases have a table with millions and millions of rows?

    I haven't seen a db larger than 10 gb.

    So I'm not surprised about your findins.
    However knowing what they're for and how to write them are 2 different things.

    I've written a lof of sql functions and i still have to go to BOL to look up the create function syntax. :))

    So yes the candidates should know about them and what they're for (no excuse there) but i'd wouldn't be too harsh on that one.

    ReplyDelete
  3. Denis3:49 AM

    Adam,

    thanks I agree with you. The only reason I asked for partitioned views/horizontal partitioning is because we are using that. There is no point in asking candidates for some obscure things that are not in use anyway

    Denis

    ReplyDelete
  4. Denis3:52 AM

    Mladen,

    Thanks for your input. Our DB is not a VLDB but it is well over 100GB. I think create function is probably also something that I would have to look up however I usually copy an old function change the name, variables and voila I am done

    Denis

    ReplyDelete