LOGS
Checkpoints occur to write any committed transactions in the log (dirty data) to the physical data file. On average takes 1 second.
These happen rarely:
shutdown
db option change
startup
when 'Simple recovery' option is used and log becomes 70% full
If 'TRUNCATE ON CHECKPOINT' DB option is active and a checkpoint command is given the log truncates which is just a bit simpler than truncating it yourself - good for dev env.
Simple recovery mode is fine for a datawarehouse as data can be loaded again - generally not OLTPish where no work can be lost.
Simple recovery mode will NOT backup the log at all.
.TRN files are backups of the transaction log activity - .LDF is the whole transaction log file.
TRN files can be set to be created at regular intervals - 1 hour?
FORM
3rd normal form is optimal - 4th and 5th are largely academic.
MAXDOP
MAXimum Degree Of Parallelism
GO
Not actually a T-SQL command but is recognised by command utils to submit batches, which gives them their own execution plan.
ERRORS
SYSMESSAGES table contains all the predefined and manually added (sp_addmessage) messages for the db.
RAISERROR can be given a message string (ad-hoc error) or a messageID (which references an existing message in sysmessages). If a string is given then the default messageID of 5000 is used.
Severity of an error:
1-10 Informational, exe not terminated, not actual error
1-16 Informational, exe terminated
17 - sql server has run out of resources
18-19 need admin attention - show up in the eventlog
20 + - conn closed, server fubar
SPROCS
WITH RECOMPILE option of EXEC recompiles the exe plan - important as exe plan is only ever created on first execution.
Debugging done with visual studio - nav server explorer then choose step into sproc option.
exec SP_EXECUTESQL - Use this as opposed to exec (@sql) as the parameters will be identified and query plan used for future executions from the query cache - as opposed to exec(@sql) which will think diff parameters mean it is a whole new query and not get the queryplan from the cache.
TRANSACTIONS
SAVE TRAN will create a named bookmark which you can choose to rollback to later on.`
SQL Server runs on port 1433