Archive for January 2012

SQL -- Create Table ( Part2)

In this blog, we will see how to create a table from UI (Object explorer).

Let's assume that you want to store your friends Name and contact details in a table in Database Test.

Follow the below steps shown in screen shot to create Friends table.

Step 1:

Go to Databases in your server.

Step 2:

Select database where you want to create table.
Here, for sample I selected database with name 'TEST' .

Step 3:
Right Click on Tables :















Click on New Table .

Following screen will be displayed.






Step 4:

Enter column names.
In this scenario one column is required to hold friend name and other column to hold his/her contact number.







Check  Allow Nulls Check box only if you want to allow that to accept nulls. That is, select Allow Nulls only if you want to insert data into table without providing value in that particular column.

Step 5:

Save your table after you enter all the required columns in the tables ( As shown in above screen shot).

Select File --> Save .
 Else, Select save icon.

A pop up will be displayed as shown below.












Enter the table Name ( Name with which you want to save your table ).

Here, My table name is "FRIENDS".

After entering the table name, Click OK in the pop up window.

Now, Your Table  ( FRIENDS) is created successfully.

Step 6:

Checking whether table is created or not.

Expand Tables on the object explorer ( left side pan). If table is created ,then you will be able to see the table name in the list.


















Expand FRIENDS table , then columns information will be displayed.

Though we can create tables from UI (User Interface ) , its not well used except for simple table creations.

Posted in | Leave a comment

SQL COMMANDS -- CREATE TABLE1

In the previous blog, database creations is explained.
Now, We will see how to create tables in a database using SQL Command.

Let's assume that you want a store your friends names and their contact numbers in your SQL database.

Here is the command for the above scenario:

CREATE TABLE FRIENDS
(
FRIEND_NAME
VARCHAR(25),

CONTACT_NUMBER INT
)

Typical Create table Sytax is:

CREATE TABLE <Table Name>
(

[Column1]            [data type],
[Column2]            [data type],
[Column3]            [data type],
[Column4]            [data type],
.....................and so on
)


High level syntax for the table creation is :



CREATE TABLE
    [ database_name.[ owner ] . | owner. ] table_name
    ( { < column_definition >
        | column_name AS
computed_column_expression         | < table_constraint > } [ ,...n ]
    )

[ ON { filegroup | DEFAULT } ]
[ TEXTIMAGE_ON { filegroup | DEFAULT } ]

< column_definition > ::= column_name data_type
    [ COLLATE < collation_name > ]
    [ [ DEFAULT constant_expression ]
        | [ IDENTITY [ ( seed , increment ) [ NOT FOR REPLICATION ] ] ]
    ]
    [ ROWGUIDCOL]
    [ < column_constraint > ] [ ...n ]

< column_constraint > ::= [ CONSTRAINT constraint_name ]
    { [ NULL | NOT NULL ]
        | [ { PRIMARY KEY | UNIQUE }
            [ CLUSTERED | NONCLUSTERED ]
            [ WITH FILLFACTOR = fillfactor ]
            [ON {filegroup | DEFAULT} ] ]
        ]
        | [ [ FOREIGN KEY ]
            REFERENCES ref_table [ ( ref_column ) ]
            [ ON DELETE { CASCADE | NO ACTION } ]
            [ ON UPDATE { CASCADE | NO ACTION } ]
            [ NOT FOR REPLICATION ]
        ]
        | CHECK [ NOT FOR REPLICATION ]
        ( logical_expression )
    }
< table_constraint > ::= [ CONSTRAINT constraint_name ]
    { [ { PRIMARY KEY | UNIQUE }
        [ CLUSTERED | NONCLUSTERED ]
        { ( column [ ASC | DESC ] [ ,...n ] ) }
        [ WITH FILLFACTOR = fillfactor ]
        [ ON { filegroup | DEFAULT } ]
    ]
    | FOREIGN KEY
        [ ( column [ ,...n ] ) ]
        REFERENCES ref_table [ ( ref_column [ ,...n ] ) ]
        [ ON DELETE { CASCADE | NO ACTION } ]
        [ ON UPDATE { CASCADE | NO ACTION } ]
        [ NOT FOR REPLICATION ]
    | CHECK [ NOT FOR REPLICATION ]
        ( search_conditions )
    }

Above syntax includes creation of Primary, foreign keys ,constrains and indexes etc.,
All the above attributes of table will be discussed in further sessions.

In this blog, we learnt simple table creation steps so far.

Posted in | Leave a comment

SQL COMMANDS -- DataBase Creation

Every body starts with the select  command in SQL Server. But, how can somebody execute a select command without a database and table. So, In this blog we will see how to create database.

New Database can be created in 2ways.

Database Creation in Object Explorer:
  1. In Object Explorer, connect to an instance of the SQL Server Database Engine and then expand that instancee.
  2. Right-click Databases, and then click New Database.
  3. In New Database, enter a database name.
  4. Enter other details and click OK to create a new database.
  5. To change the default setting, go to Options tab in the below screen.
  6. In Options tab, you can change the Collation, Recovery model and compatability level.
  7. Files groups (secondary etc.,) can be added in the Filegroups Tab.





















After making all required changed Click 'OK' to create a new Database.

Database Creation in Query Window:
Though we can create datbase using object explorer, thats not frequently used.
Many DBA's use query window to create database.

Simple command to create a database with default settings is :

                 CREATE DATABASE TEST

TEST is my database name.

Syntax:  CREATE DATABASE <Database Name>

Database Name can be a maximum of 128 characters.


Here is the typical Database creation query :

CREATE DATABASE [TEST] ON PRIMARY 
( NAME = N'TEST', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\TEST.mdf' , SIZE = 1216KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )LOG ON
( NAME = N'TEST_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\TEST_log.LDF' , SIZE = 504KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)


In the above query Primary data file is created in the path  C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\TEST.mdf

Data and log in SQL Server are stored in mdf files.

Size of the above TEST database is 1216KB and its maximum size is unlimited. Once the data in database exceeds 1216KB , then it will ve incremented by 1024KB and this process will go on.
Thats is database will auto grow to accomadate new data.
Some of the important points about Database:

  1. A database can have only one primary file.
  2. The CREATE DATABASE statement must run in autocommit mode
  3. By Default Auto growth property of Database is set to True.i.e., Database will expand automatically once it reaches its maximum limit. Once the database is created corresponding mdf file will be created in the primary location path:Here is the screen shot for the database creation of TEST Database mdf file ( Both log and prmiary file ).
To confirm whether your database is created or not, follow the below steps:

Use following command to select a database.

USE TEST

Where TEST is the database name. If you command is executed without errors, then your database creation is successful.

Typical Database selection Query :

USE <database Name >


Now, your TEST  database is ready. Start exploring various features and properties of database now. In next blog we will continue will the remaining SQL commands.

Posted in | Leave a comment

SQL SERVER Interview Questions

Q)  What are all the system database?

A)  We have 6 system databases:
  • Master
  • Model
  • MSDB
  • TempDB
  • Resource
  • Distribution
Master, Model, MSDB and TempDB are frequently used system databases.
Resource is not visible in server. They will be internally stored in server.
The distribution database is present only if you've configured replication and specified that the server act as a distributor. The

Q)  How to find the size of a database?

A)  We can find the size of the database using many ways. Some of them are:
  1. sp_helpdb is a better command which provides complete information about databases.
  2. sp_database will also provide the database size.
  3. sys.master_files table stores information related database.
            Use the following query to find the size of the database:
                       SELECT DB_NAME(database_id) AS DatabaseName,
                       Name,  (size*8)/1024 SizeMB
                       FROM sys.master_files
                       WHERE DB_NAME(database_id) = ‘master’

Q)  Which command will delete the backup history from msdb?

A)  Executing sp_delete_backuphistory stored procedure will remove backup history from database.



Q)  Can we create user objects in system database?

A)  Yes, We can. But its not recommended to create objects in System databases.


Q)  How can you confirm your server have Resource database in it?

A)  This databse is by all means hidden from the user. You can't view it in Object Explorer nor with the use of sp_helpDb or selecting from a sys.databases view.

So how do we know it's even there? Pretty simple. Go to the Data directory of your SQL installation where the databases are put by default.

In my system data directory is in the following path:
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data

Here is the screenshot for the Resource database:

Posted in | 3 Comments

SQL Server System Databases

SQL Server 2008 (and 2005) contain five System databases:  
  1. master
  2. model
  3. msdb
  4. tempdb
  5. mssqlsystemresource (Resource)
As their name implies, they are part of the system, and most of them are required for the server to run properly.These databases are used by SQL Server for its own maintenance and management.In addition to system databases, there are also system tables, stored procedures, functions, and other system objects

Following picture dipicts how server looks like with the sysytem databases:




















Resource is a hidden, system database that contains all system objects included with SQL Server 2005/2008 Express.

master:
The master database contains all of the system level information for SQL Server – all of the logins, linked servers, endpoints, and other system-wide configuration settings. The master database is also where SQL Server stores information about the other databases on this instance and the location of their files. If the master database is not present, SQL Server cannot start. master databse contains the following tables.These tables hold the login, linked server , endpoints and other system information.

Note: Size of master primary data & master log are 11 MB  and 1.25MB respectively.



















model:
Model is essentially a template database usedin the creation of any new user database created in the instance. User defined tables, stored procedures, user defined data types, etc can be created in the Model database and will exist in all future user defined databases .The database configurations such as the recovery model for the Model database are applied to future user defined databases. Model Database template is applied across all the databases in the server.

Note: Size of model primary data & model log are 0.75 MB each.

msdb:The msdb database storesinformation regarding database backups, SQL Agent information, DTS packages,SQL Server jobs, and some replication information such as for log shipping.

Here are some of the msdb system tables:

























One important item is that msdb holds backup history. Using the msdb tables, it’s possible to determine when each database and filegroup was last backed up. backupset is the table which stores this information.

sp_delete_backuphistory will remove the backup history from msdb.

Note: Size of msdb primary data and msdb log are 12.0 MB and 2.25 MB respectively.

tempdb: This database stores temporary tables (#temptable or ##temptale), table variables, cursors, work tables, row versioning, create or rebuild indexes sorted in TempDB, etc. Each time the SQL Server instance is restarted all objects in this database are destroyed, so permanent objects cannot be created in this database. This database is recreated every time SQL Server starts, andthe objects contained in it will be based upon the objects defined in the modeldatabase. In SQL Server 2005, the tempdbdatabase has taken on an additional workload; it is used as the version storefor features such as the new snapshot isolation levels and online indexingoperations.A properly sized and configured tempdb is vital for effective SQL Server performance.

Note: Default size of tempdb primary data and tempdb log are 8.0 MB and 0.5MB respectively.

Resource:  This is a Read-only database that is not accessible via the SQL Server 2005 tool set .The Resource database is responsible for physically storing all of the SQL Server 2005 system objects. This database has been created to improve the upgrade and rollback of SQL Server system objects with the ability to overwrite only this database. The database ID for the Resource database is 32767. The Resource database does not have an entry in master.sys.databases

Using the following query you can see what are all the databases present in SQL Server:








The best way to learn SQL Server is to explore how things work in the systemdatabases. As a general rule, it is not recommended to directly query thesystem tables in SQL Server; however, you can learn a lot about how SQL Serverworks by exploring the tables in these system databases.

Leave a comment

Getting Started With SQL Server

In my previous blog I listed the differnce between Various versions of SQL Server and installation steps for SQL Server.

In this blog we will see how to start working with SQL.

Opening SQL Sever Instance:

Follow the below steps to Start Open SQL Server Instance.

Start -- > All Programs -- >MicroSoft SQL Server 2005 ( or what ever the instance you have in your system ) -- >SQL Server Management Studio

A popup will be displayed asking for the credentails:

















Enter your credentials in the above Window.
SQL Server has its own built in system for security that covers logins and roles.There are 2 types of authentications in SQL Server.
  • Windows Authentication
  • SQL Server Suthentication
Windows Authentication: When you are accessing SQL Server from the same computer it
is installed on, you shouldn't be prompted to type in an username and password. In Windows Authentication server uses the system login credentials to enter into Database.Ideally, Windows authentication must be used when working in an Intranet type of an environment.

 SQL Server Suthentication:SQL Authentication is the typical authentication used for various database systems, composed of a username and a password.Obviously, an instance of SQL Server can have multiple such user accounts (using SQL authentication) with different usernames and passwords. In shared servers where different users should have access to different databases, SQL authentication should be used.Also, when a client (remote computer) connects to an instance of SQL Server on other computer than the one on which the client is running, SQL Server authentication is needed.

Here are the differences between SQL Server and Windows Authentication:


Enter into the Database with your credentials.









We will learn about System Databases in the next blog.

Leave a comment

SQL SERVER Installation Steps

In this session, I will take you to the step by step installation process of SQL Server. Here, I will show you the SQL Serer 2008 installation steps. It will be almost same for the other versions of SQL Server.

Before you start the installation, you’ll need to install the .Net 3.5 Framework. This comes pre-installed on Windows 2008 Server, but for earlier versions of Windows, you’ll need to install it first. This is a straightforward pre-requisite and is usually included as part of the SQL Server 2008 installation.

 .Net 3.5 Framework need not be installed if you are planning to go for SQL Server 2005 or SQL Server 2000 or its earlier versions.


Copy the SQL Server directory to a path in C Drive.














Double click Setup.exe file








Following screen will be displayed after installation starts:




















Select Installation link:









you will see the following screen while the installation is in progress:








We need to have product key to install the licensed version of SQL Server. Enter the SQL Product key which you get along with the license. If it is free trail version, you need not enter product key. You can use the trial version of SQL for 90days. After 90 days you need to get license if you still want to use SQL Server in your machine.













Once you enter valid Product key and press ok you will see the below screens.










Press Next -- > Next for all the consequent steps.

When Feature Selection screen is displayed, select all the features which you will be using in your process.

Its suggestable to install all the features . If all the features are available in your machine some or the other point of time you will get interest to learn new things and will get a chance to explore various features of SQL Server.





















Select the installation directory where SQL Server 2008 needs to be placed.





























































Select password in the above step.












Once the installation is complated , the following pop up will be displayed.







Restart you system and then check whether SQL Server 2008 is installed properly or not:

Follow the below steps to check whether SQL Server is installed properly or not in your system:

  1. Go to Start in your system
  2. Navigate to All Programs
  3. Select Microsoft SQL Server 2008
  4. Select SQL Server Management Studio

Click No when the following window is displayed.
Following Screen will be displayed if SQL Server is installed properly in your system.


Enter your credentials and login .This Confirms that SQL Server is installed in you system.
Now, you can start working on SQL Server 2008.

All the Best!!

Posted in | 2 Comments

Introduction to SQL SERVER

SQL is an acronym for Structured Query Language. It is a standard language for accessing databases. Microsoft SQL Server is a relational database server(DBMS), developed by Microsoft. It stores and retrieves data as requested by other software applications, be it those on the same computer or those running on another computer across a network.


Microsoft has launched various versions of SQL SERVER since it entered enterprise-level database market.Some of the popular versions of SQL SERVER are:
  • SQL Server 2000
  • SQL Server 2005
  • SQL Server 2008
  • SQL Server 2008 R2 (SQL Server 2010)
  • SQL Server 2012
Microsoft SQL Server 2000 is a full-featured relational database management system (RDBMS) that offers a variety of administrative tools to ease the burdens of database development, maintenance and administration.Some of the administrative tools in SQL Server 2000 are Enterprise Manager, Query Analyzer, SQL Profiler, Service Manager, Data Transformation Services and Books Online. We will discuss about these tools in deteail in coming sessions.


Microsoft SQL Server 2005  released in October 2005, is the successor to SQL Server 2000. Following are the new features added in SQL SERVER 2005 : .
  • ETL tool (SQL Server Integration Services or SSIS or Business Intelligence Studio)
  • SQL CLR
  • Reporting Server
  • OLAP and data mining server (Analysis Services)
  • Messaging technologies like Service Broker and Notification Services.
  • SQL Server 2005 Supports Xml Data type.
  • It has new indexing algorithms, syntax and better error recovery systems.
Microsoft SQL Server 2008  released in August 2008, is the successor to SQL Server 2005.
Enhancements in SQL Server 2008 are:
  • New data type FILESTREAM is introduced in SQL Server 2008.Structured data and metadata about the file is stored in SQL Server database, whereas the unstructured component is stored in the file system.
  •  Full-text search functionality is newly added in SQL Server 2008.This simplifies management and improves performance
  • Flat Earth and Round Earth are newly introduced data types to represent Geometric and Geographic data.
  • SQL Server includes better compression features, which also helps in improving scalability.
  • SQL Server 2008 supports the ADO.NET Entity Framework and the reporting tools, replication, and data definition will be built around the Entity Data Model.
Microsoft  SQL Server 2008 R2 or Microsoft  SQL Server 2010  was released on April 2010. New features in SQL Server 2008 R2 are :
  • Master data management or Master Data Services : Its a central management of master data entities and hierarchies.
  • Multi Server Management, a centralized console to manage multiple SQL Server 2008 instances and services including relational databases, Reporting Services, Analysis Services & Integration Services.
  • SQL Server 2008 R2 includes a number of new services, including PowerPivot for Excel and SharePoint, Master Data Services, StreamInsight, Report Builder 3.0, Reporting Services Add-in for SharePoint, a Data-tier function in Visual Studio that enables packaging of tiered databases as part of an application, and a SQL Server Utility named UC (Utility Control Point), part of AMSM (Application and Multi-Server Management) that is used to manage multiple SQL Servers.
Microsoft  SQL Server 2012 is the latest version of SQL Server .It was announced to be last version to natively support OLE DB and instead to prefer ODBC for native connectivity. This announcement has caused some controversy.


Though we have many versions of SQL Server , 2005 and 2008 SQL Server versions are popular.

Here is the comparision of various SQL server versions:



Posted in | 5 Comments

Introduction to Database Management System

Data is some piece of information that should be stored for future manipulations. The system which provides such a facility is called Database Management System or DBMS.
Data for future purpose can be saved in either a text file or excel sheet or in some other place.

The simplest form to store a data for later retrieval is using a text file.
Example : you may want to store your friends name and phone numbers to use it latter.This kind of storage is called flat file storage or unstructured storage.

But ,flat files are not suitable to large data such as storing employee details in an organization. To overcome this we need some system which should perform the storing,retrieving, manipulating and querying operations on the data and give output to us. This kind of system is called Database Management System.

DBMS : Database Management System which organizes and stores data in a structural way for fast retrieval and for data manipulations.

Some of the well known Database Management Systems are Microsoft SQL Server, Sybase, Oracle, IBM DB2, PostgreSQL, MySQL and SQLite.


Every DBMS has it is own language to write the commands. All database queries are standardized under a common language called Structured Query Language(SQL). SQL is a language used to storing,retrieving, manipulating data in a relational database.SQL also contains statements for defining and administering the objects in a database. SQL is the language supported by most relational databases.

All most all the databases store information in tables. Some of the frequently used terms in SQL are:
Column – A finite unit of data. It is represented by one of the data type.
Row – A collection of Columns.
Tables – Collection of rows.
Database – Collection of tables and other objects.

Posted in | 1 Comment