Monday, August 18, 2014

SQLTea - New SQL tutorial app in Google play store

SQLTea - Our new SQL Tutorial App on Google Play Store
 
We are happy to announce our new SQL tutorial app SQLTea  ( Native android app) for SQL learning which includes articles from this blog.


This application includes 

1) Articles for basic concepts like Normalization, Constrains, Joins, SQL Functions, SQL commands etc and also advance concepts like SQL Triggers, Views, Cursors, Stored Procedures and many more.

2) Every article is explained with the help of examples.

Techmixing.com new apptechmixing.com new app


3) Once installed, it will work in offline mode (No Internet needed). You can read articles easily even if you don't have Internet connections

4) Includes many popular articles from this blog.

5) Light weight and fast

6) Will going to frequently update this App with new blog articles.

7) Simple to use and navigates.

So please download this app and gives your valuable comments on it.



Sunday, August 17, 2014

How to restore database backup in SQL SERVER

In this article, I am going to explain the process of  Database restoration using the SQL Server Management Studio. In this article, I will try to explain this process in a simple way so that a database developer having little knowledge of DBA can restore that database using the database backup.

For database restoration process, we need to follow the below steps:-


Step 1:- Open the SQL Sever Management Studio. For this we have to follow the following path:-


START --> All Programs --> Microsoft SQL Server 2008R2 --> Click on SQL Sever Management Studio (SSMS) --> Open the SQL Server Management Studio using Login information (Username & password or Window authentication )

Please note:- Above path can be changed depending on the Window version.

Step 2:- Once SQL Sever Management Studio is opened, opened the Object Explorer. By default it will be already opened. If it is not opened, then follow the below steps to open it.

Click on the View(Top menu) --> Click on Object Explorer.

Step 3:- Under the Databases node, select the database for Restoration and right click on it. Then choose Tasks, click on Restore and then choose the Database... as shown in the below figure. Here we choose the Company_DB as database.


Select Database for restore process

Step 4:- On the click of Database.. link,  we get the Restore Database window. In this window, we can select the database backup file, first by selecting the Device radio button and then click on the Add button, as shown in the below figure:-

Select Backup file location

Step 5:- When we click on the Add button, Locate Backup File Window is opened. On this window, we have to choose the backup file of the database as shown in the below figure.



Select Database backup file

Step 6:- When user selected the database backup file and click on the OK button, it will close the Locate Backup File window. Now on the Restore database window we can see the selected database backup file as shown in the below figure.


Selected backup file
Step 7:- When we click on the Options link, below screen will be shown


Database restoration Options
In this screen, we can see many options like overwrite the existing database and many others. We can leave this screen with default values. we usually choose the option of overwrite the existing database, but user can choose other options as per his/her requirement.

If you want to know this process and its all option in detail, you can refer below link of MSDN


Restore a Database Backup (SQL Server Management Studio)


DMCA.com

How to take Database backup in SQL SERVER

This article explain the simple way to take up the backup of a database which can be easily understandable by the database programmer having little knowledge of DBA. For taking the database backup, we need to follow the given step.

Step 1:- Open the SQL Sever Management Studio. For this we have to follow the following path:-

START --> All Programs --> Microsoft SQL Server 2008R2 --> Click on SQL Sever Management Studio (SSMS) --> Open the SQL Server Management Studio using Login information (Username & password or Window authentication )

Please note:- Above path can be changed depending on the Window version.

Step2:- Once SQL Sever Management Studio is opened, opened the Object Explorer. By default it will be already opened. If it is not opened, then follow the below steps to open it.

Click on the View(Top menu) --> Click on Object Explorer.

Step3:- Under the Databases node, select the database for backup and right click on it. Then choose Tasks and then Back Up.. as shown in the below figure. Here we choose the Company_DB as database.


Database backup Fig-1
Choose Database for backup
Step4:- On the click on Back Up... link, it will create a Back Up Database window as shown below:-

Back up database Fig-2
Back Up Database Window
On this window, we have the options to choose the backup type. There are 3 backup types in SQL Server.

1) Full Backup 
2) Differential Back Up
3) Transaction Log

I will explain all 3 database types in another article. Full Backup type is the default Backup type and it is usually chosen for taking the database backup.

Also, if we want to choose another destination for storing the backup file, we can either remove the default backup destination first and then add the new destination for the database files or we can add the other destination file without removing the default destination by clicking on the Add button. On the click on Add button, it will open a new window from which we can choose our new destination. The backup file will have the extension of .bak. In this article, I will keep the default values.

If we click on the Options link from the left hand side menu, it will open the following screen.

  Backup database Fig 3
Backup database Option Screen
In this screen, we have the option of appending the new backup with the existing backup sets or overwriting the existing backup set. There are many other options available like backup like verify backup when finished and Perform checksum before writing to the Media for checking the reliability of the database backup. Let keeps the default setting. I will explains the Checksum in detail in another article.

When we click on the OK button, it will made the database backup either on the default destination which is given below:-

C:\Program Files\Microsoft SQL Server \ MSSQL10_50.MSSQLSERVER\ MSSQL\ Backup

or 

on the destination, which we have added by Clicking on the ADD button(see Back Up Database Window ).

DMCA.com

Database Creation using SQL Sever Management Studio (SSMS)

In the previous article (Database Creation using Create Database command), I explained the database creation using the Create database command. In this article, I am going to explain the steps of creation a database using SQL Sever Management Studio (SSMS) using SQL Server 2008 R2. These below database creation steps will be same for SQLServer 2005 and onward.

Step 1:- Open the SQL Sever Management Studio. For this we have to follow the following path:-

START --> All Programs --> Microsoft SQL Server 2008R2 --> Click on SQL Sever Management Studio (SSMS) --> Open the SQL Server Management Studio using Login information (Username & password or Window authentication )

Please note:- Above path can be changed depending on the Window version.

Step2:- Once SQL Sever Management Studio is opened, opened the Object Explorer. By default, it will be already opened. If it is not opened, then follow the below steps to open it.

Click on the View(Top menu) --> Click on Object Explorer

Once Object Explorer is opened, select the Databases node and right click on it . Then Select the open "New Database...".(as shown in the below figure)


Create database - Select database node
Create database fig-1
Step 3:- On the click of New Database option, New Database window is opened. In this window, we need to provide the database name and let the other values as default.(As shown in the below figure). Since we are creating the database Company_DB, we specify the name Company_DB as the Database name.


Create database fig-2
Specify database name

In this window, we can choose the owner of the database, Path, Initial size, File growth, maximum size for the Data file and the Log file. In this article, let them as default. 



Step 4:- In the "New Database" window, click on the left hand side Options link. It will open the below screen.

Create database Fig-3
Choose collation
In this screen, we can choose the database collation, Recovery Model and Compatibility level. Also there are many other options under the heading Other options, which we can change according to our need. But for creating a simple database, we can let these options has the default values.

Collation is required if we need to stored the data in the languages other than English. Many times we need to store the data of a country in its own language in to the database. For storing the data in different languages, we need to set the collation of the database as shown in the below figure:-

Create database Fig-4
Choose Collation
After that, Click on the OK button below to complete the database creation process. You can see the newly created database Company_DB under the main node Databases.



Database Creation using Create Database command

Location:India New Delhi, Delhi, India
In SQL Server, database can be created by 2 ways:-
  1.  Using Create Database command
  2.  Using SQL Sever Management Studio (SSMS).

In this article, I am going to explain the database creation using the command CREATE DATABASE.

Syntax for creating a database through Create database command is given below:-

CREATE DATABASE  'Database name'
ON  PRIMARY   ( NAME = N'Database name', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\Database name.mdf' ,
 SIZE = 2048KB , FILEGROWTH = 1024KB )
 LOG ON   ( NAME = N'Database name_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\Database name_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)

Database name:- This defines the name of the database which we are going to create.

On:-It specifies that the disk files which we are going to use for storing the data section of the database are explicitly defined.

Primary:-It specifies the primary data file that contains the startup information for the database and points to the other files in the database. User data and objects like tables, stored procedures, views can be stored in this primary file (or in secondary data files) . Every database has one primary data file. Primary file has an extension of .mdf

Log on:-It specifies that the log files which we are going to use for storing the logs of the database are explicitly defined. If LOG ON is not specified, one log file is automatically created, which has a size that is 25 percent of the sum of the sizes of all the data files for the database, or 512 KB, whichever is larger.This log file information is used for recovering database. Log file has an extension of .ldf.

Size :- It is the initial size of the file. When size is not supplied for the primary file, the Database Engine uses the size of the primary file in the model database. When a secondary data file or log file is specified, but size is not specified for the file, the Database Engine makes the file 1 MB. The size specified for the primary file must be at least as large as the primary file of the model database.

Filegrowth:- It specify the automatic growth increment of the file. Growth increment is the amount of space added to the file every time whenever there is a requirement of new space.

As per MSDN, Filegrowth can be specified in MB, KB, GB, TB, or percent (%) and if a number is specified without an MB, KB, or % suffix, the default is MB. When % is specified, the growth increment size is the specified percentage of the size of the file at the time the increment occurs. The size specified is rounded to the nearest 64 KB.

Also if we do not want to allowed additional space, we have to give 0 as the value to Filegrowth. Also If Filegrowthis not specified, the default value is 1 MB for data files and 10% for log files. The minimum value is 64 KB.


There are many other optional arguments like Maximum size, collation etc. But in this article, I have explained the main arguments which we needs to mention while creating database using Create Database command. 


For example, below SQL query will create the database Company_DB using Create database command.


CREATE DATABASE Company_DB

ON  PRIMARY   ( NAME = N'Company_DB', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\Company_DB.mdf' ,
 SIZE = 2048KB , FILEGROWTH = 1024KB )

 LOG ON   ( NAME = N'VCompany_DB_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\Company_DB_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)


DMCA.com

Thursday, August 14, 2014

IN clause in SQL Server

With IN clause, we can specify multiple values in a WHERE clause.

For example, in case of "IN", one can use the subquery to get the multiple values in the IN clause or we can have predefined values.


In this article, we are going to use the following table for some examples:-

CREATE TABLE [dbo]. [Employee](
[Empid] [Int] IDENTITY (1, 1) NOT NULL Primary key,
[EmpNumber] [nvarchar](50) NOT NULL,
[EmpFirstName] [nvarchar](150) NOT NULL,
[EmpLAStName] [nvarchar](150) NULL,
[EmpEmail] [nvarchar](150) NULL,
[Salary] [int] NULL
)


Suppose, we have another table Empprojects, which contains the employees and their project relationship and it has the below table structure

create table Empprojects (empprojectid int identity(1,1), empnumber nvarchar (50), projectcode nvarchar(50))

Now suppose we want to get the records for the employees which are associated with the project having code "Proj002" or "Proj005", then we can use the following query:-

select * from employee where empnumber in (select empnumber from Empprojects where projectcode='Proj002' or projectcode='Proj005')


In the above query, we use the subquery "select empnumber from Empprojects where projectcode='Proj002' or projectcode='Proj005' " to fetch the multiple values in the IN clause.

Also suppose, we want to get the records of employee who worked in the first project having code 'Proj001'. We can use subquery for it as we see in the above example. But we know that that project is completed years back and only 3 employees having empnumber "A001" , "A002" and "A003" worked on it , then we can pass the empnumber of these 3 employee as predefined values in the IN clause as shown in the below query:-

select * from employee where empnumber In ('A001','A002','A003')




UPPER() function in SQL Server

UPPER():- This function is used to convert the value of a column to upper case.

 Syntax:

   SELECT UPPER(string)

OR

Select UPPER(Column name) from tablename

For example,

  SELECT UPPER('Vivek')

Result:


   VIVEK



AVG() function in SQL Server

Avg():- This function is used to get the average value of a numeric column

Syntax:-


      SELECT AVG(column_name) FROM table_name


In this article, we are going to use the following table for some examples:-

CREATE TABLE [dbo]. [Employee](
[Empid] [Int] IDENTITY (1, 1) NOT NULL Primary key,
[EmpNumber] [nvarchar](50) NOT NULL,
[EmpFirstName] [nvarchar](150) NOT NULL,
[EmpLAStName] [nvarchar](150) NULL,
[EmpEmail] [nvarchar](150) NULL,
[Salary] [int] NULL
)

We insert following data into the above tables:-

Insert Into Employee (EmpNumber,EmpFirstName,EmpLAStName,EmpEmail,salary)
Values('A001','Vivek','Johari','samir@abcmail.com',200000)

Insert Into Employee (EmpNumber,EmpFirstName,EmpLAStName,EmpEmail,salary)
Values('A002','Amit','Kumar','amit@abcmail.com',100000)

Insert Into Employee (EmpNumber,EmpFirstName,EmpLAStName,EmpEmail,salary)
Values('A003','Neha','Sharma','neha@abcmail.com',300000)

Insert Into Employee (EmpNumber,EmpFirstName,EmpLAStName,EmpEmail,salary)
Values('A004','Chandra','Singh','vivek@abcmail.com',320000)

Insert Into Employee (EmpNumber,EmpFirstName,EmpLAStName,EmpEmail,salary)
Values('A005','Avinash', 'Dubey','avinash@abcmail.com',250000)
 

For example,

      Select AVG(salary) from employee


will give the given result:-

234000   

LOWER() function in SQL Server

LOWER():- This function is used to convert the value of a column to lower case

 Syntax:-

 Select LOWER (string)

 
 or
 

 SELECT LOWER(column_name) FROM table_name

For example,

SELECT LOWER('Vivek Johari')

Result:-

vivek johari

DMCA.com

LEN() function in SQL Server

LEN():- This function is used to get the length of the given string value.

  Syntax:-


    Select len (stringvalue)

  For example,

    Select len('Vivek Johari')

   Result:-

   12

Substring() function in SQL Server

Substring():- This function is used to return the specified number (third argument "len") of characters from a given string(first argument) from the start position as specified as the second argument.

Syntax:-

   Select  SUBSTRING ( expression ,start , length )

For example,

       Select  SUBSTRING ( 'kingmaker' ,1 , 4 )

Result

    king

DMCA.com

LTRIM() function in SQL Server

LTRIM ():- This function is used to get the string after the removal of leading blank spaces.

Syntax:-

LTRIM ( character_expression )


For example:-

Select LTRIM  ('  Vivek Johari')

Result:-

Vivek Johari (without blank spaces on left side)

DMCA.com

Wednesday, August 13, 2014

Max() function in SQL Server

Max():- This function gives the maximum value of the selected column

Syntax:-

Select Max (col1) from table_name


In this article, we are going to use the following table for the examples:-

CREATE TABLE [dbo]. [Employee](
[Empid] [Int] IDENTITY (1, 1) NOT NULL Primary key,
[EmpNumber] [nvarchar](50) NOT NULL,
[EmpFirstName] [nvarchar](150) NOT NULL,
[EmpLAStName] [nvarchar](150) NULL,
[EmpEmail] [nvarchar](150) NULL,
[Salary] [int] NULL
)


We insert following data into the above tables:-

Insert Into Employee (EmpNumber,EmpFirstName,EmpLAStName,EmpEmail,salary)
Values('A001','Vivek','Johari','samir@abcmail.com',200000)

Insert Into Employee (EmpNumber,EmpFirstName,EmpLAStName,EmpEmail,salary)
Values('A002','Abhinav','Golwalkar','abhinav@abcmail.com',100000)

Insert Into Employee (EmpNumber,EmpFirstName,EmpLAStName,EmpEmail,salary)
Values('A003','Neha','Sharma','neha@abcmail.com',300000)

Insert Into Employee (EmpNumber,EmpFirstName,EmpLAStName,EmpEmail,salary)
Values('A004','Chandra','Singh','vivek@abcmail.com',320000)

Insert Into Employee (EmpNumber,EmpFirstName,EmpLAStName,EmpEmail,salary)
Values('A005','Avinash', 'Dubey','avinash@abcmail.com',250000)


For example, query to get the maximum salary of an employee from the table Employee is given below:-

Select Max (salary) from Employee

It will gives the following result:-

320000

DMCA.com

RTRIM() function in SQL Server

RTRIM():-This function is used to get the string after the removal of all trailing blanks.

Syntax:-

RTRIM ( character_expression )

For example:-

Select RTRIM('Vivek Johari  ')

Result:-


Vivek Johari (without blank spaces on Right side)

DMCA.com

AND and OR Operator in Sql Server

And & Or:- These Operators are used to further filter the recordset return by SQL queries when more than one conditions are specified. In case of "AND" all the conditions must be true and in case of "OR" any of the condition must be true.
In this article, we are going to use the table Employee for explaining examples. Structure of this table is given below:-
CREATE TABLE [dbo]. [Employee](
[Empid] [Int] IDENTITY (1, 1) NOT NULL Primary key,
[EmpNumber] [nvarchar](50) NOT NULL,
[EmpFirstName] [nvarchar](150) NOT NULL,
[EmpLAStName] [nvarchar](150) NULL,
[EmpEmail] [nvarchar](150) NULL,
[Salary] [int] NULL
)


Following Insert queries will insert data into the table Employee

Insert Into Employee (EmpNumber,EmpFirstName,EmpLAStName,EmpEmail,salary)
Values('A001','Vivek','Johari','samir@abcmail.com',200000)

Insert Into Employee (EmpNumber,EmpFirstName,EmpLAStName,EmpEmail,salary)
Values('A002','Amit','Kumar','amit@abcmail.com',100000)

Insert Into Employee (EmpNumber,EmpFirstName,EmpLAStName,EmpEmail,salary)
Values('A003','Neha','Sharma','neha@abcmail.com',300000)

Insert Into Employee (EmpNumber,EmpFirstName,EmpLAStName,EmpEmail,salary)
Values('A004','Nidhi','Sharma','nidhi@abcmail.com',270000)


For example, in case of "AND", suppose we want to get records from the table Employee whose last name is "Sharma" and salary less than 300000, then we can use the following query:-

Select * from employee where EmpLastName='Sharma' AND salary < 300000

This query will return the record of the employee whose empnumber is "A004".

In case of "OR", suppose we want to get records from the table Employee whose last name is either Sharma or salary is less than 300000, then we can use the following query

Select EmpNumber from employee where EmpLastName='Sharma' OR salary  <  300000

This query will returns all the records:-

A001
A002
A003
A004


Tuesday, August 12, 2014

SUM() function in SQL Server

Sum():- This function is used to get the sum of values of the specified numeric column.

 Syntax:-

 Select Sum (column_name) from tablename


Example:-

In this article, we are going to use the following table for the examples:-

CREATE TABLE [dbo]. [Employee](
[Empid] [Int] IDENTITY (1, 1) NOT NULL Primary key,
[EmpNumber] [nvarchar](50) NOT NULL,
[EmpFirstName] [nvarchar](150) NOT NULL,
[EmpLAStName] [nvarchar](150) NULL,
[EmpEmail] [nvarchar](150) NULL,
[Salary] [int] NULL
)


We insert following data into the above tables:-
Insert Into Employee (EmpNumber,EmpFirstName,EmpLAStName,EmpEmail,salary)
Values('A001','Vivek','Johari','samir@abcmail.com',200000)

Insert Into Employee (EmpNumber,EmpFirstName,EmpLAStName,EmpEmail,salary)
Values('A002','Abhinav','Golwalkar','abhinav@abcmail.com',100000)

Insert Into Employee (EmpNumber,EmpFirstName,EmpLAStName,EmpEmail,salary)
Values('A003','Neha','Sharma','neha@abcmail.com',300000)

Insert Into Employee (EmpNumber,EmpFirstName,EmpLAStName,EmpEmail,salary)
Values('A004','Chandra','Singh','vivek@abcmail.com',320000)

Insert Into Employee (EmpNumber,EmpFirstName,EmpLAStName,EmpEmail,salary)
Values('A005','Avinash', 'Dubey','avinash@abcmail.com',250000)
 


For example, query for finding the total amount of salary given by the company to its employee is given below:-

 Select sum (salary) from Employee

 Result

 1170000

DMCA.com

Round() function in SQL Server

Round():-This function is used to round a numeric field to the number of decimals specified.

Syntax:-

    ROUND ( numeric_expression , length [ ,function ]

numeric_expression :-Expression of the exact numeric or approximate numeric data type category, except for the bit data type.

length:- Optional.Must be an expression of type tinyint, smallint, or int. When length is a positive number, numeric_expression is rounded to the number of decimal positions specified by length. When length is a negative number, numeric_expression is rounded on the left side of the decimal point, as specified by length.

function:-Function must be tinyint, smallint, or int. When function is omitted or has a value of 0 (default), numeric_expression is rounded. When a value other than 0 is specified, numeric_expression is truncated.

Examples:-

1) Select Round(234.32,-3)
Result:-
0.00

2) Select Round(234.32,-2)
Result:-
200.00

3) Select Round(234.32,-1)
Result:-
230.00

4) select Round(234.32,0)
Result:-
234.00

5) Select Round(234.32,1)
Result:-
234.30

6) Select Round(234.32, 2)
Result:-
234.32

7) Select Round(234.32,3)
Result
234.32

DMCA.com

Except command in SQL Server

This function returns the distinct values from the left query that are not also found on the right query. For Except command, below basic rules must be followed.

  1. The number and the order of the columns must be the same in all queries.
  2. The data types must be compatible.

Syntax

 { query_specification }
   EXCEPT
 { query_specification }


In this article, we are going to use the following table for the examples:-

CREATE TABLE [dbo]. [Employee](
[Empid] [Int] IDENTITY (1, 1) NOT NULL Primary key,
[EmpNumber] [nvarchar](50) NOT NULL,
[EmpFirstName] [nvarchar](150) NOT NULL,
[EmpLAStName] [nvarchar](150) NULL,
[EmpEmail] [nvarchar](150) NULL,
[Salary] [int] NULL
)


We insert following data into the above tables:-

Insert Into Employee (EmpNumber,EmpFirstName,EmpLAStName,EmpEmail,salary)
Values('A001','Vivek','Johari','samir@abcmail.com',200000)

Insert Into Employee (EmpNumber,EmpFirstName,EmpLAStName,EmpEmail,salary)
Values('A002','Amit','Kumar','amit@abcmail.com',100000)

Insert Into Employee (EmpNumber,EmpFirstName,EmpLAStName,EmpEmail,salary)
Values('A003','Neha','Sharma','neha@abcmail.com',300000)

Insert Into Employee (EmpNumber,EmpFirstName,EmpLAStName,EmpEmail,salary)
Values('A004','Chandra','Singh','vivek@abcmail.com',320000)

Insert Into Employee (EmpNumber,EmpFirstName,EmpLAStName,EmpEmail,salary)
Values('A005','Avinash', 'Dubey','avinash@abcmail.com',250000)

Example

 For example, if we execute the below queries individually, we get the following result:-

Select Empnumber from employee where salary between 100000 and 250000

Result

A001
A002
A005


Select Empnumber from employee where salary>200000

Result

A003
A004
A005


Now, if we use Except command between these two above mention queries, we got the following result:-

Select Empnumber from employee where salary>200000
 EXCEPT
Select Empnumber from employee where salary between 100000 and 250000
 

Results

A003
A004

If we reverse the order of the SQL Queries, we get the different results

Select Empnumber from employee where salary between 100000 and 250000
EXCEPT
Select Empnumber from employee where salary>200000

Result

A001
A002


From the above example's results, we can say that Except command returns the distinct values from the left query that are not also found on the right query

DMCA.com

Monday, August 11, 2014

Like() function in SQL Server

Like:- This operator is used in a WHERE clause to search for a specific pattern in the values of the column.

In this article, we are going to use the following table for the examples:-

CREATE TABLE [dbo]. [Employee](
[Empid] [Int] IDENTITY (1, 1) NOT NULL Primary key,
[EmpNumber] [nvarchar](50) NOT NULL,
[EmpFirstName] [nvarchar](150) NOT NULL,
[EmpLAStName] [nvarchar](150) NULL,
[EmpEmail] [nvarchar](150) NULL,
[Salary] [int] NULL
)


We insert following data into the above tables:-

Insert Into Employee (EmpNumber,EmpFirstName,EmpLAStName,EmpEmail,salary)
Values('A001','Vivek','Johari','samir@abcmail.com',200000)

Insert Into Employee (EmpNumber,EmpFirstName,EmpLAStName,EmpEmail,salary)
Values('A002','Amit','Kumar','amit@abcmail.com',100000)

Insert Into Employee (EmpNumber,EmpFirstName,EmpLAStName,EmpEmail,salary)
Values('A003','Neha','Sharma','neha@abcmail.com',300000)

Insert Into Employee (EmpNumber,EmpFirstName,EmpLAStName,EmpEmail,salary)
Values('A004','Nidhi','Sharma','nidhi@abcmail.com',270000)
 


For example, suppose we want to get the records of the employee whose firstname start with character 'N', then we can use the below query:-

select * from employee where EmpFirstName  like 'N%'

This query will returns the records for the employee whose empnumber is given below:

A003
A004

Again, suppose we want to get the records of the employees whose last name end with character 'a', then we can use the below query:-

Select * from employee where EmplastName  like '%a'

This query will returns the records for the employee whose empnumber is given below:-

A003
A004

Now, suppose we want to get the records of the employees whose last name contains character 'ha', then we can use the below query:-

select * from employee where EmplastName  like '%ha%'

This query will returns the records for the employees whose empnumber is given below:

A001
A003
A004

DMCA.com

Best practices for Database Programming

1. All the tables in the database have properly defined relationship using primary keys and foreign keys . 

2. Indexes should be created on the tables as it increases the performance of the SQL Queries.

3. Find the indexes which need to be reorganized or rebuild.

4. Use "set not count on/off" as it will increases the performance of the queries.

5. Try to avoid the cursor and replaces it with while loop or using switch statements or Sub Queries.
since until the cursor is not deallocated, memory is occupied by the cursor data and in case of large amount of data, it can decreased the performance of the sql queries.

6. Proper use of temporary tables and table variable.

7. Use Joins instead of Subqueries.

8. Intelligent use of Distinct command. Distinct command add additional cost to the query. So we should use Distinct commands only when we needs the unique results.

9. Proper error handling using Try....catch.

10. Do not use "Select * from tablename" command when you only needs to select few columns from the table.

11. Keep the Transactions as short as possible

12. Never use "sp_" prefix in your store procedure name.

13. Use SQL Profiler to monitor the SQL performance.

14. Use stored procedure instead of inline Queries in the code.

15. If we do not need the unique results, use Union all instead of Union.





DMCA.com

Between function in SQL Server

This function is used to select the values within a specified range. These values can be of Int type or Date data type or Text data type.

Syntax:-

    SELECT column_name(s)
    FROM table_name
    WHERE column_name BETWEEN value1 AND value2


In this article, we are going to use the following table for the examples:-

CREATE TABLE [dbo]. [Employee](
[Empid] [Int] IDENTITY (1, 1) NOT NULL Primary key,
[EmpNumber] [nvarchar](50) NOT NULL,
[EmpFirstName] [nvarchar](150) NOT NULL,
[EmpLAStName] [nvarchar](150) NULL,
[EmpEmail] [nvarchar](150) NULL,
[Salary] [int] NULL
)


We insert following data into the above tables:-

Insert Into Employee (EmpNumber,EmpFirstName,EmpLAStName,EmpEmail,salary)
Values('A001','Vivek','Johari','samir@abcmail.com',200000)

Insert Into Employee (EmpNumber,EmpFirstName,EmpLAStName,EmpEmail,salary)
Values('A002','Amit','Kumar','amit@abcmail.com',100000)

Insert Into Employee (EmpNumber,EmpFirstName,EmpLAStName,EmpEmail,salary)
Values('A003','Neha','Sharma','neha@abcmail.com',300000)

Insert Into Employee (EmpNumber,EmpFirstName,EmpLAStName,EmpEmail,salary)
Values('A004','Chandra','Singh','vivek@abcmail.com',320000)

Insert Into Employee (EmpNumber,EmpFirstName,EmpLAStName,EmpEmail,salary)
Values('A005','Avinash', 'Dubey','avinash@abcmail.com',250000)

Example


For example, below query will give the Empnumber of all the employee whose salary lies between 250000 and 320000 (Including both the values (250000 & 320000 ))

 SELECT EmpNumber
    FROM employee
    WHERE salary BETWEEN 250000 AND 320000

Results


A003
A004
A005


Between command includes both the values (value1 and value 2) for returning the result. For example, suppose we want to get the records of the employees whose empid is lies between 1 and 4 then the query will be:-


select  EmpNumber  from employee where empid between 1 and 4


Result

A001
A002
A003
A004


If we do not want to includes either of these specified minimum and maximum values or both these values , then we should use > or < operator instead of between command

For example below query will gives the EmpNumbers of employees whose empid are greater than 1 but less the 4

select  EmpNumber  from employee where empid>1and empid<4 span="">

Result

A002
A003


Not Between:- If we want to select the values outside the specified range, we can use the NOT between function. 

Syntax:-

SELECT column_name(s)
    FROM table_name
    WHERE column_name Not BETWEEN value1 AND value2


For example, below query will give the Empnumber of all the employee whose salary lies outside the range specified by the minimum value of 250000 and maximum 320000.

 SELECT EmpNumber
    FROM employee
    WHERE salary NOT BETWEEN 250000 AND 320000


Result

A001
A002 

 

DMCA.com

LEFT() function in SQL Server

Left():- This function returns the specified number of characters from the left part of the given character string.

Syntax:-


Select LEFT ( character_expression , integer_expression )
   
For example, the below query will return two characters from the beginning

Select LEFT  ('Vivek',2)

Result

Vi

DMCA.com

RIGHT() function in SQL Server

Right():- This function returns the specified number of characters from the Right part of the given character string.

Syntax:-

 Select RIGHT ( character_expression , integer_expression )

 For example, the below query will return two characters from the last.

 Select RIGHT('Vivek',2) 

 
 Result

 ek

DMCA.com

Intersection command in SQL Server

This function is used to returns distinct values that are returned by both the query on the left and right sides of the INTERSECT operand. For intersection command, below basic rules must be followed.
  1. The number and the order of the columns must be the same in all queries.
  2. The data types must be compatible.
 Syntax:-

{ query_specification }
 INTERSECT
{ query_specification }


In this article, we are going to use the following table for the examples:-

CREATE TABLE [dbo]. [Employee](
[Empid] [Int] IDENTITY (1, 1) NOT NULL Primary key,
[EmpNumber] [nvarchar](50) NOT NULL,
[EmpFirstName] [nvarchar](150) NOT NULL,
[EmpLAStName] [nvarchar](150) NULL,
[EmpEmail] [nvarchar](150) NULL,
[Salary] [int] NULL
)


We insert following data into the above tables:-

Insert Into Employee (EmpNumber,EmpFirstName,EmpLAStName,EmpEmail,salary)
Values('A001','Vivek','Johari','samir@abcmail.com',200000)

Insert Into Employee (EmpNumber,EmpFirstName,EmpLAStName,EmpEmail,salary)
Values('A002','Amit','Kumar','amit@abcmail.com',100000)

Insert Into Employee (EmpNumber,EmpFirstName,EmpLAStName,EmpEmail,salary)
Values('A003','Neha','Sharma','neha@abcmail.com',300000)

Insert Into Employee (EmpNumber,EmpFirstName,EmpLAStName,EmpEmail,salary)
Values('A004','Chandra','Singh','vivek@abcmail.com',320000)

Insert Into Employee (EmpNumber,EmpFirstName,EmpLAStName,EmpEmail,salary)
Values('A005','Avinash', 'Dubey','avinash@abcmail.com',25000
0)

Example
If we execute the below queries individually, we get the following result:-

Select Empnumber from employee where salary>200000

Result

A003
A004
A005


Select Empnumber from employee where salary between 100000 and 320000

Result

A001
A002
A003
A004
A005

Now if we use Intersection command between these queries, we get the following result.

Select Empnumber from employee where salary>200000
 INTERSECT
Select Empnumber from employee where salary between 100000 and 320000

Result

A003
A004
A005

Above example shows that the Intersection command will return only those distinct records which are return by both the queries(left side query as well as right side query).

Exception handling in SQL Server

                    
SQL server gives the exception/error handling mechanism which is similar to c#, Try..Catch block. In SQL Server, we can write the SQL Statements within the Try block and if any exception occurred control automatically goes to the group of SQL Statements written inside the next Catch block.

The syntax for Try..Catch is given below:-

BEGIN TRY
     { sql_statement | statement_block }
END TRY
BEGIN CATCH
     [ { sql_statement | statement_block } ]
END CATCH
[ ; ]

For example, suppose we have a table employeedetails whose structure is given below:

CREATE table employeedetails (id int identity(1,1),empsignum nvarchar(20), empFname nvarchar(100), empEname nvarchar(100), empdate datetime)

Now, for the demo purpose, we can insert or update data in this table with the help of a stored procedure whose script is given below:-

CREATE PROCEDURE Demo_exceptionhandling
(
@empsignum nvarchar(20),
@Fname nvarchar(100),
@Ename nvarchar(100)

)
AS
BEGIN
SET NOCOUNT ON

BEGIN TRY
   IF EXISTS (select 1 from employeedetails where empsignum=@empsignum)
     BEGIN
       Update employeedetails set empFname=@Fname ,empEname=@Ename  where empsignum=@empsignum
     END
   ELSE
      BEGIN
       Insert into employeedetails ( empsignum, empFname  ,  empEname  , empdate )
       Values (@empsignum, @Fname, @Ename, getdate())
      END
   END TRY
  BEGIN CATCH
        SELECT
     ERROR_NUMBER() AS ErrorNumber
    ,ERROR_SEVERITY() AS ErrorSeverity
    ,ERROR_STATE() AS ErrorState
    ,ERROR_PROCEDURE() AS ErrorProcedure
    ,ERROR_LINE() AS ErrorLine
    ,ERROR_MESSAGE() AS ErrorMessage
        
   END CATCH
   SET NOCOUNT OFF
 END

In the above stored procedure if any error occurs within the try block, it will automatically move to CATCH block where we can handle the error messages with the defined Error Functions in CATCH block. If we want we can save the values return by these error function into a table for future references.These Error Functions are given below:-

ERROR_LINE() - Return error line number of SQL query which cause to raise error.

ERROR_NUMBER() - Return error number which is unique and assigned to it.

ERROR_SEVERITY() - Return severity of error which indicates how serious the error is. The values are between 1 and 25.

ERROR_STATE() - Return state number of error message which cause to raise error.

ERROR_PROCEDURE() - Return name of the procedure where an error occurred.

ERROR_MESSAGE() - Return the complete text of the error message which cause to raise error.

We can also use TRY... CATCH block with transactions where we can write the ROLLBACK command within the CATCH block so that all the uncommitted transactions become rollback in case of any error.

For example, in the below SQL script, we have implemented the transaction in the stored procedure which we have mentioned above.

CREATE PROCEDURE Demo_exceptionhandling
(
@empsignum nvarchar(20),
@Fname nvarchar(100),
@Ename nvarchar(100)

)
AS
BEGIN
SET NOCOUNT ON

BEGIN TRANSACTION

BEGIN TRY
   IF EXISTS (select 1 from employeedetails where empsignum=@empsignum)
     BEGIN
       Update employeedetails set empFname=@Fname ,empEname=@Ename  where empsignum=@empsignum
     END
   ELSE
      BEGIN
       Insert into employeedetails ( empsignum, empFname  ,  empEname  , empdate )
       Values (@empsignum, @Fname, @Ename, getdate())
      END
 
  END TRY
  BEGIN CATCH

  IF @@TRANCOUNT > 0
     ROLLBACK TRANSACTION

        SELECT
     ERROR_NUMBER() AS ErrorNumber
    ,ERROR_SEVERITY() AS ErrorSeverity
    ,ERROR_STATE() AS ErrorState
    ,ERROR_PROCEDURE() AS ErrorProcedure
    ,ERROR_LINE() AS ErrorLine
    ,ERROR_MESSAGE() AS ErrorMessage
    ,GETDATE()
    
   END CATCH

   IF @@TRANCOUNT > 0
    BEGIN
     commit transaction
    END
   SET NOCOUNT OFF
 END

TRY…CATCH do not handle the following conditions:

1) Warnings or informational messages that have a severity of 10 or lower.
2) Errors that have a severity of 20 or higher that stop the SQL Server Database Engine task processing for the session.
3) Attentions, such as client-interrupt requests or broken client connections.
4) When the session is ended by a system administrator by using the KILL statement.
5) Compile errors, such as syntax errors, that prevent a batch from running.
6) Errors that occur during statement-level recompilation, such as object name resolution errors that occur after compilation because of deferred name resolution.

DMCA.com