Need Suggestion

When I am reviewing code of a stored procedure I find some interesting facts.

The developer sends the date parameter as a string and then converts the date columns of the table to string to compare value.

Like this

WHEN CONVERT(VARCHAR,  examdate,  105) = CONVERT(VARCHAR,  @P_Examdt,  105);

@P_Examdt – The parameters of stored procedure

1.       I don't understand why?

2.        Is it decreasing the performance of the query?

If anyone have any comments please suggest.

Posted by: MR. JOYDEEP DAS

Array in T-SQL

Introduction 

Some junior developer ask me a question that "Can array be created in T-SQL".  This article is dedicated to them.

So the prime question is

Can we create the Array in T-SQL . My answer is NO.

How the problem be solved

As the T-SQL not provide any array

As we cannot create array in T-SQL, but we have alternate solution for that. We can create Table variable to solve the problem of array.

How to do that

Example of table variable creation

BEGIN

  DECLARE @myTabVar TABLE(monthno INT);

 

  INSERT INTO @myTabVar (monthno)

  VALUES (1), (2), (3);

 

  SELECT * FROM @myTabVar;

END

We can add the extra columns according to needs

BEGIN

  DECLARE @myTabVar TABLE(monthno INT, monthnm VARCHAR(50));

 

  INSERT INTO @myTabVar (monthno, monthnm)

  VALUES (1, 'Jan'), (2, 'Feb'), (3, 'Mar');

 

  SELECT monthno As [Month Number] , monthnm As [Month Name] FROM @myTabVar;

END

Output:

Month Number                                Month Name

1                                              Jan

2                                              Feb

3                                              Mar

We can use it as a normal table

UPDATE a

SET    a.monthsval = SUM(b.salval)

FROM   @myTabVar a

       INNER JOIN tbl_saleval b ON a.monthno = b.monthno;

Hope you like it.

Posted by: MR. JOYDEEP DAS

Understanding the BIDS

Introduction

As a beginner before starting the MS SQL Server 2008 SSIS package, I personally think that the understanding the Integration Services in Business Intelligence Development Studio (SSIS BIDS) is very important. So in this article I am trying to provide a general idea related to SSIS BIDS. Hope it will be interesting and helpful for beginners.

Tropics to discuss

Here I am trying to discuss about 2 most important tropics of BIDS

1.    The Solution Explorer

2.    SSIS Designer

The Solution Explorer

Please look at the bellow figure of solution explorer

We can open it by menu View à Solution Explorer or by Clicking [ Ctrl+Alt+L ] Shortcut menu. The solutions explorer contains several folders mentioned bellow.

1.    Data Source Folder

It conations connection shared by multiple SSIS package of the same project. We can create connection manager in a package from the existing data source manager. A data source can be defined one time and then referenced by connection managers in multiple packages. If the data source and the packages that reference it reside in the same project, the connection string property of the data source references is automatically updated when the data source changes.

2.    Data Source View Folder

It contains the subset of the data in a data source and also contains named queries. We can designate tables, views, or named queries from a data source view as the source of data for a data flow source. It can be shared by multiple packages in a project.

3.    SSIS Package Folder

A package is the unit of work in integration service. A single project has multiple packages. The extension of a package is dtsx.

4.    Miscellaneous Folder

The folder contains others files that we want to add in our SSIS project. It may be document file, Image file etc.

SSIS Designer

SSIS Designer is a graphical tool to design SSIS Package. It contains several Tabs. We have to understand each tab. Please look at the bellow figure of SSIS Designer.

1.    Control flow Tabs

In this Tabs we can arrange the containers and tasks in the control flow tabs. It provides the logic for when data flow components are run and how they are run. Also control flows can: perform looping, calling stored procedures, moving files, managing error handling, check a condition and then call different tasks including data flows depending on the result, processing of SSAS cubes etc.

2.    Data flow Tabs

On the Data Flow tab, you combine into a data flow sources that extract data, transformations that modify and aggregate data, destinations that load data, and paths that connect the outputs and inputs of data flow components.

A data flow defines a flow of data from a source to a destination. Data flows between our selected entities (sources, transformations, destinations).

3.    Event Handlers Tabs

Here we can create workflow to responds on specified events of package. For an example we can create an event handler that sends an e-mail message when a task fails.

4.    Package Explorer tab

This tab provides a convenient explorer view of the package, with the package as a container at the top of the hierarchy, and underneath it, the connections, executables, event handlers, log providers, precedence constraints, and variables that you have configured in the package. 

5.    Progress Tab
It display the information about package Executions when we run a SSIS package in BI Studio. The Progress tab lists the start time, the finish time, and the elapsed time for validation and execution of the package and its executables; any information or warnings for the package; progress notifications; the success or failure of the package; and any error messages that are generated during package execution.

6.    Connection Manager Area

Connection managers represent a layer of abstraction between the SSIS package runtime environment and the data sources. Connection managers contain the data source connection string and other related properties. At package execution time the connection managers manage the physical connectivity to data sources and destinations. Multiple tasks can share the same connection manager. In fact, multiple tasks can use the same connection to the database. On the other hand, we could also create a separate connection manager to force each task to use a dedicated connection.

References

http://msdn.microsoft.com/en-us/library/ms174181(v=sql.105).aspx

Hope you like it

Posted by: MR. JOYDEEP DAS

All about ROUNDING

Introduction

ROUNDING is a very important factor in Mathematical calculations. In MS SQL Server we offend use different function to round the mathematical data. I saw that developer face a common problem when rounding data. It may be mathematical rounding or financial rounding. The main cause of facing problem, it is not to shame to say a lot of developer doesn't have any idea about rounding and how it works. In this article I am trying to clear all the problem related to rounding. Hope you like it and find it useful.

Tropics to discuss

1.    What the Logic Behind Rounding

2.    MS SQL Server and Rounding

3.    Financial Rounding

What the Logic Behind Rounding

As I am not a mathematician, so I follow some simple rules for mathematical rounding.

1.    If the last digit is less than 5 – you must drop it.

2.    If the last digit is greeter then 5 – you must drop it and increase the second to last digit by 1.

3.    If the last digit is equal to 5 -- drop the five and increase the second to last digit by one if it is odd, and do not increase the second to the last digit if it is even.

If we take an example

1.    Rounding 12.71   =   12.7

2.    Rounding 12.79   =   12.8

3.    Rounding 12.75   =   12.8   and Rounding 12.85  =  12.8  

MS SQL Server and Rounding

In MS SQL Server the rounding function depends on the data type.

Depends upon the data type (Int, float, decimal etc) the rounding value may be different. The MS SQL Server provide 3 types of rounding functions ROUND(), CEILING() and FLOOR() and depends on the function the value is different.

ROUND()

It returns a numeric value and round up to specified length or precisions.

The syntax is

ROUND ( numeric_expression , length [ ,function ] )

Numaric_Expressions

This can be positive or negative numbers and data type can be Int, decimal, numeric, money or small money.

If the length is an negative number in this case

Example	Result
ROUND(748.58, -1)	750
ROUND(748.58, -2)	700
ROUND(748.58, -3)	Numeric Over flow

In this Example of ROUND() the Last digit is always estimated

Example	Result
ROUND(123.9994, 3)	123.9990
ROUND(123.9995, 3)	123.0000

In this Example of ROUND() Showing approximations

Example	Result
ROUND(123.4545, 2)	123.4500
ROUND(123.45, -2)	100.00

In this Example of ROUND() Showing round and truncation

Example	Result
ROUND(150.75, 0)	151.00
ROUND(150.75, 0, 1)	150.00

CEILING()

Evaluates the value on the right side of the decimal and returns the smallest integer greater than, or equal to, the specified numeric expression. It accepts one value (Value to round).

Example:

SELECT CEILING(123.45) AS 'CEILING(123.45)',

       CEILING(-123.45)AS 'CEILING(-123.45)',

       CEILING(0.0)    AS 'CEILING(0.0)'

CEILING(123.45)       CEILING(-123.45)      CEILING(0.0)

124                              -123                             0

FLOOR()

Evaluates the value on the right side of the decimal and returns the largest integer less than or equal to the specified numeric expression and accepts one value (Value to round).

SELECT FLOOR(123.45) AS 'FLOOR(123.45)',

       FLOOR(-123.45)AS 'FLOOR(-123.45)',

       FLOOR(0.0)    AS 'FLOOR(0.0)'

FLOOR(123.45)         FLOOR(-123.45)        FLOOR(0.0)

123                              -124                             0

Financial Rounding

If I have a fanatical report showing

Srl. No	Description	Amount in Rs
1	Item-1	1000.15
2	Item-2	1000.25
3	Item-3	1000.35
4	Item-4	1000.45
Total		4001.20

If you say a customer to pay Rs. 4001.20 you definitely get some …..

So it should be rounded up to Rs. 4001.00. In another example if the grand total is Rs. 4001.71, it should be rounded up to Rs. 4002.00.

To get this type of rounding we can make an UDF of MS SQL Server. Here we are taking a concept of Round off (+) or Round off (-).

IF OBJECT_ID('dbo.fn_finantialRound') IS NOT NULL

   BEGIN

     DROP FUNCTION dbo.fn_finantialRound;

   END

GO

CREATE FUNCTION fn_finantialRound

(

      @p_RoundNumber DECIMAL(20,2)

)

RETURNS DECIMAL

AS

BEGIN

      DECLARE @v_RerVal   DECIMAL(20,2),

              @v_RndZero  DECIMAL(20,2),

              @v_OrgNo    DECIMAL(20,2),

              @v_StoreVal DECIMAL(20,2);

     

      SET @v_OrgNo    = @p_RoundNumber;

      SET @v_RndZero  = ROUND(@p_RoundNumber, 0);

      SET @v_StoreVal = @v_RndZero - @v_OrgNo;

     

      IF @v_StoreVal > 0

         BEGIN

            SET @v_RerVal = @v_OrgNo + @v_StoreVal;

         END

      ELSE IF @v_StoreVal < 0    

              BEGIN

                 SET @v_RerVal = @v_OrgNo - ABS(@v_StoreVal);

              END

           ELSE

              BEGIN

                 SET @v_RerVal = @p_RoundNumber;

              END  

     

      RETURN @v_RerVal;

END

GO

-- Testing

SELECT dbo.fn_finantialRound(12.27) -- 12.00

SELECT dbo.fn_finantialRound(12.77) -- 13.00

  

Hope you like it.

Posted by: MR. JOYDEEP DAS

WITH TIES Clause

Introductions 

If we need TOP n records from a table with ORDER BY a columns we are using this syntax

SELECT    TOP n [Column Name 1], [Column Name 2] ...

FROM      [Table Name]

ORDER BY  [Column Name]

But we have some problem with this.

So What's the Problem

If the Table have the multiple records with same value. Only one value is selected in this case.

To understand it properly let's take an example

Step-1 [ Create The Base Table ]

-- The Base Table

IF OBJECT_ID('tbl_Example') IS NOT NULL

   BEGIN

     DROP TABLE tbl_Example;

   END

GO  

CREATE TABLE tbl_Example

       (CategoryName   VARCHAR(50)   NOT NULL,

        CategoryValue  DECIMAL(10,0) NOT NULL);

GO

Step-2 [ Insert Some records ]

INSERT INTO tbl_Example

       (CategoryName,  CategoryValue)

VALUES ('CAT-1',  10),             

       ('CAT-2',  10),

       ('CAT-3',  10),

       ('CAT-4',  20),

       ('CAT-5',  20),

       ('CAT-6',  30),

       ('CAT-7',  30),

       ('CAT-8',  30),

       ('CAT-9',  30),

       ('CAT-10', 30),

       ('CAT-11', 40)

Step-3 [ Now make the Query with TOP 1 ]

SELECT  TOP 1 CategoryName,  CategoryValue

FROM    tbl_Example 

ORDER BY CategoryValue

Output:

CategoryName          CategoryValue

CAT-2                         10

If we look at the output only one record is selected. But Category Value 10 is exists for 3 records in the base table.

How to Solve it

To solve this problem we use WITH TIES after TOP n clause.

SELECT  TOP 1 WITH TIES CategoryName,  CategoryValue

FROM    tbl_Example          

ORDER BY CategoryValue

Now look at the output:

CategoryName          CategoryValue

CAT-1                         10

CAT-2                         10

CAT-3                         10

Now all the three records will display.

Caution

WITH TIES is used with ORDER BY clause. If we don't use the ORDER BY clause an error is generated.

Msg 1062, Level 15, State 1, Line 2

The TOP N WITH TIES clause is not allowed without a corresponding ORDER BY clause.

Hope you like it.

Posted by: MR. JOYDEEP DAS

NULL effects with IN and NOT IN Clause

Introduction

Treatment of NULL values is an important factor when we are writing query. Sometimes it provide vigorous output if we don't treat NULL. Here in this article I am going to explain a particular scenario with NULL effects.

Scenario

-- Query-1

SELECT 'OK' AS RESULT WHERE 10 IN(1, 2, 4, 10,NULL);

-- Query-2

SELECT 'OK' AS RESULT WHERE 10 NOT IN(1, 2, 4, NULL);

Consider the Query-1 WHERE conditions. It provides the proper output. But in Query-2 not provide any output.

Reason

If we have a closer look, the Query-1 is actually.

 SELECT 'OK' AS RESULT WHERE 10 = 1

                            OR 10 = 2

                            OR 10 = 4

                            OR 10 = 10

                            OR 10 = NULL;

In WHERE condition we find OR operators, so it works fine for 10 = 10.

But for Query-2

SELECT 'OK' AS RESULT WHERE 10 <> 1

                            AND 10 <> 2

                            AND 10 <> 4

                            AND 10 <> NULL; 

Here we find the AND operator and 10 is comparing with NULL. Here NULL is UNKNOWN.

ANSI_NULLS ON|OFF

When this is on, any query that compares a value with a null returns a 0. When off, any query that compares a value with a null returns a null value.

What MSDN says

"When SET ANSI_NULLS is ON, a SELECT statement that uses WHERE column_name = NULL returns zero rows even if there are null values in column_name. A SELECT statement that uses WHERE column_name <> NULL returns zero rows even if there are nonnull values in column_name.

When SET ANSI_NULLS is OFF, the Equals (=) and Not Equal To (<>) comparison operators do not follow the ISO standard. A SELECT statement that uses WHERE column_name =NULL returns the rows that have null values in column_name. A SELECT statement that uses WHERE column_name <> NULL returns the rows that have nonnull values in the column. Also, a SELECT statement that uses WHERE column_name <> XYZ_value returns all rows that are not XYZ_value and that are not NULL."

By default the ANSI_NULLS is ON. Now we make it OFF and see the result set of Query-1 and Query-2.

SET ANSI_NULLS OFF

GO

-- Query-1

SELECT 'OK' AS RESULT WHERE 10 IN(1, 2, 4, 10,NULL);

GO

-- Query-2

SELECT 'OK' AS RESULT WHERE 10 NOT IN(1, 2, 4, NULL);

GO

SET ANSI_NULLS ON

GO

Here in this case both the query is returning the same output.

Finding NULL Values

To find NULL values we can use

SELECT * FROM table_name WHERE columns_name IS NULL;

But if ANSI_NULLS is OFF we can write this also

SET ANSI_NULLS OFF                           

SELECT * FROM table_name WHERE columns_name = NULL;

Or by treating NULL values we can write this

SELECT * FROM table_name

WHERE ISNULL(columns_name, '') = '';

Hope you like it.

Posted by: MR. JOYDEEP DAS