The Difference Between UNION and UNION ALL

If you’re like me, after using UNION for many years you may have stumbled upon the UNION ALL and thought, what the heck is the difference? Well it’s pretty simple. UNION performs a distinct and gives you the unique results for all UNION’ed queries, whereas UNION ALL does not perform a distinct and gives you all rows regardless of whether they are duplicate. The performance difference is that UNION ALL can be much faster since distinct is an expensive operation. Here is a simple example detailing the difference. We’ll populate a single temp table with one record, then we’ll do both a UNION, and a UNION ALL to see the results. [cc lang=”sql”] — create the table and populate sample data SELECT name = ‘derek’ ,detail = ‘this is record 1’ INTO #faba — perform the UNION on the same table SELECT * FROM #faba UNION SELECT * FROM #faba — perform the UNION ALL on the same table SELECT * FROM #faba UNION ALL SELECT * FROM #faba [/cc] From the results we see below, the UNION only returns one record. The execution plans are identical except for the distinct operation being performed as the last operation before output. From this example we see how much utilization distinct is consuming relational to the other operations. (It’s a lot, however this is a rather simple operation) Long story short, if you know you’re two separate result sets contain distinct values, it’s best to use UNION ALL. If for some reason […]

Continue reading ...

Using WITH (NOLOCK)

The WITH (nolock) hint is an explicit command directed at a specific table or view used to set the transaction isolation level against the table or tables within a view for a query. Once issued, locks will not be used against the data within the table. The advantage to this is there is no chance a deadlock will occur against any other queries running against the table. The other indirect advantage is that less memory will be used in order to hold locks against that data. Example: [cc lang=”sql”] SELECT first_name, last_name, FROM dbo.person p WITH (NOLOCK) JOIN dbo.employee e WITH (NOLOCK) ON e.person_id = p.person_id WHERE p.person_id = 1; [/cc] The nolock setting above is explicit for the table it is being set against. To set the value globally for the scope of the connection, see SET TRANSACTION ISOLATION LEVEL Advantages: Deadlocks will not occur against other queries running against the same data Less memory is utilized due to the lack of row, page, or range level locking Typically allows for much higher concurrency due to lower footprint Disadvantages: Uncommitted data can be read leading to dirty reads Explicit hints against a table are generally bad practice Usage In most places I have worked, with (nolock) has been a generally accepted practice in the specific areas of the system that are not sensitive to data being slightly out of sync. It is important to know where things could go wrong though. The biggest red flag I can think of […]

Continue reading ...

How to Order Numeric Values in a Varchar Field

If you have ever tried to order numerical values in a varchar field you’ll know that the sort doesn’t occur numerically as it would if the datatype were numeric. In other words, the values of 1 and 10 will be sorted together because they both start with a leading 1. To overcome this, we have to cast the values as numeric. But this raises another issue. Since it’s a varchar field we cannot ensure that the values are all numeric which means we additionally have to take into account characters. Let’s start by creating a sample table and populating some values: [cc lang=”sql”] CREATE TABLE #varchar_field ( ID INT IDENTITY(1,1), mixed_field varchar(100), ) INSERT INTO #varchar_field (mixed_field) SELECT ‘1’ union all SELECT ‘4.9’ union all SELECT ’10’ union all SELECT ’50’ union all SELECT ‘6’ union all SELECT ‘a’ union all SELECT ‘z’ union all SELECT ‘A’ [/cc] Now let’s run a simple sort so we can see the default behavior. [cc lang=”sql”] SELECT * FROM #varchar_field ORDER BY mixed_field [/cc] Here we see everything is only sorted by it’s leading character, not taking into consideration the value of the numbers. Now let’s execute a revised version of this order by. [cc lang=”sql”] SELECT * FROM #varchar_field ORDER BY CASE WHEN ISNUMERIC(mixed_field) = 1 THEN CAST(mixed_field AS FLOAT) WHEN ISNUMERIC(LEFT(mixed_field,1)) = 0 THEN ASCII(LEFT(LOWER(mixed_field),1)) ELSE 2147483647 END [/cc] Here we check to see if the field is numeric first. If it is, we cast it to float to deal with […]

Continue reading ...

Substring Between Two Characters

Though SQL may not be the most elegant language for string handling, it does perform most functions and in a set based manner. The SQL substring function basically has the same syntax as found in other languages. In this example we will take the common scenario of extracting a string from between two fixed characters. In our example we’ll start with a domain name that contains the subdomain. We will extract only the top level domain from the results. Keep in mind for the purposes of this example all the URLs listed in the table need to have a subdomain. Setup the Data Let’s begin by creating a new table named URLs and insert some data. [cc lang=”sql”] IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N’dbo.URLs’) AND OBJECTPROPERTY(id, N’IsUserTable’) = 1) –DROP TABLE dbo.URLs CREATE TABLE dbo.URLs( url nvarchar(1024) NULL ) INSERT dbo.URLs (url) VALUES (N’www.litwareinc.com/’) GO INSERT dbo.URLs (url) VALUES (N’www.treyresearch.net/’) GO INSERT dbo.URLs (url) VALUES (N’www.adatum.com/’) GO INSERT dbo.URLs (url) VALUES (N’www.northwindtraders.com/’) GO INSERT dbo.URLs (url) VALUES (N’www.wideworldimporters.com/’) GO INSERT dbo.URLs (url) VALUES (N’www.proseware.com/’) [/cc] Now that we have the data setup let’s start with the extraction. For the substring function, the first parameter is the string we want to parse from, the next parameter is the starting position to be extracted and the last parameter is the number of characters to extract. [cc lang=”sql”] DECLARE @first_char nvarchar(10) DECLARE @second_char nvarchar(10) SET @first_char = ‘.’; SET @second_char = ‘/’; SELECT SUBSTRING ( — column url — […]

Continue reading ...

Selecting Top N Records per Group

When you have multiple rows for let’s say a productID, there will probably be times when you need to select the top n subset of each those productid’s based upon a sort criteria. This is a relatively common TSQL need and there are a couple different methods to do this. First let’s create some dummy data. [cc lang=”sql”] CREATE TABLE [#productzabub]( [SalesOrderID] [int] NOT NULL, [ProductID] [int] NOT NULL ) INSERT [#productzabub] ([SalesOrderID], [ProductID]) VALUES (71782, 707) INSERT [#productzabub] ([SalesOrderID], [ProductID]) VALUES (71783, 707) INSERT [#productzabub] ([SalesOrderID], [ProductID]) VALUES (71784, 707) INSERT [#productzabub] ([SalesOrderID], [ProductID]) VALUES (71782, 708) INSERT [#productzabub] ([SalesOrderID], [ProductID]) VALUES (71783, 708) INSERT [#productzabub] ([SalesOrderID], [ProductID]) VALUES (71784, 708) INSERT [#productzabub] ([SalesOrderID], [ProductID]) VALUES (71782, 711) INSERT [#productzabub] ([SalesOrderID], [ProductID]) VALUES (71783, 711) INSERT [#productzabub] ([SalesOrderID], [ProductID]) VALUES (71784, 711) INSERT [#productzabub] ([SalesOrderID], [ProductID]) VALUES (71782, 712) INSERT [#productzabub] ([SalesOrderID], [ProductID]) VALUES (71783, 712) INSERT [#productzabub] ([SalesOrderID], [ProductID]) VALUES (71784, 712) INSERT [#productzabub] ([SalesOrderID], [ProductID]) VALUES (71782, 714) INSERT [#productzabub] ([SalesOrderID], [ProductID]) VALUES (71783, 714) INSERT [#productzabub] ([SalesOrderID], [ProductID]) VALUES (71784, 714) INSERT [#productzabub] ([SalesOrderID], [ProductID]) VALUES (71782, 715) INSERT [#productzabub] ([SalesOrderID], [ProductID]) VALUES (71783, 715) INSERT [#productzabub] ([SalesOrderID], [ProductID]) VALUES (71784, 715) INSERT [#productzabub] ([SalesOrderID], [ProductID]) VALUES (71783, 716) INSERT [#productzabub] ([SalesOrderID], [ProductID]) VALUES (71784, 716) [/cc] Now that we have our data created, let’s select the top 2 rows for each productid based upon the earliest salesorderid. This salesorderid could actually represent any sequential column in the table, be it a date, or a primary […]

Continue reading ...
  1. Pages:
  3. 1
  4. 2
  5. 3
  6. 4
  7. 5
  8. 6
  9. 7
  10. ...
  11. 18

Featured Articles

 Site Author

  • Thanks for visiting!

Resources

css.php