MS-EXCEL :: DATEDIF

The syntax for the DATEDIF function is:

= DATEDIF ( start_date , end_date , "interval")

WITH (TABLOCK)

Use TABLOCK to boost your INSERT INTO … SELECT performance

Something that we do frequently in an analytics application is loading data from one table to another. Personally I recommend SSIS for such tasks. But there are chances SSIS is not suitable, then we usually use INSERT INTO … SELECT.

I don’t recommend SELECT … INTO because you can’t control which file group the destination table goes to. And it is very important when I have to use table partition.

So if we need use T-SQL to move data from one place to another one. It is recommended to use TABLOCK option on the destination table. With a few other constraints, the query can be executed with minimal logging.

The few constraints are:

• The database must be in bulk logged recovery model.
• The destination table must be empty or without clustered index.
• There is no non-clustered index on the destination table.

Maybe it is difficult to match the second and third constraint. But we have an alternative which is partition switch. We can partition the destination table. Load the data from source into an empty table with same schema except the index objects (usually I keep the clustered index). After load, we can do a partition switch.

Here is two sample queries:

INSERT INTO dbo.FactDevicePerformance_History
([TimeKey], [DeviceKey], [reads per sec], [read hits per sec],
[seq reads per sec], [seq read hits per sec], [writes per sec],
[write hits per sec], [Kbytes read per sec], [Kbytes written per sec])
SELECT h.* FROM AIR.dbo.FactDevicePerformance_History h
INNER JOIN AIR.dbo.DimTime t ON h.TimeKey = t.TimeKey
WHERE t.[Date] BETWEEN ‘2011-11-1′ AND ‘2011-11-30′

INSERT INTO dbo.FactDevicePerformance_History WITH (TABLOCK)
([TimeKey], [DeviceKey], [reads per sec], [read hits per sec],
[seq reads per sec], [seq read hits per sec], [writes per sec],
[write hits per sec], [Kbytes read per sec], [Kbytes written per sec])
SELECT h.* FROM AIR.dbo.FactDevicePerformance_History h
INNER JOIN AIR.dbo.DimTime t ON h.TimeKey = t.TimeKey
WHERE t.[Date] BETWEEN ‘2011-1-1′ AND ‘2011-1-31′

You can find the only difference is query hint “TABLOCK”. But first query runs 21 seconds, the second one runs only 8 seconds. First query increase the log file from 0.5MB to 672MB. The second one increase the log file from 0.5MB to 3.37MB. There are 2.3 million records moved in the query.

This is what called minimal logging since SQL Server 2005. Anyway you still need to compare these two approaches before you implement. Because with minimal logging SQL Server will force data pages to be flushed to disk before the transaction commits. So if the IO system for data file is not very fast, or the data pages affected by the operation are not sequentially arranged, you might see worse performance with minimal logging.

Time variable

DECLARE @smalldatetime smalldatetime = '1955-12-13 12:43:10';
DECLARE @time time(4) = @smalldatetime;

SELECT @smalldatetime AS '@smalldatetime', @time AS 'time';

--Result
--@smalldatetime          time
------------------------- -------------
--1955-12-13 12:43:00     12:43:00.0000
--
--(1 row(s) affected)

Beginning and End of month calculation

DECLARE @MonthBegin as datetime
DECLARE @MonthEnd as datetime

SELECT @MonthBegin = CAST( CAST(Month(GetDate()) as nvarchar(2)) + '/01/'+ CAST(Year(GetDate()) as nvarchar(4)) + ' 00:00:00.000' as datetime)

SET @MonthBegin = DateAdd(m,-1, @MonthBegin)
SET @MonthEnd = DateAdd(ss,-1,DateAdd(m,1, @MonthBegin))

SELECT @MonthBegin as 'MonthBegin'
, @MonthEnd as 'MonthEnd'

T-SQL Create table syntax

if exists (select * from sysobjects where id = object_id('dbo.tblMetricEvaluationMethod') and sysstat & 0xf = 3)

    drop table dbo.tblMetricEvaluationMethod

GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE dbo.tblMetricEvaluationMethod (

     memID int IDENTITY (1, 1) NOT NULL ,

     memText varchar(200) ,

     memTaskID int ,

     memCreateDate datetime ,

     memCreatedBy int ,

     memModifiedDate datetime ,

     memModifiedByec_EventDescription varchar(1000)

CONSTRAINT PK_tblMetricEvaluationMethod PRIMARY KEY CLUSTERED ( memID )

CONSTRAINT FK_tblMetricEvaluationMethod FOREIGN KEY (memCreatedBy) REFERENCES tblUsers(UserID)

)

GO

SET ANSI_PADDING OFF
GO



===========================================================

Important to remember that ORDER BY works below when TOP is used.
--------------------------------------------------------------------

USE [database]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].vw_name
as
SELECT TOP 10
[col1], [col2], ...
FROM dbo.[tblName]
GROUP BY [col1], [col2], ...
ORDER BY [col1], [col2], ...
GO

ALTER TABLE multiple columns

ALTER TABLE Countries
ADD  
HasPhotoInReadyStorage  bit,
 HasPhotoInWorkStorage  bit,
 HasPhotoInMaterialStorage bit,
 HasText  bit;

Clustered Index syntax

USE [DWHCBI]
GO

SET ARITHABORT ON
SET CONCAT_NULL_YIELDS_NULL ON
SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET NUMERIC_ROUNDABORT OFF

GO

/*** Object: Index [ndx_unique] Script Date: 4/17/2014 9:40:23 AM ****/
CREATE UNIQUE CLUSTERED INDEX [ndx_unique] ON [dbo].[Transform_Rolling_12_Months]
(
[Month_ID] ASC,
[Rolling12Month] ASC
)WITH (PAD_INDEX = OFF

    , STATISTICS_NORECOMPUTE = OFF

    , SORT_IN_TEMPDB = OFF

    , IGNORE_DUP_KEY = OFF

    , DROP_EXISTING = OFF

    , ONLINE = OFF

    , ALLOW_ROW_LOCKS = ON

    , ALLOW_PAGE_LOCKS = ON

    , FILLFACTOR = 95) 
ON [Primary]
GO

T-SQL Error Handling

BEGIN Transaction

BEGIN TRY


PRINT 'STEP 1 COMPLETE'

PRINT 'STEP 2 COMPLETE'

PRINT 'STEP 3 COMPLETE'
--delete from vendorcompanyvendor where vendorcompanyid > @MaxVendorCompanyID
--truncate table vendorcompanyfyenddate

--select @MaxCompanyID = @MaxCompanyID + 1
--select @MaxVendorCompanyID = @MaxVendorCompanyID + 1
--select @MaxVendorCompanyVendorID = @MaxVendorCompanyVendorID + 1
--DBCC CHECKIDENT ('company', RESEED, @MaxCompanyID)
--DBCC CHECKIDENT ('vendorcompany', RESEED, @MaxVendorCompanyID)
--DBCC CHECKIDENT ('vendorcompanyvendor', RESEED, @MaxVendorCompanyVendorID)

-- Log this rollback so the Audit log makes sense afterwards
EXEC StatusUpdate @MaxDataPeriodID, 3, '[RollBackDataPeriod]', '...completed', null


END TRY

BEGIN CATCH
 SELECT
          ERROR_NUMBER() AS ErrorNumber
        , ERROR_SEVERITY() AS ErrorSeverity
        , ERROR_STATE() AS ErrorState
        , ERROR_PROCEDURE() AS ErrorProcedure
        , ERROR_LINE() AS ErrorLine
        , ERROR_MESSAGE() AS ErrorMessage;

    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;

END CATCH

IF @@TRANCOUNT > 0
    COMMIT TRANSACTION;

List all Database Constraints

SELECT
DISTINCT
Constraint_Name AS [Constraint],
Table_Schema AS [Schema],
Table_Name AS [TableName]
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE

where Constraint_Name like '%Service%'
GO

ALTER Table Primary Key

If a primary key already doesn't already exists. then you want to do this

ALTER TABLE provider ADD PRIMARY KEY(person,place,thing);

If a primary key already exists then you want to do this

drop primary key
ALTER TABLE Table1DROP CONSTRAINT PK_Table1_Col1GO


ALTER TABLE [dbo].[tbl_Excel_Tier1Tier2IDs]
   drop constraint [PK_tbl_Excel_Tier1Tier2IDs]
GO


 ALTER TABLE [dbo].[tbl_Excel_Tier1Tier2IDs]
   ADD PRIMARY KEY(Agent_Name, PwC_GUID, Effective_Date);


ALTER TABLE dbo.tblComment_Review
ADD FOREIGN KEY (Record_ID)
REFERENCES [dbo].[tblSurvey_Response_2](Record_ID)