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: [cc lang=”sql”] 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 [/cc] 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 […]
Continue reading ...
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
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. [cc lang=”sql”] IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DumpMe]’) AND type in (N’U’)) DROP TABLE [dbo].[DumpMe] [/cc] To drop a temp table you need to look in the tempdb database for it’s existence. [cc lang=”sql”] IF OBJECT_ID(‘tempdb..#Temp’) IS NOT NULL BEGIN DROP TABLE #Temp END [/cc] Once again, I would highly recommend double checking your code prior to issuing these statements.
Continue reading ...
Performing a multi-row insert got easier in SQL 2008. A new syntax was added to accomodate multiple rows when using the VALUES statement. This code will only work in 2008+. In order to accomplish the same thing in earlier versions, you need to use the UNION statement. Here is the SQL 2008 syntax: [cc lang=”sql”] CREATE TABLE #meme ( id INT IDENTITY(1,1), first_name VARCHAR(50), last_name VARCHAR(50) ) INSERT INTO #meme ( first_name, last_name ) VALUES (‘john’, ‘smith’), (‘molly’, ‘jones’), (‘summer’, ‘fitzgerald’) SELECT * FROM #meme [/cc] To perform the same thing in earlier versions of SQL Server, you can use the UNION ALL statement: [cc lang=”sql”] INSERT INTO #meme ( first_name, last_name ) SELECT ‘john’, ‘smith’ UNION ALL SELECT ‘molly’, ‘jones’ UNION ALL SELECT ‘summer’, ‘fitzgerald’ SELECT * FROM #meme [/cc] Remember, you want to use UNION ALL instead of just UNION, otherwise if you specify to rows with the same values to be inserted, it will only insert one — as UNION inherently performs a DISTINCT. The last way is to simply call the insert statement multiple times. [cc lang=”sql”] INSERT INTO #meme ( first_name, last_name ) VALUES (‘john’, ‘smith’); INSERT INTO #meme ( first_name, last_name ) VALUES (‘molly’, ‘jones’); INSERT INTO #meme ( first_name, last_name ) VALUES (‘summer’, ‘fitzgerald’); [/cc]
As you may know, the WHERE EXISTS clause is used to constrain records from an outer query with records from an inner query. What does that mean? It’s just another way to say “it limits the result set”. And in SQL, especially in optimization purposes, limiting the result set is the name of the game. USAGE There are basically 6 ways to constrain data. The relationship between the tables and the performance impact will determine which method you need. EXISTS IN JOIN CROSS APPLY LEFT JOIN with WHERE OUTER APPLY with WHERE When you need to limit a result set between two tables that have a 1-1 relationship, it makes little difference which method you use. However if you have a 1-many or many-many relationship you are forced to use either EXISTS or IN because a join will match all records that match forcing you to do the dreaded DISTINCT. Overall, the exists is probably the most performant because it is handles limiting result sets for one-to-many and many-to-many tables most efficiently. This is because it performs a boolean operation where it finds the first instance of existence and returns saying “YES THIS DOES EXIST”. Now, that’s not to say that EXISTS is always more efficient. There are instances I’ve found where a LEFT JOIN WHERE IS NULL is more efficient than using NOT EXISTS but that’s a different article. Also, if there is a chance down the road you will need columns from the table you are constraining against […]
Continue reading ...
