Show Menu
Cheatography

H2 DATABASE 1 - SQL Cheat Sheet by

H2 DATABASE 1 - SQL

Server Roles

sysadmin
Role members can perform all activities possible on the Database Engine instance. You specify initial membership of this role when performing instal­lation of the Database Engine feature
server­admin
Role members can perform instan­ce-wide config­uration tasks. Members of this role can shut down the instance
securi­tyadmin
Assign this role to logins that must be able to manage instan­ce-­level permis­sions
proces­sadmin
Role members can terminate processes running on a Database Engine instance.
setupadmin
Role members can add linked servers to and remove linked servers from the Database Engine instance.
bulkadmin
Assign this role to logins that you want to allow to use the BULK INSERT statement on databases hosted on an instance.
diskadmin
Role members can manage instan­ce-­related files
dbcreator
Role members are able to create, alter, drop, and restore databases hosted on the Database Engine instance
public
All SQL Server logins are members of this role.

Instal­lation of SQL Server

Feature Selection
Database Engine Services
Includes the Database Engine, the core service for storing.
Instal­lation
New SQL Server stand-­alone
Installing the final SQL Server in a non-cl­ustered enviroment
Setup Support Rukes
Status of rules
should pass all the rules
Instance Config­uration
Default Instance
Choose the default instance config­uration
Engine Config­uration
Specify Server Admins
add a new or current user
Engine Config­uration
Authen­tic­ation Mode
Choosing Mixed Mode - and setting up password

Monitoring Tools

SQL Server Profiler
tracks engine process events, such as the start of a batch or a transa­ction
Distri­buted Replay
use multiple computers to replay trace data, simulating a missio­n-c­ritical workload.
System Monitor
primarily tracks resource usage
Activity Monitor
displays inform­ation about: Processes running, Blocked processes, Locks, User activity.
Transa­ct-SQL
Transa­ct-SQL stored procedures that create, filter, and define tracing:
Error logs
The Windows applic­ation event log provides an overall picture of events occurring on the Windows Server

Date data types

datetime
From January 1, 1753 to December 31, 9999
smalld­atetime
From January 1, 1900 to June 6, 2079
date
Store a date only.
time
Store a time only to an accuracy of 100 nanose­conds
 

Setup of AD Sever

ADDS
DHCP
DNS

System Databases

Master
Records all the system­-level inform­ation for an instance of SQL Server.
Model
Is used as the template for all databases
MSDB
Is used by SQL Server Agent for scheduling alerts and jobs.
TEMPDB
Is a workspace for holding temporary objects or interm­ediate result sets.

String data types

char(n)
Fixed width character string
8,000 characters
varchar(n)
Variable width character string
8,000 characters
varcha­r(max)
Variable width character string
1,073,­741,824 characters
text
Variable width character string
2GB of text data
nchar
Fixed width Unicode string
4,000 characters
nvarchar
Variable width Unicode string
4,000 characters
nvarch­ar(max)
Variable width Unicode string
536,87­0,912 characters
ntext
Variable width Unicode string
2GB of text data
binary(n)
Fixed width binary string
8,000 bytes
varbinary
Variable width binary string
8,000 bytes
varbin­ary­(max)
Variable width binary string
2GB
image
Variable width binary string
2GB

List of relational databases

Oracle
MySQL
Microsoft SQL Server
PostgreSQL
IBM DB2
A relational database management system, is a database management system that is based on the relational model.

Relational databases have often replaced legacy hierar­chical databases and network databases because they are easier to understand and use.

Designing the Instal­lation

Database Engine Services
Provides the core service for storing, proces­sing, and securing data
SQL Server Replic­ation
Allows for copying and distri­buting data and database objects from one database to another
Full-Text and Semantic Extrac­tions For Search
Supports Full-Text Extraction for fast text search
Data Quality Services
Facili­tates both comput­er-­ass­isted and intera­ctive methods of managing data source quality and integrity
Analysis Services
Supports online analytical processing (OLAP) and data mining
Reporting Services – Native
Facili­tates the creation, manage­ment, and delivery of reports through email and intera­ctive web-based formats
Reporting Services – SharePoint
Integrates report viewing and management through ShareP­oint.
Reporting Services Add-in
Provides management and user interfaces that allow integr­ation between SharePoint and SQL Server Reporting Services (SSRS).
Data Quality Client
Interacts with data source quality and integrity services
 

Comments

SYGT NOK CHEAT SHEET

Add a Comment

Your Comment

Please enter your name.

    Please enter your email address

      Please enter your Comment.

          More Cheat Sheets by Hoejlund

          Windows Backup Service Cheat Sheet