Multiple UNPIVOT in SQL Server

Learning multiple unpivot option was little interesting for me.

SELECT tempoid,version,dataField,datavalue,dataField1,datavalue1

FROM

      (SELECT t1.tempoid, t1.version, t1.data1, t1.data2, t1.data3, t1.data4, t2.data1 as d1, t2.data2 as d2, t2.data3 as d3, t2.data4 as d4

      FROM table1 t1 inner join table1 t2 on t1.tempoid = t1.tempoid and t1.version = t2.version + 1

      WHERE t1.version in (@version)

      ) AS p

UNPIVOT

      (datavalue FOR dataField IN

            (data1, data2, data3, data4)) as u1

 UNPIVOT (datavalue1 FOR dataField1 IN

      (d1, d2, d3, d4)) as u2

That’s it, simple

To under the above query, we will first create two tables and insert few records into

create table table1(OID BIGINT identity,tempoid bigint,version varchar(30),data1 varchar(30),data2 varchar(30),data3 varchar(30),data4 varchar(30))

create table table2(oid bigint identity,tempoid bigint,version varchar(30),fieldname varchar(30),Olddata varchar(30),Newdata varchar(30))

select * from table1

select * from table2

Let us first try inserting through this procedure

CREATE proc procInsert(@tempoid bigint,@version varchar(30),@data1 varchar(30),@data2 varchar(30),@data3 varchar(30),@data4 varchar(30))

AS

BEGIN

INSERT INTO table1 values(@tempoid,@version,@data1,@data2,@data3,@data4)

IF(@version >1)

BEGIN

;with cte as(

select tempoid,version,dataField,datavalue,dataField1,datavalue1

FROM

(SELECT t1.tempoid, t1.version, t1.data1, t1.data2, t1.data3, t1.data4, t2.data1 as d1, t2.data2 as d2, t2.data3 as d3, t2.data4 as d4

FROM table1 t1 inner join table1 t2 on t1.tempoid = t1.tempoid and t1.version = t2.version + 1

WHERE t1.version in (@version)

) AS p

UNPIVOT (datavalue FOR dataField IN (data1, data2, data3, data4)) as u1

UNPIVOT (datavalue1 FOR dataField1 IN (d1, d2, d3, d4)) as u2

)

–select * from cte

Insert into table2

select cte.tempoid,cte.version,cte.datafield,cte.datavalue1  as NewValue,cte.datavalue as OldValue

from cte inner join table1 t1 on t1.tempoid = cte.tempoid and t1.version = cte.version where ((cte.datafield = ‘data1’ and cte.datafield1 = ‘d1’)

or (cte.datafield = ‘data2’ and cte.datafield1 = ‘d2’) or (cte.datafield = ‘data3’ and cte.datafield1 = ‘d3’) or (cte.datafield = ‘data4’ and cte.datafield1 = ‘d4’))

AND cte.datavalue <> cte.datavalue1

END

END

go

procInsert 1,2,’2.1′,’2.2′,’2.3′,’2.4′

procInsert 1,3,’2.1′,’2.2′,’2.3′,’2.5′

Now select the records from both the tables

select * from table1

select * from table2

The required functionality is, the no of columns that are different from current row to its previous version

Those different columns should be recorded in a new table ‘table2’

now try inserting new row in table1 like

procInsert 1,4,’2.6′,’2.7′,’2.8′,’2.5′ — I have modified 3 columns here data1, data2 and data3

the result is :

(1 row(s) affected)

(3 row(s) affected)

and the output is

-Yuva

Cure Blood Cancer – Imitinef Mercilet

 

Imitinef Mercilet is the medicine, that cures Blood cancer. And also this is available free of cost @ the below locations

Cancer Institutes at Chennai (Adyar), Trivandrum, Kovai (Ramakrishna Hospital) and Bangalore.

No more deaths due to Blood cancer !!! 🙂

 

Free Heart Treatment for Kids, at Sri Sathya Sai Institute ,Bangalore.
Treatment for Kidney ailments ,at The Bangalore Kidney Foundation, No.489.39th.Cross,8th.Block, Jayanagar,Bangalore 560 082-India

-Yuva

Protecting tables, Procedures and … in SQL Server 2008

 

Here is the trigger which protects all the tables and procedures in a database / server.

create trigger triggername

on all server

for create_procedure,alter_procedure,drop_table

as 

    Print 'you are attempting to drop the table or create procedure or drop procedure'

    rollback;

This trigger can also be extended to protect all the verticals like Index, Functions, Views, etc

-Yuva

Top tricks for SQL Server database development

1) Always match datatypes in code with the columns in the database
It’s important to make sure that your datatypes match across all layers in your application. For example, if a column’s datatype is NVARCHAR (50), you should have the code in queries and stored procedures use local variables of the same datatype.

Similarly, the ADO.NET code in the data layer should specify the same datatype and length. Why is this important? Because if the datatypes and queries do not match, SQL Server needs to perform an implicit conversion of the datatypes to match them.

There are also some scenarios where SQL Server cannot use an existing index, even though the referenced column is indexed. Therefore, your query might end up using Index Scan instead of Index Seek, resulting in execution times with longer orders of magnitude than if the variables and columns were of the same type.

2) Do mass updates in batches
Developers sometimes need to modify data in one or more columns for all or most rows in a table. This is usually not an issue as long as the table is fairly small.

If the table is large, however, your update statement will lock the entire table and make it unavailable, even for data reads. Further more, a highly volatile table can bring down the entire application or website for the duration of the update. At times, a large, single transaction like this will greatly expand the size of the transaction log and — in extreme scenarios — contribute to running out of disk space on the database server.

It is therefore a good practice to do mass updates in batches, combined with frequent transaction log backups. In my experience, a batch of 10,000 or 50,000 works best. It is difficult to specify a threshold of when you should start considering batching, as it all depends on factors such as how fast you disk I/O is, how heavily the table is used, and more.

There is one guideline you can use though. A typical command timeout in ADO.NET is about 30 seconds. While the update takes place, other processes have to wait until it is finished. So if you expect that your update will take longer than 20-25 seconds, you are better off doing a batch update, otherwise you will end up with application timeouts.

Here is a sample code that shows how to update a column in a table, using 10,000 as batch size:

WHILE ( 0 = 0 )
BEGIN
        UPDATE TOP ( 10000 )
                Person
SET     Status = 2
WHERE   Status = 1
IF @@ROWCOUNT = 0
BREAK
    END

3) Utilize FOR-EACH stored procedures.
Once in a while you might need to perform the same action on all objects of a certain type. For example, you might need to assign a specific permission for all tables in the database. Developers often resort to cursors for tasks like this, but SQL Server comes with two handy stored procedures that make things a lot easier: sp_msForEachTable and sp_msForEachDB.

Each of these takes a command to be executed as a parameter. You can embed a question mark in the parameter as a placeholder for the table or database name in the command. At runtime, SQL Server replaces the question mark with the name of the table or database and executes it.

For example, the following code runs a full backup for each database on the server, except for TempDB:

EXEC sp_msforeachdb 'IF ''?'' <> ''tempdb'' BACKUP DATABASE ?
            TO DISK=''c:\backups\?.bak'' WITH INIT'

Here is another example of how these stored procedures can be useful — and somewhat dangerous. The following code deletes data in all tables in the database after disabling the foreign key. Naturally, you’ll want to exercise caution when using this code:

EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'

EXEC sp_MSForEachTable '
IF OBJECTPROPERTY(object_id(''?''), ''TableHasForeignRef'') = 1
  DELETE FROM ?
else
  TRUNCATE TABLE ?
'

EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'

4) Version your database builds

It’s considered a good practice for developers to implement numeric versioning of databases, just like they do with applications.

It doesn’t require a lot of effort to implement versioning – you just have to create a table with a version number and additional timestamps. Once you get better at assigning a build number to each set of scripts and updating the version table when you deploy those scripts, it becomes much easier to troubleshoot and compare your databases. You could even code your scripts so that they don’t execute if the build number in the database is not higher than the build number in the script. The AWBuildVersion table in the AdventureWorks sample database is a good example to look at.

5) Minimize the number of network calls

This tip applies mainly to Web applications that pull data from a database. Less experienced developers often don’t realize that each database call is a relatively expensive operation. It’s not a big deal in small applications, but since many websites could become popular and used by thousands of simultaneous users, you need to start thinking about scalability and optimizing your page load times in advance.

One thing to keep in mind is that SQL Server can return multiple ResultSets in a single stored procedure. You can use the DataSet object in ADO.NET and populate a collection of DataTable objects in a single database call

-Yuva

How to compare two tables in SQL server

SQL Server is having a command line tool (TableDiff) to compare the data in two tables.It will perform the following task.

1. A row by row comparison between a source table in an instance of Microsoft SQL Server acting as a replication Publisher and the destination table at one or more instances of SQL Server acting as replication Subscribers.
2. Perform a fast comparison by only comparing row counts and schema.
3. Perform column-level comparisons.
4. Generate a Transact-SQL script to fix discrepancies at the destination server to bring the source and destination tables into convergence.
5. Log results to an output file or into a table in the destination database.

eg:

"C:\Program Files\Microsoft SQL Server\90\COM\TableDiff.exe" -sourceserver "SERVERNAME" -sourcedatabase "DBNAME" -sourceschema "dbo" -sourcetable "SOURCETABLE1" -sourceuser "sa" -sourcepassword "PASSWORD" -destinationserver "SERVERNAME" -destinationdatabase "DBNAME" -destinationschema "dbo" -destinationtable "SOURCETABLE2" -destinationuser "sa" -destinationpassword "PASSWORD" -dt -o "C:\Documents and Settings\renjuraj\My Documents\diff.xls"

Run the above command in command prompt.Please refer your sql server installation path(C:\Program Files\Microsoft SQL Server\90\COM\TableDiff.exe).

-Yuva