SQL SERVER – Data Scrambling
declare @AlLChars varchar(100) = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'
--0123456789
--@AlLChars='123456789'
update h
set Fname= RIGHT( LEFT(@AlLChars,ABS(BINARY_CHECKSUM(NEWID())%35) + 1 ),3) + RIGHT( LEFT(@AlLChars,ABS(BINARY_CHECKSUM(NEWID())%35) + 1 ),1) +
' '+RIGHT( LEFT(@AlLChars,ABS(BINARY_CHECKSUM(NEWID())%35) + 1 ),1) + RIGHT( LEFT(@AlLChars,ABS(BINARY_CHECKSUM(NEWID())%35) + 1 ),1) + RIGHT( LEFT(@AlLChars,ABS(BINARY_CHECKSUM(NEWID())%35) + 1 ),5)
from TestTable h
SQL SERVER – JOB Status
SELECT job.NAME NAME
,job.job_ID job_ID
--,job.Originating_Server Originating_Server
,run_requested_date run_requested_date
,stop_execution_date stop_execution_date
FROM msdb.dbo.sysjobs_view job
INNER JOIN msdb.dbo.sysjobactivity
activity ON (job.job_id = activity.job_id)
WHERE run_Requested_date IS
NOT NULL
AND job.NAME IN (N'xyz')
ORDER BY
run_requested_date DESC
SQL SERVER – EXECUTE SSIS PACKAGE FROM T-SQL
/*
GET THE ENVIRONMENT_REFERENCESID WHICH
IS CONFIGURED FOR PROJECT
*/
DECLARE @REFERENCE_ID INT
SELECT @REFERENCE_ID =
REFERENCE_ID
FROM SSISDB.[INTERNAL].[ENVIRONMENT_REFERENCES] ER WITH
(NOLOCK)
JOIN SSISDB.[CATALOG].[PROJECTS] P WITH (NOLOCK) ON ER.PROJECT_ID = P.PROJECT_ID
JOIN SSISDB.[CATALOG].[FOLDERS] F WITH (NOLOCK) ON P.FOLDER_ID = F.FOLDER_ID
WHERE F.NAME = 'FOLDERNAME' AND P.NAME = 'PROJECTNAME'
/*
GENERATE EXECUTIONID BY PASSING
REQUIRED INFORMATION LIKE PACKAGENAME,FOLDERNAME,PROJECTNAME AND
ENVIRONMENT_REFERENCESID
*/
DECLARE @EXECUTION_ID BIGINT
EXEC [SSISDB].[CATALOG].[CREATE_EXECUTION] @PACKAGE_NAME=N'PACKAGE1.DTSX', @EXECUTION_ID=@EXECUTION_ID
OUTPUT,
@FOLDER_NAME=N'FOLDERNAME', @PROJECT_NAME=N'PROJECTNAME',
@USE32BITRUNTIME=FALSE,
@REFERENCE_ID=@REFERENCE_ID
SELECT @EXECUTION_ID
/*
PASS THE PARAMETER VALUES DYNAMICALLY
IN T-SQL . HERE I AM PASSING @VARIABLE1 VALUE TO PARAMETERNAME1 LIKE BELOW
*/
DECLARE @VAR0 INT = @VARIABLE1
EXEC [SSISDB].[CATALOG].[SET_EXECUTION_PARAMETER_VALUE] @EXECUTION_ID, @OBJECT_TYPE=20, @PARAMETER_NAME=N'PARAMETERNAME1',
@PARAMETER_VALUE=@VAR0
/*
EXECUTE SSIS PACKAGE FROM T-SQL
STATEMENT
*/
EXEC [SSISDB].[CATALOG].[START_EXECUTION] @EXECUTION_ID
SQL SERVER – SCRIPT TO DROP NON CLUSTERED INDEX IF EXISTS
SELECT o.[name] AS [Table Name]
,i.[name] AS [Index
Name]
,i.[fill_factor] AS
[Existing Fill Factor]
,'IF EXISTS (SELECT * FROM SYS.INDEXES WHERE
OBJECT_id=OBJECT_ID('''+QUOTENAME(s.name) +'.' + QUOTENAME(o.name)+'''' +') AND name = ''' + (Replace(Replace(i.name,'[',''),']','')) +''' )
DROP INDEX ' + QUOTENAME(i.[name]) + ' ON '
+ QUOTENAME(DB_NAME(DB_ID())) + '.' + QUOTENAME(s.[name]) +'.' + QUOTENAME(o.[name]) + ' '
+ 'WITH ( ONLINE = OFF )'
AS [DROP INDEX Statement]
FROM [sys].[indexes] i
INNER JOIN [sys].[objects] o
ON i.[object_id] = o.[object_id]
AND o.[type] = 'U' AND i.[name] <> 'NULL'
INNER JOIN sys.[schemas] s
ON o.[schema_id] = s.[schema_id]
INNER JOIN [sys].[stats] st
ON i.[name] COLLATE Latin1_General_CI_AS = st.[name] COLLATE Latin1_General_CI_AS
AND o.[object_id] = st.[object_id]
AND o.[type] COLLATE Latin1_General_CI_AS = 'U'
WHERE i.[name] NOT IN (SELECT i.[name]
FROM [sys].[objects] o
INNER JOIN [sys].[indexes]
i
ON o.[object_id] = i.[object_id]
INNER JOIN [sys].[columns]
c
ON o.[object_id] = c.[object_id]
WHERE c.[is_identity] = 1
AND o.[type] = 'U'
AND i.[type_desc] = 'CLUSTERED')
AND o.name NOT LIKE 'sys%'
ORDER BY o.[name]
SQL SERVER – DENSE_RANK() – ORDER BY SOME COLUMN BUT WE SHOULD ADD WHERE FILTER WHILE ASSIGNING RANK
Just another day I received following question from my friend and I find it very interesting. I decided to try it out on SQL Server. The question was – “Can we apply Dense_Rank function order by some Column but we should add where filter while assigning rank? ”
Interesting, isn’t it? So i took this as challenge and try to resolve this requrement as puzzle . So i took below small piece of paper and done some pseudo code. Then developed coding to get the final output
--drop table #TEMPCASE
DECLARE @StudentDetail TABLE
(
-- ID int identity(1,1),
StudentCode VARCHAR(10),
SchoolCode VARCHAR(10),
StartDate DATE
)
INSERT INTO @StudentDetail VALUES ('C00001', 'P00001', '1-Sep-2014')
INSERT INTO @StudentDetail VALUES ('C00001', 'P00002', '1-Oct-2014')
INSERT INTO @StudentDetail VALUES ('C00001', 'P00003', '10-Oct-2014')
INSERT INTO @StudentDetail VALUES ('C00001', 'P00004', '25-Oct-2015')
INSERT INTO @StudentDetail VALUES ('C00002', 'P00001', '1-Oct-2014')
INSERT INTO @StudentDetail VALUES ('C00002', 'P00002', '1-Nov-2014')
INSERT INTO @StudentDetail VALUES ('C00002', 'P00001', '1-Oct-2015')
INSERT INTO @StudentDetail VALUES ('C00002', 'P00002', '1-Nov-2015')
INSERT INTO @StudentDetail VALUES ('C00003', 'P00002', '1-Nov-2015')
select * from @StudentDetail
select ROW_NUMBER() over(order by (StudentCode)) as ID,
ROW_NUMBER() over(partition by StudentCode order by StartDate) as RowID,cast(Null as int) as RankID,*
INTO #TEMPCASE from @StudentDetail
order by 1
--SELECT * FROM #TEMPCASE
declare @i int=1,@count int=(select MAX(RowID) from #TEMPCASE)
declare @RankID int
while @i<=@count
Begin
set @RankID=(select MAX(isnull(RankID,0)) from #TEMPCASE)
--IF exists(SELECT 1 FROM #TEMPCASE where RankID is null)
-- CONTINUE
-- ELSE
-- BREAK
update tt
set RankID=@RankID+t.RankID
from #TEMPCASE tt
join (
select *,DENSE_RANK() over(order by StudentCode) as RankID
from
(
select distinct t1.ID,t1.StudentCode from #TEMPCASE t1
join #TEMPCASE t2
on t1.StudentCode=t2.StudentCode
where t1.RankID is null and t2.RankID is null and t1.RowID=1 and t2.RowID>1
and DATEDIFF(dd,t1.StartDate,t2.StartDate)<=90
union all
select distinct t2.ID,t2.StudentCode from #TEMPCASE t1
join #TEMPCASE t2
on t1.StudentCode=t2.StudentCode
where t1.RankID is null and t2.RankID is null and t1.RowID=1 and t2.RowID>1
and DATEDIFF(dd,t1.StartDate,t2.StartDate)<=90
union all
select distinct t1.ID,t1.StudentCode from #TEMPCASE t1
where t1.RankID is null and not exists (select 1 from #TEMPCASE t2 where t1.StudentCode=t2.StudentCode
and t2.RankID is null
group by t2.StudentCode having COUNT(*)>1)
) t
) t on tt.ID=t.ID
update t1
set RowID=t.RowID
from #TEMPCASE t1
join(
select ID, StudentCode,ROW_NUMBER() over(PARTITION by StudentCode order by StartDate ) as RowID from #TEMPCASE
where RankID is null
) t
on t.ID=t1.ID
set @i=@i+1
End
SELECT * FROM #TEMPCASE
SQL SERVER – SEQUENTIAL NUMBERS FAST LOAD TO TABLE
Problem
I need to load very fastly some sample data to database table with Sequential Numbers. We can load Sequential Numbers with Loops but it is a performance issue because it will load one record in one iteration . What is an alternative for loops to load Sequential Numbers data into table ?
Solution
By using iteratative Common table exepresion(CTE) , we can able to get the results very fastly . In the below query every CTE will sequres the amount of result come from previous CTE results.
I need to load very fastly some sample data to database table with Sequential Numbers. We can load Sequential Numbers with Loops but it is a performance issue because it will load one record in one iteration . What is an alternative for loops to load Sequential Numbers data into table ?
Solution
By using iteratative Common table exepresion(CTE) , we can able to get the results very fastly . In the below query every CTE will sequres the amount of result come from previous CTE results.
CREATE TABLE #T1(Id int NOT NULL PRIMARY KEY, X INT NOT NULL)
CREATE INDEX Ix ON #T1 (X, Id)
;WITH
Pass0 AS (SELECT 1 AS C UNION ALL SELECT 1), --2 rows
Pass1 AS (SELECT 1 AS C FROM Pass0 AS A, Pass0 AS B),--4 rows
Pass2 AS (SELECT 1 AS C FROM Pass1 AS A ,Pass1 AS B),--16 rows
Pass3 AS (SELECT 1 AS C FROM Pass2 AS A ,Pass2 AS B),--256 rows
Pass4 AS (SELECT 1 AS C FROM Pass3 AS A ,Pass3 AS B),--65536 rows
Pass5 AS (SELECT 1 AS C FROM Pass4 AS A ,Pass2 AS B),--1048576 rows
Cte AS (SELECT ROW_NUMBER() OVER (ORDER BY C) AS Number FROM Pass5)
INSERT INTO #T1
SELECT Number, Number
FROM Cte
WHERE Number <= 100000
SSIS – Combination of Parent Package Configuration and SQL Server Package Configuration along with Environment variable
Problem
As explained in this post SQL Server Package Configuration,we can able to make use of SQL Server configuration table values inside SSIS packages by using Environment variables and SQL server configuration . In SSIS Configuration table , it is storing one record for one package variable. If i have 20 + packages , which are Source and destination connections. then we have to create 40 + configurations in SISConfiguration table , even though all source and destinations are same because SSIS Configuration table is saving configurations based on Package variables.
Solution
By using Parent Package variable we can able to use only two records in SQL Server Configuration table instead of each package connection string i.e. one record for Source connection and another for Destination Connection.
What is Parent Package Variable
If a package calls another package using execute package task, then calling package is referred as Parent package and called package is referred as child package. When parent package variable is to be passed to a child package then it is called Parent Package Variable configuration
Sample Package Development :
To explain this , I have created two Packages . One is called as MasterPackage and Another one is called ChildPackage.
Master Package Setup :
In Master Package , i have taken simple execute package task and configure like below . Choose Location of package in File System and Browse child Package and Click on OK to complete .
Set Up Environment Variable in Master Package :
Screenshot 1 : Right Click on Control flow task window and choose Package Configuration tab
Screenshot 3 : Click on Environment Variable drop down and Choose previously created SSIS_Config_DB variable
Set Up SQL Server Configuration in Master Package :
Screenshot 1 : Right Click on Control flow task window and choose Variables and created two variables SourceCon and DestCon. In this two variables , values inside SQL Server Configuration tables will be copied by using SQL Server Configuration
Screenshot 2 : By using ConfigConnection manager ,Value of Souce Column inside SSISConfiguration Table will be Copied to package variable SourceCon and Repeated same steps for DestCon also
Child Package Set Up :
Following steps enable passing a parent package variable to child package:
1. Add a configuration to the child package
2. Select its configuration type as Parent Package Variable
3. Give the name of the parent package variable as 'SourceCon'
4. Click Next and select the Child package SourceConnection ConnectionString as shown in below (by doing this, the Child packageSourceConnection ConnectionString's value
gets overwritten with the value of parent package variable).Continue for DestCon also.
As explained in this post SQL Server Package Configuration,we can able to make use of SQL Server configuration table values inside SSIS packages by using Environment variables and SQL server configuration . In SSIS Configuration table , it is storing one record for one package variable. If i have 20 + packages , which are Source and destination connections. then we have to create 40 + configurations in SISConfiguration table , even though all source and destinations are same because SSIS Configuration table is saving configurations based on Package variables.
Solution
By using Parent Package variable we can able to use only two records in SQL Server Configuration table instead of each package connection string i.e. one record for Source connection and another for Destination Connection.
What is Parent Package Variable
If a package calls another package using execute package task, then calling package is referred as Parent package and called package is referred as child package. When parent package variable is to be passed to a child package then it is called Parent Package Variable configuration
Sample Package Development :
To explain this , I have created two Packages . One is called as MasterPackage and Another one is called ChildPackage.
Master Package Setup :
In Master Package , i have taken simple execute package task and configure like below . Choose Location of package in File System and Browse child Package and Click on OK to complete .
Set Up Environment Variable in Master Package :
Screenshot 1 : Right Click on Control flow task window and choose Package Configuration tab
Screenshot 3 : Click on Environment Variable drop down and Choose previously created SSIS_Config_DB variable
Screenshot 4 : In next wizard screen choose ConnectionString of ConfigConnection under Connection Manager section and click Next and in next screen give name as ConfigConnection and click Ok to save .
Set Up SQL Server Configuration in Master Package :
Screenshot 1 : Right Click on Control flow task window and choose Variables and created two variables SourceCon and DestCon. In this two variables , values inside SQL Server Configuration tables will be copied by using SQL Server Configuration
Screenshot 2 : By using ConfigConnection manager ,Value of Souce Column inside SSISConfiguration Table will be Copied to package variable SourceCon and Repeated same steps for DestCon also
Child Package Set Up :
Following steps enable passing a parent package variable to child package:
1. Add a configuration to the child package
2. Select its configuration type as Parent Package Variable
3. Give the name of the parent package variable as 'SourceCon'
4. Click Next and select the Child package SourceConnection ConnectionString as shown in below (by doing this, the Child packageSourceConnection ConnectionString's value
gets overwritten with the value of parent package variable).Continue for DestCon also.
SSIS – SQL Server Package Configuration
Problem
After we develop SSIS Packages, if we want to move developed packages to another server/ environment ,we need to change so many configuration settings in each SSIS Packages like DB Connection strings, File path and etc. But there is so much of work around this task. How we can take the advantage of the SQL Server Package Configuration which are providing by SSIS ?
Solution
SSIS offers serveral types of package configurations like environment variables,Parent Package and XML Configurations and SQL Server Configuration . In all types of configurations , parameter values are storing outside of SSIS packages and that values are utilizing while executing packages. In this post i am going to explain an approach for implementing SQL Server based package configuration that uses an environment variable to facilitate the deployment of the packages onto different servers without having to modify the packages
What is SQL Server package configuration ?
SQL Server package configuration is nothing but storing configuration values like Connection strings ,File path etc in SQL server table. To utlize this values inside SSIS Packages, we need to create one configuration connection manager inside SSIS package which tells, in which server and which database this SSIS Package Configuration table is exists. lets call this connection name as 'ConfigConnection'.
What is Environment Variable ?
Environment Variable is an variable created at machine level(Operating System level) and we can give the value to the Environment Variable and can use this value inside any of the program which are runing in the machine . In our example we will use SSIS_CONFIG_DB Environment Variable inside our SSIS package. So i have created one SSIS_CONFIG_DB Environment Variable at machine level and given below value
Data Source=localhost;Initial Catalog=SSISConfig;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;
We will see later in post how to create Environment Variable and edit value in this Variable
What is need of environment Variable to make use of SQL Server package configuration ?
As i explained above, to get the configuration values of SQL Server table , we have to create one connection (ConfigConnection ). Since during the development we are pointing to a development DB instance, we need to change the connection information point to the QA or Production source databases without editing the package each time.
To notify what is connection string of ConfigConnection to SSIS package , we have to use environment variable which is already having the connection string value This allows for the package to point to configuration table based on Environment (dev, QA, test, prod, etc).Each configuration table shall have the configuration values that are appropriate to each environment.The environment variable shall exists in all machine where you intend to deploy the package, and its content should be updated to accordingly.
Advantages of SQL Server package configuration over the other package configurations :
Choose Advance System Settings
Choose Environment Variables
Sample Package Development :
Let’s assume we have a package that has a connection manager called ‘SourceConnection’, which has the connection information of the source DataBase where the package is trying to retreive data from. Also we have one more connection manager called ‘DestinationConnection’ where the data is going to be load.
Screenshot 1: Sample Package which has one Data flow task , two OLEDB connections i.e. one Source Connection and one destination connection
Screenshot 3 : Right Click on Control flow task window and choose Package Configuration tab
Screenshot 5 : Click on Environment Variable drop down and Choose previously created SSIS_Config_DB variable
Screenshot 8 : Choose SQL Server type in Configuration Type And in Specify Configuration tab, Choose ConfigConnection String and Choose Configuration Table (for first time we need to create new table ,by clicking New else we can choose from drop down)
Screenshot 11: Repeat same steps for Destination Connection Also
After we develop SSIS Packages, if we want to move developed packages to another server/ environment ,we need to change so many configuration settings in each SSIS Packages like DB Connection strings, File path and etc. But there is so much of work around this task. How we can take the advantage of the SQL Server Package Configuration which are providing by SSIS ?
Solution
SSIS offers serveral types of package configurations like environment variables,Parent Package and XML Configurations and SQL Server Configuration . In all types of configurations , parameter values are storing outside of SSIS packages and that values are utilizing while executing packages. In this post i am going to explain an approach for implementing SQL Server based package configuration that uses an environment variable to facilitate the deployment of the packages onto different servers without having to modify the packages
What is SQL Server package configuration ?
SQL Server package configuration is nothing but storing configuration values like Connection strings ,File path etc in SQL server table. To utlize this values inside SSIS Packages, we need to create one configuration connection manager inside SSIS package which tells, in which server and which database this SSIS Package Configuration table is exists. lets call this connection name as 'ConfigConnection'.
What is Environment Variable ?
Environment Variable is an variable created at machine level(Operating System level) and we can give the value to the Environment Variable and can use this value inside any of the program which are runing in the machine . In our example we will use SSIS_CONFIG_DB Environment Variable inside our SSIS package. So i have created one SSIS_CONFIG_DB Environment Variable at machine level and given below value
Data Source=localhost;Initial Catalog=SSISConfig;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;
We will see later in post how to create Environment Variable and edit value in this Variable
What is need of environment Variable to make use of SQL Server package configuration ?
As i explained above, to get the configuration values of SQL Server table , we have to create one connection (ConfigConnection ). Since during the development we are pointing to a development DB instance, we need to change the connection information point to the QA or Production source databases without editing the package each time.
To notify what is connection string of ConfigConnection to SSIS package , we have to use environment variable which is already having the connection string value This allows for the package to point to configuration table based on Environment (dev, QA, test, prod, etc).Each configuration table shall have the configuration values that are appropriate to each environment.The environment variable shall exists in all machine where you intend to deploy the package, and its content should be updated to accordingly.
Advantages of SQL Server package configuration over the other package configurations :
- DBAs are usually more comfortable working with SQL Server tables than XML files.
- Storing package configuration values in a SQL Server database rather than on the file system
- Simple T-SQL commands such as INSERT, UPDATE and DELETE.
Choose Advance System Settings
Choose Environment Variables
Let’s assume we have a package that has a connection manager called ‘SourceConnection’, which has the connection information of the source DataBase where the package is trying to retreive data from. Also we have one more connection manager called ‘DestinationConnection’ where the data is going to be load.
Screenshot 1: Sample Package which has one Data flow task , two OLEDB connections i.e. one Source Connection and one destination connection
Screenshot 3 : Right Click on Control flow task window and choose Package Configuration tab
Screenshot 5 : Click on Environment Variable drop down and Choose previously created SSIS_Config_DB variable
Screenshot 6 : In next wizard screen choose ConnectionString of ConfigConnection under Connection Manager section and click Next and in next screen give name as ConfigConnection and click Ok to save .
Screenshot 7 : Again go to Package Configuration wizard and click on Add to add new configuration for Source
Screenshot 9: Choose Configuration Filter from drop down list (for new record entry in table just type 'Source' then it will insert one entry in Configuration table else choose from drop down list) to utilize value from SQL Server table dynamically , when package is executing
Screenshot 10 : Next screen , choose SourceConnection ConnectionString to copy dynamically from the SQL Server table value to SourceConnection ConnectionString
Screenshot 11: Repeat same steps for Destination Connection Also
Once we have developed our packages like above, if we want to deploy to another server like QA or Production , We just need to Create One Environment Varible at Server level and need to change connection strings values in Configuration table
Note : Once we create Environment Varible at new Environment , we need to restart that machine to identify in SSIS Packages
SQL SERVER – RUN T-SQL SCRIPT FILES USING SQLCMD WITHOUT OPEN IN SSMS
In Software World, Source of Knowledge is requirement
J.Today I got big SQL Script (600 MB) to execute in SSMS. But due to big file it was not opened in SSMS. So I was searching for solution to execute this file without opening .sql file in SSMS, I found solution in by using sqlcmd we can able to run a Transact-SQL script file. By using SQLCMD we can execute any type SQL Scripts.
To demonstrate, how to use SQLCMD. I have taken simple SQL Script and saved in Notepad and given name as SQLCmdSample.sql and Save the file in D Drive
SQL SERVER – HOW TO DELETE ALL TABLES DATA EXCEPT TOP N RECORDS IN EACH TABLE?
I always likes logical work than regular boring work. So when I am browsing on Google, I found some interesting question like how to delete all tables’ data in SQL Server except top N records in each table?
Here is the script for delete all tables data SQL Server DB except top N records in each table
Note:
Considering all tables do not have any inter dependency on each other
SQL SERVER -- SQL SERVER 2016 WHAT'S NEW ?
1 Query Store
a) Captures queries, query plans, runtime
statistics, etc.
b) Can enforce polices to direct SQL Server Query
processor to execute in specific manner
c) Contains query work load history
d) Helps system-wide or database level
performance analysis and troubleshooting
e) New system views are added to support Query
store
2 Live Query Statistics
a) Live Query plans with system resources
consumption such as CPU, memory for all running queries in the plan
b) You can drill down further into live running
queries from its query plan
c) Helps you watch change of statistics during
query execution
