SQL SERVER – Difference Between Union vs. Union All – Optimal Performance Comparison

March 11, 2009 by pinaldave

More than a year ago I had written article SQL SERVER – Union vs. Union All – Which is better for performance? I have got many request to update this article. It is not fair to update already written article so I am rewriting it again with additional information.

UNION
The UNION command is used to select related information from two tables, much like the JOIN command. However, when using the UNION command all selected columns need to be of the same data type. With UNION, only distinct values are selected.

UNION ALL
The UNION ALL command is equal to the UNION command, except that UNION ALL selects all values.

The difference between Union and Union all is that Union all will not eliminate duplicate rows, instead it just pulls all rows from all tables fitting your query specifics and combines them into a table.

A UNION statement effectively does a SELECT DISTINCT on the results set. If you know that all the records returned are unique from your union, use UNION ALL instead, it gives faster results.

Run following script in SQL Server Management Studio to see the result between UNION ALL and UNION. Download complete script from here.

/* Declare First Table */DECLARE @Table1 TABLE (ColDetail VARCHAR(10))INSERT INTO @Table1SELECT 'First'UNION ALLSELECT 'Second'UNION ALLSELECT 'Third'UNION ALLSELECT 'Fourth'UNION ALLSELECT 'Fifth'/* Declare Second Table */DECLARE @Table2 TABLE (ColDetail VARCHAR(10))INSERT INTO @Table2SELECT 'First'UNION ALLSELECT 'Third'UNION ALLSELECT 'Fifth'/* Check the data using SELECT */SELECT *FROM @Table1SELECT *FROM @Table2/* UNION ALL */SELECT *FROM @Table1UNION ALLSELECT *FROM @Table2/* UNION */SELECT *FROM @Table1UNION
SELECT *FROM @Table2GO

In our example we have two tables: @Table1 and @Table2.

Now let us run UNION ALL and UNION together and see the resultset as well as Execution Plan compared to complete set of query. You can always turn on actual execution plan using CTRL+M.

We can see from the resultset of UNION ALL that it returns everything from both the table but from UNION it is very clear that only DISTINCT rows from both the table is only retrieved.

Additionally, when comparing the execution plan of UNION ALL and UNION it is also quite clear that UNION ALL is way less expensive than UNION as it does not have DISTINCT SORT operation.

Let me know what do you think about this article. If you have any suggestion for improvement please let me know and I will update articles according to that.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – 2012 – All Download Links in Single Page – SQL Server 2012

March 11, 2012 by pinaldave

SQL Server 2012 RTM is just announced and recently I wrote about all the SQL Server 2012 Certification on single page. As a feedback, I received suggestions to have a single page where everything about SQL Server 2012 is listed. I will keep this page updated as new updates are announced.

Microsoft SQL Server 2012 EvaluationMicrosoft SQL Server 2012 enables a cloud-ready information platform that will help organizations unlock breakthrough insights across the organization.

Microsoft SQL Server 2012 ExpressMicrosoft SQL Server 2012 Express is a powerful and reliable free data management system that delivers a rich and reliable data store for lightweight Web Sites and desktop applications.

Microsoft SQL Server 2012 Feature PackThe Microsoft SQL Server 2012 Feature Pack is a collection of stand-alone packages which provide additional value for Microsoft SQL Server 2012.

Microsoft SQL Server 2012 Report BuilderReport Builder provides a productive report-authoring environment for IT professionals and power users. It supports the full capabilities of SQL Server 2012 Reporting Services.

Microsoft SQL Server 2012 Master Data Services Add-in For Microsoft ExcelThe Master Data Services Add-in for Excel gives multiple users the ability to update master data in a familiar tool without compromising the data’s integrity in Master Data Services.

Microsoft SQL Server 2012 Performance Dashboard ReportsThe SQL Server 2012 Performance Dashboard Reports are Reporting Services report files designed to be used with the Custom Reports feature of SQL Server Management Studio.

Microsoft SQL Server 2012 PowerPivot for Microsoft Excel® 2010Microsoft PowerPivot for Microsoft Excel 2010 provides ground-breaking technology; fast manipulation of large data sets, streamlined integration of data, and the ability to effortlessly share your analysis through Microsoft SharePoint.

Microsoft SQL Server 2012 Reporting Services Add-in for Microsoft SharePoint Technologies 2010The SQL Server 2012 Reporting Services Add-in for Microsoft SharePoint 2010 technologies allows you to integrate your reporting environment with the collaborative SharePoint 2010 experience.

Microsoft SQL Server 2012 Semantic Language StatisticsThe Semantic Language Statistics Database is a required component for the Statistical Semantic Search feature in Microsoft SQL Server 2012 Semantic Language Statistics.

Microsoft ®SQL Server 2012 FileStream Driver – Windows Logo CertificationCatalog file for Microsoft SQL Server 2012 FileStream Driver that is certified for WindowsServer 2008 R2. It meets Microsoft standards for compatibility and recommended practices with the Windows Server 2008 R2 operating systems.

Microsoft SQL Server StreamInsight 2.0Microsoft StreamInsight is Microsoft’s Complex Event Processing technology to help businesses create event-driven applications and derive better insights by correlating event streams from multiple sources with near-zero latency.

Microsoft JDBC Driver 4.0 for SQL ServerDownload the Microsoft JDBC Driver 4.0 for SQL Server, a Type 4 JDBC driver that provides database connectivity through the standard JDBC application program interfaces (APIs) available in Java Platform, Enterprise Edition 5 and 6.

Data Quality Services Performance Best Practices GuideThis guide focuses on a set of best practices for optimizing performance of Data Quality Services (DQS).

Microsoft Drivers 3.0 for SQL Server for PHPThe Microsoft Drivers 3.0 for SQL Server for PHP provide connectivity to Microsoft SQLServer from PHP applications.

Product Documentation for Microsoft SQL Server 2012 for firewall and proxy restricted environmentsThe Microsoft SQL Server 2012 setup installs only the Help Viewer…install any documentation. All of the SQL Server documentation is available online.

Reference: Pinal Dave (http://blog.sqlauthority.com)

What are slowly changing dimensions (SCD)?

SCD is abbreviation of Slowly changing dimensions. SCD applies to cases where the attribute for a record varies over time.
There are three different types of SCD.
1) SCD1 : The new record replaces the original record. Only one record exist in database – current data.
...

2) SCD2 : A new record is added into the customer dimension table. Two records exist in database – current data and previous history data.
3) SCD3 : The original data is modified to include new data. One record exist in database – new information are attached with old information in same row.

(Read more here http://bit.ly/sql-interview)

L SERVER – Sends backups to a Network Folder, FTP Server, Dropbox, Google Drive or Amazon S3

November 26, 2012 by pinaldave

Let me tell you about one of the most useful SQL tools that every DBA should use – it is SQLBackupAndFTP. I have been using this tool since 2009 – and it is the first program I install on a SQL server. Download a free version, 1 minute configuration and your daily backups are safe in the cloud.
In summary, SQLBackupAndFTP

• Creates SQL Server database and file backups on schedule
• Compresses and encrypts the backups
• Sends backups to a network folder, FTP Server, Dropbox, Google Drive or Amazon S3
• Sends email notifications of job’s success or failure

SQLBackupAndFTP comes in Free and Paid versions (starting from $29) – see version comparison. Free version is fully functional for unlimited ad hoc backups or for scheduled backups of up to two databases – it will be sufficient for many small customers.

What has impressed me from the beginning – is that I understood how it works and was able to configure the job from a single form (see Image 1 – Main form above)

1. Connect to you SQL server and select databases to be backed up
2. Click “Add backup destination” to configure where backups should go to (network, FTP Server, Dropbox, Google Drive or Amazon S3)
3. Enter your email to receive email confirmations
4. Set the time to start daily full backups (or go to Settings if you need Differential or  Transaction Log backups on a flexible schedule)
5. Press “Run Now” button to test

You can get to this form if you click “Settings” buttons in the “Schedule section”. Select what types of backups and how often you want to run them and you will see the scheduled backups in the “Estimated backup plan” list

A detailed tutorial is available on the developer’s website.

Along with SQLBackupAndFTP setup gives you the option to install “One-Click SQL Restore” (you can install it stand-alone too) – a basic tool for restoring just Full backups.

However basic, you can drag-and-drop on it the zip file created by SQLBackupAndFTP, it unzips the BAK file if necessary, connects to the SQL server on the start, selects the right database, it is smart enough to restart the server to drop open connections if necessary – very handy for developers who need to restore databases often.

You may ask why is this tool is better than maintenance tasks available in SQL Server? While maintenance tasks are easy to set up, SQLBackupAndFTP is still way easier and integrates solution for compression, encryption, FTP, cloud storage and email which make it superior to maintenance tasks in every aspect.

On a flip side SQLBackupAndFTP is not the fanciest tool to manage backups or check their health. It only works reliably on local SQL Server instances. In other words it has to be installed on the SQL server itself. For remote servers it uses scripting which is less reliable. This limitations is actually inherent in SQL server itself as BACKUP DATABASE command  creates backup not on the client, but on the server itself.

This tool is compatible with almost all the known SQL Server versions. It works with SQL Server 2008 (all versions) and many of the previous versions. It is especially useful for SQL Server Express 2005 and SQL Server Express 2008, as they lack built in tools for backup.

I strongly recommend this tool to all the DBAs. They must absolutely try it as it is free and does exactly what it promises. You can download your free copy of the tool from here.

Please share your experience about using this tool. I am eager to receive your feedback regarding this article.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Changing Default Installation Path for SQL Server

November 11, 2012 by pinaldave

Earlier I wrote a blog post about SQL SERVER – Move Database Files MDF and LDF to Another Location and in the blog post we discussed how we can change the location of the MDF and LDF files after database is already created. I had mentioned that we will discuss how to change the default location of the database. This way we do not have to change the location of the database after it is created at different locations.

The ideal scenario would be to specify this default location of the database files when SQL Server Installation was performed. If you have already installed SQL Server there is an easy way to solve this problem. This will not impact any database created before the change, it will only affect the default location of the database created after the change.

To change the default location of the SQL Server Installation follow the steps mentioned below:

Go to Right Click on Servers >> Click on Properties >> Go to the Database Settings screen

You can change the default location of the database files. All the future database created after the setting is changed will go to this new location.

You can also do the same with T-SQL and here is the T-SQL code to do the same.

USE [master]
GOEXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultData', REG_SZ, N'F:\DATA'GOEXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultLog', REG_SZ, N'F:\DATA'GO

What are the best practices do you follow with regards to default file location for your database? I am interested to know them.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

What is SQL Profiler ?

SQL Profiler is a graphical tool that allows system administrators to monitor events in an instance of Microsoft SQL Server. You can capture and save data about each event to a file or SQL Server table to analyze later. For example, you can monitor a production environment to see which stored procedures are hampering performance by executing too slowly.

Use SQL Profiler to monitor only the events in which you are interested. If traces are becoming too large, you can filter them based on the information you want, so that only a subset of the event data is collected. Monitoring too many events adds overhead to the server and the monitoring process and can cause the trace file or trace table to grow very large, especially when the monitoring process takes place over a long period of time.

(Read more here http://bit.ly/sql-interview)

QL SERVER – Find Weekend and Weekdays from Datetime in SQL Server 2012

November 25, 2012 by pinaldave

Yesterday we had very first SQL Bangalore User Group meeting and I was asked following question right after the session.
“How do we know if today is a weekend or weekday using SQL Server Functions?”
Well, I assume most of us are using SQL Server 2012 so I will suggest following solution. I am using SQL Server 2012′s CHOOSE function. It is
SELECT GETDATE() Today,DATENAME(dw, GETDATE()) DayofWeek,CHOOSE(DATEPART(dw, GETDATE()), 'WEEKEND','Weekday','Weekday','Weekday','Weekday','Weekday','WEEKEND') WorkDay
GO

You can use the choose function on table as well. Here is the quick example of the same.
USE AdventureWorks2012
GOSELECT A.ModifiedDate,DATENAME(dw, A.ModifiedDate) DayofWeek,CHOOSE(DATEPART(dw, A.ModifiedDate), 'WEEKEND','Weekday','Weekday','Weekday','Weekday','Weekday','WEEKEND') WorkDay
FROM [Person].[Address] A
GO

If you are using an earlier version of the SQL Server you can use a CASE statement instead of CHOOSE function.
Please read my earlier article which discusses CHOOSE function and CASE statements. Logical Function – CHOOSE() – A Quick Introduction
Reference:  Pinal Dave (http://blog.SQLAuthority.com)