Using Cursors

Using Cursors:

DECLARE @date_key INT

DECLARE date_cursor CURSOR FOR

SELECT date_key FROM dim_date WHERE [year] = 2013 ORDER BY date_key
 
OPEN date_cursor
FETCH NEXT FROM date_cursor INTO @date_key

WHILE @@FETCH_STATUS = 0
BEGIN

    SELECT @date_key

    --Insert code by @date_key here
   
   
    FETCH NEXT FROM date_cursor INTO @date_key
END

CLOSE date_cursor
DEALLOCATE date_cursor

Collation Conflict between databases (tempdb and user databases)

Msg 468, Level 16, State 9, Line 21
Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.

Use the COLLATE DATABASE_DEFAULT suffix on VARCHAR columns:

SELECT *
FROM hif_dim_account AS a
INNER JOIN #temp_hierarchy AS t
  ON t.account COLLATE DATABASE_DEFAULT = a.account COLLATE DATABASE_DEFAULT
INNER JOIN hif_dim_hierarchy AS h ON h.business_description COLLATE DATABASE_DEFAULT = t.business_description COLLATE DATABASE_DEFAULT

Row Counts for All Tables in a Database

IF OBJECT_ID('tempdb..#T','U') IS NOT NULL DROP TABLE #T
GO

CREATE TABLE #T (TableName nvarchar(500),NumberOfRows int)
GO

INSERT INTO #T
EXEC sp_msForEachTable 'SELECT PARSENAME(''?'', 1) as TableName,COUNT(*) as NumberOfRows FROM ?'
GO

SELECT * FROM #T ORDER BY NumberOfRows DESC

Search Schema for Text (eg. column names)

SELECT DISTINCT OBJECT_NAME(id) AS ObjectName
FROM syscomments WHERE [text] LIKE '%business_day%'
ORDER BY 1

Select Date Formats Using CONVERT() Function

SELECT date_key
    ,date
    ,DAY(date) AS month
    ,MONTH(date) AS month
    ,YEAR(date) AS year
    ,CONVERT(CHAR(4),date,100) + CONVERT(CHAR(4),date,120) AS a
    ,CONVERT(CHAR(10),date,103) AS b
    ,CONVERT(CHAR(11),date,113) AS c
    ,CONVERT(CHAR(10),date,120) AS d
    ,CONVERT(CHAR(11),date,112) AS e
FROM dim_date
WHERE year = 2012

Concatenate Using FOR XML

SELECT STUFF((
    SELECT ',' AS [text()], CAST(month_name AS NVARCHAR(30)) AS [text()]
    --SELECT *
    FROM dim_date
    WHERE date >= '2013-01-31' AND date <= '2013-12-31' AND date = last_day_of_month
    FOR XML PATH('')
), 1, 1, '') AS months

List of Month Names Using CTE

WITH CTEMonth
AS
(
    SELECT 1 AS month_number
    UNION ALL
    SELECT month_number + 1 FROM CTEMonth WHERE month_number <= 11
)
SELECT month_number,DATENAME(MONTH, DATEADD(MONTH, month_number, 0) - 1) [month_name] FROM CTEMonth