Dave Hoogendoorn (4/20/2009)

---

Maxdop unfortunately did not give any major improvements.

Regarding the validity of the test;I agree. However. Before the issues started, the queries were much, much faster then those 2...

If you don't use the "constraint" keyword, that command works.

create table #T (ID int);

alter table #T add primary key (ID);

Just tested that and it worked.

You need to use the nodes function before the value function. Try this, see if you can what you want from there:

select x.y.query('.').value('.[1]','int')

from @depxml.nodes('(DEPARTMENT/EMPLOYEES/EMPID)') x(y)

Look up the nodes function...

Dave Hoogendoorn (4/20/2009)

---

The query takes a long time to execute. So after two minutes I stop the query to see how many records are retrieved. The more records retrieved in...

Optimization based on assumptions and guesses is far too common.

One dev I worked with thought that table variables were THE way to optimize stored procs. Took a proc that...

UniqueIdentifier will work for that. Those are GUIDs.

Matt Whitfield (4/20/2009)

---

GSquared (4/20/2009)

---

I'm sorry that you don't know how to code well in SQL. I recommend, however, that instead of justifying it as "I just get the job...

Tempdb has an initial size of 48 Gig on a drive that only has 50 Gig of available space? If I'm misreading that, that's one thing, but if I'm...

Not sure what your "rowcounts after 2 minutes" is supposed to measure. What are you using to measure performance and what metrics are you keeping?

One possibility for reduced performance...

davidandrews13 (4/20/2009)

---

Carolyn Richardson (4/20/2009)

---

It's 3pm on a Friday, your production OLTP environment is down, every hour down costs the company one million dollars. People are phoning constantly, folks are rushing...

Even if applications are rewritten, the amount and type of activity in the table will probably not change much, since users will be doing the same things with the new...

I'd use a trace to find the most common actions taken on that table, selects, updates, inserts and/or deletes, in the production database.

That'll tell you straight away whether there are...

It's a good question and definitely needs to be considered. Not just scripts, but the forum posts are IP as well. SSC needs to have rights on those,...

GilaMonster (4/20/2009)

---

Now that's one I haven't...

Nicholas Cain (4/20/2009)

---

The question is more about how you handle a pressure situation than the actual details. If you can handle that being thrown at you in an interview and...