Files
Located under Files
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 }
