Canonical Answers

Question

After discovering the pot of gold that is ServerFault's What are the canonical answers we've discovered over the years?, I'm inclined to think dba.se could do with the same.

If agreeable to moderators and contributors alike, please edit your nominations for canonical question/answer pairs into the community-wiki post below.

Answer 1

SQL Server

• What's the difference between a temp table and table variable in SQL Server?
• What's the difference between a CTE and a Temp Table?
• Why use both TRUNCATE and DROP?
• Does restarting SQL Server speed it up?
• Q: Should I use NOLOCK?
  • Is NOLOCK always bad?
  • Justify NOT using (nolock) hint in every query
• Why Does the Transaction Log Keep Growing or Run Out of Space?
• How do I attach a database in SQL Server?
• How Do I Troubleshoot Login Failed Errors In SQL Server?
• Why is my query suddenly slower than it was yesterday?
• Retrieving n rows per group
• Guid vs INT - Which is better as a primary key?
• SQL server databases stuck in restoring state
• MAXDOP setting algorithm for SQL Server
• How do I get back some deleted records?
• What event information can I get by default from SQL Server?
• Reducing Key Lookups
• Methods of speeding up a huge DELETE FROM <table> with no clauses
• Can I install SQL Server Management Studio locally?
• SQL Server Agent Jobs and Availability Groups
• Does a database backup include data from the start of the backup, or the end, or somewhere in the middle?
• Why does my query run fast in Environment A, but slow in Environment B?
• Unable to connect to remote SQL Server instance after thorough troubleshooting
• Should the filtering column(s) always be in the keys / includes?
• Pivot rows into columns
• Add a new sysadmin account when no sysadmin account exists
• Security or Performance Risks using SQL CLR
• Slow Query Store Queries and Never Ending Query Store Search
• Best way to migrate a huge SQL Server database with low downtime over network
• How do you document your databases?

SQL Queries

• How do I get the rows from a table in the order they were inserted? (need canonical answer)
• Q: What order will my results come back in? A: Without an explicit ORDER BY, there is no guaranteed order. See these similar questions about:
  • Oracle: Does an IOT guarantee order in a select?
  • MySQL: What is the default order of records for a SELECT statement in MySQL?
  • SQL Server: Why is SSMS inserting new rows at the top of a table not the bottom?
  • See also: In SQL Server, can I guarantee an order without an explicit ORDER BY clause when an index seek is forced on a table with only a clustered index?
• What is the difference between an INNER JOIN and an OUTER JOIN ?
• Why are numbers tables "invaluable"?
• Help me write this query in SQL

Database Design

• Why shouldn't we allow NULLs?
• Is using multiple foreign keys separated by commas wrong, and if so, why?
• How do I design tables for a type/subtype case?
• How do I drop all constraints from all tables?

Database Technologies

• What are the differences between NoSQL and a traditional RDBMS?
• Which database could handle storage of billions/trillions of records?

General

• How could DBAs be more 'programmer friendly'?
• Why is Database Administration so hard?
• What are the arguments against or for putting application logic in the database layer?
• Should binary files be stored in the database?
• What is the difference between a "record" and a "row" in SQL Server?

help for question writers

• https://dba.blogoverflow.com/2012/06/help-us-help-you/
• Minimal, Complete, and Verifiable Example

MySQL

• MySQL - varchar length and performance
• restore table from .frm and .ibd file?
• How to pivot rows into columns in MySQL