SQLServerCentral - www.sqlservercentral.com

A community of more than 1,600,000 database professionals and growing

Featured Contents

Featured Script

The Voice of the DBA

How to understand NoSQL Databases

Today we have a guest editorial from Phil Factor as Steve is on vacation.

I’m a fan of Ayende Rahien’s series of blog posts entitled ‘World’s Smallest No SQL Database’, a ‘fully functional, scale out capable, sharding enabled No SQL Key/Value store in less than 60 lines of code’  (he had to fit both client and server code into a single PPT slide)

He describes the development of this project as  ‘literally just me throwing stuff off the top of my head’ to provide ‘very much a poor man’s solution’. It is hard to read it without smiling.  Ayende (a pen-name of Oren Eini) knows a great deal about databases, being the power behind NHibernate and the creator of RavenDB. Besides this, he is a master of satire, and a great communicator.

There is no better way of illustrating the fundamental processes of a database by sketching them out in code. Ayende walks the reader through initial amazement and mirth through some interesting illustrations of the challenges involved in building real world  databases.  How do you make your database atomic, consistent and  isolated? Ayende walks you through the processes. What is involved in dealing with concurrency? Click, click click, it is all done. Soon one gets the feeling that anyone can knock together a NoSQL database, and my goodness, you wouldn’t be the first to get that idea.

It is great fun, rather like building a working car out of lego bricks, and it is a powerful tuition aid.  It reminds me of the ‘Small C’ compiler written by Ron Cain and James Hendrix (not the guitarist, sadly) and published by Dr Dobbs  in 1980. It was a perfect way of learning how compilers worked, and a C in particular. It was an inspiration to a generation of programmers.

Phil Factor from SQLServerCentral.com

Join the debate, and respond to today's editorial on the forums


DR tip no.7: Keep test scripts handy. Make sure what you've restored is working

SQL Backup Pro

Try new SQL Backup Pro 7.4 today

Check out the latest version - and you can still get 5GB free storage in the cloud when using the new hosted backup features. Find out more.

Deployment Manager

A simpler way to deploy

Red Gate’s Deployment Manager can deploy your .NET apps, services, and databases in a single, repeatable process. Get your free Starter edition now.


Free eBook: SQL Server Concurrency

Every DBA must understand SQL Server concurrency and how to troubleshoot any issues. Kalen Delaney's eBook explains all - download it today.

Featured Contents


Using Lookup Function to Bridge Datasets in Report Builder

Jeffrey Li from SQLServerCentral.com

The report function, lookup, can be used to link two independent datesets. More »


VMware High Availability in SQL Server

Additional Articles from SQL Server Professional

With the massive number of servers running SQL Server within virtual machines (VMs), it's critically important for DBAs to understand the high availability options available when SQL Server is running within a VM. More »

Question of the Day

Today's Question (by pramod singla):

SET @x=N'
 @x.query('Data(*)') AS Complete_Data,
 @x.query('Data(root/a/c)') AS Element_c_Data;

What wll be the output of the above query?

Think you know the answer? Click here, and find out if you are right.

We keep track of your score to give you bragging rights against your peers.
This question is worth 1 point in this category: XQUERY.

Did you miss yesterday's question, TDE Setup: answer it now or check out the answer.

We'd love to give you credit for your own question and answer.
To submit a QOD, simply log in to the Contribution Center.


SQL Server 2012 Query Performance Tuning

SQL Server 2012 Query Performance Tuning leads you through understanding the causes of poor performance, how to identify them, and how to fix them. You’ll learn to be proactive in establishing performance baselines using tools like Performance Monitor and Extended Events. You’ll learn to recognize bottlenecks and defuse them before the phone rings. You’ll learn some quick solutions too, but emphasis is on designing for performance and getting it right, and upon heading off trouble before it occurs. Delight your users. Silence that ringing phone. Put the principles and lessons from SQL Server 2012 Query Performance Tuning into practice today.

Get your copy from Amazon today.

Featured Script

Convert Row to Column without using any other column for Pivot

ashish verma from SQLServerCentral.com

Consider below query:


It gives the below output:

Person: Ashish

So result is a table having 3 rows data in a single column named TEXT.

Now aim is to convert this into 3 column data having column names TEXT1, TEXT2, TEXT3. TEXT2 and TEXT3, may or may not be present. TEXT1 will always have some data. Output should be:

TEXT1                           TEXT2                              TEXT3
FOR DELIVERY            AX ENT.                  Person: Ashish   
The below query can solve this:
TEXT1, isnull(TEXT2,'') as TEXT2,isnull(TEXT3,'') as TEXT3
(SELECT 'TEXT' + convert(nvarchar(1),ROW_NUMBER() over (ORDER BY TEXT)) as ID, TEXT 
) AS PivotTable;

More »

Database Pros Who Need Your Help

Here's a few of the new posts today on the forums. To see more, visit the forums.

SQL Server 2005 : Business Intelligence

SSIS - Transfer SQL Server Objects Task - Performance - Hi, We have daily schedule to move around 20 tables which have huge data to another Database. The issue is The...

SQL Server 2005 : SQL Server 2005 General Discussion

create table valued function - i need to create a function passing those two varaibales. How do i do that? Table valued function select DISTINCT bb.level...

SQL Server 2005 : SQL Server 2005 Strategies

XMLDocuments growing bigger - I have a xml table in which it has 10 million records in it . Every day there will be approx...

SQL Server 2005 : SQL Server 2005 Integration Services

The 'OraOLEDB.Oracle.1' provider is not registered on the local machine - I have a SSIS (2005) package which was developed on a 32 bit machine. The Package connects to the oracle...

SQL Server 2005 : T-SQL (SS2K5)

SQL DB Email sending,HTML body formatting from two tables - I am working on a sql server DB mail sending task in which the mail body should be as HTML....

SQL Server 2008 : SQL Server 2008 - General

SSIS package works in BIDS Fails in In SQL AGent and Store - I have a simple SSIS package that uses and odbc connection using a dl4 driver connection. It works fine in...

Need Urgent help to get the Final Approver name in the list - Dear friends, i need an urgent help on a deadline task- I have a table- source "Pending" which has approver columns as...

creating a copy of a database for testing on - Problem. I need to make available a copy of one of the databases in our dev environment everyday to a...

capture web application username and other session info in sql trigger - Hi, I have created a trigger to audit changes to table rows from my web application. My web application is using...

SQL Query - Hi all, suppose if we have student name, and subjects so we want student name and max marks scored subject...

moving log files - Hi, I'm quite new to this SQL admin stuff. I need to move all the log files(user and system databases) on several...

Get the version and service pack details of all sql server instances - Hi, I'm trying to get the version and service pack details from all the sql server instances. I have a table...

Getting DateOfBirth of youngest person. - Hi, i have table named PersonalData.In this table i have a column DateOfBirth.I want to get the person who is the...

Unknown Traces - Keep restarting when stopped - We have an application that detects when tracing is enabled on our SQL server and it has been requested that...

SQL Server 2008 : T-SQL (SS2K8)

Detecting ALTER or CREATE PROCEDURE?? - Can't get this working - whinges about ALTER/CREATE statement syntax ?? Help? IF (SELECT count(name) FROM sys.sysobjects WHERE (type = 'P') and name = 'SPNAME')...

need help with pivot - I need to write a query without using pivot. Here is the scenario need to display summition of data yearly and...

need urgent help for sum aggregation - can some give me hint i am trying to get a statement that will perform a daily sum aggregation on...

daily sum aggretation - can some give me hint i am trying to get a statement that will perform a daily sum aggregation on...

Add one MilliSecond to each row - Hello Everyone I am trying something, that I am not sure is possible. I have a CreateDate column in a table, there...

When NULL IS NOT NULL - To all my SQL friends out there. While trying to construct a 1M row test harness I ran across an...

Case statement - I would like use case statement in select .for example, if rate =100, then 1, if it is null, then...

Temporary Functions? - I've found myself creating loads of functions recently which are typically used for one specific task and rarely required again....

SQL Server 2008 : SQL Server 2008 High Availability

After changing port on a cluster the proper way, default connection without port specified still working - I have a strange issue on a SQL 2008 cluster (non-R2) that I changed the default port on. I went...

SQL Server 2008 : SQL Server 2008 Administration

Cluster backup drives - I cannot see the main backup drive in SQL cluster when attempting to do a restore. The backups for the...

Programming : Powershell

Powershell for network path - I am totally new to powershell(just 2 days) so please bear my ignorance.Here is the code I am trying to...

Reporting Services : Reporting Services

SSRS - Sending Excel attachment & Body in same email - Hello , please advise , I have 2 data sets - 1st data set result email as in excel attachment -- Detail and 2nd...

Data Warehousing : Analysis Services

PLEASE HELP ME !!! MDX COUNT DISTINCT - Hi experts, I need your help. I have a cube with 3 dimensions - DimProduct with 3 hierarchies - Category - Sub Category - Product - DimClients...

To be removed from this list, please click here.
If you have any problems leaving the list, please contact the webmaster@sqlservercentral.com.
This newsletter was sent to you because you signed up at SQLServerCentral.com.
Feel free to forward this to any colleagues that you think might be interested.
If you have received this email from a colleague, you can register to receive it here.
This transmission is ©2013 Red Gate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved.
Contact: webmaster@sqlservercentral.com