Learn Ms SQL 2005

Page 1

Delete all the rows from all the tables in SQL Server

If you are in situation where you just want empty database structure, without having data in it.

Run following select statement which will generate set of delete statement to delete all the records for all the tables in your database.

SELECT
'Delete from ' + Table_Catalog + '.' + Table_Schema + '.' + Table_Name + ';'
FROM INFORMATION_SCHEMA.TABLES
WHERE Table_Type = 'BASE TABLE'
ORDER by TABLE_NAME

In case your database is large and you want to know status of which table is currently getting deleted you can use following:

SELECT
'Print(''Delete started for ' + Table_Catalog + '.' + Table_Schema + '.' + Table_Name + ''');' +
'Delete from ' + Table_Catalog + '.' + Table_Schema + '.' + Table_Name + ';' +
'Print(''Delete done for ' + Table_Catalog + '.' + Table_Schema + '.' + Table_Name + ''');' +
'Print(''.............'');'
FROM INFORMATION_SCHEMA.TABLES
WHERE Table_Type = 'BASE TABLE'
ORDER by TABLE_NAME

SQL Server Performance Tuning and Query Optimization Videos

If you are like me, who don't get much chance to get your hands dirty in fine tuning sql server queries, then you must watch this videos.

I am really thankful to this guy, who has posted such a useful videos.

http://www.youtube.com/playlist?list=PL2900t3sPCl1MZi88lYsRLUcSled8wAMU

Frankly speaking their is lot of materials out their on this topic and I always avoid learning because of that. This videos helped me to quickly get started to attack problem I was facing.

If you landed here searching how to improve performance of your website then along with database sql indexing you should also look for this checklist.
http://dotnetguts.blogspot.com/2012/09/all-about-aspnet-website-performance.html

Adding Column to SQL Server using Database Defensive Programming Technique

Recently I have learned good way to add column to sql server using database defensive programming technique from my co-worker. All the credit for this blog post goes to him. Thank you sir incase you are reading this blog post. (I have purposefully avoid mentioning name of co-worker due to privacy reason.)

Following example is very simple and self explanatory, Incase if you didn't get anything then pass your comment in comment section.

BEGIN TRANSACTION
IF EXISTS(SELECT 1 from information_schema.tables 
          where table_name = 'MyTableName')
  BEGIN
    Print('Table Exist');

    --Add Column MyColumn
    IF NOT EXISTS(SELECT 1 from information_schema.columns 
                  where table_name = 'MyTableName' 
                  and Column_Name='MyColumn')
     BEGIN
 ALTER TABLE MyTableName ADD MyColumn varchar(345) NULL;
 Print('MyColumn Column Added');
     END

    ELSE
     
     BEGIN
 Print('MyColumn Column Already Exist');
     END



  END

Else
    BEGIN
  Print('Table does not Exist');
    END


IF @@ERROR <> 0
    BEGIN
        PRINT('Problem in running script, Rolling back');
        ROLLBACK TRANSACTION;
    END
ELSE
 BEGIN
  PRINT('Script Run Successfully');
        COMMIT TRANSACTION;
 END

OCIEnvCreate failed with return code -1 but error message text was not available. - Solution

Recently I was facing an issue with connecting to oracle. I was keep on getting below error when trying to connect oracle through .Net Code.

Error: OCIEnvCreate failed with return code -1 but error message text was not available.

Cause of Error:
I am using Win 7 machine with Oracle Installed on it and using Oracle SQL Developer. Few days back IT team at my company push updates and for some reason this updates image has uninstalled Oracle client on my machine. Oracle Client being not installed on my system was the cause of this error.

Solution:
Check for Start > All Programs > Oracle - OraClient11g_home_64bit > Application Development > and Click on SQLPlus Icon.

If that Icon brings up following error, then you need to reinstall Oracle Client in order to pass through this error.

The drive or network connection that the shortcut 'SQL Plus.lnk' refers to is unavailable. Make sure that the disk is properly inserted or the network resource is available, and then try again.

If you are able to see "SQL Command Prompt" on your machine, then you do have good copy of Oracle Client installed on your machine, so you need to verify your .Net code to see whether proper connection string is assigned and so on...

Following link might also help you...

Hope this helps you in narrow down your problem.

Error: Cannot insert explicit value for identity column in table 'Users' when IDENTITY_INSERT is set to OFF

I run into situation where i have to insert explicit value for identity column and run into following error, which is expected. Incase you run into similar situation here is the solution to insert value into Identity column in SQL Server.

Error: Msg 544, Level 16, State 1, Line 1
Cannot insert explicit value for identity column in table 'Users' when IDENTITY_INSERT is set to OFF.

Cause of error: Trying to insert record including ID field where ID field is IDENTITY Column.

Solution:
1) Make sure that you are in situation where you really want to insert ID, If that is not the requirement than simply remove ID field from your insert statement and try to execute the insert statement again.

2) Since i was sure that i want to insert ID in Identity column, here is solution...

SET IDENTITY_INSERT YourTableName ON

INSERT INTO YourTableName
(IDENTITY Column, Column1...ColumnN)
VALUES
(IDENTITY Value, Value1, ...., ValueN)

SET IDENTITY_INSERT YourTableName OFF

Note:
Make sure that your insert statement does include all column name list, otherwise you will run into this error.

Error:
Msg 8101, Level 16, State 1, Line 1
An explicit value for the identity column in table 'Users' can only be specified when a column list is used and IDENTITY_INSERT is ON.

Wrong Insert Statement

INSERT INTO YourTableName
VALUES
(IDENTITY Value, Value1, ...., ValueN)

Correct Insert Statement

INSERT INTO YourTableName
(IDENTITY Column, Column1...ColumnN)
VALUES
(IDENTITY Value, Value1, ...., ValueN)

Error: Database could not be exclusively locked to perform the operation

You might receive "Database could not be exclusively locked to perform the operation" or many error like such when you are trying to perform operation on database which is already been used by process or some other users.

In order to obtain "Exclusive lock" to perform some critical database operation when someone is already using database you can perform following steps as described below to solve your problem.

Earlier I have blogged in context of restore error, but it is not limited to only restore and since this is very common error you get stuck when you are trying to do some critical database operation i have decided to explain step by step so that it can help me and many others like me who are in same situation. :)

Solution to obtain exclusive locked to perform database operations.

Step 1: Disconnect Connection.
To do so: File > Disconnect Object Explorer

Step 2: Connect Connection
To do so: File > Connect Object Explorer

Step 3: Open "New Query" window and run following command
use master
Note: Above command will make your current database to master which is important before we run following sequence of command.

Step 4: Copy and paste following sequence of command in Query window. Replace the word "MyDatabaseName" with Database name you are trying to get exclusive access.

ALTER DATABASE MyDatabaseName SET SINGLE_USER WITH ROLLBACK IMMEDIATE

ALTER DATABASE MyDatabaseName SET SINGLE_USER WITH ROLLBACK AFTER 30

ALTER DATABASE MyDatabaseName SET SINGLE_USER WITH NO_WAIT

ALTER DATABASE MyDatabaseName SET MULTI_USER WITH ROLLBACK IMMEDIATE;

You are now done and you can now try the command or operation you were trying to perform earlier which was giving you "Database could not be exclusively locked to perform the operation" error.

How to avoid multiple database request to improve performance

It is not good to execute multiple db request for loading single page. Review your database code to see if you have request paths that go to the database more than once. Each of those round-trips decreases the number of requests per second your application can serve. By returning multiple resultsets in a single database request, you can cut the total time spent communicating with the database.

In order to improve performance you should execute single stored proc and bring multiple resultset in to single db request. In this article i will explain you how to avoid multiple database request and how to bring multiple resultset into single db request.

Consider a scenario of loading a Product Page, which displays

Product Information and
Product Review Information

In order to bring 2 database request in single db request, your sql server stored proc should be declared as below.

SQL Server Stored Proc

CREATE PROCEDURE GetProductDetails
@ProductId bigint,
AS
SET NOCOUNT ON

--Product Information
Select ProductId,
ProductName,
ProductImage,
Description,
Price
From Product
Where ProductId = @ProductId

--Product Review Information
Select ReviewerName,
ReviewDesc,
ReviewDate
From ProductReview
Where ProductId = @ProductId

Asp.net, C# Code to bring multiple db request into single db request

Code Inside Data Access Class Library (DAL)

public DataSet GetProductDetails()
{
SqlCommand cmdToExecute = new SqlCommand();
cmdToExecute.CommandText = "GetProductDetails";
cmdToExecute.CommandType = CommandType.StoredProcedure;
DataSet dsResultSet = new DataSet();
SqlDataAdapter adapter = new SqlDataAdapter(cmdToExecute);

try
{
var conString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnStr"];
string strConnString = conString.ConnectionString;
SqlConnection conn = new SqlConnection(strConnString);

cmdToExecute.Connection = conn;

cmdToExecute.Parameters.Add(new SqlParameter("@ ProductId", SqlDbType.BigInt, 8, ParameterDirection.Input, false, 19, 0, "", DataRowVersion.Proposed, _productId));

//Open Connection
conn.Open();

// Assign proper name to multiple table
adapter.TableMappings.Add("Table", "ProductInfo");
adapter.TableMappings.Add("Table1", "ProductReviewInfo");
adapter.Fill(dsResultSet);

return dsResultSet;
}
catch (Exception ex)
{
// some error occured.
throw new Exception("DB Request error.", ex);
}
finally
{
conn.Close();
cmdToExecute.Dispose();
adapter.Dispose();
}
}

Code Inside Asp.net .aspx.cs page

protected void Page_Load(object sender, EventArgs e)
{
if (Request.QueryString[ProductId] != null)
{
long ProductId = Convert.ToInt64(Request.QueryString[ProductId].ToString());

DataSet dsData = new DataSet();

//Assuming you have Product class in DAL
ProductInfo objProduct = new ProductInfo();
objProduct.ProductId = ProductId;
dsData = objProduct.GetProductDetails();

DataTable dtProductInfo = dsData.Tables["ProductInfo"];
DataTable dtProductReviews = dsData.Tables["ProductReviewInfo"];

//Now you have data table containing information
//Make necessary assignment to controls
.....
.....
.....
.....
.....

}
}

Hope above code gave you basic idea of why it is important to avoid multiple db request and how to bring multiple recordset with single db request.

Restore failed - Exclusive access could not be obtained because the database is in use

Error: Restore failed for Server 'ServerName\InstanceName'. System.Data.SqlClient.SqlError: Exclusive access could not be obtained because the database is in use.

TITLE: Microsoft SQL Server Management Studio

------------------------------

Restore failed for Server 'ServerName\InstanceName'. (Microsoft.SqlServer.SmoExtended)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1600.1+((KJ_RTM).100402-1539+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Restore+Server&LinkId=20476

------------------------------

ADDITIONAL INFORMATION:

System.Data.SqlClient.SqlError: Exclusive access could not be obtained because the database is in use. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1600.1+((KJ_RTM).100402-1539+)&LinkId=20476

Solution

Try running any of this command and then try to restore.

ALTER DATABASE MyDatabaseName SET SINGLE_USER WITH ROLLBACK IMMEDIATE

ALTER DATABASE MyDatabaseNameSET SINGLE_USER WITH ROLLBACK AFTER 30

ALTER DATABASE MyDatabaseName SET SINGLE_USER WITH NO_WAIT

ALTER DATABASE MyDatabaseName SET MULTI_USER WITH ROLLBACK IMMEDIATE;

Source of Information

Delete All Stored Proc from SQL Server DB

If you need to delete all stored proc from sql server db, following script would be useful.

I have found this useful script from Mattberther's Blog

I have observed that his script won't delete stored procedure which has space in it.
Example: If stored procedure name is like "Category Insert" i.e. Procedure which has space in its name.

I have make line bold wherein i have add bracket to support this.

declare @procName sysname

declare someCursor cursor for
   select name from sysobjects where type = 'P' and objectproperty(id, 'IsMSShipped') = 0

open someCursor
fetch next from someCursor into @procName
while @@FETCH_STATUS = 0
begin
   exec('drop proc [' + @procName + ']')
   fetch next from someCursor into @procName
end

close someCursor
deallocate someCursor
go

Claim DB Space After Deleting Records in Table - Reduce DB Space

Recently I have delete 2 million unwanted records from my sql server database table, what i realise is even after deleting records, space used by database is not reducing.

After browsing help available on Internet, I found out

1) Whenever we delete records from table, sql server doesn't reduce size of database immediately.
2) Even after deleting table , sql server doesn't reduce size of database.
3) Instead of Freeing space for deleted records, sql server marks pages containing deleted records as free pages, showing that they belong to the table. When new data are inserted, they are put into those pages first. Once those pages are filled up, SQL Server will allocate new pages.

So In order to claim database space after deleting records in Table, go through following steps:

1) Check what is Size of your Database using following command?
Exec sp_spaceused

2) Delete Records from table, If you have already did that skip this step.

3) Run below command to claim unused database space.
DBCC SHRINKDATABASE(0)

DBCC SHRINKDATABASE command - Shrinks the size of the data and log files in the specified database.

Best Practise to use this command

A shrink operation is most effective after an operation that creates lots of unused space, such as a truncate table or a drop table operation.
Most databases require some free space to be available for regular day-to-day operations. If you shrink a database repeatedly and notice that the database size grows again, this indicates that the space that was shrunk is required for regular operations. In these cases, repeatedly shrinking the database is a wasted operation.
A shrink operation does not preserve the fragmentation state of indexes in the database, and generally increases fragmentation to a degree. This is another reason not to repeatedly shrink the database.

More reading on this command
http://msdn.microsoft.com/en-us/library/ms190488.aspx

Few other things of Interest
If you have Created, Alter or Drop any Database table recently then run below command.
DBCC UPDATEUSAGE(0)

DBCC UPDATEUSAGE(0) - Reports and corrects pages and row count inaccuracies in the catalog views. These inaccuracies may cause incorrect space usage reports returned by the sp_spaceused system stored procedure.

More reading on this command
http://msdn.microsoft.com/en-us/library/ms188414.aspx

Example showing how this command helps me to reduce size of my database after deleting records from table.

1) Take Backup of your Production Database.

2) Take Backup of Table Scripts of your Production Database.

3) Create Test Database in Local Environment

5) Run Tables creation script

6) Restore Production Database to Test Database in local environment

I am assuming you are familiar with above steps, actual steps begin after this.
I am also assuming that you have already deleted unwanted records in table.

7) Check Size of your Database
Exec sp_spaceused

8) Run Update Usage command
DBCC UPDATEUSAGE(0)

9) Check Size of your Database
Exec sp_spaceused

10) Run Shrink Database command
DBCC SHRINKDATABASE(0)

11) Check Size of your Database
Exec sp_spaceused

If everything goes smooth then you would see that your database size is reduced.

Error: A network-related or instance-specific error occurred while establishing a connection to SQL Server.

Error: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

If you encounter above error, while running your VS.Net, than first thing you should do is to check your web.config file to make sure that connection string is correct.

This problem might occur if you have recently

Change your PC Name
Installed new sql server instance but have not updated your web.config file with correct instance name.

Example:
Earlier you were using instance name "YourPCName\SQLExpress" but due to new sql server instance installation your instance name changed to "YourPCName\CustomInstanceName".

Below is sample connection string, where text in red is cause of problem.
Data Source=MyPCName\MySQLInstance;Initial Catalog=MyDBName;Integrated Security=True

Solution
Check Connection string in your vs.net solutions, web.config file and correct with new sql server instance name.

Messages Error 0xc0202009: Data Flow Task 1: SSIS Error Code DTS_E_OLEDBERROR.

I was getting following errors while Importing Excel file to SQL Server

Following is list of error details on Importing Excel 2007 to SQL Server 2008

Error 0xc0202009: Data Flow Task 1: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.

An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "Could not allocate space for object 'dbo.MyTable'.'PK_MyTable' in database 'MyDatabase_1384_8481' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.".

(SQL Server Import and Export Wizard)

Error 0xc0209029: Data Flow Task 1: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR.

The "input "Destination Input" (44)" failed because error code 0xC020907B occurred, and the error row disposition on "input "Destination Input" (44)" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure.

(SQL Server Import and Export Wizard)

Error 0xc0047022: Data Flow Task 1: SSIS Error Code DTS_E_PROCESSINPUTFAILED.

The ProcessInput method on component "Destination - MyTable" (31) failed with error code 0xC0209029 while processing input "Destination Input" (44). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure. (SQL Server Import and Export Wizard)

Error 0xc02020c4: Data Flow Task 1: The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020. (SQL Server Import and Export Wizard)

Error 0xc0047038: Data Flow Task 1: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED.

The PrimeOutput method on component "Source - 'ip-to-country$'" (1) returned error code 0xC02020C4. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.

(SQL Server Import and Export Wizard)

Work Around to deal with this situation

I have observed that from last couple of month my database has grow unexpectedly without my knowledge, and that was main reason for this error. So if you encounter this problem, don't follow solution blindly otherwise you would never solve root cause of problem.

Do following

Try to go through one by one table and check which table had grown unexpectedly. Tip try from checking table in which rows are inserted automatically. Example: Errorlog tables and other depends on your application.
Ones you find the that table, identify what is causing sudden growth, is it due to code change in application or due to some automatic script run by someone (virus) this problem is causing. Try to fix that problem.
Best approach is to take backup of that table and delete that table and recreate it. That will fix problem as lot of primary keys are free.
After solving this root cause problem try to apply solution.

Solution

Make sure there is enough Hard Disk space where database files are stored on server.
Turn on AUTOGROW for file groups.

Now try to Import your Excel File, make sure that you follow all points mentioned in this blog post in order to Import Excel 2007 file to SQL Server.

Random Records in SQL Server based on Where Clause

How to get random records in SQL Server based on Where Clause

Syntax

Select Top [No. of Random Records you want] *
from
(
Select Col1, Col2
from ActualTableName
where
Col1 Like '%SearchWord%'
) VirtualTable
ORDER BY NEWID()
Note: VirtualTable is table that doesn't exist in database, it is just a placeholder name.

Example
Select Top 1 *
from
(
Select QuestionId, QuestionTitle
from ForumQuestion
Where
ForumQuestion.QuestionTitle Like @SearchKeyword
) MyTable
ORDER BY NEWID()

Installing and running FullText Search on SQL Server 2008

Installing FullText Search on SQL Server 2008

1. Download SQL Express with FullText Search capability for SQL Server 2008
http://www.microsoft.com/express/Database/InstallOptions.aspx

2. Select Installation Type as New Installation

3 During Feature Selection, select Full-Text Search

4. While Instance Configuration, Create new Instance Configuration.
- Select Named Instance and give new name. Example: I have given name: MyShriGanesh

5. Main steps for installation are done, now simply press next with default choices and complete the installation.

6. After Installation login with new instance name created.
- Example: Earlier I have created named instance MyShriGanesh, so i need to login with same named instance in order to take benefit of FullText Search feature.

7. Now, Create New Database, Run Tables and SPs Scripts and restore database with data. If you don't have one already, download sample database and use that database.

After installation of sample database your object explorer will look like as follow:

8. Open Query Window and type following command to test whether FullText Search is installed or not.

Select SERVERPROPERTY('IsFullTextInstalled')
If this query returns a value of '1' then the component is installed.

Now, lets go step by step to run Full Text Search on SQL Server 2008

Step 1: Creating Full Text Catalog in SQL Server 2008
Syntax:
Create FullText Catalog DatabaseNameCatalog

Example:
Create FullText Catalog AdventureWorksCatalog

Step 2: Create FullText Index in SQL Server 2008

Syntax:

Create FullText Index on TableName

(Column1, Column2, ...., ColumnN)

Key Index PK_TablesPrimaryKey on DatabaseNameCatalog

with Change_Tracking Auto

Note:

TableName is name of table you would like to create fulltext index.
ColumnName is column on which you would like to create fulltext index, it is column you would like to perform search on.
PK_TablesPrimaryKey is primary key of table you are creating on fulltext search index.
DatabaseNameCatalog is fulltext search catalog created earlier.

Example:

Create FullText Index on Production.ProductDescription

([Description])

Key Index PK_ProductDescription_ProductDescriptionID on AdventureWorksCatalog

with Change_Tracking Auto

Step 3: Running FullText Search Query

There are many different examples of running fulltext query, I would be giving 2 examples of fulltext search query.

Example 1: Using FreeText

Select [Description]
from Production.ProductDescription
Where
FREETEXT([Description], 'Entry Level Bike')

Example 2: Using Contains

Select [Description]

from Production.ProductDescription

Where

Contains([Description], '"Entry" and "Level" and "Bike"')

Example 3: Using Weight keyword

Select [Description]
from Production.ProductDescription
Where
Contains([Description],
'ISABOUT (Entry Weight(.8), Level Weight(.4), Bike Weight (.2))')

Example 4: Using Inflectional keyword

Select [Description]
from Production.ProductDescription
Where
Contains([Description],
'FormsOf (INFLECTIONAL, Entry, Level, Bike)')

Download SQL Server 2008 R2 Express with FullText Search Feature

Whenever you are trying to download SQL Server 2008 R2 Express using following link
http://www.microsoft.com/express/database/

It doesn't come with FullText Search feature by default. Default package only consist of SQL Server Database Engine capability.

To download SQL Server 2008 R2 Express with following features

SQL Server Database Engine
SQL Server Management Studio Express
Full-Text Search
Reporting Services

Go to following link

http://www.microsoft.com/express/Database/InstallOptions.aspx

Restore failed for Server - RESTORE HEADERONLY is terminating abnormally

Recently I have upgraded my database from SQL Server 2005 Express to SQL Server 2008 Express, and I have notice on restoring database it started giving me following restore error.

Error Details

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------
ADDITIONAL INFORMATION:

The media family on device 'C:\NorthwindDB.bak' is incorrectly formed. SQL Server cannot process this media family. RESTORE HEADERONLY is terminating abnormally. (Microsoft SQL Server, Error: 3241)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.4053&EvtSrc=MSSQLServer&EvtID=3241&LinkId=20476

Few things you should try before going for solution
This error can occurs due to many reasons but you must ensure following before you start for any solution, it would save your lot of time.

1) Ensure Backup copy of Database is good.
- Take a backup and store on machine where you tried to take backup.
- Now create one dummy database and try to restore that database.
- If you are able to restore that database successfully on machine backup was created, than your backup copy is good.

You should alternately try taking backup using following command

BACKUP DATABASE NorthwindDB
TO DISK='C:\HostingSpaces\MyBackupCopy_NorthwindDB.bak' with FORMAT

If you are able to take backup successfully than Backup copy is good.

2) Ensure Backup copy doesn't get corrupted during dowload.
In my case, I have created a backup copy on Hosting Server, than stored it in .Zip Format, and than try to download it using FileZila, with default settings of filezila transfer type, i.e. Auto.

Till this point everything was going good, but still I was not able to restore DB.

3) Important : Open SQL Query window and check version of your SQL Server.
Run following command and see the output.

Select @@Version

It had given me following output
Microsoft SQL Server 2005 - 9.00.4053.00 (Intel X86) May 26 2009 14:24:20 Copyright (c) 1988-2005 Microsoft Corporation Express Edition on Windows NT 6.0 (Build 6002: Service Pack 2)

After running this command i notice that even though i am using SQL Server 2008 express, it is showing that i am using SQL Server 2005

Cause of Error
Even though I am using SQL Server 2008, but it was connected to SQL Server 2005 instance on the machine i was trying to restore.

Remember: Restoring Database from lower version to higher version would not give error. i.e. SQL Server 2005 to 2008 would not give error. But restoring from higher version to lower version will always result in error. In my case as i was connected to SQL Server 2005 instance it results me in above error.

You should validate that the instance is the right version by "SELECT @@version".

Solution
You need to fix the connection so it is using the SQL 2008 instance.

Try to run the SQL Server 2008 Express install program again, and during Name Instance configuration, specify a Name Instance with a different name. Example: MachineName\instancename

After installation, logged in with New Instance name created.

Try to create database, create tables for database and try to run restore again. It will work this time.

More about Instances
An instance of a SQL Server database is just a named installation of SQL Server. The first one you install on any given machine typically is the default instance, which has no specific name - you just connect to it using the (local) name or the name of the server machine.

SQL Server Express typically installs as a SQLExpress instance - so the instance is called SQLExpress, and you connect to it using (local)\SQLExpress or .\SQLExpress or MyServerMachine\SQLExpress.

SQL Server instances are totally independent of one another, and you can basically install as many as you like - you just have to make sure to use unique instance names for each. But when you run the SQL Server Installation again, it will show you what instances are already installed, and prompt you for a new instance name for a new installation.

Remove sql server 2005 express tools - Installing SQL Server 2008

I was trying to install SQL Server 2008 Express edition, and installation wizard was not allowing me to proceed further unless I remove SQL Server 2005 Express tools.

I tried removing SQL Server 2005 Express tools from Uninstall Program window, but still the same error continues. In order to fix this you must remove following registry key from registry.

Solution
1) Open Run Menu and type regedit.
2) It will open Registry Editor
3) Now go to following location.
HKEY_LOCAL_MACHINE > Software > Microsoft > Microsoft SQL Server > 90

4) Remove Registry Key 90.

Now try to install SQL Server 2008 and it would work this time.

Solution on RowNum and Union Query Problem

I was trying to create stored procedure for Paging data.

Query which I tried using to fetch data contains union. I want RowNum for this union query and I was trying to do create query as follow.

Select Id,

Title,

ROW_NUMBER() OVER(ORDER BY Title Desc) as RowNum

from

(

select Col1 as Id,Col2 as Title from Table1

union all

select Col1 as Id, Col2 as Title from Table2

union all

select Col1 as Id,Col2 as Title from Table3

) DataSet1

Above query works fine till I perform where clause on RowNum field. As RowNum is not part of subquery, when I tried to perform filter on RowNum field I was getting following error.

Select Id,

Title,

ROW_NUMBER() OVER(ORDER BY Title Desc) as RowNum

from

(

select Col1 as Id,Col2 as Title from Table1

union all

select Col1 as Id, Col2 as Title from Table2

union all

select Col1 as Id,Col2 as Title from Table3

) MyTable

Where

RowNum BETWEEN (1 * 25) + 1 AND (1 * 25) + 25

Error: Invalid column name 'RowNum'

Solution:

In order to resolve this situation I have placed my Query like this.

Select *

From

(

My Query

) MyTable

Where Clause for RowNum filter

In order to resolve Row_Number() function problem with Union Query, I have modified my query to following.

Select *

From

(

Select Id,

Title,

ROW_NUMBER() OVER(ORDER BY Title Desc) as RowNum

from

(

select Col1 as Id,Col2 as Title from Table1

union all

select Col1 as Id, Col2 as Title from Table2

union all

select Col1 as Id,Col2 as Title from Table3

) MyTable1

) MyTable2

Where

RowNum BETWEEN (1 * 25) + 1 AND (1 * 25) + 25

Import Excel 2007 into SQL Server 2005

Perfrom following steps to Import Excel 2007 Sheet into SQL Server 2005.

Step 1: Select Destination Database where you would like to Import Excel 2007 Sheet.

Right click database > Tasks > Select Import data... as shown in following figure.

Step 2: Select "Microsoft Office 12.0 Access Database Engine OLE DB Provider" from data source selection drop down. Click on "Properties..." button as shown in figure.

Step 3: Properties dialog box appears.

Click on "Connection Tab" and Enter Excel 2007 file path in Data source text box.

Step 4: Click on "All Tab"

Select "Extended Properties" from given edit values and click on "Edit Value..." button

Step 5: Edit value dialog box appears.

Enter "Excel 12.0" in Property value textbox and click OK button

Step 6: Press Test Connection button to make sure everything is working good.

Finally press OK button.

After that you need to follow usual procedure to import excel sheet data into sql server. i.e. You need to click next and follow wizard instructions.

Page 2

Append Leading Zero to return value of SQL Select Statement

Append Leading Zero to return value of SQL Select Statement.

Scenario

My SQL table contains USA Zipcode's stored in a numeric format. I have notice that Zipcode with numeric lenght less than 5 has leading zero's.

Here is few example of zipcode containing leading zero's

Rochester, MI 4

Aguadilla PR 603

Caguas PR 726

Ponce PR 731

Belmont MA 2478

Andover NJ 7821

Metuchen NJ 8840

Vernon CT 6066

I want to display correct zipcode every time i fetch value from the database.

So, I want to display zipcode number to always be 5 digits in length. The database might returns a incorrect zipcode, either single digit or up to 4 digits, but I always need enough leading zeros added to the zipcode in order to fit the 5 digit requirement.

Example it should return above zipcode's in following form (Append Leading Zero)

Rochester, MI 00004

Aguadilla PR 00603

Caguas PR 00726

Ponce PR 00731

Belmont MA 02478

Andover NJ 07821

Metuchen NJ 08840

Vernon CT 06066

Solution for displaying leading zero while returning values from SQL Select statement

If your column datatype is Varchar than use following

SELECT City, State, Right(Replicate('0',5) + Zipcode,5) from Location

If your column datatype is Numeric than use following

SELECT City, State, Right(Replicate('0',5) + convert(varchar(6),Zipcode ) ,5) from Location

Space Used by Database and Table SQL Server Command

How to know how much space is consumed by your database.

Exec sp_spaceused

Output

database_name database_size unallocated space

------------------------------------------------ ---

Northwind 58.38 MB 8.23 MB

reserved data index_size unused

------------------ ------------------ --------------

12056 KB 10464 KB 1104 KB 488 KB

How to know how much space is consumed by each table in your database

EXEC sp_MSforeachtable @command1="EXEC sp_spaceused '?'"

Well i cannot display output information but you can certainly try it SQL Analyzer and see the useful information about your database to control its size and remove unnecessary data.

SQL Server Utility Functions for Searching String in SQL Server 2005

Set of Utility Function for SQL Server 2005

AT(): Returns the beginning numeric position of the nth occurrence of a character expression within another character expression, counting from the leftmost character.
RAT(): Returns the numeric position of the last (rightmost) occurrence of a character string within another character string.
OCCURS(): Returns the number of times a character expression occurs within another character expression (including overlaps).
OCCURS2(): Returns the number of times a character expression occurs within another character expression (excluding overlaps).
PADL(): Returns a string from an expression, padded with spaces or characters to a specified length on the left side.
PADR(): Returns a string from an expression, padded with spaces or characters to a specified length on the right side.
PADC(): Returns a string from an expression, padded with spaces or characters to a specified length on the both sides.
CHRTRAN(): Replaces each character in a character expression that matches a character in a second character expression with the corresponding character in a third character expression.
STRTRAN(): Searches a character expression for occurrences of a second character expression, and then replaces each occurrence with a third character expression. Unlike a built-in function Replace, STRTRAN has three additional parameters.
STRFILTER(): Removes all characters from a string except those specified.
GETWORDCOUNT(): Counts the words in a string.
GETWORDNUM(): Returns a specified word from a string.
GETNUMWORD(): Returns the index position of a word in a string.
GETALLWORDS(): Inserts the words from a string into the table.
PROPER(): Returns from a character expression a string capitalized as appropriate for proper names.
RCHARINDEX(): Similar to the Transact-SQL function Charindex, with a Right search.
ARABTOROMAN(): Returns the character Roman numeral equivalent of a specified numeric expression (from 1 to 3999).
ROMANTOARAB(): Returns the number equivalent of a specified character Roman numeral expression (from I to MMMCMXCIX).

You can get code from this web link for each of this functionshttp://it.toolbox.com/wiki/index.php/User-Defined_string_Functions_MS_SQL_Server_2005_Transact-SQL_SQLCLR

Trim Characters in SQL Server

How to Trim Characters in SQL Server

If you are looking for following solution than probably this post can help you out:

How to Trim Characters in SQL Server
How to Extract Part of String in SQL Server
Returning only Numeric Part of String in SQL Server
Trim String in SQL Server
Trim Left and Right Portion of String in SQL Server
How to Sort Numeric String in SQL Server.

Note: The Example I am going to discuss is the solution for above mention criteria, understand it and you can be able to derive solution for your problem.

I want to sort a string like this.

Nodes

1-49

100-249

1000-2499

10000+

250-499

2500-4999

50-99

500-749

5000-9999

750-999

Lets go step by step and then letter sum it up.

Step1: To undergo this task I need to extract characters from left till this “-“ or “+” character.

Let find length till this “-“

select distinct MyExpr,charindex('-',MyExpr)-1 as Nodes from MyTable

1-49	1
10000+	-1
750-999	3
5000-9999	4
250-499	3
1000-2499	4
500-749	3
2500-4999	4
100-249	3
50-99	2

So I also require separate statement for

select distinct MyExpr,charindex('-',MyExpr)-1 as Nodes from MyTable

1-49	-1
10000+	5
750-999	-1
5000-9999	-1
250-499	-1
1000-2499	-1
500-749	-1
2500-4999	-1
100-249	-1
50-99	-1

Now lets extract part of string by using substring function and case select statement.

select distinct MyExpr as Nodes,

substring(MyExpr,1,(

case

when charindex('-',MyExpr)-1 = '-1'

then charindex('+',MyExpr)-1

else charindex('-',MyExpr)-1 end

)

as 'NodesSort'

from MyTable

Order by 'NodesSort'

So this gives us extraction of Part of string from string. You can also see trim right portion of string.

1-49	1
100-249	100
1000-2499	1000
10000+	10000
250-499	250
2500-4999	2500
50-99	50
500-749	500
5000-9999	5000
750-999	750

Now casting the string and displaying in sorted order.

Casting string to int and applying order by for sorting.

select distinct MyExpr as Nodes,

-- SORT Logic

cast(

substring(MyExpr,1,(

case

when charindex('-',MyExpr)-1 = '-1'

then charindex('+',MyExpr)-1

else charindex('-',MyExpr)-1 end

)

as int) as 'NodesSort'

from MyTable

order by 'NodesSort'

1-49	1
50-99	50
100-249	100
250-499	250
500-749	500
750-999	750
1000-2499	1000
2500-4999	2500
5000-9999	5000
10000+	10000

This method is not optimal, as we are using functions within function to achieve task, but yet you can achieve the solution, if anyone of you know more better way to achieve same task than please share.

InStr Function in SQL Server

Visual basic support “InStr” Function, which Returns the starting position of the specified expression in a character string.

To perform same task in SQL Server used CHARINDEX Function.

“CharIndex” Function is equivalent to “InStr” Function of SQL Server.

Example 1:

select Phone,charindex('-',Phone) from customers

Result

030-0074321	4
(5) 555-4729	8
(5) 555-3932	8
(171) 555-7788	10
0921-12 34 65	5
0621-08460	5
88.60.15.31	0

You can also specify start position

Example 2:

select Phone,charindex('-',Phone,5) from customers

here start position is 5.

Result

030-0074321	0
(5) 555-4729	8
(5) 555-3932	8
(171) 555-7788	10
0921-12 34 65	5
0621-08460	5
88.60.15.31	0

Minus Keyword in SQL Server

Minus Keyword in SQL Server 2000

Oracle supports "Minus" Keyword, but today suddenly when i tried to execute same in SQL Server it won't work.

So, I have found out Alternate way to use "Minus" in SQL Server

Alternate Way for "Minus" Keyword in SQL Server is used "Except" Keyword

Example:

Select City, State
from Employee
Minus
Select City, State
from Customer

This will work fine with Oracle, but won't work with SQL Server.

So alternate way to use "Minus" Keyword in SQL Server

Select City, State
from Employee
Except
Select City, State
from Customer

Wednesday, January 16, 2008

Database Script for US Phone No. Validation

Database Script for Validating Previously stored US Phone Numbers.

Example: You were not storing phone no. in format 1234567890 and to convert it to 123-456-7890 format following script would be useful.

Database Script for Formatting USA Phone Number

Note: Red Mark are the considerations which you need to taken care of for individual table.

DECLARE c1 CURSOR READ_ONLY

FOR

select [Phone-Number],[Last Name],[First Name] fromTemp_Boniva_Speakers

declare @PhoneNum varchar(50)

declare @NewPhoneNum varchar(50)

declare @FirstDash varchar(1)

declare @SecondDash varchar(1)

declare @LeftNum varchar(3)

declare @MiddleNum varchar(3)

declare @LastNum varchar(4)

--Extra Fields for checking uniqueness of record

declare @FirstName varchar(50)

declare @LastName varchar(50)

declare @Email varchar(200)

open c1

FETCH NEXT FROM c1

Into @PhoneNum, @LastName, @FirstName

WHILE @@FETCH_STATUS = 0

BEGIN

set @PhoneNum = ltrim(@PhoneNum)

set @PhoneNum = rtrim(@PhoneNum)

--PRINT @PhoneNum

/********** Logic for conversion **************/

set @FirstDash = SUBSTRING(@PhoneNum,4,1)

set @SecondDash = SUBSTRING(@PhoneNum,8,1)

--When Proper Dash are their (Eg: 123-456-7890)

if @FirstDash = '-' and @SecondDash = '-'

begin

set @NewPhoneNum = @PhoneNum --Don't Change

end

--When Space is their instead of dash (Eg: 123 456 7890)

else if ascii(@FirstDash) = 32 and ascii(@SecondDash) = 32

begin

set @NewPhoneNum = REPLACE(@PhoneNum,@FirstDash,'-')

end

--When Given format is their (Eg: (123) 456-7890)

else if SUBSTRING(@PhoneNum,1,1) = '(' and SUBSTRING(@PhoneNum,4,1) = ')' and ascii(SUBSTRING(@PhoneNum,5,1)) = 32

begin

set @LeftNum = SUBSTRING(@PhoneNum,2,3)

set @MiddleNum = SUBSTRING (@PhoneNum,6,3)

set @LastNum = SUBSTRING (@PhoneNum,10,4)

set @NewPhoneNum = @LeftNum + '-' + @MiddleNum + '-' + @LastNum

end

--When No Dash is their (Eg: 1234567890)

else if IsNumeric(@PhoneNum) = 1

begin

set @LeftNum = SUBSTRING(@PhoneNum,1,3)

set @MiddleNum = SUBSTRING (@PhoneNum,4,3)

set @LastNum = SUBSTRING (@PhoneNum,7,4)

set @NewPhoneNum = @LeftNum + '-' + @MiddleNum + '-' + @LastNum

end

else --For Unknown Format Don't Loose the data

begin

set @NewPhoneNum = @PhoneNum

end

--Update

update Temp_Boniva_Speakers

set [Phone-Number]=@NewPhoneNum

where

--Uniqueness Checking

[Last Name] = @LastName and

[First Name] = @FirstName

--select @NewPhoneNum as NewPhone, @PhoneNum as OldPhone, @LastName, @FirstName

FETCH NEXT FROM c1

Into @PhoneNum, @LastName, @FirstName

END

CLOSE c1

DEALLOCATE c1

Thursday, January 10, 2008

Free Database Compare Software for SQL Server

@ Colleague Thanks for providing useful information.

Free Database Compare Software for SQL Server , MY SQL and MS Access. It will also allow you to create snapshots of a database and compare a live database to a snapshot. A database structure viewer is included. You can filter the comparison results by type of object and you can also choose to exclude the comparison of constraints and fields.

Download Now

Friday, October 12, 2007

SQLCommunity.com has launched

A wordwide SQL Server community www.sqlcommunity.com has been launched. This is a joint effort of Microsoft Employees, few MVPs and SQL Server experts.

Lot of material for SQL Users

Highlights of www.SQLCommunity.com

SQL Server Articles
Discussion Topics for Allmost all topic on SQL including all versions of SQL
Useful Links for SQL Server
Readmade Scripts for SQL Server
Useful tools for SQL
Tips and Tricks
SQL Clinic
SQL Community to get instant help on SQL Topics from industry gurus.

Logon to www.sqlcommunity.com

Wednesday, October 10, 2007

Function Execution in SQL Server 2005

In this article you will learn, everything about using Function Execution inSQL Server 2005

String Functions
Date and Time Functions
Mathematical Functions

String FunctionsString Functions are used for manipulating string expression. Note: string expression should be passed within single quote.

Len('') - Returns length of string.

Example: select Len("Shri Ganesh") will return 11

Lower('') - Convert all characters to lowercase characters.

Example: select Lower('Shri Ganesh') will return shri ganesh

Upper('') - Convert all characters to uppercase characters.

Example: select Upper('Shri Ganesh') will return SHRI GANESH

LTrim('') - Removes spaces from given character strings on left.

Example: select LTrim(' Shri Ganesh') will return Shri Ganesh

Note: It doesn't removes tab or line feed character.

RTrim('') - Removes space from given character strings on right.

Example: select LTrim('Shri Ganesh ') will return Shri Ganesh
Note: It doesn't removes tab or line feed character.

Trim('') - Removes spaces from given character strings from both left and right.

Example: select LTrim(' Shri Ganesh ') will return Shri Ganesh
Note: It doesn't removes tab or line feed character.

SubString('') - Returns a part of string from original string.

SubString(character_expression, position, length)

position - specifies where the substring begins.

length - specifies the length of the substring as number of characters.

Example: select SubString('Shri Ganesh',6,7) where in
6 - Starting position of sub string from given string.
6 - It is no. of characters to be extract from given string, starting from 6.
That is it will return "Ganesh" As ganesh start from 6th character upto 6 characters.

Replace('') - Replace the desired string within the original string.

Replace(character_expression, searchstring, replacementstring)

SearchString - string which you want to replace.
ReplaceString - new string which you want to replace with

Example: select replace('Think High To Achieve High','High','Low')
here, function search for every occurrence of High and replace it with Low.
Original - Think High To Achieve High
Result - Think Low To Achieve Low

Right('') - extract particular characters from right part of given string expression.

Example: select right('Think High To Achieve High',15) will return "To Achieve High"
This function will be helpful when you want particular characters from right part.
Example: Let say i have social security nos. and i want to extract last 4 digit of it.

select right('111-11-1111',4) will return 1111
select right('222-22-2222',4) will return 2222
select right('333-33-3333',4) will return 3333
select right('444-44-4444',4) will return 4444

Date and Time FunctionsDate and Time Functions are used for manipulating Date and Time expression.

GetDate() - Returns current date and time of a system.

Example: select GetDate() will return something like "2007-10-10 15:34:37.287"

GetUTCDate() - Returns current date and time information as per UTC (Universal Time Coordinate or Greenwich Mean Time)

Example: select GetDate() will return something like "2007-10-10 15:34:37.287"

DatePart and Abbrevation, which we will be using with DatePart, DateADD, DateDIFF function.

Datepart	Abbreviations
Year	yy, yyyy
Quarter	qq, q
Month	mm, m
Dayofyear	dy, y
Day	dd, d
Week	wk, ww
Weekday	dw, w
Hour	Hh
Minute	mi, n
Second	ss, s
Millisecond	Ms

DatePart() - Returns an integer representing a datepart of a date.

Note: Example are based on considering "2007-10-10 15:34:37.287" as GetDate()

Example:

select DatePart("day",GetDate()) will return 10.
select DatePart("hour",GetDate()) will return 16.
select DatePart("dayofyear",GetDate()) will return 283. And so on...

DateADD() - Returns adds a date or time interval to a specified date.

Syntax: DateADD(Abbrevation, number to be added, date)
Example:

select DateAdd("day",7,GetDate()) will return 2007-10-17 16:09:18.280
select DateAdd("month",20,GetDate()) will return 2009-06-10 16:10:02.643
And so on...

DateDIFF() - Returns difference between two specified dates.

Syntax: DateDIFF(Abbrevation, startdate, enddate)
Note: If the end date is earlier than the start date, the function returns a negative number. If the start and end dates are equal or fall within the same interval, the function returns zero.
Example:

select DATEDIFF("mm", Getdate()-500,GETDATE()) will return 17
You must pass valid start and end date otherwise you will receive error.

Mathematical Functions
Mathematical Functions are used for manipulating Mathematical expression.

ABS() - Returns positive value of numeric expression.

Example: In following example both statement will return 3.14

select ABS(3.14)
select ABS(-3.14)

Ceiling() - Returns the smallest integer that is greater than or equal to a numeric expression.

Example:

select Ceiling(3.14) will return 4
select Ceiling(-3.14) will return 3.

Floor() -Returns the largest integer that is less than or equal to a numeric expression.

Example:

select Floor(3.14) will return 3
select Floor(-3.14) will return 4

Round() - Returns a numeric expression that is rounded to the specified length or precision.

Example:

select Round(3.14, 1) will return 3.10
select Round(-3.17, 1) will return -3.20
select Round(3.12345, 4) will return 3.12350
select Round(3.12345, 3) will return 3.12300

Power() - POWER(numeric_expression, power)

Example: select power(2,3) will return 8

Sunday, October 07, 2007

Query execution in SQL Server 2005

SQL Query execution

In this article you will explore

How to execute query within sql server 2005 environment
How to filter records with where clause
How to arrange records with order by clause
How to group records by group clause
How to filter grouped records with having clause
Concept of Joins, explanation and example of

Inner Join
Outer Join

Left Outer Join
Right Outer Join
Full Join or Cross Join

Self Join

Concept of sub queries, with explanation and example.
Concept of correlated sub queries, with explanation and example.

Ok, so lets begin...

For execution of Query, Open New Query Editor as shown in figure.

Now lets execute different data manipulation statement.

Select Database to perform Database Operation using "use" Statement
--Select Database for Data Manipulation opperation
use MySamplePractise

For mastering, Data Manipulation from beginner to expert, Study following Example with 150+ Queries.

Select Statement Execution in SQL Server 2005
--Display each details of all Customers
select * from customer

--Display FirstName, City and Country Details of Customers
select FirstName, City, Country from Customer

--Using Alias to display details, Display "Customer Name" by concating FirstName and LastName of Customer
select FirstName + ' ' + LastName as 'Customer Name' from Customer

Filtering Records with "Where Clause"

--Display Customers from country USA
select * from Customer
where
Country = 'USA'

--Display Customers from All countries except USA
select * from Customer
where
Country <> 'USA'

--Display details of Customers whose CustomerID in a range from 1003 to 1008
select * from Customer
where
CustomerID >= 1003 and CustomerID <= 1008

--You can replace above statement by using Between statement.
--Between will show customer with customerID 1003 to 1008.
select *
from Customer
where CustomerID between 1003 and 1008

--Display Customers whose LastName starts from P
select * from Customer where LastName like 'P%'

--Display Customers whose country name ends with A
select * from Customer where Country like '%A'

--Display Customers whose FirstName or LastName consist of word khan
select *
from Customer
where FirstName like '%Khan%' or LastName like '%Khan%'

--Display Customers whose FirstName second character is A
select *
from Customer
where FirstName like '_A%'

Ordering Records with "Order by" Caluse
--Note: by default when neither ASC (for Ascending) or DESC (for descending) is specify, it is ordered in Ascending order.

--Display Customers of USA country ordered by state
select * from Customer
where Country='USA'
order by State

--Display Customers of USA country ordered by state in descending order.
select * from Customer
where Country='USA'
order by State DESC

--Order Customer by LastName in descending order and FirstName in Ascending order.
select * from Customer
order by LastName DESC, FirstName ASC

Grouping Records by "Group by" Caluse

--Display Count of Customers in each country
select Country,Count(CustomerID) from Customer
group by country

--Display Count of Customers group the data by Country and State
select Country, state, Count(CustomerID) from Customer
group by country, state

Filtering Grouped data by "Having" Caluse

--Display Count of Customers country wise and display those countries only where more than 2 customers are available.
select Country,Count(CustomerID) from Customer
group by country
having Count(CustomerID) > 2

Using both Where and Having Clause to Filter data.

--Display Count of Customers from country "USA", state wise where in display details of those state only which have atleast 2 Customer
--It will display only Customer Count from "NJ" state of "USA" country.
select State,Count(CustomerID) from Customer
where state='NJ' and country='USA'
group by state
having count(CustomerID) > 2

Using Order by to arrange Grouped data

--Display Count of Customers from country "USA", state wise and it should display details in descending order of state of those state where Customers are atleast 2.
select State,Count(CustomerID) from Customer
where country='USA'
group by state
having count(CustomerID) > 2
Order by state desc

Now, lets display data from multiple table. For that we need to understand Joins so lets begin what is Join and how many different ways to display data.

Joins in SQL Server

Joins in SQL are used to combine the data from two or more tables on a common column between the two tables.

Different types of Joins in SQL Server and its explanation

Inner Join
Outer Join

Left Outer Join
Right Outer Join
Full Outer Join or Cross Join

Self Join

Lets go through the concept quickly.

Inner Join
Inner Join return those rows which are common in both the tables. In other words, Inner Join displays only the rows that have matching rows in both the tables.
Note: Nulls are excluded. Another thing you may even write just "Join" instead of "Inner Join" as default join is "Inner Join"

Example of Inner Join
We can use two types of syntax to write Join Query, that is

Join Query with ANSI Join Syntax
Join Query with Microsoft Join Syntax

well i will go through both example, but we will follow ANSI Join Syntax as it is widely used, but according to me Microsoft Join Syntax is easy...

Use Northwind
Method 1: ANSI JOIN Syntax
select * from
orders inner join [order details]
on
orders.orderid = [order details].orderid

Method 2: Former Microsoft JOIN Syntax
select * from orders, [order details]
where orders.orderid = [order details].orderid

Both query will be generate the same result.

Now, lets turn to example from our SamplePractise Database

--Display Details of Customer who have placed order.
select LastName, FirstName, Country, OrderId, OrderDate from
Customer inner Join Orders
on
Customer.CustomerID = Orders.CustomerID

--or-- Note: Inner join is default join and so you can write just join instead of inner join.
select LastName, FirstName, Country, OrderId, OrderDate from
Customer Join Orders
on
Customer.CustomerID = Orders.CustomerID

--Display Details of Customer living in "USA" who have placed order and arrange data by latest order placed.
select LastName, FirstName, Country, OrderId, OrderDate from
Customer inner Join Orders
on
Customer.CustomerID = Orders.CustomerID
where Country = 'USA'
order by OrderDate DESC

Outer Join
Outer Join, return all the rows from at least one of the table mentioned in the Join clause. Lets understand 3 types of outer join

Left Outer Join
Left outer join returns all the records from left table and only matching records from right table.

Example of Left Outer Join:
--Display details of all Customers and order details for those customer who have place any order
select LastName, FirstName, Country, OrderID, OrderDate
from Customer Left Outer Join Orders
on
Customer.CustomerId = Orders.CustomerID

Right Outer Join
Right outer join returns all the records from right table and only matching records from left table.

Example of Right Outer Join:
--Display all product type and product available in it
select producttype, productname
from producttype right outer join product
on
producttype.producttypeid = product.producttypeid
order by producttype

Full Join or Cross Join
A cross join returns the sets of records from the two joined tables. If A and B are two sets then cross join = A X B.

Example of Full Join or Cross Join
--Display all orders and all its details
select * from orders cross join orderdetails
-or-
select * from orders, orderdetails

Self Join
A table is join to itself in a self join.

Example for Self Join:
--Display details of customer living in same state
select distinct a.* from customer a, customer b
where a.customerid <> b.customerid and
a.state = b.state
order by state

--Consider an Example of Employee Table, wherein you want to find details of all the employee drawing same salary
select distinct a.* from employee a, employee b
where a.empid <> b.empid and
a.salary = b.salary

Joining three or more table
In joining three or more tables, join applies to only one relationship, and each relationship represents an association between only two tables.

FROM table1 join table2
ON table1.primarykey = table2.foreignkey join table3
ON table2.primarykey = table3.foreignkey

The first join applies to table1 and table2, the second join applies to table2and table3.

Example of Joining three or more tables

--Sample syntax
select * from
table1 left outer join table2 on (table1.field1 = table2.field1),
join table3 on (table2.field2 = table3.field2)

--Display details from customer, order and orderdetails table.
select c.lastname, c.firstname, o.orderid, o.orderdate, od.orderdetailsid, p.productname
from
customer c Inner Join orders o on (c.customerid = o.customerid)
inner join orderdetails od on (o.orderid = od.orderid)
inner join product p on (od.productid = p.productid)
order by o.orderid, od.orderdetailsid, p.productname

Sub Queries
A query within another query is called sub query.

Example of Sub Query
--Display details of customer who haven't placed any order
select * from customer
where customerid not in (select customerid from orders)

--Display details of customer belonging to "NJ" state of "USA" country has placed order.
select * from customer
where state = 'NJ' and
customerid in (select customerid from orders)

Correlated Sub Queries
A correlated subquery is a SELECT statement nested inside another T-SQL statement, which contains a reference to one or more columns in the outer query. Therefore, the correlated subquery can be said to be dependent on the outer query. This is the main difference between a correlated subquery and just a plain subquery. A plain subquery is not dependent on the outer query, can be run independently of the outer query, and will return a result set. A correlated subquery, since it is dependent on the outer query will return a syntax errors if it is run by itself. Source of Information

Example of Correlated Sub Queries

--Display order information along with amount of order.
select o.orderid, o.orderdate,
(select sum(od.amount) from orderdetails od
where o.orderid = od.orderid) as "Amount"
from orders o

--Display orderdetails along with product details.
select od.orderdetailsid,
(select p.productname from product p
where od.productid = p.productid) as "ProductName",
(select p.productprice from product p
where od.productid = p.productid) as "ProductPrice",
od.quantity as "Quantity",
od.quantity * (select p.productprice from product p
where od.productid = p.productid) as "Amount"
from orderdetails od
Note: you can also use
ANY - To display any matching record within sub query
ALL - To display details of those records who match all the criteria of sub query

Saturday, October 06, 2007

Basics of SQL Server 2005

Lets begin with basics of SQL Server 2005

In this article you will learn

How to Create Database in SQL Server 2005
How to Create Table in SQL Server 2005
How to Create Database Diagram in SQL Server 2005
How to Create Relationship among Tables in SQL Server 2005
How to Insert Data in SQL Server 2005

First of all logged in to SQL Server 2005 Database with Administrator rights as most of the stuff we gona do require admin rights.

Creating Database in SQL Server 2005

Right click Database Folder and click Database as shown in Figure.

Note: You can also download the script file and run in newly created database

Now type your desired database name and choose appropriate settings for your desired database. I have selected all default settings here and press OK button

So our "MySampleDatabase" Is created.

While working with Windows VistaNote: You may receive error "An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)"CREATE DATABASE permission denied in database 'master'. (Microsoft SQL Server, Error: 262)

To overcome this error in Windows Vista you need explicitly logon as Administrator as shown in figure

So after creation of Database it consist of various database object as shown in figure.

Creating Tables in SQL Server 2005 database

Now, right click the newly created MySamplePractise Database and add new table

This open the table in design mode, Lets create customer table and choose appropriate datatype, if you are not sure about choosing the correct datatype in sql server 2005 refer this article

Here, CustomerID is Identity field, that is Auto generated by system. It is similar to AutoNumber field in MS Access and Sequence in Oracle.

Understanding Identity Specification Attribute.

(Is Identity) - Yes : will generate autonumber
Identity Increment - It is number of increment you want from the previously generated ID, here i have taken 1.
Identity Seed - It commands SQL Server 2005 from where to start generating autonumber. Here, I have taken value 1000, so it will generate autonumber from 1001

ConstraintConstraint is a check which is made on table which must be followed by user entering data. It reduce the number of Invalid data feed-in into system by prechecking its validity.

In this table 2 constraint are used that is "Allow Null" and "Primary Key"

Allow Null Constraint - If checked than will Allow Null values in field, else it will not allow you to bypass the column without entering data.
Primary Key Constraint - It checks that each record in table is unique and not null. To refer more on Keys

Creating Primary Key on Column of SQL Server 2005 Right click the column field and set the field as Primary Key

Now in similar fashion create Order Table, OrderDetails Table, Product Table, ProductType Table.

Order Table

OrderDetails Table

Product Table

ProductType Table

so your object explorer looks as under

Creating Database Diagram in SQL Server 2005

Next step select Table and press Add button

Adding a releationship among tables

Right click the "Many" Table as They don't allow to change foreign key table in Releationship dialog.

Example: Click on Orders Table, as 1 Customer can place Many Orders

Foreign Key Relationship dialog box will appear, press add button and you will find details appear on right pane of dialog box.

Now click the open dialog button on right of "Table And Columns Specification" from right pane of dialog box.

For creating relationship between Orders and Customer, CustomerID is used. Select Table "Customer" for Primary Key Table and table "Orders" for Foreign Key.

And press ok button to create relationship between two table.

Advantage of creating relationship

It will not allow to enter orders details without customer information. That is it allow to enter orders details of valid customer.
It will not allow to delete customer information, until all details associated with customer is delete.
You may change setting as per your need by selecting No from dropdown of "Enforce for Replication" and "Enforce foreign key constraint"

Similarly you need to create relationship between remaining tables.

1 Customer can place Many Orders
1 Order can have Many OrderDetails
1 Product can be in Many OrderDetails
1 ProductType consist of Many Product

At the end Relationship diagram looks as under

When you are dealing with large database with 100's of table, you can change the view of table by selecting all table with "ctrl+A" and right clicking any of selected table and change its views so that it is convinent to view.

A sample diagram will now look as under

So now as Database Designing and Table creation is done, lets enter some sample data

Inserting Record in Table for SQL Server 2005, right click the table and select open table, it gives you MS Access type editor to enter data. Note those who prefer inserting data through SQL Query or Script may open SQL Editor. Similarly making change in table design click modify.

Now key-in the data in customer table, as shown in figure. Note: here CustomerID is generated automatically and its value is starting from 1001 as we have set Increment Seed to 1000.

Insert Record into Customer Table.

Similarly enter data for other tables.

Remember, you don't need to enter data into CustomerID, as it is Identity field wherein data is auto generated by system. As we have enforce Referential Integrity by creating relationship, so you are not allowed to add record in orders table with anonymous customerId.

Friday, October 05, 2007

Datatypes in SQL Server 2005

Lets begin with easy series of understanding SQL Server 2005.

If you are begineer and want to gain Database and RDBMS knowledge in seconds this SQL Tutorial

Why choosing datatype is important or what the disadvantage of choosing wrong datatype?

Choosing too small datatype cannot meet the system need and at some point your system may become useless. Example use of smalldatetime (2 bytes) datatype over datetime (4 bytes) datatype can make your system useless when you need to provide information based on millisecond data.

Choosing too large datatype can waste space and increase expense. Example: let say choosing of smallint to store age data is wastage of space as same information can be stored in tinyint and serves the need. So here we can save space and money by allocating tinyint (1 byte) over smallint (2 bytes).

Choosing incorect datatype can degrades performance, because of conversion of data. Example choosing a char datatype to store datetime information will degrades performance as it requires frequent boxing and unboxing of type.

Choosing between tinyint, smallint, int, bigint datatype in SQL Server 2005

You should use int datatype to store exact number. Now depends on chart shown below you and your maximum and minimum data range consideration you can able to decide which is better to go with.

Data type	Range	Storage
bigint	-2^63 (-9,223,372,036,854,775,808) to 2^63-1 (9,223,372,036,854,775,807)	8 Bytes
int	-2^31 (-2,147,483,648) to 2^31-1 (2,147,483,647)	4 Bytes
smallint	-2^15 (-32,768) to 2^15-1 (32,767)	2 Bytes
tinyint	0 to 255	1 Byte

Choosing between datetime and smalldatetime datatype in SQL Server 2005
datetime datatype are used for storing date and time information.

Comparision chart

Data type	Range	Storage/Accuracy
datetime	January 1, 1753, through December 31, 9999	4 Bytes/3.33 milliseconds
smalldatetime	January 1, 1900, through June 6, 2079	2 Bytes/1 minute

Difference between datetime and smalldatetime datatype in SQL Server 2005
datetime is more accurate than smalldatetime datatype. datetime datatype stores milliseconds data while smalldatetime datatype doesn't store millisecond data and thus it is less accurate. But in most cases we don't require such a accurate information, so in this case you can storing datetime in smalldatetime datatype and saves the extra 2 bytes storage space. Example: DOJ details of employee doesn't require such a accuracy level so you can use smalldatetime datatype.

Choosing between char, varchar, varchar(MAX) and text datatype in SQL Server 2005
char, varchar, varchar(MAX) and text datatype are used for storing character string. Note you cannot store multilingual data. i.e. You will not allowed to store japanes or hindi characters.

Char datatype

Stores character (non-unicode) data.
Stores data in fixed-length format.
Choose char datatype when size of data is consistent.

Varchar datatype

Stores Character (non-unicode) data.
Stores data in variable-length format.
Choose varchar datatype when size of data varies between 8000 bytes

Varchar(MAX) datatype

Stores Character (non-unicode) data.
Stores data in variable-length format.
Choose varchar datatype when size of data varies and it may exceeds 8000 bytes.

Text datatype

Text datatype is used in previous versions of SQL Server, it is recommended to use Varchar(MAX) instead of Text datatype, as ntext,text, and image data types will be removed in a future version of Microsoft SQL Server
Stores large amount of character string
Stores data in variable-length format.
Choose text datatype when size of data varies may exceeds 8000 bytes.

Difference between char and varchar datatype
char stores fixed-length character data while varchar stores variable-length character data.

Difference between varchar and varchar(MAX) datatype
varchar stores variable-length character data whose range varies upto 8000 bytes, varchar(MAX) stores variable-length character data whose range may varies beyond 8000 bytes.

Should i choose varchar(MAX) or text datatype
They both provides same functionality, but it is better to choose varchar(MAX) over text datatype as text data types will be removed in a future version of Microsoft SQL Server.

Choosing between nchar, nvarchar, nvarchar(MAX) and ntext datatype in SQL Server 2005
nchar, nvarchar, nvarchar(MAX) and ntext datatype are used for storing unicode character string. you can store multilingual data. i.e. You can store japanes or hindi characters.

nChar datatype

Stores unicode character data.
Stores data in fixed-length format.
Choose nchar datatype when size of data is consistent and you want to store multi-lingual data.

nVarchar datatype

Stores unicode Character data.
Stores data in variable-length format.
Choose nvarchar datatype when size of data varies upto 4000 bytes and data can be multi-lingual.

nVarchar(MAX) datatype

Stores unicode Character data.
Stores data in variable-length format.
Choose nvarchar datatype when size of data varies and it may exceeds 4000 bytes and data can be multi-lingual.

nText datatype

nText datatype is used in previous versions of SQL Server, it is recommended to use nVarchar(MAX) instead of Text datatype, as ntext,text, and image data types will be removed in a future version of Microsoft SQL Server
Stores large amount of character string
Stores data in variable-length format.
Choose ntext datatype when size of data varies may exceeds 4000 bytes and data can be multi-lingual.

Difference between nchar and nvarchar datatype
nchar stores fixed-length unicode character data while nvarchar stores variable-length unicode character data.

Difference between nvarchar and nvarchar(MAX) datatype
varchar stores variable-length unicode character data whose range varies upto 4000 bytes, varchar(MAX) stores variable-length unicode character data whose range may varies beyond 4000 bytes.

Should i choose varchar(MAX) or text datatype
They both provides same functionality, but it is better to choose varchar(MAX) over text datatype as text data types will be removed in a future version of Microsoft SQL Server.

Difference between

char and nchar
varchar and nvarchar
varchar(MAX) and nvarchar(MAX) and
text and ntext datatype

Former can stores non-unicode character strings while latter can stores unicode character string.
Former require less memory, while latter requires doubles as much memory as former. i.e. char can stores upto 8000 bytes while nchar can stores upto 4000 bytes, because regular data are of 8bits while unicode data require 16bits storage space.
Example: Former is used to store character data which doesn't suppor multi-language support, while latter can support character multi-language character support.
For more detail

Choosing between numeric and decimal datatype in SQL Server 2005
Numeric and decimal datatype both are used when the data have fixed precision and scale.
Precision - Maximum total digit that can be stored on both left and right of decimal point.
Scale - Mamimum total digit that can be stored on right of decimal point.
There is no functional difference between numeric and decimal datatype. For more detail

Choosing between money and smallmoney datatype in SQL Server 2005
Money and smallmoney datatype are used for storing monetary data.

Data type	Range	Storage
money	-922,337,203,685,477.5808 to 922,337,203,685,477.5807	8 bytes
smallmoney	- 214,748.3648 to 214,748.3647	4 bytes

Choosing between float and real datatype in SQL Server 2005
Both float and real datatype are approximate datatype. i.e. Not all values in datatype can be represented exactly.

Data type	Range	Storage
float	- 1.79E+308 to -2.23E-308, 0 and 2.23E-308 to 1.79E+308	Depends on the value of n
real	- 3.40E + 38 to -1.18E - 38, 0 and 1.18E - 38 to 3.40E + 38	4 Bytes

syntax of float [ ( n ) ]

SQL Server 2005 treats n as one of two possible values. If 1<=n<=24, n is treated as 24. If 25<=n<=53, n is treated as 53.

n value	Precision	Storage size
1-24	7 digits	4 bytes
25-53	15 digits	8 bytes

Choosing between binary, varbinary and varbinary(MAX) datatype in SQL Server 2005

Use binary when the sizes of the column data entries are consistent.
Use varbinary when the sizes of the column data entries vary considerably.
Use varbinary(max) when the column data entries exceed 8,000 bytes.

For more details

bit datatype - bit datatype can take a value of 1, 0, or NULL. It consumes 1 byte. Note: The string values TRUE and FALSE can be converted to bitvalues: TRUE is converted to 1 and FALSE is converted to 0.

xml datatype - SQL Server 2005 introduces a native data type called XML. By using this feature, you can create a table with one or more columns of type XML besides relational columns. These XML values are stored in an internal format as large binary objects (BLOB), which enable the XML model to support document order and recursive structures. Because of the seamless integration of the XML data type with SQL Server's storage and programming models, you can query and update XML documents and even write joins between XML data and relational data in your database. SQL Server 2005 uses the same query engine and optimizer for querying XML data as it does for relational data, radically enhancing your ability to work with XML data. For more reading on xml datatype

Page 3

Performance Tunning articles on SQL Server

Thursday, August 23, 2007

SQLScheduler to schedule various SQL jobs

SQLScheduler to schedule various SQL jobs

SQLScheduler is a fully functional client/server application written in C# that allows administrators to schedule various SQL jobs for SQL Server Express and other versions of SQL Server.

Features:

Supports all versions of SQL Server 2000 and 2005
Supports unlimited SQL Server instances with an unlimited number of jobs.
Allows to easily schedule SQL Server maintenance tasks: backups, index rebuilds, integrity checks, etc.
Runs as Windows Service
Email notifications on job success and failure
And more...

Requirements:

Windows 2000, 2003, XP
.NET Framework 2.0
SQL Server 2000, 2005

Download Product Page

Screenshot

Friday, August 17, 2007

SQL Server 2005 Tutorial and Future of SQL Server

Brief and easy tutorial for SQL Server 2005 Tutorial

Lesson 1. SQL Server 2005 Overview
Lesson 2. Overview of SQL Server 2005 Architecture
Lesson 3. Installing SQL Server 2005
Lesson 4. Transact-SQL Enhancements in SQL Server 2005
Lesson 5. XML integration with SQL Server 2005
Lesson 6. Using the .NET CLR in SQL Server 2005
Lesson 7. Developing Client Applications with ADO .NET 2.0
Lesson 8. Using Service Broker
Lesson 9. Using Native HTTP Support
Lesson 10. Using Notification Services

Future of SQL Server

SQL Server 2008 code named "Katmai"

Best Practices for SQL Server 2000

Article on SQL Server 2000 Best PracticesIt contain 35 useful tips, check out

Thursday, August 16, 2007

SQL Server Keyboard Shortcuts

SQL Server Keyboard ShortcutsComplete list of SQL Server Keyboard Shortcuts

Click here

Tuesday, August 14, 2007

SQL Optimization Tips

SQL Optimization Tips

• Use views and stored procedures instead of heavy-duty queries.
This can reduce network traffic, because your client will send to
server only stored procedure or view name (perhaps with some
parameters) instead of large heavy-duty queries text. This can be used
to facilitate permission management also, because you can restrict
user access to table columns they should not see.

• Try to use constraints instead of triggers, whenever possible.
Constraints are much more efficient than triggers and can boost
performance. So, you should use constraints instead of triggers,
whenever possible.

• Use table variables instead of temporary tables.
Table variables require less locking and logging resources than
temporary tables, so table variables should be used whenever possible.
The table variables are available in SQL Server 2000 only.

• Try to use UNION ALL statement instead of UNION, whenever possible.
The UNION ALL statement is much faster than UNION, because UNION ALL
statement does not look for duplicate rows, and UNION statement does
look for duplicate rows, whether or not they exist.

• Try to avoid using the DISTINCT clause, whenever possible.
Because using the DISTINCT clause will result in some performance
degradation, you should use this clause only when it is necessary.

• Try to avoid using SQL Server cursors, whenever possible.
SQL Server cursors can result in some performance degradation in
comparison with select statements. Try to use correlated sub-query or
derived tables, if you need to perform row-by-row operations.

• Try to avoid the HAVING clause, whenever possible.
The HAVING clause is used to restrict the result set returned by the
GROUP BY clause. When you use GROUP BY with the HAVING clause, the
GROUP BY clause divides the rows into sets of grouped rows and
aggregates their values, and then the HAVING clause eliminates
undesired aggregated groups. In many cases, you can write your select
statement so, that it will contain only WHERE and GROUP BY clauses
without HAVING clause. This can improve the performance of your query.

• If you need to return the total table's row count, you can use
alternative way instead of SELECT COUNT(*) statement.
Because SELECT COUNT(*) statement make a full table scan to return the
total table's row count, it can take very many time for the large
table. There is another way to determine the total row count in a
table. You can use sysindexes system table, in this case. There is
ROWS column in the sysindexes table. This column contains the total
row count for each table in your database. So, you can use the
following select statement instead of SELECT COUNT(*): SELECT rows
FROM sysindexes WHERE id = OBJECT_ID('table_name') AND indid < 2 So,
you can improve the speed of such queries in several times.

• Include SET NOCOUNT ON statement into your stored procedures to stop
the message indicating the number of rows affected by a T-SQL statement.
This can reduce network traffic, because your client will not receive
the message indicating the number of rows affected by a T-SQL statement.

• Try to restrict the queries result set by using the WHERE clause.
This can results in good performance benefits, because SQL Server will
return to client only particular rows, not all rows from the table(s).
This can reduce network traffic and boost the overall performance of
the query.

• Use the select statements with TOP keyword or the SET ROWCOUNT
statement, if you need to return only the first n rows.
This can improve performance of your queries, because the smaller
result set will be returned. This can also reduce the traffic between
the server and the clients.

• Try to restrict the queries result set by returning only the
particular columns from the table, not all table's columns.
This can results in good performance benefits, because SQL Server will
return to client only particular columns, not all table's columns.
This can reduce network traffic and boost the overall performance of
the query.
1.Indexes
2.avoid more number of triggers on the table
3.unnecessary complicated joins
4.correct use of Group by clause with the select list
5 In worst cases Denormalization

Index Optimization tips

• Every index increases the time in takes to perform INSERTS, UPDATES
and DELETES, so the number of indexes should not be very much. Try to
use maximum 4-5 indexes on one table, not more. If you have read-only
table, then the number of indexes may be increased.

• Keep your indexes as narrow as possible. This reduces the size of
the index and reduces the number of reads required to read the index.

• Try to create indexes on columns that have integer values rather
than character values.

• If you create a composite (multi-column) index, the order of the
columns in the key are very important. Try to order the columns in the
key as to enhance selectivity, with the most selective columns to the
leftmost of the key.

• If you want to join several tables, try to create surrogate integer
keys for this purpose and create indexes on their columns.

• Create surrogate integer primary key (identity for example) if your
table will not have many insert operations.

• Clustered indexes are more preferable than nonclustered, if you need
to select by a range of values or you need to sort results set with
GROUP BY or ORDER BY.

• If your application will be performing the same query over and over
on the same table, consider creating a covering index on the table.

• You can use the SQL Server Profiler Create Trace Wizard with
"Identify Scans of Large Tables" trace to determine which tables in
your database may need indexes. This trace will show which tables are
being scanned by queries instead of using an index.

• You can use sp_MSforeachtable undocumented stored procedure to
rebuild all indexes in your database. Try to schedule it to execute
during CPU idle time and slow production periods.
sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?')"

For SQL SERVER Frequently Asked Interview Questions

Monday, August 13, 2007

SQL Server FAQ Interview Questions

FAQ on Administration in SQL Server

159. Explain the architecture of SQL Server?
**

160. Different types of Backups?
o A full database backup is a full copy of the database.
o A transaction log backup copies only the transaction log.
o A differential backup copies only the database pages modified after
the last full database backup.
o A file or filegroup restore allows the recovery of just the portion
of a database that was on the failed disk.

161. What are `jobs' in SQL Server? How do we create one? What is tasks?
Using SQL Server Agent jobs, you can automate administrative tasks and
run them on a recurring basis.
**

162. What is database replication? What are the different types of
replication you can set up in SQL Server? How are they used?
Replication is the process of copying/moving data between databases on
the same or different servers. SQL Server supports the following types
of replication scenarios:
Snapshot replication
Transactional replication (with immediate updating subscribers, with
queued updating subscribers)
Merge replication

163. What are the different types of replications available in
sqlserver and brief about each?
**

164. What is snapshot replication how is it different from
Transactional replication?
Snapshot replication distributes data exactly as it appears at a
specific moment in time and does not monitor for updates to the data.
Snapshot replication is best used as a method for replicating data

that changes infrequently or where the most up-to-date values (low
latency) are not a requirement. When synchronization occurs, the
entire snapshot is generated and sent to Subscribers.
Snapshot replication would be preferable over transactional
replication when data changes are substantial but infrequent. For
example, if a sales organization maintains a product price list and
the prices are all updated at the same time once or twice each year,
replicating the entire snapshot of data after it has changed is
recommended. Creating new snapshots nightly is also an option if you
are publishing relatively small tables that are updated only at the
Publisher.
Snapshot replication is often used when needing to browse data such as
price lists, online catalogs, or data for decision support, where the
most current data is not essential and the data is used as read-only.
These Subscribers can be disconnected if they are not updating the data.
Snapshot replication is helpful when:
• Data is mostly static and does not change often. When it does
change, it makes more sense to publish an entirely new copy to
Subscribers.
• It is acceptable to have copies of data that are out of date for a
period of time.
• Replicating small volumes of data in which an entire refresh of the
data is reasonable.
Snapshot replication is mostly appropriate when you need to distribute
a read-only copy of data, but it also provides the option to update
data at the Subscriber. When Subscribers only read data, transactional
consistency is maintained between the Publisher and Subscribers. When
Subscribers to a snapshot publication must update data, transactional
consistency can be maintained between the Publisher and Subscriber
because the dat

For More SQL SERVER Frequently Asked Interview Questions

FAQ on Permissions in SQL Server

FAQ on Permissions in SQL Server

149. A user is a member of Public role and Sales role. Public role has
the permission to select on all the table, and Sales role, which
doesn't have a select permission on some of the tables. Will that user
be able to select from all tables?
**

150. If a user does not have permission on a table, but he has
permission to a view created on it, will he be able to view the data
in table?
Yes.

151. Describe Application Role and explain a scenario when you will
use it?
**

152. What is the difference between the REPEATABLE READ and SERIALIZE
isolation levels?

The level at which a transaction is prepared to accept inconsistent
data is termed the isolation level. The isolation level is the degree
to which one transaction must be isolated from other transactions. A
lower isolation level increases concurrency, but at the expense of
data correctness. Conversely, a higher isolation level ensures that
data is correct, but can affect concurrency negatively. The isolation
level required by an application determines the locking behavior SQL
Server uses.
SQL-92 defines the following isolation levels, all of which are
supported by SQL Server:
• Read uncommitted (the lowest level where transactions are isolated
only enough to ensure that physically corrupt data is not read).
• Read committed (SQL Server default level).
• Repeatable read.
• Serializable (the highest level, where transactions are completely
isolated from one another).
Isolation level Dirty read Nonrepeatable read Phantom
Read uncommitted Yes Yes Yes
Read committed No Yes Yes
Repeatable read No No Yes
Serializable No No No

153. Uncommitted Dependency (Dirty Read) - Uncommitted dependency
occurs when a second transaction selects a row that is being updated
by another transaction. The second transaction is reading data that
has not been committed yet and may be changed by the transaction
updating the row. For example, an editor is making changes to an
electronic document. During the changes, a second editor takes a copy
of the document that includes all the changes made so far, and
distributes the document to the intended audience.
Inconsistent Analysis (Nonrepeatable Read) Inconsistent analysis
occurs when a second transaction accesses the same row several times
and reads different data each time. Inconsistent analysis is similar
to uncommitted dependency in that another transaction is changing the
data that a second transaction is reading. However, in inconsistent
analysis, the data read by the second transaction was committed by the

transaction that made the change. Also, inconsistent analysis involves
multiple reads (two or more) of the same row and each time the
information is changed by another transaction; thus, the term
nonrepeatable read. For example, an editor reads the same document
twice, but between each reading, the writer rewrites the document.
When the editor reads the document for the second time, it has changed.
Phantom Reads Phantom reads occur when an insert or delete action is
performed against a row that belongs to a range of rows being read by
a transaction. The transaction's first read of the range of rows shows
a row that no longer exists in the second or succeeding read, as a
result of a deletion by a different transaction. Similarly, as the
result of an insert by a different transaction, the transaction's
second or succeeding read shows a row that did not exist in the
original read. For example, an editor makes changes to a document
submitted by a writer, but when the changes are incorporated into the
master copy of the document by the production department, they find
that new unedited material has been added to the document by the
author. This problem could be avoided if no one could add new material
to the document until the editor and production department finish
working with the original document.

154. After removing a table from database, what other related objects
have to be dropped explicitly?
(view, SP)

155. You have a SP names YourSP and have the a Select Stmt inside the
SP. You also have a user named YourUser. What permissions you will
give him for accessing the SP.
**

156. Different Authentication modes in Sql server? If a user is logged
under windows authentication mode, how to find his userid?
There are Three Different authentication modes in sqlserver.
0. Windows Authentication Mode
1. SqlServer Authentication Mode
2. Mixed Authentication Mode
"system_user" system function in sqlserver to fetch the logged on user

name.

157. Give the connection strings from front-end for both type
logins(windows,sqlserver)?
This are specifically for sqlserver not for any other RDBMS
Data Source=MySQLServer;Initial Catalog=NORTHWIND;Integrated
Security=SSPI (windows)
Data Source=MySQLServer;Initial Catalog=NORTHWIND;Uid=" ";Pwd="
"(sqlserver)

158. What are three SQL keywords used to change or set someone's
permissions?
Grant, Deny and Revoke

For More SQL SERVER Frequently Asked Interview Questions

FAQ on Tools in SQL Server

FAQ on Tools in SQL Server

133. Have you ever used DBCC command? Give an example for it.
The Transact-SQL programming language provides DBCC statements that
act as Database Console Commands for Microsoft® SQL Serve 2000. These
statements check the physical and logical consistency of a database.
Many DBCC statements can fix detected problems. Database Console

Command statements are grouped into these categories.
Statement category Perform
Maintenance statements Maintenance tasks on a database, index, or
filegroup.
Miscellaneous statements Miscellaneous tasks such as enabling
row-level locking or removing a dynamic-link library (DLL) from memory.
Status statements Status checks.
Validation statements Validation operations on a database, table,
index, catalog, filegroup, system tables, or allocation of database pages.
DBCC CHECKDB, DBCC CHECKTABLE, DBCC CHECKCATALOG, DBCC CHECKALLOC,
DBCC SHOWCONTIG, DBCC SHRINKDATABASE, DBCC SHRINKFILE etc.

134. How do you use DBCC statements to monitor various aspects of a
SQL server installation?

135. What is the output of DBCC Showcontig statement?
Displays fragmentation information for the data and indexes of the
specified table.

136. How do I reset the identity column?
You can use the DBCC CHECKIDENT statement, if you want to reset or
reseed the identity column. For example, if you need to force the
current identity value in the jobs table to a value of 100, you can
use the following:
USE pubs
GO
DBCC CHECKIDENT (jobs, RESEED, 100)
GO

137. About SQL Command line executables
Utilities
bcp
console
isql
sqlagent
sqldiag
sqlmaint
sqlservr
vswitch
dtsrun
dtswiz
isqlw
itwiz
odbccmpt
osql
rebuildm
sqlftwiz
distrib
logread
replmerg
snapshot
scm
regxmlss

138. What is DTC?
The Microsoft Distributed Transaction Coordinator (MS DTC) is a
transaction manager that allows client applications to include several
different sources of data in one transaction. MS DTC coordinates
committing the distributed transaction across all the servers enlisted
in the transaction.

139. What is DTS? Any drawbacks in using DTS?
Microsoft® SQL Server™ 2000 Data Transformation Services (DTS) is a
set of graphical tools and programmable objects that lets you extract,

transform, and consolidate data from disparate sources into single or
multiple destinations.

140. What is BCP?
The bcp utility copies data between an instance of Microsoft® SQL
Server™ 2000 and a data file in a user-specified format.
C:\Documents and Settings\sthomas>bcp
usage: bcp {dbtable query} {in out queryout format} datafile
[-m maxerrors] [-f formatfile] [-e errfile]
[-F firstrow] [-L lastrow] [-b batchsize]
[-n native type] [-c character type] [-w wide character type]
[-N keep non-text native] [-V file format version] [-q quoted identifier]
[-C code page specifier] [-t field terminator] [-r row terminator]
[-i inputfile] [-o outfile] [-a packetsize]
[-S server name] [-U username] [-P password]
[-T trusted connection] [-v version] [-R regional enable]
[-k keep null values] [-E keep identity values]
[-h "load hints"]

141. How can I create a plain-text flat file from SQL Server as input
to another application?
One of the purposes of Extensible Markup Language (XML) is to solve
challenges like this, but until all applications become XML-enabled,
consider using our faithful standby, the bulk copy program (bcp)
utility. This utility can do more than just dump a table; bcp also can
take its input from a view instead of from a table. After you specify
a view as the input source, you can limit the output to a subset of
columns or to a subset of rows by selecting appropriate filtering
(WHERE and HAVING) clauses.
More important, by using a view, you can export data from multiple
joined tables. The only thing you cannot do is specify the sequence in
which the rows are written to the flat file, because a view does not
let you include an ORDER BY clause in it unless you also use the TOP
keyword.
If you want to generate the data in a particular sequence or if you
cannot predict the content of the data you want to export, be aware
that in addition to a view, bcp also supports using an actual query.

The only "gotcha" about using a query instead of a table or view is
that you must specify queryout in place of out in the bcp command line.
For example, you can use bcp to generate from the pubs database a list
of authors who reside in California by writing the following code:
bcp "SELECT * FROM pubs..authors WHERE state = 'CA'" queryout
c:\CAauthors.txt -c -T -S

142. What are the different ways of moving data/databases between
servers and databases in SQL Server?
There are lots of options available, you have to choose your option
depending upon your requirements. Some of the options you have are:
BACKUP/RESTORE, detaching and attaching databases, replication, DTS,
BCP, logshipping, INSERT...SELECT, SELECT...INTO, creating INSERT
scripts to generate data.

143. How will I export database?
Through DTS - Import/Export wizard
Backup - through Complete/Differential/Transaction Log

144. How to export database at a particular time, every week?
Backup - Schedule
DTS - Schedule
Jobs - create a new job

145. How do you load large data to the SQL server database?
bcp

146. How do you transfer data from text file to database (other than DTS)?
bcp

147. What is OSQL and ISQL utility?
The osql utility allows you to enter Transact-SQL statements, system
procedures, and script files. This utility uses ODBC to communicate
with the server.
The isql utility allows you to enter Transact-SQL statements, system
procedures, and script files; and uses DB-Library to communicate with
Microsoft® SQL Server™ 2000.
All DB-Library applications, such as isql, work as SQL Server
6.5–level clients when connected to SQL Server 2000. They do not
support some SQL Server 2000 features.
The osql utility is based on ODBC and does support all SQL Server 2000
features. Use osql to run scripts that isql cannot run.

148. What Tool you have used for checking Query Optimization? What is

the use of profiler in sql server? What is the first thing u look at
in a SQL Profiler?
SQL Profiler is a graphical tool that allows system administrators to
monitor events in an instance of Microsoft® SQL Server™. You can
capture and save data about each event to a file or SQL Server table
to analyze later. For example, you can monitor a production
environment to see which stored procedures is hampering performance by
executing too slowly.
Use SQL Profiler to:
• Monitor the performance of an instance of SQL Server.
• Debug Transact-SQL statements and stored procedures.
• Identify slow-executing queries.
• Test SQL statements and stored procedures in the development phase
of a project by single-stepping through statements to confirm that the
code works as expected.
• Troubleshoot problems in SQL Server by capturing events on a
production system and replaying them on a test system. This is useful
for testing or debugging purposes and allows users to continue using
the production system without interference.
Audit and review activity that occurred on an instance of SQL Server.
This allows a security administrator to review any of the auditing
events, including the success and failure of a login attempt and the
success and failure of permissions in accessing statements and objects

For More SQL SERVER Frequently Asked Interview Questions

SQL Server FAQ on Random Category

SQL Server FAQ on Random Category

87. What are the constraints for Table Constraints define rules
regarding the values allowed in columns and are the standard mechanism
for enforcing integrity. SQL Server 2000 supports five classes of
constraints.
NOT NULL
CHECK
UNIQUE
PRIMARY KEY
FOREIGN KEY

88. There are 50 columns in a table. Write a query to get first 25 columns
Ans: Need to mention each column names.

89. How to list all the tables in a particular database?
USE pubs
GO
sp_help

90. What are cursors? Explain different types of cursors. What are the
disadvantages of cursors? How can you avoid cursors?
Cursors allow row-by-row processing of the result sets.
Types of cursors: Static, Dynamic, Forward-only, Keyset-driven.
Disadvantages of cursors: Each time you fetch a row from the cursor,
it results in a network roundtrip, where as a normal SELECT query
makes only one roundtrip, however large the result set is. Cursors are
also costly because they require more resources and temporary storage
(results in more IO operations). Further, there are restrictions on
the SELECT statements that can be used with some types of cursors.

Most of the times, set based operations can be used instead of
cursors. Here is an example:
If you have to give a flat hike to your employees using the following
criteria:
Salary between 30000 and 40000 -- 5000 hike
Salary between 40000 and 55000 -- 7000 hike
Salary between 55000 and 65000 -- 9000 hike
In this situation many developers tend to use a cursor, determine each
employee's salary and update his salary according to the above
formula. But the same can be achieved by multiple update statements or
can be combined in a single UPDATE statement as shown below:
UPDATE tbl_emp SET salary =
CASE WHEN salary BETWEEN 30000 AND 40000 THEN salary + 5000
WHEN salary BETWEEN 40000 AND 55000 THEN salary + 7000
WHEN salary BETWEEN 55000 AND 65000 THEN salary + 10000
END
Another situation in which developers tend to use cursors: You need to
call a stored procedure when a column in a particular row meets
certain condition. You don't have to use cursors for this. This can be
achieved using WHILE loop, as long as there is a unique key to
identify each row. For examples of using WHILE loop for row by row
processing, check out the 'My code library' section of my site or
search for WHILE.

91. Dynamic Cursors?
Suppose, I have a dynamic cursor attached to table in a database. I
have another means by which I will modify the table. What do you
think will the values in the cursor be?
Dynamic cursors reflect all changes made to the rows in their result
set when scrolling through the cursor. The data values, order, and
membership of the rows in the result set can change on each fetch. All
UPDATE, INSERT, and DELETE statements made by all users are visible
through the cursor. Updates are visible immediately if they are made
through the cursor using either an API function such as SQLSetPos or
the Transact-SQL WHERE CURRENT OF clause. Updates made outside the
cursor are not visible until they are committed, unless the cursor

transaction isolation level is set to read uncommitted.

92. What is DATEPART?
Returns an integer representing the specified datepart of the
specified date.

93. Difference between Delete and Truncate?
TRUNCATE TABLE is functionally identical to DELETE statement with no
WHERE clause: both remove all rows in the table.
(1) But TRUNCATE TABLE is faster and uses fewer system and transaction
log resources than DELETE. The DELETE statement removes rows one at a
time and records an entry in the transaction log for each deleted row.
TRUNCATE TABLE removes the data by deallocating the data pages used to
store the table's data, and only the page deallocations are recorded
in the transaction log.
(2) Because TRUNCATE TABLE is not logged, it cannot activate a trigger.
(3) The counter used by an identity for new rows is reset to the seed
for the column. If you want to retain the identity counter, use DELETE
instead.
Of course, TRUNCATE TABLE can be rolled back.

94. Given a scenario where two operations, Delete Stmt and Truncate
Stmt, where the Delete Statement was successful and the truncate stmt
was failed. – Can u judge why?

95. What are global variables? Tell me some of them?
Transact-SQL global variables are a form of function and are now
referred to as functions.
ABS - Returns the absolute, positive value of the given numeric
expression.
SUM
AVG
AND

96. What is DDL?
Data definition language (DDL) statements are SQL statements that
support the definition or declaration of database objects (for
example, CREATE TABLE, DROP TABLE, and ALTER TABLE).
You can use the ADO Command object to issue DDL statements. To
differentiate DDL statements from a table or stored procedure name,
set the CommandType property of the Command object to adCmdText.
Because executing DDL queries with this method does not generate any
recordsets, there is no need for a Recordset object.

97. What is DML?

Data Manipulation Language (DML), which is used to select, insert,
update, and delete data in the objects defined using DDL

98. What are keys in RDBMS? What is a primary key/ foreign key?
There are two kinds of keys.
A primary key is a set of columns from a table that are guaranteed to
have unique values for each row of that table.
Foreign keys are attributes of one table that have matching values in
a primary key in another table, allowing for relationships between
tables.

99. What is the difference between Primary Key and Unique Key?
Both primary key and unique key enforce uniqueness of the column on
which they are defined. But by default primary key creates a clustered
index on the column, where are unique creates a nonclustered index by
default. Another major difference is that, primary key doesn't allow
NULLs, but unique key allows one NULL only.

100. Define candidate key, alternate key, composite key?
A candidate key is one that can identify each row of a table uniquely.
Generally a candidate key becomes the primary key of the table. If the
table has more than one candidate key, one of them will become the
primary key, and the rest are called alternate keys.
A key formed by combining at least two or more columns is called
composite key.

101. What is the Referential Integrity?
Referential integrity refers to the consistency that must be
maintained between primary and foreign keys, i.e. every foreign key
value must have a corresponding primary key value.

102. What are defaults? Is there a column to which a default can't be
bound?
A default is a value that will be used by a column, if no value is
supplied to that column while inserting data. IDENTITY columns and
timestamp columns can't have defaults bound to them.

103. What is Query optimization? How is tuning a performance of query
done?

104. What is the use of trace utility?

105. What is the use of shell commands? xp_cmdshell

Executes a given command string as an operating-system command shell
and returns any output as rows of text. Grants nonadministrative users
permissions to execute xp_cmdshell.

106. What is use of shrink database?
Microsoft® SQL Server 2000 allows each file within a database to be
shrunk to remove unused pages. Both data and transaction log files can
be shrunk.

107. If the performance of the query suddenly decreased where you will
check?

108. What is execution plan?

109. What is a pass-through query?
Microsoft® SQL Server 2000 sends pass-through queries as
un-interpreted query strings to an OLE DB data source. The query must
be in a syntax the OLE DB data source will accept. A Transact-SQL
statement uses the results from a pass-through query as though it is a
regular table reference.
This example uses a pass-through query to retrieve a result set from a
Microsoft Access version of the Northwind sample database.
SELECT *
FROM OpenRowset('Microsoft.Jet.OLEDB.4.0',
'c:\northwind.mdb';'admin'; '',
'SELECT CustomerID, CompanyName
FROM Customers
WHERE Region = ''WA'' ')

110. How do you differentiate Local and Global Temporary table?
You can create local and global temporary tables. Local temporary
tables are visible only in the current session; global temporary
tables are visible to all sessions. Prefix local temporary table names
with single number sign (#table_name), and prefix global temporary
table names with a double number sign (##table_name). SQL statements
reference the temporary table using the value specified for table_name
in the CREATE TABLE statement:
CREATE TABLE #MyTempTable (cola INT PRIMARY KEY)
INSERT INTO #MyTempTable VALUES (1)

111. How the Exists keyword works in SQL Server?
USE pubs
SELECT au_lname, au_fname
FROM authors
WHERE exists
(SELECT *
FROM publishers
WHERE
authors.city = publishers.city)
When a subquery is introduced with the keyword EXISTS, it functions as
an existence test. The WHERE clause of the outer query tests for the
existence of rows returned by the subquery. The subquery does not
actually produce any data; it returns a value of TRUE or FALSE.

112. ANY?
USE pubs
SELECT au_lname, au_fname
FROM authors
WHERE city = ANY
(SELECT city
FROM publishers)

113. to select date part only
SELECT CONVERT(char(10),GetDate(),101)
--to select time part only
SELECT right(GetDate(),7)

114. How can I send a message to user from the SQL Server?
You can use the xp_cmdshell extended stored procedure to run net send
command. This is the example to send the 'Hello' message to JOHN:
EXEC master..xp_cmdshell "net send JOHN 'Hello'"
To get net send message on the Windows 9x machines, you should run the
WinPopup utility. You can place WinPopup in the Startup group under
Program Files.

115. What is normalization? Explain different levels of normalization?
Explain Third normalization form with an example?
The process of refining tables, keys, columns, and relationships to
create an efficient database is called normalization. This should
eliminates unnecessary duplication and provides a rapid search path to
all necessary information.
Some of the benefits of normalization are:
• Data integrity (because there is no redundant, neglected data)
• Optimized queries (because normalized tables produce rapid,
efficient joins)
• Faster index creation and sorting (because the tables have fewer
columns)
• Faster UPDATE performance (because there are fewer indexes per table)
• Improved concurrency resolution (because table locks will affect
less data)
• Eliminate redundancy
There are a few rules for database normalization. Each rule is called
a "normal form." If the first rule is observed, the database is said

to be in "first normal form." If the first three rules are observed,
the database is considered to be in "third normal form." Although
other levels of normalization are possible, third normal form is
considered the highest level necessary for most applications.
First Normal Form (1NF)
• Eliminate repeating groups in individual tables
• Create a separate table for each set of related data.
• Identify each set of related data with a primary key.
Do not use multiple fields in a single table to store similar data.
For example, to track an inventory item that may come from two
possible sources, an inventory record may contain fields for Vendor
Code 1 and Vendor Code 2. But what happens when you add a third
vendor? Adding a field is not the answer; it requires program and
table modifications and does not smoothly accommodate a dynamic number
of vendors. Instead, place all vendor information in a separate table
called Vendors, then link inventory to vendors with an item number
key, or vendors to inventory with a vendor code key.
Another Example
Subordinate1 Subordinate2 Subordinate3 Subordinate4
Bob Jim Mary Beth
Mary Mike Jason Carol Mark
Jim Alan
Eliminate duplicative columns from the same table. Clearly, the
Subordinate1-Subordinate4 columns are duplicative. What happens when
we need to add or remove a subordinate?
Subordinate
Bob Jim
Bob Mary
Bob Beth
Mary Mike
Mary Jason
Mary Carol
Mary Mark
Jim Alan
Second Normal Form (2NF)
• Create separate tables for sets of values that apply to multiple
records.
• Relate these tables with a foreign key.
Records should not depend on anything other than a table's primary key
(a compound key, if necessary).
For example, consider a customer's address in an accounting system.
The address is needed by the Customers table, but also by the Orders,

Shipping, Invoices, Accounts Receivable, and Collections tables.
Instead of storing the customer's address as a separate entry in each
of these tables, store it in one place, either in the Customers table
or in a separate Addresses table.
Another Example:
CustNum FirstName LastName Address City State ZIP
1 John Doe 12 Main Street Sea Cliff NY 11579
2 Alan Johnson 82 Evergreen Tr Sea Cliff NY 11579
A brief look at this table reveals a small amount of redundant data.
We're storing the "Sea Cliff, NY 11579" and "Miami, FL 33157" entries
twice each. Additionally, if the ZIP code for Sea Cliff were to
change, we'd need to make that change in many places throughout the
database. Our new table (let's call it ZIPs) might look like this:ZIP City State
11579 Sea Cliff NY
33157 Miami FL
46637 South Bend IN
Third Normal Form (3NF)
• Eliminate fields that do not depend on the key.
Values in a record that are not part of that record's key do not
belong in the table. In general, any time the contents of a group of
fields may apply to more than a single record in the table, consider
placing those fields in a separate table.
For example, in an Employee Recruitment table, a candidate's
university name and address may be included. But you need a complete
list of universities for group mailings. If university information is
stored in the Candidates table, there is no way to list universities
with no current candidates. Create a separate Universities table and
link it to the Candidates table with a university code key.
Another Example :
Order Number Customer Number Unit Price Quantity Total
1 241 $10 2 $20
2 842 $9 20 $180
The total can be derived by multiplying the unit price by the
quantity, therefore it's not fully dependent upon the primary key. We
must remove it from the table to comply with the third normal form:
Order Number Customer Number Unit Price Quantity

1 241 $10 2
2 842 $9 20
http://databases.about.com/library/weekly/aa091601a.htm
Domain/key normal form (DKNF). A key uniquely identifies each row in a
table. A domain is the set of permissible values for an attribute. By
enforcing key and domain restrictions, the database is assured of
being freed from modification anomalies. DKNF is the normalization
level that most designers aim to achieve.
**
Remember, these normalization guidelines are cumulative. For a
database to be in 2NF, it must first fulfill all the criteria of a 1NF
database.

116. If a database is normalized by 3 NF then how many number of
tables it should contain in minimum? How many minimum if 2NF and 1 NF?

117. What is denormalization and when would you go for it?
As the name indicates, denormalization is the reverse process of
normalization. It's the controlled introduction of redundancy in to
the database design. It helps improve the query performance as the
number of joins could be reduced.

118. How can I randomly sort query results?
To randomly order rows, or to return x number of randomly chosen rows,
you can use the RAND function inside the SELECT statement. But theRAND function is resolved only once for the entire query, so every row
will get same value. You can use an ORDER BY clause to sort the rows
by the result from the NEWID function, as the following code shows:
SELECT *
FROM Northwind..Orders
ORDER BY NEWID()

119. sp_who
Provides information about current Microsoft® SQL Server™ users and
processes. The information returned can be filtered to return only
those processes that are not idle.

120. Have you worked on Dynamic SQL? How will You handled " (Double
Quotes) in Dynamic SQL?

121. How to find dependents of a table?
Verify dependencies with sp_depends before dropping an object

122. What is the difference between a CONSTRAINT AND RULE?
Rules are a backward-compatibility feature that perform some of the
same functions as CHECK constraints. CHECK constraints are the
preferred, standard way to restrict the values in a column. CHECK
constraints are also more concise than rules; there can only be one
rule applied to a column, but multiple CHECK constraints can be
applied. CHECK constraints are specified as part of the CREATE TABLE
statement, while rules are created as separate objects and then bound
to the column.

123. How to call a COM dll from SQL Server 2000?
sp_OACreate - Creates an instance of the OLE object on an instance of
Microsoft® SQL Server
Syntax
sp_OACreate progid, clsid,
objecttoken OUTPUT
[ , context ]
context - Specifies the execution context in which the newly created
OLE object runs. If specified, this value must be one of the following:
1 = In-process (.dll) OLE server only
4 = Local (.exe) OLE server only
5 = Both in-process and local OLE server allowed
Examples
A. Use Prog ID - This example creates a SQL-DMO SQLServer object by
using its ProgID.
DECLARE @object int
DECLARE @hr int
DECLARE @src varchar(255), @desc varchar(255)
EXEC @hr = sp_OACreate 'SQLDMO.SQLServer', @object OUT
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT
SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
RETURN
END
B. Use CLSID - This example creates a SQL-DMO SQLServer object by
using its CLSID.
DECLARE @object int
DECLARE @hr int
DECLARE @src varchar(255), @desc varchar(255)
EXEC @hr = sp_OACreate '{00026BA1-0000-0000-C000-000000000046}',
@object OUT
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT
SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
RETURN
END

124. Difference between sysusers and syslogins?

sysusers - Contains one row for each Microsoft® Windows user, Windows
group, Microsoft SQL Server™ user, or SQL Server role in the database.
syslogins - Contains one row for each login account.

125. What is the row size in SQL Server 2000?
8060 bytes.

126. How will you find structure of table, all tables/views in one db,
all dbs?
sp_helpdb - will give list of all databases
sp_helpdb pubs - will give details about database pubs. .mdf, .ldf
file locations, size of database.
select * from information_schema.tables where table_type='base table'
OR
SELECT * FROM sysobjects WHERE type = 'U' - lists all tables under
current database
***

127. What is English query?

128. B-tree indexes or doubly-linked lists?

129. What is the system function to get the current user's user id?
USER_ID(). Also check out other system functions like USER_NAME(),
SYSTEM_USER, SESSION_USER, CURRENT_USER, USER, SUSER_SID(), HOST_NAME().

130. What are the series of steps that happen on execution of a query
in a Query Analyzer?
1) Syntax checking 2) Parsing 3) Execution plan

131. Which event (Check constraints, Foreign Key, Rule, trigger,
Primary key check) will be performed last for integrity check?
Identity Insert Check
Nullability constraint
Data type check
Instead of trigger
Primary key
Check constraint
Foreign key
DML Execution (update statements)
After Trigger
**

132. How will you show many to many relation in sql?
Create 3rd table with 2 columns which having one to many relation to
these tables.
For More SQL SERVER Frequently Asked Interview Questions

Page 4

FAQ on Transaction in SQL Server

FAQ on Transaction in SQL Server84. What is Transaction?
A transaction is a sequence of operations performed as a single
logical unit of work. A logical unit of work must exhibit four
properties, called the ACID (Atomicity, Consistency, Isolation, and
Durability) properties, to qualify as a transaction:
• Atomicity - A transaction must be an atomic unit of work; either all
of its data modifications are performed or none of them is performed.
• Consistency - When completed, a transaction must leave all data in a
consistent state. In a relational database, all rules must be applied
to the transaction's modifications to maintain all data integrity. All
internal data structures, such as B-tree indexes or doubly-linked
lists, must be correct at the end of the transaction.
• Isolation - Modifications made by concurrent transactions must be
isolated from the modifications made by any other concurrent
transactions. A transaction either sees data in the state it was in
before another concurrent transaction modified it, or it sees the data
after the second transaction has completed, but it does not see an
intermediate state. This is referred to as serializability because it
results in the ability to reload the starting data and replay a series
of transactions to end up with the data in the same state it was in
after the original transactions were performed.
• Durability - After a transaction has completed, its effects are
permanently in place in the system. The modifications persist even in
the event of a system failure.

85. After one Begin Transaction a truncate statement and a RollBack
statements are there. Will it be rollbacked? Since the truncate
statement does not perform logged operation how does it RollBack?
It will rollback.
**

86. Given a SQL like
Begin Tran
Select @@Rowcount
Begin Tran
Select @@Rowcount
Begin Tran
Select @@Rowcount
Commit Tran
Select @@Rowcount
RollBack
Select @@Rowcount
RollBack
Select @@Rowcount
What is the value of @@Rowcount at each stmt levels?
Ans : 0 – zero.
@@ROWCOUNT - Returns the number of rows affected by the last statement.
@@TRANCOUNT - Returns the number of active transactions for the
current connection.
Each Begin Tran will add count, each commit will reduce count and ONE
rollback will make it 0.
For More SQL SERVER Frequently Asked Interview Questions

FAQ on View in SQL Server

FAQ on View in SQL Server80. What is View? Use? Syntax of View?
A view is a virtual table made up of data from base tables and other
views, but not stored separately.
• Views simplify users perception of the database (can be used to
present only the necessary information while hiding details in
underlying relations)
• Views improve data security preventing undesired accesses
• Views facilite the provision of additional data independence

81. Does the View occupy memory space?
No

82. Can u drop a table if it has a view?
Views or tables participating in a view created with the SCHEMABINDING
clause cannot be dropped, unless the view is dropped or changed so
that it no longer has schema binding. In addition, ALTER TABLE
statements on tables that participate in views having schema binding
will fail if these statements affect the view definition.
If the view is not created using SCHEMABINDING, then we can drop the
table.

83. Why doesn't SQL Server permit an ORDER BY clause in the definition
of a view?
SQL Server excludes an ORDER BY clause from a view to comply with the
ANSI SQL-92 standard. Because analyzing the rationale for this
standard requires a discussion of the underlying structure of the
structured query language (SQL) and the mathematics upon which it is
based, we can't fully explain the restriction here. However, if you
need to be able to specify an ORDER BY clause in a view, consider
using the following workaround:
USE pubs
GO
CREATE VIEW AuthorsByName

AS
SELECT TOP 100 PERCENT *
FROM authors
ORDER BY au_lname, au_fname
GO
The TOP construct, which Microsoft introduced in SQL Server 7.0, is
most useful when you combine it with the ORDER BY clause. The only
time that SQL Server supports an ORDER BY clause in a view is when it
is used in conjunction with the TOP keyword. Note that the TOP keyword
is a SQL Server extension to the ANSI SQL-92 standard.
For More SQL SERVER Frequently Asked Interview Questions

FAQ on Lock in SQL Server

FAQ on Lock in SQL Server74. What are locks?
Microsoft® SQL Server™ 2000 uses locking to ensure transactional
integrity and database consistency. Locking prevents users from

reading data being changed by other users, and prevents multiple users
from changing the same data at the same time. If locking is not used,
data within the database may become logically incorrect, and queries
executed against that data may produce unexpected results.

75. What are the different types of locks?
SQL Server uses these resource lock modes.
Lock mode Description
Shared (S) Used for operations that do not change or update data
(read-only operations), such as a SELECT statement.
Update (U) Used on resources that can be updated. Prevents a common
form of deadlock that occurs when multiple sessions are reading,
locking, and potentially updating resources later.
Exclusive (X) Used for data-modification operations, such as INSERT,
UPDATE, or DELETE. Ensures that multiple updates cannot be made to the
same resource at the same time.
Intent Used to establish a lock hierarchy. The types of intent locks
are: intent shared (IS), intent exclusive (IX), and shared with intent
exclusive (SIX).
Schema Used when an operation dependent on the schema of a table is
executing. The types of schema locks are: schema modification (Sch-M)
and schema stability (Sch-S).
Bulk Update (BU) Used when bulk-copying data into a table and the
TABLOCK hint is specified.

76. What is a dead lock? Give a practical sample? How you can minimize
the deadlock situation? What is a deadlock and what is a live lock?
How will you go about resolving deadlocks?
Deadlock is a situation when two processes, each having a lock on one
piece of data, attempt to acquire a lock on the other's piece. Each
process would wait indefinitely for the other to release the lock,
unless one of the user processes is terminated. SQL Server detects
deadlocks and terminates one user's process.
A livelock is one, where a request for an exclusive lock is
repeatedly denied because a series of overlapping shared locks keeps
interfering. SQL Server detects the situation after four denials and

refuses further shared locks. A livelock also occurs when read
transactions monopolize a table or page, forcing a write transaction
to wait indefinitely.

77. nolock?
Locking Hints A range of table-level locking hints can be specified
using the SELECT, INSERT, UPDATE, and DELETE statements to direct
Microsoft® SQL Server™ 2000 to the type of locks to be used.
Table-level locking hints can be used when a finer control of the
types of locks acquired on an object is required. These locking hints
override the current transaction isolation level for the session.
Note The SQL Server query optimizer automatically makes the correct
determination. It is recommended that table-level locking hints be
used to change the default locking behavior only when necessary.
Disallowing a locking level can affect concurrency adversely.

Locking hint Description
HOLDLOCK Hold a shared lock until completion of the transaction
instead of releasing the lock as soon as the required table, row, or
data page is no longer required. HOLDLOCK is equivalent to SERIALIZABLE.
NOLOCK Do not issue shared locks and do not honor exclusive locks.
When this option is in effect, it is possible to read an uncommitted
transaction or a set of pages that are rolled back in the middle of a
read. Dirty reads are possible. Only applies to the SELECT statement.
PAGLOCK Use page locks where a single table lock would usually be taken.
READCOMMITTED Perform a scan with the same locking semantics as a
transaction running at the READ COMMITTED isolation level. By default,
SQL Server 2000 operates at this isolation level.
READPAST Skip locked rows. This option causes a transaction to skip
rows locked by other transactions that would ordinarily appear in the
result set, rather than block the transaction waiting for the other
transactions to release their locks on these rows. The READPAST lock
hint applies only to transactions operating at READ COMMITTED

isolation and will read only past row-level locks. Applies only to the
SELECT statement.
READUNCOMMITTED Equivalent to NOLOCK.
REPEATABLEREAD Perform a scan with the same locking semantics as a
transaction running at the REPEATABLE READ isolation level.
ROWLOCK Use row-level locks instead of the coarser-grained page- and
table-level locks.
SERIALIZABLE Perform a scan with the same locking semantics as a
transaction running at the SERIALIZABLE isolation level. Equivalent to
HOLDLOCK.
TABLOCK Use a table lock instead of the finer-grained row- or
page-level locks. SQL Server holds this lock until the end of the
statement. However, if you also specify HOLDLOCK, the lock is held
until the end of the transaction.
TABLOCKX Use an exclusive lock on a table. This lock prevents others
from reading or updating the table and is held until the end of the
statement or transaction.
UPDLOCK Use update locks instead of shared locks while reading a
table, and hold locks until the end of the statement or transaction.
UPDLOCK has the advantage of allowing you to read data (without
blocking other readers) and update it later with the assurance that
the data has not changed since you last read it.
XLOCK Use an exclusive lock that will be held until the end of the
transaction on all data processed by the statement. This lock can be
specified with either PAGLOCK or TABLOCK, in which case the exclusive
lock applies to the appropriate level of granularity.

78. For example, if the transaction isolation level is set to
SERIALIZABLE, and the table-level locking hint NOLOCK is used with the
SELECT statement, key-range locks typically used to maintain
serializable transactions are not taken.
USE pubs
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
BEGIN TRANSACTION
SELECT au_lname FROM authors WITH (NOLOCK)
GO

79. What is escalation of locks?
Lock escalation is the process of converting a lot of low level locks

(like row locks, page locks) into higher level locks (like table
locks). Every lock is a memory structure too many locks would mean,
more memory being occupied by locks. To prevent this from happening,
SQL Server escalates the many fine-grain locks to fewer coarse-grain
locks. Lock escalation threshold was definable in SQL Server 6.5, but
from SQL Server 7.0 onwards it's dynamically managed by SQL Server.
For More SQL SERVER Frequently Asked Interview Questions

FAQ on Trigger in SQL Server

FAQ on Trigger in SQL Server71. What is Trigger? What is its use? What are the types of Triggers?
What are the new kinds of triggers in sql 2000? When should one use
"instead of Trigger"?
Microsoft® SQL Serve 2000 triggers are a special class of stored
procedure defined to execute automatically when an UPDATE, INSERT, or
DELETE statement is issued against a table or view. Triggers are
powerful tools that sites can use to enforce their business rules
automatically when data is modified.
The CREATE TRIGGER statement can be defined with the FOR UPDATE, FOR
INSERT, or FOR DELETE clauses to target a trigger to a specific class
of data modification actions. When FOR UPDATE is specified, the IF
UPDATE (column_name) clause can be used to target a trigger to updates
affecting a particular column.
You can use the FOR clause to specify when a trigger is executed:
• AFTER - The trigger executes after the statement that triggered it
completes. If the statement fails with an error, such as a constraint
violation or syntax error, the trigger is not executed. AFTER triggers
cannot be specified for views, they can only be specified for tables.
You can specify multiple AFTER triggers for each triggering action
(INSERT, UPDATE, or DELETE). If you have multiple AFTER triggers for a
table, you can use sp_settriggerorder to define which AFTER trigger
fires first and which fires last. All other AFTER triggers besides the
first and last fire in an undefined order which you cannot control.
AFTER is the default in SQL Server 2000. You could not specify AFTER
or INSTEAD OF in SQL Server version 7.0 or earlier, all triggers in
those versions operated as AFTER triggers.
• INSTEAD OF -The trigger executes in place of the triggering action.
INSTEAD OF triggers can be specified on both tables and views. You can
define only one INSTEAD OF trigger for each triggering action (INSERT,
UPDATE, and DELETE). INSTEAD OF triggers can be used to perform

enhance integrity checks on the data values supplied in INSERT and
UPDATE statements. INSTEAD OF triggers also let you specify actions
that allow views, which would normally not support updates, to be
updatable.
An INSTEAD OF trigger can take actions such as:
• Ignoring parts of a batch.
• Not processing a part of a batch and logging the problem rows.
• Taking an alternative action if an error condition is encountered.
In SQL Server 6.5 you could define only 3 triggers per table, one for
INSERT, one for UPDATE and one for DELETE. From SQL Server 7.0
onwards, this restriction is gone, and you could create multiple
triggers per each action. But in 7.0 there's no way to control the
order in which the triggers fire. In SQL Server 2000 you could specify
which trigger fires first or fires last using sp_settriggerorder.
Triggers can't be invoked on demand. They get triggered only when an
associated action (INSERT, UPDATE, DELETE) happens on the table on
which they are defined.
Triggers are generally used to implement business rules, auditing.
Triggers can also be used to extend the referential integrity checks,
but wherever possible, use constraints for this purpose, instead of
triggers, as constraints are much faster. Till SQL Server 7.0,
triggers fire only after the data modification operation happens. So
in a way, they are called post triggers. But in SQL Server 2000 you
could create pre triggers also.

72. Difference between trigger and Stored procedure?

73. The following trigger generates an e-mail whenever a new title is
added in the pubs database:
CREATE TRIGGER reminder
ON titles
FOR INSERT
AS
EXEC master..xp_sendmail 'MaryM', 'New title, mention in the next
report to distributors.'
For More SQL SERVER Frequently Asked Interview Questions

FAQ on Stored Procedure in SQL Server

FAQ on Stored Procedure in SQL Server49. What is Stored procedure?
A stored procedure is a set of Structured Query Language (SQL)
statements that you assign a name to and store in a database in
compiled form so that you can share it between a number of programs.
• They allow modular programming.

• They allow faster execution.
• They can reduce network traffic.
• They can be used as a security mechanism.

50. What are the different types of Storage Procedure?
. Temporary Stored Procedures - SQL Server supports two types of
temporary procedures: local and global. A local temporary procedure is
visible only to the connection that created it. A global temporary
procedure is available to all connections. Local temporary procedures
are automatically dropped at the end of the current session. Global
temporary procedures are dropped at the end of the last session using
the procedure. Usually, this is when the session that created the
procedure ends. Temporary procedures named with # and ## can be
created by any user.
a. System stored procedures are created and stored in the master
database and have the sp_ prefix.(or xp_) System stored procedures can
be executed from any database without having to qualify the stored
procedure name fully using the database name master. (If any
user-created stored procedure has the same name as a system stored
procedure, the user-created stored procedure will never be executed.)
b. Automatically Executing Stored Procedures - One or more stored
procedures can execute automatically when SQL Server starts. The
stored procedures must be created by the system administrator and
executed under the sysadmin fixed server role as a background process.
The procedure(s) cannot have any input parameters.
c. User stored procedure

51. How do I mark the stored procedure to automatic execution?
You can use the sp_procoption system stored procedure to mark the
stored procedure to automatic execution when the SQL Server will start.
Note. Only objects in the master database owned by dbo can have the
startup setting changed and this option is restricted to objects that
have no parameters.
USE master
EXEC sp_procoption 'indRebuild', 'startup', 'true')

52. How can you optimize a stored procedure?

53. How will know whether the SQL statements are executed?
When used in a stored procedure, the RETURN statement can specify an
integer value to return to the calling application, batch, or
procedure. If no value is specified on RETURN, a stored procedure
returns the value 0. The stored procedures return a value of 0 when
no errors were encountered. Any nonzero value indicates an error
occurred.

54. Why one should not prefix user stored procedures with sp_?
It is strongly recommended that you do not create any stored
procedures using sp_ as a prefix. SQL Server always looks for a stored
procedure beginning with sp_ in this order:
0. The stored procedure in the master database.
1. The stored procedure based on any qualifiers provided (database
name or owner).
2. The stored procedure using dbo as the owner, if one is not specified.
Therefore, although the user-created stored procedure prefixed with
sp_ may exist in the current database, the master database is always
checked first, even if the stored procedure is qualified with the
database name.

55. What can cause a Stored procedure execution plan to become
invalidated and/or fall out of cache?
0. Server restart
1. Plan is aged out due to low use
2. DBCC FREEPROCCACHE (sometime desired to force it)

56. When do one need to recompile stored procedure?
if a new index is added from which the stored procedure might benefit,
optimization does not automatically happen (until the next time the
stored procedure is run after SQL Server is restarted).

57. SQL Server provides three ways to recompile a stored procedure:
• The sp_recompile system stored procedure forces a recompile of a
stored procedure the next time it is run.
• Creating a stored procedure that specifies the WITH RECOMPILE option
in its definition indicates that SQL Server does not cache a plan for
this stored procedure; the stored procedure is recompiled each time it

is executed. Use the WITH RECOMPILE option when stored procedures take
parameters whose values differ widely between executions of the stored
procedure, resulting in different execution plans to be created each
time. Use of this option is uncommon, and causes the stored procedure
to execute more slowly because the stored procedure must be recompiled
each time it is executed.
• You can force the stored procedure to be recompiled by specifying
the WITH RECOMPILE option when you execute the stored procedure. Use
this option only if the parameter you are supplying is atypical or if
the data has significantly changed since the stored procedure was
created.

58. How to find out which stored procedure is recompiling? How to stop
stored procedures from recompiling?

59. I have Two Stored Procedures SP1 and SP2 as given below. How the
Transaction works, whether SP2 Transaction succeeds or fails?
CREATE PROCEDURE SP1 AS
BEGIN TRAN
INSERT INTO MARKS (SID,MARK,CID) VALUES (5,6,3)
EXEC SP2
ROLLBACK
GO

CREATE PROCEDURE SP2 AS
BEGIN TRAN
INSERT INTO MARKS (SID,MARK,CID) VALUES (100,100,103)
commit tran
GO
Both will get roll backed.

60. CREATE PROCEDURE SP1 AS
BEGIN TRAN
INSERT INTO MARKS (SID,MARK,CID) VALUES (5,6,3)
BEGIN TRAN
INSERT INTO STUDENT (SID,NAME1) VALUES (1,'SA')
commit tran
ROLLBACK TRAN
GO
Both will get roll backed.

61. How will you handle Errors in Sql Stored Procedure?
INSERT NonFatal VALUES (@Column2)
IF @@ERROR <>0
BEGIN
PRINT 'Error Occured'
END
http://www.sqlteam.com/item.asp?ItemID=2463

62. I have a stored procedure like
commit tran
create table a()
insert into table b
--
--
rollback tran
what will be the result? Is table created? data will be inserted in

table b?

63. What do you do when one procedure is blocking the other?

64. How you will return XML from Stored Procedure?

65. Can a Stored Procedure call itself (recursive). If so then up to
what level and can it be control?
Stored procedures are nested when one stored procedure calls another.
You can nest stored procedures up to 32 levels. The nesting level
increases by one when the called stored procedure begins execution and
decreases by one when the called stored procedure completes execution.
Attempting to exceed the maximum of 32 levels of nesting causes the
whole calling stored procedure chain to fail. The current nesting
level for the stored procedures in execution is stored in the
@@NESTLEVEL function.
eg:
SET NOCOUNT ON
USE master
IF OBJECT_ID('dbo.sp_calcfactorial') IS NOT NULL
DROP PROC dbo.sp_calcfactorial
GO
CREATE PROC dbo.sp_calcfactorial
@base_number int, @factorial int OUT
AS
DECLARE @previous_number int
IF (@base_number<2) factorial="1" 1="1" previous_number="@base_number-1" factorial="-1)" factorial="@factorial*@base_number">

that can be called from T-SQL, just the way we call normal stored
procedures using the EXEC statement.

68. Difference between view and stored procedure?
Views can have only select statements (create, update, truncate,
delete statements are not allowed) Views cannot have "select into",
"Group by" "Having", "Order by"

69. What is a Function & what are the different user defined functions?
Function is a saved Transact-SQL routine that returns a value.
User-defined functions cannot be used to perform a set of actions that
modify the global database state. User-defined functions, like system
functions, can be invoked from a query. They also can be executed
through an EXECUTE statement like stored procedures.
0. Scalar Functions
Functions are scalar-valued if the RETURNS clause specified one of the
scalar data types
1. Inline Table-valued Functions
If the RETURNS clause specifies TABLE with no accompanying column
list, the function is an inline function.
2. Multi-statement Table-valued Functions
If the RETURNS clause specifies a TABLE type with columns and their
data types, the function is a multi-statement table-valued function.

70. What are the difference between a function and a stored procedure?
0. Functions can be used in a select statement where as procedures cannot
1. Procedure takes both input and output parameters but Functions
takes only input parameters
2. Functions cannot return values of type text, ntext, image &
timestamps where as procedures can
3. Functions can be used as user defined datatypes in create table but
procedures cannot
***Eg:-create table (name varchar(10),salary getsal(name))
Here getsal is a user defined function which returns a salary type,
when table is created no storage is allotted for salary type, and
getsal function is also not executed, But when we are fetching some
values from this table, getsal function get's executed and the return

Type is returned as the result set.

For More SQL SERVER Frequently Asked Interview Questions

FAQ on Joins in SQL Server

FAQ on Joins in SQL Server43. What are joins?
Sometimes we have to select data from two or more tables to make our
result complete. We have to perform a join.

44. How many types of Joins?
Joins can be categorized as:
• Inner joins (the typical join operation, which uses some comparison
operator like = or <>). These include equi-joins and natural joins.
Inner joins use a comparison operator to match rows from two tables
based on the values in common columns from each table. For example,
retrieving all rows where the student identification number is the
same in both the students and courses tables.
• Outer joins. Outer joins can be a left, a right, or full outer join.
Outer joins are specified with one of the following sets of keywords
when they are specified in the FROM clause:
• LEFT JOIN or LEFT OUTER JOIN -The result set of a left outer join
includes all the rows from the left table specified in the LEFT OUTER
clause, not just the ones in which the joined columns match. When a
row in the left table has no matching rows in the right table, the
associated result set row contains null values for all select list
columns coming from the right table.

• RIGHT JOIN or RIGHT OUTER JOIN - A right outer join is the reverse
of a left outer join. All rows from the right table are returned. Null
values are returned for the left table any time a right table row has
no matching row in the left table.
• FULL JOIN or FULL OUTER JOIN - A full outer join returns all rows in
both the left and right tables. Any time a row has no match in the
other table, the select list columns from the other table contain null
values. When there is a match between the tables, the entire result
set row contains data values from the base tables.
• Cross joins - Cross joins return all rows from the left table, each
row from the left table is combined with all rows from the right
table. Cross joins are also called Cartesian products. (A Cartesian
join will get you a Cartesian product. A Cartesian join is when you
join every row of one table to every row of another table. You can
also get one by joining every row of a table to every row of itself.)

45. What is self join?
A table can be joined to itself in a self-join.

46. What are the differences between UNION and JOINS?
A join selects columns from 2 or more tables. A union selects rows.

47. Can I improve performance by using the ANSI-style joins instead of
the old-style joins?
Code Example 1:
select o.name, i.name
from sysobjects o, sysindexes i
where o.id = i.id
Code Example 2:
select o.name,
i.name
from sysobjects o inner join sysindexes i
on o.id = i.id
You will not get any performance gain by switching to the ANSI-style
JOIN syntax.
Using the ANSI-JOIN syntax gives you an important advantage: Because
the join logic is cleanly separated from the filtering criteria, you
can understand the query logic more quickly.
The SQL Server old-style JOIN executes the filtering conditions before
executing the joins, whereas the ANSI-style JOIN reverses this
procedure (join logic precedes filtering).
Perhaps the most compelling argument for switching to the ANSI-style
JOIN is that Microsoft has explicitly stated that SQL Server will not
support the old-style OUTER JOIN syntax indefinitely. Another
important consideration is that the ANSI-style JOIN supports query
constructions that the old-style JOIN syntax does not support.

48. What is derived table?
Derived tables are SELECT statements in the FROM clause referred to by
an alias or a user-specified name. The result set of the SELECT in the
FROM clause forms a table used by the outer SELECT statement. For
example, this SELECT uses a derived table to find if any store carries
all book titles in the pubs database:
SELECT ST.stor_id, ST.stor_name
FROM stores AS ST,
(SELECT stor_id, COUNT(DISTINCT title_id) AS title_count
FROM sales
GROUP BY stor_id
) AS SA
WHERE ST.stor_id = SA.stor_id
AND SA.title_count = (SELECT COUNT(*) FROM titles)

For More SQL SERVER Frequently Asked Interview Questions

FAQ on Data Types in SQL Server

FAQ on Data Types in SQL Server38. What are the data types in SQL
bigint Binary bit char cursor
datetime Decimal float image int
money Nchar ntext nvarchar real
smalldatetime Smallint smallmoney text timestamp
tinyint Varbinary Varchar uniqueidentifier

39. Difference between char and nvarchar / char and varchar data-type?
char[(n)] - Fixed-length non-Unicode character data with length of n
bytes. n must be a value from 1 through 8,000. Storage size is n
bytes. The SQL-92 synonym for char is character.
nvarchar(n) - Variable-length Unicode character data of n characters.
n must be a value from 1 through 4,000. Storage size, in bytes, is two
times the number of characters entered. The data entered can be 0
characters in length. The SQL-92 synonyms for nvarchar are national
char varying and national character varying.
varchar[(n)] - Variable-length non-Unicode character data with length
of n bytes. n must be a value from 1 through 8,000. Storage size is
the actual length in bytes of the data entered, not n bytes. The data
entered can be 0 characters in length. The SQL-92 synonyms for varchar
are char varying or character varying.

40. GUID datasize?

128bit

41. How GUID becoming unique across machines?
To ensure uniqueness across machines, the ID of the network card is
used (among others) to compute the number.

42. What is the difference between text and image data type?
Text and image. Use text for character data if you need to store more
than 255 characters in SQL Server 6.5, or more than 8000 in SQL Server
7.0. Use image for binary large objects (BLOBs) such as digital
images. With text and image data types, the data is not stored in the
row, so the limit of the page size does not apply.All that is stored
in the row is a pointer to the database pages that contain the
data.Individual text, ntext, and image values can be a maximum of
2-GB, which is too long to store in a single data row.

For More SQL SERVER Frequently Asked Interview Questions

FAQ on Indexes in SQL

FAQ on Indexes in SQL

28. What is Index? It's purpose?
Indexes in databases are similar to indexes in books. In a database,
an index allows the database program to find data in a table without
scanning the entire table. An index in a database is a list of values
in a table with the storage locations of rows in the table that
contain each value. Indexes can be created on either a single column
or a combination of columns in a table and are implemented in the form
of B-trees. An index contains an entry with one or more columns (the
search key) from each row in a table. A B-tree is sorted on the search
key, and can be searched efficiently on any leading subset of the

search key. For example, an index on columns A, B, C can be searched
efficiently on A, on A, B, and A, B, C.

29. Explain about Clustered and non clustered index? How to choose
between a Clustered Index and a Non-Clustered Index?
There are clustered and nonclustered indexes. A clustered index is a
special type of index that reorders the way records in the table are
physically stored. Therefore table can have only one clustered index.
The leaf nodes of a clustered index contain the data pages.
A nonclustered index is a special type of index in which the logical
order of the index does not match the physical stored order of the
rows on disk. The leaf nodes of a nonclustered index does not consist
of the data pages. Instead, the leaf nodes contain index rows.
Consider using a clustered index for:
o Columns that contain a large number of distinct values.
o Queries that return a range of values using operators such as
BETWEEN, >, >=, <, and <=. o Columns that are accessed sequentially. o Queries that return large result sets. Non-clustered indexes have the same B-tree structure as clustered indexes, with two significant differences: o The data rows are not sorted and stored in order based on their non-clustered keys. o The leaf layer of a non-clustered index does not consist of the data pages. Instead, the leaf nodes contain index rows. Each index row contains the non-clustered key value and one or more row locators that point to the data row (or rows if the index is not unique) having the key value. o Per table only 249 non clustered indexes. 30. Disadvantage of index? Every index increases the time in takes to perform INSERTS, UPDATES and DELETES, so the number of indexes should not be very much. 31. Given a scenario that I have a 10 Clustered Index in a Table to all their 10 Columns. What are the advantages and disadvantages? A: Only 1 clustered index is possible.

32. How can I enforce to use particular index?
You can use index hint (index=) after the table name.
SELECT au_lname FROM authors (index=aunmind)

33. What is Index Tuning?
One of the hardest tasks facing database administrators is the
selection of appropriate columns for non-clustered indexes. You should
consider creating non-clustered indexes on any columns that are
frequently referenced in the WHERE clauses of SQL statements. Other
good candidates are columns referenced by JOIN and GROUP BY operations.
You may wish to also consider creating non-clustered indexes that
cover all of the columns used by certain frequently issued queries.
These queries are referred to as "covered queries" and experience
excellent performance gains.
Index Tuning is the process of finding appropriate column for
non-clustered indexes.
SQL Server provides a wonderful facility known as the Index Tuning
Wizard which greatly enhances the index selection process.

34. Difference between Index defrag and Index rebuild?
When you create an index in the database, the index information used
by queries is stored in index pages. The sequential index pages are
chained together by pointers from one page to the next. When changes
are made to the data that affect the index, the information in the
index can become scattered in the database. Rebuilding an index
reorganizes the storage of the index data (and table data in the case
of a clustered index) to remove fragmentation. This can improve disk
performance by reducing the number of page reads required to obtain
the requested data
DBCC INDEXDEFRAG - Defragments clustered and secondary indexes of the
specified table or view.
**

35. What is sorting and what is the difference between sorting &
clustered indexes?
The ORDER BY clause sorts query results by one or more columns up to
8,060 bytes. This will happen by the time when we retrieve data from

database. Clustered indexes physically sorting data, while
inserting/updating the table.

36. What are statistics, under what circumstances they go out of date,
how do you update them?
Statistics determine the selectivity of the indexes. If an indexed
column has unique values then the selectivity of that index is more,
as opposed to an index with non-unique values. Query optimizer uses
these indexes in determining whether to choose an index or not while
executing a query.
Some situations under which you should update statistics:
1) If there is significant change in the key values in the index
2) If a large amount of data in an indexed column has been added,
changed, or removed (that is, if the distribution of key values has
changed), or the table has been truncated using the TRUNCATE TABLE
statement and then repopulated
3) Database is upgraded from a previous version

37. What is fillfactor? What is the use of it ? What happens when we
ignore it? When you should use low fill factor?
When you create a clustered index, the data in the table is stored in
the data pages of the database according to the order of the values in
the indexed columns. When new rows of data are inserted into the table
or the values in the indexed columns are changed, Microsoft® SQL
Server™ 2000 may have to reorganize the storage of the data in the
table to make room for the new row and maintain the ordered storage of
the data. This also applies to nonclustered indexes. When data is
added or changed, SQL Server may have to reorganize the storage of the
data in the nonclustered index pages. When a new row is added to a
full index page, SQL Server moves approximately half the rows to a new
page to make room for the new row. This reorganization is known as a
page split. Page splitting can impair performance and fragment the
storage of the data in a table.
When creating an index, you can specify a fill factor to leave extra

gaps and reserve a percentage of free space on each leaf level page of
the index to accommodate future expansion in the storage of the
table's data and reduce the potential for page splits. The fill factor
value is a percentage from 0 to 100 that specifies how much to fill
the data pages after the index is created. A value of 100 means the
pages will be full and will take the least amount of storage space.
This setting should be used only when there will be no changes to the
data, for example, on a read-only table. A lower value leaves more
empty space on the data pages, which reduces the need to split data
pages as indexes grow but requires more storage space. This setting is
more appropriate when there will be changes to the data in the table.
For More SQL SERVER Frequently Asked Interview Questions

SQL Queries FAQ

SQL Queries FAQ

T-SQL Queries
1. 2 tables
Employee Phone
empid
empname
salary
mgrid empid
phnumber

2. Select all employees who doesn't have phone?
SELECT empname
FROM Employee
WHERE (empid NOT IN
(SELECT DISTINCT empid
FROM phone))

3. Select the employee names who is having more than one phone numbers.
SELECT empname
FROM employee
WHERE (empid IN
(SELECT empid
FROM phone
GROUP BY empid
HAVING COUNT(empid) > 1))

4. Select the details of 3 max salaried employees from employee table.
SELECT TOP 3 empid, salary
FROM employee
ORDER BY salary DESC

5. Display all managers from the table. (manager id is same as emp id)
SELECT empname
FROM employee
WHERE (empid IN
(SELECT DISTINCT mgrid
FROM employee))

6. Write a Select statement to list the Employee Name, Manager Name
under a particular manager?
SELECT e1.empname AS EmpName, e2.empname AS ManagerName
FROM Employee e1 INNER JOIN
Employee e2 ON e1.mgrid = e2.empid
ORDER BY e2.mgrid

7. 2 tables emp and phone.
emp fields are - empid, name
Ph fields are - empid, ph (office, mobile, home). Select all employees
who doesn't have any ph nos.
SELECT *
FROM employee LEFT OUTER JOIN
phone ON employee.empid = phone.empid
WHERE (phone.office IS NULL OR phone.office = ' ')
AND (phone.mobile IS NULL OR phone.mobile = ' ')
AND (phone.home IS NULL OR phone.home = ' ')

8. Find employee who is living in more than one city.
Two Tables:Emp City
Empid Empid
empName City
Salary
SELECT empname, fname, lname
FROM employee
WHERE (empid IN
(SELECT empid
FROM city
GROUP BY empid
HAVING COUNT(empid) > 1))

9. Find all employees who is living in the same city. (table is same
as above)
SELECT fname
FROM employee
WHERE (empid IN
(SELECT empid
FROM city a
WHERE city IN
(SELECT city
FROM city b
GROUP BY city
HAVING COUNT(city) > 1)))

10. There is a table named MovieTable with three columns - moviename,
person and role. Write a query which gets the movie details where Mr.

Amitabh and Mr. Vinod acted and their role is actor.
SELECT DISTINCT m1.moviename
FROM MovieTable m1 INNER JOIN
MovieTable m2 ON m1.moviename = m2.moviename
WHERE (m1.person = 'amitabh' AND m2.person = 'vinod' OR
m2.person = 'amitabh' AND m1.person = 'vinod') AND (m1.role = 'actor')
AND (m2.role = 'actor')
ORDER BY m1.moviename

11. There are two employee tables named emp1 and emp2. Both contains
same structure (salary details). But Emp2 salary details are incorrect
and emp1 salary details are correct. So, write a query which corrects
salary details of the table emp2
update a set a.sal=b.sal from emp1 a, emp2 b where a.empid=b.empid

12. Given a Table named "Students" which contains studentid, subjectid
and marks. Where there are 10 subjects and 50 students. Write a Query
to find out the Maximum marks obtained in each subject.

13. In this same tables now write a SQL Query to get the studentid
also to combine with previous results.

14. Three tables – student , course, marks – how do go @ finding name
of the students who got max marks in the diff courses.
SELECT student.name, course.name AS coursename, marks.sid, marks.mark
FROM marks INNER JOIN
student ON marks.sid = student.sid INNER JOIN
course ON marks.cid = course.cid
WHERE (marks.mark =
(SELECT MAX(Mark)
FROM Marks MaxMark
WHERE MaxMark.cID = Marks.cID))

15. There is a table day_temp which has three columns dayid, day and
temperature. How do I write a query to get the difference of
temperature among each other for seven days of a week?
SELECT a.dayid, a.dday, a.tempe, a.tempe - b.tempe AS Difference
FROM day_temp a INNER JOIN
day_temp b ON a.dayid = b.dayid + 1

OR
Select a.day, a.degree-b.degree from temperature a, temperature b
where a.id=b.id+1

16. There is a table which contains the names like this. a1, a2, a3,
a3, a4, a1, a1, a2 and their salaries. Write a query to get grand
total salary, and total salaries of individual employees in one query.
SELECT empid, SUM(salary) AS salary
FROM employee
GROUP BY empid WITH ROLLUP
ORDER BY empid

17. How to know how many tables contains empno as a column in a database?
SELECT COUNT(*) AS Counter
FROM syscolumns
WHERE (name = 'empno')

18. Find duplicate rows in a table? OR I have a table with one column
which has many records which are not distinct. I need to find the
distinct values from that column and number of times it's repeated.
SELECT sid, mark, COUNT(*) AS Counter
FROM marks
GROUP BY sid, mark
HAVING (COUNT(*) > 1)

19. How to delete the rows which are duplicate (don't delete both
duplicate records).
SET ROWCOUNT 1
DELETE yourtable
FROM yourtable a
WHERE (SELECT COUNT(*) FROM yourtable b WHERE b.name1 = a.name1 AND
b.age1 = a.age1) > 1
WHILE @@rowcount > 0
DELETE yourtable
FROM yourtable a
WHERE (SELECT COUNT(*) FROM yourtable b WHERE b.name1 = a.name1 AND
b.age1 = a.age1) > 1
SET ROWCOUNT 0

20. How to find 6th highest salary
SELECT TOP 1 salary
FROM (SELECT DISTINCT TOP 6 salary
FROM employee
ORDER BY salary DESC) a
ORDER BY salary

21. Find top salary among two tables
SELECT TOP 1 sal
FROM (SELECT MAX(sal) AS sal
FROM sal1
UNION
SELECT MAX(sal) AS sal
FROM sal2) a
ORDER BY sal DESC

22. Write a query to convert all the letters in a word to upper case
SELECT UPPER('test')

23. Write a query to round up the values of a number. For example even
if the user enters 7.1 it should be rounded up to 8.
SELECT CEILING (7.1)

24. Write a SQL Query to find first day of month?

SELECT DATENAME(dw, DATEADD(dd, - DATEPART(dd, GETDATE()) + 1,
GETDATE())) AS FirstDay
Datepart Abbreviations
year yy, yyyy
quarter qq, q
month mm, m
dayofyear dy, y
day dd, d
week wk, ww
weekday dw
hour hh
minute mi, n
second ss, s
millisecond ms

25. Table A contains column1 which is primary key and has 2 values (1,
2) and Table B contains column1 which is primary key and has 2 values
(2, 3). Write a query which returns the values that are not common for
the tables and the query should return one column with 2 records.
SELECT a.col1
FROM a, b
WHERE a.col1 <>
(SELECT b.col1
FROM a, b
WHERE a.col1 = b.col1)
UNION
SELECT b.col1
FROM a, b
WHERE b.col1 <>
(SELECT a.col1
FROM a, b
WHERE a.col1 = b.col1)

26. There are 3 tables Titles, Authors and Title-Authors. Write the
query to get the author name and the number of books written by that
author, the result should start from the author who has written the
maximum number of books and end with the author who has written the
minimum number of books.

27.
UPDATE emp_master
SET emp_sal =
CASE
WHEN emp_sal > 0 AND emp_sal <= 20000 THEN (emp_sal * 1.01) WHEN emp_sal > 20000 THEN (emp_sal * 1.02)
END

Page 5

SQL Injection Problem, Example and Solution for Preventing

SQL Injection Problem, Example and Solution for Preventing:

What is SQL Injection Problem? SQL injection is a strategy for attacking databases.

Example of SQL Injection Problem:An ASP page asks the user for a name and a password, and then sends the following string to the database:
SELECT FROM users WHERE username = 'whatever' AND password = 'mypassword'

It seems safe, but it isn't. A user might enter something like this as her user name:
' OR 1>0 --

When this is plugged into the SQL statement, the result looks like this:
SELECT FROM users WHERE username = '' OR 1>0 -- AND password = ''

This injection comments out the password portion of the statement. It results in a list of all the names in the users table, so any user could get into your system.

How to Prevent SQL Injection Problem.
There are numerous ways a malicious user might penetrate your system using SQL injection and various defenses, but the simplest approach is to avoid dynamic SQL. Instead, use stored procedures everywhere.

Sunday, July 01, 2007

Free e-learning on SQL Server 2008

Free e-learning on SQL Server 2008
https://www.microsoftelearning.com/eLearning/courseDetail.aspx?courseId=78337

Title: Clinic 7045: What's New in Microsoft® SQL Server™ 2008
Course Type: Self-paced Course
Available Offline: Yes
Estimated Time of Completion: 2 Hours
Language: English
Description: In this clinic, you will learn about the new and enhanced features included in SQL Server 2008. You will explore the new data types and the data management features. Additionally, you will learn about the enhanced Integration Services, Analysis Services, and Reporting Services included in SQL Server 2008. This online clinic is composed of a rich multimedia experience.

Objectives: At the end of the course, students will be able to:

Describe the features of SQL Server 2008 and their benefits.
Describe the features of enterprise data platform that help you to secure data in applications.
Describe the dynamic development features that facilitate the development of database applications.
Describe the features of SQL Server 2008 that provide data storage solutions beyond relational databases.
Describe the enhanced features in SSIS that help you to integrate data effectively.
Describe the enhanced features in SSAS that help you to improve the BI infrastructure.
Describe the enhanced features in SSRS that help you to improve the scalability of the reporting engine.

Friday, June 15, 2007

Login to SQL Server 2005 with Administration Privileges on Windows Vista OS

Login to SQL Server 2005 with Administration Privileges on Windows Vista OS you need to explicitly login as Administrator to perform administration task on sql server 2005 objects.

Look in figure for how to login explicitly as administrator privileges on windows vista OS

So now you can able to avoid many errors like Database creation, etc

Example:
Create failed for Database 'SampleDB'. (Microsoft.SqlServer.Smo)

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

CREATE DATABASE permission denied in database 'master'. (Microsoft SQL Server, Error: 262)

Asp.net Connectivity with SQL Server 2005

Asp.net Connectivity with SQL Server 2005
You will get number of queries on forums for connecting application to database. Most of time they are unable to connect because of incorrect connection string.

Step 1: Adding connection string code to web.config file

Syntax for connection string

<appsettings>
<add source="[ServerName]" value="" key="MyDBConnection">;Initial catalog =[dbname];user id=[username];password=[password];" />
</appsettings>

Example for connection string

Here, its an example of Windows Authentication Mode

<appsettings>
<add value="Data Source=SHRIGANESH-PC\SQLEXPRESS;Initial Catalog=firstdb;Integrated Security=True" key="conn">
</appsettings>

Step 2: Writing Code

Lets write code to display data in gridview.

private void BindGrid()
{
string sqlconn = ConfigurationManager.AppSettings["conn"].ToString();
SqlConnection conn = new SqlConnection(sqlconn);
SqlDataAdapter da = new SqlDataAdapter("select * from employee", sqlconn);
DataSet ds = new DataSet();
da.Fill(ds);
GridView1.DataSource = ds.Tables[0].DefaultView;
GridView1.DataBind();
}

Solution for everyone, anytime

For all who are facing problem while connection application to backend, I would
suggest them to connect the application SqlDataSource Control and configure the
datasource through wizard, so that a misprint of character in your connection
string can be avoided and you can be on work without posting query on forum.

Finally Exception that you might face while connecting to .net application with sql server 2005 on windows vista operating system.

Asp.net connectivity with Sql Server 2005 is same as connecting to Sql Server
2000, but you can be put into trouble due to incorrect connection string and may
receive following error exception

The user is not associated with a
trusted SQL Server connection.

A connection was successfully
established with the server, but then an error occurred during the login
process.

To resolve the above error, check that connection string
you have used for connectivity is correct.

Saturday, June 09, 2007

SQL OPERATORS

The Operators Supported by SQL*Plus are as under
1) Comparision Operators
2) Logical Operators
3) Arithmetic Operators
4) Operators used to negate conditionsThey are discussed in brief as under.

SQL COMPARISION OPERATORSComparision Operators as the name sujjests are used to test values in SQL statement.
The comparision operators are as under
* Equality (=)
* Non-Equality (<>)
* Greater-than (>)
* Less-than (<) * Greater-than or equal to (>=)
* Less-than or equal to (<=) All Operators Works according to their names.

For example:-

1)SQL> select * from emp
where
emp_name='RAHUL';
->It will select all details of employee whose name is rahul.

2) SQL> select emp_name from emp
where
emp_age > 50;
-> It will list names of those employee whose age is above 50 yrs.

3) SQL> select emp_name from emp
where
emp_job = 'ACCOUNTANT' OR emp_job= 'MUNIM';
->It will list names of those employee whose status is munim or accountant in the company.
->Note, if any of condition satisfies than the record will display as they are joint using or operator(discussed later).

SQL LOGICAL OPERATORS-> Logical operators are those operators that are use SQL keywords to make comparision instead of symbols.
->A Logical operator is used to combine the results of two conditions.
The Logical Operators are AND, OR and NOT.
They also covers
* LIKE
* BETWEEN
* IN
* IS NULL
* ALL and ANY

AND- AND operator display records only when both conditions are satisfied.
eg:
SQL>select * from emp
where
emp_age < emp_name="'SAJID';">The above eg shows rows for those employee whose age is less than 50 yrs and name is sajid

OR- OR operator display records on matching of any condition.
eg:
SQL>select * from emp
where
emp_age < emp_name="'SAJID';">The above eg shows rows for those employee whose age is less than 50 yrs or name is sajid.

NOT- explained under negate operators.

LIKE
->LIKE operator is used to search a character pattern, without knowing exact character value.
->The LIKE operator used wildcard operators to compare a value to similar values.
->There are two wildcards used in conjunction with LIKE operator.
( % ) The percent sign-The percent sign represents Zero, one
or multiple characters.
( _ ) The Underscore-The underscore represents single
number or characters.
->These symbols can be used in combinations.
eg:
1)SQL> select * from emp
where
emp_name LIKE 'A%';
->Finds any name that start with A and continues for any length.

2) SQL>select * from emp
where
emp_name LIKE '%A';
->Finds any name that ends with A.

3) SQL>select * from emp
where
emp_name LIKE '%A%';
->Finds any name that have A in any position.

4) SQL>select * from emp
where
emp_salary LIKE '3___5';
->Finds any value in a five digit number that starts with 3 and end with 5.

5) SQL>select * from emp
where
emp_salary LIKE '_5%5';
->Finds any values that have a 5 in the second position and ends with a 5.

BETWEEN->The BETWEEN operator is an extension of comparision operator as to make more user friendly.
->The BETWEEN operator is used to search for values whose minimum and maximum values are given. Maximum and minimum values are included as a part of search.
-> A Between operator can be used with both character and
numeric datatypes. However, one cannot mix the data types.
eg:
1)SQL>select * from emp
where
emp_salary BETWEEN 5000 AND 10000;
->It searches for those employee whose salary is lies between 5000 and 10000, including the values 5000 and 10000.

IN->The IN operator search the value from a predetermined list, if any of the value matches than row will be displayed.
eg:
1)SQL>select * from emp
where
emp_job
IN ('ACCOUNTANT','PROGRAMMER','OPERATOR');
->It will display all details of those employee whose status in company is either accountant, programmer or operator.Note IN operator is basically use to remove multiple OR operators. The above statement using OR operator will be
emp_job='ACCOUNTANT' OR emp_job='PROGRAMMER' OR emp_job='OPERATOR';
Note here emp_job is repeated three times which is not efficient way of generating query, so using IN operator we can reduce our burden and also make our statement more efficient.

2)SQL>select * from emp
where
emp_depart_no IN ('5','10','15','20');
->It will display details of those employee whose department number is either 5,10,15 or 20.

IS NULL->IS NULL operator is used to compare a value with a NULL value.
eg:
->Let search for the employee who haven't have their email-id
1) SQL> select * fromemp
where
email_id IS NULL;
->It will display records of those employee who haven't created their email-id.
NOTE:- If you tried NULL operator to use with equality operator it will not give proper result.consider above eg. if you modify above statement using equality operator it will display improper result.
email_id = NULL does not find a NULL value. You will be prompted with the message no rows selected even though there are rows with a NULL values.

2)SQL>select * from emp
where
emp_salary IS NULL;
->It will display records of those employee whose pay field is NULL. This is consider as invalid's records and should be deleted immediately.

ALL and ANY
ALL->The ALL operator compares a value to all values in another value set.
eg:
1)SQL>select * from emp
where
emp_salary > ALL
(select emp_salary from emp where emp_job = 'OPERATOR');
->It will compares salary value to see whether it is greater than all salaries of the employee whose status is operator.

2)SQL>select * from emp
where
emp_age <= ALL (select emp_age from emp where emp_state='CA'); ->It will compares age value to see whether it is less than or equal to all employee's age who is staying in CA.

ANY->The ANY operator compares a value to any values in another value set.
eg:
1)SQL>select * from emp
where
emp_salary > ANY
(select emp_salary from emp where emp_job = 'PROGRAMMER');
->It will compares salary value to see whether it is greater than ANY salaries of the employee whose status is programmer.

2)SQL>select * from emp
where
emp_age <= ANY (select emp_age from emp where emp_state='CA'); ->It will compares age value to see whether it is less than or equal to any employee's age who is staying in CA.

SQL ARITHMETIC OPERATORS
->The arithmetic operators are mentioned as under
1) Addition ( + )
2) Subtraction ( - )
3) Multiplication ( * )
4) Division ( / )They work same as we have been taught so far.
->Let consider usage of arithmetic operator by following result table It contains field like rno, stname, std, div, totsub, totmarks.
where rno is roll number, stname is student name, std is standard, div is division, totsub is total subjects and totmarks is total marks.

1) To find percentage of all student
SQL>select rno,stname,(totmarks / totsub) as percentage
from result;
->Here help of division operator is taken to get percentage.

CONCEPT OF ALIASES
Aliases - Aliases is a temporary name assign to the field created at
execution of statement or field already there in a table.

consider the above example, field is created at the execution of statement and the name percentage is aliases for it.
eg:
1)SQL>select ename as employee_name from emp;
->here field is already there in a table and temporary name is created to display field title more user friendly.
->Note aliases is not compulsory on a field it is used just to interface more user friendly with sql statements.

Now back to the discussion of arithmetic operators,
continue with the examples

2)SQL>select prodname as Product_Name,qty as Quantity,
price as Price_per _unit,(qty*price) as Amount
from product
where
prodname = 'SCREW';
->Here query contains aliases Product_Name for prodname, Quantity for qty, Price_per_unit for price and Amount for (qty*price)
->This query display product name, quantity, price and amount for records whose prodname is screw.
->Here Amount is a field which will be created at the time of execution. It uses the multiplication operator.

Similarly we use arithmetic operators according to our requirement but the only point to note is that while using multiple operators operators works according to the precedence.
* and / have higher priority than + and - operator, but we can make priority of + and - higher than * and / by adding parenthesis to the statement.

SQL NEGATE OPERATORS
The Negate Operators reverses the meaning of the logical operators with which it is used.
We have explained some NOT operators which are mentioned as under.
* NOT EQUAL ( <> ) or ( != ).
* NOT BETWEEN
* IS NOT NULL
* NOT LIKE
* NOT IN
NOT EQUAL->Not equal works exactly oposite to the equal operator.
eg:
1)SQL>select * from emp
where
emp_job <> 'ACCOUNTANT';
->Here all records are selected excluding those whose status is accountant in company.

2)SQL>select * from emp
where
emp_salary != 5000;
->Selects all records excluding those whose salary is 5000.
NOTE:- Operator ( <> ) and ( != ) works same.

NOT BETWEEN-> This operator is used negate the between operator.
eg:
1)SQL>select * from emp
where
emp_salary NOT BETWEEN 2000 and 3000.
->It will select all records excluding those whose salary is between 2000 and 3000, including 2000 and 3000.

IS NOT NULL->It will check whether the selected field is not empty(null).
eg:
1)SQL>select * from emp
where
email_id IS NOT NULL;
->Selects records of those employee who have their email-id.

NOT LIKE->The NOT LIKE operator used wildcard operators to compare a value that is not similar.
->It supports both wildcard character that are mentioned in like operators.
eg:
1)SQL> select * from emp
where
emp_name NOT LIKE 'A%';
->Finds any name that do not start with A and continues for any length.

2) SQL>select * from emp
where
emp_name NOT LIKE '%A';
->Finds any name that do not ends with A.

3) SQL>select * from emp
where
emp_salary NOT LIKE '1___0';
->Finds any value in a five digit number that do not starts with 1 and end with 0.

NOT IN->The NOT IN operator search the value that is not mentioned in a predetermined list, if any of the value matches than row will be displayed.
eg:
1)SQL>select * from emp
where
emp_job
NOT IN ('PROGRAMMER','OPERATOR');
->It will display all details of employee excluding those whose status in company is either programmer or operator.

2)SQL>select * from emp
where
emp_depart_no NOT IN ('5','10','15','20');
->It will display details of employee excluding those whose department number is either 5,10,15 or 20.

Related Links

TRANSACTION CONTROL LANGUAGE (TCL)

TRANSACTION:-Collection of operation that forms a single logical unit of work are called Transactions.
In other words, Transactions are units or sequences of work accomplished in logical order, whether in a manual fashion by a user or automatically by some sort of a database program. In a relational database using SQL, transactions are accomplished using the DML commands, which are already discussed.
A transaction can either be one DML statement or a group of statements. When managing groups of transactions, each designated group of transactions must be successful as one entity or none of them will be successful.
The Following list describes the nature of transactions:
->All transactions have a begining and an end.
->A transaction can be saved or undone.
->If a transaction fails in the middle, no part of the transaction can be saved to the database.

TRANSACTIONAL CONTROLTransactional control is the ability to manage various transactions that may occur within a relational database management system. (Note keep in mind that transaction is group of DML statements).
When a transaction is executed and completes successfully, the target table is not immediately changed, although it may appear so according to the output. When a transaction successfully completes, there are transactional control commands that are used to finalize the transaction.
There are three commands used to control transactions:
1) COMMIT
2) ROLLBACK
3) SAVEPOINT
When transaction has completed, it is not actually taken changes on database, the changes reflects are temporary and are discarded or saved by issuing transaction control commands. Explanatory figure is drawn as under.

TRANSACTIONAL CONTROL COMMANDS
1) COMMIT Command
->The commit command saves all transactions to the database since the last COMMIT or ROLLBACK command.
Syntax
commit [work];

The keyword commit is the only mandatory part of the syntax. Keyword work is optional; its only purpose is to make the command more user-friendly.

example
SQL>delete from emp
where
emp_age > 75;
->The above command deletes the records of those employee whose age is above 75 yrs. Though the changes are reflected on database they are not actually save as explained above they are stored in temporary area. To allow changes permanently on database commit command is used.

SQL> COMMIT WORK;
->The above command will made changes permanently on database, since last commit or rollback command was issued.
note here work is totally optional, it is just to make command more user friendly.

2) ROLLBACK Command->The rollback command is the transactional control command used to undo transactions that have not already been saved to the database. The rollback command can only be used to undo transactions since the last COMMIT or ROLLBACK command was issued.

Syntax
SQL>rollback [work];

The keyword rollback is the only mandatory part of the syntax. Keyword work is optional; its only purpose is to make the command more user-friendly.

example
SQL>delete from emp
where
emp_age > 75;
->The above command deletes the records of those employee whose age is above 75 yrs. Though the changes are reflected on database they are not actually save as explained above they are stored in temporary area. To discards changes made on database rollback command is used.

SQL> ROLLBACK WORK;
->The above command will discards changes made on database,since last commit or rollback command was issued.
note here work is totally optional, it is just to make command more user friendly.

3) SAVEPOINT Command
->A savepoint is a point in a transaction that you can roll the transaction back to without rolling back the entire transaction.
->Practical example
consider that a person walking and after passing some distance the road is split into two tracks. The person were not sure to choose which track, so before randomly selecting one track he make a signal flag, so that if the track was not the right one he can rollback to signal flag and select the right track. In this example the signal flag becomes the savepoint. Explanatory figure is as under.

Syntax
SQL>SAVEPOINT
->Savepoint name should be explanatory.

example
->Before deleting the records of employee whose age is above 75, we are not sure that whether we are given work to actually delete the records of employee whose age is above 75yrs or 80yrs. So before proceding further we should create savepoint here if we are been order later than it might create loss of information.
SQL>savepoint orignally;

SQL>delete from emp
where
emp_age > 75;
->The above command deletes the records of those employee whose age is above 75 yrs. Though the changes are reflected on database they are not actually save as explained above they are stored in temporary area.

->After some time we are given order to increase employee salary to 10%. We can increase by generating following command. But before that we will make savepoint to our data so incase if the top level management change their mind and order's no increment should be given than we have can simply pass rollback entry achieve present state.
SQL>savepoint increm_sal;

SQL>update emp
set salary=salary + (salary*10);
->It will Increase salary of employee by 10%.

->After sometime top level management decided that salary of only programmer should be increased by 10% than only we have to do is just to pass entry of rollback before salary is updated.

SQL>rollback to increm_sal;
->It will rollback the changes made to emp_salary now we can update salary records for employee who is programmer. If we have dout than we can put savepoint, otherwise savepoint is not compulsory.

SQL>update emp
set salary=salary + (salary*10);
where
emp_status='PROGRAMMER';
->It will increase salary of only programmers.

If all the changes have been taken place and now we have decided that no further changes require and we have to made changes to apply on database permanently than we can simply generate commit command to reflect changes permanently on database.

SQL>commit work;
Related Links

DATA CONTROL LANGUAGE (DCL)

INTRODUCTION to DCL

DATA CONTROL LANGUAGE is known as DCL.
DCL Statement is used for securing the database.
DCL Statement control access to database.
As data is important part of whole database system we must take proper steps to check that no invalid user access the data and invalidate the information created by us. To kept such a kind of watch we must have to execute certain DCL statement.
Two main DCL statement are Grant and Revoke.

GRANT Statement- Grant privilege(Rights which are to be allocated) is used when we want our database to share with other users, with certain type of right granted to him. Consider that if we want our enduser to have only access privilege to our database, we can grant it by executing command.
- Grant privilege is assigned not only on table object, but also views, synonyms, indexes, sequences,etc.

Syntax:
GRANT PRIVILEGES ON
TO

Example
1)SQL> grant select on
emp to endusers;
- here emp is the table_name whose access right is being allocated to the user who logged on as endusers.
11)SQL>grant insert,select,delete on
emp to operators;
- here user who logged on as operators are granted access, insertion and deletion right on the database.
111)SQL>grant insert (empno, ename, job) on
emp to endusers;
- In some case, we require to hide the information to particular users, this can be achived by grant as in the above command we want to hide the detail of employee salary to endusers, so by executing above command we can hide the information regarding empsalary to the endusers.

REVOKE Statement- Revoke privilege(Rights which are to be de-allocated) is used when we want our database to stop sharing the information with other users, with certain type of right revoked to him. Consider that if we want our operators to have only access privilege to our database, we can revoke it by executing command.
- Revoke privilege is assigned not only on table object, but also views, synonyms, indexes, sequences,etc.

Syntax
REVOKE PRIVILEGES ON
FROM

example
1)SQL> revoke insert, delete on
emp from operators;
Related Links

DATA MANIPULATION LANGUAGE (DML)

INTRODUCTION to DML
By data manipulation language we mean:
->The retrieval of information stored in the database.
->The insetion of new information into the database.
->The deletion of information from the database.
->The modification of data stored in the database.
Thus, it is a language that enables users to access or manipulate data as organised by the appropriate data model.

There are basically two types of DML
i>Procedural DMLs->It requires a user to specify what data is needed and how to get it.
ii>Non-Procedural DMLs->It requires a user to specify what data is needed without specifying how to get it.

As SQL is Non-Procedural language we will switch on to Non-Procedural DMLs as it is easy to understand and became very efficient for new users to begin with it.

The category of DML contains four basic statements:
i>select - which retrieves rows from a table.
ii>Insert - Which adds rows to a table.
iii>Update - Which modifies existing rows in a table.
iv>Delete - Which removes rows from a table.

SELECT Statement
->To view all rows or some of the rows from a table or more than one table depending upon a userdefined criteria,this command is used.
->By default, select statement display all rows which matches the criteria but we can get the unique records by using keyword distinct with it.

syntax:
SELECT [DISTINCT ALL] FROM
WHERE

->keyword ALL is optional by default it consider ALL rows in table.

example:
1)SQL>select * from emp;
->It will display all rows of emp table including all feilds.we can customize the output by selecting the columns which are needed to us.
2)SQL>select empno,ename from emp;
->It will display all rows of emp table including empno and employee_name detail for an employee.
3)SQL>select * from emp
where
job = 'clerk';
->It will display all details of employee whose status is clerk.
4)SQL>select distinct ename from emp;
->It will display all unique name of employee if employee_name is repeated twice than it will display only ones.Thus it discards the duplicate records.

INSERT Statement->INSERT command is used to insert one or more rows in a table.
(There are many of syntax for insert command but one mentioned as under is the only standard way to insert multiple rows.)

syntax:-
INSERT INTO
(........)
VALUES
(<&Fieldname1>......<&FieldnameN>)
->Where the Fieldname should be valid field for a table.
->Field having datatype char,varchar2 and Date kind of data should be written in single quota.

examples:
1)SQL>Insert into emp
(empno,ename,job)
values
('1','SHREY','MANAGER');
->Above command will insert data for one record, here as data are mentioned directly, so we have made use of single quota.
2)SQL>Insert into emp
(empno,job)
values
(&empno,'&amp;job');
->Here we have customize the insert command to take data for only two field that is empno and job for multiple records.
->When you don't want to type the command which is used last than simply press the slash to activate the command which is used by you last.
3)SQL>Insert into emp
values
(&empno,'&amp;ename','&job');
->Note in Above command we haven't declare the field in which data is to be entered, it is optional when we have to enter data for all fields in column.
4)SQL>Insert into emp
(empno,ename,job)
values
('5','VRAJ',NULL);
->The above command will create a row and assign value 5 for empno and vraj for ename and leave the column job.
->If we doesn't want to enter value for a particular field we can just type NULL in it place during defining the INSERT command. And just press enter while entering the value.
5)SQL>Insert into emp_bkup
(select * from emp);
->The above command will copies all the rows from table emp and insert it into the table emp_bkup, provided that the structure of emp and emp_bkup is same.
->The above command is efficient to run when we want to create backup copy of our data.

UPDATE Statement
->Update command is used to modify content of table, that to under satisfaction of particular criteria.

syntax:
UPDATE
SET =
WHERE
;
->Where Clause is optional.
->Fieldname is name of column whose contents are to be manipulated.

example:
1)SQL>Update emp
set job = 'ACCOUNTANT'
where
job = 'MUNIM';
->Above sql statement will Modify Job field for employee whose status is munim, it will update the status munim with accountant.
2)SQL>Update emp
set salary = salary + (salary * 10)
where
job = 'PROGRAMMER';
->Above statement will increase salary of employee by 10% whose status is programmer.

DELETE Statement
->DELETE command is used to delete one or more rows from the table.
Note:-No warnings are given before deleting rows so be careful while performing this operation.

syntax:
DELETE FROM
WHERE
;
->Table_name is name of table from which you want to delete record/s.
->Criteria is condition under which you want to delete record/s.

example:
1)SQL>Delete from emp
Where
empno = 4;
->Above statement remove record of empno 4.
->Only one record is deleted.
2)SQL>Delete from emp
Where
job = 'OPERATOR';
->Above statement remove record/s of those employee whose status is operator in the company.

Related Links

DATA DEFINITION LANGUAGE (DDL)

INTRODUCTION to DDL

A database scheme is specified by a set of definition which are expressed by a special language called a Data Definition Language. The result of compilation of DDL statement is a set of tables which are stored in a special file called Data Dictionary.
-> DDL defines the structure of data.
-> DDL statements in SQL language are responsible for creating or modifying database structures such as tables, views, and indexes.

Let us now understand DDL statements and how it works on Oracle database.

TABLE(A table consist of Rows and Columns. Table is a collection of related records that are treated as a unit. )

Convention for Naming a Table in Oracle-> Each table owned by a Oracle account must have a unique name.
-> A table name cannot exceed 30 characters in length.
-> A table name must begin with an alphabetic character.
-> A table name can contain the letters A through Z, the digits 0 through 9, and the characters $, #, and _(underscore).
-> A table name cannot be an SQL reserved word.
-> You can use uppercase and lowercase characters in naming tables; oracle is not case sensitive as long as table or column names are not enclosed in double quotes.
-> A table name should be descriptive.

Convention for Naming Column in Oracle.-> Within a single table, a column name must be unique. However, you may use the same column name in different tables.
-> Like other database objects, a column name can be upto 30 characters long.
-> The column name must begin with an alphabetic character.
-> The column name can contain the letters A through Z, the digits 0 through 9, and the characters $, #, and _(underscore). The name cannot include spaces.
-> A column name cannot be an SQL reserved word.
-> As with tables, be descriptive in naming a column. Descriptive column names help users understand the definition of each column.
-> An Oracle table may have upto 254 columns.

Commands Related To Table
1) Syntax to CREATE TableCREATE TABLE
(
[column level constraint],
:
:
,
[Table level constraint]
);
Here,
-> Table_name is the name for the table.
-> Column_name1 through Column_nameN are valid column names.
-> Datatypes is a valid Oracle datatype specification.
-> Constraint_name is a optional, but it should be used in order to avoid invalid information to be entered in the database.
NOTE:-you can assign default value for field by using the keyword default and specifying default value for that field.
eg:-city varchar2(25) default 'AHMEDABAD'
(Though SQL is not case sensitive, but contents of table are case sensitive so it is good practise to write data always in uppercase).
Tip - CHAR, VARCHAR AND DATE kind of data should be written in single quota.

example
sql> create table emp
(
emp_no number (6) primary key,
emp_name varchar2(35),
emp_address varchar2(45),
emp_city varchar2(30),
emp_state varchar2(30),
emp_age number(2),
emp_sex char,
emp_department varchar2(30)
);
Table created.

2) Describe Table-> This command will describe the table.
Syntax for describing table
sql> desc ;
or
sql> describe ;
example
sql> desc emp;

3) ALTER Table-> Alter command is used to make modification after table has created.
-> Modification like to add column, to drop constraint like primary key,etc., However you cannot delete the column nor you can decrease the width of column.
Syntax to ALTER Table definition
ALTER TABLE
[ADD MODIFY DROP]
( ,
:
:
);

examples

-> To Add column in a table.
sql> alter table emp
add
(emp_phno number(10));

-> To Modify Table
sql> alter table emp
modify
(
emp_city varchar2(35),
emp_state varchar2(35)
);

-> To Drop Table's Primary key and other constraints.
general syntax
ALTER TABLE
[DROP ENABLE DISABLE]
CONSTRAINT ;

sql> alter table emp
drop primary key;

After successfully completion of above command you get the message table altered.

4) Command to Delete contents of Table
TRUNCATE-> Truncate command will delete all the table contents by removing all the rows(records) from the table.
-> After the implementation of this command, only the structure of the table remains.
Syntax for Truncate command
sql> TRUNCATE TABLE ;
example
sql> truncate table emp;
-> It will delete all the rows of the emp table and only structure of the table remains.

5) Command to Delete Table
DROP-> Drop command will delete the table contents and its structure.
Syntax for Drop command
sql> DROP TABLE ;
example
sql> drop table emp;
-> It will delete all the rows and the structure of emp table.

6) Command to Rename Table-> Rename command will rename the table name.
Syntax for Rename command
Sql > RENAME TO
example
sql> Rename employee to emp;
-> Now the table name is renamed and you can manipulate it using name emp.

INTEGRITY CONSTRAINTSAn Integrity constraint is a trick used by oracle to prevent invalid data entry into the table. It is only a rules applied to restrict user to enter particular value. It prevent this by defining it at column-level or table-level constraint. The main difference between both is that column-level constraint is defined at the column level and cannot reference by any other columns in the table. A table-level constraint is defined at the table level and can reference to any of the table's columns.
NOTE:-not null and check constraint cannot be defined as table-level constraint.
Integrity constraint are categorized into three types they are as under
1) Domain Integrity Constraints
2) Entity Integrity Constraints
3) Referential Integrity Constraint.
I) DOMAIN INTEGRITY CONSTRAINTS-> Domain integrity constraint Prevent invalid data entry by enforcing rules like NOT NULL and CHECK on the column.

NOT NULL->By default, every column can contain null value. But, If we want to restrict the user to enter the value at any cost we should put not null constraint.
->In other words, not null field should always contains value.
example
create table emp
(
empno number(4) constraint ntnl not null,
ename varchar2(25),
job varchar2(25)
);
->here empno is column-level constraint and after implementation of the above command while user want to enter the value, he must have to enter empno. ntnl is the constraint name.

CHECK-> check constraint is used to match the enter data with predifined criteria during designing of table.
-> As it check for predifined criteria it stops the invalid user to do mischief with the database.
-> Thus it helps in making database consistent by feeding reliable information.
example
create table emp
(
empno number(4) constraint ntnl not null,
ename varchar2(25),
job varchar2(25) constraint check(job in('clerk','manager'))
);
->here check constraint will look for job in clerk or manager and if enduser try's to enter job for another category an error code for it will be generated by sql.

II) ENTITY INTEGRITY CONSTRAINTS-> Entity integrity constraint Prevent invalid data entry by enforcing rules like UNIQUE and PRIMARY KEY on the column.

UNIQUE-> Unique constraint allowed null values, but it does not allowed duplicate values.
-> It may be composite upto 16 columns.
example
create table emp
(
empno number(4),
ename varchar2(25),
job varchar2(25),
constraint unino unique(empno)
);
->here unino is constraint name for table-level constraint definition and constraint unique is applied to empno, so after execution of above command user can enter only unique value or may not enter the value.

PRIMARY KEY-> A primary key is a field that uniquely identifies each records in a table.
-> It should neither be duplicate nor null.
-> It may be composite upto 16 columns.
example
create table emp
(
empno number(4) constraint pkno primary key ,
ename varchar2(25),
job varchar2(25)
);
->here Primary key is created for empno so it will alone identifies each records in a table. pkno is a constraint name for column-level definition of constraint.

III) REFRENTIAL INTEGRITY CONSTRAINTS-> Often, we wish to ensure that a value that appers in one relation for a given set of attributes also appears for a certain set of attributes in another realtion. This condition is called referential integrity.
-> To implement this, we should define the column in the parent table as a primary key and the same column in the child table as a foreign key referring to the corresponding parent entry.
->foreign key is a column or combination of column which refers to primary key of primary table.

example
create table emp
(
empno number(4) constraint pkno primary key ,
ename varchar2(25),
job varchar2(25)
);

create table education_detail
(
empno number(4) ,
degree varchar2(30),
foreign key (empno) references emp(empno)
);

-> here emp table contains the details of the employee while education_detail table consist of their achived degree's by their no. one-to-many relationship is formed here. As one employee have many degree's and even few employee have no degree's.

ON CASCADE DELETE-> It is special facilty through which we can ensure that the row deleted in the master table will automatically deletes the row in the reference table.
for example if we have records of employee details than after deleting any one of their record will automatically deletes the corresponding entry in the reference table.
example
create table education_detail
(
empno number(4) references emp(empno) on-delete-cascade,
degree varchar2(30)
);

Related Links

ORACLE Datatypes Basics

1) CHAR
-> Char contain alphanumeric data.
-> Length of Char datatype is between 1 and 255 characters.
-> If you don't specify a length, a char column stores a single character by default.
-> Char datatypes stores data in the Fixed-Length Format.
-> Be aware, when using this data type, that spaces are padded to the right of the value to supplement the total allocated length of the column.
-> It can be declared as char(size) where size is the total length allowed for a value in a column defined as char.

2) DATE-> Date datatype is used to store all date and Time information.
-> Oracle always allocates a Fixed - 7 bytes for a DATE column.
-> Oracle uses the default format of DD-MON-YY for entering and displaying.
-> Following Include as a Part of DATE are century, year, month, day, hour, minute and second.
-> It enables you to store dates in range of January 1, 4712 B.C. To December 31, 4712 A.D.
-> To view System Date and time we can use the SQL function called SYSDATE.

3) LONG-> It can store alphanumeric strings.
-> It stores the data in Variable-Length Format.
-> Length of Long rise upto 2 giga bytes.
-> Long is used to store more than 2000 characters in a column.
-> You cannot use any of Oracle's built-in functions or operator with LONG column.
-> You can think of a LONG column as a large container into which you can store or retrieve data but not manipulate it.
Limitations of LONG data type are as under
i> Only one column in a table can have LONG datatype, which should not contain unique or primary key constraint.
ii> Column cannot be indexed.
iii> Procedures or stored procedures cannot accept LONG datatype as argument.

4) NUMBER
-> It can store Numeric data such as Zero, Positive or Negative, Fixed or Floating point data.
-> It accepts Positive and negative integers and real number and has from 1 to 38 digits of precision.
-> Scale which refers to number of digits to the right of the decimal point, which varies between (-84) to 127.
-> Format of declaring NUMBER datatype is NUMBER( Precision, Scale)
example:-
1) NUMBER(5) - It can store numeric data upto 5 digits i.e. 99999
2) NUMBER(8,2) - It can store numeric data of 6 Integers and 2 for decimal.
i.e. 999999.99

5) VARCHAR2-> It stores Alphanumeric data values.
-> The field must be between 1 and 2000 characters long.
-> VARCHAR2 supports Oracle built-in functions and operators.
-> It supports a variable length character string.
-> Format of declaring VARCHAR2 IS VARCHAR2( size ).

6) VARCHARSame as Varchar2. Always use Varchar2 instead of varchar as it may not be supported in future.

7) BLOB (Binary Large OBjects)-> Blob include documents, graphics, sound, video, actually any type of binary file you can think of.
-> A binary large object with a limit of 4GB in length.
-> When you want to associate a BLOB with a 'NORMAL' row, two choices are available to you.
i> Store the BLOB in an operating system file(such as an MS-DOS file) and stores the directory and filename in the associated table.
ii> Store the BLOB itself in the LONG RAW column.

8) CLOB (Character Large OBject)-> A character Large object with a limit of 4GB in length.

9) BOOLEAN-> Boolean variables are used in PL/SQL control structure such as IF-then-ELSE and LOOP statements. A Boolean value can hold true, false or NULL only.

10) LONG RAW-> It can store binary data upto 2GB.
-> LONG RAW datatype is designed for BLOB storage. You can't use any of the built-in functions with a LONG RAW column.

11) RAW-> It can store binary data upto 255 bytes.
-> Because of this storing restriction, a RAW column is less useful than a LONG RAW column.

12) ROWID-> Hexadecimal string representing the address of a row in a table.

13) INTEGER-> Specifies size of an INTEGER(n) digits wide.

14) BINARY INTEGER-> The number type is stored in a decimal format which is optimized for accuracy and store efficiency. This datatype is used to store signed integer values, which range from TM2147483647 to +2147483647. It is stored in a 2's complement binary format. Loop Counter are often of type BINARY INTEGER.

Related Links

SQL Basics

SQL(STRUCTURED QUERY LANGUAGE)

SQL is a Non-procedural Language.
SQL provides a fixed set of datatypes you cannot define new datatypes.
Every SQL statement is terminated by a semicolon.
An SQL statement can be entered on one line or split across several lines for clarity.
SQL is not a case sensitive; you can mix uppercase and lowercase when referencing SQL keywords (such as select and Insert), table names and column names.
SQL determines how to perform the request.
SQL is the official and de facto standard language for interfacing with relational database.
SQL is Fourth Generation Language (4GLs).
SQL*Plus is one of the Front end tool of ORACLE.
SQL is the interfacing language between the user and the ORACLE database.
SQL as non-procedural, does not bother how to get data - Interested in what data to get.

SQL HISTORY AND STANDARDS* Found in mid 1970's by IBM for system R.
* ORACLE incorporated it in the year 1979.
* SQL used by IBM/DBZ and DS database systems.
* SQL adopted as a standard language for RDBMS by ANSI in 1986.

SQL statements can be broadly categorized into Three types:-
1)DATA DEFINITION LANGUAGE(DDL)Which defines the structure of the data. The statements are CREATE, ALTER,etc.
2)DATA MANIPULATION LANGUAGE(DML)which retrives or modify data.
The category of DML contains four basic statements:
i>select - which retrieves rows from a table.
ii>Insert - Which adds rows to a table.
iii>Update - Which modifies existing rows in a table.
iv>Delete - Which removes rows from a table.
3)TRANSACTION CONTROL LANGUAGE(TCL)Which defines the privileges granted to database users. TCL statements are used for securing the database.TCL statement such as COMMIT,SAVEPOINT and ROLLBACK affirm or and revoke database transactions.
(commands will be explained later).

LOGIN PROCEDURE FOR SQL*PLUS
FOR LINE MODE VERSION1) C:\>SQLPLUS
2) C:\>PLUS80

FOR GRAPHICAL USER INTERFACE(GUI) MODE1) C:\>PLUS80W
2) START -> PROGRAMS -> ORACLE -> SQL*PLUS.

NOTE:-The above path are define considering most common path used, their can be other path for same procedure depending upon the way of installation of particular software.

CONNECTING TO AN ORACLE DATABASE
Before you create a new user, you will have to know how to connect to an oracle database. The dialog window prompts you for three items:

i) User Name :- The oracle database user you are using for connection.

ii) Password :- The User Password associated with username.

iii) Host string :- A database or connect string is require while you are trying to connect to an Oracle database on server.

The starter database comes with some default user names and user password which are shown in the following table. Use these default values until you establish your own user_name and user_password.They are given as ROLES, USER_NAME AND PASSWORD respectively.
1)[DBA - INTERNAL - ORACLE]
2)[CONNECT RESOURCES - SCOTT - TIGER]
3)[DBA - SYSTEM - MANAGER]
4)[DBA/CONNECT RESOURCES - SYS - CHANGE_ON_INSTALL]
5)[CONNECT RESOURCES - DEMO - DEMO]
6)[DBA - PO8 - PO8]

NOTE:-This are the commonly use user_name and password still there are many for longon purpose.

TIPS FOR USER NAME AND PASSWORDA user name must be associated with a user password. The maximum length for the user_name is 30 characters.
A user Password is a word or phrase (no space or commas allowed) associated with a user_name. Keep this password secret in order save your data from modified by other users. The maximum length is 30 Characters.

To create usersyntax:-create user identified by ;
example:-create user vivek identified by vr1;
where vivek is user and password is vr1.

To Give Rights after creating userssyntax:- grant to ;
grant on to ;
example:- 1)grant DBA to vivek;
where DBA is Right and vivek is user_name.
2)grant update,delete on library to nihar;

To Remove the Grant given to userssyntax :- revoke on from
;
example :- revoke insert on library from tushar;
where Right is insert table is library and user is tushar.

To see Rights of userssyntax :- show all user;

To Connect to another user from current loginsyntax :- connect ;

To see the name of user in which you are currently been logged onsyntax :- select user from dual;
or
show user;

DUAL:- DUAL is usually a pseudocolumn which doesn't require any argument, It can be considered as built-in value that returns specified piece of information by quering a table.
for example:-sysdate always returns the current date and time. similarly here it provides information about the current user who is connected to the oracle database.

Before Ending this topic let us learn common command which is require frequently that is
Command To Clear Screan
syntax
cl scr;
Related Links

Basic DBMS Concept

A)DATABASE MANAGEMENT SYSTEM(DBMS)The software used for the management, maintenance and retrieval of the data stored in a database is called DBMS.
Database management systems frees the programmer from the need to worry about the organization and location of data. The primary goal of a DBMS is to provide an environment which is both convenient and efficient to use in retrieving and storing information.

B) A DATABASE STORAGE HIERARCHY
1) DATABASEA collection of data files integrated and organised into a simple comprehensive file system, which is arranged to minimize duplication of data and to provide conenient access to information within that system to satisfy a wide variety of user needs.

2) DATA FILESA file is a number of related records that are treated as a unit.
eg:- A collection of all employee records for one company would be an employee file.

3) RECORDSA collection of related items of data that are treated as a unit.
eg:- An employee record would be collection of all fields of one employee.
->Record is sometimes referred as tuple.

4) FIELDIndividual element of data is called Field.
eg:- Bank cheque consist of following field cheque no, date, payee, numeric amt, signature, bank,etc.
->Field is sometimes referred as Data item.

C) Basic DBMS TERMS
1) RELATION
A relationship is an association among several entities
eg:- A cusst_Acct relationship associates a customer with each account that she or he has.

2) REDUNDANCYIf same piece of information is stored in database for number of times the database is said to be redundant. We should check our database should not be redundant as it wastes make our disk space, reduced efficiency of database, require more processing time, and their are chances of Inconsistency due to it in our database.
eg:-If we have to tables emp_details (contains details of employee) and Payroll(contains Payment details to employee), than if we include details of employee in payroll table, than it is said to be redundancy as same piece of information is repeated.

3) INCONSISTENCYInconsistency is various copies of the same data may no longer agree. Inconsistency occurs due to redundancy, so redundancy should be reduced . Though we cannot elimnates the redundancy , but we can reduced it upto certain level.
eg:- If we have details of employee stored in emp_details and payroll table than while updating information we should check that both tables are updated or not, if we update the address of one employee in emp_details and same details is not updated in payroll table, than database is said to be in inconsistent state.

4) PROPAGATING UPDATESPropagating updates ensures users that any change made to record of one files, automatically made to other files. This process is known as Propagating updates. Where the term "Updates" is used to cover all the operations of creation, deletion and modification.
-we can avoid inconsistency by using propagating update technique.

5) INSTANCESThe collection of information stored in the database at a particular moment in time is called an instances of the database.

6) SCHEMESThe overall design of the database is called the database schemes. Schemes are changed infrequently, if at all.

7) USERSThere are four different types of database system users.

- Application programmers:- A person who prepares application program are called application programmer. Application programs operates on the data in all the usual ways: retrieving information, creating new information, deleting or changing existing information.

- SOPHISTICATED USERS:- Sophisticated users interact with the system without writing programs. Instead, they form their requests in a database query language. Each such query is submitted to a query processor whose function is to take a DML statement and break it down into instructions that the database manager understands.

- SPECIALIZED USERS:-Some sophisticated users write specialized database application that do not fit into the traditional data processing framework. Among these application are computer-aided design systems, knowledge-base and expert systems, systems that store data with complex data types
eg:-For Graphics and Audio data.

- END USERS:-Unsophisticated users interact with the system by invoking one of the permanent application programs that have been written previously.Thus they are persons who uses the information generated by a computer based system. Retrival is the most common function for this class of user.

D) KEYS concept in DBMSA Key is a single attribute or combination of two or more attributes of an
entity set that is used to identify one or more instances of the set.

1) PRIMARY KEY:-A primary key is a field that uniquely identifies each record in a table. As it uniquely identify each entity, it cannot contain null value and duplicate value.
eg:-Consider the customer table, which has field :customer_number, customer_socialsecurity_number, and customer_address.here customer_number of each entity in customer table is distinct so customer-number can be a primary key of customer-table.

2) SUPER KEY :- If we add additional attributes to a primary key, the resulting combination would still uniquely identify an instance of the entity set. Such augmented keys are called superkey.
A primary key is therefore a minimum superkey.

3) CANDIDATE KEY:-A nominee's for primary key field are know as candidate key.
eg:-From above example of customer table, customer_socialsecurity_number is candidate key as it has all characteristics of primary key.

4) ALTERNATE KEY:-A candidate key that is not the primary key is called an Alternate key.
eg:- In above example, customer_socialsecurity_number is a candidate key but not a primary key so it can be considered as alternate key.

5) COMPOSITE KEY:- Creating more than one primary key are jointly known as composite key.
eg:-In above example, if customer_number and customer_socialsecurity_number are made primary key than they will be jointly known as composite key.

6) FOREIGN KEY:- Foreign key is a primary key of master table, which is reference in the current table, so it is known as foreign key in the current table. A foreign key is one or more columns whose value must exist in the primary key of another table.
eg:-Consider two tables emp(contains employees description) and emp_edu(contains details of employee's education), so emp_id which is primary key in emp table will be referred as foreign key in emp_edu table.

E) NORMALIZATION
It is important to understand the concept of normalization before switch on to the creation of table and its manipulation.

- NORMALIZATION is the process of grouping data into logical related groups.
- Normalization is the process of reducing the redundancy of data in a relational database.
- A database that is not normalized may include data that is contained in one or more different tables for no apparent reason. This could be bad for security reasons, disks space usage, speed of queries, efficiency of database updates, and may be most importantly, data integrity. A database before normalization is one that has not been broken down logically into smaller, more manageable tables.

BENEFITS OF NORMALIZATIONNormalization provides numerous benefits to a database.
some of the major benefits include

Wider the overall database organization.
The reduction of redundant data.
Data Inconsistency can be avoided.
A much more flexible database design.
A better handle on database security.

Related Links

Saturday, May 19, 2007

Webcasts on Windows Server Longhorn and SQL Sever 2005

Webcasts on Windows Server Longhorn and SQL Sever 2005

Attend a series of LIVE Webcasts by experts from Microsoft on upcoming technologies and get on to the edge of the steep-learning curve.

Register Now!

Webcast on Windows Server Longhorn

Date & Time	Title	Speaker
May 21, 2007 11:00 AM - 12:30 PM	Windows Server Codenamed "Longhorn" Server - Technical Overview-Part 1	Ranjana Jain
May 22, 2007 11:00 AM - 12:30 PM	Windows Server Codenamed "Longhorn" Server - Technical Overview-Part 2	Ranjana Jain
May 23, 2007 11:00 AM - 12:30 PM	Server Role Management in Windows Server longhorn	Ranjana Jain
May 24, 2007 11:00 AM - 12:30 PM	Windows Server "Longhorn" Terminal Services Technical Overview	Ranjana Jain
May 25, 2007 11:00 AM - 12:30 PM	Active Directory Domain Services in Windows Server Longhorn Technical Overview	Ranjana Jain

Webcast on SQL Sever 2005

Date & Time	Title	Speaker
June 20, 2007 02:00 PM - 03:30 PM	Capacity Planning Considerations – SQL Server 2005	Vinod Kumar
June 21, 2007 02:00 PM - 03:30 PM	Understanding IO and Storage system with SQL Server 2005	Vinod Kumar
June 22, 2007 02:00 PM - 3:30 PM	Performance Tuning and Troubleshooting – SQL Server 2005	Vinod Kumar

Sunday, October 29, 2006

Merge Individual Query Result in a sorted fashion

It is not possible to apply Order by Clause while using UNION Query, when we want to sort the individual query result and then want to merge the result data

Case: If we want to sort the individual query result and then want to merge the result data it is not possible to do so. Read the following article to see how it is possible.
Example:

Problem
--------
select categoryid,categoryname
from categories
order by 2
UNION
select productid,productname
from products
order by 2

Error
------
--It will give me following error
Server: Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'UNION'.

Partial Solution
------------------
select categoryid,categoryname
from categories
UNION
select productid,productname
from products
order by 2

--Problem with Partial Solution
It will sort all the result data by column 2., It is general sorting which is applied when we want to sort all the result data.

But what if we want to sort the individual query result and then want to merge the result data.

Solution
----------
select * from
(
select categoryid,categoryname, 1 as myOrderfrom categories
UNION
select productid,productname, 2 as myOrder
from products
)
myTableorder by myTable.myOrder
So finally here the result query will display the data sorted by category and then by product. and the result data is shown.

Wednesday, October 25, 2006

SQL Refactor from Red Gate

This software does what it's name implies. It helps you to refactor your database and SQL code. http://www.red-gate.com/messageboard/viewtopic.php?t=3438

For SQL Prompt: http://www.red-gate.com/products/SQL_Prompt/index.htm?gclid=CLaPur_8gogCFQt7WAodtxE5tw

Wednesday, September 20, 2006

Replacing Cursor with For Loops

Advantages of replacing curosors with for loop
-Implicitly declares its loop index as a %rowtype record
-opens, fetch and close operation are done automatically.
-It reduce coding and it is easy to understand.


----------------------------------
--Simple Select Procedure Example
----------------------------------
--Create Procedure
CREATE PROCEDURE SP_Print_Hello
AS
BEGIN
         FOR IDX IN (Select ename from emp)
         LOOP
                  DBMS_OUTPUT.PUT_LINE('Hello '  IDX.ename);
         END LOOP;
END;


--Execute Procedure
begin
   scott.sp_inc_salary ( );
end;


--Output
Hello SMITH
Hello ALLEN
Hello WARD
Hello JONES
Hello MARTIN
Hello BLAKE
Hello CLARK
Hello SCOTT
Hello KING
Hello TURNER
Hello ADAMS
Hello JAMES
Hello FORD
Hello MILLER
----------------------------------



-------------------------------------
--Manipulate Batch Update Statement
-------------------------------------
--Create Procedure
CREATE PROCEDURE SP_Increment_Salary
AS
 mIncAmt number(5);
BEGIN
         mIncAmt := 2000;
         FOR IDX IN (Select empno,sal from emp)
         LOOP
                            Update emp
                            Set sal = IDX.sal + mIncAmt
                            where empno = IDX.empno;
         END LOOP;
END;


--Execute Procedure
begin
   scott.SP_Increment_Salary ( );
end;


--OUTPUT
Procedure updated successfully....
-----------------------------------------

Data Dictionary Queries for SQL Server and Oracle

For SQL Server
--Finding all details of Primary Key constraint
select * from sysobjectswhere xtype='PK'

--Finding all details of Foreign Key constraint
select * from sysobjectswhere xtype='F'

--Finding all User-Defined objects (tables, etc)
select * from sysobjectswhere xtype='U'

--Finding all System objects
select * from sysobjectswhere xtype='S'

--Finding all user names
select * from sysusers

--Finding Column Names of Particular Table
--Select Pubs Database
select c.name from sysobjects o, syscolumns cwhere o.id = c.id ando.name = 'publishers'

For ORACLEselect * from sys.dba_objectswhere owner = 'scott'and object_type='TABLE'
SELECT owner, object_name, object_type FROM sys.dba_objectswhere object_type='SEQUENCE' and owner='scott';

Today I was just trap with some queries and so the solution i come up with.

Rush for Free SQL Intellisense before 1st Sep.

Download Free SQL Intellisense before 1st Sep.
http://red-gate.com/products/SQL_Prompt/index.htm

SQL Prompt™ Features
1) Intellisense for SQL Server, plus other features
2) Code completion for fast, accurate script building
3) Discoverability in SQL Server query creation
4) Keyword formatting, code snippet integration other
5) extended features

END

Learn Ms SQL 2005

Page 1

Error: OCIEnvCreate failed with return code -1 but error message text was not available.

Solution to obtain exclusive locked to perform database operations.

Wednesday, January 16, 2008

Thursday, January 10, 2008

Friday, October 12, 2007

Wednesday, October 10, 2007

Sunday, October 07, 2007

Saturday, October 06, 2007

Friday, October 05, 2007

Thursday, August 23, 2007

Friday, August 17, 2007

Thursday, August 16, 2007

Tuesday, August 14, 2007

Monday, August 13, 2007

Sunday, July 01, 2007

Friday, June 15, 2007

Saturday, June 09, 2007

Saturday, May 19, 2007

Sunday, October 29, 2006

Wednesday, October 25, 2006

Wednesday, September 20, 2006

Labels

Comments

Post a Comment

Popular posts from this blog