SQL Server Management Studio useful hints

IntelliSense Database Schema Refresh for Syntax / Column Validation

When you are writing your query within the query editor and you want to refresh or update your IntelliSense cache which stores the database schema → Ctrl + Shift + R

Show Line Numbers

To enable line numbers in your query editor, go to the menu item Tools > Options

Go to the section Text Editor > Transact-SQL > General, and tick "Line Numbers"

Avoid subqueries or embedded queries

Try to avoid subqueries, embedded queries or multiple layer selects within your SQL. One way around this is to import your intended data into a temporary table or common table expression and then link to that table within your main SQL query.

Common Table Expressions (CTEs) vs Temporary Tables

It is recommended that if you are querying large data tables and you expect your result set to be large you should use Temporary Tables as part of your query. The main reason for this is that CTEs use database server memory, while extremely fast, it can fill up extremely easily if your database server has limited memory resources. Temp Tables however use the file system on the server, which limits its impact on the server memory and results in better performance for large data.

WITH CTE AS (
    SELECT DataField
    FROM Table
)
SELECT DataField FROM CTE;

IF OBJECT_ID('tempdb..#TempTable', 'U') IS NOT NULL DROP TABLE #TempTable;
CREATE TABLE #TempTable (
   ID INT IDENTITY(1,1) PRIMARY KEY,
   DataField varchar(100)
);

INSERT INTO #TempTable (DataField)
	SELECT DataField FROM Table WHERE DataField='Filter';

SELECT DataField FROM #TempTable;

COUNT(*) vs COUNT(1)

To optimise performance on your SQL Query, it is best to use COUNT(1) for counting the number of rows.

For example (Row Count: 61,278,414):

COUNT(*) → 52 seconds
COUNT(1) → 47 seconds

SELECT DISTINCT vs SELECT ... GROUP BY

To optimise performance in removing duplicates in your query, it is best to use SELECT ... GROUP BY

For example (Row Count: 61,278,414):

SELECT DISTINCT → 50 seconds
SELECT ... GROUP BY → 48 seconds

Row Numbers and Row Numbers by Column Value(s)

Using this function and method within your SQL query can ultimately avoid any data import post-processing done by the BI tool, which in turn speeds up the load and responsiveness of your dashboard.

To get a row number for all your data, you can use the below code:

ROW_NUMBER() OVER (ORDER BY [INDEX_COLUMN] ASC) as [Row Number]

To get a row number for parts of your data based on a set of columns and their values, you can use the below code:

ROW_NUMBER() OVER (PARTITION BY [INDEX_COLUMN_2],[INDEX_COLUMN_3] ORDER BY [INDEX_COLUMN_1] DESC) as [Row Number]

Source: ROW_NUMBER (Transact-SQL)