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 ...
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]
Continue reading ...
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 ...
Changing the datatype of a string that represents a date to an actual date datatype is relatively easy using the CAST function. As long as the date string is in a format recognizable to the regional settings defined on your SQL Server, the conversion can take place with a one line command. There are a few different date datatypes you can choose from. The safest best would be to use the datetime datatype. This is one of the larger date datatypes (8 bytes) that can accommodate dates between the range of ‘1753-01-01 00:00:00.000’ AND ‘9999-12-31 23:59:59.997’ The following string representations of all ‘2012-01-01’ all convert the same with my English (US) settings: [cc lang=”sql”] SELECT CAST(‘2012-01-01′ AS DATETIME) SELECT CAST(’01-01-2012’ AS DATETIME) SELECT CAST(‘2012/01/01′ AS DATETIME) SELECT CAST(’01/01/2012’ AS DATETIME) SELECT CAST(‘010112’ AS DATETIME) SELECT CAST(‘010112’ AS DATETIME) [/cc] SQL Server 2008+ introduced additional date datatypes including DATE & TIME. These data types function as you would expect, only keeping the date without the time, or the time without the date. [cc lang=”sql”] SELECT CAST(‘2012-01-01’ AS DATE) [/cc] The most compatible string format you can use to represent both date & time together is the following. This allows for easy conversion back and forth: [cc lang=”sql”] SELECT CAST(‘2012-01-01T23:05:00.000’ as datetime) SELECT CAST(‘2012-01-01T23:05:00.000’ as time) [/cc] That is the letter T separating the date from the time.
Continue reading ...
