SQL Server: Functions vs. Stored Procedures to return result sets

A while back, I was building the database schema for a web application which had some reporting functionality and among other things, I had do implement logic in the database to prepare the data for the application's reports. The queries I constructed were relatively complex which meant that I needed to construct objects in the database to encapsulate these queries. So, it came down to a choice between table-valued functions and stored procedures. If you do a little research you'll notice that there is no clear cut suggestion regarding the choice between functions or stored procedures for cases when you need to retrieve a result set from the database. So, here is a comparison of the two, which, in the end will be concluded with a suggestion for those of you out there who can't make up your minds.

T-SQL statements

With regards to the types T-SQL statements that each of the objects can contain, stored procedures are much more versatile because almost all T-SQL statements can be included in a stored procedure. The only exceptions are the following:

• USE <database>
• CREATE AGGREGATE, RULE, DEFAULT, CREATE, FUNCTION, TRIGGER, PROCEDURE, or VIEW

However, when it comes to table-valued functions, there is an entirely different story. Based on the T-SQL statements that can be used in them, they are quite limited. Namely, they cannot:

• Apply schema or data changes in the database
• Change the state of the database or a SQL Server instance
• Create or access temporary tables
• Call stored procedures
• Execute dynamic SQL
• Produce side effects such as relying on the information from a previous invocation.

So, basically, only SELECT statements are allowed in table-valued functions. The only exception is on multistatement-table valued functions, which must contain an INSERT ... SELECT statement that populates the table variable which will be returned by the multi-statement table-valued function. 

Parameters and return types

Both, stored procedures and table-valued functions accept parameters of all data types however, there are a few differences.

The first, and most important is that unlike stored procedures, table-valued functions do not accept output parameters. In fact, table-value functions return data to the client in only one way: through the RETURN statement. Stored procedures on the other hand, do accept output parameters and they have three ways to return data to the client: through output parameters, by executing a select statement in the procedure's body or by using a RETURN statement.

Another, more subtle difference is on how parameters with default values are handled. While both stored procedures and table valued functions support default values for parameters, these type of parameters are optional only on stored procedures. Weird as it is, if you want the default value for a parameter when using the function, you have to write the DEFAULT keyword in place of the value for that parameter. With stored procedures you can simply omit the value and SQL Server will supply the default value. 

The last difference is that when you call a stored procedure, you can specify the parameter values by association, meaning that you can use a syntax like this: <parameter_name> = <value> to supply the parameter values, which greatly improves the code's readability. You can't do this with functions. This might become an issue if the function has a lot of parameters, because you would constantly need to review the documentation just to find out the order of the parameters in the definition.

So, if you need to return multiple result sets or if you are worried about the readability of your code, stored procedures might be a better option

Performance

If you think about it, table-valued functions, especially inline-table valued functions, are a lot like another database object. Yeap, you guessed it, VIEWS. Even the SQL Server optimizer treats inline table valued functions the same as it does views. This is why one can think of table valued functions like parameterized views.

Performance wise, functions and stored procedures are identical. They both make use of execution plan caching, which means that they are not recompiled every time they are executed. To prove they are identical, you can create a function and a procedure with the same SELECT statement, execute each one a few times, and then check the sys.dm_exec_query_stats DMO. You will notice that the last_elapsed_times differ very little.

Usage

This is where I think, table-valued functions have the greatest advantage. Because they resemble views, they can be placed anywhere a table can be placed in a query. This means that you can filter the result set of the function, use them in join statements, etc. You cannot do the same with stored procedures. Of course, if you have enough knowledge and experience with T-SQL you could probably find some workaround, but generally, manipulating the result set returned by a stored procedure is not as straightforward as doing the same for a table-valued function. So, if for some reason, you need to apply some additional manipulation to the data returned by a function you can do that very easily. If that same data comes from a stored procedure, in most cases you may need to alter the procedure's code, which will require having the necessary permissions and what not. 

If you think that the result set of the stored procedure of function may need to be further manipulated, use table valued functions.

One last thing

One thing that I really like about table-valued functions, is that you can use the SCHEMABINDING option on them to prevent any changes on the underlying objects that can break the function. The same option is not always available on stored procedures. You can use it only on natively compiled stored procedures which are available only on SQL Server 2014 and up, and Azure SQL database. So, if you are using regular stored procedures to retrieve data, keep in mind that they can break if you change the structure of the referenced objects.

To conclude, as a general rule of thumb, I tend to use table-valued functions whenever I need to retrieve a result set from the database, and stored procedures when I need to perform some work on the database.

Read More

SQL Server: DATETIME vs DATETIME2

When it comes to a choice between data types for a field in a SQL Server database's table, an issue that is frequently discussed in popular forums is a choice between the DATETIME and DATETIME2 data types. According to the official MSDN documentation, it is recommended that you use DATETIME2 for new work because it is more portable, aligns with the SQL Standard, offers more precision and has a greater range. There aren't too many people who would dispute the recommendations of one of the "Big 4" companies, myself included, but, for those curious minds out there, let's see why DATETIME2 is the better choice.

Precision

DATETIME2 has a fractional precision of up to 7 digits compared to the DATETIME's precision of 3 fractional digits. The 'up to' part means that the user can manually specify the precision through an optional parameter. The default precision is 7 digits. This increased precision means that a conversion to the DATETIME2 data type of a string like '2016-11-11 20:20:20.4444' will succeed whereas the conversion of the same string to DATETIME will fail.

Accuracy

DATETIME2 supersedes DATETIME in accuracy by a relatively big margin. Although DATETIME has a precision of 3 fractional digits, it will round the last digit to an increment of .000, .003 or .007 whereas the DATETIME2 data type, supports an accuracy of 100 nanoseconds. Let's see how these differences affect the values by converting '2016-11-11 20:20:20.444' to DATETIME and DATETIME2 with 3 digits of precision. 

Even though the conversion is supported by both data types, converting to DATETIME means that you will be sacrificing accuracy. So if you aim to accurately store date and time with more than 2 fractional digits in your database the only choice for the data type is DATETIME2.

Range

DATETIME2 also supports a greater range of values than DATETIME. The former supports dates from 0001-01-01 00:00:00 to 9999-12-31 23:59:59.9999999 whereas the latter supports dates from 1753-01-01 00:00:00 to 9999-12-31 23:59:59.997. As a small additional benefit that avoids some confusion for those developers working with the .NET platform, the range of DATETIME2 complies with the range of the DateTime data type in C# and VB.NET.

Memory space required

If you are thinking that the additional capabilities of the DATETIME2 data type translate into additional storage space requirements, you are mistaken. DATETIME2 requires anywhere between 6 and 8 bytes whereas DATETIME requires 8 bytes of storage. The space required by DATETIME2 depends on the fractional precision you choose for the column: 

• 0 to 2 digits - 6 Bytes
• 3 to 4 digits - 7 Bytes
• more than 4 digits - 8 Bytes

So if your aim is to save storage space and increase read performance, DATETIME2 is the way to go.

Compliance with standards

DATETIME2 is compliant with both the ANSI and ISO 8601 standards for SQL whereas DATETIME is not compliant with any of those standards.

In conclusion, if it's range, precision, accuracy, storage space optimization or compliance with standards that you require, DATETIME2 is a better choice.

Read More

Fastest way to create and distribute a dynamic report from SQL Server

From request to delivery in 5 minutes or less! xSQL RSS Reporter for SQL Server enables you to generate standard Atom or RSS feeds containing any data that you are authorized to pull from a SQL Server database. The concept is very simple, you write a query that will be executed against a database and if the query executes successfully, RSS Reporter will automatically provide the output of that query in a standard Atom or RSS feed format that can be consumed from any device, anywhere. All that's left to do is send the url link to the individuals you wish to distribute the report to - every time they they open that report they will see the current data which can also be refreshed on demand. They can also filter and sort the report as they wish.

Here is a screen shot showing how you can define the feed/ report:

Download now and see what you have been missing!

Read More

Tables can't be compared

"I am trying to compare two databases and xSQL Data Compare has marked all table pairs with a red X and the messages on the output window show something like [16:08:11] Session [New Compare* (2)]: Pair [dbo].[MyTable] - [dbo].[MyTable] can't be compared. - what am I doing wrong?"

This is more or less a typical email we receive quite often from our users. The answer is, you are not doing anything wrong, but whoever designed that database didn't do a great job!

When comparing two database the xSQL Data Compare first reads the list of tables and views as well as their definitions and performs an automatic pairing of the tables and views based on names. There are certain mapping rules that allow the user to configure the mapping but by default it pairs them based on a simple name match. It then pairs the columns from both tables on each table pair and last but not least, it identifies the candidate comparison keys and decides which one to use. A candidate comparison key must uniquely identify each row on the table, so the ideal comparison key would be the Primary Key of the table but a unique index would be ok too. When it does not find a candidate key for a table pair it marks the pair as "non-comparable" since it has no way to match rows to each other. All tables in your database should have a primary key. However, if you find yourself in charge of a database that does not have primary keys defined don't despair - with a little bit of extra work you can still compare those tables. The xSQL Data Compare allows you to manually select comparison keys for each table pair:
 

To manually define the comparison keys for a table pair, drill down on that pair (click on the ... button on the left of the pair) and on the window that appears click on the "Unique Keys" tab. Choose one or more columns that you know form a unique key for the table and select that combination of columns as the comparison key. You will need to go through this exercise for all table pairs that are shown as non-comparable so it's a bit tedious however, once you have done this xSQL Data Compare will remember and the next time you need to compare those databases you will not see the can't compare message anymore.

xSQL Data Compare supports all SQL Server versions from SQL Server 2000 to SQL Server 2016 as well as SQL Server Azure, and it's free for SQL Server Express edition. Download now and see for yourself why thousands of users love it!

Read More

Documenting databases

Open any book or article about database development and administration, and I can all but guarantee that you will find a section for database documentation that describes its importance, best practices, conventions, etc. Why is this? How come so much attention is given to an aspect of databases that, at first sight, has nothing to do with their functionality? Those sections in books and articles, provide dozens of arguments in favor of the importance of database documentation, almost too much to remember. However, most of the reasons boil down to the following:

• An environment that is less complex.
• Lower likelihood of errors
• Databases that are easy to maintain and troubleshoot
• Low training cost for new staff
• Higher productivity 

It also offers a common language between IT and business decision makers, and an easy way to find hot spots or areas that are troublesome and could potentially become bottlenecks for the database.

Even though the high importance of documentation is stated and repeated over and over, and is just blatantly obvious, the process of documenting databases is often postponed or overworked by DBAs or developers (myself included). Why? Well, because it is simply too boring. Most DBAs would prefer to perform virtually any other task instead of typing in the descriptions of every element, one by one, in a Word or HTML document. If only there was some kind of tool that would do this instead of having to spend precious human work hours on it. But wait, there is! So we arrive at the goal of this article, to demonstrate how xSQL Documenter reads databases and generates those databases' documentation in Compiled HTML Help document and HTML files. 

For this demo, I'm using the AdventureWorks2012 sample database for the simple reason of it being a very well documented database. The process is fairly straightforward. First, you specify the databases you want to document by supplying the connection strings. xSQL Documenter supports all of the popular database engines such as SQL Server, MySQL, Oracle, DB2 etc. (for a full list, have a look here)

Choose the objects in those databases that you want documented:

And then press 'GO'. xSQL Documenter will read each object's type, descriptions (in the case of SQL Server it will read the MS_Description field), relationships with other database objects, and just about any other information it can find about the object. Then, it will use all the information gathered to build the help files. These help files will be saved by default in an 'output' folder on the same location as xSQLDocumenter.exe and will have the following format:

 As you can see, the information is grouped first by database, then by object type, and then by schema. Clicking on any object type will display a list of all the objects of that type along with some other data for each object. These data differ for every object type. For example, for tables, it will display the number of columns, indexes, constraints, number of objects it refers to and objects that refer to it and a comment which is taken from the MS_Description field. For views, it will display the number of columns, code length, number of dependents (like indexes), number of objects the view depends on, the row size and a comment, again taken from MS_Description. If you click on any object a new page will open with detailed information about each column in the table.

Below is the detailed view of the documentation for the Employee table.

First it displays a description and data for all the columns, these data include the column's data type, default value, whether it is nullable or not, a description, etc. Further down, you can also see details about the indexes and a list of all the objects that reference the Employee table and object which this table references.

 At the end of the page you can see a chart showing the relations among objects. Keep in mind that all objects above the Employee table in the chart are objects that are referenced by this table and all objects below it are objects that reference this table. Besides this chart you also see detailed information about all the constraints and foreign keys for the Employee table.

 As demonstrated, with just a few clicks you can generate a full and comprehensive help material that will display info about every database object you want and the relationships between these objects. And, if one has the good sense to provide a description for objects during creation, xSQL Documenter will display that description, making it that much easier to understand the role and function of the object. If this documentation is not to be uploaded in an intranet to be viewed using a browser you can use the .chm file which is indexed and searchable. This makes finding information about a particular object even easier.

This tutorial shows only the tip of the iceberg regarding the capabilities and the information that xSQL Documenter is able to display. For a full reference to this capabilities you can view xSQL Documenter's online help.

Read More

Database synchronization in the Software Development Life Cycle

Reading the title, most of you are probably thinking: What does database synchronization have to do with the Software Development Life Cycle (SDLC)? Stick around, because I'll get to that in just a minute. First, let me give a quick introduction to SDLC for those one or two developers out there who, by an impossible twist of fate, may have never heard of SDLC in their working experience. SDLC is a multi-phase process which ensures that good software is supplied to the customers. The term 'good software' has quite a broad definition but generally, 'good software' implies software that meets the requirements, has been tested thoroughly and has minimal errors. SDLC has a number of variations, but usually the software will go through these 5 stages:

1. Requirement analysis 
2. Design
3. Implementation / Development
4. Testing
5. Maintenance

Since, the focus of this article is database synchronization, I'll skip the first three stages and go straight to the stages which deal with database synchronization, which are testing and maintenance.

In the context of testing, database synchronization is useful in preparing the testing environment. For example, usually when a web application or a new feature is completed it will first be published on a test domain which is made available only to some chosen individuals / groups. This test site will probably need its own database that will have to be created or updated every time a change is made. You could backup the development database and restore it in the test environment or generate the database's script and execute it on the test environment but this would require the database to be recreated every time which can be a very time consuming process for large databases. Also, this is not possible if the databases are in the Azure cloud environment. The best option here is to use a synchronization tool like xSQL Schema Compare or xSQL Data Compare. So, you could use xSQL Schema Compare to transfer any schema changes from the development database to the testing environment and xSQL Data Compare to transfer any changes in lookup data. As a side benefit, xSQL Schema Compare can also be used to take periodic snapshots of the database in order to keep track of the changes made to it and rollback any of them is necessary.

Since the synchronization process is fairly straightforward and more or less the same in most of the scenarios, I'll first explain the other scenario in which database synchronization is necessary and then demonstrate how it's done and how to automate it.

The other, very important stage where database synchronization might take place in, is maintenance. In this stage, this process is done in both directions. First of all, for every bug fix or new feature, schema changes and lookup data are transferred from the development environment to the testing and then the live one. This process is the same as the one in the testing stage. Secondly, to make sure that the software is working correctly, quality assurance is performed. Quality assurance can have its own environment or it can use the testing environment. One thing is for certain though, it needs to have data that is as close as possible to the live data. Obviously, no type of data satisfies this condition better than the live data itself. By using xSQL Data Compare, these live data can be transferred to the quality assurance environment where they can then be used as needed.
Without further ado, let's proceed to a demonstration of schema and data synchronization. I'll be using the AdventureWorks2012 database in this demo. I made a copy of this database (AdventureWorksCopy) which will be used as the testing environment's database. Suppose this database belongs to a company that has decided to extend its medical and dental benefits to the employee families. Obviously, they will want to have some data in the system for the employee's family members. To do this, a table called EmployeeDependant is added to the HumanResources schema in the development database which has records for the employee's families. Of course, before this feature can be published it needs to be tested, which means that the schema changes need to be transferred to AdventureWorksCopy. There are 4 very simple steps to this process.

1. Add the databases in xSQL Schema Compare.
2. Compare AdventureWorks2012 with AdventureWorksCopy. The comparison reveals that there is a table EmployeeDependant in AdventureWorks2012 that does not exist in AdventureWorksCopy (note the one-way arrow to the right).
   
3. Generate the synchronization script.
4. Execute the script. By opening the database in SSMS I can see that the EmployeeDependant table is added to the AdventureWorksCopy database.

The new EmployeeDependant table has a column named 'RelationshipWithEmployee'. Instead of it being a simple VARCHAR column it's better to have it as a foreign key to a 'Relationships' table. The records in this table will probably never change (there is a fixed number of relationship types in a family), so it can be used as an example of the lookup data that may need to be transferred from the development environment to the testing one. Doing this synchronization with xSQL Data Compare is, again, a very straightforward process. Before I show this process' steps, here is a screen shot of the data in the Relationships table that will be transferred to the AdventureWorksCopy database:

To add these data to the Relationships table in AdventureWorksCopy database do the following:

1. Add the databases to xSQL Data Compare.
2. Choose the objects that need to be compared. In this case, only the Relationships table will be synchronized, so only that table will be selected from the objects to be compared.
   
   
3. Compare the tables
4. Generate the synchronization script for the AdventureWorksCopy database, Here is the script that is generated:
   

   SET XACT_ABORT ON;
   SET ARITHABORT ON;
   SET NUMERIC_ROUNDABORT OFF;
   SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
   
   
   BEGIN TRANSACTION;
   
   
   /*-- -Insert(s): [HumanResources].[Relationships] */
   SET IDENTITY_INSERT [HumanResources].[Relationships] ON;
   INSERT INTO [HumanResources].[Relationships] ([Id],[RelationshipName]) VALUES(1,'Spouse');
   INSERT INTO [HumanResources].[Relationships] ([Id],[RelationshipName]) VALUES(2,'Sibling');
   INSERT INTO [HumanResources].[Relationships] ([Id],[RelationshipName]) VALUES(3,'Parent');
   INSERT INTO [HumanResources].[Relationships] ([Id],[RelationshipName]) VALUES(4,'Child');
   SET IDENTITY_INSERT [HumanResources].[Relationships] OFF;
   
   
   
   COMMIT TRANSACTION;
   

5. Excecute the script. After execution, if a select query is run on the Relationships table AdventureWorksCopy database, it will display the following data: 

Using a similar process to the one in this demo you can copy the data in the live database in the Quality Assurance database and run any tests that you need.

In most cases, these tasks are repetitive. For example, an organization might have the practice of synchronizing the live data with quality assurance every 3 days. Instead of doing this manually every time, you can just generate a XML configuration file for xSQL Data Compare Command Line by clicking the button in the picture below and then, using Windows Scheduler, schedule the comparison to be run periodically with that XML file as an argument.

Automation of the schema comparison is exactly the same as automation of data comparison. The only difference is that xSQL Schema Compare Command Line needs to be used.

In conclusion, the benefits of using synchronization tools like xSQL Schema and Data compare are quite obvious. They can simplify and automate parts of the SDLC that are trivial and boring and would otherwise, take a relatively long time to complete.

Read More

Comparing and synchronizing Oracle databases

Applies to: xSQL Oracle Data Compare v1.0 - 2.0

As you may have noticed, most of the articles in this blog focus around scenarios and solutions for databases in Microsoft's SQL Server. However, most of the experienced DBA's in the field of information technology and data sciences have probably dealt with more than one database engine. Anyone working in big enterprises certainly knows of Oracle's database engines (12c being their latest version). According to DB-Engines' ranking, Oracle's databases have the number one spot in the popularity ranking of major database engines with 1413.01 points. Having said this, I felt that it was high time I posted an article that provides some insight into data synchronization in Oracle databases.
Without further ado, let's see how to perform a data comparison and synchronization with xSQL Oracle Data Compare. In this demo I have used the sample HR schema that can be found in this Git repository. I created two databases (orcl and orcl2) and added the HR schema in both. Then, I deleted all the data from the COUNTRIES table in orcl2.
The first thing you do is add the databases. For those of you who are used to xSQL Data Compare for SQL Server, you will notice that the dialog is a bit different:

In the Connection Name, you can specify any name you like to identify the connection with. Personally, I like to keep the Connection Name the same as the Service Name (which is basically the same as Database name in SQL Server). But this will work with any type of string, as long as you type the Service Name correctly. Then you specify the username (Schema name) and the password for that user. In the Host Name and Port fields enter the server name and service's port number on that server. After adding the databases, the comparison and synchronization process is more or less the same as in xSQL Data Compare for SQL Server:

1. You click on Compare Schemas and choose the databases and schemas you would like to compare. For the schema selection, just leave the '<default>' option selected and xSQL Data Compare will automatically retrieve the tables associated with the user specified when the connection was added. The following tables from the HR schemas in both databases are mapped together.
   
   
2. After the objects are analyzed and mapped together, you can perform the comparison by clicking on 'Compare Data'. Before you do this, you can also edit the comparison and scripting options to customize the comparison process by specifying for example, whether Views should be compared, or if the character case should be ignored in text columns. Apart from this you can also define some simple mapping rules to ignore table name prefixes or suffixes. The comparison result below shows that the COUNTRIES and LOCATIONS tables are different in both databases (LOCATIONS table is different because it contains a foreign key to the COUNTRIES table whose values I set to null in order to delete all the records from the COUNTRIES table).
   
3. Now, all that is left is to generate the synchronization script for the left database (which is orcl2) and execute it so that the synchronization process is completed. Here is a screen shot of the script:
   

And there you have it, a complete data synchronization process for two Oracle databases. This is a very basic scenario, but I hope it provides the knowledge needed to perform more complex data comparison and synchronization tasks. You can check out the online documentation to learn how to use xSQL Oracle Data Compare's features complex synchronization tasks.

Read More

How to deploy your SQL Server database to a remote server

CASE 1: you have direct access to both the SQL Server where the source database is and the SQL Server where the target database is.

1. First time deployment
1. Backup / restore method
1. Backup the database on the source
2. Copy the backup file to the target machine
3. Restore the database on the target
4. Create logins and set permissions as needed
2. Compare and Synchronize method
1. Create database on the target machine (blank)
2. Use xSQL Schema Compare to compare and synchronize the database schemas of the source and the target. 
3. Use xSQL Data Compare to populate the remote database with whatever data you might have on the source that you want to publish (lookup tables etc.)
2. Database exists in the target server
1. Compare and Synchronize method
1. Use xSQL Schema Compare to compare and synchronize the database schemas of the source and the target. 
2. Use xSQL Data Compare to push any data you need from the source to the target. Caution: be careful not to affect any data that exists on the target already.

CASE 2: You can not directly access the target server but you have a way to deploy SQL scripts on that server. As is indeed the case in most scenarios you also should have a way to get a backup of your database from that remote host. In this case follow those simple steps:

1. Restore the remote database on your local environment
2. Use xSQL Schema Compare to compare your source database with the restored database. Generate the schema synchronization script and save it.
3. Use xSQL Data Compare to compare your source database with the restored database. Carefully make your selections to ensure you push only the data you want to push from the source to the target. Generate the data synchronization script and save it. 
4. Deploy your schema synchronization script to the target machine. 
5. Deploy your data synchronization script to the target machine.

Both xSQL Schema Compare and xSQL Data Compare are completely free for SQL Server Express with no restrictions or limitations. Furthermore, for other editions of SQL Server the tools are free if the database has under a certain number of objects in it (current limitations are listed here).

Read More

Comparing xSQL Schema Snapshots

Applies to: xSQL Schema Compare v7.0.0 – 9.0.2

Before I develop a scenario where xSQL Schema Compare snapshot comparison is necessary let’s explain what xSQL Schema snapshots are. An xSQL Schema snapshot is a compact file that contains all the schema information for a database. In simple words, an xSQL Schema snapshot is basically a picture of a database’s schema at a specific moment in time. These snapshots are very useful if you want to keep track and get a clear picture of the evolution of a database, by comparing the snapshots with each other or with the live database.

Scenario: Naturally, the question that arises is “Where and how would one use this feature?”. Let’s, for the purpose of this article, take the place of a software developer in a relatively big company. In most cases the development department is separate from data management which means that developers do not have any access to the live databases because these databases are managed by entirely different persons. So how would the developer go about updating the live database with the schema changes made in the development database after the development of a new feature is completed? Direct database comparison is not possible because no party has access to both databases. Simply sending an email to the DBA with the changes you need to be made is out the question because it’s error prone and for anything more than very minor changes, it is not feasible.

Solution: Use snapshots! Ideally, the DBA who manages the live database would create a simple job that periodically takes a schema snapshot of the live database and dumps it into a shared repository to which the developers have access. These snapshots would then be used by the developers for auditing and debugging purposes. In this case, you can use these snapshots to compare the development database with a snapshot of the live database, generate the synchronization script and send it to the DBA to execute it on the database. Let’s see how this is done.

For demonstration purposes I created a copy of the Northwind database and took a snapshot of the copy with xSQL Schema Compare. This copy will serve as the live database. Then I added a column on one of the tables in the original database which will serve as the development database. The picture below shows the snapshot in xSQL Schema Compare:

The comparison process for databases and snapshots is exactly the same. For the left database I am using the NORTHWND which has the extra column and instead of the right database I added the snapshot.

After choosing the database and the snapshot, I ran the comparison and xSQL Schema Compare found the difference in the ‘Categories’ table, for which, the left database has one additional column.

If I generate the synchronization script for the right database, it will generate this SQL Statement:

CodeProject

So, at this moment, I as a developer, have the synchronization script which I can send to the DBA and let him/her make the change to the live database represented in this demo by NorthwindCopy. And all of this was done without needing to access the database from which the snapshot was taken.

In conclusion, besides this scenario, xSQL Schema Compare’s snapshots can be used to stimulate a very basic version control for the database’s schema. For example, if you as a developer are not sure, which is the best database construct for a new feature you are about to implement, you can take a snapshot of the database, make the changes you need to do to test the database construct for that new feature, and if something does not work the way it should, you can always roll back these changes by synchronizing the database with the snapshot. 

Read More

Why is SQL Server scanning the clustered index when I have an index on the column I am filtering on?

*Originally published in our old blog on January 2009

I ran into a very simple SQL Server performance related question the other day. A puzzled DBA was staring at a simple query “SELECT * FROM table1 WHERE col_n = x”. Since this was a fairly common query that was executed very frequently he had appropriately created an index on col_n. Yet, for some reason SQL Server was simply ignoring the index on col_n and instead scanning the clustered index. So the puzzling question on this DBAs mind was “why is SQL Server scanning the table? Isn’t the SQL Server Query Optimizer smart enough to see that it will be more efficient to use the col_n index?

The answer lies in the fact that the SQL Server Query Optimizer is smarter than that (albeit not as smart as I wish it would be). You see, when deciding on a particular execution plan the Query Optimizer has to use an actual value for the parameter x to estimate the number of rows that will be returned on each step. It further assumes that the rows you are looking for are randomly distributed and that it will need to do a page read for every row being returned in addition of the reads it needs to do on the index pages. Depending on this number it makes a determination whether it will be more efficient to just scan the whole table and pull out the rows it needs or go to the col_n index first to get a list of addresses and then go and pull those rows.

So, now that we know why should we just leave it there since it appears that SQL Server is correctly picking the most efficient path? Not so fast! Remember all that evaluation is being done based on a certain value of parameter x. It could be that for most of the values of parameter x scanning the table is more efficient. In that case you can simply drop the index on col_n if it is not needed for any other queries and leave it at that. However, it could be that for 99% of the possible values of x it would be a lot more efficient to utilize the col_n index – it just so happened that unfortunately when generating the plan the value of x happened to fall on that 1% for which scanning is more efficient. In this case you have two options:

• simply force SQL Server to dispose the bad plan that it has cashed and generate a new one; OR
• use a HINT to force SQL Server to use the index. The danger with this however is that with time as the data changes the use of that index may not be optimal so I would recommend that you avoid using HINTS whenever possible and let SQL Server do its job.

CodeProject One last note: it is often not necessary to return all the columns – in other words instead of SELECT * FROM table1 WHERE col_n = x you may only need to return a couple of columns like SELECT col_1, col_2 FROM table1 WHERE col_n = x in which case it could be worth it to include col_1 and col_2 in the col_n index. That way SQL Server would not need to go to the table at all but instead get all it needs from the index pages. In certain scenarios where you have a rarely updated but very frequently queried table the above approach of including other columns in the col_n index may make sense even if the query returns all the columns.

Read More

SQL Split String Function (STRING_SPLIT in 2016)

* originally published in our old blog in August 2011 - updated with SQL Server 2016 reference

As a SQL Server developer you are often faced with the need to split a string of values that may be separated by comma, space, tab, or any other separator. In 2011 we published a simple table-valued function (SplitString see below) that takes a string and a divider as parameters and returns a table containing the values into a list form (one value for each row). The parameters are defined as a varchar(1024) for the string of values and char(1) for the divider but you can change those based on your needs.

As of SQL Server 2016 a new function STRING_SPLIT ( string , separator )  was introduced that does the same thing. So if you are using SQL Server 2016 use the available STRING_SPLIT function (you can read more about it here: https://msdn.microsoft.com/en-us/library/mt684588.aspx).
However, if you are using an older version of SQL Server you can take advantage of our SplitString function:

CREATE FUNCTION SplitString 
(
    @SeparatedValues VARCHAR(1024),
    @Divider CHAR(1)
)
RETURNS    @ListOfValues TABLE ([value] VARCHAR(50))
AS  BEGIN 
      DECLARE @DividerPos1 int, @DividerPos2 int
      SET @DividerPos1 = 1
      SET @DividerPos2 = CHARINDEX(@Divider, @SeparatedValues, 0)

     WHILE @DividerPos2 > 0
           BEGIN 
                  INSERT INTO @ListOfValues VALUES (SUBSTRING(@SeparatedValues, @DividerPos1, @DividerPos2 - @DividerPos1))
                  SET @DividerPos1 = @DividerPos2 + 1
                  SET @DividerPos2 = CHARINDEX(@Divider, @SeparatedValues, @DividerPos1)
            END
           -- Now get the last value if there is onw
                  IF @DividerPos1 <= LEN(@SeparatedValues) 
                       INSERT INTO @ListOfValues VALUES (SUBSTRING(@SeparatedValues, @DividerPos1, LEN(@SeparatedValues) - @DividerPos1 + 1))

        RETURN
  END
GO

Once you create the function you can call it like this:

SELECT * FROM [SplitString] (@mystring, @myseparator)

or with hardcoded values (in this example the separator is a vertical line):

SELECT * FROM [SplitString] ('value1|value2|value3', '|')

This will return:
   value1
   value2
   value3
 
Note that if the string starts with a divider like '|value1|value2|value3' then the first value returned will be a blank value.

CodeProject You can of course insert those values directly into a table either via INSERT INTO <mytable> ... SELECT * FROM [SplitString]... or into a temp table via SELECT * INTO #mytemptable...FROM...

Read More

t-sql random string generator

Note: originally published in our old blog on December 2008.

CodeProject This is a simple stored procedure that you can use to generate a random string. You can use it as a random password generator and for other purposes. It is very simple so I won't bore you with unnecessary explanations but if you do happen to have any questions feel free to post them here.

CREATE PROCEDURE [dbo].[usp_GenerateRandomString]
  @sLength tinyint = 10,
  @randomString varchar(50) OUTPUT
AS
BEGIN
  SET NOCOUNT ON
  DECLARE @counter tinyint
  DECLARE @nextChar char(1)
  SET @counter = 1
  SET @randomString = ''

  WHILE @counter <= @sLength
    BEGIN
      SELECT @nextChar = CHAR(ROUND(RAND() * 93 + 33, 0))
      IF ASCII(@nextChar) not in (34, 39, 40, 41, 44, 46, 96, 58, 59)
        BEGIN
          SELECT @randomString = @randomString + @nextChar
          SET @counter = @counter + 1
        END
    END
END

Read More

Distributed Databases - One Way Synchronization

Applies to: xSQL Data Compare v7.0.0 – v9.0.0

Business Need

A very common organization of the data infrastructure for big companies who operate in many countries and use some type of centralized system to manage their operations, is to have separate databases for each branch and one database that is operated by the headquarters that has the following data in it:

• A copy of all of the transactional data (sales, orders, etc.) from each branch.
• Lookup data like lists of products, services, categories, etc.

Both types of data need to be periodically synchronized between the central database and the branches’ databases. There are two directions for this synchronization.

Central -> Branches

This synchronizes the lookup data. For example, every time the company decides to add new products, services or even a new country where it operates, the logical way to go about this is to add the new data to the lookup tables in the central database and then synchronize the branches’ databases with this central database.

Branches -> Central

This synchronizes transactional data. This type of synchronization can be done at the end of every business day to transfer all the new or updated transactions from the branches to the central database which can then be used by the company’s HQ to build different types of reports.   

Solution

xSQL Data Compare’s one-way synchronization. If the synchronization script is generated using the default options, it will make the database upon which it is executed, the same as the database with which it is compared. But in this case, that is not the desired result. Thankfully xSQL Data Compare offers the option to choose between synchronizing the left, right or different rows, or a combination of these options. This means that if you choose to synchronize only the right rows, rows that are in the right database but not in the left would be copied to the left, and rows that are in the left DB but not in the right would not be deleted.

To demonstrate this, below are the comparison results for the Products table of two Northwind databases (NORTHWND and NorthwindCopy). The Products table in the NORTHWND database does not have products with ID 8 to 14. The Products table in the NorthwindCopy database does not have products with ID 1 to 7. Also, the data for the product with id 20 is different in the NorthwindCopy database. A row is considered different if that row exists on both tables with the same primary key and at least one of the other fields is different. The goal here is to copy products with ID 8 to 14 and the changes in the product with ID 20 from the NorthwindCopy to NORTHWND. This means that xSQL Data Compare needs to generate a script for the left database (NORTHWND) where only the different and new rows from the right database will be synced. These rows will be left checked.

So, all the right rows will be checked:

And all the different rows:

To make sure that none of the rows that are in the left database’s table but not in the right is deleted, all the left rows will be unchecked:

Doing this will generate the following script (when Generate script for NORTHWND is clicked):

/*-- -Delete(s): [dbo].[Products] */

/*-- -Update(s): [dbo].[Products] */
SET IDENTITY_INSERT [dbo].[Products] ON;
UPDATE [dbo].[Products] SET [QuantityPerUnit]=N'35 gift boxes' WHERE [ProductID]=20
SET IDENTITY_INSERT [dbo].[Products] OFF;

/*-- -Insert(s): [dbo].[Products] */
SET IDENTITY_INSERT [dbo].[Products] ON;
INSERT INTO [dbo].[Products] ([ProductID],[ProductName],[SupplierID],[CategoryID],[QuantityPerUnit],[UnitPrice],[UnitsInStock],[UnitsOnOrder],[ReorderLevel],[Discontinued]) VALUES(8,N'Northwoods Cranberry Sauce',3,2,N'12 - 12 oz jars',40.00,6,0,0,0);
INSERT INTO [dbo].[Products] ([ProductID],[ProductName],[SupplierID],[CategoryID],[QuantityPerUnit],[UnitPrice],[UnitsInStock],[UnitsOnOrder],[ReorderLevel],[Discontinued]) VALUES(9,N'Mishi Kobe Niku',4,6,N'18 - 500 g pkgs.',97.00,29,0,0,1);
INSERT INTO [dbo].[Products] ([ProductID],[ProductName],[SupplierID],[CategoryID],[QuantityPerUnit],[UnitPrice],[UnitsInStock],[UnitsOnOrder],[ReorderLevel],[Discontinued]) VALUES(10,N'Ikura',4,8,N'12 - 200 ml jars',31.00,31,0,0,0);
INSERT INTO [dbo].[Products] ([ProductID],[ProductName],[SupplierID],[CategoryID],[QuantityPerUnit],[UnitPrice],[UnitsInStock],[UnitsOnOrder],[ReorderLevel],[Discontinued]) VALUES(11,N'Queso Cabrales',5,4,N'1 kg pkg.',21.00,22,30,30,0);
INSERT INTO [dbo].[Products] ([ProductID],[ProductName],[SupplierID],[CategoryID],[QuantityPerUnit],[UnitPrice],[UnitsInStock],[UnitsOnOrder],[ReorderLevel],[Discontinued]) VALUES(12,N'Queso Manchego La Pastora',5,4,N'10 - 500 g pkgs.',38.00,86,0,0,0);
INSERT INTO [dbo].[Products] ([ProductID],[ProductName],[SupplierID],[CategoryID],[QuantityPerUnit],[UnitPrice],[UnitsInStock],[UnitsOnOrder],[ReorderLevel],[Discontinued]) VALUES(13,N'Konbu',6,8,N'2 kg box',6.00,24,0,5,0);
INSERT INTO [dbo].[Products] ([ProductID],[ProductName],[SupplierID],[CategoryID],[QuantityPerUnit],[UnitPrice],[UnitsInStock],[UnitsOnOrder],[ReorderLevel],[Discontinued]) VALUES(14,N'Tofu',6,7,N'40 - 100 g pkgs.',23.25,35,0,0,0);
SET IDENTITY_INSERT [dbo].[Products] OFF;

As you can see, INSERT statements are generated for products with id 8-14. An UPDATE statement is also generated for Product with ID 20 and there are no DELETE statements.

Execute the script and there you have it, all the rows that are different and in NorthwindCopy but not in NORTHWIND are copied to the latter. To do the opposite, simply check all the left rows, uncheck the right and generate a script for NorthwindCopy.

One thing to note: In real life scenarios, to use this technique, it would be preferable to have all the Primary keys as ‘uniqueidentifiers’ to avoid primary key collision. 

Automation

As always, xSQL Data Compare Command Line can be used to automate the entire process in order to perform this synchronization periodically. All you need to do is after you have specified the rows you want to sync is to generate the XML file that will be passed as an argument to xSQLDataCmd.exe. This can be done by clicking this button:

CodeProject

Read More

Syncing QA data with Production

Applies to: xSQL Data Compare v7.0.0 and higher

WARNING: this article focuses on the data transfer / synchronization task and does not address operation security issues and sensitive data scrubbing / obfuscation which are critical whenever dealing with live, production data.

Nowadays, Quality Assurance (QA) is a very important part of the development process for any company that strives to offer a reliable product, to satisfy its clients, and be competitive in the market. And let’s not forget the “side benefit” of reduction of life cycle costs.

One of the main aspects of QA are the data and databases it uses. These data are usually kept in a separate environment from the production and, to have the best possible quality assurance process, data from the production environment are copied to the QA environment. For this task there is a choice between two main options:

• Backing up and restoring the live database. Although this might work with small databases, if we are dealing with large databases with many tables and millions of rows, it becomes a very expensive operation because the database will have to be recreated each time the synchronization is performed. Also, if the databases are in a cloud environment like Azure, which does not support restoring a backup, this option is automatically eliminated. As a final point, in those cases where backup – restore is a viable option, there is always the issue of automating the synchronization process, which in the case of backup - restore operations is problematic at best. 
• Using comparison tools like our xSQL Data Compare. For any large databases, whose data change rapidly, this is probably the best option. And that’s because the comparison and synchronization process is highly customizable and easy to automate.

     There are two cases that show in detail how xSQL Data Compare’s features can be used in the QA synchronization process.

1. Suppose you have a production database with a table which currently has 5 million rows and needs to be synchronized with the QA Database. One way to go about this is to back up the live database and restore it in QA. The problem here is very easy to identify. Unless this is the first synchronization, it’s very improbable that all 5 million rows of the table in the Production database will have differences from the table in QA.
   For argument’s sake, let’s say that there are 100,000 records out of sync. If one was to use the backup – restore option, 5 million new rows would be inserted in the table in the QA database. So there’s 4,900,000 unnecessary INSERT operations and the server will be doing 50 times the amount of work it actually needs to do. By any standards, this is unacceptable.
   The process can be made much more efficient by using xSQL Data Compare, because after the comparison, xSQL Data Compare generates a synchronization script only for the rows that are out of sync. This is a big improvement already, but it can be made even better. Since the synchronization process is, in most cases, performed periodically, every week for example, than you already know that the only rows that are out of sync are the ones added or modified in the week prior to the synchronization. So there is no need to compare all 5 million rows. Just the ones that are out of sync.
   You can do this by using the where clause of xSQL Data Compare in which you can enter conditions in the same way you would enter them in a SQL Query. Below is an example in which the where condition is specified as ModifiedDate >= DATEADD(DAY, -7,GETDATE()). This will compare only the records which were modified in the last week. To open the dialog shown in the picture click the button selected in red.
   


2. As I said in the first case, synchronization of the QA environment is usually a periodic and very repetitive process, so automating it would save a lot of time for DBAs or people responsible for this task. Data Compare addresses this issue with its command line version. The comparison from the first case can also be done from xSQL Data Compare command line and scheduled to be run periodically with Windows Task Scheduler. The same options that were specified in the UI can be specified in an XML file that will be given as a parameter to xSQLDataCmd.exe. Below is the example XML configuration to perform the same comparison as in the first case.

<?xml version="1.0" encoding="utf-8"?>
<SqlDataCompareCmdConfig xmlns="http://www.xsqlsoftware.com/schemas/xSQLDataCompareCmdSchema.xsd">
  <!-- **** auto-generated data compare cmd line config file, created on 10/20/2016 4:14:43 PM **** -->
  <LeftDatabase>
    <SqlServer>Production</SqlServer>
    <DatabaseName>AdventureWorks2012</DatabaseName>
    <TrustedConnection>true</TrustedConnection>
  </LeftDatabase>
  <RightDatabase>
    <SqlServer>QualityAssurance</SqlServer>
    <DatabaseName>AdventureWorksCopy</DatabaseName>
    <TrustedConnection>true</TrustedConnection>
  </RightDatabase>
  <TablesMappingRules />
  <TablePairs>
    <!-- ******* custom pairs ******* -->
    <!-- Sales.SalesOrderHeader -->
    <Pair Action="Include">
      <LeftObject Name="Sales.SalesOrderHeader" />
      <RightObject Name="Sales.SalesOrderHeader" />
      <WhereClause>ModifiedDate >= DATEADD(DAY, -7,GETDATE())</WhereClause>
    </Pair>
    <!-- **************************** -->
  </TablePairs>
  <CommandLineOptions>
    <ErrorLogName>xSQL</ErrorLogName>
    <ErrorLogType>WindowsLog</ErrorLogType>
    <OverwriteComparisonLog>true</OverwriteComparisonLog>
    <Direction>RightAsLeft</Direction>
    <Synchronize>true</Synchronize>
  </CommandLineOptions>
</SqlDataCompareCmdConfig>

Now, all that needs to be done is for the task to be scheduled in windows task manager and Data Compare will synchronize all the changes made in the last week in the production database. And just so you don’t have to manually write the XML file, xSQL Data Compare can generate it for you from the UI by clicking this button.

If, for any reason, the schema in your production database has changed and is out of sync with the QA database, use Schema Compare for SQL Server to sync the schemas first and then sync the data.

In conclusion, apart from the scenario described in this article there are a ton of other synchronization scenarios which, by using xSQL Data Compare, can be customized to be very efficient, and have the QA environment at your disposal in a very short time. For a full reference of the available customizations check out xSQL Data Compare’s onlinedocumentation.

CodeProject

Read More

Columnstore Indexes in 2 minutes

The standard way of storing relational database tables on physical media is row based, that is, each row occupies a contiguous space. The term that is used for this type of data storage is rowstore.

In simple terms, you can think of the columnstore as a transposed rowstore. Logically nothing changes, you can still think of and see a table as a normal table with rows and columns, but physically the data is stored in a column-wise format.

Why do this and when? 

• Think of a column like “Country” on say an “Orders” table – you basically have a handful of country IDs repeating millions of times. Just imagine the kind of data compression you can achieve on such column! 
• Now think of a query like “get sales by country” – instead of scanning the whole table, SQL Server will only need to deal with two compressed columns and will be able to return the results many times faster using significantly less resources. 
• When? Storing fact tables and large dimension tables in data warehouses as clustered columnstore indexes will significantly improve compression and query performance.

Why not do this?
If columnstore indexes are so great why not store tables as clustered columnstore indexes always? Just imagine what an insert|update|delete looks like in the case of a clustered columnstore index for a table that say has just 20 columns! It is kind of like doing 20 separate inserts|updates|deletes, one for each column. So, on a transactional database columnstore indexes are not a good idea.

Best of both worlds?
SQL Server 2016 lets us create an updatable non-clustered columnstore index on a rowstore table and non-clustered rowstore indexes on clustered columnstore indexes. This mixing comes with a cost in both cases but in certain scenarios the gains achieved make this worthwhile.

CodeProject Our Schema and Data Compare tools support comparing and synchronizing columnstore indexes.

Read More

In-memory tables in 60 seconds

As a SQL Server DBA or developer here is what you should know about memory-optimized tables (in-memory tables):

• Introduced in SQL Server 2014
• Primary store is main memory, second copy maintained on disk to achieve durability
• Handled by in-memory OLTP, a memory optimized database engine integrated with SQL Server engine
• Fully durable, ACID transactions. Supports delayed durability for better performance (risk losing  committed transaction that have not been saved to disk)
• SQL Server supports non-durable memory optimized tables (not logged and data not persisted to disk) – no recovery option in case of disk crash
• No-lock concurrency eliminates blocking and achieves better performance. Concurrency handled through row versioning. 
• Not organized in pages and extents, memory-optimized tables are a collection of row versions and a row version is addressed using 8-byte memory pointers
• Data in memory-optimized tables can be accessed:
• (most efficient) Through natively compiled SPs (the limitations is that natively compiled SPs can only access in-memory tables)
• Through interpreted t-sql inside a standard SP or through ad-hoc t-sql statements. 

CodeProject Our Schema and Data Compare tools support comparing and synchronizing in memory tables. 

Read More

Stretch Databases in 60 seconds

SQL Server 2016 introduces Stretch Databases, a beautiful feature that by itself makes migrating to SQL 2016 worth it. What is it? A stretch database is basically a database with an automated cloud archiving mechanism. Here’s how it works in a few words:

• You enable stretch database for your database and the tables you’re interested in “archiving” 
• You decide to “archive” either a whole table, for example a history table, OR just certain rows, for example all transactions older than 12 months for a transaction table.
• SQL Server will then silently migrate (archive) your cold data based on the criteria you defined, from the live database to Azure 

A couple of awesome things to note:

• No need to change queries or applications – if the data a query is pulling happens to be “cold” then you may notice some latency but other than that the whole thing is completely transparent. 
• Your data is encrypted end to end (in the live database, on the way, and in the target)
• Faster backups, faster queries, overall significantly better performance (of course the improvement depends heavily on how you define the criteria that separates cold and hot data). 

CodeProject By the way, as of version 9 our Schema and Data Compare tools support comparing and synchronizing Stretch Databases and  stretch tables.

Read More

Temporal Tables in 60 seconds

If you've ever designed a database you've most certainly run into the need for what we called history tables, those tables that stored the history of row changes (sometimes the whole row and sometimes just certain columns) and that were populated through insert/update/delete triggers defined on the "parent" table. With SQL Server 2016 there's no need to manually implement history tables anymore, you just define a table as a system-versioned temporal table and let the SQL Server engine take care of maintaining the row change history.

With system-versioned temporal tables pulling data from the current and the associated history table at the same time is very easy and efficient using the FOR SYSTEM_TIME clause.

System-Versioned temporal tables are supported on SQL Server 2016 and SQL Azure.

This is all you need to know in order to decide if and when you may need to use temporal tables.

PS our Schema and Data Compare tools support comparing and synchronizing System-Versioned Temporal tables.

------------------------------------------------------------------------------------------------------------------
If you wish to spend more than 60 seconds here is a brief explanation on how this works.

• The live, temporal table has two explicitly defined datetime2 type columns referred to as period columns that are used exclusively by the system to record period of validity for each row whenever the row is modified (the columns are typically denoted as SysStartTime and SysEndTime)
• The temporal table contains a reference to the history table (SQL Server can automatically create the history table or you can specify it yourself)
• ON INSERT on the temporal table the system sets the SysStartTime to the begin time of the current transaction and SysEndTime to max value (999-12-31)
• ON UPDATE/DELETE on the temporal table 
• a new row is inserted in the history table with the SysStartTime coming as is from the temporal table and the SysEndTime being set to the begin time of the current transaction. 
• On the live table either the row is deleted (in case of a delete operation) or the SysStartTime is updated to the begin time of the current transaction (in the case of an update). 

More details from the source:

• Creating a System-Versioned Temporal table: https://msdn.microsoft.com/en-us/library/mt590957.aspx
• Querying System-Versioned Temporal tables: https://msdn.microsoft.com/en-us/library/mt591018.aspx

CodeProject

Read More

Synchronizing NOT NULL Columns

Applies to: xSQL Schema Compare v7.0.0 – v 9.0.0, xSQL Data Compare v7.0.0 – v9.0.0

Description: Using xSQL Schema Compare and xSQL Data Compare to keep the tables of two databases in sync is a no-brainer, and, almost always, the default synchronization options will do the trick. However, there are a few special cases in which knowing how to correctly manipulate the comparison and synchronization options is a must to complete the synchronization process correctly. One of this cases is the synchronization of NOT NULL Columns.

Let’s say there are two tables in two different databases that need to be synchronized. Both of these tables have data and one of them has one or more columns with a NOT NULL constraint and no default value. In this case, synchronization of these table’s schemas using the default options is not possible.

Reason: This is because when you generate the synchronization script xSQL Schema Compare will create the following statement:

Notice the NOT NULL without a default value specification in the ALTER TABLE statement. Logically this is correct because one of the tables has the ‘TestColumnNotNull’ with a NOT NULL constraint and no default value, so it will try to create the same column on the other table. But, because these tables both have data, adding a column in one of them would mean that the values for this column would be NULL. Since the column has a NOT NULL constraint SQL Server will not allow the addition of this column.

Workaround: The solution to this is to force xSQL Schema Compare to script the new columns as NULL by checking the ‘Script new columns as NULL’ in the comparison options dialog (picture below):

This time, when the synchronization script is generated it will not add the NOT NULL constraint, and SQL Server will allow the schema synchronization. After this, the column on the target database can be manually updated with valid data, or the data synchronization with xSQL Data Compare can be performed, which will fill the newly added column with values. To add the NOT NULL constraint, all that needs to be done is to uncheck the ‘Script new columns as NULL’ and perform the schema comparison and synchronization. This time SQL Server will have no problem with adding a NOT NULL constraint in the new column because it does not have any NULL values.

And there you go, the tables are synchronized. CodeProject

Read More