Facts

*** Can't nest aggregate function - error

Select Y,Q, Max(Sum) as MaxSum
FROM ( -- Calculate Sums
SELECT Category, Year(SalesDate) as Y, DatePart(q,SalesDate) as Q, max(Sum(Amount)) as Sum
FROM RawData
GROUP BY Category, Year(SalesDate), DatePart(q,SalesDate)
) sq
GROUP BY Y,Q
ORDER BY Y,Q

*** -- Solution: Including Detail description

SELECT MaxQuery.Y, MaxQuery.Q, AllQuery.Category, MaxQuery.MaxSum as sales
FROM (-- Find Max Sum Per Year/Quarter
Select Y,Q, Max(Sum) as MaxSum
FROM ( -- Calculate Sums
SELECT Category, Year(SalesDate) as Y, DatePart(q,SalesDate) as Q, Sum(Amount) as Sum
FROM RawData
GROUP BY Category, Year(SalesDate), DatePart(q,SalesDate)
) sq
GROUP BY Y,Q
) MaxQuery
JOIN (-- All Amount Query
SELECT Category, Year(SalesDate) as Y, DatePart(q,SalesDate) as Q, Sum(Amount) as Sum
FROM RawData
GROUP BY Category, Year(SalesDate), DatePart(q,SalesDate)
)AllQuery
ON MaxQuery.Y = AllQuery.Y
AND MaxQuery.Q = AllQuery.Q
AND MaxQuery.MaxSum = AllQuery.Sum
ORDER BY MaxQuery.Y, MaxQuery.Q

Read Users' Comments (0)

Group All

looks like left outer join

Read Users' Comments (0)

Using Statistical functions & how affect null values when calculating Avg

Read Users' Comments (0)

Using the New Windowing Functions

1. SELECT ProductID, TransactionDate,
Row_Number() OVER(ORDER BY TransactionDate) as RowNumDate,
Row_Number() OVER(ORDER BY ProductID) + 5000 as RowNumiD
FROM Production.TransactionHistory

-- not permitted
Where Row_Number() OVER(ORDER BY TransactionDate) Between 10001 AND 20000
ORDER BY TransactionDate;

New feature in sql 2005, here, using Row_Number in where close is not permitted.

2. So if we want to use mid range of rownumbers use this way

-- Select mid-range of rownumbers & every 5th row
SELECT RowNum, ProductID, TransactionDate
FROM (SELECT ProductID, TransactionDate,
Row_Number() OVER(ORDER BY TransactionDate) as RowNum
FROM Production.TransactionHistory) sq
WHERE RowNum Between 10001 AND 20000
-- AND RowNum%5 = 0
ORDER BY RowNum ;

In here use sub query as a table and get back the result set

3. Ranking and Windowing - these are very helpful when we are reporting

SELECT SalesOrderId, CustomerId, TerritoryID,
ROW_NUMBER() OVER(ORDER BY CustomerId) AS RowNum,
RANK() OVER(ORDER BY CustomerId) AS [Rank],
DENSE_RANK() OVER(ORDER BY CustomerId) AS [DenseRank],
NTILE(5) OVER(ORDER BY CustomerId) AS NTile5,
RANK() OVER(PARTITION BY TerritoryID ORDER BY CustomerId) AS [Partition]
FROM Sales.SalesOrderHeader;

4. Sample Data - randomly select data


SELECT
SalesOrderId, CustomerId
FROM Sales.SalesOrderHeader TABLESAMPLE (10000 rows)

return random 8000, 11400, 12000 rows. it's not a bug at all and here this method is more powerful than Select Top 1000 way.

SELECT
SalesOrderId, CustomerId
FROM Sales.SalesOrderHeader TABLESAMPLE (10 percent)
ORDER BY CustomerId






Read Users' Comments (0)

Working with Correlated Subqueries

Read Users' Comments (0)

Common table expression

This is new feature to Sql Server 2005

Why we should use Common table expression :-

1. Can use multiple sub queries in user friendly manner
2. Avoid complexity
3. Initially define the sub query and after call this sub query again and again in base query multiple time like a table name

Read Users' Comments (0)

Best Practices in MVC - Performance aspect also

1. DO consider using asynchronous controllers for long running requests.

ASP.NET’s threading pool has a default limit of 12 concurrent worker threads per CPU. When the requests overload the server’s ability to process these requests, a queue is built up of requests. For example, any request which takes a considerable amount of time waiting for external resources, such as database or large file operations. These external requests block the thread they occupy for the entire wait period. When this queue gets too large (5000 requests pending), the server starts responding with 503 (server too busy) errors.

In ASP.NET 4 the number of concurrent threads is set by default to 5000. While it is possible to increase the default limits, there is a better way to mitigate long running requests from tying up threads, modifying the long running requests to run asynchronously. ASP.NET MVC enables you to implement asynchronous controllers for this purpose. For more information about how to implement an asynchronous controller, see Using an Asynchronous Controller in ASP.NET MVC.

2. DO use an OutputCache filter for static pages.

Use OutputCache attribute when you are returning less frequently updated data; a good candidate may be your home page. You can use this technique for both HTML and JSON data types. When using it, only specify the cache profile name; do not specify anything else. If you need to fine tune caching, use the output cache section of the Web.config file.

For example, the OutputCache attribute is attached to Dashboard action method in the following code.

[AcceptVerbs(HttpVerbs.Get), OutputCache(CacheProfile = "Dashboard")]
public ActionResult Dashboard(string userName, StoryListTab tab, OrderBy orderBy, int? page)
{
...
}
In the Web.config file, the duration is fine tuned to 15 seconds.

3. DON'T overuse session, instead use TempData for short lived (intra-request) storage.

4. DO consider partial page updates using AJAX for reducing bandwidth.

5. Performance Recommendations

Performance is a multi-faceted problem for web-sites, as a myriad of bottlenecks can affect performance including:

Database
--------

Inefficient queries
Incorrectly placed indexes
Non-normalized design

Bandwidth problems
------------------

Large request size (affected by individual large images, .css, .js, .html, etc.)
Content that references many other items, such as multiple script, CSS, or image files
Slow connection speed

Processing power
----------------

Server: expensive operations
Client: poorly written javascript


6. DO use ASP.NET special resource folders and resource files.

While writing your web pages add an ASP.NET project folder for globalized content (App_GlobalResources) and for localized content for a given view (App_LocalResources). In each of these folders, you should add a resource (.resx) file that you should name according to the controller name. In other words, if your controller is named SubmissionPipeline, the resource file should be named SubmissionPipeline.resx.

7. Localization and Globalization Recommendations

Globalization is the process of making a product multi-lingual, where localization is the process of adapting a global product for a particular language and country. To develop a web application that supports globalization and localization, keep at least one rule in mind. Do not use hard-code strings in views.


8. DO guard against common attack vectors.

Website security needs to concern all web developers writing enterprise class websites and services. There are a host of well known attack vectors that you should know about. These attack vectors include (but are not limited to):

Cross-site scripting (XSS) attacks
SQL injection
Cross-site Request Forgery (XSRF)
Improperly implementing model binding

Read Users' Comments (0)

Complex Join

Explain using family twin scenario:

These are the conditions(in dbo.Person table- Self join with multiple conditions) -
1. different personID so have to use not equal both two person ids
2. twins mother and father same
3. and date of birth not equal to null


Read Users' Comments (0)

Self Join - Using in hierarchy scenarios

Join with the same table again and again recursively. Following images illustrate this joining concept using family scenario.




Read Users' Comments (0)

Cross Join

Read Users' Comments (1)comments

Select Top Vs Select Top 5 WITH TIES

Read Users' Comments (0)

Working with Nulls & Coalesce

we cannot use (=) to evaluate some value to null

Have to use IS NULL


**** Coalesce Function ***

Ex :

Select coalesce(null, 1+null, 1+2, 'xx') -> output=3

Read Users' Comments (0)