Get Default constraint detailes include columns

SELECT df.name as DFconstraintName, a.name as ColumnName

FROM   sys.all_columns a

JOIN   sys.tables t     ON a.object_id = t.object_id

JOIN   sys.schemas s    ON t.schema_id = s.schema_id

JOIN   sys.default_constraints df ON a.default_object_id = df.object_id

WHERE  s.name = 'SchemaName'

AND    t.name = 'TableName'

AND    a.name = 'ColumnName'

msdb.dbo.agent_datetime

msdb.dbo.agent_datetime is translate from integer data and time numbers (in YYYYMMDD and HHMMSS formats) to datetime.

select msdb.dbo.agent_datetime(20170205, 0)

select msdb.dbo.agent_datetime(20170205, 123122)

select msdb.dbo.agent_datetime(20170205, 112)

select run_date,run_time, msdb.dbo.agent_datetime(run_date,run_time)

from msdb.dbo.sysjobhistory

How long did a job run? Or: cast HHMMSS format to seconds

How long did a job run??

It should be simple:

select run_duration from msdb.dbo.sysjobhistory

BUT - NO!

run_duration is Elapsed time in the execution of the job or step in HHMMSS format (int) (from technet.microsoft).

Pay attention, that it is not always has all the 6 digits: 00:10:46 is 1046.

So, we need to cast it:

Cast to seconds:

146 = one minute and 46 seconds = 106 seconds:

select

(      (run_duration % 100) -- Seconds

       + 60 * ((run_duration /100) % 100) -- + minutes to seconds

       + 3600 * ((run_duration / 10000) % 100) -- + Hours to seconds

) as Duration_SEC

from msdb.dbo.sysjobhistory

Cast to 'HH:MM:SS' format:

146 = 00:01:46

select

(CASE len(run_duration)

       WHEN 1 THEN cast('00:00:0'

       + cast(run_duration as char) as char (8))

       WHEN 2 THEN cast('00:00:'

       + cast(run_duration as char) as char (8))

       WHEN 3 THEN cast('00:0'

       + Left(right(run_duration,3),1)

       +':' + right(run_duration,2) as char (8))

       WHEN 4 THEN cast('00:'

       + Left(right(run_duration,4),2)

       +':' + right(run_duration,2) as char (8))

       WHEN 5 THEN cast('0'

       + Left(right(run_duration,5),1)

       +':' + Left(right(run_duration,4),2)

       +':' + right(run_duration,2) as char (8))

       WHEN 6 THEN cast(Left(right(run_duration,6),2)

       +':' + Left(right(run_duration,4),2)

       +':' + right(run_duration,2) as char (8))

END) as Duration

from msdb.dbo.sysjobhistory

An error occurred while executing batch. Error message is: Exception of type 'System.OutOfMemoryException' was thrown

Error message on SSMS:

An error occurred while executing batch. Error message is: Exception of type 'System.OutOfMemoryException' was thrown.

Cause:

Management Studio is running out of memory.

What to do?:

As a lot of computers issues, the most simple way to deal with it is to close the SSMS and open it again…

SentryOne Plan Explorer PRO Goes FREE!

SentryOne Plan Explorer is one of the best tool that help to investigate execution plans on SQL Server.

Now it's free!

https://blogs.sentryone.com/greggonzalez/plan-explorer-pro-goes-free

WITH (TABLOCK) - Minimal logging while insert

In order to improve performance for an insert statement,

We can consider use WITH (TABLOCK) option:

INSERT INTO dbo.testItai WITH (TABLOCK) (id, NAME) SELECT ...

Whet it will do is to minimize the writes to the log.

Try it:

Run insertion statement with and without the TABLOCK and check the log:

INSERT INTO dbo.testItai (id, NAME)

SELECT TOP 5000 ABS(CHECKSUM(NEWID())) % 100,

       char(65+ ABS(CHECKSUM(NEWID())) % 26) + char(65+ ABS(CHECKSUM(NEWID())) % 26) + char(65+ ABS(CHECKSUM(NEWID())) % 26) + char(65+ ABS(CHECKSUM(NEWID())) % 26) + char(65+ ABS(CHECKSUM(NEWID())) % 26) +char(65+ ABS(CHECKSUM(NEWID())) % 26)

FROM master..sysobjects a cross join master..sysobjects b

INSERT INTO dbo.testItai WITH (TABLOCK) (id, NAME)

SELECT TOP 5000 ABS(CHECKSUM(NEWID())) % 100,

       char(65+ ABS(CHECKSUM(NEWID())) % 26) + char(65+ ABS(CHECKSUM(NEWID())) % 26) + char(65+ ABS(CHECKSUM(NEWID())) % 26) + char(65+ ABS(CHECKSUM(NEWID())) % 26) + char(65+ ABS(CHECKSUM(NEWID())) % 26) +char(65+ ABS(CHECKSUM(NEWID())) % 26)

FROM master..sysobjects a cross join master..sysobjects b 

Check the log using

select * from fn_dblog(null, null)

You will see that after the insertion without the TABLOCK 5000 records will be written in the log, and much less with the TABLOCK.

WITH (TABLOCK) has few constraints:

1. The destination table must be empty or without clustered index.

2. There is no non-clustered index on the destination table.

Or conditional join

The case:

SELECT ...

FROM TableA a

JOIN TableB b ON (a.ID = b.ID OR a.ID = b.ParentID)

or:

JOIN TableB b ON     a.ID = b.ID

                           AND    (b.AColumn = @AColumn OR @AColumn = 0)

                           -- @AColumn = 0 mean no condition ("all")

Of course it's not forbidden to use conditional join, 

but most of the time it won't be good to the performance of the query.

There are few possible solutions.

0. Keep use the conditional join

For cases the the performance are not get Worse (example: small tables),

or when the 'complecated' cases are uncommon.

1. UNION

Good for 'simple' cases:

Instead of:

SELECT ...

FROM TableA a

JOIN TableB b ON (a.ID = b.ID OR a.ID = b.ParentID)

use:

SELECT ...

FROM TableA a

JOIN TableB b ON a.ID = b.ID

UNION

SELECT ...

FROM TableA a

JOIN TableB b ON a.ID = b.ParentID

or similar case:

Instead of:

SELECT ...

FROM TableA a

JOIN TableB b ON     a.ID = b.ID

                           AND    (b.AColumn = a.AColumn OR b.BColumn = a.BColumn)

Use:

       SELECT ...

       FROM TableA a

       JOIN TableB b ON     a.ID = b.ID

                                  AND b.AColumn = a.AColumn

UNION

SELECT ...

FROM TableA a

JOIN TableB b ON     a.ID = b.ID

                           AND    b.BColumn = a.BColumn

2. filter in the "where":

Instead of:

SELECT ...

FROM TableA a

JOIN TableB b ON     a.ID = b.ID

                                  AND    (b.AColumn = @AColumn OR @AColumn = 0)

SELECT ...

FROM TableA a

JOIN TableB b ON     a.ID = b.ID

WHERE (b.AColumn = @AColumn OR @AColumn = 0))

3. 2 queries:

For cases that the JOIN is depended on a parameter:

Instead of:

SELECT ...

FROM TableA a

JOIN TableB b ON     a.ID = b.ID

                           AND    (b.AColumn = @AColumn OR @AColumn = 0)

                           -- @AColumn = 0 mean no condition ("all")

Use:

IF @AColumn = 0

       SELECT ...

       FROM TableA a

       JOIN TableB b ON     a.ID = b.ID

ELSE

SELECT ...

FROM TableA a

JOIN TableB b ON     a.ID = b.ID

                           AND    b.AColumn = @AColumn -- without consider the case of @AColumn = 0

4. Insert data to temp table

It's good for similar cases like in point 2, but for queries with a lot of joined tables

What we'll do is to store the data for the conditional join in a temp table and use it in the main query:

Instead of:

SELECT ...

FROM TableA a

JOIN TableB b ON     a.ID = b.ID

                           AND    (b.AColumn = @AColumn OR @AColumn = 0)

                           -- @AColumn = 0 mean no condition ("all")

JOIN TableC c .....

JOIN TableD d .....

JOIN TableE e .....

Use:

CREATE TABLE #TempCond (

INSERT INTO #TempCond ()

       SELECT ... -- take the relevant columns

       FROM TableA a

       JOIN TableB b ON     a.ID = b.ID

                                  AND    (b.AColumn = @AColumn OR @AColumn = 0)

--Or, if required, use one of the solutions in points 1,2 to this temp insertion

SELECT ...

FROM #TempCond

JOIN TableC c .....

JOIN TableD d .....

JOIN TableE e .....