SQL Server - Chairul Huda 5212100127 Cheat Sheet

A Programming › SQL cheat sheet in English by huda127

MABD Cheat Sheet

SQL

15,664 views 3,429 downloads 25 Feb 2015 History

PRs

Download

3.7 avg · 0 ratings

Server Level Options

option/min/max/def

Configuration 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 distributed queries (A)	0	1	0
affinity I/O mask (A, RR)	-2147483648	2147483647	0
affinity64 I/O mask (A, only available on 64-bit version of SQL Server)	-2147483648	2147483647	0
affinity mask (A)	-2147483648	2147483647	0
affinity64 mask (A, RR), only available on 64-bit version of SQL Server	-2147483648	2147483647	0
Agent XPs (A)	0	1	0
allow updates (Obsolete. Do not use. Will cause an error during reconfigure.)	0	1	0
backup compression 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 authentication	0		0
cost threshold for parallelism (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
filestream_access_level	0	2	0
fill factor (A, RR)	0	100	0
ft crawl bandwidth (max), see ft crawl bandwidth(A)	0	32767	100
ft crawl bandwidth (min), see ft crawl bandwidth(A)	0	32767	0
ft notify bandwidth (max), see ft notify bandwidth(A)	0	32767	100
ft notify bandwidth (min), see ft notify bandwidth(A)	0	32767	0
index create memory (A, SC)	704	2147483647	0
in-doubt xact resolution (A)	0	2	0
lightweight pooling (A, RR)	0	1	0
locks (A, RR, SC)	5000	2147483647	0
max degree of parallelism (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 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 (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 connections (A, RR, SC)	0	32767	0
user options	0	32767	0
xp_cmdshell (A)	0	1	0

Server Configuration Tools

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

Change Server Level Options

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

Database Level Options

option/statement

Auto Options

Option	Statement
AUTO_CLOSE	True
AUTO_CREATE_STATISTICS	True
AUTO_UPDATE_STATISTICS	True
AUTO_SHRINK	False
Auto_Update_Statistics_Asynchronously	False

Database Mirroring Options

Options	Statment
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

Option	Statement
DATE_CORRELATION_OPTIMIZATION	ON

PAGE VERIFY Options

Option	Statement
PAGE_VERIFY	TORN_PAGE_DETECTION
PAGE_VERIFY	CHECKSUM

Emergency Mode Options

Options	Statement
PAGE_VERIFY	CHECKSUM
PAGE_VERIFY	TORN_PAGE_DETECTION

PARAMETERIZATION Options

Options	Statement
PARAMETERIZATION	FORCED
PARAMETERIZATION	SIMPLE

SQL Options

Options	Statement
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

Options	Statement
CURSOR_CLOSE_ON_COMMIT	OFF
CURSOR_DEFAULT	GLOBAL

Create Table Database

CREATE TABLE [dbo].[Categories](   [CategoryID] [int] IDENTITY(1,1) NOT NULL,  [CategoryName] [nvarchar](15) 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 Database-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;**

Badges

Good Cheat Sheet Nice Cheat Sheet

Comments

No comments yet.