Using Union Instead of OR

Sometimes slow queries can be rectified by changing the query around a bit.  One of these examples can be illustrated when multiple values are compared within a WHERE clause using an OR or IN statement.  Often times, OR can cause a scan against an index or table which may not be the preferable execution plan in terms of IO consumption, or overall query speed. A lot of variables come into play when the query optimizer creates an execution plan.  These variables include a multitude of hardware specs, instance settings, database settings, statistics (table, index, auto-generated), and also the way the query is written.  The one we are changing here is the way the query is written.  As unsuspecting as it may seem, even though two different queries can return the exact same results, the path at which they take can be entirely different just based upon the format of the query. UNION vs OR In most of my experience with SQL Server, the OR is generally less efficient than a UNION. What tends to happen with an OR is it causes a scan more often. Now this sometimes may actually be a better path to take in some cases and I’ll leave that to a separate article but overall I’ve found that touching more records is the biggest cause of slowness. So let’s start our comparison. Here is our OR statement: SELECT SalesOrderID, * FROM sales.SalesOrderDetail WHERE ProductID = 750 OR ProductID = 953 From this execution plan we see […]

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. 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) 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 […]

Continue reading ...

Varchar Vs Char

Every once in a while I’ll find a field someone created where the datatype choice was not the best. This comes up relatively often with Varchar vs Char. All of the confusion just comes from not understanding the differences and causes and effects of each. With that, I’ll outline the only times I will use one over the other. Let’s take a look at the behavior of char vs varchar: CREATE TABLE #meme (   first_name CHAR(50),   last_name VARCHAR(50) ) INSERT INTO #meme (first_name, last_name) SELECT ‘john’, ‘smith’ SELECT * FROM #meme SELECT DATALENGTH(first_name), DATALENGTH(last_name) FROM #meme You can see from the value returned from DATALENGTH that first_name is 50 bytes long despite only taking 4 characters. The other characters are empty strings. Storage wise, varchar is out the gate up to 2 bytes larger than a char because it needs to store the length. It’s 1 byte larger if it’s over 255 characters. That’s why you will often see varchar definitions as varchar(255). Pain-in-the-butt wise, char is far more a pain to deal with unless all the data in the char column takes up the exact amount of space defined for the char column. I typically only use char if all the fields are going to be the exact same size guaranteed. Even then, I’m hesitant. Partly because disk space is not as much of an issue as it was before, and mostly because if you DO have variable length fields in a char column, then the remaining […]

Continue reading ...

SQL 2012 Power View Reporting Services Addin

I have to say, I was pretty blown away by a recent demo of the new Power View product (codename: CRESCENT) released with SQL Server 2012 Reporting Services. The demo shows some really cool visualization features. Power View is only available on Enterprise or Business Intelligence versions of SQL Server 2012 and it is also only accessible through Sharepoint 2010 Enterprise Edition or through Excel 2013. It’s a reporting services add-in written in Silverlight. If you are eager you can download the Excel 2013 preview here. And here is the link for the SQL Server 2012 Eval. The demo seems to run off of a cube, however they do not specify. I’m not sure, but I assume you could also run this off of data source views exposed by reporting services however it probably would not have the cross-reference capability. Power View Youtube Demo. Here are some more interesting links: TechNet – Power View Overview Visualizing the Summer Games with Power View in Excel 2013! Deployment Checklist

Continue reading ...

Drop Table if Exists

To determine if a table exists, it’s best to go against the sys.objects view by querying the object_id using the fully qualified name of the table. The additional ‘type’ field in the where clause ensures that the table that is about to be dropped is a User table and not a system table. IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DumpMe]’) AND TYPE IN (N’U’)) DROP TABLE [dbo].[DumpMe] To drop a temp table you need to look in the tempdb database for it’s existence. IF OBJECT_ID(’tempdb..#Temp’) IS NOT NULL BEGIN DROP TABLE #Temp END Once again, I would highly recommend double checking your code prior to issuing these statements.

Continue reading ...

Featured Articles

 Site Author

  • Thanks for visiting!