Types of partitions(HDD)

Primary Partition is a partition that is needed to store and boot an operating system, though applications and user data can reside there as well, and what’s more, you can have a primary partition without any operating system on it. There can be up to a maximum of four primary partitions on a single hard disk, with only one of them set as active (see “Active partition”).

Active (boot) partition is a primary partition that has an operating system installed on it. It is used for booting your machine. If you have a single primary partition, it is regarded as active. If you have more than one primary partition, only one of them is marked active (in a given PC session).

Extended partition can be sub-divided into logical drives and is viewed as a container for logical drives, where data proper is located. An extended partition is not formatted or assigned a drive letter. The extended partition is used only for creating a desired number of logical partitions.

Logical drive is created within an extended partition. A logical partition is a way to extend the initial limitation of four partitions. An extended partition can contain up to 24 logical partitions (you’re limited by the number of drive letters and the amount of hard drive space available for creating drives; of course, it’s senseless to use 24 partitions on a system in most cases, because it will be a data organization nightmare). Logical partitions are used for storing data mainly, they can be formatted and assigned drive letters; their details are listed in the extended partition’s table - EMBR (Extended Master Boot Record).

Partition software

So to implement basic and advanced disk partitioning operations, you require special software. Here are possible tools you may use. You can read another article about partition recovery software too.

Built-in partitioning utility

Most operating systems use the built-in “fdisk” command to create hard disk partitions. But this solution can’t be called reliable - you may lose your data. If you resize or merge partitions, or reinstall your OS with the number of partitions different from the initial one, data loss is inevitable. Everything will be erased totally.
The “fdisk” feature is at the same time not that easy-to-use; you should be an experienced PC user to implement partitioning of your hard drive. Still it’s free and is shipped together with the majority of operating systems.

Free software

Free pieces of hard drive partitioning software have no guarantee of data integrity and have a minimum of backup features. Perhaps the most popular utilities are Gnome Partition Editor (GParted), Partition Logic and Cute Partition Manager. You can easily download them from websites.
Let’s analyze Gnome Partition Editor (GParted), for instance. It’s a Linux-based tool for newbies. The features it can boast include only create, format, delete, resize, copy, move and merge partitions. Plus it can change volume label. As you see the number of options is not that high and, once again, there is no guarantee that you’ll be able to access your data after carrying out the listed operations.

Commercial software

To have the peace-of-mind performing hard drive partitioning tasks, you need to possess truly powerful partition managers. You’ll have to pay , but you’ll obtain reliable, fast and easy-to-use partitioning software with a wide range of features. Here are the market leaders:

• Acronis Disk Director

http://www.acronis.com/homecomputing/products/diskdirector/

• Norton Partition Magic

http://www.symantec.com/norton/products/overview.jsp?pcid=sp&pvid=pm80

• Paragon Partition Manager

http://www.partition-manager.com/

Thanks
R.karthikeyan

Understanding file systems

What is file system?
Any computer file is stored on some kind of storage with a given capacity. Actually, each storage is a linear space to read or both read and write digital information. Each byte of information on the storage has its own offset from the storage start (address) and is referenced by this address. A storage can be presented as a grid with a set of numbered cells (each cell – single byte). Any file saved to the storage takes a number of these cells.

Generally, computer storages use a pair of sector and in-sector offset to reference any byte of information on the storage. The sector is a group of bytes (usually 512 bytes) that is a minimum addressable unit of the physical storage. For example, byte 1030 on a hard disk will be referenced as sector #3 and offset in sector 16 bytes ([sector]+[sector]+[16 bytes]). This scheme is applied to optimize storage addressing and use a smaller number to reference any portion of information on the storage.

To omit the second part of the address (in-sector offset), files are usually stored starting from the sector start and occupy all whole sectors (e.g.: 10-byte file occupies the whole sector, 512-byte file also occupies the whole sector, at the same time, 514 byte file occupies two whole sectors).

Each file is stored to 'unused' sectors and can be read then by known position and size. However, how do we know what sectors are used or unused? Where are file size and position stored? Where is file name? These answers give us the file system.

As a whole, file system is a structured data representation and a set of metadata that describe the stored data. File system can not only serve for the purposes of the whole storage but also be a part of an isolated storage segment – disk partition. Usually the file system operates blocks, not sectors. File system blocks are groups of sectors that optimize storage addressing. Modern file systems generally use block sizes from 1 up to 128 sectors (512-65536 bytes). Files are usually stored from the start of a block and take entire blocks.

Immense write/delete operations to file system cause file system fragmentation. As a result files aren't stored as whole fragments anymore and are divided into fragments. For example, a storage is entirely taken by files with size about 4 blocks (e.g. pictures collection). User wants to store a file that will take 8 blocks and therefore deletes the first and the last file. By doing this he releases 8 blocks, however, the first segment is near to the storage start, and the second – to the storage end. In this case 8-block file will be split into two parts (4 blocks for each part) and will take free space 'holes'. The information about both fragments, which are parts of a single file, will be stored to file system.

In addition to user files the file system also stores its own parameters (such as block size), file descriptors (that include file size, file location, its fragments etc.), file names and directory hierarchy. It may also store security information, extended attributes and other parameters.

To comply with diverse requirements as to storage performance, stability and reliability there exists a great variety of file systems each developed to serve certain user purposes.


Windows file systems

Microsoft Windows OS use two major file systems: FAT, inherited from old DOS with its later extension FAT32, and modern NTFS file systems.

FAT (File Allocation Table):
FAT file system is one of the most simple types of file systems. It consists of file system descriptor sector (boot sector or superblock), file system block allocation table (referenced as File Allocation Table) and plain storage space to store files and folders. Files on FAT are stored in directories. Each directory is an array of 32-byte records, each defines file or file extended attributes (e.g. long file name). File record references the first block of file. Any next block can be found through block allocation table by using it as linked-list.

Block allocation table contains an array of block descriptors. Zero value indicates that the block is not used and non-zero – reference to the next block of the file or special value for file end.

The number in FAT12, FAT16, FAT32 stands for the number if bits used to enumerate file system block. This means that FAT12 may use up to 4096 different block references, FAT16 - 65536 and FAT32 - 4294967296. Actual maximum count of blocks is even less and depends on implementation of file system driver.

FAT12 was used for old floppy disks. FAT16 (or simply FAT) and FAT32 are widely used for flash memory cards and USB flash sticks. It is supported by mobile phones, digital cameras and other portable devices.

FAT or FAT32 is a file system, used on Windows-compatible external storages or disk partitions with size below 2GB (for FAT) or 32GB (for FAT32). Windows can not create FAT32 file system over 32GB (however Linux supports FAT32 up to 2TB).

NTFS (New Technology File System):
NTFS was introduced in Windows NT and at present is major file system for Windows. This is a default file system for disk partitions and the only file system that is supported for disk partitions over 32GB. The file system is quite extensible and supports many file properties, including access control, encryption etc. Each file on NTFS is stored as file descriptor in Master File Table and file content. Master file table contains all information about the file: size, allocation, name etc. The first and the last sectors of the file system contain file system settings (boot record or superblock). This file system uses 48 and 64 bit values to reference files, thus supporting quite large disk storages.


Resilient File System (ReFS)
ReFS  is a new local file system on windows 8 and server 2012 . It maximizes data availability, despite errors that would historically cause data loss or downtime. Data integrity ensures that business critical data is protected from errors and available when needed. Its architecture is designed to provide scalability and performance in an era of constantly growing data set sizes and dynamic workloads.
The key features of ReFS are:

• Integrity: ReFS stores data so that it is protected from many of the common errors that can cause data loss. File system metadata is always protected. Optionally, user data can be protected on a per-volume, per-directory, or per-file basis. If corruption occurs, ReFS can detect and, when configured with Storage Spaces, automatically correct the corruption. In the event of a system error, ReFS is designed to recover from that error rapidly, with no loss of user data.
• Availability: ReFS is designed to prioritize the availability of data. With ReFS, if corruption occurs, and it cannot be repaired automatically, the online salvage process is localized to the area of corruption, requiring no volume down-time. In short, if corruption occurs, ReFS will stay online.
• Scalability: ReFS is designed for the data set sizes of today and the data set sizes of tomorrow; it’s optimized for high scalability.
• App Compatibility: To maximize AppCompat, ReFS supports a subset of NTFS features plus Win32 APIs that are widely adopted.
• Proactive Error Identification: The integrity capabilities of ReFS are leveraged by a data integrity scanner (a “scrubber”) that periodically scans the volume, attempts to identify latent corruption, and then proactively triggers a repair of that corrupt data.

MacOS file systems

Apple Mac OS operating system applies HFS+ file system, an extension to their own HFS file system that was used on old Macintosh computers.

HFS+ file system is applied to Apple desktop products, including Mac computers, iPhone, iPod, as well as Apple X Server products. Advanced server products also use Apple Xsan file system, clustered file system derived from StorNext or CentraVision file systems.

This file system except files and folders also stores Finder information about directories view, window positions etc.

Linux file systems

Open-source Linux OS always aimed to implement, test and use different concepts of file systems. Among huge amount of various file system types the most popular Linux file systems nowadays are:

• Ext2, Ext3, Ext4 - 'native' Linux file system. This file system falls under active developments and improvements. Ext3 file system is just an extension to Ext2 that uses transactional file write operations with journal. Ext4 is a further development of Ext3, extended with support of optimized file allocation information (extents) and extended file attributes. This file system is frequently used as 'root' file system for most Linux installations.
  
• ReiserFS - alternative Linux file system designed to store huge amount of small files. It has good capability of files search and enables compact files allocation by storing file tails or small files along with metadata in order not to use large file system blocks for this purpose.
  
• XFS - file system derived from SGI company that initially used it for their IRIX servers. Now XFS specifications are implemented in Linux. XFS file system has great performance and is widely used to store files.
  
• JFS - file system developed by IBM for their powerful computing systems. JFS one usually stands for JFS, JFS2 is the second edition. Currently this file system is open-source and is implemented in most modern Linux distributions.
  

The concept of 'hard links' used in this kind of OS makes most Linux file systems similar in that the file name is not regarded as file attribute and rather defined as an alias for a file in certain directory. File object can be linked from many locations, even many times from the same directory under different names. This is one of the causes why recovery of file names after file deletion or file system damage can be difficult or even impossible.


BSD, Solaris, Unix file systems

The most common file system for these OS is UFS (Unix File System) also often referred to FFS (Fast File System – fast compared to a previous file system used for Unix). UFS is a source of ideas for many other file system implementations.

Currently UFS (in different editions) is supported by all Unix-family OS and is major file system of BSD OS and Sun Solaris OS. Modern computer technologies tend to implement replacements for UFS in different OS (ZFS for Solaris, JFS and derived file systems for Unix etc.).


Clustered file systems

TClustered file systems are used in computer cluster systems. These file systems have embedded support of distributed storage.

Among such distributed file systems are:

• ZFS - Sun company 'Zettabyte File System' - the new file system developed for distrubuted storages of Sun Solaris OS.
  
• Apple Xsan - the Apple company evolution of CentraVision and later StorNext file systems.
  
• VMFS - 'Virtual Machine File System' developed by VMware company for its VMware ESX Server.
  
• GFS - Rad Hat Linux 'Global File System'.
  
• JFS1 - original (legacy) design of IBM JFS file system used in older AIX storage systems.
  

Common property of these file systems is distributed storages support, extensibility and modularity.



Thanks
R.karthikeyan

Best Way to remember-Ethernet Cable Color Coding

The information listed here is to assist Network Administrators in the color coding of Ethernet cables. Please be aware that modifying Ethernet cables improperly may cause loss of network connectivity. Use this information at your own risk, and insure all connectors and cables are modified in accordance with standards. The Internet Centre and its affiliates cannot be held liable for the use of this information in whole or in part.

T-568A Straight-Through Ethernet Cable

The TIA/EIA 568-A standard which was ratified in 1995, was replaced by the TIA/EIA 568-B standard in 2002 and has been updated since. Both standards define the T-568A and T-568B pin-outs for using Unshielded Twisted Pair cable and RJ-45 connectors for Ethernet connectivity. The standards and pin-out specification appear to be related and interchangeable, but are not the same and should not be used interchangeably.

T-568B Straight-Through Ethernet Cable

Both the T-568A and the T-568B standard Straight-Through cables are used most often as patch cords for your Ethernet connections. If you require a cable to connect two Ethernet devices directly together without a hub or when you connect two hubs together, you will need to use a Crossover cable instead.

RJ-45 Crossover Ethernet Cable

A good way of remembering how to wire a Crossover Ethernet cable is to wire one end using the T-568A standard and the other end using the T-568B standard. Another way of remembering the color coding is to simply switch the Green set of wires in place with the Orange set of wires. Specifically, switch the solid Green (G) with the solid Orange, and switch the green/white with the orange/white.
Ethernet Cable Instructions:

• Pull the cable off the reel to the desired length and cut. If you are pulling cables through holes, its easier to attach the RJ-45 plugs after the cable is pulled. The total length of wire segments between a PC and a hub or between two PC's cannot exceed 100 Meters (328 feet) for 100BASE-TX and 300 Meters for 10BASE-T.
• Start on one end and strip the cable jacket off (about 1") using a stripper or a knife. Be extra careful not to nick the wires, otherwise you will need to start over.
• Spread, untwist the pairs, and arrange the wires in the order of the desired cable end. Flatten the end between your thumb and forefinger. Trim the ends of the wires so they are even with one another, leaving only 1/2" in wire length. If it is longer than 1/2" it will be out-of-spec and susceptible to crosstalk. Flatten and insure there are no spaces between wires.
• Hold the RJ-45 plug with the clip facing down or away from you. Push the wires firmly into the plug. Inspect each wire is flat even at the front of the plug. Check the order of the wires. Double check again. Check that the jacket is fitted right against the stop of the plug. Carefully hold the wire and firmly crimp the RJ-45 with the crimper.
• Check the color orientation, check that the crimped connection is not about to come apart, and check to see if the wires are flat against the front of the plug. If even one of these are incorrect, you will have to start over. Test the Ethernet cable.

Ethernet Cable Tips:

• A straight-thru cable has identical ends.
• A crossover cable has different ends.
• A straight-thru is used as a patch cord in Ethernet connections.
• A crossover is used to connect two Ethernet devices without a hub or for connecting two hubs.
• A crossover has one end with the Orange set of wires switched with the Green set.
• Odd numbered pins are always striped, even numbered pins are always solid colored.
• Looking at the RJ-45 with the clip facing away from you, Brown is always on the right, and pin 1 is on the left.
• No more than 1/2" of the Ethernet cable should be untwisted otherwise it will be susceptible to crosstalk.
• Do not deform, do not bend, do not stretch, do not staple, do not run parallel with power cables, and do not run Ethernet cables near noise inducing components.

Basic Theory:

By looking at a T-568A UTP Ethernet straight-thru cable and an Ethernet crossover cable with a T-568B end, we see that the TX (transmitter) pins are connected to the corresponding RX (receiver) pins, plus to plus and minus to minus. You can also see that both the blue and brown wire pairs on pins 4, 5, 7, and 8 are not used in either standard. What you may not realize is that, these same pins 4, 5, 7, and 8 are not used or required in 100BASE-TX as well. So why bother using these wires, well for one thing its simply easier to make a connection with all the wires grouped together. Otherwise you'll be spending time trying to fit those tiny little wires into each of the corresponding holes in the RJ-45 connector.

Thanks
 R.karthikeyan

 

Build Your Server Lab in the Cloud

Hi..

Today i have successfully created Windows Server 2012 on Cloud Environment... Herewith I am sharing you the Step-by-step.

Note that this lab requires a valid Windows Azure Subscription - You can sign up for a FREE trial from the Windows Azure Free Preview page.
To sign up for your Windows Azure Free Preview subscription, first select the closest location from the list below:
After you have signed up for your free trial account, click the try it now button on the preview features page in the Windows Azure management portal.

In this lab you will walk through the steps of provisioning a new virtual machine and enabling Windows Remote Management.
Creating a New Virtual Machine
In this task you will use the Quick Create VM creation option to provision a virtual machine to be customized.

1. Log in to the Windows Azure Management Portal using your Microsoft Account.
    
2. Click the +New button, then select Virtual Machine | From Gallery. 
    
   
    
3. Creating a Virtual Machine from Gallery 
     In the VM OS Selection page, click Platform Images and select Windows Server 2012. 
    
   
    
4. Virtual Machine OS Selection  
   In the Virtual Machine Configuration page, specify a unique description name for the VM Name and set both password boxes to a password of your choosing. Leave the VM Size with Small value. 
    
   
    
5. Virtual Machine Configuration Page
     In the VM Mode page, select Standalone Virtual Machine and set the DNS Name to a unique DNS name that you will use for connecting to this new virtual machine. Leave the remaining fields with their default values. 
    
   
    
6. Virtual Machine Mode page
     Finally, leave the VM Options page with the default values and finish the VM wizard. 
    
7. Wait until your Virtual Machine is created, you can check its status in the Virtual Machines page within Windows Azure Management portal. Then, click your Virtual Machine name to open the Dashboard. 
    
   
    
8. Virtual Machine Created
     In the bottom menu, click Connect to download an .rdp file to connect to the VM using Remote Desktop Connection. 
    
   
    
9. Connecting to the Virtual Machine
     Note: When asked for Login, specify the Administrator user account with the same password credentials you supplied in step 4 above.

You now have a new virtual machine that you can use for studying Windows Server 2012 in a lab environment!


  Thanks
R.karthikeyan

FREE Hands-on Training: Build Virtual Machines in the Cloud with Windows Azure - IT Pros ROCK! at Microsoft - Site Home - TechNet Blogs

With Windows Azure, you can easily deploy and run Windows Server and Linux virtual machines on your own virtual network in the cloud. These capabilities can be really useful for several scenarios, such as: building virtual lab environments, piloting new software in the cloud, deploying hybrid IT applications that connect on-premises datacenter workloads with cloud-based VM’s … and MORE!
With the FREE online training below, you’ll learn how to leverage these capabilities for your network environment with step-by-step hands-on guidance …

FREE Hands-on Training: Build Virtual Machines in the Cloud with Windows Azure - IT Pros ROCK! at Microsoft - Site Home - TechNet Blogs

Thanks
R.karthikeyan

DNS Interview Questions and Answers

 Interview questions and answers on DNS server  Windows 2003 and 2008

Q1. What is DNS?
 Domain Name System is a service that can be installed on any windows server operating system to resolve the Name to IPAddress and vice-versa. TCP/IP networks, such as the Internet, use DNS to locate computers and services through user-friendly names

Q2. What is DDNS?
 Dynamic DNS or DDNS is a method of updating, in real time, a Domain Name System to point to a changing IP address on the Internet. This is used to provide a persistent domain name for a resource that may change location on the network.

Q3. What are the resource records in DNS?

• A (Address) Maps a host name to an IP address. When a computer has multiple adapter cards and IP addresses, it should have multiple address records.
• CNAME (Canonical Name) Sets an alias for a host name. For example, using this record, zeta.tvpress.com can have an alias as www.tvpress.com.
• MX (Mail Exchange) Specifies a mail exchange server for the domain, which allows mail to be delivered to the correct mail servers in the domain.
• NS (Name Server) Specifies a name server for the domain, which allows DNS lookups within various zones. Each primary and secondary name server should be declared through this record.
• PTR (Pointer) Creates a pointer that maps an IP address to a host name for reverse lookups.
• SOA (Start of Authority) Declares the host that is the most authoritative for the zone and, as such, is the best source of DNS information for the zone. Each zone file must have an SOA record (which is created automatically when you add a zone).

Q4. What are a Forward and Reverse Lookup?

• Forward Lookup: When a name query is send to the DNS server against to IP address, it is generally said a forward lookup.
• Reverse Lookup: DNS also provides a reverse lookup process, enabling clients to use a known IP address during a name query and look up a computer name based on its address.

Q5. What is Primary zone?
This is the read and writable copy of a zone file in the DNS namespace. This is primary source for information about the zone and it stores the master copy of zone data in a local file or in AD DS. Dy default the primary zone file is named as zone_name.dns in %windir%\System32\DNS folder on the server.

Q6. What is Secondary zone?
This is the read only copy of a zone file in the DNS namespace. This is secondary source for information about the zone and it get the updated information from the master copy of primary zone. The network access must be available to connect with primary server. As secondary zone is merely a copy of a primary zone that is hosted on another server, it cannot be stored in AD DS.

Q7. What is stub Zone?
A stub zone is a read only copy of a zone that contains only those resource records which are necessary to identify the authoritative DNS servers for that particular zone. A stub zone is practically used to resolve names between separate DNS namespaces. This type of zone is generally created when a corporate merger or acquire and DNS servers for two separate DNS namespaces resolve names for clients in both namespaces.
A stub zone contains:
The start of authority (SOA) resource record, name server (NS) resource records, and the glue A resource records for the delegated zone.
The IP address of one or more master servers that can be used to update the stub zone.

Q8. What is Caching Only Server?
Caching-only servers are those DNS servers that only perform name resolution queries, cache the answers, and return the results to the client. Once the query is stored in cache, next time the query in resolved locally from cached instead of going to the actual site.

Q9. What is Aging and Scavenging?
DNS servers running Windows Server support aging and scavenging features. These features are provided as a mechanism to perform cleanup and removal of stale resource records from the server and zone. This feature removes the dynamically created records when they are stamped as stale.
By default, the aging and scavenging mechanism for the DNS Server service is disabled.
Scavenging and aging must be enabled both at the DNS server and on the zone

Q10. What is SRV record in DNS?
The SRV record is a resource record in DNS that is used to identify or point to a computer that host specific services i.e Active directory.

Q11. What is Forwarding in DNS?
A forwarder is a feature in DNS server that is used to forward DNS queries for external DNS names to DNS servers outside of that network. We can configure a DNS server as a forwarder to forward the name query to other DNS servers in the network when they cannot resolve locally to that DNS server.

Q12. What is Conditional Forwarding in DNS?
We can configure the DNS server to forward queries according to specific domain names using conditional forwarders. In this case query is forward to an IP address against a DNS domain name.

Q13. What are Queries types in DNS?

• Recursive Query: This name queries are generally made by a DNS client to a DNS server or by a DNS server that is configured to pass unresolved name queries to another DNS server, in the case of a DNS server configured to use a forwarder.
• Iterative Query: An iterative name query is one in which a DNS client allows the DNS server to return the best answer it can give based on its cache or zone data. If the queried DNS server does not have an exact match for the queried name, the best possible information it can return is a referral. The DNS client can then query the DNS server for which it obtained a referral. It continues this process until it locates a DNS server that is authoritative for the queried name, or until an error or time-out condition is met.

Q14. What are Tools for troubleshooting of DNS?
DNS Console, NSLOOKUP, DNSCMD, IPCONFIG, DNS Logs.

Q15. How to check DNS health?
Using the DCdiag.
i.e. (dcdiag /test:dns /v /e)

Hi If you found this post useful then kindly share your view and feed back through comments.

Thanks
R.karthikeyan

SQL Basics

This page contains the essential commands to manage databases using SQL (Structured Query Language). I use it as a refference for myself. I don't pretend to be an SQL-expert. Others may use this reference freely. If you have any suggestions or comments please e-mail me!
Some commands depend on the type of server used. I experiment with four servers, Microsoft SQL Server, Borland Interbase Server, MySQL Server en Sybase SQL Anywhere. If a command is specific to one server I indicate this with an icon.

1.  indicates that the command works with MS SQL Server.
2.  indicates the command works with Borland InterBase Server.
3. indicates that the command works with MySQL Server.
4. indicates that the command works with Sybase SQL Anywhere.

The commands are written in bold, italicised text (databasenames, fieldnames, values, ...) is up to you to fill in. Note also that I speak of fields and records instaid off rows and coloms. In MS SQL Server manuals I see they use rows and coloms, but I prefer to use fields and records, probably because that was the way in dBase and MS Access, which I used for years.

---

1. List all the databases on the server:

sp_databases 
show databases 

---

2. Select a database to use:

use databasename  

---

3. List tables in the database:

show tables 
sp_help There is another, more complicated, command to retrieve the names of the tables in the database, but where you get a list of ALL tables, including system tables using sp_help, you can specify to view only user defined tables
select * from SYSOBJECTS 
where TYPE = 'U'
order by NAME If you want to see the tables of an InterBase database use the menu "Metadata", "Show..." and select "Tables" in the "View Information on"-dropdown. Click "OK" and you see the tables of the active database. Using ISQL you can use show tables
 To see the tables in Sybase press functionkey F7.

---

4. List fields in a table:

describe tabelname 
sp_help tablename  To see the fields of an InterBase database use the menu "Metadata", "Show..." and select "Tables" in the "View Information on"-dropdown. Type the name of the database in the "Object name"-textfield. Click "OK" and you see the fields of the specified table. Using ISQL you can use show table tablename
 To see the fields in a Sybase table press functionkey F7, select the table and click "Columns".

---

5. Create a database:

create database databasename 
on
(name='databasename_dat',
filename='c:\mssql7\data\databasename_dat.mdf',
size=2mb,
filegrowth=1mb)
log on
(name='databasename_log',
filename='c\mssql7\data\databasename_log.ldf',
size=2mb,
filegrowth=1mb)

---

6. Create a table in the database en store values in it:

create table tablename    
(fieldname1 datatype1, fieldname2 datatype2, ...)
insert into tablename
values(fieldvalue1, fieldvalue2, ...)

---

7. Constraints:

Primary key: constraint constraintname primary key (non)clustered (fieldname1, fieldname2, ...)

Foreign key: constraint constraintname foreign key (fieldname) references tablename(fieldname)

No dulicates: constraint constraintname unique nonclustered (fieldname)

Check: Add values to the fieldnamelist in the 'create table'-command:
(fieldname1 datatype1 check (fieldname1 in ('value1', 'value2', ...)), fieldname2 datatype2, ...)

Default value: Add default value to the fieldlist in the 'create table'-command:
(fieldname1 datatype1, fieldname2 datatype2 default 'value', fieldname3 datatype3, ...)

---

8. Select all records from table:

select * from tablename    
order by fieldnameNote that this command could take some time and put a lot of stress on the processor and memory of the server if you try to view a large database this way. On my server (Pentium MMX 233 Mhz - 64 Mb memory) it took 1:25 minutes to retrieve 25000 records from a database and it gave warnigs of shortage in virtual memory, so please don't try this on a production database (;-). Usualy it is better to select fields and conditions to limit the amount of records and the stress on the server.

---

9. Select set of records from table:

select fieldname1, fieldname2, ... from tablename    
where fieldname = "condition"
order by fieldname
Comparison Operators that can be used in the condition are:

=, !=, <>, <, >, <=, >= and LIKE.

With LIKE you can specify e.g. all the names beginning with S as 'name LIKE "s%"'.
You can also use boolean operators to specify more then one condition (OR, AND, NOT, BETWEEN, IN). With 'BETWEEN' you give a minimum and maximum value to the condition, with 'IN' you can give a list of values.

Example:

select Firstname, Lastname, Department from Employees
where Lastname LIKE "Van%" AND
Badge BETWEEN 121990 and 141990 AND
Department IN ("Sales", "Logistics")
order by Lastname
This statement retrieves all the employees where Lastname begins with "Van", whose badgenumbers are between 121990 and 141990, who work at the Sales- or Logisticsdepartment, and displays their Lastname, Firstname and Dapartment ordered by Lastname.

Note theat the 'ORDER BY' statemant can have more then one fieldname and can also take 'asc' or 'desc' as last argument (for ascending or descnding order).

---

10. Retrieve unique values

select distinct fieldname from table    

---

11. Add records to a table:

insert into tablename (fieldname1, fieldname2, ...)    
values ('value1', 'value2', ...)
If you leave out the fieldnames then the values must match the number of fields in the table. If you only want to add a value to some fields you have to specify them and the values must mach the number of specified fields. The unspecified fields are filled with NULL or the default constraint defined in the table. You could concider to specify defaults like "unknown" or "N/A" to avoid a NULL-value in the field.

---

12. Updating records in a table;

update tablename    
set fieldname1='value1', fieldname2='value2', ...
where fieldname3 = condition
If you ommit the 'where'-statement the update will be performed on ALL the records in the table!

---

13. Deleting a record from a table

delete from tablename    
where fieldname=condition
Be arefull in using this command!! Ommitting the 'where'-statement will erae ALL the records in the table and leave you with an empty table!!!

---

14. Adding a field to the records in a table:

alter table tablename    
add fieldname datatype
The new field is filled with NULL and can be filled using the update-command after adding the field.

---

15. Changing the width of a field

alter table tablename 
alter column fieldname newdatatypealter table tablename   
modify fieldname newdatatype

---

16. Removing field from the records

alter table tablename 
drop column fieldnamealter table tablename   
drop fieldname

---

17. Combine two querries:

select fieldname from tablename
union
select fieldname2 from tablename2
order by fieldname
This union will remove all duplicates. To retain duplicates you have to use 'union all'

---

18. Basic SQL-functions:

select avg(fieldname) from tablename
Returns the arithmeticaverage of the fields.

select count(*) from tablename
where fieldname=condition
Returns the number of records that match the condition.

select max(fieldname) from tablename
Returns the largest value of fieldname.

select min(fieldname) from tablename
Returns the smallest value of fieldname.

select sum(fieldname) from tablename
Returns the summation value of fieldname.

select convert(newdatatype, fieldname) from tablename
converts one datatype into another.

---

19. String functions

ASCII returns ASCII code of leftmost character of a characterexpression.

CHAR converts an ASCII code to a character.

SOUNDEX returns four-digit code used to compare two strings with DIFFERENCE.

DIFFERENCE returns difference between values of two character expressions returned by SOUNDEX.

LEFT returns part of character string, starting at number of character from the left specified in the argument.

LOWER converts uppercase strings to lowercase.

UPPER converts lowercase strings to uppercase.

LTRIM removes leading spaces from a string.

RTRIM removes trailing spaces from a string.

CHARINDEX returns starting position of specified character expression in a string.

PATINDEX rerurns starting position of first occurence of a substring in a string.

REPLICATE returns multiple sets of characters specified in the first argument. The second argument specifies number of sets. (eg. select replicate ('a', 5) returns 'aaaa')

REVERSE returns reverse order of a string of characters.

RIGHT returns part of character string, starting at number of character from the right specified in the argument.

SPACE returns a string of spaces, length specified in argument.

STR converts numeric data to character data.

STUFF inserts a string into another string.

SUBSTRING returns a part of a string (arguments are startpoint and length).

+ (concatenetion) concatenates two or more strings.

---

20. Arithmetic functions:

ACOS, ASIN, ATAN, ATAN2, COS, COT, SIN, TAN, DEGREES, RADIANS, CEILING, FLOOR, EXP, LOG, LOG10, PI(), POWER, ABS, RAND, ROUND, SIGN, SQRT.

---

21. TEXT and IMAGE functions:

SET TEXTSIZE specifies number of bytes displayed for data stored as TEXT or as IMAGE.

TEXTPTR returns pointer to first database page of stoed text.

READTEXT extracts substring from data stored as TEXT or as IMAGE.

TEXTVALID check validity of textpointer.

---

22. Date functions:

DATENAME returns specified part of date as characterstring.

DATEPART returns specified part of date as integer value.

GETDATE returns current date and time.

DATEADD returns value of the date with an additional date interval added.

DATEDIFF returns difference between parts of two specified dates.

---

23. Views:

Note that a view under SQL is the same as a query you create with Access.

23.1 Create views:

create view viewname as
select fieldname1, fieldname2, ... from tablename1, tablename2, ...
where fieldname = conditionYou can't edit a view using SQL. You can use the Enterprise Manager of MS SQL to edit a view or you can delete a view and recreate it with the same name.
You can use alternative names for the columns in a view:
create view viewname (col1, col2, col3, ...)as
select fieldname1, fieldname2, ... from tablename1, tablename2, ...
where fieldname = condition

23.2 Display definition of a view:

sp_helptext viewnameTo prevent the possibility to view the definition of a view you can use encryption:
create view viewname with encryption as
select fieldname1, fieldname2, ... from tablename1, tablename2, ...
where fieldname = condition

23.3 Display associations of a view:

sp_depends viewname

23.4 Delete a view from the database:

drop view viewname1, viewname2, ...

23.5 Insert records through a view:

insert into viewname
values ('value1', 'value2', ...)You can insert rows through the view that DON'T match the WHERE-statement inthe view definition, but then you can't retrieve the new row with the view. If you want to prevent this you can use the check option:
create view viewname as
select fieldname1, fieldname2, ... from tablename1, tablename2, ...
where fieldname = condition with check option

23.6 Delete records through a view:

delete from viewname where fieldname = conditionYou can't delete records through a view that don't match the WHERE-statement in the view definition.

23.7 Updating records through a view:

update viewname set fieldname='value' where fieldname=conditionYou can update a record through a view so that it doesn't match the WHERE-statement anymore. You can't update a view if the updated columns belong to different tables.

---

24. Indexes:

24.1 Simple index:

create index index_name
on table_name (fieldname)

24.2 Unique index:

create unique index index_name
on table_name (fieldname)This kind of index enforces integrity of the table by disallowing duplicete values in the indexed field.

24.3 Clustered index:

create clustered index index_name
on table_name (fieldname)A clustered index forces SQL Server to phisicaly store the table data in the exact order of of the index. This improves the performance of the table. You can only have obne clustered index on a table, the selected fieldname should be choosen carefully and every table should have an clustered index.

24.4 Display index info:

sp_helpindex tablename

24.5 Deleting an index:

drop index table_name.index_name

24.6 Primary and foreign keys:

You can specify a primary key on a table while creating it (see constraint), or you can add a primary key to an existing table by altering it. alter table table_name add constraint constraint_name primary key

24.7 Display primary and foreign keys info:

sp_helpconstraint table_name

24.6 Deleting primary and foreign keys:

alter table table_name drop constraint constraint_name

---

25. Transaction:

A transaction is a series of SQL-statements performed at once, with the ability to undo the changes if something goes wrong during the processing of the statements. A transaction is enclosed in "begin tran" and "commit tran".Example:

begin tran
 update tablename
 set fieldname = newvalue
 where fieldname = condition

 update tablename2
 set fieldname =  newvalue
 where fieldname = condition

 if @@error != 0
 begin
  rollback tran
  print 'Error occured, no rows updated'
  return
 end
commit tran

You can nest one transaction ino another by using named transactions. Be shure however to include a 'commit tran' for every 'begin tran' to avoid leaving a transaction open.
Example:

begin tran tran1_name
 update tablename
 set fieldname = newvalue
 where fieldname = condition

  begin tran tran2_name
   update tablename
   set fieldnname = newvalue
   where fieldname = condition
   if @@error != 0
   begin
    rollback tran tran2_name
    print 'Failed to update tablename'
    return
   end
  commit tran tran2_name
commit tran tran1_name

---

26. Rules:

A rule controls the values that can be entered into a field of a table. To apply a rule to a field you have to take two steps: create the rule and bind the rule to the field.

26.1 Create a rule:

create rule rule_name
as @fieldname in ('value1','value2','value3')

26.2 Bind a rule:

sp_bindrule 'rule_name', 'table_name.field_name'

26.3 Unbind a rule:

sp_unbindrule table_name.field_name

26.4 Drop a rule:

drop rule rule_name
!!You first have to unbind the rule before you can drop it!!To display rule bindings you can use 'sp_help tablename', to display the rules you can use 'sp_helptext rule_name' and to rename a rule you can use 'sp_rename rule_name, new_name'.

---

27. Defaults:

A default is create to provide a value for a field if the user doesn't fill in one. Just like creating rules you have to make to steps to apply ad efault: create it and bind it to a field in a table.

27.1 Create a default:

create default default_name as value

27.2 Bind a default:

sp_bindefault default_name, 'tablename.fieldname'

27.3 Unbind a default:

sp_unbindefault 'tablename.fieldname'

27.4 Drop a default:

drop default default_name
!!You first have to unbind the default before you can drop it!!To display default bindings you can use 'sp_help tablename', to display the default you can use 'sp_helptext default_name' and to rename a default you can use 'sp_rename default_name, new_name'.

---

28. Stored procedures:

Stored procedures are routines or series of SQL-commands that run on the server side. The benefits are performance, because the server typically is the most powerfull computer on the network, and security, because you can control add/change/delete/list operations.

28.1 Create a stored procedure:

create procedure procedure_name
as procedure_commandsTo execute the procedure you use 'exec procedure_name'. You can use parameters in procedures to pass values to the procedure:
create procedure procedure_name (@p1 type, @p2 type, ...)
as insert into table_name
values (@p1, @p2, ...)
Now you can execute this procedure by passing values to it:
exec procedure_name (value1, value2, ...)
You can also use variables in a procedure. You first have to declare them (declare @var_name var_type) and then you can assign a value to it (select @var_name = expression).

28.2 Display a stored procedure:

sp_helptext procedure_name

28.3 Delete a stored procedure:

drop procedure procedure_nameIf you want to alter a procedure, you first have to drop it and then recreate it under the same name.

28.4 Procedure Auto Execution:

You can automaticaly run a procedure every time the server is started by using:sp_makestartup procedure_name
sp_unmakestartup procedure_name removes the procedure from the startupprocedures
sp_helpstartup displays the procedures currently running at startup

28.5 Flow-control statements:

The following statements can be used in stored procedures to control the flow of the procedure:

• if ... else ...
• begin ... end
• while ...
• break
• continue
• print
• goto
• return
• raiserror
• waitfor
• case ... when

---

29. Datatypes:

This is an overview of the different datatypes used by the different servers.

MySQL	Borland InterBase Server	MS SQL Server	Sybase SQL
tinyint	-	tinyint	tinyint
smallint	smallint	smallint	smallint
mediumint	-	-	-
int	int	int	int
bigint	-	-	-
-	numeric	numeric	numeric
decimal	decimal	decimal	decimal
-	-	real	real
float	float	float	float
double	double precision	-	double
date	date	-	date
-	-	smalldatetime	-
datetime	-	datetime	-
timestamp	-	timestamp	timestamp
time	-	-	time
year	-	-	-
char(n)	char(n)	char(n)	char(n)
varchar(n)	varchar(n)	varchar(n)	varchar(n)
-	-	-	long varchar
tinytext	-	-	-
text	-	text	-
-	-	ntext	-
mediumtext	-	-	-
longtext	-	-	-
-	nchar(n)	nchar(n)	-
-	nchar varying(n)	nvarchar(n)	-
-	-	binary(n)	binary(n)
-	-	varbinary(n)	-
-	-	-	long binary
tinyblob	-	-	-
-	-	image	-
blob	blob	-	-
mediumblob	-	-	-
longblob	-	-	-
enum	-	-	-
set	-	-	-
-	-	bit	-
-	-	uniqueidentifier	-
-	-	money	-