You don't need to create a diagram, but you can if you want to visualize what was created on the database. If you want to use the GUI to check...

I thought it was fairly expected and common to put indexes on foreign keys (depending on needs of the queries used, etc., of course). I've never seen any documentation from...

There's really not a difference between a query and a procedure. A procedure is just a query stored in the database. Queries can be parameterized just like queries. In fact,...

Are the servers the same? Do they have the same amount of memory and the same size and number of processors? Like Gail already said, what do the execution plans...

Good point on schemas. Although there, again, is a bunch of behavior that simply using it to group tables by function without also taking advantage of the security & execution...

One thing to consider as well is that sprocs are vendor specific and require rewrites if you move from your current database platform, while there may be less changes if...

Nice troll bait... I'm a little hungry...

Anyone advocating stance within the development and technicals sphere that says ALWAYS or NEVER is usually wrong. So, saying "ALWAYS use stored procedures" is...

Storing data into different operational areas doesn't add any actual benefits unless those different operational areas also have different loads, different stresses, needs to be backed up at different times......

Yes, you absolutely can. It's a pretty common design method. The one place almost everyone will agree that something like this applies is in a many-to-many relationship. There are other...

What I meant was, automated processes, such as a checkpoint, can pop up in the middle of a query and affect the overall performance (not by much, I'll grant you)...

It really depends on the system you're working on. Most of the systems I have experience with have a pretty common set of data in cache the majority of the...

Key word and tricky phrase in the question is "similar"

Variations in memory, cpu, disk I/O, cache, cache size, statistics, index fragmentation, yada, yada, yada, will result in differences in execution...

For testing, and for testing only, don't do this in Production, I'll run DBCC FREEPROCCACHE to clean out all the procedures and force recompiles. Then try running CHECKPOINT followed by...

You can only have one NULL value in those columns with the constraint.

Just to add a tiny wrinkle to what everyone else said... I've frequently found situations where the foreign key was the best candidate for the clustered index. It was the...