Author : Mayank Tripathi


SQL Server Management Studio shortcuts

Column and block text selection :

Using SHIFT to Select Text –

In this post we will have a look on “SQL Server Shortcut keys”. with some other details. It is well known that using the SHIFT key you can perform normal text selection in SSMS.  If you put your cursor to the left of “Person.Address” and hold the SHIFT key and then put your cursor at the end of “Person.CountryRegion” it will select the first three lines of code as shown below.

SQL Server Management Studio shortcuts

Using SHIFT+ALT to Select Columns

If you would like to select columns or blocks then Microsoft SQL Server offers a solution for you. You can use the key shortcut SHIFT+ALT as described in the following steps. Please note that this feature works using SSMS for SQL Server 2008 and up.

Place your cursor to the left of “[Person].[Address]”, press SHIFT+ALT then click at the end of “Person” … More


Speed up MS SQL Server Reporting Services SSRS on First Run

On first run of SSRS, report rendering is very slow and in this post we will explore “how to speed up SSRS on first run”. The reason behind is that we have to wait for the application pool to spin up. During each start up, the SSRS web service reads and decrypts the rsreportserver.config file, it has to physically open up a socket connection between the two servers since the connection pool is empty, log into the database instance, etc. Also the web service has to make RPC calls into the Windows Service to get the encryption keys. There is an idle timeout value which forces the application to shut down after 20 minutes by default. We can tweak this timeout setting to have the application always up and running.

The solution to resolve this issue is :

You can modify the “RecycleTime” parameter in RSReportServer configuration file for SQL … More


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