UDF Domain Name from Email Address

USE Northwind

DECLARE @EMAIL VARCHAR(255)
DECLARE @EMAILDOMAIN VARCHAR(255)

SET @EMAIL = 'shane@microsoft.com'
SET @EMAILDOMAIN = 
        SUBSTRING
                (
                @EMAIL,
                CHARINDEX('@',@EMAIL,1),
                LEN(@EMAIL)
                )

SELECT @EMAILDOMAIN

--CREATE FUNCTION GetEmailDomain
--(
--      @EMAIL VARCHAR(255)
--)
--RETURNS VARCHAR(255)
--AS
--BEGIN
--      RETURN SUBSTRING
--              (
--              @EMAIL,
--              CHARINDEX('@',@EMAIL,1),
--              LEN(@EMAIL)
--              )
--END


--SELECT dbo.GetEmailDomain
--              (
--              'shane@microsoft.com'
--              )
--RESULT: @microsoft.com

References

http://www.sqlteam.com/item.asp?ItemID=1955

String Formatting SPACE Function

Easy enough one but easily forgotten all the same.

Use Northwind

SELECT 'A' + SPACE(10) + 'B' AS 'formatted'

References

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_fa-fz_7oqb.asp

OPTION MAXDOP Limit Query to 1 CPU

Great for large SELECT statements to limit to 1 CPU or where UNIONS or INNER JOINS are running slowly in a multi-CPU environment.

Use Northwind

SELECT * FROM PRODUCTS OPTION (MAXDOP 1)

References

http://www.sql-server-performance.com/case_studies.asp
http://dbforums.com/t555536.html
http://www.experts-exchange.com/Databases/Q_21279226.html

Sub Query NULL Got ya

Using a negative (NOT IN) Sub Query that contains a NULL value returns no result.

Use Northwind

--(1) works as normal
SELECT *        FROM Products 
                WHERE ProductID 
                IN (1,10)

--(2) incorporates and NULL value, works as normal
SELECT *        FROM Products 
                WHERE ProductID 
                IN (NULL,1,10)

--(3) negative, works as normal
SELECT *        FROM Products 
                WHERE ProductID 
                NOT IN (1,10)

--(4) negative with NULL, doesn't return values
SELECT *        FROM Products 
                WHERE ProductID 
                NOT IN (NULL,1,10)




--related best practise, INDEX HINT
SELECT *        FROM Products 
                (INDEX = PK_Products) 
                WHERE ProductID 
                NOT IN (1,10)

--related best practice, RANGE
SELECT *        FROM Products 
                WHERE ProductID 
                BETWEEN 1 AND 10

COALESCE Function replace NULL values

One of those rarely used (and easily forgotten) functions that's listed under 'best practise'. This is a handy function for replacing the NULL value of a column or variable with a DEFAULT value within calculations and formulas.

Not of course to be used within large SELECT statements to replace NULL values within a column due to performance issues.

USE Northwind

DECLARE @caol_tmp INT

--NULL value
SELECT @caol_tmp 'caol_tmp'

--with coalesce
SELECT COALESCE(@caol_tmp,2005) 
        AS'caol_tmp'

--causes an error, must be of the same type
--SELECT COALESCE(@caol_tmp,'string value') 
--      AS 'caol_tmp'

References

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ca-co_9dph.asp

After RESTORE user account does not work

Connection failed:
SQLState: '42000'
SQL Server Error: 4064
[Microsoft][ODBC SQL Server Drive][SQL Server]
Cannot open user default database. Login failed.

OR

Error 21002: [SQL-DMO]User 'database account' 
already exists.

This happens when a database has been restored onto a different MSSQL Server, the database user account no longer refers to the SQL Server login (as the SPID - Service Profile Identifier, is no longer consistent) and you can not edit the permissions of the SQL Server Login as you get an error message saying that user already exists.

Solution

Login as SA (into Query Analyzer) and run the SP on the database in question:

sp_change_users_login 'Update_One', 
                      '[Database Account Name]', 
                      '[SQL Server Login Name]'

Related (and interesting)

--changes default database
sp_defaultdb '[SQL Server Login Name]', 
             '[Database Name]'

--list users (run on actual database)
sp_change_users_login 'Report'

References

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_ca-cz_8qzy.asp
http://www.mcse.ms/archive81-2003-12-190563.html

UPDATE table values from another table

--Shane Devane
--August 2005
--http://practicalsqlserver.blogspot.com

--BUSINESS TASK
--replace old phone number (values) with new 
--phone numbers (values) in members table

--CODE PURPOSE
--update a column value using the corrosponding 
--value in another table across all rows in the 
--table

--PROBLEM
--Two tables. table (A) is large (500K+ rows) and
--contains only 2 columns, 1 column is the old 
--value phone number (which is the value in table
--(b)) and the 2nd column in table (A) is the new
--value and needs to get put into table (b)

--SOLUTION
--get all the old values in table (b) and for 
--each value, loop through each value and simply
--UPDATE the with the new value

--STEPPING THROUGH
--1. using an INNER JOIN, get the old values 
--from the smaller table (b), and the get the new
--values from the large table (a) into a temp 
--table (inner join not used in example version!)
--2. loop through the results and UPDATE the old 
--value in table (b) with the new value.

--USAGE
--you will need to adjust a few lines of code 
--below
--(1) your main query to retrive the correct 
--"OLD Value" and new values
--(2) the UPDATE command you wish to perform on 
--each row(s)

USE Northwind

--initialise variables
DECLARE @oldValue VARCHAR(800)
DECLARE @replaceValue VARCHAR(800)

--get oldvalues and new values into temp table
IF      (
        object_id('tempdb..#TBCopy_08_2005') 
        IS NOT NULL
        )
        BEGIN
        DROP TABLE "#TBCopy_08_2005"
        END

CREATE TABLE #TBCopy_08_2005 (
oldValue VARCHAR(800),
replaceValue VARCHAR(800)
) 
DECLARE sqlCursor CURSOR LOCAL FOR 
        SELECT oldValue, replaceValue
        FROM #TBCopy_08_2005

--(1) populate temporary table
--[user needs to edit]
INSERT #TBCopy_08_2005
        (oldValue,replaceValue) 
        SELECT ContactName, ContactTitle
        FROM customers

--main loop 
OPEN sqlCursor
FETCH NEXT      FROM sqlCursor
                INTO @oldValue, @replaceValue
WHILE (@@FETCH_STATUS = 0)
        BEGIN

        --(2) perform update for rows
        --[user needs to edit]
        UPDATE orders  
        SET ShipName = @replaceValue
        WHERE ShipName = @oldValue
       
        FETCH NEXT      
        FROM sqlCursor
        INTO @oldValue, @replaceValue

        END
CLOSE sqlCursor

--clean up variables
DEALLOCATE sqlCursor