Intellisense is one of the most frequently used features of SQL Server in the development community – so much so that if Intellisense stops working, work comes to a grinding halt and frantic calls to the IT team are made.

This post documents the steps, in order of most common occurrence (in my experience) that can be taken when faced with an issue that relates to Intellisense.

Assumption: The user is attempting to type in code in the code block, i.e. not in the comments block or string literals.

1. Confirm that Intellisense is enabled (it may have been accidentally turned off due to “happy clicking”)
   
   
   
   • Refer: Enable/Disable Intelli-sense [by Madhivanan (Blog)]: http://beyondrelational.com/modules/2/blogs/70/posts/10965/exploring-ssms-enabledisable-intellisense.aspx
   
   
2. Refresh the local cache
   
   
   
   • Often, when workstations (especially laptops) are hibernated and awaken again, newer objects are created or when connections in the Query editor are changed, Intellisense may stop working or take a while to refresh
     
   • To manually refresh the cache, press (Ctrl + Shift + R) or go to Edit –> Intellisense –> Refresh Local Cache
   
   
3. Is the SSMS connecting to a SQL Server running a lower version? (e.g. SSMS for SQL 2012 connecting to SQL 2008 R2/2005 or lower) If yes, SSMS would not work
   
4. Review the code – does an error exist such that the queries in the particular query editor cannot be parsed?
   
5. After Visual Studio 2010 SP1 is installed on a workstation with SQL Server 2008 R2 (any release from RTM to CU6), the Intelli-sense feature in the SQL server Management Studio (SSMS) may stop working
   
   
   
   • This is a  known, documented issue with Microsoft (KB2531482)and the fix is quite simple – deploy the latest service pack/cumulative update of SQL Server 2008 R2

While Intellisense kicks back to life in most cases after the above steps are performed, it is still possible that it may cease to work in rare cases. The last step in this case is to reinstall the SQL Server client tools.

Further Reading:

Here are some other posts about Intelli-sense that you may be interested in:

• Enable/Disable Intelli-sense [by Madhivanan (Blog)]: http://beyondrelational.com/modules/2/blogs/70/posts/10965/exploring-ssms-enabledisable-intellisense.aspx
  
• Intelligent Use of Intelli-sense [by Madhivanan (Blog)]: http://beyondrelational.com/modules/2/blogs/70/Posts/18053/intelligent-use-of-intellisense.aspx
  
• Intelli-sense and hyperlinks: http://bit.ly/VR4qbF
  
• Intelli-sense Completion mode: http://bit.ly/VdlXw6
  
• Intelli-sense uses a “LIKE” search from SQL 2012 onwards: http://bit.ly/10HBo23

Until we meet next time,

Be courteous. Drive responsibly.

Today, I do not have a SQL Server blog. Today’s post is about a solution to a problem that I faced recently when working with documents stored on Sharepoint Online.

I had received a new laptop from the IT team and when attempting to edit an Excel file in Excel 2010 directly from Sharepoint, I encountered the following error:

The file is corrupt and cannot be opened.

Root Cause

When a document is opened from the Internet, Office spawns off another program under restricted permissions to open the document. This restricted process will be unable to create temporary files and folders required for Office to open and edit the document.

Solutions

The following solutions can be implemented to resolve the issue:

Solution 01:

If a particular Sharepoint URL is frequently used and trusted, a good alternative would be to Add the URL to the list of Trusted sites in under the browser Options.

Office will trust documents originating from trusted locations and hence these documents will open successfully.

Solution 02:

Follow the steps provided in the Microsoft KB article: http://support.microsoft.com/kb/2387587

Solution 03 (NOT recommended):

Disable Protected Mode (NOT recommended)

1. Go to File -> Options -> Trust Center
   
2. Open the Trust Center window by clicking on the “Trust Center Settings” button
   
3. Navigate to the “Protected View” window
   
4. Uncheck the following checkboxes:
   
   
   
   • Enable Protected View for files originating from the Internet
     
   • Enable Protected View for files located in potentially unsafe locations
     
   • Enable Protected View for Outlook attachments
     
   • Enable Data Execution Prevention mode

While most blogs and forums suggest that protected mode options should be unchecked, I would not recommend this as it reduces the security enforced by Office.

Have you ever encountered this issue? If yes, how did you resolve the error?

Until we meet next time,

Be courteous. Drive responsibly.

I have often seen a recommendation that using DISTINCT instead of GROUP BY is better from a performance perspective. DISTINCT is definitely a more readable alternative, but I wanted to test for myself as to what the truth was.

To compare the performance of the two, I wrote the following simple queries.

The query below helps in identifying the number of records that SQL Server will need to process. On my machine, when executed without the GROUP BY or the DISTINCT clause, the query generates records in excess of 380,000.

USE AdventureWorks2012;

GO

SELECT  COUNT(so1.object_id)

FROM    sys.objects AS so1

        CROSS JOIN sys.objects AS so2 ;

GO

The queries below fetch the same information – one uses the GROUP BY whereas the other query uses a DISTINCT.

USE AdventureWorks2012;

GO

–Clean the buffers and procedure cache

DBCC DROPCLEANBUFFERS;

DBCC FREEPROCCACHE;

GO

–Evaluate GROUP BY

SELECT  so1.object_id,

        so1.name

FROM    sys.objects AS so1

        CROSS JOIN sys.objects AS so2

GROUP BY so1.object_id, so1.name ;

GO
–Clean the buffers and procedure cache

DBCC DROPCLEANBUFFERS;

DBCC FREEPROCCACHE;

GO
–Evaluate DISTINCT

SELECT  DISTINCT

        so1.object_id,

        so1.name

FROM    sys.objects AS so1

        CROSS JOIN sys.objects AS so2 ;

GO

Before executing the two queries (one with the GROUP BY and the other with the DISTINCT), I had turned on the actual execution plan.

Upon studying the execution plan, the following observations can be made:

1. When the SQL server database engine detected that the 2^nd query did not have any aggregation function, it was smart enough to convert the GROUP BY into a DISTINCT when the query plan is generated
   
2. There is no difference in the execution plan across both the queries

In conclusion,

DISTINCT is not faster than GROUP BY. In fact, Both DISTINCT and GROUP BY demonstrate equal performance impact.

When aggregations are not being used, DISTINCT is more readable when compared to GROUP BY.

Until we meet next time,

Be courteous. Drive responsibly.

One of our systems needs to support paging and needs to work on multiple SQL Server versions (SQL 2005 and above). Our solution therefore uses the TOP clause and not the OFFSET…FETCH solution introduced in SQL 2012.

Being a reasonably old system, we also get a lot of enhancement requests. One of the requirements that we received was to change the order in which the elements were returned to the application by the query. As one of the team members was working on the enhancement, he raised an interesting question:

We need to use both the TOP and the ORDER BY clause. Which will be evaluated first?

Here’s the reason why the question is interesting. We know that the ORDER BY is evaluated after the SELECT (after all, how can you order a result set if you haven’t fetched anything into the result set in the first place?). The way a T-SQL query is written the TOP clause is part of the SELECT clause. So, we have two options with respect to the evaluation sequence:

1. Fetch the number/percentage of records based on the value specified in the TOP clause and then order this intermediate result set
2. Fetch all records, order them and then fetch the number/percentage of records based on the value specified in the TOP clause

In each of these options, the results returned by the query will be different. In order to answer this question, I came up with the following quick test:

TOP and ORDER BY

To begin our test, let me create a test table, and insert some records into it. Note that:

1. The Table does not have a clustered index
2. The records being inserted into the test table are out-of-order

USE tempdb;
GO
--Safety Check
IF OBJECT_ID ('tempdb..#OrderingTest','U') IS NOT NULL
    DROP TABLE #OrderingTest;
GO

--Create a test table
CREATE TABLE #OrderingTest (OrderId INT NOT NULL,
                            OrderValue VARCHAR(20)
                           );
GO

--Insert some test data
--Note that records are being inserted out of order
INSERT INTO #OrderingTest (OrderId, OrderValue)
VALUES (10, 'Ten'),
       (5, 'Five'),
       (4, 'Four'),
       (12, 'Twelve'),
       (1, 'One'),
       (8, 'Eight'),
       (2, 'Two'),
       (3, 'Three'),
       (7, 'Seven'),
       (9, 'Nine');
GO

TOP without ORDER BY

First up, let us SELECT TOP 5 records from the table without the ORDER BY clause.

USE tempdb;
GO
--SELECT the TOP 5 records from the table
--without ORDER BY clause
SELECT TOP 5 ot.OrderId, 
             ot.OrderValue
FROM #OrderingTest AS ot;
GO

As can be seen from the result-set, without the ORDER BY clause, no ordering was performed. The records were returned in the physical order in which they were found by the database engine.

TOP with ORDER BY

Now, let us add an ORDER BY clause to the query. First up, we will perform the standard ascending (ASC) ordering.

USE tempdb;
GO
SELECT TOP 5 ot.OrderId, 
             ot.OrderValue
FROM #OrderingTest AS ot
ORDER BY ot.OrderId ASC;
GO

Next, let us change the order to descending by using the DESC keyword:

USE tempdb;
GO
SELECT TOP 5 ot.OrderId, 
             ot.OrderValue
FROM #OrderingTest AS ot
ORDER BY ot.OrderId DESC;
GO

As can be seen from the results, the TOP operation was performed after the ORDER BY, i.e. the results were ordered first, the TOP was performed later.

Adding a clustered index

Finally, let us add a clustered index on the table and then fetch the records without an ORDER BY clause.

USE tempdb;
GO
ALTER TABLE #OrderingTest
    ADD CONSTRAINT pk_OrderingTest 
    PRIMARY KEY CLUSTERED (OrderId ASC);
GO

--Repeat Scenario A
USE tempdb;
GO
SELECT TOP 5 ot.OrderId, ot.OrderValue
FROM #OrderingTest AS ot;
GO

As can be seen from the screenshot above, the results are in a sorted order. The sorting comes from the fact that the database engine used the clustered index to access the table. Because the clustered index arranges records in a logical order, they are accessed in when accessed via the clustered index they are accessed in a specific order.

However, SQL Server does not guarantee the order in which the results are returned, so if that’s the requirement, it is always a good idea to use the ORDER BY clause and sort the results explicitly.

Conclusion

The conclusion quite simply is that

The TOP clause is evaluated after the ORDER BY/sort operation.

If no explicit ordering is performed, SQL Server returns results in the order in which they are physically accessed by the database engine.

Until we meet next time,

Be courteous. Drive responsibly.

The T-SQL language constantly undergoes modifications. Every release bring in something new, something to help develop an easier solution to a common business problem. With Microsoft SQL Server 2012, enhancements have been incorporated to the Windows functions to allow an easier solution to what is commonly referred to as the “Running Total Problem”.

The whole concept of running totals is that as the transactions continue to be added, the system maintains the total of a given sequence. This allows the system to fetch this summary without having to maintain any record of the individual transactions itself.

Here’s an example based on the transactions for one of the SalesOrders in the AdventureWorks2012 sample database.

SalesOrderId	UnitPrice	OrderQty	Running Total	Running Total Formula
43659	2024.994	1	2024.994	(Unit Price * Order Quantity)
43659	2024.994	3	8099.976	Total from previous line + (Unit Price * Order Quantity)
43659	2024.994	1	10124.97	(same as above)
43659	2039.994	1	12164.964	(same as above)
43659	2039.994	1	14204.958	(same as above)
43659	2039.994	2	18284.946	(same as above)
43659	2039.994	1	20324.94	(same as above)
43659	28.8404	3	20411.4612	(same as above)
43659	28.8404	1	20440.3016	(same as above)
43659	5.7	6	20474.5016	(same as above)
43659	5.1865	2	20484.8746	(same as above)
43659	20.1865	4	20565.6206	(same as above)

Essentially, the running total at each row is the sum of the running total value from the previous row and the expression to be added from the current row (in this case, Unit Price * Order Quantity). That’s where the problems start in SQL Server.

The running total is calculated in a sequence and unfortunately, SQL Server does not guarantee the order of records in a result set which makes it difficult to compute the running total in a SET based operation. The solution to this problem has therefore been a CURSOR operation where each record is independently processed in a loop.

However, the T-SQL enhancements made in SQL Server 2012 have a possible solution to this problem. The the aggregate function SUM() has been enhanced to operate as a windowing function which now supports the ORDER BY clause in the OVER clause. This gives the ability for SUM to be performed in the intended sequence of the transactions. Here’s a query to demonstrate this:

USE AdventureWorks2012;
GO
SELECT SalesOrderID,
       SalesOrderDetailID,
       UnitPrice,
       OrderQty,
       LineTotal,
       SUM(UnitPrice * OrderQty) 
            OVER (PARTITION BY SalesOrderId 
                  ORDER BY SalesOrderDetailID
                 ) 
                 AS RunningTotal
FROM Sales.SalesOrderDetail
WHERE SalesOrderID = 43659;
GO

NOTE: Attempting to run this query against a SQL Server 2008R2 or lower environment will result in an error related to missing support of the ORDER BY clause. They support the use of OVER and PARTITION BY with the SUM() function, but do not support ORDER BY.

The result set available from the query is shown below.

As can be seen from the result set, Microsoft SQL Server auto-calculated the running total for the given Sales Order in a given sequence. These results also match with the table provided above.

Finally, the purpose of a running total is that a system/user should not have to maintain a record of all the transactions. Hence, the maximum value of the running total should match the Sub-total stored on the SalesOrder header record.

USE AdventureWorks2012;
GO
SELECT SalesOrderID,
       SubTotal,
       TaxAmt,
       Freight,
       TotalDue
FROM Sales.SalesOrderHeader
WHERE SalesOrderID = 43659;
GO

Summary

The ORDER BY clause can be used when using SUM() over a window defined by the OVER clause to generate a running total of values in a given sequence.

It is really amazing that the solution to  a problem that has challenged many brains over the years can be this simple. Kudos to the #SQLServer product development team at #Microsoft. Thanks, guys!

Further Reading

• Windowing functions – Underappreciated features of Microsoft SQL Server [Link]
  
• Aggregate Function – SUM [Link]
  
• Over Clause [Link]
  
• Running Totals, Wikipedia [Link]

Until we meet next time,

Be courteous. Drive responsibly.