SnDO SQL Server 2012 Cheat Sheet by abas060

-access check cache bucket count /0/16384/0
-access check cache quota/0/214­748­3647/0
-ad hoc distri­buted queries/0/1/0
-affinity I/O mask /-2147­483­648­/21­474­8647/0
-affinity64 I/O mask /-2147­483­648­/21­474­8647/0
-Agent XPs/0/1/0
-allow updates /0/1/0
-backup compre­ssion default/0/1/0
-blocked process threshold/0/86400/0
-c2 audit mode /0/1/0
-clr enabled/0/1/0
-common criteria compliance enabled /0/1/0
-contained database authen­tic­ation0/-/0
-cost threshold for parall­elism/0/32767/5
-cross db ownership chaining/0/1/0
-cursor threshold/-1/21­474­836­47/-1
-Database Mail XPs /0/1/0
-default full-text language/0/214­748­364­7/1033
-default language/0/9999/0
-default trace enabled/0/1/1
-disallow results from triggers/0/1/0
-EKM provider enabled/0/1/0
-filest­rea­m_a­cce­ss_­level/0/2/0
-fill factor /0/100/0
-ft crawl bandwidth (max)/0/327­67/100
-ft crawl bandwidth (min)/0/32767/0
-ft notify bandwidth (max)/0/327­67/100
-ft notify bandwidth (min)/0/32767/0
-index create memory/704/2­147­483­647/0
-in-doubt xact resolution/0/2/0
-lightw­eight pooling/0/1/0
-locks/5000/­214­783­647/0
-max degree of parall­elism/0/32767/0
-max full-text crawl range /0/256/4
-max server memory/16/21­474­83647
-max text repl size/0/214­748­364­7/65536
-max worker threads /128/3­2767/0
-media retention /0/365/0
-min memory per query /512/2­147­483­647­/1024
-min server memory/0/214­748­3647/0
-nested triggers/0/1/1
-network packet size /512/3­276­7/4096
-Ole Automation Procedures /0/1/0
-open objects /0/214­748­3647/0
-optimize for ad hoc workloads /0/1/0
-PH_timeout /0/3600/60
-precompute rank /0/1/0
-priority boost/0/1/0
-query governor cost limit/0/214­748­3647/0
-query wait/-1/21­474­836­47/-1
-recovery interval/0/32767/0
-remote access /0/1/1
-remote admin connec­tions/0/1/0
-remote login timeout/0/214­748­3647/10
-remote proc trans/0/1/0
-remote query timeout/0/214­748­364­7/600
-Replic­ation XPs Option /0/1/0
-scan for startup procs/0/1/0
-server trigger recursion/0/1/1
-set working set size /0/1/0
-show advanced options/0/1/0
-SMO and DMO XPs /0/1/1
-transform noise words /0/1/0
-two digit year cutoff /1753/­999­9/2049
-user connec­tions /0/32767/0
-user options/0/32767/0
-xp_cmd­shell /0/1/0

GUI (some options)

sp_Con­figure

for basic options

sp_Con­figure

for advanced options
First, turn on Show Advanced Options
Then list them
Then activate desired option(s)
RECONF­IGURE
Notable: authen­tic­ation mode can’t be switched this way but xp_reg­write can be used

AUTO_CLOSE

/True

AUTO_C­REA­TE_­STA­TISTICS

/True

AUTO_U­PDA­TE_­STA­TISTICS

/True

AUTO_S­HRINK 

/False

Auto_U­pda­te_­Sta­tis­tic­s_A­syn­chr­onously

/False

CURSOR­_CL­OSE­_ON­_COMMIT

/OFF

CURSOR­_DE­FAULT

/GLOBAL

OFFLINE | ONLINE | EMERGENCY

/ONLINE

READ_ONLY | READ_WRITE

/READ_WRITE

SINGLE­_USER | RESTRI­CTE­D_USER | MULTI_USER

/MULTI_USER

DATE_C­ORR­ELA­TIO­N_O­PTI­MIZ­ATION

/OFF

DB_CHA­INING

/OFF

TRUSTW­ORTHY

/OFF

PARAME­TER­IZATION

/SIMPLE

RECOVERY

/FULL

PAGE_V­ERIFY

/CHECKSUM

ENABLE­_BROKER | DISABL­E_B­ROKER | NEW_BROKER | ERROR_­BRO­KER­_CO­NVE­RSA­TIONS

/ENABLE­_BROKER

ALLOW_­SNA­PSH­OT_­ISO­LATION

/OFF

READ_C­OMM­ITT­ED_­SNA­PSHOT

/OFF

READ_C­OMM­ITT­ED_­SNA­PSHOT

/OFF

ANSI_NULLS 

/OFF

ANSI_P­ADDING

/OFF

ANSI_W­ARNINGS

/OFF

ARITHABORT

/OFF

CONCAT­_NU­LL_­YIE­LDS­_NULL

/OFF

QUOTED­_ID­ENT­IFIER

/OFF

NUMERI­C_R­OUN­DABORT 

/OFF

RECURS­IVE­_TR­IGGERS 

/OFF

Code
ALTER DATABASE DBx SET ____
GUI
Can’t change every option