AdventureWorks Sample OLTP Database
Documentation generated on: May 14 2009 9:44PM
| type | count |
|---|---|
| tables | 70 |
| views | 17 |
| indexes | 167 |
| procedures | 9 |
| functions | 11 |
| types | 6 |
| triggers | 12 |
| defaults | 147 |
| rules | 0 |
| schemas | 18 |
| assemblies | 1 |
| name | role memership |
|---|---|
| dbo | db_owner |
| guest | |
| INFORMATION_SCHEMA | |
| sys |
| name | role id | is application role |
|---|---|---|
| public | 0 | 0 |
| db_owner | 16384 | 0 |
| db_accessadmin | 16385 | 0 |
| db_securityadmin | 16386 | 0 |
| db_ddladmin | 16387 | 0 |
| db_backupoperator | 16389 | 0 |
| db_datareader | 16390 | 0 |
| db_datawriter | 16391 | 0 |
| db_denydatareader | 16392 | 0 |
| db_denydatawriter | 16393 | 0 |
| file | file group | size | max size | growth | usage |
|---|---|---|---|---|---|
| C:\Program Files\Microsoft SQL Server\MSSQL10.KATMAI\MSSQL\DATA\AdventureWorks_Data.mdf | PRIMARY | 174080 KB | Unlimited | 16384 KB | data only |
| C:\Program Files\Microsoft SQL Server\MSSQL10.KATMAI\MSSQL\DATA\AdventureWorks_Log.ldf | 2048 KB | 2147483648 KB | 16384 KB | log only |
| name | value |
|---|---|
| name | AdventureWorks |
| server | SPRING\KATMAI |
| size | 172.00 MB |
| created | Mar 19 2009 9:08PM |
| status | ONLINE |
| owner | NT AUTHORITY\SYSTEM |
| version | Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) Jul 9 2008 14:43:34 Copyright (c) 1988-2008 Microsoft Corporation Developer Edition on Windows NT 6.0 <X86> (Build 6001: Service Pack 1) |
| unallocated | 0.66 MB |
| reserved | 173408 KB |
| data size | 88752 KB |
| index size | 78568 KB |
| unused | 6088 KB |
| compatibility level | 100 |
| updateability | READ_WRITE |
| user access | MULTI_USER |
| recovery | SIMPLE |
| 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 |
| name | deny login | has access | sys admin | security admin | server admin | setup admin | process admin | disk admin | db creator | bulk admin |
|---|---|---|---|---|---|---|---|---|---|---|
| NT AUTHORITY\SYSTEM | no | yes | yes | no | no | no | no | no | no | no |
| NT SERVICE\MSSQL$KATMAI | no | yes | yes | no | no | no | no | no | no | no |
| NT SERVICE\SQLAgent$KATMAI | no | yes | yes | no | no | no | no | no | no | no |
| sa | no | yes | yes | no | no | no | no | no | no | no |
| SPRING\Jesse | no | yes | yes | no | no | no | no | no | no | no |
| name | value |
|---|---|
| Name | SPRING\KATMAI |
| TrueName | SPRING\KATMAI |
| DefaultTextMode | True |
| Collation | SQL_Latin1_General_CP1_CI_AS |
| Edition | Developer Edition |
| ErrorLogPath | C:\Program Files\Microsoft SQL Server\MSSQL10.KATMAI\MSSQL\Log |
| IsCaseSensitive | False |
| IsClustered | False |
| IsFullTextInstalled | True |
| IsSingleUser | False |
| Language | English (United States) |
| MasterDBLogPath | C:\Program Files\Microsoft SQL Server\MSSQL10.KATMAI\MSSQL\DATA |
| MasterDBPath | C:\Program Files\Microsoft SQL Server\MSSQL10.KATMAI\MSSQL\DATA |
| MaxPrecision | 38 |
| NetName | SPRING |
| OSVersion | 6.0 (6001) |
| PhysicalMemory | 3069 |
| Platform | NT INTEL X86 |
| Processors | 4 |
| Product | Microsoft SQL Server |
| ProductLevel | RTM |
| RootDirectory | C:\Program Files\Microsoft SQL Server\MSSQL10.KATMAI\MSSQL |
| Version | 10.0.1600 |
| VersionString | 10.0.1600.22 |
| 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\MSSQL10.KATMAI\MSSQL\Backup |
| DefaultFile | |
| DefaultLog | |
| LoginMode | Mixed |
| MailProfile | |
| NumberOfLogFiles | -1 |
| PerfMonMode | None |
| TapeLoadWaitTime | -1 |
| name | description | run value | config value | minimum | maximum | is advanced | is dynamic |
|---|---|---|---|---|---|---|---|
| Ad Hoc Distributed Queries | Enable or disable Ad Hoc Distributed Queries | 0 | 0 | 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 | 0 | 0 | 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 | 0 | 0 | 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 | -1 | 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 | True |
| 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 | 0 | 0 | 0 | 1 | True | False |
| set working set size | set working set size | 0 | 0 | 0 | 1 | True | False |
| show advanced options | show advanced options | 0 | 0 | 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 |
| xp_cmdshell | Enable or disable command shell | 0 | 0 | 0 | 1 | True | True |
CREATE DATABASE [AdventureWorks] ON PRIMARY ( NAME = N'AdventureWorks_Data', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.KATMAI\MSSQL\DATA\AdventureWorks_Data.mdf' , SIZE = 174080KB , MAXSIZE = UNLIMITED, FILEGROWTH = 16384KB ) LOG ON ( NAME = N'AdventureWorks_Log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.KATMAI\MSSQL\DATA\AdventureWorks_Log.ldf' , SIZE = 2048KB , MAXSIZE = 2048GB , FILEGROWTH = 16384KB ) COLLATE SQL_Latin1_General_CP1_CI_AS ALTER DATABASE [AdventureWorks] SET COMPATIBILITY_LEVEL = 100 IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled')) begin EXEC [AdventureWorks].[dbo].[sp_fulltext_database] @action = 'enable' end ALTER DATABASE [AdventureWorks] SET ANSI_NULL_DEFAULT OFF ALTER DATABASE [AdventureWorks] SET ANSI_NULLS ON ALTER DATABASE [AdventureWorks] SET ANSI_PADDING ON ALTER DATABASE [AdventureWorks] SET ANSI_WARNINGS ON ALTER DATABASE [AdventureWorks] SET ARITHABORT ON ALTER DATABASE [AdventureWorks] SET AUTO_CLOSE OFF ALTER DATABASE [AdventureWorks] SET AUTO_CREATE_STATISTICS ON ALTER DATABASE [AdventureWorks] SET AUTO_SHRINK OFF ALTER DATABASE [AdventureWorks] SET AUTO_UPDATE_STATISTICS ON ALTER DATABASE [AdventureWorks] SET CURSOR_CLOSE_ON_COMMIT OFF ALTER DATABASE [AdventureWorks] SET CURSOR_DEFAULT GLOBAL ALTER DATABASE [AdventureWorks] SET CONCAT_NULL_YIELDS_NULL ON ALTER DATABASE [AdventureWorks] SET NUMERIC_ROUNDABORT OFF ALTER DATABASE [AdventureWorks] SET QUOTED_IDENTIFIER ON ALTER DATABASE [AdventureWorks] SET RECURSIVE_TRIGGERS OFF ALTER DATABASE [AdventureWorks] SET ENABLE_BROKER ALTER DATABASE [AdventureWorks] SET AUTO_UPDATE_STATISTICS_ASYNC OFF ALTER DATABASE [AdventureWorks] SET DATE_CORRELATION_OPTIMIZATION OFF ALTER DATABASE [AdventureWorks] SET TRUSTWORTHY OFF ALTER DATABASE [AdventureWorks] SET ALLOW_SNAPSHOT_ISOLATION OFF ALTER DATABASE [AdventureWorks] SET PARAMETERIZATION SIMPLE ALTER DATABASE [AdventureWorks] SET READ_COMMITTED_SNAPSHOT OFF ALTER DATABASE [AdventureWorks] SET HONOR_BROKER_PRIORITY OFF ALTER DATABASE [AdventureWorks] SET READ_WRITE ALTER DATABASE [AdventureWorks] SET RECOVERY SIMPLE ALTER DATABASE [AdventureWorks] SET MULTI_USER ALTER DATABASE [AdventureWorks] SET PAGE_VERIFY CHECKSUM ALTER DATABASE [AdventureWorks] SET DB_CHAINING OFF