Passed 70-229: SQL Server Design

2 more exams to go for my MCAD & MCSD.NET

I passed the one for SQL 7.0, but I haven't recertified. What were some of the hardest questions?

ko0, good luck

What were some of the hardest questions?Using Indexed Views and schema binding. Wasn't really familiar with that. You get a few questions about reading XML docs into a table and vice versa. Lots of questions about deciding between views and stored procs, and a lot of question about clustered and nonclustered indexes. If you are familiar with SQL books online, you can find everything you need for the exam in those docs.

awesome man. almost there.

It figures they'd stick a bunch of XML stuff into the SQL 2000 test.

I hate the index selection questions. I can never remember the difference between clustered and non-clustered and why it makes a difference. In all of my years as a DBA, I've never had to use that knowledge.

Well, it is pretty important. Here's the easiest way to explain. Clustered Index is the index on a table that determines the actual physical order of records in the table. That being said, there can only be one Clustered index. It is best to set a clustered index on something that can be ordered like integers, varchar fields like name, or datetimes. Best IMHO is using an int as an identity field as a clustered index. A bad way to use a clustered index is on GUID.

Nonclustered is when you have an index of pointers to locations of the actual physical data. The indexes are stored separate from the data. You can have many NONclustered indexes on a table.

Think of a phone book how the physical order is by lastname, firstname - that's like a clustered index. Think of an index in the back of a book that says SUBJECT Page 99, that's like a nonclustered index.

Make sense?

If not, SQL Server has the index tuning wizard which can figure out the best places to put index for you so you don't even have to think about it. ;-)