Restore Backup File(.bak) in Sql Server.

In this article, We will discuss How to restore Backup file(.bak) in Sql Server.

First of all, login into your Sql Server Management Studio.

Goto Databases, right Click on it -> Click Restore Database... 

After that a new PopUp Will Come and there We need to Select the backup file(.bak).

To Select the .bak file Check "Device" Radio button and click on browse button beside of it and add that .bak file in it.

After that give your Destination Database name and Which is in below figure,

Then Click Ok.

After few Seconds/Minutes of time the database will be restored Successfully.

Goto your Databases and Click  refresh and now you can able to see the Restored database.

Read More

Database Backup in Sql Server.

In this article, We will discuss How to take backup of a Database in Sql Server.

First of all, Login into your SQL Server Management Studio.

Select the particular database which you want to take backup -> After that right click on it -> Goto Tasks -> Goto Backup -> Click on it.

After that a new Screen Will appear and which looks like as below figure,

After that Press Ok.

After few Seconds/Minutes, the backup will be Completed and it will show you in popup.

After that goto that particular location where you saved you backup file and you can see the .bak file.

Read More

Case Sensitive Search in Sql Server.

In this article, We will discuss about How to do a Case Sensitive Search in Sql Server.

By default, Sql Server is Case Insensitive.

Before, We apply Case Sensitive Search in Sql Server first of all we need to check whether that particular column is Case Sensitive or Case InSensitive.

Below is an example to Check for a particular column whether it is Case Sensitive or Case InSensitive.

As the above Column is Case Insensitive, So we need to Convert that Column into Case Sensitive.

So While Searching only We will Convert that Case Insensitive Column into Case Sensitive Column Without altering the table(even if we can alter the table also).

Case Sensitive = SQL_Latin1_General_CP1_CS_AS
CaseInSensitive = SQL_Latin1_General_CP1_CI_AS


The query looks like as below,

select * from LoginDet where VarPwd='DotNet' collate SQL_Latin1_General_CP1_CS_AS

So, the output is looks like as below figure,

As you can see in the above image,  VarPwd='DotNet' matched with Case Sensitive So it returned the result.

But let's  Changed our Search String from VarPwd='DotNet' to VarPwd='dotNet' and it Will return no result because by using Case Sensitive Search it won't able to find that String  and which is shown in below figure,

Read More

Expression type int is invalid for COLLATE Clause in Sql Server

Moved this article to Expression type int is invalid for COLLATE Clause in Sql Server

Read More

Case Sensitive and Case InSensitive in SQL Server.

In this article, We will discuss about Case Sensitive and Case InSensitive in SQL Server.

By default, SQL Server is Case InSensitive.

Case Sensitive or Case Insensitive applies to Database,Tables and Columns Only.

Whenever we will Create a new database,tables and Columns by default it Will take as a Case InSensitive.

Case Sensitive also applies to those Columns Whose datatype is char,varchar, nvarchar and text because in these datatype both Small case and Capital case are different in terms of ASCII code.

For Example,  "A" and "a" both are different because "A" letter ascii code is 65 and "a" letter ascii code is 97.

So, due to ascii code We can apply case sensitive to char,varchar,nvarchar and text datatype. 

In the below example, I Showed How Case Insensitive by default applies to a particular varchar Column.

Here in the above image i created one table Whose name is LoginDet with 2 Columns as IID and VarPwd. In the VarPwd Column you can see by default it will take "SQL_Latin1_General_CP1_CI_AS" Collation.

If We want then at the time of table Creation also We can Create Column as Case Sensitive by applying "SQL_Latin1_General_CP1_CS_AS" Collation.

Collation means nothing about Particular rules and here Case Sensitive and Case InSensitive are two Collation.

Case Sensitive =  "SQL_Latin1_General_CP1_CS_AS" Collation

Case InSensitive = "SQL_Latin1_General_CP1_CI_AS" Collation

Below is an example of How to change one column from Case Insensitive to Case Sensitive to an existing Column.

Example:-

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

alter table LoginDet 

alter column VarPwd varchar(10) Collate SQL_Latin1_General_CP1_CS_AS

So after this Column Changed, the Case Sensitive looks like as follows,

Even if  We can add Case Sensitive and Case Insensitive While Creating the table.

Read More

Drop failed for Database "Demo" or Cannot drop database "Demo" because currently in Use in Sql Server.

In this article, We Will discuss about Drop failed for Database "Demo" or Cannot drop database "Demo" because currently it is in Use.

Whenever we are deleting any Database then at Sometime Drop failed for Database "Demo" or Cannot drop Database "Demo" arises because whenever Someone is using that Database then directly you can't able to delete that Database Which is Shown in below image.

Solution

----------

To Resolve this error We need to Just Check the "Close existing connections" Checkbox and again Click Ok. After that the Database Will be deleted Which is Shown in below image.

Read More

Error converting varchar to numeric in Sql Server.

In this article, We will discuss Why "Error converting varchar to numeric in sql" and How to resolve this error.

While we are working on any project or development work there is a need to retrieve data from one column but whose datatype is varchar but we need to show the output in decimal format.

So we will implement like this,

select convert(decimal(18,3),BillRate) from BillDetails

or
select cast(BillRate as decimal(18,3)) from BillDetails

This Works absolutely fine but sometimes it will still create error like "Error converting varchar to numeric".

This issue arises because may be null or empty or character or string value present in that Column value and as we know null or empty or character or string can't convert into decimal.

To fix this error we need to skip these values by applying filter condition like in where clause.

Read More

Difference between Varchar and NVarchar in Sql Server.

-Varchar stands for Non-Unicode Variable length character data type where as NVarchar stands for Unicode Variable length character data type.

-Varchar can store only Non-Unicode values where as NVarchar can store both Unicode and Non-Unicode Values.

-Varchar takes 1 byte per character where as NVarchar takes 2 byte per character.

-Varchar can store values from 1 to 8000 where as NVarchar can store 1 to 4000 because it takes 2 byte per character.

Read More

Formatting of numbers by leading zeros in SQL Server.

In this article, We will discuss How to format of numbers by leading Zeros.

While working on Some project or sometimes the client requirement will come like, i want all values with leading particular number of zeros.

Example:-

I want all the values with 4 leading zeros if no value exists.

if value is 1 then it should show 0001.

if value is 78 then it should show 0078.

if value is 333 then it should show 0333.

but if value if 1234 then it show 1234 because we are working on 4 leading zeros and as it contains value 1234 which is 4 digit so here no preceding zeros will come.

Now lets implement the same thing in SQL Server,

declare @number int = 1;

select format(@number,'0000')

Output=0001

declare @number int = 78;

select format(@number,'0000')

Output=0078

declare @number int = 333;

select format(@number,'0000')

Output=0333

declare @number int = 1234;

select format(@number,'0000')

Output=1234

Note:-  Please make sure that @number should be int.

But what if i have a column which is of type varchar.Can it be possible? OfCouse it is possible. Let me give you an example

Example:-

declare @number int = 86;

select format(convert(int,@number),'0000')

Read More

Import Excel File through SQL Server.

In this article We will discuss How to upload all excel data into Sql Server database directly Without Using any Code.

In Sql Server there is a direct option exists to upload Excel file and it will Create the table automatically with datatype and which is much more faster as compared to Code.

Note:- To Import Excel File from Sql Server compulsory we need Microsoft Office installed on that System.

First login into your Sql Server and right click on your database -> Goto Tasks -> Click Import Data -> Click Next -> then Choose Data Source Which is given in below image and in data source select "Microsoft Excel".

-> Click Next 

-> After that in Destination Select "Microsoft OLE DB Provider for SQL Server",Select Server Name, Select either Windows/Sql Server Authentication and accordingly  Fill UserName/Password and select Database from DropDownlist and Click Next.

-> In the next Screen Choose First Radio Button and Click Next.

-> In the next Screen We need to Select CheckBoxes and accordingly the name will Create and We can edit the name also and click Next and each one will Create a Separate table.

-> In the next Screen Check the 1st Option and Click Next and Finish and after that it will take Some time to Create the table.

Read More