Tuesday, June 26, 2012

DBS Stuff

 

Files

Located under Files

image

clip_image001

Data: Initial at least 3 GB | Autogrowth, at least 100MB each iteration. Better to use in Megabytes rather than in Percent

Log: Initial size 7GB |Autogrowth at least 100MB each iteration.

FILE GROUPS

Different files, preferably on different machines

Allows tables, and in general SQL objects, to be created in different files

CREATE TABLE [dbo].[Status](
    [StatusId] [smallint] IDENTITY(1,1) NOT NULL,
    [Type] [nchar](50) NOT NULL,
CONSTRAINT [PK_Status] PRIMARY KEY CLUSTERED
(
    [StatusId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

 

Brown is the index Filegroup

Purple is the Table FileGroup

Primary is the default file group when none are created

 

LOG

Use a sequential writing hard disk - such as Raid 0 or Raid 1.

Full – saves everything, incl all bulk inserts (values), is reduced only when db is backed up| used for differential backups.

Bulk – saves almost everything, not including bulk inserts i.e. It only saves the size of the bulk insert without the data itself.

Simple – a cyclical log file that only saves data not yet committed as well as last check points.

DATA

User random access writing hard disk – such as Raid 5

Hebrew

Use collation "HEBREW_CI_AS"

Collation however only works for varchar

In general than adding data in Unicode don't forget to use N'מידע חשוב'! The N stands for Unicode.

SQL DDL

Decimal (Scale, Precision)

Scale – number of digits

Precision – number of digits (of scale) which are decimal.

SQL prefers INT over other types in general.

 

Nchar / Varchar

Nchar – char that is Unicode (not dynamic)

varchar - Variable (dynamic) sized data strings. (Penalty of size of string – 2bytes)

Nvarchar – Dynamic Unicode string (incl the penalty)

Unicode – takes up 2 chars for every char (2X the space)

Misc

Add Indexes to most ForeignKey constraints

DataBase projects

CLR allows to add Dot.NET CLR dlls that can plugin and create new "Functions", that run using Dot.Net engine per row (like any sql function).

To create ->

Visual Studio è New Projectè DataBase è SqlServerè CLR Database project

Service Broker

Like a crippled msmq.

Includes an Event driven DeQueue that can trigger a stored procedure

You can configure Max currents

Is Transactionable {with rollback support }