I want to restore only a single table from my database backup. We have been hearing this request from many of our SQL Backup customers. The problem is simple: Given a large database backup and a live database that has a partial “corruption”, e.g. the data in a single table has been accidentally modified and committed, the DBA would like to have the table, and nothing else, restored. Or, another example is when a particular stored procedure has been modified, and we would like to get back the original version. This problem is a bit worse if we do not even know which backup file contains the right information.

In SQL Server the only way to solve the above problem is to restore the whole backup file to a new database, and copy the table data or the stored procedure to the current database.

Well, this problem is now history. We have just released the first public beta of SQL Compare 7. This tool, which is primarily used for comparing and synchronizing two database schemata, can now read the schema information directly from a backup file. This complements well SQL Data Compare 6.1 which already has this feature at the data level. These two products open up a lot of new recovery opportunities.

If you want to restore a few stored procedures, you just compare your live database with a backup file, and you can explore the differences in your schema objects. It may be that the modified stored procedure has a few dependent stored procedures that you also wish to restore. After selecting all the schema objects you wish to retrieve from the backup files, you can either restore them or create and save a synchronization script. When it comes to tables, Data Compare can do even more. Not only can you restore the data in a particular table, you can do this at a much finer level of granularity. First, you can see the differences between a table in the backup and the live database, and you can control which individual rows should be restored.

Anyway, back to SQL Compare 7 Beta 1. You can download it from http://www.red-gate.com/messageboard/viewtopic.php?t=6730

It was quite a challenge to implement reading schemata information from backup files. First, the core system tables are different in SQL Server 2000, 2005 and 2008. Some objects, like XML schema collections, have very cryptic representations, and there is of course backup compression in SQL Server 2008. We are still working on row and page compressions. Of course we support Red Gate’s SQL Backup files, both compressed and encrypted ones, but the tool works with SQL Server’s native backups just as well.

Another major feature for this beta is support for SQL Server 2008. Although we will not see SQL Server 2008 RTMed until at least Q3 2008, when it is out, we hope to have SQL Compare 7 ready and waiting for it. In the meantime, you are encouraged to download and try this beta. Please let us know if you find any issues with it, or if you think that there is something we have missed from this product. The forum for this beta is on http://www.red-gate.com/messageboard/viewforum.php?f=78

Regards,

  Andras

Among the large number of new features in SQL Server 2008 there are two, almost insignificant features, that I use almost constantly. These are variable initialization and the values clause of insert statements that now accept multiple rows.

When playing with SQL one inevitably declares some variables. After the declaration, and as per the SQL standard, we can use the SET statement to assign a value to a variable:

DECLARE @a int, @b int

SET @a = 1

SET @b = 2

If we do not stick to the standard, then we can use a SELECT statement to assign values to several variables in a single statement:

SELECT @a = 1, @b =2

Sometimes, using SELECT is indeed our only choice, e.g. when we want to get both the row count and the error code from the last statement. There is no SET alternative to the following statement:

SELECT @RowCount = @@ROWCOUNT, @ErrCode = @@ERROR

Anyway, SQL Server 2008 allows us to declare and initialize variables in a single statement, like:

DECLARE @a int = 1, @b int = 2

While I still do not understand why saving on the number of lines of code on my screen makes me feel better (two 19’’ screens provide me with plenty of space :) ), somehow I like this feature.

Another small extension to T-SQL that prevents me from including more typos is adding multiple rows in an insert statement:

DECLARE @table TABLE ( a INT, b INT )

INSERT INTO @table VALUES (1,1), (2,2), (3,3)

The INSERT statement here inserts three rows into the @table. This is something I got used to before, and was missing from SQL Server.

It is nice to see that in SQL Server 2008 Microsoft is adding such syntactic sugar in addition to the number of other improvements, like spatial data types, data compression, transparent data encryption, etc. I’ve been talking about these improvements at VBUG and NxtGen events during the last few weeks, so in my blog I’d also like to say thanks to the people who attended and organized these events.

SQLBits was held in Reading this Saturday and it certainly superseded my expectations. There were over 300 people, excellent talks and plenty of opportunities to meet others.

There were four parallel sessions, one for BI, Dev, Katmai and DBA, and since there is only one me, it was, as always, difficult to choose.

One thing that probably tops my list of interesting findings for the day is the “British language”. Since the event was in the UK, this was of course bound to happen. And it did happen at least four times in Dave McMahon’s entertaining session. He liked using “SET LANGUAGE ‘British’”. This is a statement that certainly increases the comfort level of SQL Server. It is useful when you want to parse date strings in your session, and these strings are following the format of a different culture. Let’s say, they are in the format commonly used in the UK. You may not want to play with SET DATEFORMAT, just type in “SET LANGUAGE ‘British’”.  Note that sys.messages is looked up based on the language_id, which in case of British is still 1033, so do not expect different error messages after using SET LANGUAGE ‘British’ :)

There were of course more interesting bits at this event, but I leave them for later. For now I just want to say thanks to Simon Sabin and Tony Rogerson for organizing the event, and of course, many thanks to those who attended my session.

Pivots in SQL Server 2005 can rotate a table, i.e. they can turn rows into columns. PIVOTs are frequently used in reports, and they are reasonably simple to work with. However, I've seen quite a few questions about this operator. Most questions were about the column list in the PIVOT statement. This list is fixed, but many times the new columns are determined by the report at a later stage. This problem is easily solved when we mix pivots with dynamic SQL, so here is a very simple example about how to dynamically generate the pivot statement:

PIVOT allows you to turn data rows into columns. For example, if you have a table like this (I use only three months here for simplicity):

CREATE TABLE Sales ([Month] VARCHAR(20) ,SaleAmount INT)

INSERT INTO Sales VALUES ('January', 100)
INSERT INTO Sales VALUES ('February', 200)
INSERT INTO Sales VALUES ('March', 300)

SELECT * FROM SALES
 

Month             SaleAmount
----------------  -----------
January           100
February          200
March             300 

Suppose we wanted to convert the above into this:

 
January     February    March
----------- ----------  ----------
100         200         300

We can do this using the PIVOT operator, as follows:

SELECT  [January]
      , [February]
      , [March]
FROM    ( SELECT    [Month]
                  , SaleAmount
          FROM      Sales
        ) p PIVOT ( SUM(SaleAmount)
                    FOR [Month] 
                      IN ([January],[February],[March])
                  ) AS pvt

However, in the above example, I have the column names fixed as the first three months. If I want to create a result in which the columns are dynamic (for example, they are read from a table), then I need to construct the above query dynamically. To demonstrate this let’s look at the following example:

In the first table I have the column names I want to use:

CREATE TABLE Table1 (ColId INT,ColName VARCHAR(10))
INSERT INTO Table1 VALUES(1, 'Country')
INSERT INTO Table1 VALUES(2, 'Month')
INSERT INTO Table1 VALUES(3, 'Day')

In the second table I have the data. This consists of a row identifier (tID), a column ID (ColID) that refers to the column type in Table1, and a value:

CREATE TABLE Table2 (tID INT,ColID INT,Txt VARCHAR(10))

INSERT INTO Table2 VALUES (1,1, 'US')
INSERT INTO Table2 VALUES (1,2, 'July')
INSERT INTO Table2 VALUES (1,3, '4')
INSERT INTO Table2 VALUES (2,1, 'US')
INSERT INTO Table2 VALUES (2,2, 'Sep')
INSERT INTO Table2 VALUES (2,3, '11')
INSERT INTO Table2 VALUES (3,1, 'US')
INSERT INTO Table2 VALUES (3,2, 'Dec')
INSERT INTO Table2 VALUES (3,3, '25')

Now I would like to retrieve data from these two tables, in the following format:

tID         Country    Day        Month
----------- ---------- ---------- ----------
1           US         4          July
2           US         11         Sep
3           US         25         Dec 

In other words I want to turn the data rows in Table2 into columns. If I had a fixed set of columns for the result, i.e. the columns Country, Day, and Month were fixed, I could use SQL Server 2005’s PIVOT operator in a query like:

SELECT  tID
      , [Country]
      , [Day]
      , [Month]
FROM    ( SELECT    t2.tID
                  , t1.ColName
                  , t2.Txt
          FROM      Table1 AS t1
                    JOIN Table2 
                       AS t2 ON t1.ColId = t2.ColID
        ) p PIVOT ( MAX([Txt])
                    FOR ColName IN ( [Country], [Day],
                                     [Month] ) ) AS pvt
ORDER BY tID ;

However I need to construct this query dynamically, because the column names Country, Day, and Month are specified in a table, and can be changed independently from my query. In our case these columns are given in Table1.

In the first step to generate the final pivot query I need to create the list of columns, in this case [Country], [Day], [Month].

Since there is no string concatenation aggregator in SQL (a concatenation aggregator would not be deterministic without some order restriction), and since the column names are stored in rows of a table, I need to flatten these columns into a single row or variable. There are various solutions to achieve this. One solution would be to use a query like:

DECLARE @cols NVARCHAR(2000)
SELECT  @cols = COALESCE(@cols + ',[' + colName + ']',
                         '[' + colName + ']')
FROM    Table1
ORDER BY colName

This query works both on SQL Server 2000 and 2005. It is efficient, but some may not like it because it uses the same variable (@cols) on both sides of an assignment. Another solution that works on SQL Server 2005 only is to use XML PATH.

DECLARE @cols NVARCHAR(2000)
SELECT  @cols = STUFF(( SELECT DISTINCT TOP 100 PERCENT
                                '],[' + t2.ColName
                        FROM    Table1 AS t2
                        ORDER BY '],[' + t2.ColName
                        FOR XML PATH('')
                      ), 1, 2, '') + ']'

This second query (I’ve seen this posted by Peter Larsson) has the advantage that it does not use the @cols variable on the right hand side. I like this solution more, since this can be extended as a general string concatenation aggregate in more complex queries.

Both of the above queries generate, from Table1, the string: ‘[Country],[Day], [Month]’. This column list is used twice in the pivot query that we aim to construct. Once it is use in the list of columns that we want to retrieve, and once it is used as the list of values that should become columns. Having constructed this list of columns above, we can just concatenate it with the missing parts of the pivot query like:

DECLARE @query NVARCHAR(4000)
SET @query = N'SELECT tID, '+
@cols +'
FROM
(SELECT  t2.tID
      , t1.ColName
      , t2.Txt
FROM    Table1 AS t1
        JOIN Table2 AS t2 ON t1.ColId = t2.ColID) p
PIVOT
(
MAX([Txt])
FOR ColName IN
( '+
@cols +' )
) AS pvt
ORDER BY tID;'

Executing this with

EXECUTE(@query)

will give us the expected result: a table that is pivoted and shows columns that were specified in a table:

tID         Country    Day        Month
----------- ---------- ---------- ----------
1           US         4          July
2           US         11         Sep
3           US         25         Dec

My current interest is focused on database evolution and on ways to manage changes to database schemata. This interest is reflected in my current project, SQL Compare 6. This version has the goal to help source control integration for databases by allowing users to handle a set of SQL creation scripts as a possible source for the database schema. However, even with this tool, it is not a simple task to set up source control, and there are many problems one needs to find a solution for. I have summarized some of these problems in an article which you can access on Simple Talk

We have just released the first public beta for SQL Compare 6.0. The full version of SQL Compare 6.0 is scheduled for release towards the end of June 2007. For the forum and download information visit http://www.red-gate.com/MessageBoard/viewforum.php?f=65

So what is new in SQL Compare 6.0 beta?

SQL Compare 6.0 beta introduces a new data source. Until version 5.3 SQL Compare could compare and synchronize live SQL Server databases and SQL Compare schema snapshot files. Version 6.0 extends this with creation scripts. You can now specify a filesystem folder with creation SQL files. These are read in as a virtual database and can be compared with another database, or creation SQL files.

This is handy when you have some SQL scripts, and want to execute them in dependency order.

But in SQL Compare 6.0 you will also be able to synchronize to scripts. This means that if you develop on a live database, you can update a set of SQL script files that represent the database schema. This opens doors for source control for your database.

In addition to comparing and synchronizing to and from object-level SQL scripts, SQL Compare 6 contains many enhancements to its user interface. We have improved searching in the comparison results as well as in the detailed differences panels. We have also improved our project selection dialogs.