Monday, November 21, 2011

SQL Server - Check for Leap Year (Part 2)

And here is the second part to check for leap year. This uses the standard algorithm to check for leap year.
For more information about Leap Year: Leap Year on Wikipedia
 


-------------------------------------------------
-- Does the function already exist? 
-- if so... drop it.
-------------------------------------------------
IF OBJECT_ID(N'dbo.xIsLeapYear2',N'FN') IS NOT NULL
BEGIN
    print 'Function found... Dropping'
    DROP FUNCTION dbo.xIsLeapYear2
END

GO

CREATE FUNCTION xIsLeapYear2(@Year SmallInt)
RETURNS CHAR(3)
AS



-------------------------------------------------
--Function will Check to See if the year
--passed in is a leap year.
--
--This function uses an Algorithm to get the job done. 
--
--You pass in a Year and it returns 'Yes' or 'No'
-------------------------------------------------

BEGIN

    DECLARE @CheckYear Char(3)
   
    SET @CheckYear =
            (
              SELECT
                CASE
                    -- Is the Year Divisible by 400 with 0 remaining?
                    WHEN @Year%400 = 0 THEN 'YES'
                   
                    -- Is the Year Divisible by 100 with 0 remaining?
                    WHEN @Year%100 = 0 THEN 'NO'
                   
                    -- Is the Year Divisible by 4   with 0 remaining?
                    WHEN @Year%4   = 0 THEN 'YES'
                ELSE
                    'NO'
                END
            )


 

    RETURN @CheckYear

END

GO




-------------------------------------------------
--Now we will check to see if the code works
-------------------------------------------------


PRINT''
PRINT 'Using the algorithm operator to check leap year.'
PRINT '------------------------------------------------'
PRINT 'Is 1900 a Leap year?  ' + dbo.xIsLeapYear2(1900)
PRINT 'Is 2000 a Leap year?  ' + dbo.xIsLeapYear2(2000)
PRINT 'Is 2008 a Leap year?  ' + dbo.xIsLeapYear2(2008)
PRINT 'Is 2010 a Leap year?  ' + dbo.xIsLeapYear2(2010)





SQL Server - Check for Leap Year (Part 1)

I have this co-worker who is obsessed about leap year.  This post is dedicated to him.

There are various ways to determine if a given year is a leap year.  The first function here is using the various SQL Date functions to get the job done.



-------------------------------------------------
-- Does the function already exist? 
-- if so... drop it.
-------------------------------------------------
IF OBJECT_ID( N'dbo.xIsLeapYear',N'FN' ) IS NOT NULL
BEGIN
    print 'Function found... Dropping'
    DROP FUNCTION dbo.xIsLeapYear
END

GO

CREATE FUNCTION xIsLeapYear(@Year SmallInt)
RETURNS CHAR(3)
AS


-------------------------------------------------
--Function will Check to See if the year
--passed in is a leap year.
--
--This function uses SQL Server Date functions
--to get the job done. 
--
--You pass in a Year and it returns 'Yes' or 'No'
-------------------------------------------------

BEGIN
    DECLARE @Date   DateTime
    DECLARE @Result Char(3)
   

    -- Turn the Passed in year to Feb 28th
    SET @Date  = Convert( Varchar(4), @Year ) + '0228'
   
    -- Add one Day to the date
    SET @Date  = DateAdd( d, 1, @Date )
   
    -- Is the @date variable still February?
    IF ( DatePart(M, @date) = 2 )
        BEGIN
            SET @Result = 'Yes'
        END
    ELSE
        BEGIN
            SET @Result = 'NO'
        END


    RETURN @Result
END

GO


-------------------------------------------------
--Now we will check to see if the code works
-------------------------------------------------

PRINT ''
PRINT 'Using SQL Server Date functions to check Year.'
PRINT '----------------------------------------------'
PRINT 'Is 1900 a Leap year?  ' + dbo.xIsLeapYear(1900)
PRINT 'Is 2000 a Leap year?  ' + dbo.xIsLeapYear(2000)
PRINT 'Is 2008 a Leap year?  ' + dbo.xIsLeapYear(2008)
PRINT 'Is 2010 a Leap year?  ' + dbo.xIsLeapYear(2010)






Sunday, November 20, 2011

SQL Server 2012

I'm very excited to see that the SQL Server 2012 RC0 was recently pushed out.  This has also been known with the code name of "Denali".

Since I mainly work with the Data warehousing and Business Intelligence side of things, I'll post some  of the things that caught my attention...



Microsoft has introduced a new Semantic Model (BISM) to improve Excel, Reporting Services and the  SharePoint experience.It appears that the new Semantic Model is in addition to the current BI Models, so you won't be forced to upgrade your existing stack at launch time.

PowerPivot
  1. Supports Hierachies
  2. Multiple relationships between tables
  3. easily create, edit and manage measures
  4. Key Performance Indicators (KPIs) <-- Nice!
  5. new DAX functions
    1. Time series, distinct counts
       
Analysis Services
  1. The above items from the PowerPivot
  2. In addition to row-level security
  3. partitions 
  4. Direct Query Mode
    1. enables access of information directly form the source system.











Saturday, November 19, 2011

SQL Server - Create indexes on Temp Tables

-------------------------------------------------
--Create temp table to hold values
-------------------------------------------------
CREATE TABLE #myTempTable
(
     ID       int
   , customer varchar(50)
)

-------------------------------------------------
--Create Clustered Index on the ID
-------------------------------------------------
CREATE CLUSTERED INDEX #IX_myTempTable_ID
     ON #myTempTable ( ID )

-------------------------------------------------
--Create Index on customer
-------------------------------------------------
CREATE INDEX #IX_myTempTable_Customer
     ON #myTempTable ( customer )
 
-- Note:  When you drop the table, 
--        the indexes will be dropped at the same time.

SQL Server - Check to see if a temp table already exists



-------------------------------------------------
--Check to see if temp table exists
-------------------------------------------------
IF OBJECT_ID('tempdb..#myTempTable') IS NOT NULL
  BEGIN
   PRINT '#myTempTable Already Exists... Dropping'
   DROP TABLE #myTempTable
  END

ELSE
  BEGIN
    PRINT '#myTempTable does not yet exist...'
  END


-------------------------------------------------
--Create temp table to hold values
-------------------------------------------------
CREATE TABLE #myTempTable
(
     ID       int
   , customer varchar(50)
)

PRINT '#myTempTable Created'

SQL Server - Check to see if a function already exists

So, you need to know if a function already exists in your database?  The code you seek is below.  

Need to know how to create that function as well?  I've created a useless function just for you.

-------------------------------------------------
-- Does the function already exist?
-- if so... drop it.







--
--
-- This will check for the various types of functions
-- Type of:
--   FN = Scalar Function
--   IF = Inline Table Function
--   TF = Table Function
--
-- If you are working with Assembly CLR Functions then you will
-- need to use:
--
--   FS = Assembly (CLR) Scalar Function
--   FT = Assembly (CLR) Table Valued Function
--
-------------------------------------------------IF  EXISTS
    (
        SELECT *
            FROM sys.objects
            WHERE object_id = OBJECT_ID(N'dbo.
xTestFunction')
            AND type in (N'FN', N'IF', N'TF')
    )

  BEGIN
   PRINT 'function found... Dropping'
   DROP FUNCTION dbo.xTestFunction
  END

ELSE
  BEGIN
    PRINT 'function does not yet exist...'
  END

GO

-------------------------------------------------
--Create a useless Test Function
-- Pass in a text string
-- Get the same text string back
-------------------------------------------------
CREATE FUNCTION dbo.xTestFunction( @Input NVarChar(MAX) )

RETURNS NVarChar(MAX)
AS

BEGIN
    DECLARE @Result NVarChar(MAX)

    SET @Result = @Input
  
    RETURN @Result
END

GO


-------------------------------------------------
--Now we will check to see if the code works
-------------------------------------------------
Select dbo.xTestFunction('This is my useless text string Input')