Using Coalesce

The coalesce function is used to find the first non-null value. The function takes limitless number of parameters in order to evaluate the first non null. If all the parameters are null, then COALESCE will also return a NULL value. [cc lang=”sql”] — hard coded example SELECT MyValue = COALESCE(NULL, NULL, ‘abc’, 123) [/cc] The example above returns back ‘abc’ as it is the first non null value. When would you use COALESCE you ask? Well, the most common scenario I use it in is when I am joining two or more tables together and the tables all contain an acceptable value for, say, firstname. However if firstname is null, in the first table, we will want to use it from the second table, and so forth. [cc lang=”sql”] SELECT FirstName = COALESCE (a.FirstName, b.First_Name, c.Fname) FROM HRUsers a LEFT JOIN MarketingUsers b ON b.EmployeeID = a.EmployeeID LEFT JOIN SalesUsers c ON c.EmployeeID = a.EmployeeID [/cc] This example basically says, if FirstName is populated in the HRUsers table, we want to use that one first, otherwise, use the First_Name field in MarketingUsers, or if that is null, take the FName from SalesUsers. It should be noted that if only comparing two values, the ISNULL function has been proven to be quicker. Continue reading ...

SQL Insert Into Statement

The “insert into” statement is used in order to insert data into an existing table. The syntax for this is fairly simple. In the first section of the statement, you specify the table name and column names in which you are inserting data into. The second part is where the source of the data is coming from. There are actually two different ways to specify source data. One way is using a SELECT statement, and the other way using the VALUES statement. Inserting using the VALUES Statement The VALUES statement is typically used when either hard coding values to be inserted or when specifying variables to be inserted. The SELECT statement is typically used when querying data from an existing table. [cc lang=”sql”] INSERT INTO Customer ( FirstName ,LastName ) VALUES(‘Bill’, ‘Jones’) [/cc] We see here that the VALUES statement takes the actual column values to be inserted into the Customer table. Using this method, you can only insert one row at a time. With the advent of SQL Server 2008, the VALUES statement can actually insert multiple rows at one time by delimiting with a comma. [cc lang=”sql”] INSERT INTO Customer ( FirstName ,LastName ) VALUES (‘Bill’, ‘Jones’) ,(‘John’, ‘Smith’) [/cc] INSERT INTO Using the SELECT Statement The select statement is generally used when selecting from the table. [cc lang=”sql”] INSERT INTO Customer ( FirstName ,LastName ) SELECT FirstName = t.First_Name ,LastName = t.Last_Name FROM Customers_Import t [/cc] This statement will actually insert all the customers from the Customers_Import […]

Alter Index All Tables

This script comes in handy when needing to alter all indexes in a database and rebuild them. This will only work on SQL Server 2005+. It utilizes the ALL keyword in the Alter index statement to rebuild all the indexes for a particular table. This script retrieves all base tables and stores them into a temporary table, then loops through rebuilding everything. [cc lang=”sql”] USE AdventureWorksLT2008 GO SELECT RowNum = ROW_NUMBER() OVER(ORDER BY t.TABLE_NAME) ,TableName = t.TABLE_SCHEMA + ‘.’ + t.TABLE_NAME ,AlterMe = ‘ALTER INDEX ALL ON [‘ + t.TABLE_SCHEMA + ‘].[‘ + t.TABLE_NAME + ‘] REBUILD;’ INTO #Reindex_Tables FROM INFORMATION_SCHEMA.TABLES t WHERE TABLE_TYPE = ‘BASE TABLE’ DECLARE @Iter INT DECLARE @MaxIndex INT DECLARE @ExecMe VARCHAR(MAX) SET @Iter = 1 SET @MaxIndex = ( SELECT COUNT(1) FROM #Reindex_Tables ) WHILE @Iter < @MaxIndex BEGIN SET @ExecMe = ( SELECT AlterMe FROM #Reindex_Tables WHERE RowNum = @Iter ) EXEC (@ExecMe) PRINT @ExecMe + ' Executed' SET @Iter = @Iter + 1 END [/cc] Continue reading ...

SQL Date Comparison

When comparing the datetime datatype in SQL Server, it is important to maintain consistency in order to gaurd against SQL interpreting a date differently than you intend. In at least one occasion I have seen someone specify a short format for a date, like (1/4/08) only to find that SQL interpreted the month as the year. A couple incidences similar to this has scared me enough to be very careful in my SQL Date comparisons. Most of the time when hard coding a date I define it out the way SQL Server defines it (at least according to SQL Profiler). Like this: [cc lang=”sql”] DECLARE @MyDate datetime SET @MyDate = ‘2000-01-04T00:00:00.000’ SELECT * FROM Customers WHERE ModifiedDate = @MyDate [/cc] When defined this way, SQL will have no option to interpret in any other way that what is presented. The downside is, yes, it’s long. However when hard coding dates, I’d personally rather be safe that sorry. I haven’t run into it, nor have I tested it, but I’d hate to have all my dates coded in a system changed just because the next version of SQL or any other system decided to interpret things differently. The other safe way to compare dates is to use SQL Server’s datepart function. Utilizing this method, you can never go wrong either. [cc lang=”sql”] SELECT * FROM Customers WHERE — Compare year DATEPART(yyyy, ModifiedDate) = 2000 — compare month AND DATEPART(mm, ModifiedDate) = 08 — compare day AND DATEPART(dd, ModifiedDate) = 01 [/cc] […]