I saw "clustered" and breezed right past "primary." Constraints have to be dropped and re-added, which brings FKs and NC indexes into it as well. Messy.

Why BCHR doesn't normally matter: https://www.simple-talk.com/sql/database-administration/great-sql-server-debates-buffer-cache-hit-ratio/%5B/url%5D

As for PLE drops, you can trace for queries running when the PLE bottoms out to figure what is happening in the database when it...

Like how to move them? Lookup CREATE INDEX, and specifically the DROP_EXISTING and ON [filegroup] options.

In terms of being able to replicate data between two systems, nothing comes to mind. Why do you ask?

Schemas do not own things, but I know what you mean.

In that case you can look into certificate signing or EXECUTE AS. I would go for certificate signing, it's a...

But what is the XQuery '.' literal actually doing? Can I *assume* that since an empty string is sent to FOR XML PATH('') no root node name is created, and...

SQL_Surfer (3/8/2013)

---

I have a stored proc that gets executed by SQL login which has execute access to the schema owned by stored proc.

Inside the stored proc, it has insert statement...

a.k.a. a "Hobbit" 😀

Your intuition was correct. You cannot declare variables inside parentheses where only a derived table should exist. You also cannot use WITH to begin a CTE inside parenethese where a...

Ha, I still do not know the backstory on that one but it sounds funny. Building on that, maybe DBCC MIDDLE_EARTH since it involves a hobt_id?

The "hangs infinitely" could be abother process blocking your proc. We can't know from the information presented. Have you looked at what else is running on the system when it...

bummer

kpwimberger (3/8/2013)

---

Now I just need to pull it all apart a few times to see if I can understand all the parts. I hate applying a fix I don't fully...

GilaMonster (3/8/2013)

---

opc.three (3/8/2013)

---

You know I am curious about these...

It looks like your code got truncated. It will not parse (missing END?) and also does not contain the original statement you were asking about beginning with SELECT * INTo...