SQL Server Project 1

PROBLEM:

we have a table named as CONSULTANTS. And it has some data with id, name and expertise fields. Write a SQL query which could give the following output:

Original table output	Expected output

SOLUTION:

Step 1: creation of the table

CREATE TABLE CONSULTANTS(

  id NVARCHAR(10),

  name NVARCHAR(20),

  expertise NVARCHAR(20)

);

Step 2: inserting some records into the table

INSERT INTO CONSULTANTS VALUES('001', 'Mr. B', 'SSRS');

INSERT INTO CONSULTANTS VALUES('002', 'Mr. R', 'SSRS');

INSERT INTO CONSULTANTS VALUES('001', 'Mr. B', 'SSIS');

INSERT INTO CONSULTANTS VALUES('003', 'Mr. K', 'DBA');

INSERT INTO CONSULTANTS VALUES('004', 'Mr. M', 'Nothing');

Step 3: Checking the output of the table

SELECT * FROM CONSULTANTS;

Step 4: Writing the code to get the expected output

SELECT

  result.id [Consultant ID]

  ,result.name [Consultant Name]

  ,iif(result.SSRS = 1, 'Yes', '') SSRS

  ,iif(result.SSIS = 1, 'Yes', '') SSIS

  ,iif(result.DBA = 1, 'Yes', '') DBA

  ,iif(result.Nothing = 1, 'Yes', '') Nothing

FROM(

      SELECT

          *

      FROM

          CONSULTANTS

   PIVOT(

          COUNT(expertise)

          FOR expertise IN ([SSRS], [SSIS], [DBA], [Nothing])

   ) AS PIVOTRESULT

) result

And after executing the above query, it will throw the following output.

Good luck  :D

How to duplicate a TABLE using Microsoft SQL Server Mangement?

We can easily duplicate a TABLE using Microsoft SQL Server Management Studio by using the following two line of codes:

SELECT * INTO NewTable

FROM OldTable                                 

Note:

• change NewTable to your desired table name
• change OldTable to your current table name
• the above SQL will copy only the basic table structure and all the data of that table. The SQL will NOT copy any of the constraints. To do this, you need to script those out and change the names in those scripts

Differences between Stored Procedure and Stored Function

Stored Programs: Types and description

MySQL Stored Procedure All

---

Beginning Stored Procedures

---

---

Stored Procedures with Multiple Resultset

---

---

Stored Procedures with Parameters - Input and Output

---

---

Demo Stored Procedures - Drop and Create

---

What is the impact to use of desc, asc or none of them?

---

SQL Query without any ordering:

---

if you don’t specify, by default it’s a ascending order. [1,2,3… a,b,c]

select Name

  ,CountryRegionCode,

  ROW_NUMBER() over (order by Name)

from

  Sales.SalesTerritory

Output of the above query:

---

SQL Query with Ascending order: [1,2,3… a,b,c]

---

select Name

  ,CountryRegionCode,

  ROW_NUMBER() over (order by Name)

from

  Sales.SalesTerritory

order by

  Name asc

Output of the above query:

---

SQL Query with Descending order: [3,2,1… z,y,x….]

---

select Name

  ,CountryRegionCode,

  ROW_NUMBER() over (order by Name)

from

  Sales.SalesTerritory

order by

  Name desc

Output of the above query:

How to improve performance when we need to merge data from several data sources?

For merge operation, input data must be sorted. So, we generally use Sort to sort data. And the SSIS package looks like the following:

But it has a performance issue. If the data source has too many number of records, it becomes very very slow. So, the solution is to avoid the use of Sort component.

To do this, go to the Advanced Editor of source component and change the following proterties:

1 means ----> ascending

-1 means ---> deascending

After completing the above steps, you will see the that the package is executed faster compared to the previous scenario.