Looping through list in SQL

If we have a comma separated list and the requirement is to loop through the list of items one by one or to pass these items to a stored procedure. The easiest and simple way to do this is given below with coments.

--comma separated list
Declare @S varchar(20)
set @S ='abc,def,ghi,jkl' 

--loop through each item
while len(@S) > 0
Begin

--get the first item form the list
declare @S1 varchar(20)
set @S1 = left(@S, charindex(',' , @S+',')-1)

--Print it
Print @S1

--Delete the item from the list
set @S = Stuff(@S,1,charindex(',',@S+','),'')

End

TRANSACTION and Error Handling

Transactions are very useful when you are not sure if there could be a error or not. And if there is a error, the set of sql statements need not be executed or needs to be rolled back.In this case we can TRANSACTIONS that help us to ROLLBACK if there was any error while executing the sql statements.

The below example also shows simple error handling. If there is any error while updating the table then control is passed on to the CATCH block and the TRANSACTION is rolled back.

USE [PUBS]
GO

DECLARE @iError int

BEGIN TRANSACTION

    UPDATE table
    SET column = 'value'
    WHERE column = 'condtion'
   
    select @iError = @@ERROR
    IF (@iError <> 0) GOTO CATCH
   
COMMIT TRANSACTION

CATCH:
IF(@iError <> 0)
BEGIN
    PRINT 'Error message'
    ROLLBACK TRANSACTION
END

DELETE and TRUNCATE can be rolled back

As we all know, DELETE can be rolled be back even if it is committed, using the log file and some third party to tools.

Truncate can also be rolled back, if it is within a TRANSACTION and if the session is not closed.

Try the below code, first create a table, then insert some data into it. Then run these set of lines. You should get some data before truncate is run, after truncate there shouldn't be no data and after rollback, if the original data is returned then TRUNCATE CAN be rolled back.

NOTE: I am running this code in SQL SERVER 2008


BEGIN TRANSACTION

SELECT * FROM table1

TRUNCATE TABLE table1

SELECT * FROM table1

ROLLBACK

SELECT * FROM table1

IDENTITY_INSERT

If you want to insert a specific value into a identity column of a sql server table then here is how you can do it.

SET IDENTITY_INSERT ON

And then insert the identity value into the table. If a table already has this property set to ON in the same session then SQL will throw out the error indicating which table is it set.

After inserting the value set it OFF by executing

SET IDENTITY_INSERT OFF

Types of Triggers and Creating Triggers

In this blog let us see how many types of trigger we can find and how to create them. Lets not waste any time then.

There are two kinds of triggers.
-After Triggers
-Instead of Triggers

After Triggers are of 3 types
-After Insert
-After Update
-After Delete

Instead of Triggers are of 3 types again.
-Instead of Insert
-Instead of Update
-Instead of Delete

Lets see an example in each category by creating the triggers and executing them

First, create a simple table with two columns.

CREATE TABLE [dbo].[TestTable](
    [EmpId] [varchar](50) NULL,
    [Name] [varchar](255) NULL
)


Create another table where the data will be inserted by the trigger.

CREATE TABLE [dbo].[AFTERINSERT](
    [EmpId] [varchar](50) NULL,
    [Name] [varchar](255) NULL
)


Insert some data into the TestTable.

INSERT INTO TESTTABLE
VALUES('01','ABC')

INSERT INTO TESTTABLE
VALUES('02','BCD')

Now create the below trigger that will insert the data into AfterInsert table and also print the data that has been inserted into TestTable

InsertTrigger is the name of the trigger and since we are creating trigger on TestTable for and insert we mention that in the trigger which is clearly readable.

In the body of the trigger, we create two variables to hold the data from INSERTED table. This is not a physical table but a logical one that holds the just inserted data.

And that data is inserted into the AfterInsert table.
 
CREATE TRIGGER INSERTTRIGGER ON TESTTABLE
FOR INSERT

AS

DECLARE @EMPID VARCHAR(255)
DECLARE @NAME VARCHAR(255)

SET @EMPID = I.EMPID FROM INSERTED I
SET @NAME = I.NAME FROM INSERTED

--PRINT THE ID AND THE NAME
PRINT @EMPID + ' ' + @NAME

--INSERT INTO AFTERINSERT TABLE
INSERT INTO AFTERINSERT
VALUES(@EMPID,@NAME)

GO

Now try inserting data into the TestTable by using this line

Insert into TestTable values('03', 'CDE')

You can create similar triggers for Update and Delete as well. For Update, there is no logical table, but have to use Inserted. For Delete, there is a table called Deleted. Use Deleted instead of Inserted in the above trigger for deletion.

Now lets create a trigger for Instead Of Delete. This can be used to intercept any modifications to the table like insert, update or delete.

Below example is for Instead of delete.

As explained before, since we are checking on TestTable, we have to mention the table name and Instead Of delete.

Most of the body is same except that, we are checking if emp id is '01'. If yes, then prints the message and rollsback. if not deletes it.

Create the below trigger and try to delete the emp id '01'.

CREATE TRIGGER INSTDDELETE ON TESTTABLE
INSTEAD OF DELETE

AS

DECLARE @EMPID VARCHAR(255)
DECLARE @NAME VARCHAR(255)

SET @EMPID = I.EMPID FROM DELETED I
SET @NAME = I.NAME FROM DELETED I

IF @EMPID = '01'
    BEGIN
        --PRINT THE ID AND THE NAME
        PRINT @EMPID + ' ' + @NAME + ' CANNOT BE DELETED'
        ROLLBACK
    END

GO

Similarly we can create Instead of triggers for Update and Insert as well. Instead of using Deleted table use Inserted table.

Debug.writeline

One of the class in .Net that is very useful to log messages or track the sequence of execution is to use 'System.Diagnostics.Debug' class. The Debug.Writeline("Message") writes the message to the output window while debugging. If you want this message to be written to a file then you need to have listener that listens to these messages which is better understood by the below example of code.

Place this code in a button click of windows application. This code is applicable only for vb.net.

    Dim Tr As TextWriterTraceListener
    Tr = New TextWriterTraceListener(System.IO.File.CreateText("Output.txt"))
    Debug.Listeners.Add(Tr)
    Debug.WriteLine("Test Message")
    Tr.Flush()
    Tr.Close()
    Tr.Dispose()

Declares a new TexWriterTraceListener that will create a new text file. Add this listener to the Debug class and start writing the messages. Then flush the buffer of listener and close and dispose the trace. The output.txt file should be placed in the debug folder of your application.

You can also use the Debug class to write the message on conditional basis. For example use:

Dim quantity as integer = 51
Debug.WritelineIf(quantity > 50, "This message will appear")
Debug.WritelineIf(quantity < 50, "This message will NOT appear")

There is one more method called 'Assert' that will display the message only if the evaluated condition returns false. For example:

Dim quantity as integer = 51
Debug.Assert(quantity > 50 "This message will not appear") 
Debug.Assert(quantity < 50 "This message will appear since the quantity < 50 is false")

For better readability, use Debug.Indent() method to indent subsequent messages. You can also use Debug.UnIndent() method to UnIndent subsequent messages.

Debug.Write() appends the message to the same line, while Debug.Writeline() writes the message to a new line.

At last, you can also use Trace.Writeline instead of Debug.Writeline. Both classes send messages to all the listeners in the debug mode. However, in release mode, only Trace.Writeline works.

Copy one database to another database

There are several ways to do this, below are two options:

Option 1
- Right click on the database you want to copy
- Choose 'Tasks' > 'Generate scripts'
- 'Select specific database objects'
- Check 'Tables'
- Mark 'Save to new query window'
- Click 'Advanced'
- Set 'Types of data to script' to 'Schema and data'
- Next, Next


You can now run the generated query on the new database.

Option 2
- Right click on the database you want to copy
- 'Tasks' > 'Export Data'
- Next, Next 
- Choose the database to copy the tables to
- Mark 'Copy data from one or more tables or views'
- Choose the tables you want to copy
- Finish

Repeat the value using Replicate

This post is to repeat a specific value for specified number of times and assign that to a variable or if you have a column then do so.

This uses Replicate() function to repeat a string for max length of variable using sql_variant_property

The last statement LEN(@test) gets the lenght of the value assinged to the variable

DECLARE @test VARCHAR(255)
SET @test = ''
SET @test = REPLICATE('ab',CONVERT(INT,(select sql_variant_property(@test, 'MaxLength'))))
SELECT @test
SELECT LEN(@test)

Data type size of variable and column in sql

This post is to detect the length of the data type of a variable and a column.

The first piece of code declares a variable, and usein sql_variant_property finds out the length of the datatype.

The second one, gets the size of the column data type of a table.

--For variable

DECLARE @test varchar(255)
SET @test = '' --Must assign a value
SELECT sql_variant_property(@test, 'MaxLength')

--For column of a table
SELECT column_name, data_type, character_maximum_length 
FROM information_schema.columns
WHERE table_name = 'myTable'

SQL query performance

Below are some of the tips to improve the performance of stored procedures of queries

-Use TRUNCATE instead of DELETE where ever possible.

-REMOVE 'SELECT TOP 100 PERCENT FROM TABLE' and use 'SELECT * FROM TABLE' or 'SELECT      column_names FROM TABLE'

-Design out the OUTER JOINs. Filter the data as much as possible so that the procedure runs on minimum set of data. Use temp tables or table variables to do this.



-If you are using 'NOT IN' then avoid using that. It causes severe overhead to the performance of the query.

-Have a primary key to the table and create non clustered Indexes where ever necessary, this increases the performace surprisingly.

-Avoid using functions in the query, since each row of the data has to be processed through the function, this causes overhead to the performance.



You can read the below articles for detailed information:

http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-1/


http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-2/ 

http://www.simple-talk.com/sql/t-sql-programming/ten-common-sql-programming-mistakes/

http://www.sommarskog.se/error-handling-I.html
 

Create Non Clustered Index

CREATE NONCLUSTERED
INDEX Index_Name
ON Table_1 (Col_1)
INCLUDE (Col_2, Col_3, Col_4)

Find duplicate rows in a table

SELECT col_1, col_2, count(col_2)
FROM table_1
GROUP BY col_2
HAVING count(col_2) > 1

Select all tables and their row counts

SELECT DISTINCT convert(varchar(30),object_name(i.id)) [Table Name], i.rows FROM sysindexes i INNER JOIN sysobjects o on i.id = o.id