AdventureWorks database in SQL Server 2005 Overview

Documentation

This is a sample database that ships with SQL Server 2005. It's for an imaginary company that sells bicycles.

Documentation generated on: Mar 4 2007 2:47AM

Database objects

type	count
tables	70
views	18
stored procedures	11
user defined functions	28
user defined types	10
triggers	77
defaults	29
rules	0
schemas	19
XML schema collections	8
assemblies	9
certificates	1
symmetric keys	1
asymmetric keys	1
synonyms	2

Database users

name	login	groups	created	updated	has db access
dbo	sa	db_owner	Apr 8 2003 9:10AM	Oct 14 2005 1:58AM	1
guest			Apr 8 2003 9:10AM	Apr 8 2003 9:10AM	0
INFORMATION_SCHEMA			Oct 14 2005 1:36AM	Oct 14 2005 1:36AM	0
sys			Oct 14 2005 1:36AM	Oct 14 2005 1:36AM	0
dork	dork		Aug 23 2006 10:35PM	Aug 23 2006 10:35PM	1

Database files

database	file	file group	size	max size	growth	usage
AdventureWorks_Data	C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks_Data.mdf	PRIMARY	184,256 kb	Unlimited	16384 KB	data only
AdventureWorks_Log	C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks_Log.ldf		2,048 kb	2147483648 KB	16384 KB	log only

Database properties

name	value
name	AdventureWorks
server	SUMMER
size	181.94 mb
owner	SUMMER\Jesse
created	Jul 16 2006
status	ONLINE
compatibility level	90
updateability	READ_WRITE
user access	MULTI_USER
recovery	SIMPLE
version	611
collation	SQL_Latin1_General_CP1_CI_AS
SQL sort order	52
auto close	no
auto shrink	no
in standby	no
torn page detection	no
ansi null default	no
ansi nulls	yes
ansi padding	yes
ansi warnings	yes
arithmetic abort	yes
auto create statistics	yes
auto update statistics	yes
close cursors on commit	no
full text	yes
local cursors default	no
null concat	yes
numeric round abort	no
quoted identifiers	yes
recursive triggers	no
published	no
subscribed	no
sync with backup	no

Server Properties

name	value
Name	summer
TrueName	SUMMER
DefaultTextMode	True
Collation	SQL_Latin1_General_CP1_CS_AS
Edition	Standard Edition
ErrorLogPath	C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG
IsCaseSensitive	False
IsClustered	False
IsFullTextInstalled	True
IsSingleUser	False
Language	English (United States)
MasterDBLogPath	C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA
MasterDBPath	C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA
MaxPrecision	38
NetName	SUMMER
OSVersion	5.1 (2600)
PhysicalMemory	2047
Platform	NT INTEL X86
Processors	2
Product	Microsoft SQL Server
ProductLevel	SP1
RootDirectory	C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL
Version	9.0.2047
VersionString	9.00.2047.00
AbortOnArithmeticErrors	False
AbortTransactionOnError	False
AnsiNullDefaultOff	False
AnsiNullDefaultOn	False
AnsiNulls	False
AnsiPadding	False
AnsiWarnings	False
ConcatenateNullYieldsNull	False
CursorCloseOnCommit	False
DisableDefaultConstraintCheck	False
IgnoreArithmeticErrors	False
ImplicitTransactions	False
NoCount	False
NumericRoundAbort	False
QuotedIdentifier	False
AuditLevel	Failure
BackupDirectory	C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup
DefaultFile	C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data
DefaultLog	C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG
LoginMode	Mixed
MailProfile
NumberOfLogFiles	-1
PerfMonMode	None
TapeLoadWaitTime	-1

Server Configuration

name	description	run value	config value	minimum	maximum	is advanced	is dynamic
Ad Hoc Distributed Queries	Enable or disable Ad Hoc Distributed Queries	1	1	0	1	True	True
affinity I/O mask	affinity I/O mask	0	0	-2147483648	2147483647	True	False
affinity mask	affinity mask	0	0	-2147483648	2147483647	True	True
Agent XPs	Enable or disable Agent XPs	1	1	0	1	True	True
allow updates	Allow updates to system tables	0	0	0	1	False	True
awe enabled	AWE enabled in the server	0	0	0	1	True	False
c2 audit mode	c2 audit mode	0	0	0	1	True	False
clr enabled	CLR user code execution enabled in the server	1	1	0	1	False	True
cost threshold for parallelism	cost threshold for parallelism	5	5	0	32767	True	True
cross db ownership chaining	Allow cross db ownership chaining	0	0	0	1	False	True
cursor threshold	cursor threshold	-1	-1	-1	2147483647	True	True
Database Mail XPs	Enable or disable Database Mail XPs	0	0	0	1	True	True
default full-text language	default full-text language	1033	1033	0	2147483647	True	True
default language	default language	0	0	0	9999	False	True
fill factor (%)	Default fill factor percentage	0	0	0	100	True	False
index create memory (KB)	Memory for index create sorts (kBytes)	0	0	704	2147483647	True	True
lightweight pooling	User mode scheduler uses lightweight pooling	0	0	0	1	True	False
locks	Number of locks for all users	0	0	5000	2147483647	True	False
max degree of parallelism	maximum degree of parallelism	0	0	0	64	True	True
max server memory (MB)	Maximum size of server memory (MB)	2147483647	2147483647	16	2147483647	True	True
max text repl size (B)	Maximum size of a text field in replication.	65536	65536	0	2147483647	False	True
max worker threads	Maximum worker threads	0	0	128	32767	True	False
media retention	Tape retention period in days	0	0	0	365	True	False
min memory per query (KB)	minimum memory per query (kBytes)	1024	1024	512	2147483647	True	True
min server memory (MB)	Minimum size of server memory (MB)	8	0	0	2147483647	True	True
nested triggers	Allow triggers to be invoked within triggers	1	1	0	1	False	True
network packet size (B)	Network packet size	4096	4096	512	32767	True	True
Ole Automation Procedures	Enable or disable Ole Automation Procedures	0	0	0	1	True	True
open objects	Number of open database objects	0	0	0	2147483647	True	False
PH timeout (s)	DB connection timeout for full-text protocol handler (s)	60	60	1	3600	True	True
precompute rank	Use precomputed rank for full-text query	0	0	0	1	True	True
priority boost	Priority boost	0	0	0	1	True	False
query governor cost limit	Maximum estimated cost allowed by query governor	0	0	0	2147483647	True	True
query wait (s)	maximum time to wait for query memory (s)	-1	-1	-1	2147483647	True	True
recovery interval (min)	Maximum recovery interval in minutes	0	0	0	32767	True	True
remote access	Allow remote access	1	1	0	1	False	False
remote admin connections	Dedicated Admin Connections are allowed from remote clients	0	0	0	1	False	True
remote login timeout (s)	remote login timeout	20	20	0	2147483647	False	True
remote proc trans	Create DTC transaction for remote procedures	0	0	0	1	False	True
remote query timeout (s)	remote query timeout	600	600	0	2147483647	False	True
Replication XPs	Enable or disable Replication XPs	0	0	0	1	True	True
scan for startup procs	scan for startup stored procedures	1	1	0	1	True	False
set working set size	set working set size	0	0	0	1	True	False
show advanced options	show advanced options	1	1	0	1	False	True
SMO and DMO XPs	Enable or disable SMO and DMO XPs	1	1	0	1	True	True
SQL Mail XPs	Enable or disable SQL Mail XPs	0	0	0	1	True	True
transform noise words	Transform noise words for full-text query	0	0	0	1	True	True
two digit year cutoff	two digit year cutoff	2049	2049	1753	9999	True	True
user connections	Number of user connections allowed	0	0	0	32767	True	False
user options	user options	0	0	0	32767	False	True
Web Assistant Procedures	Enable or disable Web Assistant Procedures	0	0	0	1	True	True
xp_cmdshell	Enable or disable command shell	0	0	0	1	True	True