Show Menu
Cheatography

SnDO SQL Server 2012 Cheat Sheet by

tugas MABD kelas D oleh MOH. HASAN BASRI

Server­-level Options

 

opt/mi­n/m­ax/def

 

Available Server­-level Options

-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
 

How to Change Server­-level Options

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

Databa­se-­level Options

 

option­s/d­efault

 

Auto options

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 options

CURSOR­_CL­OSE­_ON­_COMMIT
/OFF
CURSOR­_DE­FAULT
/GLOBAL

Database availa­bility options

OFFLINE | ONLINE | EMERGENCY
/ONLINE
READ_ONLY | READ_WRITE
/READ_WRITE
SINGLE­_USER | RESTRI­CTE­D_USER | MULTI_USER
/MULTI_USER

Date Correl­ation Optimi­zation Options

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

External access options

DB_CHA­INING
/OFF
TRUSTW­ORTHY
/OFF

Parame­ter­ization options

PARAME­TER­IZATION
/SIMPLE

Recovery options

RECOVERY
/FULL
PAGE_V­ERIFY
/CHECKSUM

Service Broker options

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

Snapshot isolation options

ALLOW_­SNA­PSH­OT_­ISO­LATION
/OFF
READ_C­OMM­ITT­ED_­SNA­PSHOT
/OFF

SQL Options

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

How to Change Server and Database Options

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

Comments

No comments yet. Add yours below!

Add a Comment

Your Comment

Please enter your name.

    Please enter your email address

      Please enter your Comment.

          Related Cheat Sheets

          SQL Server Cheat Sheet
          SQL Server Danar124 Cheat Sheet
          SQL Server / Rahadiyan Danar A - 5212100124 Cheat Sheet