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.

Using the concept of Unpivot

Pivoting and Unpivoting are a very handy features in SQL Server. Unpivoting technique in particular is very handy. Consider an example. Say there is an employee table that has ID,Name,Homeph,MobilePh,Workph fields in the table and there are more than 100 million records. Now a new requirement comes in to add the Faxnumber also. What are the options we have? One is using native ALTER TABLE statement and add the new field.

ALTER TABLE Employee
ADD Faxnum VARCHAR(13)

100 million records, its ok to alter the table. What if there are a billion records and a requirement comes in to add another field say ManagerPh? Again we have to alter the table. The best way to handle these kinds of situations is to use UNPIVOT. Look at the pattern. HomePh,MobilePh,Workph,FaxNum... all are of similar type (Phone numbers). The table can be called as PIVOTED (remember its just an example. to pivot the data we need to USE aggregate functions on one of the filed. Its just for understanding).

We can Unpivot. Create a table Employee with Fields ID,PhoneType, Number
Now we can use UNPIVOT or alternate way is to use Union/UnionAll

Using UNPIVOT:

SELECT ID,PhoneType,Number
FROM Employee
UNPIVOT(Number FOR PhoneType IN(HomePh,MobilePh,Workph)) UNP

This will change the fields to records. So, If at all new phone type needs to be added, there is no need to change the schema of the table. We just need to update the query.

Using UNION ALL

SELECT ID, 'HomePh' AS PhoneType, HomePh AS Number
UNION ALL
SELECT ID, 'MobliePh',MobilePh
UNION ALL
SELECT ID, 'WorkPh',WorkPh

The above UNION ALL statement is same as using UNPIVOT. I used UNION ALL here. UNION also can be used. Logically, UNION will do a DISTINCT on select columns. This decreases the performance. This is very true if the table holds huge amounts of data.

Find First Sunday of a month

There are several ways to find First Sunday of a month. The following is one of the ways

DECLARE @dtDate DATETIME
SET @dtDate = GETDATE() -- Replace with any other date
SELECT DATEADD(WEEKDAY,(8-(DATEPART(WEEKDAY,DATEADD(mm,DATEDIFF(m,0,@dtDate),0))))%7,DATEADD(mm,DATEDIFF(m,0,@dtDate),0))

First look at
DATEDIFF(m,0,@dtDate). 0 in datetime corresponds to 1900-01-01. We calculate the difference of months from 1900-01-01 to a given date. Once we get this we add the difference we got to the anchordate.
DATEADD(mm,DATEDIFF(m,0,@dtDate),0). This gives the firstday of the given month. Once we have firstday of a given month, we have to find the weekday of the day. In SQL server Sunday starts with 1 and Saturday ends with 7.
DATEPART(WEEKDAY,DATEADD(mm,DATEDIFF(m,0,@dtDate),0)). This gives a number from 1-7. Now we subtract the obtained number from 8 since, chronologically second sunday would be 8. Once we subtract from 8, we are getting the remainder of the value after dividing it by 7 as there are seven days in a week. This step is required because if first day of the month is first sunday, if we don't do a modulo, the logic will show second sunday as first sunday.
Once we have this value, we add this value to the weekday and the final query will be

SELECT DATEADD(WEEKDAY,(8-(DATEPART(WEEKDAY,DATEADD(mm,DATEDIFF(m,0,@dtDate),0)))),DATEADD(mm,DATEDIFF(m,0,@dtDate),0))

If you want to get first monday replace 8 with 9 and for first tueday replace 8 with 10...for first saturday...If you want to get second sunday add +7 to the above query for third sunday add +14 and so on....its that simple