The XML source does not validate the data in the XML file against the XSD

Important to know:

The SSIS "XML source" element check validation of the input XML file,

but – check only the structure, not the data!

That mean not valid data will pass this validation check!

Is it good or not?

Depend on the case.

But don't forget it…

https://docs.microsoft.com/en-us/sql/integration-services/data-flow/xml-source

Get plans of a Stored Procedure

Select  PS.total_logical_reads/NullIf(DateDiff(Minute,PS.cached_time,GetDate()),0) N,

        Coalesce(DB_Name(PS.database_id),DB_Name(PSh.dbid),DB_Name(PSph.dbid))

        DBName,

        Object_Schema_Name(Coalesce(PS.object_id,PSh.objectid,PSph.objectid),Coalesce(PS.database_id,PSh.dbid,PSph.dbid))

        SchemaName,

        Coalesce(Object_Name(PS.object_id,PS.database_id),Object_Name(PSh.objectid,PSh.dbid),Object_Name(PSph.objectid,PSph.dbid))

        ObjectName,

        PS.*,

        PSh.text,

        PSph.query_plan

From    (Select Object_Name(object_id), *

        From    sys.dm_exec_procedure_stats (NoLock)

        Where   database_id=DB_ID('DatabaseName')

                And Object_Schema_Name(object_id)='SchemaName'

                And Object_Name(object_id)='SpName'

        Order By total_logical_reads/NullIf(DateDiff(Minute,cached_time,GetDate()),0) Desc) PS

Outer Apply sys.dm_exec_sql_text(PS.sql_handle) PSh

Outer Apply sys.dm_exec_query_plan(PS.plan_handle) PSph;

Pivot Table With Multiple Column Aggregates

CREATE TABLE #DoublePivot

(      [ID] INT IDENTITY(1,1) NOT NULL,

       [UserID] INT NOT NULL,

       [SomeType] INT NOT NULL,

       [NumberA] INT NOT NULL,

       [NumberB] INT NOT NULL,

       [Created] DATETIME NOT NULL DEFAULT(getdate())

)

INSERT INTO #DoublePivot

       (      UserID, SomeType, NumberA, NumberB )

       VALUES

       (      1, 1, 20, 10 ),

       (      1, 2, 50, 20 ),

       (      1, 1, 10, 0),

       (      2, 1, 10, 10),

       (      2, 2, 10, 40),

       (      1, 1, 20, 20),

       (      2, 2, 20, 70);

SELECT * FROM #DoublePivot order by UserID, SomeType

SELECT UserID, SomeType, SUM(NumberA) as SumNumberA, SUM(NumberB) as SumNumberB FROM #DoublePivot Group by UserID, SomeType order by UserID, SomeType

                                 

       SELECT UserID,

                     MAX([1]) AS SomeType1_NumberA,

                     MAX([-1]) AS SomeType1_NumberB,

                     MAX([2]) AS SomeType1_NumberB ,

                     MAX([-2]) AS SomeType2_NumberB

       FROM

                     (      SELECT UserID,

                                         SomeType,

                                         (-1 * SomeType) as SomeTypeB,

                                         SUM(NumberA) AS NumberA,

                                         SUM(NumberB) AS NumberB

                           FROM #DoublePivot

                           GROUP BY UserID, SomeType

                     )  BaseTable

                     PIVOT

                     (      SUM(NumberA)

                           FOR SomeType IN ([1], [2])

                     ) AS PivotA

                     PIVOT

                     (      SUM(NumberB)

                           FOR SomeTypeB IN ([-1], [-2])

                     ) AS PivotB

       GROUP BY UserID

DROP TABLE #DoublePivot

*  (-1 * SomeType) - in order to set a new "base" column to the second pivot summary.

"Automatic" extended events - system_health

After SQL Server installation, A default extended events session - system health – is created.

System health runs without any noticeable performance effects, and collect some important events:

• Deadlocks
• Sessions that have waited on locks, waits, etc.
• More….

See all in:

https://docs.microsoft.com/en-us/sql/relational-databases/extended-events/use-the-system-health-session

I use it to catch deadlocks graph, and it was very helpful!

You can also filters events.

If you don’t remember the exact event name, you can type part of it:

After you get the results and see the event you need, you can specify the filter by it:

SSIS error: The SQL command requires a parameter named, which is not found in the parameter mapping

SSIS error message:

The SQL command requires a parameter named "@aaaa", which is not found in the parameter mapping.

Cause and Solutions:

1. The parameter is really not exists. Solution: declare it.

2. Space - " " - in the mapping pf the parameter - see the picture below. Solution: delete the space.

OUTPUT Clause for the MERGE Statements

MERGE ....

USING...

WHEN ...

WHEN ...

...

OUTPUT

   $action,

   inserted.*,

   deleted.*;

Get the number of rows in tables

In order to get the number of rows in tables you can simply run count(*).

But:

1. You need to run it for each table in the database.

2. Fot big tables - it takes time.

So. use thos query in order to get the number of rows in tables, or in one table - set WHERE clause on the table name or ID.

; WITH rCnt AS

(

       SELECT p.object_id, SUM(p.rows) as RowsCount

       FROM sys.partitions p

       WHERE p.index_id IN (0,1) -- heap or clustered index

       GROUP BY p.object_id, OBJECT_NAME(p.object_id)

)

SELECT SCHEMA_NAME(t.schema_id) AS SchemaName,

       t.name as TableName,

       rCnt.RowsCount

FROM   rCnt

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