Systems Engineering and RDBMS

  • Click Here for Decipher's Homepage

  • Categories

  • Questions?

    Please send your wish list of things that you would like us to write about or if you have suggestions to help improve this blog site. You can send all questions/suggestions to: Blog Support
  • Archives

  • Blog Stats


Archive for May, 2009

Data Access Tracing and SQL 2008 Developer Training Kit

Posted by decipherinfosys on May 31, 2009

Here are two links for Developers working on SQL Server based systems and using .Net:

  • Whitepaper from MSFT on Data Access Tracing – here, and
  • SQL Server 2008 Developer’s Training Kit – here.

Posted in .Net Development, SQL Server | Leave a Comment »

Microsoft’s new search engine – Bing

Posted by decipherinfosys on May 30, 2009

On June 3rd, MSFT is all set to launch it’s new search engine called Bing.  The search market is really dominated by Google and Yahoo at this time.  As per this article on, statistics indicate that close to 30% of the searches done by the end user fail to provide satisfactory answers to the end user and hence there is still a lot of scope in the search market.  Bing is being touted as a decision engine by MSFT and it will be more geared towards certain domains and the accompanying presentation tier, commercial content, ads etc. will thus be also geared towards those domains.  You can read more here:

Posted in News, Technology | Leave a Comment »

Different XML Data Type Methods

Posted by decipherinfosys on May 29, 2009

We have covered the different XML data type methods before in our blog posts.  Over here, we are just aggregating all of them together and will show the differences between each of those methods.  The xml data type allows 5 different methods to allow us to query, modify or shred the XML data and present it to us in a record format.  The 5 methods are:

a) nodes() method:  This method can be used to convert XML data into row/column format.  Here is the link for all the posts that we have done on the nodes method and those posts also have links to the BOL and other related articles.

b) exist() method: This method helps us to specify XQuery on the XML instance and if the value matches, it returns back 1 and if non-matches, it gives back a 0.  We had covered it in our post here.  Take a look at the word doc. attachment in that post and it shows how to declare a variable and use it in the exist() method.

c) value() method: This method allows us to perform XQuery on a xml instance and return a scalar value back.  One of the posts in which we had covered this was the one where we had compared two different XML variables – here.

d) query() method: This method also allows us to perform XQuery on a xml instance and return back an untyped XML to us.   We had covered this in our post on the ReportServer DB Queries – here.  And the last method in the list is:

e) modify() method: This method is new in SQL Server 2008.   As the name also suggests, this method is used do DML operations against a XML instance.  We had covered this here and here.

We have seen that not many DBA’s spend much time in learning about XML and the XML capabilities of SQL Server.  We would highly recommend getting familiar with all the enhancements in SQL Server 2005 and SQL Server 2008.  Key is to also know when to use it and how to use it.  One of the best books out there on this topic is the book by Michael Coles – Pro SQL Server 2008 XML.

Posted in SQL Server | 3 Comments »

Back to the Basics: Simple ways of copying data from one table to another

Posted by decipherinfosys on May 29, 2009

A junior DBA at a client site recently asked this question while performing his duties:

“I have 2 tables in the same schema in the database and I need to copy the data from one to the other one.  I typically use DTS (SQL Server 2000) or SSIS (SQL Server 2005) to do that.  Also, at times, I have a need to copy data from multiple tables into a single table for analyzing the summary data.  Is there an easy way to do that using SQL?”

There are two very simple ways of achieving this via T-SQL.

Method 1: One can either create a table (if the destination table does not exist) and then do an:

insert into <new table> select … from <old table or set of tables> where <the where condition over here>


Method 2: If this is something that you need to do as a quick and dirty thing and you do not intend to keep the new table around post the analysis, you can also do:

select … into <new table> from <old table or set of tables> where <the where condition over here>

The “select into” is the same as CTAS in Oracle.  You can read more on that here.

Posted in SQL Server | Leave a Comment »

Google Wave

Posted by decipherinfosys on May 29, 2009

A friend passed along the link to this article on TechCrunch about Google’s Wave which is essentially a new tool from Google for facilitating communication and collaboration over the web.  It is scheduled to be released later this year but there is a cool demo video available for us to look at.  Here are the links:

The techCrunch article – here.

The demo video – here.

Posted in News, Technology | Leave a Comment »

Ten ways Twitter will change American Business

Posted by decipherinfosys on May 28, 2009

An interesting article at 24/7 Wallst – discussing the micro-blogging platform Twitter and how it can change the American Businesses – you can read it here.

Posted in News, Technology | Leave a Comment »

SQL Server 2008 BOL – May 2009 version

Posted by decipherinfosys on May 26, 2009

You can download the latest version of BOL from here.  For anyone looking to learn more about SQL Server 2008, BOL and Technet should be the first resources to learn from supplemented by books, blogs and other online articles.

Posted in SQL Server | Leave a Comment »

Bulk Loading XML Data

Posted by decipherinfosys on May 20, 2009

Two days ago, as part of a project that we are working on, we got an XML file that was close to 3GB in size.  First attempt was to use openrowset() to dump the XML into a table and then query that data using the nodes() method to parse out the data elements and then do the processing of the data from the staging table(s) into the transactional system.  The openrowset() call went through pretty quickly but working on the XML in the XML data type column in the table was just taking forever.  This was even after the creation of the XML indexes.  Then we decided to use XML Bulk Load and load up the data into the table(s) in the staging area.  One issue was that the client did not know which elements/attributes they will be using in the future so they wanted all of them.

So, using XMLBulkLoad, there is an option of creating the table structure as part of the bulk load and that is what we ended up doing.  It was of course done in a staging area and then once better understanding was there for the data and their relationships, proper table structures with relationships were created.  This is how the code looked like:

Dim objload
Set objload = CreateObject(“SQLXMLBulkLoad.SQLXMLBulkload.4.0”)
objload.ConnectionString = “provider=SQLOLEDB;data source=DIS-03;database=Decipherprod;integrated security=SSPI”
objload.ErrorLogFile = “c:\BulkDataLoad\Load_error.log”

objload.SchemaGen = True
objload.SGDropTables = True

objload.Execute “DataWarehouse-3.4.xml”, “DataWarehouse34_FC_USA_M_20090505.xml”
Set objload = Nothing


  • BOL entry for XML Bulk Load – here.
  • BOL entry for openrowset() – here.

Posted in SQL Server | Leave a Comment »

Supporting a higher version of the RDBMS

Posted by decipherinfosys on May 18, 2009

We have seen this happen too many times – a vendor would state in their presentation or marketing material that their product “supports” a newer version of the RDBMS.  A lot of times we have seen that the vendor just makes sure that their product does not break when running on a newer version of the RDBMS.  They do not take advantage of any of the new features of that RDBMS but since their product’s regression test went through fine after making connection changes and any other trivial changes that they needed to make, it makes it’s way into the marketing material.

That is mis-representation of the truth.  One should be clear in their message whether they really support a newer version of the RDBMS or have they just made sure that their product will run on it but will not take advantage of any of the new features till they come up with a subsequent release.  One of our clients bought a product from a vendor based on that claim only to find out that all the DB code was still using features from 2 releases ago – because of backward compatibility, it was still working but that defeats the purpose.  A proper due diligence should be done if you want to ensure that you get what you want else you might still end up buying and then implementing & supporting a product built on legacy codebase.

Posted in DB2 LUW, Oracle, SQL Server | Leave a Comment »

SQL Server 2008 R2

Posted by decipherinfosys on May 16, 2009

SQL Server 2008 R2 which you might recall as previous code of Kilimanjaro was announced at the recently concluded TechEd 2009.  There are plenty of new enhancements – take a look at the MSFT site here:

Also, their advertising video is pretty funny (the introduction part) – you can see it here.  You can also sign up to receive notifications of the CTP.

Posted in SQL Server | Leave a Comment »