SQL Server / Rahadiyan Danar A - 5212100124 Cheat Sheet

A Programming › SQL cheat sheet in English by danar124

Microsoft SQL Server Cheat Sheet Server-level Options dan database-level options

SQL Server Options Database-level Server-level

12,370 views 3,575 downloads 24 Feb 2015 History

PRs

Download

3.8 avg · 1 rating

Server-level Options

opt/min/max/def

Configuration

-access check cache bucket count /0/16384/0
-access check cache quota /0/2147483647/0
-ad hoc distributed queries /0/1/0
-affinity I/O mask /-2147483648/214748647/0
-affinity64 I/O mask /-2147483648/214748647/0
-Agent XPs /0/1/0
-allow updates /0/1/0
-backup compression default /0/1/0
-blocked process threshold /0/86400/0
-audit mode /0/1/0
-clr enabled /0/1/0
-common criteria compliance enabled /0/1/0
-contained database authentication 0/-/0
-cost threshold for parallelism /0/32767/5
-cross db ownership chaining /0/1/0
-cursor threshold /-1/2147483647/-1
-Database Mail XPs /0/1/0
-default full-text language /0/2147483647/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
-filestream_access_level /0/2/0
-fill factor /0/100/0
-ft crawl bandwidth (max) /0/32767/100
-ft crawl bandwidth (min) /0/32767/0
-ft notify bandwidth (max) /0/32767/100
-ft notify bandwidth (min) /0/32767/0
-index create memory /704/2147483647/0
-in-doubt xact resolution /0/2/0
-lightweight pooling /0/1/0
-locks /5000/214783647/0
-max degree of parallelism /0/32767/0
-max full-text crawl range /0/256/4
-max server memory /16/2147483647
-max text repl size /0/2147483647/65536
-max worker threads /128/32767/0
-media retention /0/365/0
-min memory per query /512/2147483647/1024
-min server memory /0/2147483647/0
-nested triggers /0/1/1
-network packet size /512/32767/4096
-Ole Automation Procedures /0/1/0
-open objects /0/2147483647/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/2147483647/0
-query wait /-1/2147483647/-1
-recovery interval /0/32767/0
-remote access /0/1/1
-remote admin connections /0/1/0
-remote login timeout /0/2147483647/10
-remote proc trans /0/1/0
-remote query timeout /0/2147483647/600
-Replication 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/9999/2049
-user connections /0/32767/0
-user options /0/32767/0
-xp_cmdshell /0/1/0

Database-Level Configuration

opt/statement

AUTO OPTIONS

AUTO_CLOSE/True
AUTO_CREATE_STATISTICS/True
AUTO_UPDATE_STATISTICS/True
AUTO_SHRINK /False
Auto_Update_Statistics_Asynchronously/False

Database Mirroring Options

PARTNER / FAILOVER
PARTNER / FORCE_SERVICE_ALLOW_DATA_LOSS
PARTNER / OFF
PARTNER / RESUME
PARTNER / SAFETY OFF
PARTNER / SUSPEND
PARTNER / TIMEOUT 20
WITNESS / OFF

Date Correlation Optimization Option

DATE_CORRELATION_OPTIMIZATION / ON

Emergency Mode Option

PAGE_VERIFY / TORN_PAGE_DETECTION
PAGE_VERIFY / CHECKSUM

PAGE VERIFY Option

PAGE_VERIFY / TORN_PAGE_DETECTION
PAGE_VERIFY / CHECKSUM

PARAMETERIZATION

PARAMETERIZATION / FORCED
PARAMETERIZATION / SIMPLE

Service Broker Options

SET NEW_BROKER
SET ENABLE_BROKER
SET ERROR_BROKER_CONVERSATIONS
SET TRUSTWORTHY ON
SET DISABLE_BROKER

SQL Options

READ_COMMITTED_SNAPSHOT/OFF
ANSI_NULLS /OFF
ANSI_PADDING/OFF
ANSI_WARNINGS/OFF
ARITHABORT/OFF
CONCAT_NULL_YIELDS_NULL/OFF
QUOTED_IDENTIFIER/OFF
NUMERIC_ROUNDABORT /OFF
RECURSIVE_TRIGGERS /OFF

CURSOR OPTIONS

CURSOR_CLOSE_ON_COMMIT/OFF
CURSOR_DEFAULT/GLOBAL

Change Server-Level Options

- **New query Using Transact-SQL (sp-configure)**

Use AdventureWorks2012;
GO
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'fill factor', 100;
GO
RECONFIGURE;
GO

Change Database-Level Options

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

Badges

Good Cheat Sheet Nice Cheat Sheet

Comments

No comments yet.