Results In Random Order

Facebooktwitterredditpinterestlinkedinmail

Although SQL Server does not have a built in way to return your query results in random order, you can accomplish this in an easy way.  One thing to note is that this way of returning the results is very expensive… so it could take a while.

In the following example, the magic happens in the order by clause.  The NEWID() function will generate a unique GUID for every record.  This will cause the results to come back randomized.

Syntax

SELECT FirstName,
       LastName
FROM   NameTable
ORDER  BY NEWID()

Option Recompile

Facebooktwitterredditpinterestlinkedinmail

Option recompile is one of those hidden gems when it comes to increasing stored procedure performance.  When used in the right spot, this can speed up a never ending stored procedure call to instantaneous.  If you don’t know what option recompile is… it’s just a query hint that can be added to the end of a sql query.

What Option Recompile Does

When option recompile is added to the query hints, it does a couple things.  The first thing that it does is it tells SQL Server to discard any stored execution plan for that query.  This will cause SQL Server to rebuild the execution plan for this query.  The next thing it does is the magical part.  When SQL Server goes to build the execution plan, it will replace the local variables with the actual values in those variables.  This allows SQL Server to pull the data SUPER fast.

Symptoms

Keep in mind that many times the slowdown in a stored procedure call is just one statement inside the stored procedure.

The easiest way that I have found to figure out if this will help a slow stored procedure is to decompose the stored procedure in to separate sql statements.  I then run the sql statements to see what one is the slow one.  If I run a statement that is slow and uses a local variable, this may be a good candidate.  Next I replace the local variables in that statement with the explicit values.  If I rerun the statement and it performs fast, I know that this could use option recompile.

Syntax

DECLARE @CustomerName   VARCHAR(250)

SET     @CustomerName = "Tester"

SELECT  CustomerID,
        CustomerName,
        Address
FROM    Customer
WHERE   CustomerName = @CustomerName
OPTION  (RECOMPILE)

How To Format A Number With Commas

Facebooktwitterredditpinterestlinkedinmail

SQL Server 2012 makes it a lot easier to format a number with commas, but let’s face it… previous versions haven’t been so nice to us.  Here are the easiest ways that I know to format a number with commas.

SQL Server 2012+

SELECT	FORMAT(1234567890, '###,###,###') AS 'FormattedNumber'

SQL Server 2008 And Previous

SELECT	REPLACE(CONVERT(VARCHAR(50), (CAST(1234567890 AS money)), 1), '.00', '') AS FormattedNumber