Sql Server : Difference between varchar and nvarchar

Difference between varchar and nvarchar

Mainly nvarchar stores unicode characters and varchar stores non-unicodes characters.

"unicodes" means 16-bit character encoding scheme allowing characters from lots of other languages like  Chinese, Japanese etc, to be encoded in a single character set.

That means unicodes is using 2 bytes per character to store and nonunicodes uses only 1 byte per character to store.

Example:Take French Language, in French Language Désolé can not be stored in varchar datatype The difference as indicated by the N prefix is that the N datatypes use a unicode codeing system for data.

 If you are not storing the other language characters like French, Portuguese then both data type are same. varchar   datatype should be used instead. The reason for this is that nvarchar takes twice as much space as varchar, this is because of the need to store the extended character codes for other languages

Please provide your feedback for the post, if you find this post useful. Also Post your query or scenario, i will be happy to help.

Sql Server : create new Database from the Backup of another Database

How to create new Database from the Backup of another Database?

Scenario:

I was using Adventureworks2008 database for practicing in Sql Server management system. During practice I wanted to use DDL, (create, alter, drop), and DML (delete, update) and many other operations as well. For that I wanted to keep my adventure works database safe for other practices as well.  Then I had a thought , if would have a copy of this same database , so that I could perform all kind of operations on one copy of my Adventureworks2008 database, and another remains safe for real projects.

Solution :

For the above scenario, we can create same database as Adventureworks2008 in two ways

A.      If Adventureworks2008 database exists on some other server or system, then we can take back up of Adventureworks2008, and then we can restore our empty database from the backup file.

B.      If Adventureworks2008 database exists on same server, then we can directly restore our empty database from Adventureworks2008 database.

Case A. When Adventureworks2008 database exists on some other server or system

Below are the steps that need to take place for creating backup file for Adventureworks2008

1: Right click on AdventureWorks2008 Database and go to Tasks->Backup

             2: Give a path as Destination, where you want to keep the backup., then click on ‘OK’

  Case B:  When Adventureworks2008 database exists on same server,

Then no need to create backup file, we can directly restore it from Adventureworks2008 Database

Below are the Steps, which will be followed to create and restore new database in both cases A and B

Step1:

Create an empty new database say Copy_AdventureWorks

Here in the below snapshot, the path mentioned, is the path where the new database will be created

Step2:

Right Click on Copy_AdventureWorks database then go to Tasks->Restore->Database

In the below snapshot of restoring database, we have to give the source and location for backup file to restore.

There can be two case, as mentioned above Case A and Case B.

Here we are assuming that Adventureworks2008 database exists on the same system that is Case B, So we select ‘from database’ and select ‘Adventureworks2008’ Database form the dropdown.

In Case B, when Adventure Works doesn’t exists on same system or server, then we will select ‘From Device’, and will give the path where the backup file exists.

When we click on OK, we get below error

‘Restore Failed and the backup set holds a backup of a database other than the existing Copy_AdventureWorks’

It’s because the backup taken is of Adventureworks2008 database. So we will need to overwrite the existing database. For this, go to option and check the checkbox ‘Overwrite the existing database’

Still we get some error as shown below in the snapshot:

The error is ‘…/.AdventureWorks2008_Data.mdf cannot be overwritten. It is being used by database ‘Adventureworks2008’.

The solution is to change the name for .mdf and .ldf file. That we can do by changing the name in Restore As column

 

 Change the name ‘AdventureWorks2008_Data’ to ‘Copy_AdventureWorks_Data’

And ‘AdventureWorks2008_Log’ to ‘Copy_AdventureWorks_Log’

And then click ‘ok’, it will work and you will get your new empty database Copy_AdventureWorks restored with the same Adventureworks2008 Database.

Please provide your feedback for the post, if you find this post useful. Also Post your query or scenario, i will be happy to help.