SQLAuthority News – Proposed eGov Standards Policy – Benefit for All or Only A Chosen Few

Does the proposed eGov Standards Policy benefit all or only a chosen few?

As a wider audience comes to accept new technology, so the technology itself grows. The recent debate in India on the eGov Standards policy has been a point of contention for some time. I would like to start our discussion on this topic by posing two questions:

Question 1: Should government mandate single standards for a given technology domain?

The obvious answer would appear to be “Yes”, but the considered answer is actually “No”.

The stipulation of a “single standard” would unnecessarily restrict the technology choices for the Government and would result in the exclusion from Government projects of Indian ISV’s who do not conform to the standard. In the Indian IT ecosystem, not many software companies offer their products for free. This is not only an Indian phenomenon, but a global one too. Software is not generally free.

Question 2: Should royalty free IP in standards be used in eGovernment solutions as suggested in section 5.1.1?

Once again, the obvious answer would appear to be “Yes”, but the considered answer is actually “No”.

If everything is free, who will pay the bills? We all know that in life there is no such thing as a free lunch. To build a sustainable standard takes time, people and money. The Ethernet standards, developed in the IEEE in the IEEE 802 series, provide us with a good example of this. If everything has to be royalty-free, how is the inventor to be paid for his efforts? It is simply impractical to expect everything to be free.

Open Standard vs. Open Source

The real eGovernment debate revolves around understanding the true meaning of “Open Standards”. Wikipedia is a great source on the subject and provides various points of view.

A clear understanding of the differences between Open Standards and Open Source is necessary. Open Standards can be used both in Open Source and paid technologies. For example, UNICODE is standardized under RAND terms and is used in both Windows and Linux. Sadly, Open Source has become a tool of exploitation in hands of the few who usurp the claim on Open Standard.

Facing Reality

For Government procurements, the lowest bidder is generally awarded the contract. The “artificial” imposition of a single royalty-free standard would preclude Government Departments from selecting solutions on a value-for-money basis. Many eGovernment Interoperability Frameworks across the globe do not impose such stipulations for the very reason that they have adverse effects on interoperability.

A number of organizations offer their software for free, but profit by charging surreptitiously for support. To my mind, it is better to be upfront and open about the real costs. Many have found themselves locked into agreements, only to be shocked by the actual long-term costs. This is what most Open Source implementations end up doing.

By towing the “free line”, effectively the Government is siding with the Open Source philosophy and killing vendors who charge for their software upfront. This is not in the best interests of the emerging software economy, nor the development of intellectual property.

The debate on standards is NOT purely academic in nature. If this standard is passed, Open Source commercial vendors stand to gain millions in Government contracts. Please refer to section 7.4 on this point. The debate is also NOT about what is good or bad. It is really a debate on how the proponents of a single standard will secure their profits from the Government.

The proponents of a single standard have a loud voice, and they appeal loudly to higher authorities and institutions. I believe that this should come to and end. The real issue, i.e. the confusion between Open Standards and Open Source needs to be clarified before a crucial error is made.

Suggestion for Better eGovernment Standard Policy

As we have seen, the term “open standard” means different things to different bodies. While I support the Indian Government’s goals to achieve interoperability for e-government, I recommend the following amendments to the eGov Standards policy:

  1. Remove section 5.1.4, because to prove that a particular standard is better than all other existing standard is virtually impossible to do objectively.
  2. Remove all preferences in section 5.2 for Open Source as they only favor commercial Open Source companies.
  3. If preference needs to be given at all, give them to software developed in India by Indian companies. As an added advantage, the Government can more tightly regulate these companies.
  4. To let the taxpayer’s money be utilized most effectively and to meet the objectives detailed in section 3.4, the committee should think of including a “lowest cost preference” clause in section 5.
  5. Define Open Extensions and/or Open Subsets clearly.

I sincerely urge the intellectuals of this country to climb down from “socialist” viewpoints and ensure that the IT the ecosystem is best leveraged to the benefit of the nation.

To better e-governance!

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

SQL SERVER – Introduction to Cloud Computing


“Cloud Computing,” to put it simply, means “Internet Computing.” The Internet is commonly visualized as clouds; hence the term “cloud computing” for computation done through the Internet. With Cloud Computing users can access database resources via the Internet from anywhere, for as long as they need, without worrying about any maintenance or management of actual resources. Besides, databases in cloud are very dynamic and scalable.

Cloud computing is unlike grid computing, utility computing, or autonomic computing. In fact, it is a very independent platform in terms of computing. The best example of cloud computing is Google Apps where any application can be accessed using a browser and it can be deployed on thousands of computer through the Internet.

Key Characteristics

Cloud computing is cost-effective. Here, cost is greatly reduced as initial expense and recurring expenses are much lower than traditional computing. Maintenance cost is reduced as a third party maintains everything from running the cloud to storing data. Cloud is characterized by features such as platform, location and device independency, which make it easily adoptable for all sizes of businesses, in particular small and mid-sized. However, owing to redundancy of computer system networks and storage system cloud may not be reliable for data, but it scores well as far as security is concerned. In cloud computing, security is tremendously improved because of a superior technology security system, which is now easily available and affordable. Yet another important characteristic of cloud is scalability, which is achieved through server virtualization.

In a nutshell, cloud computing means getting the best performing system with the best value for money.

Cloud Computing Architecture

Cloud computing architecture, just like any other system, is categorized into two main sections: Front End and Back End. Front End can be end user or client or any application (i.e. web browser etc.) which is using cloud services. Back End is the network of servers with any computer program and data storage system. It is usually assumed that cloud contains infinite storage capacity for any software available in market. Cloud has different applications that are hosted on their own dedicated server farms.

Cloud has centralized server administration system. Centralized server administers the system, balances client supply, adjusts demands, monitors traffic and avoids congestion. This server follows protocols, commonly known as middleware. Middleware controls the communication of cloud network among them.

Cloud Architecture runs on a very important assumption, which is mostly true. The assumption is that the demand for resources is not always consistent from client to cloud. Because of this reason the servers of cloud are unable to run at their full capacity. To avoid this scenario, server virtualization technique is applied. In sever virtualization, all physical servers are virtualized and they run multiple servers with either same or different application. As one physical server acts as multiple physical servers, it curtails the need for more physical machines.

As a matter of fact, data is the most important part of cloud computing; thus, data security is the top most priority in all the data operations of cloud. Here, all the data are backed up at multiple locations. This astoundingly increases the data storage to multiple times in cloud compared with a regular system. Redundancy of data is crucial, which is a must-have attribute of cloud computing.

Different forms of Cloud Computing

Google Apps., Salesforce.com, Zoho Office and various other online applications use cloud computing as Software-As-Service (SAAS) model. These applications are delivered through browser, and multiple customers can access it from various locations. This model has become the most common form of cloud computing because it is beneficial and practical for both the customers and the services providers. For customers, there is no upfront investment and they can Pay-As-They-Go and Pay-As-They-Grow. On the other hand, the service providers, can grow easily as their customer base grows.

Aamzon.com, Sun and IBM offer on-demand storage and computing resources. Web service and APIs enable developers to use all the cloud from internet and allow them to create large-scale, full-featured application. Cloud is not simply limited to providing data storage or computing resources, it can also provide managed services or specific application services through web.

Cloud Computing Concerns

Security of confidential data (e.g., SSN or Credit Card Numbers) is a very important area of concern as it can make way for very big problems if unauthorized users get access to it. Misuse of data can create big issues; hence, in cloud computing it is very important to be aware of data administrators and their extent of data access rights. Large organizations dealing with sensitive data often have well laid out regulatory compliance policies. However, these polices should be verified prior to engaging them in cloud computing. There is a possibility that in cloud computing network, sometimes the network utilizes resources from another country or they might not be fully protected; hence, the need arises for appropriate regulatory compliance policies.

In cloud computing, it is very common to store data of multiple customers at one common location. Cloud computing should have proper techniques where data is segregated properly for data security and confidentiality. Care must be taken to ensure that one customer’s data does not affect another customer’s data. In addition, Cloud computing providers must be equipped with proper disaster recovery policies to deal with any unfortunate event.

Selection of Provider

A good service provider is the key to good service. So, it is imperative to select the right service provider. One must make sure that the provider is reliable, well-reputed for their customer service and should have a proven track record in IT- related ventures. The Cloud Computing Incidents Database (CCID) records and monitors verifiable, noteworthy events that impact cloud computing providers. Visit the following Wikipedia link to obtain the list all such events. http://wiki.cloudcommunity.org/wiki/CCID

Relational Database and Cloud Computing

Comparison has often been drawn between Relational Database and Cloud Computing. They are related for sure but they should not be confused for being the same thing. In actual fact, they are not really competing with each other. There are some unique requirements of applications when they do not call for any advance query techniques but rather need fast access to database. In such scenarios cloud computing should be used. In cloud, data is stored across myriad geographic locations, and processing data from different geographic database leads to delay in receiving data. In case of applications where there is the need of processing huge database using complex queries, traditional relational database is best suited. Cloud has its limitations. As for now, it only supports distributed computing; transactional operations are not currently supported in cloud computing.


Cloud Computing is the next big thing in the arena of computing and storage. There are some concerns about security and its availability. However, different service providers are coming up with various solutions and suggestions in response to customers’ concerns. In any case, cloud is getting bigger and better, and as long as they are available through web services, without capital infrastructure investment at reasonable price, it is for sure going to proliferate and create robust demand in times to come.

Additional Reads

While writing this article, I really enjoyed reading Cloud Computing Manifesto (http://wiki.cloudcommunity.org/wiki/Cloud_Computing_Manifesto ) – public declaration of principles and intentions for cloud computing. Manifesto suggests 10 principles of cloud computing, namely User centric, Philanthropic, Openness, Transparency, Interoperability, Representation, Discrimination, Evolution, Balance, and Security.

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

SQLAuthority News – Author’s Birthday – Top 7 Commenters – Volunteers

Today is July 30 and I am very happy; it’s my Birthday, celebration time!!! The most common question I receive on my every birthday is -what are my plans for birthday. Let me share my plans here today. Additionally, if you are interested to know when SQL Server was born read my post SQLAuthority News – Author BirthDay – SQL Server Birthday.

My first plan is that I am going to take a break from blogging on anything technical today and spend more time with my family.

Let me tell you about my second plan. I am very much pleased and encouraged with active participation from many of you on this blog. I have received so far more than 10s of thousands of comments on my blog. These comments are open for you all to read and you can use them in any fashion that can help you out. Without good participation there is no life in any community, whether it is offline or online.

For the same reason, I have introduced Top 7 Commenters section in the side bar of SQLAuthority.com. There are many active users who have been participating for a long time. One of them I just recognized is Imran Mohammed. This SQL Expert has been extremely helpful to the community and has been providing comprehensive answers to hundreds of questions. Hats Off to you Imran! I totally appreciate your knowledge and dedication to SQL community. Without passion and love toward helping community, this is absolutely not possible.

You can subscribe to SQLAuthority.com feed, which will display all the new posts of my blog as they are published.

You can subscribe to SQLAuthority.com comments, which will show all the new comments posted on the blog as they are published.

If you prefer to receive SQLAuthority.com articles in email, you will get all the fresh posts in email.

There is no automated feature for top commenters where I host, so I will have to calculate this manually. I will keep it updated on weekly basis. Every week we will see new names there. First column is number of post, and second column is number name with link to experts website or blog or email.

Every day I receive a couple of emails about starting a forum on SQLAuthority.com. I do plan to start the forum in future. Currently, there are plenty of very good forums on the web. SQLAuthority.com is a blog and below every blog post there is a discussion related to the subject discussed in the blog post.

SQLAuthority.com is all about community and people who are passionate about SQL. Everyone is welcome to participate here. There are many ways to participate.

  • Discussing blog post
  • Helping user who have posted their issues on blog
  • Asking questions
  • Writing Guest Post
  • Submitting useful SQL Scripts
  • Reviewing any Microsoft-related products
  • Reviewing Books

Let me know if you have any other useful and interesting suggestions on how I can involve YOU more in my efforts to take SQL community to the next level.

If you are interested in participating in volunteer program do send me email at pinal ‘at’ SQLAuthority.com and we can discuss something out.

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

SQL SERVER – Server Type and File Extention

Owing to my personal experience so far, I can undeniably say that Microsoft Windows products are outstanding. One of the reasons that make them exceptional is their little nifty tricks. For instance, every time I double click myfilename.sql it opens Microsoft SQL Server Management Studio (SSMS). The reason how Windows discerns that it has to open SSMS is because the extension of file I had clicked is .sql. I explored and found that SQL Server has few more filetypes associated with it, which are as follows.

SQL Server - .sql
SQL Server Compact 3.5 SP1 - .sqlce
SQL Server Analysis Service – .mdx, .xmla

Moving on, I would like to talk about another command that can open SQL Server Management Studio. Just type sqlwb in command prompt to open SSMS and bring credential prompt on screen.

I hope you find this article useful. Let me have your feedback so that we can take this topic further.

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

SQL SERVER – Logical Query Processing Phases – Order of Statement Execution

You can download the poster from Itzik Ben-Gan’s T-SQL Querying page over here.

Of late, I penned down an article – SQL SERVER – Interesting Observation of ON Clause on LEFT JOIN – How ON Clause Effects Resultset in LEFT JOIN – which received a very intriguing comment from one of my regular blog readers Craig. According to him this phenomenon happens due to Logical Query Processing. His comment instigated a question in my mind. I have put forth this question to all my readers at the end of the article. Let me first give you an introduction to Logical Query Processing Phase.

What actually sets SQL Server apart from other programming languages is the way SQL Server processes its code. Generally, most programming languages process statement from top to bottom. By contrast, SQL Server processes them in a unique order which is known as Logical Query Processing Phase. These phases generate a series of virtual tables with each virtual table feeding into the next phase (virtual tables not viewable). These phases and their orders are given as follows:

2. ON
11. TOP

As OUTER join is applied subsequent to ON clause, all rows eliminated by the ON clause will still be included by the OUTER join as described in the article SQL SERVER – Interesting Observation of ON Clause on LEFT JOIN – How ON Clause Effects Resultset in LEFT JOIN.

However, I am perplexed about the last two, ORDER BY and TOP. According to some people TOP comes first in logical query processing phase while others suggest that ORDER BY comes first. Now, here I’ve laid down my questions for you all to think about:

1) What is the correct answer for order query processing phase – ORDER BY or TOP?
2) How can we create an example to verify query processing phase for ORDER BY and TOP?

I will soon publish the answers I receive to the above questions on this blog, with due credit given to my readers.

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

SQLAuthority News – Top 10 Strategic Technologies for 2009

Gartner, Inc. analysts highlighted the top 10 technologies and trends that will be strategic for most organizations. Factors that denote significant impact include a high potential for disruption to IT or the business, the need for a major dollar investment, or the risk of being late to adopt.

The top 10 strategic technologies for 2009 include:

Virtualization. Much of the current buzz is focused on server virtualization, but virtualization in storage and client devices is also moving rapidly.

Cloud Computing. Cloud computing is a style of computing that characterizes a model in which providers deliver a variety of IT-enabled capabilities to consumers

ServersBeyond Blades. Servers are evolving beyond the blade server stage that exists today. This evolution will simplify the provisioning of capacity to meet growing needs.

Web-Oriented Architectures. The Internet is arguably the best example of an agile, interoperable and scalable service-oriented environment in existence.

EnterpriseMashups. Enterprises are now investigating taking mashups from cool Web hobby to enterprise-class systems to augment their models for delivering and managing applications.

Specialized Systems. Appliances have been used to accomplish IT purposes, but only with a few classes of function have appliances prevailed.

Social Software and Social Networking. Social software includes a broad range of technologies, such as social networking, social collaboration, social media and social validation.

Unified Communications. During the next five years, the number of different communications vendors with which a typical organization works with will be reduced by at least 50 percent.

Business Intelligence. Business Intelligence (BI) have a direct positive impact on a company’s business performance, dramatically improving its ability to accomplish its mission by making smarter decisions at every level of the business from corporate strategy to operational processes.

Green IT. Shifting to more efficient products and approaches can allow for more equipment to fit within an energy footprint, or to fit into a previously filled center.

Reference : Pinal Dave (http://blog.sqlauthority.com), Gartner Inc.(Published after receiving permission)

SQL SERVER – Interesting Interview Questions – Revisited

I really enjoyed users participation in my previous question. Read SQL SERVER – Interesting Interview Questions before continuing reading this article. This interview question was about user participation and about how good and how different you can come with your T-SQL script. What I really liked is that many users took this test seriously and did their best to answer. I really want to congratulate all the readers who have attempted to answer this question.

As I have said earlier it did not matter what is the database structure, but it mattered what should be the good database architecture design. Here it only mattered if you can write T-SQL based on question.

Following readers have got correct answer.



Imran Mohammed




I really want to thank and congratulate readers who have answered this question correct. I would suggest that you book mark this article as well original article for your personal reference. In future if you need ever recommendation regarding any interview I will be happy to list this articles for you in reference.

Now let us see their solution in detail.Please go over interview question again before continuing reading following comments.

Solution byZod

DECLARE @Class1 INT, @Class2 INT, @Class3 INT,
@Class1Total INT, @Class2Total INT, @Class3Total INT
@Class1Total = 0
SET @Class2Total = 0
SET @Class3Total = 0
Class1, Class2, Class3
FROM StudentsEnroll
FETCH next FROM student_CURSOR
INTO @Class1, @Class2, @Class3
@Class1Total = @Class1Total + @Class1
SET @Class2Total = @Class2Total + @Class2
SET @Class3Total = @Class3Total + @Class3
FETCH next FROM student_CURSOR
INTO @Class1, @Class2, @Class3
SELECT 'Class1 has ' + CAST(@Class1Total AS VARCHAR(10)) + ' students'
SELECT 'Class2 has ' + CAST(@Class2Total AS VARCHAR(10)) + ' students'
SELECT 'Class3 has ' + CAST(@Class3Total AS VARCHAR(10)) + ' students'

Solution by Eric

'Class1 has ' + CAST(SUM(CAST([Class1] AS INT)) AS VARCHAR(10)) + ' students.\n'
+ 'Class2 has ' + CAST(SUM(CAST([Class2] AS INT)) AS VARCHAR(10)) + ' students.\n'
+ 'Class3 has ' + CAST(SUM(CAST([Class3] AS INT)) AS VARCHAR(10)) + ' students.\n'
FROM [StudentsEnroll];

Solution by Imran Mohammed

SELECT CLASS+ ' has '+CONVERT(VARCHAR(10),COUNT(orders ))+' Students ' 'Output' FROM (
SELECT students, Class, Orders
(SELECT students, class1, class2,class3
FROM studentsenroll ) p
(Orders FOR Class IN
(class1, class2, class3 )
AS unpvt) X WHERE orders = 1 GROUP BY class

Solution by bhadeliaimran

SET @opXml = '<ClassStud value=''' + REPLACE(
'Class1 has ' + CAST(SUM(CAST(Class1 AS SMALLINT)) AS VARCHAR(4)) + ' students' ,
',Class2 has ' + CAST(SUM(CAST(Class2 AS SMALLINT)) AS VARCHAR(4)) + ' students' ,
',Class3 has ' + CAST(SUM(CAST(Class3 AS SMALLINT)) AS VARCHAR(4)) + ' students'
FROM [StudentsEnroll]
FOR XML PATH('') ), ',', ''' /><ClassStud value=''') + ''' />'
SELECT x.value('@value', 'varchar(100)') AS [output]
FROM @opXml.nodes('/ClassStud') AS p(x)

Solution by fly

INSERT INTO @tbl (i)
CASE WHEN i = 1 THEN 'Class 1 has ' + CAST(SUM(CAST (class1 AS INT)) AS VARCHAR(100)) + ' Students' ELSE
CASE WHEN i = 2 THEN 'Class 2 has ' + CAST(SUM(CAST (class2 AS INT)) AS VARCHAR(100)) + ' Students' ELSE
CASE WHEN i = 3 THEN 'Class 3 has ' + CAST(SUM(CAST (class3 AS INT)) AS VARCHAR(100)) + ' Students' ELSE '' END
JOIN @tbl t ON i <= 3

Solution by pom

SELECT 'class1 has ' + CAST((SELECT COUNT(*) FROM StudentsEnroll WHERE class1=1 GROUP BY class1) AS CHAR(3)) + 'Students'
'class2 has ' + CAST((SELECT COUNT(*) FROM StudentsEnroll WHERE class2=1 GROUP BY class2) AS CHAR(3)) + 'Students'
'class3 has ' + CAST((SELECT COUNT(*) FROM StudentsEnroll WHERE class3=1 GROUP BY class3) AS CHAR(3)) + 'Students'

Other two solution proposed earlier in original article.

Solution 1 – Using only SELECT statement

@Col = 1
WHILE (@Col < 4)
(‘SELECT     ”Class’+@Col+‘ Has ” + CAST(COUNT(Students) AS VARCHAR(100)) + ” Students” Results
FROM         studentsenroll
WHERE         Class’
+@Col+‘ = 1
GROUP BY      Class’
SET @Col = @Col + 1

Solution 2 – Getting results in one resultset

SELECT ‘Class1 has ’ + CAST(COUNT(*) AS VARCHAR(10)) +‘ Students’
FROM StudentsEnroll
WHERE Class1 = 1
SELECT ‘Class2 has ’ + CAST(COUNT(*) AS VARCHAR(10)) +‘ Students’
FROM StudentsEnroll
WHERE Class2 = 1
SELECT ‘Class3 has ’ + CAST(COUNT(*) AS VARCHAR(10)) +‘ Students’
FROM StudentsEnroll
WHERE Class3 = 1

Now the real question is which of this articles are favorite solution of yours. Please leave your choice along with reason in comment.

I promise if you can not write solution but if you understand the proposed solution in this article you will usually pass any database T-SQL problem in interviews. Additionally, if you are looking for job in SQL Server related area please find your right job here. Make sure to read all the interview questions and answers for SQL Server 2008 before you appear for interview.

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