-
Ordered list of returned result set is called Cursor.
-
Without “order by” clause query doesn’t return results in particular order even if clustered index is there.
-
Standard SQL must have “Select” and “have” whereas T-SQL can work with only “Select”
-
“dbo”.”order” standard where as [dbo].[order] is Microsoft format.
-
T-SQL is based upon SQL which in turn based upon relational model which further is based upon set theory and predicate logic.
Archive for the ‘SQL Server 2012’ Category
All about SQl Server 2012 and above
Exam 70-461 Notes – Set Theory
Posted by Pramod Singla on March 23, 2017
Posted in Certifications, MCSA 70-461 | Tagged: exam 70-461, set theory | Leave a Comment »
Exam 70-461 Notes – Grouping Sets
Posted by Pramod Singla on March 23, 2017
-
Data Analysis function can be either a group function or a window function.
-
T-SQL Supports 3 clauses that allow define multiple grouping sets.These operators can generate the same result set as when you use UNION ALL to combine single grouping queries.Here and here are the Details.
-
ROLLUP
-
CUBE
-
Grouping Sets
-
-
Rollup is used where hierarchy is formed in the input columns like for Country,Region,city.
-
Grouping
Grouping_ID
Excepts one input and returns 0 if element is in grouping set else 1
Excepts lists of columns as input and return a integer representing bitmap.
GROUPING is used to distinguish the null values that are returned by ROLLUP, CUBE or GROUPING SETS from standard null values.
https://msdn.microsoft.com/en-us/library/ms178544.aspx
https://msdn.microsoft.com/en-us/library/bb510624.aspx
Posted in Certifications, Development, MCSA 70-461 | Tagged: exam 70-461, grouping | Leave a Comment »
SSIS 2012 (SSISDB)- Check execution status of SSIS Package
Posted by Pramod Singla on November 27, 2015
Problem Statement: How to check the execution status of a SSIS package, deployed in SSISDB, using system tables?
Solution:
DECLARE @status INT=2/*Running The possible values are created (1), running
(2), cancelled (3), failed (4), pending (5), ended unexpectedly (6), succeeded (7),
stopping (8), and completed (9)*/
SELECT *
FROM catalog.executions
WHERE status = @statusSELECT e.executable_id,e.package_name,
e.[executable_name],
es.[execution_path],
es.[execution_duration],
[execution_result] = CASE es.[execution_result]
WHEN 0 THEN ‘Success’
WHEN 1 THEN ‘Failure’
WHEN 2 THEN ‘Completion’
WHEN 3 THEN ‘Cancelled’
END,
es.start_time,
es.end_time
FROM [SSISDB].[catalog].[executables] e
INNER JOIN [SSISDB].[catalog].[executable_statistics] es
ON es.[executable_id] = e.[executable_id]
WHERE e.execution_id IN (SELECT execution_id
FROM catalog.executions
WHERE status = @status)
ORDER BY e.executable_id DESC,es.start_time DESC
Posted in SQL Server 2012, SSIS | 1 Comment »