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 ...
The need to join tables in order to determine which records to delete is a common requirement. The syntax can be somewhat tricky because you need to use an alias for the table you want to delete from. First, here is a simple example: [cc lang=”sql”] DELETE o FROM #orders o JOIN #customers c ON c.customer_id = o.customer_id WHERE c.last_name = ‘jones’ AND c.first_name = ‘alexandria’ [/cc] The following query will delete all the order records from the customers who have the name alexandria jones. This will only delete records from the orders table, however I strongly suggest you perform a SELECT statement prior to actually performing the delete using the same query as such: [cc lang=”sql”] –DELETE o SELECT o.* FROM #orders o JOIN #customers c ON c.customer_id = o.customer_id WHERE c.last_name = ‘jones’ AND c.first_name = ‘alexandria’ [/cc] This will show you the exact records you are about to delete. Once you get the count, I actually suggest you take one additional safety measure to ensure you don’t delete more records by using the TOP clause: [cc lang=”sql”] –DELETE o SELECT count(o.*) FROM #orders o JOIN #customers c ON c.customer_id = o.customer_id WHERE c.last_name = ‘jones’ AND c.first_name = ‘alexandria’ — if the above query returns 5 records, set 5 below DELETE TOP (5) o FROM #orders o JOIN #customers c ON c.customer_id = o.customer_id WHERE c.last_name = ‘jones’ AND c.first_name = ‘alexandria’ [/cc] Once again.. test, test, test before you delete!
Continue reading ...
