MSSQL Server 2005
Prof.: Alex Casanovas (SoftObert)
######CLASS 1 – 27/02/2009 #######
Relations – Entity
Entity = Table
Property = (COLUMNS)
[CLIENTS]
Propreties | Conditions, Name, SUrname, Address….
Strong Entity(Independent) or Weak Entity(Depends on other)
CLIENTS –> ORDER
Entity Relations:
1 -> 1
1 -> n
n -> m
Hierarchy, properties from the Father.
|MS-SQL SERVER
Min Requirements:
Win2k Pro
DIsk 3GB
Definie the installation:
1.Choose security type (mixed or native)
2.Choose installation type (Standard or CUstom)
3.Choose custom options (Instance/DB Name,Character Type, etc…)
4.Choose additional products (Client Agent, Analyzer, etc…)
Types of DB System:
OLTP, Online Transaction (insert,query,update transactions). 24×7 critical data
Analytic, Information storage, management analyze data to decide. Not 24×7
SQL 2005 (9.0)
Program FIles OPtions:
+Management Studio
+Anaylitical Services
+Performance Tools
+Business Intelligence Development Suit
+Configuration Tools
[GUI TOOL]
MS SQL Server Mgmt. Studio (Left Tree):
+DB (<Version> , <ServerName><InstanceName>)
-System DBs (Master,SystemInfo Users,Other DBs)
(Model, Template of DBs,Parameters to use in the creation of DBs)
(Msdb, Agentes,Schedulle Task Jobs,
(Tempdb,temp data of another,grow untill service is restarted)
-DB Snapshots
-.ReportServer
-.ReportServerTempDB
+Security
-Logins
-Server Roles, group properties, by permissions: backup, create,admin etc…
-Credentials
+Server Objects
-Backup Devices, Disk,Media,Network,Tape
-Endpoints, End user connections type: SOPA, TSQL,DB Mirroring etc..
-Linked Servers, Providers
-Triggers, Code that runs automatic
+Replication (allows DBs in different locations to have Sync data)
-Local Publications
-Local Subscriptions
+Management
-Maintenance Plans, group of actions to keep DB working
-SQL Server Logs,
Current and Archived,
Activity Monitor,DB Mail,
DB Mail
Distributed Transaction Coordinator
Full Text Search
+Legacy
+Notification Services
+SQL Server Agent (maintain open communications and schedulle tasks)
++DB Structures:
Tables, System Tables(auto created).
Views, Direct access to info in the DB, saved Query.
Synonyms, Alias to call Objects
Programmability, Transaction SQL code, (StoredProcedures,Type,Rules,Triggers etc..)
Service Broker, Relate DB with Users by messages
Storage, Partition Schemes, Partition Functions
Security, Users, Roles(groups,permissos), Certificates etc…
|CREATE A NEW DATABASE
TO see Script that created the DB:
Left Click on DB > Script DB as > Create
SQL CODE -> CREATE DB:
CREATE DATABASE [test] ON PRIMARY
( NAME = N’test’, FILENAME = N’C:DATAtest.mdf’ , SIZE = 3072KB , MAXSIZE = 102400KB , FILEGROWTH = 5120KB )
LOG ON
( NAME = N’test_log’, FILENAME = N’C:DATAtest_log.ldf’ , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
COLLATE Modern_Spanish_CI_AS
SQL CODE -> CREATE TABLE :
USE TEST
CREATE TABLE [dbo].[TBL_CLIENT](
[PK_CLIENT] [int] IDENTITY(1,1) NOT NULL,
[NAME] [varchar](50) COLLATE Modern_Spanish_CI_AS NULL
) ON [PRIMARY]
GO /** BLOCK OF CODE SEPARATION, BEST TO IDENTIFY ERRORS
CMD:
CREATE TABLE <NAME> (COLUMN_01 varchar(), not null)
Data Types:
+TEXT:
char(10) , uses all 10 chars
varchar(10), uses only entered character (recommended)
nchar or NVarchar, uses ANSI charac. system, allows export more widely occupay double the space.
text(), allows up to 2GB no char size specified
ntext(), allows up to 1GB type ANSI
+DATE:
smallDateTime(), date range 1753-1999 ?
datetime(), date range 1900 2079 ?
timestamp(), auto time value
UniqueIdentifier(), auto ID Unique in all SQL server in the Instance, composed by MAC,ServerInstance,etc… 128 chars
+NUMERIC:
tinyint, 0-255
smallint,-32.768 +32.767
int, -2.770.000 to +2.770.000
bigint, -9bil +9bil
decimal, decimal 10^6
float, like decimal but bigger
real, bigger then float
money, from -900milions to +900millio
smallmoney, -200,000,000 to +200,000,000
+OTHER:
bit, 0 or 1 (bollean)
binary, Images PDF etc…
varbinary, better compression rate, SQL2008 will use only that.
+More Properties:
Default Value, can use =getdate() (to insert by default the date of localsystem)
Identity SPecification: Auto Increment Value (MAX VALUE + 1, is better)
Constraints: -PRIMARY, Unique inside the Table, can be 1 or more column combined
-FORGEIN KEY,(relationships) Define 1 or more columns in a table that reference to a PRIMARY in other table
-CHECK, Restricts what value does the column can have, extend data type
System Administrator