Daily Archives: Sep 15, 2014


COUNT(star) vs. COUNT(ColumnReference)

The difference is COUNT(*) includes NULL values while COUNT(column_reference) doesn’t. As an example consider the two below queries for Northwind database that find the number of orders placed by each Northwind customer:
WRONG:
SELECT C.CompanyName, COUNT(*) AS NoOfOrders
FROM dbo.Customers AS C LEFT OUTER JOIN dbo.Orders AS O
ON C.CustomerID = O.CustomerID
GROUP BY C.CompanyName
ORDER BY NoOfOrders
RIGHT:
SELECT C.CompanyName, COUNT(O.OrderId) AS NoOfOrders
FROM dbo.Customers AS C LEFT OUTER JOIN dbo.Orders AS O
ON C.CustomerID = O.CustomerID
GROUP BY C.CompanyName
ORDER BY NoOfOrders
More

SQL Server 2012 Multidimensional vs tabular

Which model should you use, Multidimensional vs Tabular?

Well, there is no clear-cut answer, but there are some factors that can make you choose one over the other:

  • If you want to use DAX, you have to use Tabular
  • If you want to use Power View, you have to use Tabular
  • If your dataset is extremely large, go with Multidimensional
  • If you need writeback support, you have to use Multidimensional
  • If you need access to many different external data sources, choose Tabular
  • If you need complex calculations, scoping, and named sets, choose Multidimensional
  • If you need extreme speed and consistently fast query time, choose Tabular
  • If you need Many-to-Many relationships, choose Multidimensional (can be done in Tabular but difficult)
  • If your solution requires complex modeling, choose Multidimensional
  • If you need any of the following features, you must use Multidimensional: Actions, Custom Assemblies, Custom Rollups, Custom Drillthrough Actions (but BIDS Helper
More