Split a given string into words based on the delimiter.

We can split a string into different words based on the delimiter. This example shows how we can achieve this. We can also use recursive CTE but if the string is too long MAXRECURSION can be encountered.

--DECLARE THE VARIABLES

DECLARE @String NVARCHAR(4000)
DECLARE @Delimiter NVARCHAR(10)
DECLARE @DelimiterPosition INT
DECLARE @StartofString INT
DECLARE @EndofString INT
DECLARE @DelimiterLength INT

--INITIALIZE THE VARIABLES

SET @String = 'Welcome,to,the.'
SET @Delimiter = ','
SET @DelimiterLength = LEN('.'+@Delimiter+'.')-2 --WILL BE USEFUL WHEN WE HAVE WHITE SPACE AS DELIMITER
SET @StartofString = 1
SET @EndofString = LEN(@String)+1
SET @DelimiterPosition = 0

WHILE (@DelimiterPosition < @EndofString)
BEGIN

SET @DelimiterPosition = CHARINDEX(@Delimiter,@String,@StartofString)
IF(@DelimiterPosition = 0)
SET @DelimiterPosition = @EndofString --FOR THE LAST SPLIT WORD, THE DELIMITER POSITION WILL BE ONE. SO, WE SET IT TO END OF THE STRING
PRINT SUBSTRING(@String,@StartofString,@DelimiterPosition-@StartofString)
SET @StartofString = @DelimiterPosition+@DelimiterLength
END


If we want to use CTE the following code will do exactly as above


DECLARE @Delimiter NVARCHAR(10)
DECLARE @String NVARCHAR(4000)
SET @String = 'Welcome,to,the,new'
SET @Delimiter = ','

;WITH Split
AS(
SELECT SUBSTRING(@String+@Delimiter,1,CHARINDEX(@Delimiter,@String+@Delimiter)-1) AS Word
,SUBSTRING (@String+@Delimiter,CHARINDEX(@Delimiter,@String+@Delimiter)+1,LEN(@String+@Delimiter)) As Remaining
   
    UNION ALL
   
SELECT SUBSTRING(Remaining,1,CHARINDEX (@Delimiter,Remaining)-1) AS Word
,SUBSTRING (Remaining,CHARINDEX(@Delimiter,Remaining)+1,LEN(Remaining)) As Remaining
     
FROM split
  WHERE CHARINDEX (',',Remaining)<>0
  )
 
  SELECT Word FROM Split

Know whether a number is power of two

There are several ways to find whether a given number is power of two. I am sharing two ways by which we can see if the number is power of two or not. There is a basic rule to know whether a given number is power of two or not. (N) & (N-1) is Zero. Here & is Bitwise and. When we use &, SQL server internally converts the given Number into Binary and performs Bitwise and. Take an example.
N=2 Binary is 10
N-1 = 1 Binary is 01
N& N-1 =
10
01
---
00
---
This is simple logic. This is the SQL code


DECLARE @N INT
SET @N =  -- pass a value
IF(@N & (@N-1))=0
PRINT 'Pow of two'
ELSE
PRINT 'Not pow of two'

The other way is to do it programatically. Here is how you can do



DECLARE @IncomingNumber int
SET @IncomingNumber = --pass a value
DECLARE @BinNumber VARCHAR(200)
SET @BinNumber = ''

WHILE @IncomingNumber <> 0
BEGIN

IF(@IncomingNumber%2 = 0)
SET @BinNumber = CAST(0 AS VARCHAR(200))+ @BinNumber
ELSE IF(@IncomingNumber%2=1)
SET @BinNumber= CAST(1 AS VARCHAR(200))+@BinNumber

SET @IncomingNumber = @IncomingNumber / 2

END

SELECT @BinNumber
IF(SUBSTRING(@BinNumber,1,1)=1 AND CAST(SUBSTRING(@BInNumber,2,LEN(@BinNumber)) AS INT) =0)
PRINT 'Pow of two'
ELSE
PRINT 'Not Pow of two'

Convert an Integer to Binary

Here is a quick and fast way to convert an integer to Binary number. Binary number is base to two. That is we have to divide the number by two and take the remainder in the reverse order. In SQL server we can achieve this by this simple script.



DECLARE @InputValue int SET @InputValue = --Specify the number
DECLARE @Binary VARCHAR(200)
SET @Binary = '' -- Initialize to nothing
WHILE @InputValue <> 0
BEGIN
SET @Binary = SUBSTRING('01', (@InputValue % 2) + 1, 1) + @Binary
SET @InputValue = @InputValue / 2
END SELECT @Binary

Look at SUBSTRING() . I hardcoded '01' and taking the length of  Input value. Remember when you divide any number by two, the remainder will be either 0 or 1 and it won't go beyond. Once Remainder is taken, take the substring of '01' the values would be SUBSTRING('01', 1 OR 2,1). So we get either 0 or 1 which is binary. We add the previous binary value to the existing substring. Remember @Binary is string so, the order is important. append @Binary at the end. When you divide a number by 2 and try to take its value, always remember that the remainders are taken in bottom up approach. That is why I am appending the previous remainder.

Steps for table replication:

Steps to setup Publication (Source):

1.       Open SSMS and connect to the instance.

2.       From the object explorer, go to Replication.

3.       Right click on Replication and select New -> Publication

4.       Publication wizard will open. Click next and select the database where the table(s) resides which needs to be replicated and click next.

5.       Select the type of publication which will satisfy the requirement and click next.

6.       You will be presented with the tables in the DB. Select the required Table(s) and click next.

7.       You will be presented with filter options. Select the required filters if you don’t want all the data to be replicated. You may skip if you don’t want to filter the data.

8.       In the next step you can specify how often to replicate. You can schedule to run daily at certain times or you can replicate as soon as new data enters the table.

9.       In the next steps you have to specify the security settings for SQL AGENT. Follow this carefully.

10.   At the end you will be provided with options to create the publication and generate script for publication.

11.   Select both so that you may reuse the script for later deployment to other environments.

Steps to setup Subscription (Target)

1.       Once the publication is created, the selected tables are ready for replication.

2.       Right click on Replication and select New ->Subscription. Wizard opens. Click Next.

3.       You will be presented with the DB where the source tables are (Publisher). Select the DB and click next.

4.       You will be presented with two options. Select the appropriate option according to your needs and click next.

5.       In this step you will have to select the target Instance and the target DB and click next.

6.       Select the security options for the SQL AGENT and click next.

7.       Specify how often the job should run and click next.

8.       Specify how the initialization would be and click next.

9.       Click both the options and click next. You will be prompted to save the file. Specify save location and click finish.

10.   Table is automatically created in the target DB.

You have successfully replicated the table. For a test, add new data in the publisher table and do a select on the Subscription table. Depending on the type of snapshot and how often the SQL AGENT job runs, the data will be replicated. 

Table replication in SQL Server 2008/R2

Replication comes in handy when you need other environments to be in sync with your production environment. Generally, Database replication is often used. But, what if, if you need only some tables in a particularDB in your dev environment needs to be synced with Prod environment? The answer is just replicate the required tables. You can replicate tables from one DB to another DB in the same Server/instance or you can from One server to another server. Once the tables are replicated, new field [msrepl_tran_version] will be added to the end of the table. You may hide this to external users by creating a view. In replication terminology, we call Publisher and Subscriber. Publisher is the mainDB that has the tables. Subscriber is the target. Once you subscribe to a publisher for a particular table(s), you will see them in the Subscriber (the target DB). You can also specify the replication strategy whether the replication is transactional/snapshot etc...

Stored Procedure best practices

There is a nice presentation that was found on the web published by Kevin Kline. The actual topic starts from slide 9 / page 10. This indeed will be very helpful. The performance of the procedures increases significantly.

http://www.quest.com/sql_server/pdfs/Stored-Procedure-BP.pdf

Optimizing SSIS packages

When I was working for a client, I faced a serious problem with SSIS package. The SSIS package is simple, extract data from csv file and two databases and load the data into a single table. I used a flat file source, two OLEDB Sources, a data conversion and one OLEDB destination. Once the package was created in the dev, I executed it and it took whooping 5 hours to execute. I was scared of the time it took. We were supposed to schedule a job with this package that executes every 30 mins. So, I started digging into the package. There were several fields from the source which were not used. Instead of using a table in the source, I wrote a SQL command that selects only the desired fields. In the destination mappings were updated. I re ran the package and it did not make a significant impact on the execution time. I was confused of what to do. Went into the destination table and checked the schema. Wow, its the index that caused all the problems. There is one clustered and three non clustered indexes on the table. I disabled the indexes and re ran the package. Its amazing the package executed in 15 minutes. So, I added few steps in the package where before the dataflow begins, the indexes are disabled and once the dataflow executes, the indexes are rebuilt. This way the performance was amazing and the job is scheduled to run the package every 30 mins. These are the few steps which would be helpful to optimize the SSIS package.

1. While extracting the data from source use query to pull only the required fields
2. If there are indexes on the destination table, disable them and enable the indexes once the data load is complete.
3. If data is extracted and loaded in the same server, use SQL server Destination instead of OLEDB Destination.

There are some other optimization techniques but, they depend on the environment and the kind of package thats being developed. Since SSIS is mostly about data, pull and push be optimized first.