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!
Dropping a column is a fairly straightforward operation as long as no dependencies depend on the column. The following code tested on SQL 2008 returns back almost instantly even when tested against tables with over 1 million rows. It seems the drop column statement performs similar to a truncate statement in that it simply moves a pointer and does not actually delete the data from the pages. The syntax is simple, we can pass in a comma delimited list of columns to drop from the table. [cc lang=”sql”] ALTER TABLE ##my_tables DROP COLUMN first_name, last_name [/cc] For the first example, we’ll create a table and then drop one of it’s columns. [cc lang=”sql”] IF OBJECT_ID(‘tempdb..##meme’) IS NOT NULL BEGIN DROP TABLE ##meme END CREATE TABLE ##meme ( first_name VARCHAR(50), last_name VARCHAR(50), ssn VARCHAR(9) CONSTRAINT ssn_unique UNIQUE ) ALTER TABLE ##meme DROP COLUMN first_name [/cc] That was easy, it simply dropped the column. It won’t always happen that way however. If we have a constraint, index, or key that depends on the column, we need to drop or disable that first. [cc lang=”sql”] IF OBJECT_ID(‘tempdb..##meme’) IS NOT NULL BEGIN DROP TABLE ##meme END CREATE TABLE ##meme ( first_name VARCHAR(50), last_name VARCHAR(50), ssn VARCHAR(9) CONSTRAINT ssn_unique UNIQUE ) ALTER TABLE ##meme DROP COLUMN first_name, ssn [/cc] Here we get the error: So we need to drop the constraint in this case prior to dropping the column. [cc lang=”sql”] IF OBJECT_ID(‘tempdb..##meme’) IS NOT NULL BEGIN DROP TABLE ##meme END CREATE TABLE ##meme ( […]
Continue reading ...
One common requirement in SQL when inserting data between tables is to match the new identity column with the old identity column. The most common solution to this problem is to perform a cursor and do the inserts one at a time. While this may work, it is not very efficient because set based operations are the bread and butter of SQL. Anytime looping comes into play, excessive resource consumption also occurs. There are a couple solutions to this, both use the OUTPUT clause introduced in SQL 2005. The first solution uses an intermediary reference table to hold the old and new values. This is done by outputting the inserted identity columns while sorting the source identity columns. You then join back on the source identity columns to get both old and newly created identities. This solution was created by my good SQL developer friend Mike Newell. Method 1 [cc lang=”sql”] — Drop temp tables if they already exist IF OBJECT_ID(‘TempDB..#source’, ‘U’) IS NOT NULL DROP TABLE #source; IF OBJECT_ID(‘TempDB..#target’, ‘U’) IS NOT NULL DROP TABLE #target; IF OBJECT_ID(‘TempDB..#xref’, ‘U’) IS NOT NULL DROP TABLE #xref; GO — Create the temp tables CREATE TABLE #source (id INT PRIMARY KEY IDENTITY(1, 1), data INT); CREATE TABLE #target (id INT PRIMARY KEY IDENTITY(1, 1), data INT); CREATE TABLE #xref (row INT PRIMARY KEY IDENTITY(1, 1), source_id INT, target_id INT); GO — If xref table is being reused, make sure to clear data and reseed by truncating. TRUNCATE TABLE #xref; — Fill source […]
The OUTPUT clause in SQL Server 2008 was probably one of the most functional T-SQL enhancements added. I personally don’t use it enough because I often forget about it, however I have used it to overcome some serious deadlock incidents. It basically works in conjunction with INSERT, UPDATE, or DELETE. In my opinion it will probably be most utilized in an update statement, however I’m sure there are many scenarios I may be forgetting. The way it works is while performing one of these statements, you have the option to output any data within the rows that are being INSERTED UPDATED or DELETED — into another table. This is important because it allows only a single pass through on the table. Whereas before when performing an update to a table, in order to get in-row data you would have to perform a separate select. This could especially become a problem if the update and select needed to be within a transaction. But the output clause is transactional by nature because it occurs within a single statement. Let’s look at an example: [cc lang=”sql”] DECLARE @customer_id int = 1234; DECLARE @customer table ( first_name varchar(50), last_name varchar(50), phone_number varchar(50), visit_count int; ); UPDATE c SET customer_visit_count += 1 OUTPUT DELETED.first_name, DELETED.last_name, DELETED.phone_number, INSERTED.customer_visit_count INTO @customer FROM [customer] c WHERE c.customer_id = @customer_id; SELECT first_name, last_name, phone_number, visit_count FROM @customer; [/cc] This allows a single query (and one transaction) to be used in order to update and select from a table. The […]
Continue reading ...
