How to add new instance in SQL Server

  1. All program -> SQL Server Installation center
    In windows 8 -> Search -> SQL Server Installation Cepter
  2. From left menu select “Installation->New Sql server standalone installation or add features”
  3. Browse CD or Physical directory of SQL Server
  4. Auto run “Setup support rules” click ok
  5. Product update -> click next
  6. Install setup files -> click next
  7. Setup support rules and click next
  8. Select Installation type -> select “Perform new installation of SQL Server 2012” and click next1
  9. Enter product key and click next
  10. I accept the licence key
  11. Setup rules -> select “All features with default” -> Click next
  12. Feature selection -> Click Next
  13. Instance Configuration -> Named instance -> Type SQLExpress
    And InstanceId -> Type SQLEXPRESS and click next2
  14. Server configuration -> click next
  15. Database engine configuration -> Select windows authentication mode -> Click add current user and click next3
  16. Click next …. Next … next like as usual sql server installation.
  17. After complete installation. Try to enter using new created instance

4

SQL Join with Microsoft SQL Server

What is SQL Joins?

A SQL join clause is used to combine records from two or more tables in a database based on common field between them. It creates a set of rows in a temporary table.

There are different types of joins available in SQL:
• INNER JOIN
• LEFT JOIN
• RIGHT JOIN
• FULL JOIN
• SELF JOIN
• CARTESIAN JOIN/CROSS JOIN

To understand JOINs, let’s consider the following two tables Customers and Orders is as follows

Customers

01-Customer02

Orders

02-Orders02

INNER JOIN
INNER JOIN returns match data between tables. It returns rows when there is at least one match in both the tables.

03-InnerJoin

Syntax:

SELECT Cust.Id, Cust.Name, ord.Amount, ord.[Date]
FROM Customers Cust
INNER JOIN Orders ord
ON Cust.Id = ord.CustomerId;

Output:

Id          Name                                               Amount                                  Date

———– ————————————————– ————————————— ———-

2           Hamidur Rahman                          4000.00                                 2014-01-10

2           Hamidur Rahman                          5000.00                                 2014-01-10

3           Jamilur Rahman                            3000.00                                 2014-02-13

4           Hasan Sarwar                                  600.00                                  2014-03-22

LEFT JOIN/LEFT OUTER JOIN
Left Outer Join returns all the rows from the left table in conjunction with the matching rows from the right table. If there are no columns matching in the right table, it returns NULL values.

04-Left join

Syntax:

SELECT Cust.Id, Cust.Name, ord.Amount, ord.[Date]
FROM Customers Cust
LEFT JOIN Orders ord
ON Cust.Id = ord.CustomerId;

Output:

Id          Name                                               Amount                                  Date

———– ————————————————– ————————————— ———-

1           Mahedee Hasan                               NULL                                    NULL

2           Hamidur Rahman                           4000.00                                 2014-01-10

2           Hamidur Rahman                           5000.00                                 2014-01-10

3           Jamilur Rahman                              3000.00                                 2014-02-13

4           Hasan Sarwar                                  600.00                                  2014-03-22

5           Kamal Hossain                                NULL                                    NULL

6           Imran Khan                                     NULL                                    NULL

7           Abu Sayem                                       NULL                                    NULL

RIGHT JOIN/RIGHT OUTER JOIN
Right outer join returns all the rows from the right table in conjunction with the matching rows from the left table. If there are no columns matching in the left table, it returns NULL values.

05-Right join

Syntax:

SELECT Cust.Id, Cust.Name, ord.Amount, ord.[Date]
FROM Customers Cust
RIGHT JOIN Orders ord
ON Cust.Id = ord.CustomerId;

Output:

Id               Name                                               Amount                                  Date

———– ————————————————– ————————————— ———-

2                 Hamidur Rahman                        4000.00                                 2014-01-10

2                 Hamidur Rahman                        5000.00                                 2014-01-10

3                 Jamilur Rahman                           3000.00                                 2014-02-13

4                 Hasan Sarwar                                600.00                                  2014-03-22

NULL        NULL                                               800.00                                  2014-03-22

FULL JOIN /FULL OUTER JOIN
Full outer join is combines left outer join and right outer join. It returns row from either table when the conditions are met and returns null value when there is no match

06-Full Join

Syntax:

SELECT Cust.Id, Cust.Name, ord.Amount, ord.[Date]
FROM Customers Cust
FULL JOIN Orders ord
ON Cust.Id = ord.CustomerId;

Output:

Id                    Name                                               Amount                                  Date

———– ————————————————– ————————————— ———-

1                      Mahedee Hasan                             NULL                                    NULL

2                     Hamidur Rahman                          4000.00                                 2014-01-10

2                    Hamidur Rahman                           5000.00                                 2014-01-10

3                    Jamilur Rahman                             3000.00                                 2014-02-13

4                   Hasan Sarwar                                   600.00                                  2014-03-22

5                   Kamal Hossain                                 NULL                                    NULL

6                   Imran Khan                                      NULL                                    NULL

7                    Abu Sayem                                       NULL                                    NULL

NULL          NULL                                                800.00                                  2014-03-22

SELF JOIN
The SQL SELF JOIN is used to join a table to itself as if the table were two tables, temporarily renaming at least one table in the SQL statement.

Syntax:

SELECT A.Id, A.Name, B.Name AS Introducer
FROM Customers A, Customers B
WHERE A.IntroducerId = B.Id

Output:

Id          Name                                               Introducer

———– ————————————————– ————————————————–

1           Mahedee Hasan                             Hamidur Rahman

2           Hamidur Rahman                         Mahedee Hasan

3           Jamilur Rahman                           Hamidur Rahman

4           Hasan Sarwar                                Jamilur Rahman

5           Kamal Hossain                              Hasan Sarwar

6           Imran Khan                                   Kamal Hossain

7           Abu Sayem                                     Imran Khan

CARTESIAN JOIN/CROSS JOIN
The SQL cross join produces a result set which is the number of rows in the first table multiplied by the number of rows in the second table, if no WHERE clause uses in the second table

07-crossjoin

Syntax:

SELECT cust.Id, cust.Name, ord.Amount, ord.[Date]
FROM Customers cust, Orders ord

SELECT cust.Id, cust.Name, ord.Amount, ord.[Date]
FROM Customers cust CROSS JOIN Orders ord

Output:

Id          Name                                               Amount                                  Date

———– ————————————————– ————————————— ———-

1           Mahedee Hasan                            4000.00                                 2014-01-10

2           Hamidur Rahman                       4000.00                                 2014-01-10

3           Jamilur Rahman                          4000.00                                 2014-01-10

4           Hasan Sarwar                                4000.00                                 2014-01-10

5           Kamal Hossain                             4000.00                                 2014-01-10

6           Imran Khan                                  4000.00                                 2014-01-10

7           Abu Sayem                                     4000.00                                 2014-01-10

1           Mahedee Hasan                            5000.00                                 2014-01-10

2           Hamidur Rahman                       5000.00                                 2014-01-10

3           Jamilur Rahman                          5000.00                                 2014-01-10

4           Hasan Sarwar                               5000.00                                 2014-01-10

5           Kamal Hossain                             5000.00                                 2014-01-10

6           Imran Khan                                  5000.00                                 2014-01-10

7           Abu Sayem                                    5000.00                                 2014-01-10

1           Mahedee Hasan                            3000.00                                 2014-02-13

2           Hamidur Rahman                       3000.00                                 2014-02-13

3           Jamilur Rahman                          3000.00                                 2014-02-13

4           Hasan Sarwar                               3000.00                                 2014-02-13

5           Kamal Hossain                             3000.00                                 2014-02-13

6           Imran Khan                                  3000.00                                 2014-02-13

7           Abu Sayem                                    3000.00                                 2014-02-13

1           Mahedee Hasan                            600.00                                  2014-03-22

2           Hamidur Rahman                       600.00                                  2014-03-22

3           Jamilur Rahman                          600.00                                  2014-03-22

4           Hasan Sarwar                               600.00                                  2014-03-22

5           Kamal Hossain                             600.00                                  2014-03-22

6           Imran Khan                                  600.00                                  2014-03-22

7           Abu Sayem                                    600.00                                  2014-03-22

1           Mahedee Hasan                            800.00                                  2014-03-22

2           Hamidur Rahman                        800.00                                  2014-03-22

3           Jamilur Rahman                          800.00                                  2014-03-22

4           Hasan Sarwar                               800.00                                  2014-03-22

5           Kamal Hossain                             800.00                                  2014-03-22

6           Imran Khan                                  800.00                                  2014-03-22

7           Abu Sayem                                    800.00                                  2014-03-22

Additional Information Related JOIN

Left Outer Join Where Null
Left outer join where null returns all the rows from the left table in conjunction without the matching rows from the right table.

08-left Join and Null

Syntax

SELECT Cust.Id, Cust.Name, ord.Amount, ord.[Date]
FROM Customers Cust
LEFT JOIN Orders ord
ON Cust.Id = ord.CustomerId
where ord.CustomerId is null

Output

Id          Name                                               Amount                                  Date

———– ————————————————– ————————————— ———-

1           Mahedee Hasan                              NULL                                    NULL

5           Kamal Hossain                               NULL                                    NULL

6           Imran Khan                                    NULL                                    NULL

7           Abu Sayem                                      NULL                                    NULL

Right Outer Join Where Null
Right outer join where null returns all the rows from the right table in conjunction without the matching rows from the left table.

08-Right Join and Null

Syntax:

SELECT Cust.Id, Cust.Name, ord.Amount, ord.[Date]
FROM Customers Cust
RIGHT JOIN Orders ord
ON Cust.Id = ord.CustomerId
where Cust.Id is null

Output

Id              Name          Amount        Date
———– ————————————————– ————————————— ———-
NULL       NULL          800.00        2014-03-22

Full Outer Join Where Null
Full outer join is combines left outer join and right outer join. It returns row from either table when the conditions are met and returns null value when there is no match

10-full Join and Null

Syntax

SELECT Cust.Id, Cust.Name, ord.Amount, ord.[Date]
FROM Customers Cust
FULL OUTER JOIN Orders ord
ON Cust.Id = ord.CustomerId
WHERE Cust.Id is null
OR ord.CustomerId is null

Output

Id          Name                                                  Amount                                  Date

———– ————————————————– ————————————— ———-

1           Mahedee Hasan                                  NULL                                    NULL

5           Kamal Hossain                                   NULL                                    NULL

6           Imran Khan                                        NULL                                    NULL

7           Abu Sayem                                          NULL                                    NULL

NULL        NULL                                            800.00                                  2014-03-22

Recovering SQL Server Database from Suspect Mode

Sometimes we have to face a critical situation when SQL Server database going to Suspect Mode. In that moment no work can be done on database. Database may go into suspect mode because the primary file group is damaged and the database cannot be recovered during the startup of the SQL Server

Reason for database to go into suspect mode:

  1.  Data files or log files are corrupt.
  2.  Database server was shut down improperly
  3.  Lack of Disk Space
  4.  SQL cannot complete a rollback or roll forward operation.

How to recover database from suspect mode:

1.    Change the status of your database. Suppose database name is “BluechipDB”

EXEC sp_resetstatus '';

Example:

EXEC sp_resetstatus 'BlueChipDB'

  1.  Set the database in “Emergency” mode
ALTER DATABASE  SET EMERGENCY;

Example:

ALTER DATABASE BlueChipDB SET EMERGENCY
  1.  Check the database for any inconsistency
DBCC CHECKDB('');

Example:

DBCC checkdb('BlueChipDB')

4. If you get any error after executing DBCC CHECKDB then immediately bring the database in SINGLE USER MODE by running following query. If no error found then you need not execute the following query.

ALTER DATABASE  SET SINGLE_USER WITH ROLLBACK IMMEDIATE

Example:

 ALTER DATABASE BlueChipDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE

5. For safety, take the backup of the database.

6. Run the following query as next step. Remember while using the below query, that uses REPAIR_ALLOW_DATA_LOSS, is a one way operation that is once the database is repaired all the actions performed by these queries can’t be undone. There is no way to go back to the previous state of the database. So as a precautionary step you should take backup of your database in step 5 mentioned above.

DBCC CHECKDB ('', REPAIR_ALLOW_DATA_LOSS);

Example:

DBCC CheckDB ('BlueChipDB', REPAIR_ALLOW_DATA_LOSS)

7. Finally, bring the database in MULTI USER mode

ALTER DATABASE  SET MULTI_USER;
ALTER DATABASE [BlueChipDB]  SET MULTI_USER
  1.  Refresh your database server and verify the connectivity of your database. Now users should be able to connect to the database properly. If any data loss, you can restore database – backup taken in step 5.

Retrieve Store Procedure’s Output parameter by C# (ASP.net)

How to retrieve store procedure’s output parameter by C#? Here I have explained with a simple example. I created a simple store procedure with output parameter. Then I catch it from code behind page of asp.net and displayed it to a label. Here I used a class DBConnector to connect with database. Lets look on the demo.

Step 1: Set connection string in web.config.


     


Step 2: Create a store procedure with output parameter.

CREATE PROCEDURE [dbo].[sp_output_param]
(
     @input_param VARCHAR(200)
    , @Response VARCHAR(250) OUTPUT
)
AS
    --DECLARE @myName
BEGIN  
    SET @Response = 'Welcome ' + @input_param
END

Step 3: Create a DBConnector Class to retrieve data from database.

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
 
public class DBConnector
{
    private string connectionString = null;
    private SqlConnection sqlConn = null;
    private SqlCommand cmd = null;
 
    public DBConnector()
    {
        connectionString = ConfigurationManager.ConnectionStrings["SQLServerConnectionString"].ToString();
    }
 
 
    public SqlCommand GetCommand()
    {
        cmd = new SqlCommand();
        cmd.Connection = sqlConn;
        return cmd;
    }
 
    public SqlConnection GetConn()
    {
        sqlConn = new SqlConnection(connectionString);
        return sqlConn;
    }
 
}

Step 4 : Create a label name lblMsg in asp page. Then in a button event, write the following code. You will see your desired output in the lebel which is actually the value of output parameter. Here btnGetOutputParam_Click is the click event of Button btnGetOutputParam.

protected void btnGetOutputParam_Click(object sender, EventArgs e)
{
        SqlConnection sqlConn;
        SqlCommand cmd;
 
        DBConnector objDBConnector = new DBConnector();
        sqlConn = objDBConnector.GetConn();
 
        cmd = objDBConnector.GetCommand();
 
        SqlDataReader rdr = null;
 
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = "sp_output_param";
        cmd.Parameters.AddWithValue("@input_param", "Mahedee");
        cmd.Parameters.Add("@Response", SqlDbType.VarChar, 250);
        cmd.Parameters["@Response"].Direction = ParameterDirection.Output;
 
        try
        {
            if (sqlConn.State == ConnectionState.Closed)
                sqlConn.Open();
 
            rdr = cmd.ExecuteReader();
 
            string outputValue = cmd.Parameters["@Response"].Value.ToString();
            this.lblMsg.Text = outputValue;
        }
        catch (Exception exp)
        {
            throw (exp);
        }
        finally
        {
            if (sqlConn.State == ConnectionState.Open)
                sqlConn.Close();
        }
 
    }

You will see in label – Welcome Mahedee which is provided by output parameter of store procedure.

Install SQL Server 2008 R2 – Step by Step

We often fall problem to install SQL Server 2008 R2. Here is the step by step procedure to install SQL Server 2008 R2. Microsoft .NET Framework 3.5 SP1 is the pre-requisite for SQL Server 2008 R2. If it is not installed in your computer, install it before proceed to the setup procedure.

Step 1: Run DVD of SQL Server 2008 R2 or Open DVD and click setup.exe

1

Step 2: Select Installation and Click New installation or add features to an existing installation.

2

Step 3: After completing pre installation check, press Ok.

3

Step 4: Type Product Key and click “Next”.

4

Step 5: Select License Term and click “Next”.

5

Step 6: Click “Install” in the following screen.

6

Step 7: Setup performed Support Rules Validation. If no error found, click “Next”. Turn off your windows firewall from control panel to eliminate warning of windows firewall of following screen.

7

Step 8: Select Setup Role – SQL Server Feature Installation. Click Next.

8

Step 9: Select Features or Click “Select All” and click “Next”.

9

Step 10: Click “Next” of Installation Rule windows.
10Step 11: Select Default Instance for simplicity and click “Next”.11

Step 12: Click “Next” after confirming disk space requirement

12

Step 13: In the Server Configuration Screen click “Use the same account for all SQL Server Services”

13

Step 14: In the Pop up screen type user name with domain name and password. Then click Ok.

14

Step 15: Click “Next” in the Server Configuration Screen.

15

Step 16: Select Mixed Mode, type password and confirm password. Click “Add Current user” and click Next in Database Engine Configuration Screen.

16

Step 17: Click “Add Current User” & “Next” in Analysis Service Configuration Screen.

17

Step 18: Select Native Mode & Click Next in Reporting service configuration screen.

18

Step 19: Click “Next” in error reporting screen.

Step 20: Click “Next” in the following screen.

20Step 21: Click “Install” in the following screen.

21Installation progressing……22

Step 22: Complete Installation. Click Close button.

23

Set a fixed amount of memory in SQL Server (Using SQL Server Management Studio)

Min Server memory and max server memory are two server memory options in SQL Server to configure the amount of memory (in megabytes) in the buffer pool used by an instance. SQL Server can change its memory requirements dynamically by default. But you can configure it based on your system memory.

Follow the following steps to set fixed amount of memory in SQL Server.

    1. Right click on server and select Properties in Object Explorer.

1

    1. Click/Select Memory node like below screen.

2

  1. Under Server Memory Options, enter the amount that you want forMinimum server memory and Maximum server memory. Here I used 4500 MB on basis of my system capacity.

Default settings:
Min server memory = 0
Max server memory = 2147483647 MB

Create, Drop, Insert, Update, Delete and Select – The Basic SQL

This topic for those who are very beginner of SQL. Here I will show some basic SQL by Microsoft SQL Server.

1. Create a table: Here I created a table name hrm_employee. Here emp_gid is int type, primary key and identity. Identity means its value will increment automatically by the sql server. You don’t need to insert it’s value.

--Create Table : hrm_employee---
CREATE TABLE hrm_employee
(
emp_gid INT PRIMARY KEY IDENTITY,
emp_fullnm VARCHAR(100),
emp_nicknm VARCHAR(50),
emp_designation VARCHAR(100)
)

2. Insert data in hrm_employee table: In this occasion, I have inserted 3 data i.e 3 rows. If you see in closer look, you will see that I didn’t insert any value of emp_gid because it increments its value automatically because it is an identity column.

--Insert data ito table: hrm_employee----

-- Insert data i.e. row-1 ---
INSERT INTO hrm_employee
(
emp_fullnm
, emp_nicknm
, emp_designation
)
VALUES
(
'Md. Mahedee Hasan'
, 'Mahedee'
, 'Senior Software Engineer'
)

-- Insert data i.e. row-2 ---
INSERT INTO hrm_employee
(
emp_fullnm
, emp_nicknm
, emp_designation
)
VALUES
(
'Md. Asrafuzzaman'
, 'Emon'
, 'Senior Software Engineer'
)

-- Insert data i.e. row-3 ---
INSERT INTO hrm_employee
(
emp_fullnm
, emp_nicknm
, emp_designation
)
VALUES
(
'Md. Khondakar Enamul Haque'
, 'Rony'
, 'Broadcast Engineer'
)

4. Infromation Retrive: Get all information of hrm_employee. Here I showed two ways to retrive data and I suggest to use second one. Second one is best practiced.

--Get all data from hrm_employee--
SELECT * FROM hrm_employee
--or
SELECT emp_gid, emp_fullnm, emp_nicknm, emp_designation FROM hrm_employee

5. Update hrm_employee: Is very easy to update a row or multiple rows against a condition. Lets look on the following query.

--update data from table: hrm_employee --
UPDATE hrm_employee
SET emp_fullnm = 'M.K. Enamul Haque'
, emp_designation = 'Senior Broadcast Engineer'
WHERE emp_gid = 3


6. Delete Information:
If you want to delete information from hrm_employee against a condition follow
the query.

---Delete data from table: hrm_employee---
DELETE FROM hrm_employee WHERE emp_gid = 3

7. Drop Table: Drop table means remove table for database. Use the query to drop hrm_employee table.

---Drop table i.e remove table from Database--
DROP TABLE hrm_employee