Andras

Software Architect - Red Gate Software

  • SQL Server 2008: New languages and older operating systems

    Posted Monday, April 21, 2008 11:36 AM | 2 Comments

    SQL Server 2008 introduces a few new languages like Welsh, Tibetan and Norwegian. While these new languages map to the codepages in Windows Server 2008, in earlier operating systems (like XP) this is not so, and simple queries like “select * from mytable” can break with the SQL error 4078.


    The problem occurs if you use a collation that uses one of the new languages in a char, varchar or text column. In queries that return such a non-unicode column SQL Server relies on the client to use the appropriate codepage when displaying or converting the returned characters to Unicode. If the codepage does not exist on the client side, the query will fail. For example, in the following we declare a table variable with a varchar column that uses the new FRISIAN_100_CS_AS collation.


    DECLARE @t TABLE (

          id INT PRIMARY KEY IDENTITY(1, 1),

          data VARCHAR(100) COLLATE FRISIAN _100_CS_AS)

                                                                  

    INSERT  INTO @t VALUES  ( N'aaa' )

    SELECT data FROM @t


    The above statement will work fine on the computer on which SQL Server 2008 is installed. If you execute it in Query Analyzer on a computer with Windows XP, then you will get the following error:


    Server: Msg 4078, Level 16, State 1, Line 13

    The statement failed because column 'data' (ID=0) uses collation Welsh_100_CS_AS, which is not recognized by older client drivers. Try upgrading the client operating system or applying a service update to the database client software, or use a different collation. See SQL Server Books Online for more information on changing collations.


    (If it was run from a .Net application, the SqlException exception message is: “The Collation specified by SQL Server is not supported.”)


    (I use Query Analyzer only because with CTP6 of SQL Server 2008 you cannot use Management Studio 2005. However, you can use osql, sqlcmd, Query Analyzer, your own applications, etc.)


    The same problem occurs with .Net applications. Whether an application that executes the above query will actually work will depend on what operating system it is executed on.


    A reasonably simple workaround is not to use select *, and when querying columns that are based on the char, varchar, or text, cast them to nchar, nvarchar or ntext. In this case the data will be returned in Unicode (actually UCS-2), and the query will work on pre Windows Server 2008 systems. In a .Net application you would also need to collate the result to a known collation, like latin1_general.

     

    The new languages in SQL Server 2008 that seem to be affected are:


    Chinese_Traditional_Stroke_Count, Chinese_Traditional_Bopomofo, Chinese_Simplified_Pinyin, Chinese_Simplified_Stroke_Order, Chinese_Traditional_Pinyin, Chinese_Traditional_Stroke_Order, Danish_Greenlandic, Japanese_XJIS, Japanese_Bushu_Kakusu, Norwegian, Romansh, Serbian_Latin, Serbian_Cyrillic, Bosnian_Latin, Bosnian_Cyrillic, Urdu, Persian, Mapudungan, Upper_Sorbian, Bashkir, Maltese, Sami_Norway, Sami_Sweden_Finland, Turkmen, Bengali, Assamese, Pashto, Tibetan, Welsh, Khmer, Lao, Frisian, Tamazight, Nepali, Azeri_Latin, Azeri_Cyrillic

  • SQL Compare 7 Beta and peeking into SQL Server backup files

    Posted Friday, March 28, 2008 10:51 AM | 2 Comments

    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

  • SQL Server 2008 CTP6 takes a day off

    Posted Friday, February 29, 2008 4:55 PM | 1 Comments

    SQL Server 2008 CTP6 does not work on February 29th. Period.

    On one hand, it is really nice that Microsoft is releasing Community Technology Previews for its latest SQL Server. It helps developers and DBAs to test their solutions and get familiar with the next version of SQL Server. On the other hand, since Microsoft still has many months before the final release, there are many bugs in these CTPs. This is certainly expected, this is the point of these CTPs. But there was one bug that nearly killed me.

    As a last minute preparation for tomorrow’s SQL Bits event in Birmingham, I decided to check that my demo scripts work properly. I tried to connect to my SQL Server instance on my laptop, and was greeted with an unpleasant error message. Initially I was not worried, after a few minutes of fluffing around I started to blame the screen capturing application I’ve been asked to install for the event. After some more headache, I though, it was not worth the time, so I installed SQL Server 2008 CTP6 on a second laptop. Surprise! The installation has failed too. Same happened when I tried to install it on a virtual server, etc. So now, after three hours wasted, I’ve found a relevant bug report https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=330614

     

    Apparently, SQL Server 2008 CTP6 does not work on 29 February. It is ironic that this is just two days after the launch of the product (the launch has nothing to do with the release).

    Changing the system time to 1 March has temporarily resolved this issue :), but the bald patch on my head has grown today.

  • Type less in SQL Server 2008 (variable initialization and multiple row inserts)

    Posted Thursday, February 28, 2008 9:34 AM | 1 Comments

     

    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.

  • Can I redeclare a variable in T-SQL? Teaser for SQL Bits

    Posted Friday, February 15, 2008 1:17 PM | 2 Comments

    Variables in T-SQL behave differently from what people may be used to in languages like C or C#. The scope of a variable is the batch in which it is declared. This may not be intuitive to people who are used to local variables. So let’s look at an interesting example:

     

    DECLARE @counter INT

    SET @counter = 0

     

    WHILE @counter < 100

        BEGIN

            DECLARE @value INT

            SET @value = ISNULL(@value, 0) + 5

            SET @counter = @counter + 1

        END

     

    SELECT  @value

     

     

    In this example we have a simple loop. The loop counter is a variable @counter. What is interesting that inside the loop we have a variable declaraion for @variable. We assign it a value (if it was null we set it to 5, if it was not null, we increment it by 5). Does this make sense? How could I ever consider the previous value if I’ve just declared the variable? What is unusual for many developers, is that the declaration is evaluated only once. This means that in the second iteration of the loop the @value variable is already declared and set, so we are incrementing it by five in every subsequent iteration.

    At the end of the loop we can access the @value variable (do not forget that the scope is the whole batch), and the value will be 500 (100 iterations, 100 times adding 5).

    Variable declarations are evaluated at the time of parsing, and the scope is the whole batch. Because of this, you cannot have two declare statements for the same variable in a batch, even if the only one declaration is reachable in the code. For example, the following statement will result in a parsing error:

    IF 1 = 1

        DECLARE @a INT

    ELSE

        DECLARE @a INT

     

    The error is:

    Msg 134, Level 15, State 1, Line 4

    The variable name '@a' has already been declared. Variable names must be unique within a query batch or stored procedure.

     

     

    If you would like to know more about how transient data, like the above variable, is handled in SQL Server, come to my session at SQL Bits in Birmingham (UK) on 1 March, 2008. Like the previous SQL Bits event it is free. The last one was held in Reading half a year ago, and with 20 sessions and over 300 people attending it, it provides a unique opportunity to learn about SQL Server, as well as to meet your fellow DBAs and SQL developers in the UK. I will talk about transient data in SQL Server, and you can learn about what is stored in tempdb, how the tempdb can be a performance bottleneck, what the differences between temporary tables and table variables are, what is stored in the transaction log file and how its can size be kept under control.

  • Some interesting, obscure (and absolutely useless) T-SQL syntax

    Posted Wednesday, November 28, 2007 10:05 AM | 3 Comments

    /*
    Writing ugly SQL Statements is an art. Today I've spent
    a few minutes trying to break our parser as well as giving
    a grill to SQL Server's parser.
    So here is some extra syntax that is not behaving as
    I'd expect it to behave.
    */

    select 1.a

    -- Yes, this is legal, surprise surprise it returns a
    -- table with a single column, a single row, the value
    -- is 1, the column name is "a" !

    -- So let's push it a bit further:

    select -1.a

    /*
    Works, the result is:
    a
    ---------------------------------------
    -1
    */

    select -1/2.[-1/2]

    /*
    Wow, this works too, and it gives me
    -1/2
    ---------------------------------------
    -0.500000

    So can I just put anything after the ?decimal? point?
    */

    select 1.SomeString

    /*
    SomeString
    ---------------------------------------
    1
    */


    -- But then there are exceptions:
    select 1.a, 1.e, 0.a

    /*
    a                                  a
    ----------- ---------------------- ----------------
    1           1                      0.0
    sa

    1.e does not produce the alias (e for exponent???),
    0.a seems to use float, so the value displayed is 0.0

    It is a shame that you cannot use this with string literals.
    Unless you want to torture your colleagues I reckon that
    the above is absolutely useless. But if you have an idea why
    the above works, and works the way it works, do let me know.

    PS: the above syntax works on 2000,2005 and 2008.

      Andras
    */

  • SQL Server 2008: Microsoft has given, and Microsoft has taken away (powersum)

    Posted Thursday, November 22, 2007 12:14 PM | 8 Comments

    SQL Server 2008 November CTP came out this week. This CTP finally contains many major features that are worth playing with. However, there are things that are no longer in SQL Server, things that I will be missing (and many things that I’ll be happy to see go).

     

    One of my favourite system functions is powersum. This is not documented, so Microsoft cannot be blamed at all for removing it (Well, not really removing it, but just denying public access to it. It is very useful, so Microsoft keep using it internally). However, I liked using this on 2005 to create bit arrays from numbers. Powersum is an aggregate. It takes numbers, and it returns a varbinary. It takes its input number, and SHIFTs 1 to the left by this number, then ORs this with the aggregated value.


    For example the following query returns 0x16:


    SELECT  powersum(col)

    FROM    ( SELECT    1

              UNION ALL

              SELECT    2

              UNION ALL

              SELECT    4

            ) AS data ( col )

     

    0x16’s binary representation is 10110 (so the bits at position 1, 2 and 4 are set).


    I’m really sorry to see this function go. I used it when returning column permissions in SQL Compare. It allowed me to group column permissions together, consequently my queries were sending less data. It also allowed building query results in 2005 that were similar to SQL Server 2000’s syspermissions table.

     

    However, the access restriction to this useful function reminds me that while there are new features in SQL Server 2008, some obsolete features and syntax will go away, just like it has happened with SQL Server 2005. I’ve seen many people finally migrating to the “new” join syntax, and abandoning the =* syntax in 2005. Since SQL Server 2008 does not support compatibility level 70 and before, this syntax will finally die out.

     

    2008 also seems to remove the DUMP and LOAD keywords. People should use BACKUP and RESTORE instead, and it is a good time to look at all those old maintenance jobs.

     

    Books online mentions a few other changes that we should expect in SQL Server 2008. (The page is Deprecated Database Engine Features in SQL Server 2005). However this list is likely to change. For example this page claims that SETUSER will be removed from 2008, but it still seems to work (let’s not forget though that SQL Server 2008 is not yet released). It is worth however to keep a close eye on this page as well as the "Discontinued Database Engine Functionality in SQL Server 2008" page in 2008’s Books online.

  • SQLBits in Reading and the British language

    Posted Monday, October 08, 2007 9:35 AM | 0 Comments

    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.

  • The GO command can have a parameter?

    Posted Monday, September 24, 2007 1:57 PM | 2 Comments

    I have mixed feelings about the GO command. It is not a T-SQL statement, it is just something that Management Studio and the other SQL Server tools understand as a batch separator command. Indeed, you can change it to whatever you wish in Management Studio under Tools->Options->Query Execution->SQL Server->General->Batch separator.
    So you can write queries like

    SELECT * FROM sys.objects
    foo
    SELECT * FROM sysobjects
    foo

    Of course I'm still struggling to find a reason why someone would change the GO command.

    One thing I've found out recently is its parameter. SQL Server Management Studio seems to accept an integer after the GO command, and this will start an execution loop. For example if you write

    PRINT 'Hello word'
    GO 5

    The result will be:

    Beginning execution loop
    Hello word
    Hello word
    Hello word
    Hello word
    Hello word
    Batch execution completed 5 times.


    This is perfect for lazy moments when I want to populate a test table with some default values like:

    CREATE TABLE foo
        ( a INT PRIMARY KEY IDENTITY
        , b INT DEFAULT 1
        )
    GO

    I usually write something like:
    INSERT TOP (10) INTO foo (b) SELECT 1 FROM sys.objects

    but with the parameter to the GO command the above can be achieved with even less typing:

    INSERT INTO foo DEFAULT VALUES
    GO 10

    The above will also insert 10 rows :)
    Do let me know if you find a more interesting use for this parameter.

        Andras
  • Pivots with Dynamic Columns in SQL Server 2005

    Posted Friday, September 14, 2007 9:28 AM | 13 Comments

    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('')
                          ), 12'') + ']'
    
    

    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
    
  • Do you need to be dbo to compare two databases using SQL Compare?

    Posted Monday, July 30, 2007 10:01 AM | 1 Comments

    This question comes up time to time. SQL Compare compares two database schemata, and in order to do so it needs to read the system tables and system views. On the other hand, some organizational policies can be rather restrictive about who and with what permissions are allowed to connect to a production system.

     

    The more complex databases make use of object owners to organise the database objects. Objects like tables, views, user defined types can be owned by individual users/schemas. Under SQL Server 2000, in order to read all the schema information, you do need to be dbo, otherwise you will not be able to see some of the object definitions, or you will not be able to learn about the existence of certain database objects.

     

    Under SQL Server 2005 however there is a permission (VIEW DEFINITION) to control access to viewing object definitions. To add this permission to user UserA in a particular database run:

     

    GRANT VIEW DEFINITION TO UserA;

     

    You can also grant this permission at server level by executing:

     

    GRANT VIEW ANY DEFINITION UserA;

     

    The nice thing about this is that with the help of the above permission you are not granting permission to modify the database schema, so you can allow people to use SQL Compare to compare and monitor schema changes without allowing them to modify the databases in question. You can still generate a synchronization script, but that you can later execute as a different user.

     

    Andras

  • Databases and source control article

    Posted Wednesday, May 30, 2007 2:39 PM | 2 Comments

    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

  • How to order creation SQL files in SQL Compare 6.0 Professional

    Posted Monday, May 21, 2007 10:00 AM | 3 Comments

    It is a good practice to store creation scripts for the database organized in a way that there is a single SQL file for each database object. The problem starts when one wants a single script that creates all of these database objects.

    I’ve seen a solution in which the creation SQL files are concatenated, and then executed. Obviously the first time some dependencies were not right, so the script failed. This can occur for many reasons, e.g. a foreign key creation is executed before the referenced table is created. So the next step is to look at the error message, and reorder the problematic object. One executes this script, and looks at the error message again. Finally there will be a script that executes without a problem. Seemingly. One can create a stored procedure that references another, before the referenced stored procedure is created. So for example, if the stored procedure ProcA is calling ProcB, one can create ProcA before ProcB is created. Assuming that ProcB is created afterwards, there is no difference in terms of the way ProcA is executed. However, the sysdepends table will not be updated properly.

     

    SQL Compare 6.0 Professional will provide a solution to the above task. It can read in creation SQL files from a folder (the final version will read in recursively), and compare the scripts against a live database. If you compare these scripts against an empty live database, you can use the synchronize wizard to create a script that orders the object in the correct dependency order.

    There are many options that can influence how this ueber script is assembled. First, you can use “Do not use transactions in synchronization SQL scripts”. If this option is set, SQL Compare will basically just concatenate the creation SQL files in the right dependency order. If this option is not set, then transactions are added to ensure that the whole script is executed as a single transaction.

     

    Another useful way to influence the generated script is to compare the creation script files against a specific version of SQL Server. If the empty database is on SQL Server 2005, then the generated script will also use SQL Server 2005 syntax when it is available.

     

    SQL Compare 6 processes the scripts that it reads in, as it is seen in the above example, when SQL Compare can decide on the syntax to use. If one wants to just see a list of database objects in the order they should be executed, one can look at the action plan SQL Compare generates, and order the scripts manually, but with a bit of “help”.

     

    While ordering SQL creation scripts is not the primary use of SQL Compare, it is a powerful means to create a single SQL creation script from a large number of SQL files.

  • SQL Compare 6.0 beta - read and sync to and from SQL scripts

    Posted Tuesday, May 15, 2007 3:53 PM | 9 Comments

    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.

  • SQL Refactor 1.0 released

    Posted Tuesday, October 31, 2006 1:25 PM | 0 Comments

    Following a very successful beta program we have now released the first version of SQL Refactor.

    SQL Refactor is an Add-In to Microsoft Management Studio, with a large number of features that include laying out SQL, renaming views, stored procedures, table, functions, their parameters and columns and handling all their references, table split, encapsulate as new stored procedure, etc.

    For a full list of features see http://www.red-gate.com/products/SQL_Refactor/index.htm or my previous blog post about the beta http://www.simple-talk.com/community/blogs/andras/archive/2006/10/03/2300.aspx

    From http://www.red-gate.com/products/SQL_Refactor/index.htm you can download a fully functional trial or buy the product.

    If you have suggestions for new versions or have a question about the product please use our forum http://www.red-gate.com/MessageBoard/viewforum.php?f=53 or contact us directly.

    I'd like to thank all our beta testers both in my and in my team's name for helping us to release SQL Refactor.

     

    Andras

More Posts Next page »

















<May 2008>
SuMoTuWeThFrSa
27282930123
45678910
11121314151617
18192021222324
25262728293031
1234567
Execution Plan Basics
 Every day, out in the various discussion boards devoted to Microsoft SQL Server, the same types of... Read more...

Net Performance Cribsheet
 Robyn and Phil tackle the topic of how to make .NET applications perform well. As usual, they try to... Read more...

SQL Code Layout and Beautification
 William Brewer takes a look at the whole topic of SQL Code layout and beautification, an important... Read more...