Very good challenge from msdn forum

Challenge

Nice article about partitioning

Table Partitioning

Physical Joins

Nice articles from msdn blogs. Credits to Craig Freedman

Nested Loop Joins


Merge Joins


Hash Joins

SQL 2016

SQL Server 2016 preview is out. It has lot of new features

http://www.microsoft.com/en-us/server-cloud/products/sql-server-2016/

Fibonacci series with T-SQL

DECLARE @intStart       INT            = 0

DECLARE @intEnd         INT            = 1000 --change this to what ever fibonacci series you want. Make sure the results fit in integer datatype. if not change them to BIGINT or DECIMAL/FLOAT

DECLARE @vchFinalOutPut VARCHAR(MAX)   = ' '

DECLARE @intResult      INT            = 0

DECLARE @intNextValue   INT            = 1

WHILE @intStart < @intEnd

BEGIN

   SET @vchFinalOutPut = @vchFinalOutPut + ',' + LTRIM(RTRIM(STR(@intStart)))

   SET @intResult = @intStart --Store the start value

   SET @intStart = @intNextValue

   SET @intNextValue = @intResult + @intNextValue --Add previous and current value

END

PRINT LTRIM(RTRIM(STUFF(@vchFinalOutPut,PATINDEX('%,%', @vchFinalOutPut),1,'')))

RIGHT functionality without using builtin RIGHT function

CREATE FUNCTION dbo.RightValues

(

   @InputString   NVARCHAR(MAX)

   ,@RightValues  INT

)

RETURNS NVARCHAR(MAX)

AS

BEGIN

   DECLARE  @Count   INT = 0

            ,@Len    INT

            ,@Output NVARCHAR(MAX) =''

   SET @Len = LEN(@InputString)

   IF @Len < @RightValues

   BEGIN

      RETURN NULL

   END

   ELSE

   BEGIN

      WHILE (@Count < @RightValues)

      BEGIN

         SET @Output = SUBSTRING(@InputString, @Len-@Count,1) + @Output

         SET @Count = @Count + 1

      END

   END

   RETURN @Output

END

SELECT dbo.RightValues('abc',4)

Remove bad characters from a string

This simple CTE can be used to remove bad characters in string. It will take string and allowed characters and returns neat string. Change the @AllowedPattern as you wish and check the output string. For example if you have a string abc@#$()123 and you want a string abc123, Set allowedPattern to '0-9a-z. With little tweak this can be converted to a function.





DECLARE @String VARCHAR(8000),
@AllowedPattern VARCHAR(100),
@Holder VARCHAR(8000)
SET @String = '!@#$% 123 ??abc##rd,,,'
SET @AllowedPattern = '0-9a-z'
SET @Holder = ''

;WITH t1 AS (SELECT 1 N UNION ALL SELECT 1 N),
t2 AS (SELECT 1 N FROM t1 x, t1 y),
t3 AS (SELECT 1 N FROM t2 x, t2 y),
t4 AS (SELECT 1 N FROM t3 x, t3 y),
tally AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS num
          FROM t4 x, t4 y)
SELECT @Holder = @Holder + SUBSTRING(@String,num,1)
FROM tally
WHERE num <= LEN(@String)
AND PATINDEX('['+@AllowedPattern+']',SUBSTRING(@String,num,1)) = 1

SELECT @Holder