Creating a table in SQL server 2005

After creating a database, you need to create tables in your database.Its as simple as creating the database. Expand the database in which you want to create tables in and select the tables tab. Right click on the tables tab to get an option "New Table".

When you click the new table option, you are presented with the column creation page. In this page the column names, datatype and null or not null characters can be specified.

Although you can create columns with spaces, it will cause issues later as it cant be used in many operations. So dont leave spaces in column names.

After adding the required number of columns the table can saved by clicking on the save button.

The table is saved to the database and can be seen under the tables tab under the database you had selected.

Three types of constraints can be set on columns for restricting the entry of data into them.They are:

1. Check Constraint - This is used to set a constraint against which data is validated before entry
   
2. Default Constraint - This sets a default value to be filled in a column when no value is specified by the insert command
   
3. Unique Constraint - when the unique constraint is set on a column, it cant have duplicate values.
   

A check constraint can be set by selecting the "Constraint" tab under the database of choice. A Check Constraint box opens up asking you to specify the Constraint expression.

Ex: (column_name like '[0-9]')

once the constraint is added, only values that match the constraint can be added to the table. In this case only values from 0 to 9 can be added to this column.

The default value can be set by selecting the table in design mode and choosing the column on which the default value has to be set. This can be done while creating the table as well. The Default value or binding can be set as below


The unique constraint can be set by running the update query to set a column as unique.

EX: Alter Table_name Add Constraint Constraint_name Unique(column_name)

SQL server 2005 - recovery model

The SQL server 2005 has 3 recovery models. These are as follows:

1. Full - The full recovery model is used in production settings as default recovery model. In this recovery model the database as well as the transaction logs are backed up. So the database can be recovered back to any point in time.
   
2. Bulk Logged - The bulk logged recovery model can be used while doing bulk insert."BULK INSERT" will insert large amounts of data from various types of files.When the bulk logged recovery model is set the bulk inserts are not saved in the transaction logs, as it can be very taxing to save in the transaction log.
   
3. Simple - This does not take any transaction log back up. Suited for a development environment.

While doing bulk inserts, the recovery model has to be changed from full to bulk logged. After finishing the bulk insert, the database has to be backed up and the recovery model has to be set back to full recovery model.

Create a database using SQL server 2005

The SQL Server Management Studio Express can be downloaded for free and installed after installing MSXML 6.0. This can be used to connect to the sql server installation. The login screen such as is one comes when you open the Management Studio express.


The authentication mode, server name, username and password have to be filled up to login to the server.The options about the connectivity can be filled into suit your needs. These are network protocol, packet size, connection time-out,Execution time out and also encryption. After connecting to the server, select the "Databases" in the object Explorer and right click to get a list of options. The first otpion is for creating a New database. Select the option to create a new database as given below.

The SQL New database options screen is opened up. The name, location and size of the data and transaction log files can be chosen in the General screen.

The Growth option and owner can also be set in the general option. If you want to add/remove files to the database, they can be added /removed using the add/remove button at the right bottom corner.
Various other options such as the recovery model, file groups and other options can be set while creating the database, but can also be changed later.Each of these options are important and need to be dealt with separately. To start with using the defaults does not cause any problems.

SQL server 2005 error and usage report

The SQL server install has an option to send error and usage reports to Microsoft. The option to send or not send is prompted during install. The screen presented during install is rather plain.

The option can be changed after installation is completed. In the programs menu select Microsoft SQL server 2005. Select Configuration tools in Microsoft SQL server 2005.In that select "Error and Usage Report Settings".

Details such as the location of error and usage reports can be specified for each of the different instances.


The error and usage reports can be sent only for a specific instance or all. You can also check the Privacy statement of SQL server 2005 from Microsoft in this screen.

However, will this report generation have a performance impact due to the extra overhead is something that is to be checked.

Uninstall SQL server by instance

If you have installed SQL server, you will want to remove it from the system. Its as easy as removing a program using "Add or Remove programs" from Control Panel. Just remove the Microsoft SQL server 2005.

If one or more instances of the server are installed on the same machine, the instances can be removed individually or as a whole. The option to choose the instances to be removed is provided.

The various components that need to be removed can also be selected for selective removal. The screen to choose is as below.
After the various components that are to be removed and the instances that are to be removed are ticked, the confirmation screen showing components and instances that will be removed are shown. After confirmation the uninstall is done. If two or more instances are running, one instance can be removed without affecting the execution of another instance. However, its better to have a fall back plan in case the other instnace is affected due to system hanging.

SQL Server 2005 Express Edition Install - Part -3

More than one instance of the SQL server can be installed on the same machine. After finishing the previous steps, the option to specify if you want a default install or a named instance can be specified. Note that two default installations or two instances with same name cant be installed. For a list of installed instances, click the "Installed Instances" button. After the Exsisting components screen, the Service account can be configured. There are four types of Service accounts. Check the microsoft documentation for a detailed explanation.

1. The "Domain User account" is used when the "service must interact with network services"


2. "Local Service account" has "same level of access to resources and objects as members of the Users group"


3. "Network Service account" is same as local service account, but the network services can be accesed using the credentials of users group.


4. "Local System account" is a highly privileged account and must be used with caution!!
   The services that are to be started at end of installation can be choosen in this screen itself.

The next screen allows the "Authentication Mode" to be choosen.If the server is to be on a network with all systems having a Windows domain account, the Windows authentication mode can be choosen, else the Mixed mode has to be selected and a password has to be specified for "sa" logon.

The colation settings define the sorting behaviour for the server and are rather important.
Generally the colaltion settings are of utmost importance when upgrading from a previous version of SQL server.

SQL Server 2005 Express Edition Install - Part 2

The system configuration check is initiated after the previous steps are completed. The number of errors and warnings are listed after the config check is done. Its ok if you have warnings, you will not be stopped from going ahead with the install. However, if errors are encountered the install cant be completed. The errors have to be remedied before the instalaltion can go ahead. The messages provided beside the status can be very useful in resolving the errors.

The install gets intresting from the next screen :). You have to provide some registration information Name and Company. Well thats not the intresting part, below the comapany name there is a check box thats ticked by default saying "Hide Advanced Configuration options". Make sure you uncheck this check box if you plan on configuring the service account type or collation settings. The next screen allows us to select which components to install. The amount of space each component takes up can be checked before installing the component.

The "database serices" installs the SQL server Database engine, tools for managing relational and XML data and replication.
The client components installs command line tools, connectivity components, programming models, management tools and development tools.Choose the components that are best suited for your needs.

SQL Server 2005 Express Edition Install

The SQL Server 2005 Express Edition is available for free download.Make sure you have the ".Net Framework 2.0" on your system before running the SQL server 2005 installation.The files start getting extracted as soon as the installation file is opened. After the extraction of files is completed the license page is presented.Tick the "I Accept" condition after reading the license! The prerequisites are installed and the componenets installed are listed.

After the "Next " button is clicked, the system is scanned for coniguration settings.The welcome screen is presented after the system configuration is scanned.

The setup files have now been copied to the local machine from the installation media, either the disk or the installation file.

The instalaltion and configuration will start from the next step. Its better to make sure that the system meets all hardware requirements before moving on to the next step.