SQL Training - 02

SQL Server Features:

• BI: Business Inelegance; Which means Transforming Data to Knowledge  

to help decision maker to make right decisions

• DB Engine: Takes care of exciting transact SQL statements,
•  security(Data is the most Valuable Asset, 
• by default it encrypt the data and you can encrypt it by key), Audit(Logging every transaction), Roles and Authentications(users)
• indexing

it contains Transact SQL (T-SQL) programming Interface -- Reaching Data anytime anywhere--  , Handel errors

How? using every techniques to access data 

SQL Server 2012 : T-SQL is a set bases more efficient  (One loop to retrieve data)

T-SQL was before 2012 T-SQL is Cursor base (Slow, not Up-todate, more IO )

• Replication: Distributing data to different locations using FTP(Push and Pull), Publisher(Master Database) and Subscriber
• SQL Agent: 
• Scheduling Tasks and Jobs
• Rebuilding Index
• Backups
• Alert Fail 
• High Availability (H.A): through 
• Mirroring(Server 1 and Server 2 and Witness)
• always on (replications)
• fail over clustering (stand by)
• Log shipping: Distributing log files on replicas 

** ADO.Net library it contains everything to access database (Offline and Classified)

During installation set the Default Collation to Arabic_100

to overcome on hamza you have to use Arabic collation 

------------
Backups 3 Models :

• Simple: no log backups so you can't recover the data at point of time, , 
• Full Model: no lose of data, you can restore to any point of time
• Bulk-logged Model: you can restore only to the end of any backup 

SQL Server Browser:

• List all available servers
• Connect to any available server 

Datatypes:

• Use the specific datatype for fields
• Use specific length for varchar, char
• The datatype varchar(max) not indexable 
• varchar reserves the needed space and not the value which you supplied but the char reserves the value you supplied, for example :varchar(100) and you have entered 10 digits, only 10 digits will reserved from the space, char(100) it reserves 100 digits even you didn't added any values 
• Use date when you don't need the time, use YYYY-MM-DD or DD-MM-YYYY

Why default values are recommended ??

to avoid Nulls, when binding values to web controls the null makes a problem


Table Properties:

• Name
• Schema 
• Constraints
• File Group 
• Number of Fields

Field Properties:

• Name
• Datatype
• Idexing
• Null-able 

Why to use allow null and set default value?

it allows the data-insert to insert null for some reason