Journey to SQL Authority with Pinal Dave

SQLAuthority News – Converting a Delimited String of Values into Columns

February 10, 2010 by pinaldave

This blog post is about two great bloggers and their excellent series of blog posts. It was quite unusual to see two bloggers posting articles that are supporting each other and constantly improving the articles to the next level.

Two blogs which I am going to mention here are as follows: SELECT Blog FROM Brad.Schulz CROSS APPLY SQL.Server() – Brad Schulz and Demystifying SQL Server – Adam Haines. Before continuing this blog post, I suggest you all to bookmark these blogs for future reference.

The whole thing started when Adam tried to answer the question “How to transform a delimited values into columns?” on MSDN SQL Forum. Adam made the first blog post here http://jahaines.blogspot.com/2009/06/converting-delimited-string-of-values.html and then Brad and Adam bounced a few ideas off the wall. Then, Adam started his series on concatenating column values http://jahaines.blogspot.com/2009/06/concatenating-column-values-part-1.html, and http://jahaines.blogspot.com/2009/07/concatenating-column-values-part-2.html. In part two of the series, Brad wanted to get a deeper understanding of why the results came out the way they did. Adam then started another series on unpacking or parsing out delimited characters, http://jahaines.blogspot.com/2009/11/splitting-delimited-string-part-1.html and here http://jahaines.blogspot.com/2009/12/splitting-delimited-string-part-2.html. From here, Brad started digging even deeper into the internals of the XML method. Brad has a comprehensive list of the back and forth on this post, http://bradsruminations.blogspot.com/2009/12/delimited-string-tennis-anyone.html and a final post here http://bradsruminations.blogspot.com/2010/01/delimited-string-tennis-again-final.html.

The final outcome is that both the XML method and number table can perform equally, but the XML method has to be coded in a very particular way; otherwise performance will be horrendous. Also, the permanent number table shows consistently more performance than the inline number table; however, on smaller string sizes, the difference here is that most of the inline number tables require more CPU and RAM, whereas the permanent number table requires more IO. For an average developer, the number table solution is probably the easiest of the solutions to implement.

Overall, I strongly suggest to go through the abovementioned posts; you will love it and become their fan. Please note these posts are very easy to understand and if you know a bit of XML only, you will still be able to understand them very well. The above description has been taken with proper consent of the Adam and Brad.

Hats Off to you Guys! You guys inspire me and many SQL enthusiasts!

Reference: Pinal Dave (http://blog.SQLAuthority.com)

Posted in Pinal Dave, SQL, SQL Authority, SQL Function, SQL Query, SQL Server, SQL String, SQL Tips and Tricks, SQLAuthority Website Review, T SQL, Technology | Tagged SQL XML | Leave a Comment »

SQL SERVER – Brief Note about StreamInsight – What is StreamInsight

February 9, 2010 by pinaldave

StreamInsight is a new event processing platform introduced in upcoming version SQL Server 2008 R2. Similar to other components such as SSIS, SSAS or Service Broker, it also needs to be installed along with the SQL Server.

Up to SQL Server 2005, Microsoft’s main focus on SQL Server was to build a platform to efficiently store, manage, and retrieve data. However, now, Microsoft enhanced SQL Server to accept, monitor, and respond to complex and high number of events in near zero latency. For this, Microsoft introduced StreamInsight using the following approaches:

Continuous and incremental processing of unending sequences of events.
Lightweight streaming architecture that supports highly parallel execution of continuous queries over high-speed data.
The use of in-memory caches and incremental result computation provide excellent performance with high data throughout and low latency.
Low latency is achieved because the events are processed without costly data load or storage operations in the critical processing path.
All processing is automatically triggered by incoming events. In particular, applications do not have to incur any overhead for event polling.
Static reference or historical data can be accessed and included in the low-latency analysis.

In StreamInsight, the events are processed by a CEP (complex event processing) Server. CEP Server has its own optimizer and standing queries to handle events optimally in real time. In simple terms, we can understand it as given below:

This architecture is designed to process up to 100,000 or even more events per second.

Following are few areas where this new platform would help in monitoring, mining, and analyzing the data in motion and in providing significantly more informed business decisions in real-time:

Manufacturing process monitoring and control
Clickstream analysis
Financial services
Power utilities
Health care
IT monitoring
Logistics
Telecom

Another important thing about StreamInsight is that the developers do not have to learn any new language to implement it. Its CEP Server (standing queries) can be created in LINQ (Language Integrated Queries), while the event Source application and Target application can be written in Microsoft .Net language. By using LINQ, developers familiar with SQL will be able to quickly write queries in a declarative fashion. Its deployment is also simple with flexible methods such as integrating in an application as a hosted DLL or running as a stand alone server through a wrapper such as an executable or windows service.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

Posted in DBA, Pinal Dave, SQL, SQL Authority, SQL Documentation, SQL Query, SQL Server, SQL Tips and Tricks, T SQL, Technology | Tagged StreamInsight | 2 Comments »

SQL SERVER – Find the Size of Database File – Find the Size of Log File

February 8, 2010 by pinaldave

I encountered the situation recently where I needed to find the size of the log file. When I tried to find the script by using Search@SQLAuthority.com I was not able to find the script at all. Here is the script, if you remove the WHERE condition you will find the result for all the databases.

SELECT DB_NAME(database_id) AS DatabaseName, Name AS Logical_Name, Physical_Name, (size*8)/1024 SizeMB FROM sys.master_files WHERE DB_NAME(database_id) = 'AdventureWorks' GO

Reference: Pinal Dave (http://blog.SQLAuthority.com)

Posted in Pinal Dave, SQL, SQL Authority, SQL Query, SQL Scripts, SQL Server, SQL System Table, SQL Tips and Tricks, SQLServer, T SQL, Technology | Tagged SQL Log File | 5 Comments »

SQL SERVER – SQL Server 2008 R2 Update for Developers Training Kit

February 7, 2010 by pinaldave

SQL Server 2008 R2 offers an impressive array of capabilities for developers that build upon key innovations introduced in SQL Server 2008. The SQL Server 2008 R2 Update for Developers Training Kit is ideal for developers who want to understand how to take advantage of the key improvements introduced in SQL Server 2008 and SQL Server 2008 R2 in their applications, as well as for developers who are new to SQL Server. The training kit is brought to you by Microsoft Developer and Platform Evangelism.

Download SQL Server 2008 R2 Update for Developers Training Kit

Reference: Pinal Dave (http://blog.SQLAuthority.com)

Posted in Database, SQL, SQL Authority, SQL Documentation, SQL Download, SQL Query, SQL Server, SQL Tips and Tricks, SQLAuthority News, SQLServer, T SQL, Technology | Leave a Comment »

SQLAuthority News – Presenting Two Sessions at TechED Sri Lanka

February 6, 2010 by pinaldave

I will be presenting following two sessions at TechEd Sri Lanka this week. I am very excited as this is very first time I will be presenting in TechEd event. I have previously presented many sessions but I have never presented at this premier Microsoft Event.

I will be presenting on following two subject.

The history of the Log: Change Data Capture (CDC)
Pinal Dave on 8-Feb-10 at 02.00 – 03.15
Learn to capture the history of data using CDC. An age old method of writing queries and triggers to capture change in database table is replaced with much powerful asynchronous method of change data capture (CDC). All attendees will learn how to configure CDC in less than 60 seconds.

The Other Side of SQL Server Index : Advanced Solutions to Ancient Problem
Pinal Dave on 9-Feb-10 at 02.15 – 03.15
SQL Server Index is very powerful tool and when in hand of the less skilled expert, the same tool can be very dangerous for performance and can kill server. This session touches the root base of the how incorrect usage of Index can reduce performance and what is the correct way to implement indexing solution. A problem addressed in the session may be age old but the solutions are looked into with the focus of latest versions of SQL Server.

Along with me there will be other two Solid Quality Mentors – Rushabh Mehta and Joy Ratnayake presenting on various topics. Since announcement of the event, I have already received few emails I promise that one of the evening, I will for sure meet all of you who have requested the meeting.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

Posted in MVP, Pinal Dave, SQL, SQL Authority, SQL Query, SQL Server, SQL Tips and Tricks, SQLAuthority Author Visit, SQLAuthority News, T SQL, Technology | Tagged TechEd | 5 Comments »

SQL SERVER – Stream Aggregate Showplan Operator – Reason of Compute Scalar before Stream Aggregate

February 5, 2010 by pinaldave

I keep a check on the questions received from my readers; when any question crosses my threshold, I surely try to blog about it online. Stream Aggregate is a quite commonly encountered showplan operator. I have often found it in very simple COUNT(*) operation’s execution plan. If you like to read an official note on the subject, you can read the same on Book Online over here. The Stream Aggregate operator groups rows by one or more columns and then calculates one or more aggregate expressions returned by the query.

Running the following query will give you Stream Aggregate Operator in Execution Plan. To turn on Execution Plan, press CTRL + M.

USE AdventureWorks GO SELECT COUNT(*) cRows FROM HumanResources.Shift; GO

Now if you try to see the Aggregate operation in text instead of graphic, you can find that it clearly suggests that aggregation happens because of the COUNT(*) operation.

SET SHOWPLAN_ALL ON; GO SELECT COUNT(*) cRows FROM HumanResources.Shift; GO SET SHOWPLAN_ALL OFF; GO

Notably, in the same operation, the Compute Scalar operation is also present, which in fact does the implicit conversion of the COUNT(*) operation. This operation is there because during the stream aggregation operation, the Expression is converted to BIGINT, and it is converted back to INT right after COUNT(*) operation.

Now let us try to prove that Stream Aggregate returns results in the format for BIGINT and not in INT datatype, which is leading to use of additional operation of scalar convert. We will run the above code with COUNT_BIG(*) instead of COUNT(*).

SET SHOWPLAN_ALL ON; GO SELECT COUNT_BIG(*) cRows FROM HumanResources.Shift; GO SET SHOWPLAN_ALL OFF; GO

You can clearly see from this example that there is no Compute Scalar operation when function COUNT_BIG is used in place of COUNT.

Now, the abovementioned information leads to the following question: does this mean that the performance of COUNT_BIG is better than COUNT operation. Let us compare the performance for the same by running following code.

SET SHOWPLAN_ALL ON; GO SELECT COUNT(*) cRows FROM HumanResources.Shift; GO SELECT COUNT_BIG(*) cRows FROM HumanResources.Shift; GO SET SHOWPLAN_ALL OFF; GO

From the execution plan, the cost of both the operations is exactly same. Now I would like to get your opinion on what you think of this article and your initial reaction to the behavior of SQL Server. Please do leave a comment here.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

Posted in Pinal Dave, SQL, SQL Authority, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, SQLServer, T SQL, Technology | Tagged Query Operator | 2 Comments »

SQL SERVER – Get the List of Object Dependencies – sp_depends and information_schema.routines and sys.dm_sql_referencing_entities

February 4, 2010 by pinaldave

Recently, I read a question on my friend Ritesh Shah’s SQL site regarding the following: sp_depends does not give appropriate results whereas information_schema.routines does give proper answer.

I have quite often seen that information_schema.routines gives proper dependency relationship where assp_depends returns an incorrect answer. However, as per book online sp_depends will be deprecated, and instead, sys.dm_sql_referencing_entities and sys.dm_sql_referenced_entities are recommended.

Let us quickly see where sp_depends fail and other solutions work fine.

Let us first create two scenarios.

Scenario 1: Normal Table Creation Order, where objects are created first and then used afterwords.

USE TempDB GO CREATE TABLE dbo.TestTable ( ID INT, Name VARCHAR(100)) GO -- dbo.First is not created yet CREATE PROCEDURE dbo.Second AS EXEC dbo.First GO CREATE PROCEDURE dbo.First AS SELECT ID, Name FROM TestTable GO

Scenario 2: Objects are created afterwords and they are referenced first.

USE TempDB GO CREATE TABLE dbo.TestTable ( ID INT, Name VARCHAR(100)) GO CREATE PROCEDURE dbo.First AS SELECT ID, Name FROM TestTable GO -- dbo.First is already created CREATE PROCEDURE dbo.Second AS EXEC dbo.First GO

Now let us run following three queries on both the scenarios.

-- Method 1: Using sp_depends sp_depends 'dbo.First' GO -- Method 2: Using information_schema.routines SELECT * FROM information_schema.routines ISR WHERE CHARINDEX('dbo.First', ISR.ROUTINE_DEFINITION) > 0 GO -- Method 3: Using DMV sys.dm_sql_referencing_entities SELECT referencing_schema_name, referencing_entity_name, referencing_id, referencing_class_desc, is_caller_dependent FROM sys.dm_sql_referencing_entities ('dbo.First', 'OBJECT'); GO

Result from Scenario 1

Result from Scenario 2

It is clear that sp_depends does not give proper/correct results when the object creation order is different or following deferred name resolution.

I suggest the use of the third method, in which sys.dm_sql_referencing_entities is used.

Use the following script to get correct dependency:

SELECT referencing_schema_name, referencing_entity_name, referencing_id, referencing_class_desc, is_caller_dependent FROM sys.dm_sql_referencing_entities ('YourObject', 'OBJECT'); GO

Let me know the type of scripts you use for finding Object Dependencies. I will post your script with due credit.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

Posted in Pinal Dave, Readers Question, SQL, SQL Authority, SQL Query, SQL Scripts, SQL Security, SQL Server, SQL System Table, SQL Tips and Tricks, SQLServer, T SQL, Technology | 7 Comments »

SQLAuthority News – MVP Open Day South Asia – Jan 20, 2010 – Jan 23, 2010 – Review Part Fun

February 3, 2010 by pinaldave

MVP Open Day South Asia was held in Hyderabad from Jan 20, 2010 to Jan 23, 2010. This event was a fun-filled event as well as an educational one. The event was held at Microsoft IDC at Hyderabad, the largest Microsoft Development location after Redmond. I had great time meeting my friends and some of the renowned experts from all over the South Asia. The whole event started with networking with other MVPs as well as Product Group members.

Besides lots of learning and meeting experts, this event was filled with fun too. The best thing for me was that I went there with my family. I attended the event with my wife – Nupur and my four-month-old daughter Shaivi. Though I was busy meeting my fellow MVPs and Microsoft Experts, I still had quality time to spend with my family. This event was held at Ista Hotel, Hyderabad. Nupur really liked the Spa there. While we were at sessions, she had a great time herself. She even met a couple of film stars at the hotel.

Shaivi, my little one, had great time as well. It was her first tour out of my state. She had never traveled outside our city since she was born; it was also her first air journey. She was such a nice baby and never cried during her whole journey in flight. She was one of the most popular persons at the event and also the most photographed one. Needless to say, I was the one who took most of her photographs!

There were plenty of fun programs scheduled at the event. Every evening at dinner, we had networking and gathering of the MVPs. I think this was the best time of the day as after whole day of technical sessions and techno-talk, all the MVPs and Experts are in a very light mood to have fun. Not to mention that during the same time, we could share the best with them. There was an excellent theme party arranged on one of the nights. The theme was “Qawwali.” During this party, there were loads of singing and dancing – all the MVPs and experts had loads of fun. Allen, my friend from Nepal, helped me join the dancing party.

I must say that every time I meet Windows expert Vijay Raj, I just feel that I met my lost brother. He was the first one to remind me this year that I must visit the MVP Open Day event; and he was the first one to call me on the first day of MVP Open Day asking why I have yet not reached to the Hyderabad. Shaivi indeed had great time at the event as well as she got to see many new things and new people. Abhishek Kant gave her the first piano lessons of her life! I have wonderful photographs of Abhishek teaching her piano. Jacob is quite known for teaching me SQL, but I had learned few tips on how to handle young kid as well. Sanjay Shetty, one of the Regional Directors of MS India, was one of the guys who should not be missed. We had a great time together talking about the community. He even introduced us to few other community leaders.

Suprotim Agrawal and Minal Agrawal are the very first MVP couple of India. Suprotim is great a great person; he is not only an expert in Dotnet, but also very fluent with regard to the SQL Server. We talked about community, blogs and even making money! Minal and Nupur got along very well, and as far as I know, they were busy talking about recipes. I had great pleasure to meet one of the very senior DotNet MVPs. Hima Bindu Vejella, during the event. Hima is the community leader of the Hyderabad User Group; she impressed my family with her efficiency and very polite nature. She was kind enough to share some tips with us on how one can manage home and work efficiently.

Every night, we had late night sessions, where all the “geeks” talked about technology in an extremely light mood; we, in fact, had one of the best arguments on different search engines. On one night, we had a great time learning different SEO tricks from Amit Agrawal. Amit is a great guy and a very down-to-earth person. He openly participated and shared all the knowledge with us. He did not hesitate to answer any questions which we asked. He even gave us tips on how we can improve our blogging skills; Amit took personal interest in SQLAutohrity.com too. I must confess that after meeting him, my respect for him has increased.

I cannot end the review of my event if I forget to mention the video of the Vic. Vic, as usual, is very innovative and to the point. Geetesh Bajaj, who is hails from Hyderabad but the internationally known author of Powerpoint books was as usual very helpful. All the MVPs visited Golkunda Fort and had great fun at the Sound and Light Show. I must mention that I won a portable hard drive using my Karma Points. I enjoyed my interaction with the MVPs of Sri Lanka as well; I even managed to learn a few words from their local language.

I honestly would like to express my huge thanks to all the MVPs whom I met as well as Microsoft for organizing such a great event, where all the leaders of the community came together to meet the simple goal of helping the community.

Pinal @ MVPOpenDay Hyderabad IDC

Pinal, Nupur and Shaivi Dave at Theme Party, Hyderabad

Shaivi Dave and Abhishek Kant

Shaivi, Nupur, Pinal with Rushabh Mehta

Shaivi Dave

Theme Party @ MVP Open Day Hyderabad

Suprotim and Minal Agrawal at Minal's birthday

Party Time at MVP Open Day Hyderabad

Watching Pinal Flying Kites

Reference: Pinal Dave (http://blog.SQLAuthority.com)

Posted in MVP, Pinal Dave, SQL, SQL Authority, SQL Query, SQL Server, SQL Tips and Tricks, SQL User Group, SQLAuthority Author Visit, SQLAuthority News, T SQL, Technology | 9 Comments »

SQLAuthority News – MVP Open Day South Asia – Jan 20, 2010 – Jan 23, 2010 – Review Part Business

February 2, 2010 by pinaldave

MVP Open Day South Asia was held in Hyderabad from Jan 20, 2010 to Jan 23, 2010. This event was a fun-filled as well as an educational event. This event was held at Microsoft IDC at Hyderabad – the largest Microsoft Development location after Redmond. I had a great time meeting my friends and some of the renowned experts from all over the South Asia. The whole event started with networking with other MVPs as well with Product Group members.

The reason many MVPs attended this event is that they were very keen to know the Product Team Members. We use many different Microsoft Products and a chance to meet the maker of the product is considered to be very important for any technology enthusiast. However, as soon as the euphoria of meeting the person is over, the next round starts about the rave and rants for the product. I think this is the best part of the event, which both parties appreciate the most.

We were blessed with an excellent Key Note session every day. The first day we had Technical Opening Session by SriniKoppolu (MD & VP) for MSIDC. He has made MS IDC possible in India – most of the credit goes to him. It was amazing to listen to a person like him; also, I must say that he is the owner of a very polite as well as down to earth personality. We also managed to click a group photograph with him. This session was followed by technical sessions by many different product group managers.

The second day, we had the Key Note by Rajiv Kumar, GM, MBD, MSIDC, and on the third day, the Key Note was delivered by Chandrasekar Gopalan, Director (Dev Support), GTSC. So many events occur at MS IDC; there is so much to learn from the top leadership of the Microsoft. The best part was the round table meeting with SQL Server Product Team at IDC. We were able to meet people who are behind SQL Server product. We were able to ask them our queries as well as learn few internal tricks from them. I really enjoyed the session on SQL Azure and SQL Server R2. Again, we all MVPs were not willing to leave the round table session wanting for more. Finally, Abhishek Kant managed to bring an end to the session when he saw that SQL Server Team was busy for almost 45 min after the completion of the session. Media session was one of the most interesting sessions where we all learned about how we can be part of media community.

This event had perfect ending for all the SQL Enthusiasts as SQL PASS President Rushabh Mehta himself attended the meeting on last day and addressed MVPs who are excited for SQL Server. He provided his vision for SQL PASS with South Asia as well globally. Rushabh answered so many questions for the community in the last one hour of his session, which mainly comprised of lots of questions and answers. The questions ranged from SQL events organization to SQL Awareness in the community. It was great to listen from PASS President himself about his global vision on SQL Server.

The credit for this successful event goes to SQL Server MVP Lead Abhishek Kant. This event was a very smoothly executed event, and there was not even a single boring incident. I must say that I have immensely enjoyed this event. There were lots of fun stuff at the event as well, and I will cover them in next series of this post.

Professionally, I am taking away a treasure of knowledge. Community is very important and it has no boundaries. Allen from Kathmandu, Nepal, is someone whom I respect for his continuous community efforts. I was very fortunate to learn new methods to help the community and learn tricks, where I can help more people with limited resources. I was able to talk with Jacob Sebastian for more than 6 hours during our journey and was also could enjoy the company of the greatest tech blogger of all the time, Mr. AmitAgrawal and we also discussed about his successful technical blog. Amit Agrawal is one of the most down-to-earth persons I have ever met in my life. Before I met him, I had already heard that he is very polite and helpful; so I was really eager to meet him. After the meeting, I realized that he is like an old friend to almost everyone he meets. Well, we will talk about him more in another blog post. If I could pick one person who is more excited about Windows Technology than MS itself, then it is Vijay Raj; it was great to meet him again. I was also very pleased to meet Hima Bindu Vejella from Hyderabad. Suprotim Agrawal and Minal Agrawal are great usual. Also, I enjoyed meeting many MVPs at this meeting.

In the next series, I will talk more about fun part of this event.

Pinal @ MVPOpenDay Hyderabad IDC

Abhishek Kant, Pinal Dave

Pinal Dave (SQLAuthority), Amit Agrawal (labnol)

Abhishek Kant @ MVPOpenDay Hyderabad IDC

Jacob Sebastian, Pinal Dave

Rushabh Mehta @ MVPOpenDay Hyderabad IDC

Pinal Dave (SQLAuthority), Vijay Raj (MSIGeek)

Pianl Dave, Allen Bailochan Tuladhar

Pinal Dave, Sanjay Shetty

Renuka Prasad, Pinal Dave

SQLAuthority.com

Reference: Pinal Dave (http://blog.SQLAuthority.com)

Posted in MVP, Pinal Dave, SQL, SQL Authority, SQL Query, SQL Server, SQL Tips and Tricks, SQL User Group, SQLAuthority Author Visit, SQLAuthority News, T SQL, Technology | 2 Comments »

SQL SERVER – Question – How to Convert Hex to Decimal

February 1, 2010 by pinaldave

In one of the recent projects, I realize the bottleneck of the query was an inline function which was converting Hex to Decimal. I optimized the inline function and reduced the query running time to one-tenth of the original running time. Later, I was eager to find out the script my blog readers might be using for hex to decimal conversion. Please leave your comments here and I will consider all the valid answers and publish with due credit to the author in one of the future posts. If the script you have posted here is not your original script, I suggest that you include the source as well.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

Posted in Pinal Dave, Readers Question, SQL, SQL Authority, SQL Puzzle, SQL Query, SQL Server, SQL Tips and Tricks, SQLServer, T SQL, Technology | 6 Comments »