Show Menu
Cheatography
  1. Home >
  2. Programming >

SQL Server - Chairul Huda 5212100127 Cheat Sheet by

MABD Cheat Sheet

Server Level Options

option­/mi­n/m­ax/def

Config­uration Option Table

Option
Minimum Value
Maximum Value
Default
access check cache bucket count (A)
0
16384
0
access check cache quota (A)
0
2147483647
0
ad hoc distri­buted queries (A)
0
1
0
affinity I/O mask (A, RR)
-21474­83648
2147483647
0
affinity64 I/O mask (A, only available on 64-bit version of SQL Server)
-21474­83648
2147483647
0
affinity mask (A)
-21474­83648
2147483647
0
affinity64 mask (A, RR), only available on 64-bit version of SQL Server
-21474­83648
2147483647
0
Agent XPs (A)
0
1
0
allow updates (Obsolete. Do not use. Will cause an error during reconf­igure.)
0
1
0
backup compre­ssion default
0
1
0
blocked process threshold (A)
0
86400
0
c2 audit mode (A, RR)
0
1
0
clr enabled
0
1
0
common criteria compliance enabled (A, RR)
0
1
0
contained database authen­tic­ation
0
  
0
cost threshold for parall­elism (A)
0
32767
5
cross db ownership chaining
0
1
0
cursor threshold (A)
-1
2147483647
-1
Database Mail XPs (A)
0
1
0
default full-text language (A)
0
2147483647
1033
default language
0
9999
0
default trace enabled (A)
0
1
1
disallow results from triggers (A)
0
1
0
EKM provider enabled
0
1
0
filest­rea­m_a­cce­ss_­level
0
2
0
fill factor (A, RR)
0
100
0
ft crawl bandwidth (max), see ft crawl bandwi­dth(A)
0
32767
100
ft crawl bandwidth (min), see ft crawl bandwi­dth(A)
0
32767
0
ft notify bandwidth (max), see ft notify bandwi­dth(A)
0
32767
100
ft notify bandwidth (min), see ft notify bandwi­dth(A)
0
32767
0
index create memory (A, SC)
704
2147483647
0
in-doubt xact resolution (A)
0
2
0
lightw­eight pooling (A, RR)
0
1
0
locks (A, RR, SC)
5000
2147483647
0
max degree of parall­elism (A)
0
32767
0
max full-text crawl range (A)
0
256
4
max server memory (A, SC)
16
2147483647
2147483647
max text repl size
0
2147483647
65536
max worker threads (A)
128
32767
0
media retention (A, RR)
0
365
0
min memory per query (A)
512
2147483647
1024
min server memory (A, SC)
0
2147483647
0
nested triggers
0
1
1
network packet size (A)
512
32767
4096
Ole Automation Procedures (A)
0
1
0
open objects (A, RR, obsolete)
0
2147483647
0
optimize for ad hoc workloads (A)
0
1
0
PH_timeout (A)
1
3600
60
precompute rank (A)
0
1
0
priority boost (A, RR)
0
1
0
query governor cost limit (A)
0
2147483647
0
query wait (A)
-1
2147483647
-1
recovery interval (A, SC)
0
32767
0
remote access (RR)
0
1
1
remote admin connec­tions
0
1
0
remote login timeout
0
2147483647
10
remote proc trans
0
1
0
remote query timeout
0
2147483647
600
Replic­ation XPs Option (A)
0
1
0
scan for startup procs (A, RR)
0
1
0
server trigger recursion
0
1
1
set working set size (A, RR, obsolete)
0
1
0
show advanced options
0
1
0
SMO and DMO XPs (A)
0
1
1
transform noise words (A)
0
1
0
two digit year cutoff (A)
1753
9999
2049
user connec­tions (A, RR, SC)
0
32767
0
user options
0
32767
0
xp_cmd­shell (A)
0
1
0
 

Server Config­uration Tools

1. Ad Hoc Remote Queries
2. CLR enabled
3. Remote Admin Connec­tions (the tool refers to this setting as DAC)
4. OLE Automation Procedures
5. SQL Mail XPs
6. Web Assistant Procedures
7. Xp_cmd­shell
8. HTTP access to SQL Server through endpoints
9. Service Broker

Change Server Level Options

Use Advent­ure­Wor­ks2012;
GO
sp_con­figure 'show advanced options', 1;
GO
RECONF­IGURE;
GO
sp_con­figure 'fill factor', 100;
GO
RECONF­IGURE;
GO

Database Level Options

option­/st­atement

Auto Options

Option
Statement
AUTO_­­­CL­O­­SE­­
True
AUTO_­­­CR­E­­­AT­­E­_­­ST­­­A­TI­­­ST­­I­C­S
True
AUTO_­­­UP­D­­­AT­­E­_­­ST­­­A­TI­­­ST­­I­C­S­
True
AUTO_­­­SHRINK
False
Auto_­­­Up­d­­­at­­e­_­­St­­­a­ti­­­st­­i­­c­s­_­­­Asy­­­n­c­h­­r­­on­­­ou­s­­ly­­
False

Database Mirroring Options

Options
Statment
PARTNER
FAILOVER
PARTNER
FORCE_­­SE­R­V­IC­­E_A­­LL­O­W­_D­­ATA­­_LOSS
PARTNER
OFF
PARTNER
RESUME
PARTNER
SAFETY OFF
PARTNER
SUSPEND
PARTNER
TIMEOUT 20
WITNESS
OFF

Date Correl­­ation Optimi­­zation Option

Option
Statement
DATE_C­­OR­R­E­LA­­TIO­­N_­O­P­TI­­MIZ­­ATION
ON

PAGE VERIFY Options

Option
Statement
PAGE_V­­ERIFY
TORN_P­­AG­E­_­DE­­TECTION
PAGE_V­­ERIFY
CHECKSUM

Emergency Mode Options

Options
Statement
PAGE_V­­ERIFY
CHECKSUM
PAGE_V­­ERIFY
TORN_P­­AG­E­_­DE­­TECTION

PARAME­­TE­R­I­ZATION Options

Options
Statement
PARAME­­TE­R­I­ZATION
FORCED
PARAME­­TE­R­I­ZATION
SIMPLE

SQL Options

Options
Statement
READ_­­­CO­M­­­MI­­T­T­­ED­­­_­SN­­­AP­­S­H­OT
OFF
ANSI_­­­NULLS
OFF
ANSI_­­­PA­D­­­DI­N­G­
OFF
ANSI_­­­WA­R­­­NI­­N­G­S
OFF
ARITH­­­AB­O­­RT
OFF
CONCA­­­T_­N­­­UL­­L­_­­YI­­­E­LD­­­S_­­N­U­LL
OFF
QUOTE­­­D_­I­­­DE­­N­T­­IF­­­IER
OFF
NUMER­­­IC­_­­­RO­­U­N­­DABORT
OFF
RECUR­­­SI­V­­­E_­­T­R­­IGGERS
OFF

CURSOR Options

Options
Statement
CURSO­­­R_­C­­­LO­­S­E­­_O­­­N­_C­­­OM­­M­I­T­
OFF
CURSO­­­R_­D­­­EF­­A­U­LT
G­L­OBAL
 

Create Table Database

CREATE TABLE [dbo].Categories NOT NULL,  [CategoryName] nvarchar COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,   [Description] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,   [Picture] [image] NULL  ) ON [SECONDARY]  TEXTIMAGE_ON [SECONDARY]

Create a database snapshot

USE master;  GO    CREATE DATABASE northwind_snapshot1115 ON      ( NAME = northwind,    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\nwindsparse1.ss'),      ( NAME = northwind_data2,    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\nwindsparse2.ss')   AS SNAPSHOT OF northwind;  GO

Change Databa­­se­-­Level Options

ALTER DATABASE <database_name> SET <options> <statement>

Reverting Database

USE master;  GO  CREATE DATABASE AdventureWorks_130PM ON     ( NAME = AdventureWorks_data,    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\Data\aw1.ss')  AS SNAPSHOT OF AdventureWorks;
 

Metadata

  • Languages:
  • Published: 25th February, 2015
  • Last Updated: 13th May, 2016

Favourited By

jeaninem71

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

          Web Programming Cheat Sheet
          SQL Server Cheat Sheet
          SQL Cheat Sheet

          More Cheat Sheets by huda127

          SQL Server Cheat Sheet