Wednesday, 3 December 2014

Fix a Problem with Aliasing

We are aware of aliasing in SQL Server , but its impact is huge.

You are presented with another grouped query that fails, this time because of
an aliasing problem. As in the first exercise, you are provided with instructions on how to fix
the query.
              1. Clear the query window, type the following query, and execute it.
                   SELECT OrgID, SUM(SaleOrders) AS SaleOrders
                   FROM Sales.Orders
                   WHERE  SaleOrders > 20000
                   GROUP BY OrgID;

The query was supposed to return only shippers for whom the SaleOrders value is
greater than 20,000, but instead it returns an empty set. Try to identify the problem in
the query.

2. Remember that the WHERE filtering clause is evaluated per row—not per group. The
query filters individual orders with a freight value greater than 20,000, and there are
none. To correct the query, you need to apply the filter per each shipper group—not
per each order. You need to filter the total of all freight values per shipper. This can be
achieved by using the HAVING filter. You try to fix the problem by using the following
              SELECT OrgID,  SUM(SaleOrders) AS TotalSaleOrders
              FROM Sales.Orders
             GROUP BY OrgID
             HAVING TotalSaleOrders > 20000
But this query also fails. Try to identify why it fails and what needs to be revised to
achieve the desired result

The problem now is that the query attempts to refer in the HAVING clause to the alias
totalfreight, which is defined in the SELECT clause. The HAVING clause is evaluated
before the SELECT clause, and therefore, the column alias isn’t visible to it. To fix the
problem, you need to refer to the expression SUM(freight) in the HAVING clause, as

SELECT Orgid, SUM(SaleOrders) AS TotalSaleOrders
FROM Sales.Orders
HAVING SUM(SaleOrders) > 20000.00

[Note : Reference - ]

Sunday, 20 July 2014

Show animated wait window ,while processing request

Its a major problem faced while processing DB request else functions etc .

The problem is when we perform command on database it takes time of few seconds, at that time my applications main window becomes halted.
when i performs command on database execution returns on main application when whole command is executed on database.
Just Like "NonQuery()"
Lets assume we are working on 4.0 and sending request to view.  i am showing a form before it and closing that form after the command, but problem is i am putting a GIF image on the form, because exection is halted on main application, the gif don't shows animation until the command is executed.
so that i putted it on a BackGroundWorker, but main thread is halted so its child tread also become halt.
we can not put database transaction in background worker, becuase sometime working is depended on result retrieved from database, and we want to halt application untill data is not fetched, but in this case also we want that on wait form, the gif image should show its animation.
What to do in this case, and how to do ?
For that no need of large block of code.
<asp:UpdateProgress ID="updateProgress1" runat="server">'
       <div id="progressBackgroundFilter">
           <div id="processMessage" style="text-align: center">
            <asp:Image ID="imgLoader" runat="server" ImageUrl="~/Images/loader.gif" />    <br />
Same problem I faced while processing request from Windows application.
That time , we normally used Thread class and its methods
Thread query = new Thread(() => Method("Method", "Parameters", Blah));
  query.Start();    //start your animation
  while (query.IsAlive)   {
    Thread.Sleep(10);    }
I don't have any idea about  BackgroundWorker  in windows form. But you can try with it.

Saturday, 12 July 2014

Dynamic query in SQL

Dynamic query which nothing but conditional query which help to optimize query and improve performance of query.

Currently using  [AdventureWorksLT2008R2] datbase as following :

Try first

EXEC ('SELECT * FROM SalesLT.Product')

You can try following just for idea

DECLARE @SQL NVARCHAR(max), @ParmDefinition NVARCHAR(1024)
DECLARE @ListPrice money = 2000.0, @LastProduct varchar(64)
SET @SQL =       N'SELECT @pLastProduct = max(Name)
                   FROM SalesLT.Product
                   WHERE ListPrice >= @param1'
SET @ParmDefinition = N'@param1 money,
                        @pLastProduct varchar(64) OUTPUT'
EXECUTE sp_executeSQL      -- Dynamic T-SQL
            @param1 = @ListPrice,
            @pLastProduct=@LastProduct OUTPUT
SELECT [ListPrice >=]=@ListPrice, LastProduct=@LastProduct

Tips :

If you tried it, then also tried for Temp Table and Table variable for knowledge.

Query :
1)  DECLARE @T AS MyTable;

     INSERT INTO @T VALUES (1,2), (2,3)


     EXEC sp_executesql   N'SELECT *  FROM @T'

Error :  Declare Scalar variable @T

2)   Use  TYPE feature of SQL Server  for Table variable . Refer following query to execute sql statement with table variable

Foo int,
Bar int


INSERT INTO @T VALUES (1,2), (2,3)


EXEC sp_executesql
 N'@T MyTable READONLY',


Saturday, 5 July 2014

Paging in SQL Server

Paging is one the functionality which we can achieved in many ways. So many plugins,technologies used for development.

My sir already used in 2008, I think over to tried it in by different way. It found that Microsoft provides pagination feature with SQL Server 2012. We can acheived it by row_number() and minimum ids etc But from SQL Server 2012 we can do pagination by using FETCH and OFFSET.

From above records, I need to split next 6 records after 32 Address ID.
So doing pagination is a two-step process: -
  • First mark the start of the row by using “OFFSET” command.
  • Second specify how many rows you want to fetch by using “FETCH” command.

Query : 
1. Fetching records from 0 to above

SELECT * FROM [Address] 
order by AddressID
offset 0 rows – start from zero
Fetch Next 6 Rows only

2. Fetching records from 32 to next 6 records paging.

SELECT * FROM [Address] 
order by AddressID
offset 6 rows 
Fetch Next 6 Rows only

[Still reading regarding same... but you can start reading regarding FETCH and OFFSET ]

NIHAR'S BLOG: Sequence : Auto Generated ID's in SQL Server 2012

NIHAR'S BLOG: Sequence : Auto Generated ID's in SQL Server 2012: Hi friends , after long time come back with new excellent features of SQL Server 2012. Don't worry , I am not posting all features bec...

Reading text file in SQL Server

There are many option to read text file data and store it into database. We may have following options available for reading/storing/updating data into database
1.  Import and Export wizard of SQL Serve
2. SQL Server Integration Services
3. Using C# with SQL CLR will insert data into tables
But , using few lines code of query need to execute in SSMS.
SQL Server provides "BULK INSERT" Operation to read data from local system and store it into database tables.
Steps :
1. First create text file with some name "Abc.txt" and enter values as
Save and Copy path of file.
2. Open SSMS and create temp table for data insertion
Create table temp_Data
name nvarchar(20)
3.  Use following query to view operation
BULKINSERT Temp_dataFROM  'D:\Abc.txt'WITH(
=',',     -- Here you can use any delimiter for seperation
='\n'  );
SELECT * FROM Temp_data
o/P :  3 Rows Affected
Here some arguments commonly used in BULK INSERT

Sunday, 6 April 2014

Packages in PL/SQL

Brief :
       "Packages" word only come up with Oracle PL/SQL.

As Microsoft fan , googling about " Packages in SQL Server "..
Actually their will be no dependency on that ..(LOLzz)

We are working on huge database , or rapid increases of Stored procedures , functions etc (proliferation) .
It will be difficult to manage it.
Packages can save time to describe procedure / function schema.

What is Packages in Oracle ?

If you observed any document related to Packages , let you know about "Interface in OOP" and if you search packages in SQL Server,you will get references related to SSIS , Data flow , control flow  etc.
(I am also beginner to all this..)
Some advantages tells that , why you need it ?

I found packages to be really useful for a number of reasons:
  1. All the procedures and functions relating to a specfic sub-system are in one program unit. This is just good design practice but it's also easier to manage, e.g. in source control.(We can said it to be Cohesion)
  2. Constants, sub-types and other useful things: there's more to PL/SQL than stored procedures. Anything we can define in a package spec can be shared with other programs, for instance user-defined exceptions.
  3. Overloading: the ability to define a procedure or function with the same name but different signatures.
  4. Security: defining private procedures in the package body which can only be used by the package because they aren't exposed in the specification.
  5. Sharing common code: another benefit of private procedures.
  6. We only need to grant EXECUTE on a package rather than on several procedures.

While SQL Server has nothing to offer by way of the "cool features" of encapsulation and package state like you are used to, you can organize your stored procedures into schemas.

In enterprise manager, these procs are still all listed together which makes for a HUGE treelist if you have hundreds of procs. I too miss the organization and cool features of Oracle packages. However, all platforms have their strengths.if we 

[note : Please correct us, if wrong somewhere]
Reference link for Oracle Packages