SQL SERVER – SQL Puzzle of SET ANSI_NULL – Win USD 50 worth Amazon Gift Cards and Bubble Copter R/C

We all love puzzles and here is interesting puzzle which you can play with me and win Amazon Gift Cards and Bubble Copter R/C. The contest for Amazon Gift Card is open worldwide, however, Bubble Copter winner will be chosen from USA only.

First run following script:

-- Query1
SELECT 'SQLAuthority' AS Statement11
WHERE 'Authority' IN ('S','Q', 'L', 'Authority', NULL);
-- Query 2
SELECT 'SQLAuthority' AS Statement12
WHERE 'Authority' NOT IN ('S','Q', 'L', NULL);

You will get following result:

You can clearly see that in the first case we are getting different results. Here are the questions you need to answer to win the Amazon Gift Cards and Bubble Copter R/C.

There are two steps to take participate in the contest:

Step 1: Answer the Question

Question: Why do Query 1 return results but Query 2 does not return any result?

Answer this question in comments area along with the question in Step2.

Step 2: Identify File Size

Question: What is the size of the DevArt Schema Compare installation file in KB? Please leave a note in the comment area.

Please note the size of the file should be KB and not in MB. You can download the file from here.


  • 2 lucky winners will get USD 25 worth Amazon Gift Card (Open worldwide, Total giveaway value USD 50)
  • One lucky winner from USA will get  Bubble Copter R/C (Shipping from USA to other countries is not possible)

Rules and Conditions:

  • Contest open till May 25 12:00 GMT. 
  • Please leave your answer in the comment area in following format:
    • Answer to Q1:
    • Answer to Q2:
  • Please note that winner will be selected after May 25th by random selection and will be posted as a comment to this blog.
  • The answers will be kept hidden till the winner will be announced to have fair competition.

Click to Download Scripts

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

SQL SERVER – A New Approach to Scale .NET Applications

In a previous article, I wrote about scale-up vs. scale-out architectures using SQL Server and NuoDB as examples.  NuoDB recently announced the general availability of their latest product release, 1.1, and it looks like they’ve made significant progress in improving their Microsoft support. NuoDB now supports 64-bit Windows environments, natively integrates with Visual Studio, LINQ and EntityFramework to name a few.

For those of you who haven’t had a chance to read my previous article, NuoDB is a distributed cloud database that supports SQL and ACID transactions. A single logical NuoDB database can be deployed on one or many cloud machines to support the scale-out model without the need to partition, shard, or replicate.

The NuoDB distributed architecture enables you to scale computational and storage resources independently. Meaning, if your application demands higher transactional throughput, simply add more transaction engines, and if you need a highly available redundant database, add more storage managers.

A typical scale-out architecture might look like this:

Each process in the NuoDB architecture represents a virtual or physical cloud resource but the database as a whole continues to represent itself as a single logical database to any client that’s connected to it.

Many of you have been developing, deploying and maintaining .NET applications using SQL Server and might be familiar with setting up a server farm or application cluster to improve performance. As you know, this is no easy task, from both a development and deployment perspective.  I’m not advocating that you eliminate SQL Server from your stack, but let’s entertain the idea of using NuoDB as the primary database for your application backend. Since it presents itself as a single logical database to your application regardless of how many machines it might be running on, you’re able to simplify your code and your scale-out strategy. The transaction engine layer of NuoDB uses a dynamic caching mechanism to eliminate the need to shard or federate your data. Transaction engines connect directly to client applications and are able to process all transaction types (CRUD) and maintain data consistency across the entire deployment environment. This opens up the opportunity to write applications specifically designed for a scale-up architecture and leverage NuoDB to provide scale-out capabilities. Below is an example of co-locating transaction engines on each node in a server farm.

 The genius of this system is that co-located transaction engines should be the most responsive to the application. However, in the event that one or more transaction engines get tied up, the broker process automatically connects applications to the next most responsive. There is no prerequisite for the data to actually be present on the transaction engine. NuoDB uses a built-in asynchronous replication technique to get data directly from other transaction engines or storage managers.

I have been following NuoDB for some time now and with each release the technology becomes more exciting. Click here if you would like to try out the latest 64-bit version of NuoDB that includes an ADO.NET driver for Visual Studio integration. Comments welcome!

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

SQLAuthority News – Sharding or No Sharding of Database – Working on my Weekend Project

Recently I came across situation where database sharding was once again a suggested solution by architectures. Everytime I hear the word sharding I remember my earlier article about NuoDB on Shard No More – An Innovative Look at Distributed Peer-to-peer SQL Database. Sharding requires developers to think about things like rollbacks, constraints, and referential integrity across tables within their applications when these types of concerns are best handled by the database. It also makes other common operations such as joins, searches, and memory management very difficult.

Each NuoDB database consists of at least three or more processes that enable a single database to run across multiple hosts. These processes include a Broker, a Transaction Engine and a Storage Manager.  Brokers are responsible for connecting client applications to Transaction Engines and maintain a global view of the network to keep track of the multiple Transaction Engines available at any time. Transaction Engines are in-memory processes that client applications connect to for processing SQL transactions. Storage Managers are responsible for persisting data to disk and serving up records to the Transaction Managers if they don’t exist in memory.

Here is my question to you all – do you ever face a situation of sharding of your database is required? When is sharding required not a good solution?

I have downloaded NuoDB today and going to build a small application which I can play with to build a scenario where sharding is no longer required. Would you be interesting in joining me with this exercise? I would like to work along with you to build such a scenario. Download NuoDB and let us start building something interesting.

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

SQL SERVER – Discussion – Scale-up vs Scale-out Architectures

Note: NuoDB is a complete re-think of relational databases with innovative support for the Cloud’s dynamic, asynchronous nature. Download NuoDB to experience the Scaling Out scenario discussed in this blog post.

Scaling Up

There are many different ways of scaling SQL Server to accommodate more transactions and throughput. The general scale-up approach includes:

  • Adding more CPU to increase computational performance
  • Adding RAM to increase query and data caching
  • Adding more storage such as SSDs and partitioning various I/O processes to different physical disks

As long as larger machines are available and your organization has the means to purchase them, then scaling up your database to increase performance is the best route. However, even if you have the means to scale up your infrastructure you will always be bound by the availability of bigger and faster hardware. Given this limitation on Moore’s Law and other organizational concerns such variable workloads and applications contending for the same dataset, additional approaches are required.

Common examples of variable workloads and applications are OLTP systems that need to be reported on by various departmental stakeholders. Other examples include financial applications that are aggregating financial transactions, marketing systems that measure segmentation performance, and other CRM/ERP systems. Developers and administrators have come up with some standard, generally accepted methods for supporting these demands on data. They include:

  • Replication techniques to enable various applications to connect to replica databases and eliminate computational and I/O constraints on the master
  • Clustering configurations to handle failover and availability concerns
  • Vertical and horizontal database partitioning to optimize query performance

Scaling Out

The introduction of SQL Azure and its ability to be deployed on to the cloud makes scaling out an option. Many instances of SQL Azure databases can be federated to support a single scaled out application. However, this approach is really no different than what developers have been doing with MySQL for many years. Federation is simply a managed method of sharding your database that comes at a cost.  Developers begin with database sharding and then that leads into application level sharding.  More sharding/federating means more complexity in your application architecture. Things databases are very good at such as query processing and managing constraints now become the developer’s responsibility.

Scale Everything

Databases have come a long way and what Microsoft is doing with SQL Azure is amazing. Their integrated development and deployment architecture gives them a clear advantage among .NET developers, administrators, and architects. However, a major shift in database technology is underway. New players are emerging that are changing how applications are built and how they are scaled. Some of the new entrants include NoSQL and NewSQL databases. NoSQL technologies might be outside the scope of this article mainly for their lack of SQL and ACID support, so let’s focus on a new database called NuoDB. Although the company might not agree that they are a NewSQL database, I will broadly characterize them as such for the sake of simplicity.

NuoDB is a database company in Cambridge, MA that I have been following for sometime. Unlike many NewSQL technologies on the market, they have decided to take a fresh approach to building a cloud database. They call it an emergent architecture, roughly modeled around how emergence manifests itself in nature. The example they use is a flock of birds and how birds flock and migrate from point A to B without any individual bird responsible for the flock as a whole. Let me break it down in more salient terms.

NuoDB is a distributed cloud database that supports SQL and ACID transactions. A single logical NuoDB database can be deployed on one or many cloud machines to support the scale-out model without the need to partition, shard, or replicate.

Brokers act as load balancers that connect clients to transaction engines. Transactions engines are responsible for executing SQL and dynamically cache data for read/write purposes. Transaction engines are a key component of NuoDB that enable it operate in a distributed environment without partitioning or sharding. These processes are able to asynchronously communicate with each other to fetch data that might be cached in memory, eliminating the need for sharding and replication. As updates are processed, transaction engines asynchronously broadcast changes to their peers to maintain consistency. Storage managers are responsible for serving up data to transaction engines and serializing data to disk for durability. A typical scale out architecture might look like this:

Each process in the NuoDB architecture represents a virtual or physical cloud resource but the database as a whole continues to represent itself as a single logical database to any client that’s connected to it.

This article was a brief introduction to one of many new database architectures that are making their way into mainstream applications. I like NuoDB because they are actively pursuing the Microsoft community with their public .NET Beta program. If you’re like me, and are interested in new approaches to solving database problems, I’d recommend you check out NuoDB and their .NET program.

Download NuoDB to experience the Scaling Out scenario discussed in this blog post.

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

SQLAuthority News – HP Project Moonshot Interchangeable and Interlockable Servers with Elastically Scale NuoDB

HP has recently released a new product called Project Moonshot.  For anyone interested in databases, server systems, or cloud computing, the description of this new option was intriguing.  On its most basic level, Project Moonshot is a scalable server system, available in small units that are easy to enlarge and can run a variety of databases and clouds – anything you can think up.

Update: Don’t forget to scroll all the way down and read call for action. I value your opinion about NuoDB.

Basics of Project Moonshot

Here are the basics:  Moonshot consists of interchangeable and interlockable servers that are much smaller and more energy efficient than traditional models.  A single Moonshot unit is 4.3U tall and can hold 45 servers, which connect together with 1G ethernet (between individuals) and 10G Ethernet (for the whole system).  The systems, though, are available for purchase individually, configured with an Intel Atom S1260 processor, 8 G of memory and 200GB SSD or 500GB HDD.

Obviously this doesn’t appear to be a server to knock anybody’s sock off, but once you start scaling up, the possibilities are endless.  This system can scale up to 180 nodes in a single unit.  The end game with this kind of system is to provide different for servers for different jobs (like computation, storage, or memory).  You can put 45 units into a 4.3 rack-unit, and suddenly you have the computing power to reckon with, and it still energy efficient.

NuoDB and Project Moontshot

I’m sure a lot of minds are racing right now, thinking up ways to use this new technology.  Well, NuoDB certainly was.  They have been test driving this new system for the past few weeks, and have come up with some unique trials to best utilize and test Moonshot.  NuoDB designed tests to help showcase what they do best: scalable and cloud databases.

NuoDB took a real world situation in mind.  In the real world, hosts have multiple websites (like blogs) that all gets steady but low traffic.  However, if a blog is lucky, it will start attracting a lot of attention.  Attention = traffic.  A lot of traffic can seriously slow servers down if there is not enough computing power dedicated to them.  That is where a scalable system like Moonshot comes into play.  NuoDB also implemented some of their own techniques into the tests to show the full integration possibilities of Moonshot.

For example: hibernating and waking databases.  By keeping some computing power “in reserve,” Moonshot helps administrators take full advantage of all the database, assigning rules to govern when and how memory is used.  For high traffic sites or times, more power is parceled out for those needs, and less is used when less is needed.  The idea seems simple, but implementation required the Moonshot Project for it to become a reality.

This is a simple overview of a long-ranging project that looked at many different variables and what Moonshot was capable of under different circumstances.  The result, though, was that even when running 7200 active databases, they were only using about 70% of the system, leaving a huge percentage of power for extra growth and capacity.  Check out the NuoDB website for more details about this exciting new project!

Call for Action

Download NuoDB and try out their new innovation. The installation and download is extremely easy. I will be very interested to know what is your experience with NuoDB.

Further Reading

NuoDB team have been writing lots of interesting blog post on the subject of Moonshot. I strongly suggest you to read them.

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

SQLAuthority Contests – Get 50 Amazon Gift Cards – Experience NuoDB Starlings 1.0.2

SQL is the standard database language. It has been around since the 1970s. ACID (Atomic, Consistent, Isolated, and Durable) guarantees that the database tier handles the transaction processing in a consistent and reliable manner, freeing the application from managing these tasks (and freeing you from having to code them into the application), while guaranteeing the integrity of the data in the database.

NuoDB is the world’s first and only patented, elastically-scalable, SQL database built for decentralized computing resources. It is built with a focus on the elastic scalability on the cloud with 100% ACID guarantees and SQL compliance. Another interesting point about this innovative database is that it removes administration tasks like sharding, caching, clustering and performance tuning. It has almost same syntax and interface which is familiar to any other database application.

NuoDB has recently released their a version earlier this month. The product is very robust and has many new enhencements. The recent release has over 75 bugs fixes and improved stability with Amazon S3. The best part is that this database can be installed on pretty much any platform i.e. MacOS, Windows, Amazon EC2 as well on Ubuntu. They have also recently announced availability on the Amazon AWS marketplace.

50 Amazon Gift Card

NuoDB is fantastic product and the people behind the scenes are fantastic. They not only know how to build a great product they even know how to build relationships. Here is the simple contest they offered exclusively for us. There are no complex rules or difficult questions to answer. Just experience how easy and fantastic their product is.

Things to do:

1) Answer following question in comment area below:

Who is Edgar F. Codd?

2) Download NuoDB Starlings 1.0.2 before April 10, 2013

Download Now

The first 50 people who download NuoDB and answer the question above will get USD $10 Amazon Gift Card – no catch and no restriction. Just do it before April 10, 2013.

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

SQLAuthority News – NuoDB Announces Webinar Series To Demystify Cloud Data Management

For those of you who read my blog regularly, you know that I’ve been evaluating NuoDB, a new database company that is re-writing the rules for relational databases. They’ve introduced a new category – Cloud Data Management Systems (CDMS) – and are claiming to be the first and only.

In an effort to ‘demystify CDMS’, they’re kicking-off a 5 part webcast series where industry experts, analysts, customers and partners will join NuoDB to cover a range of topics related to managing data in the cloud.

If you’re curious about the difference between a CDMS and a NoSQL store, NewSQL or even a 1980s RDBMS, like I am, this would be a good webcast to attend.

The first one starts tomorrow, Feb 19, at 12:00 p.m. EST.  It features Principal Analyst from Ovum, Tony Baer, and NuoDB’s CEO Barry Morris, and promises to cover:

  • Cloud database adoption, challenges and opportunities
  • What it means to be in the Cloud vs. on the Cloud
  • 12 Rules of CDMS and the need for Emergence
  • NewSQL vs. NoSQL vs. SQL

Tony Baer leads Ovum`s Big Data research practice. Over his 25 years in the industry, Baer has studied issues of data integration, software and data architecture, middleware, and application development. Having tracked the emergence of BI and data warehousing back in the 1990s, Baer sees similar parallels emerging in the world of big data today and discusses this frequently in his blog and Twitter feed. Baer’s coverage focuses on how Big Data must become a first class citizen in the data center, IT organization, and the business. He’s definitely someone worth listening to!

And as many of you know, Barry Morris also has over 25 years of industry experience in the USA and Europe, running private and public companies ranging in scale from early startup phase to 1,000+ employees. He loves to build companies around industry-changing paradigm-shifts in technology. Another great person to hear from!

Registration is required and you can register here. I’ll be there. Will you?

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