Generate Random value

Wonder how random confirmation numbers are generated? Here is one way to generate. This code will exclude all the special characters and consider values from 0-9 and [a-z]/[A-Z]. But the final result will be all UPPER case characters.


 DECLARE @Lenght INT = 6
 DECLARE @OutPut VARCHAR(10) = ''
 DECLARE @ExcludeList VARCHAR(100) = '~`!@#$%^&*()_-+={[}]|\:;"''?/>.<,'
 DECLARE @CharList CHAR

 WHILE (@Lenght >0)

 BEGIN
        SET @CharList = CHAR(RAND() * 72 + 48)
        IF CHARINDEX(@CharList,@ExcludeList) = 0
        BEGIN
               SELECT @OutPut = @OutPut + @CharList
               SET @Lenght = @Lenght - 1
        END
 END

 SELECT UPPER(@OutPut)

 

 

LEAD() Function in SQL 2012 makes life easy

2012 introduced LEAD Function. Lets take a scenario for example. We have a categorytable which has categoryID and CategoryValue. You are required to show another calculated field called NextCategoryValue which is basically the value from next record. Normally what we do is write a CTE or table expression and get the next value. The output should be as follows

Here is the sample code

IF OBJECT_ID('dbo.Category','U') IS NOT NULL

DROP TABLE dbo.Category
GO
CREATE TABLE Category
(
    CategoryID INT
    ,CategoryValue INT
)
 

INSERT INTO Category(CategoryID, CategoryValue)

VALUES (1,1), (1,2), (1,4), (1,5), (2,8), (2,10), (3,11),(3,13)

--Get the next Category Value

;WITH CTE
AS
(
    SELECT CategoryID
            ,CategoryValue
           ,ROW_NUMBER() OVER(ORDER BY CategoryValue) AS Ranges
    FROM dbo.Category
)
SELECT A.CategoryID
    , A.CategoryValue
    , B.CategoryValue AS NextCategoryValue
FROM
CTE A
LEFT JOIN CTE B
ON A.Ranges + 1 = B.Ranges

We can achieve the same functionality with LEAD() function with very less code and with a huge perf. improvement

--USE LEAD() Function to get next category value

SELECT CategoryID
        ,CategoryValue
        ,LEAD(CategoryValue) OVER(ORDER BY CategoryValue) AS NextCategoryValue
FROM dbo.Category

We can also use PARTITION BY in the OVER clause of lead function if we want to get the values based on categoryID or a key value. Here is the execution plan

This is very useful for date ranges and other stuff.

Calculating the number of occurances of a character/string inside a string

How can we find number of occurances of a particular string in another string? For example in SQLServer, how can we find number of occurances of 'e'? Its a simple but interesting logic

DECLARE @String AS VARCHAR(MAX) = 'SQLServer'
,@SearchString AS VARCHAR(MAX) = 'e'

SELECT (LEN(@String) - LEN(REPLACE(@String,@SearchString,'')))/LEN(@SearchString)

So simple. First take the actual string. Replace the string with nothing by replace function and using search string and divide the whole value with the length of search string.

Find Index fragmentation for each index on a table in a DB

The following query will give out the fragmentation percentage for each index on a table in DB.


SELECT DISTINCT * FROM( SELECT  DB_NAME(ps.database_id) AS DBName
,OBJECT_NAME(ps.OBJECT_ID) AS TabeName
,ps.index_id
,b.name
,b.type_desc 
,ps.avg_fragmentation_in_percent
      FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS ps
     INNER JOIN sys.indexes AS b 
     ON ps.OBJECT_ID = b.OBJECT_ID
    AND ps.index_id         =  b.index_id
    WHERE ps.database_id = DB_ID()
          )A
ORDER BY a.avg_fragmentation_in_percent DESC

SQL SERVER Architecture

Very good article about datacompression

http://msdn.microsoft.com/en-us/library/dd894051(v=sql.100).aspx

Handling Deadlocks

"A deadlock occurs when two or more tasks permanently block each other by each task having a lock on a resource which the other tasks are trying to lock [msdn]" 


When SQL Server detects a deadlock between two tasks, it will terminate one of the tasks. We don't know which task would be terminated by SQL Server. It will have its own computations. What if we don't want one of the tasks to be a victim of dead lock? (Victim is the task that will be terminated by SQL Server). The following steps helps to prioritize the deadlock. There is a keyword

SET DEADLOCK_PRIORITY. We can use this property at the start of transaction or a task. It takes in following values. LOW/NORMAL/HIGH/ -10 to 10

Syntax: SET DEADLOCK_PRIORITY HIGH

Consider there are two tasks/Connections T1 and T2 and these will be dead locked. Normally when you run the two connections, connection1 with Task T1 becomes a victim. But, you want T2 to be the victim. If you don't want task T1 to be a victim, Set the DEADLOCK_PRIORTY greater than the DEADLOCK_PRIORITY of Task T2 .

IN T1 at the top of the query

SET DEADLOCK_PRIORITY NORMAL

GO

IN T2 at the top of the query

SET DEADLOCK_PRIORITY HIGH

GO

Now open two connections and run the queries. T2 will be a victim.