Monthly Archives: May 2010

February 2010 Tip of the Month

SQL Server T-SQL:  Working with time values in queries has always presented challenges.  Especially when you need only part of a datetime value.  Either truncating or rounding time has usually been done by converting a datetime value to a string and then using the parts that are needed.  The functions below can be used to return time in several different states.  Use them to round or truncate time values.

 

CREATE FUNCTION dbo.fnRoundTimeToNearestSecond (@TimeIn DATETIME)

RETURNS DATETIME

AS

BEGIN

      RETURN DATEADD(ms,ROUND(DATEDIFF(ms,DATEADD(day,DATEDIFF(day,0,@TimeIn),0),@TimeIn ),-3),DATEADD(day,DATEDIFF(day,0,@TimeIn),0))

END

GO

CREATE FUNCTION dbo.fnRoundTimeToNearestMinute (@TimeIn DATETIME)

RETURNS DATETIME

AS

BEGIN

      RETURN dbo.fnRoundTimeToMinIncrement(@TimeIn, 1)

END

GO

CREATE FUNCTION dbo.fnTruncateTimeToNearestHour (@TimeIn DATETIME)

RETURNS DATETIME

AS

BEGIN

      RETURN DATEADD(Hour, DATEDIFF(Hour, 0, @TimeIn), 0)

END

GO

CREATE FUNCTION dbo.fnTruncateTimeToNearestDay (@TimeIn DATETIME)

RETURNS DATETIME

AS

BEGIN

      RETURN DATEADD(Day, DATEDIFF(Day, 0, @TimeIn), 0)

END

GO

CREATE FUNCTION dbo.fnTruncateTimeToNearestMonth (@TimeIn DATETIME)

RETURNS DATETIME

AS

BEGIN

      RETURN DATEADD(Month, DATEDIFF(Month, 0, @TimeIn), 0)

END

GO

CREATE FUNCTION dbo.fnTruncateTimeToNearestYear (@TimeIn DATETIME)

RETURNS DATETIME

AS

BEGIN

      RETURN DATEADD(Year, DATEDIFF(Year, 0, @TimeIn), 0)

END

GO

CREATE FUNCTION dbo.fnRoundTimeToMinIncrement

(

        @TIME DATETIME

      , @MIN SMALLINT

)

RETURNS DATETIME

AS

BEGIN

      SET @MIN = ABS(@MIN) — Ensure minutes is a positive number

      IF @MIN > 1440

            SET @MIN = 1440

       — We are going to separate the DATE and TIME parts, do some math and add them together

       RETURN DATEADD(DAY, 0, DATEDIFF(DAY, 0, @TIME)) + — Here’s the DATE part

                  CAST(ROUND(CAST(CAST(CONVERT(VARCHAR(12), @TIME, 14) AS DATETIME) AS FLOAT) * 1440.0 / @MIN, 0) / (1440.0 / @MIN) AS DATETIME) — And now the TIME math

END

GO

January 2010 Tip of the Month

 SQL Server Administration:  Monitor disk space free with a simple routine.  Make sure that you have room on your drives to hold the work being performed.  If you run out of disk space, your SQL Server will stop accepting requests.  In the code below, the @Threshold variable is designed to hold a value in MB that is the amount of space that should be available on any drive connected to your SQL Server.  Change the @Threshold value to find drives that do not have enough space free.  The routine below will return a list of drives that have less than 500MB free.

 

SET NOCOUNT ON

DECLARE @Threshold NUMERIC (5, 0)

SET @Threshold = 500  — 500MB

CREATE TABLE #DiskSpace ( 

  Drive VARCHAR (2), 

  SpaceFreeMB NUMERIC (10, 2)) 

INSERT INTO #DiskSpace (Drive, SpaceFreeMB) 

       EXEC ( ‘master..xp_fixeddrives’ )

SELECT Drive, SpaceFreeMB

FROM #DiskSpace

WHERE SpaceFreeMB < @Threshold

DROP TABLE #DiskSpace

SET NOCOUNT OFF

May 2010 Tip of the Month

SQL Server Administration:  List all principal and object permissions with the following query.  Use this to periodically check that permissions have been properly set for access to objects in your databases.

 

 

SELECT USER_NAME(p.grantee_principal_id) AS principal_name,

        dp.type_desc AS principal_type_desc,

        p.class_desc,

        OBJECT_NAME(p.major_id) AS object_name,

        p.permission_name,

        p.state_desc AS permission_state_desc

FROM sys.database_permissions p

INNER JOIN sys.database_principals dp

ON p.grantee_principal_id = dp.principal_id

April 2010 Tip of the Month

SQL Server Development:  Take apart delimited strings with ease with a function that is commonly found in Visual Basic.  Pass a delimited string into the fnSplit function and it will output the string as a table that can be queried. 

The function is called by this statement: 

SELECT * FROM dbo.fnSplit(‘SQLRx,Solves,Performance,Problems’,’,’)

It will output a table…give it a try!

CREATE FUNCTION [dbo].[fnSplit] (@String VARCHAR(8000), @Delimiter CHAR(1))       

      RETURNS @temptable TABLE (items VARCHAR(8000))         

      AS         

      BEGIN             

            DECLARE @idx INT              

            DECLARE @slice VARCHAR(8000)  

            SELECT @idx = 1                  

                  IF LEN(@String)<1 OR @String IS NULL  RETURN 

            WHILE @idx!= 0             

            BEGIN                 

                  SET @idx = CHARINDEX(@Delimiter,@String)                 

                  IF @idx!=0                     

                        SET @slice = LEFT(@String,@idx – 1)                 

                  ELSE                    

                        SET @slice = @String

                  IF(LEN(@slice)>0)                

                        INSERT INTO @temptable(items) VALUES(@slice)

                  SET @String = RIGHT(@String,LEN(@String) – @idx)                 

                  IF LEN(@String) = 0 BREAK             

            END     

      RETURN         

      END

GO

March 2010 Tip of the Month

SQL Server 2008 Installation:  Sometimes installing a clustered instance of SQL Server 2008 on a Windows Server 2008 Cluster can be difficult to accomplish.  Establishing proper permissions for the service accounts and knowing how to perform a slipstream installation can make your installation a success.  Make sure that the Windows OS has installed properly (meaning that there were no errors during install) and that the cluster has access to all necessary resources and can fail over quickly.  After verifying the Cluster, make sure to disable the User Account Control (UAC) and grant the following permissions to the SQL Server service accounts:

  • Act as part of the operating system
  • Adjust memory quotas for a process
  • Allow log on locally
  • Allow log on through Terminal Services
  • Bypass traverse checking
  • Impersonate a client after authentication
  • Lock pages in memory
  • Log on as a batch job
  • Log on as a service
  • Perform Volume Maintenance Tasks
  • Manage auditing and security log
  • Replace a process level token

 

Perform a slipstream installation by following the steps in this KB article:

http://support.microsoft.com/kb/955392

Take note of this KB article to further resolve installation issues with SQL Server 2008 SP1 CU6:

http://support.microsoft.com/kb/976899