Thursday, March 2, 2017

New Schema Compare build adds option to exclude checked property

A new build of xSQL Schema Compare is available for download. The new build adds a new comparison option that excludes a property called “checked” for check constraints and foreign keys. This property corresponds to the scripting clause “WITH CHECK”/”WITH NOCHECK”. The screen shot below shows the new option.
xSQL Schema Compare is currently free for SQL Server Express.  

Monday, February 20, 2017

Thursday, February 16, 2017

New DB Searcher tool for SQL Server/Azure

Just a Great Tool, No Cost, No Strings

The new xSQL Database Searcher tool, previously known as the xSQL Object Search is now available for download, no cost and no strings attached. The new version:
  • Supports all version of SQL Server from 2005 to 2016
  • Supports SQL Azure v11 and v12
  • Can be used as a stand-alone tool OR as an add-in to SSMS 2008 - 20016
  • Searches both the database objects and SQL Server jobs
  • Supports from simple equal or like searches to regular expression searches
Best of all, it costs nothing and has absolutely no strings attached, just download, install and enjoy!

Please tell us how you like the tool and how we can make it better. We would greatly appreciate it!

Wednesday, February 8, 2017

Get Bose SoundSport or Amazon Fire TV Stick with Alexa on us today

Purchase a new, 1 user, 1 year Silver Subscription before February 15, 2017 and we will send you an Amazon Fire TV Stick with Alexa - just enter the promo code FIRETV17 on the shopping cart page. Once you complete the order please email us with your name, and the address where you want us to ship the Fire TV Stick.
Make it a new, 5 user Silver Subscription license or any other combination of licenses with a total value of $800 or more and we will send you a Bose Sound Sport Wireless Headphones in black, aqua or citron color - just enter promo code SOUNDSPORT17 on the shopping cart page. Once you complete the order please email us with your name, your color choice, and the address where you want us to ship the Bose SoundSport headphones.

Available to US and Canadian residents only. Limit 1 per customer. Expires on January 31, 2017.

Monday, February 6, 2017

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.




Tuesday, January 31, 2017

xSQL Profiler v2 with support for SQL 2016 released

We just release version 2 of our xSQL Profiler tool which now supports from SQL Server 2000 to SQL Server 2016, all versions, all editions.

In addition of adding support for SQL Server 2012, 2014 and 2016 the new version also brings:

  • Simplified setup (standard installation)
  • Revised built-in event definitions
  • Improved validation when adding server/starting trace to make sure correct permissions are in place
  • Possibility to set load data timeout limits
  • Other minor bug fixes and improvements
For you who might not be familiar with it xSQL Profiler is an easy to install, easy to use, agent-less SQL Server monitoring tool that can be utilized for performance, auditing and compliance related tasks. It allows you to configure precise traces and schedule those traces to run at certain intervals on multiple servers. What makes the tool even more helpful is the ability to automatically collect the data from all servers into a central repository (SQL Server database) which you can then query / analyse directly. 

xSQL Profiler is FREE for 1 SQL Server Express instance. You can download the new version from: http://www.xsql.com/download/sql_server_profiler/ 

Monday, January 23, 2017

Get an Amazon Echo Dot OR a Fitbit Charge 2 on us!

Purchase a new, 1 user, 1 year Silver Subscription before the end of this month (January 2017) and we will send you an Amazon Echo Dot (2nd Generation) black or white, your choice - just enter the promo code ECHODOT on the shopping cart page. Once you complete the order please email us with your name, the color choice and the address where you want us to ship the Echo Dot.
Make it a new, 5 user Silver Subscription license or any other combination of licenses with a total value of $800 or more and we will send you a Fitbit Charge 2 Heart Rate + Fitness Wristband Black - just enter promo code FITBIT17 on the shopping cart page. Once you complete the order please email us with your name, your size and the address where you want us to ship the FitBit Charge.

Available to US and Canadian residents only. Limit 1 per customer. Expires on January 31, 2017.

Wednesday, January 18, 2017

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!

Wednesday, January 11, 2017

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!

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.