Basic Tips of SQL Scripts : Need to know while troubleshooting
Good SQL knowledge is very necessary when you do technical troubleshooting. In this part you must have knowledge about basic sql like insert, update , delete ,create, drop , truncate.
->Also you must have knowledge about few keywords like "between", "top " , "order by" , "group by ", "distinct", "Replace",
->Also learn Join , mainly inner join will be very useful. Trigger, stored procedure will be also useful to troubleshoot deeply.
->Trigger will be helpful when table's data are being updated and you need to track all data information , you can create new table and track records in that table. see below trigger example,
->Cursor knowledge is required when you will need to update multiple rows dynamically.
-> Also try to learn SQL Server Profiler , it will be very useful when you want to track sql scripts
You can follow below article for this:
http://www.codeproject.com/Articles/21371/SQL-Server-Profiler-Step-by-Step
See below sample scripts to get basic idea of inner join , cursor, trigger:
Inner Join Sample
2)
Trigger Sample
Good SQL knowledge is very necessary when you do technical troubleshooting. In this part you must have knowledge about basic sql like insert, update , delete ,create, drop , truncate.
->Also you must have knowledge about few keywords like "between", "top " , "order by" , "group by ", "distinct", "Replace",
->Also learn Join , mainly inner join will be very useful. Trigger, stored procedure will be also useful to troubleshoot deeply.
->Trigger will be helpful when table's data are being updated and you need to track all data information , you can create new table and track records in that table. see below trigger example,
->Cursor knowledge is required when you will need to update multiple rows dynamically.
-> Also try to learn SQL Server Profiler , it will be very useful when you want to track sql scripts
You can follow below article for this:
http://www.codeproject.com/Articles/21371/SQL-Server-Profiler-Step-by-Step
See below sample scripts to get basic idea of inner join , cursor, trigger:
Inner Join Sample
select h.ID,m.ID as
moduleid,dt.ID as DocTypeId,dt.Name as docTypeName,tt.Name as
transactionTypeNAME,tt.ID AS tRANSACTIONTYPEID,dt.* from DocumentType dt
inner join
TransactionType tt on dt.TransactionType_ID =tt.ID
inner join Module m
on tt.Module_ID=m.ID
inner join
HostApplication h on m.HostApplication_ID=h.ID
order by dt.Name desc
Cursor Sample:
1)
DECLARE @i int
DECLARE @HaldID
bigint
DECLARE
@HaldParentID varchar(250)
DECLARE @UpdateHALD
CURSOR
--BBCRM - Prospect -
442
PRINT 'BBCRM -
Prospect - 442'
SET @i=0;
SET @UpdateHALD =
CURSOR FOR
SELECT ID,ParentID
FROM PaperSave..HostApplicationLocalData WITH (NOLOCK)
WHERE TransactionType_ID=442 AND ID IN (SELECT
HostApplicationLocalData_ID FROM Document) and len(HostDataValues)<40
OPEN @UpdateHALD
FETCH NEXT
FROM @UpdateHALD
INTO @HaldID,@HaldParentID
WHILE @@FETCH_STATUS
= 0
BEGIN
WITH GetHALD AS
(SELECT UPPER(CONVERT(varchar(100),PROS.ID)) AS [ParentID]
,UPPER(CONVERT(varchar(100),PROS.ID)) AS [Prospect ID] ,C.NAME AS
[Constituent],c.LOOKUPID As [Lookup ID],ISNULL((select NAME From
Shelby..CONSTITUENT Where ID=PROS.PROSPECTMANAGERFUNDRAISERID),'')AS [Prospect
Manager],convert(date,isnull(PROS.PROSPECTMANAGERSTARTDATE,'1999-01-01')) AS
[Start Date],ISNULL(AD.CITY,'') AS
[City],ISNULL(STATE.DESCRIPTION,'') As [State],ISNULL(Ad.POSTCODE,'') As
[Zip] FROM Shelby..PROSPECT PROS LEFT JOIN Shelby..CONSTITUENT C on PROS.ID =
C.ID LEFT JOIN (select CONSTITUENTID, CITY,
POSTCODE,ADDRESSBLOCK,Convert(varchar(100),STATEID) as STATEID From
Shelby..ADDRESS where ISPRIMARY = 1) AD ON c.ID = AD.CONSTITUENTID LEFT JOIN Shelby..STATE ON STATE.ID =
AD.STATEID WHERE UPPER(CONVERT(varchar(100),PROS.ID))=@HaldParentID)
UPDATE
PaperSave..HostApplicationLocalData
SET
HostData='<?xml version="1.0"
encoding="utf-8"?><Prospect><Prospect_ID><![CDATA['
+ (SELECT ISNULL([Prospect ID],'') FROM GetHALD WHERE
ParentID=HostApplicationLocalData.ParentID) + ']]></Prospect_ID><Constituent><![CDATA['
+ (SELECT ISNULL([Constituent],'') FROM GetHALD WHERE
ParentID=HostApplicationLocalData.ParentID) +
']]></Constituent><Lookup_ID><![CDATA[' + (SELECT
ISNULL([Lookup ID],'') FROM GetHALD WHERE ParentID=HostApplicationLocalData.ParentID)
+ ']]></Lookup_ID><Prospect_Manager><![CDATA[' + (SELECT
ISNULL([Prospect Manager],'') FROM GetHALD WHERE
ParentID=HostApplicationLocalData.ParentID) +
']]></Prospect_Manager><Start_Date><![CDATA[' + (SELECT
CONVERT(varchar,[Start Date]) FROM GetHALD WHERE
ParentID=HostApplicationLocalData.ParentID) +
']]></Start_Date><City><![CDATA[' + (SELECT ISNULL([City],'')
FROM GetHALD WHERE ParentID=HostApplicationLocalData.ParentID) +
']]></City><State><![CDATA[' + (SELECT ISNULL([State],'')
FROM GetHALD WHERE ParentID=HostApplicationLocalData.ParentID) +
']]></State><Zip><![CDATA[' + (SELECT ISNULL([Zip],'') FROM
GetHALD WHERE ParentID=HostApplicationLocalData.ParentID) +
']]></Zip></Prospect>'
,
HostDataValues=
(SELECT ISNULL([Prospect ID],'') FROM GetHALD WHERE
ParentID=HostApplicationLocalData.ParentID) + ';' + (SELECT
ISNULL([Constituent],'') FROM GetHALD WHERE
ParentID=HostApplicationLocalData.ParentID) + ';' + (SELECT ISNULL([Lookup
ID],'') FROM GetHALD WHERE ParentID=HostApplicationLocalData.ParentID) + ';' +
(SELECT ISNULL([Prospect Manager],'') FROM GetHALD WHERE
ParentID=HostApplicationLocalData.ParentID) + ';' + (SELECT
CONVERT(varchar,[Start Date]) FROM GetHALD WHERE ParentID=HostApplicationLocalData.ParentID)
+ ';' + (SELECT ISNULL([City],'') FROM GetHALD WHERE
ParentID=HostApplicationLocalData.ParentID) + ';' + (SELECT ISNULL([State],'')
FROM GetHALD WHERE ParentID=HostApplicationLocalData.ParentID) + ';' + (SELECT
ISNULL([Zip],'') FROM GetHALD WHERE ParentID=HostApplicationLocalData.ParentID)
+ ';'
WHERE
TransactionType_ID=442 AND ID=@HaldID
SET @i=@i+1;
PRINT
CONVERT(varchar,@i) + ' Updated'
FETCH NEXT
FROM @UpdateHALD
INTO @HaldID,@HaldParentID
END
CLOSE @UpdateHALD
DEALLOCATE
@UpdateHALD
2)
use PaperSave
DECLARE @i int
DECLARE @HALD_ID
bigint
DECLARE
@HostDataValues varchar(250)
DECLARE
@UpdateParentID CURSOR
declare @s
varchar(200);
declare @a
varchar(200);
declare @b
varchar(200);
declare @c
varchar(200);
declare @d
varchar(200);
declare @e
varchar(200);
declare @f
varchar(200);
declare @g
varchar(200);
declare @h
varchar(200);
declare @Parentid
varchar(200);
SET @UpdateParentID
= CURSOR FOR
select ID ,
HostDataValues from HostApplicationLocalData where TransactionType_ID=340 and
Id=10132
SET @i=0;
OPEN @UpdateParentID
FETCH NEXT
FROM @UpdateParentID
INTO @HALD_ID,@HostDataValues
WHILE @@FETCH_STATUS
= 0
BEGIN
--select
parentid from HostApplicationLocalData
where TransactionType_ID=340 and Id=10132
--
F9ADD13F-B055-497B-83CB-ECF6CEE29C0E;163;AAA
Concrete;Payment;10.00;Cash;2015-07-23;(Payment) 10.00 Cash - AAA Concrete;24;
set
@s=@HostDataValues;
set @a= SUBSTRING(@s, charindex(';', @s)+1,
len(@s) )
--select @a;
set @b= SUBSTRING(@a,CHARINDEX(';',@a)+1 ,len(@a))
set @c= SUBSTRING(@b,CHARINDEX(';',@b)+1 ,len(@b))
set @d= SUBSTRING(@c,CHARINDEX(';',@c)+1 ,len(@c))
--select @d;
set
@e=SUBSTRING(@d,0,CHARINDEX(';',@d) )
select @e;
set @f=
SUBSTRING(REVERSE(@s),2,len(@s))
set @g=
REVERSE(SUBSTRING(@f, 1, charindex(';', @f)-1))
select @g;
set @Parentid=
(select ID from BBInfinity..revenue where batchnumber =@g and amount=@e);
update
HostApplicationLocalData set ParentID=@Parentid where id=@HALD_ID
-- get PO number
from document id
-- store below query
result in one variable
FETCH NEXT
FROM @UpdateParentID
INTO @HALD_ID,@HostDataValues
END
CLOSE
@UpdateParentID
DEALLOCATE
@UpdateParentID
Trigger Sample
USE [BBInfinity]
GO
/****** Object: Table
[dbo].[USR_PAPERSAVE_REVENUEBATCH_MAPPING_trigger] Script Date: 7/2/2015 12:23:33 PM ******/
SET ANSI_NULLS ON
GO
SET
QUOTED_IDENTIFIER ON
GO
CREATE TABLE
[dbo].[USR_PAPERSAVE_REVENUEBATCH_MAPPING_trigger](
[ID]
[uniqueidentifier] NOT NULL,
[COMMITEDBATCHREVENUEID]
[nvarchar](50) NOT NULL,
[ADDEDBYID]
[uniqueidentifier] NOT NULL,
[CHANGEDBYID]
[uniqueidentifier] NOT NULL,
[DATEADDED]
[datetime] NOT NULL,
[DATECHANGED]
[datetime] NOT NULL,
[TS]
[timestamp] NOT NULL,
[TSLONG]
[bigint] NULL,
[Status]
[nvarchar](50) NULL,
[Date]
[datetime] NULL CONSTRAINT
[DF_USR_PAPERSAVE_REVENUEBATCH_MAPPING_trigger_Date] DEFAULT (getdate())
) ON [DEFGROUP]
GO
Insert Trigger:
/****** Object: Trigger
[dbo].[USR_PAPERSAVE_REVENUEBATCH_MAPPING_Insert_Logs] Script Date: 7/2/2015 12:19:26 PM ******/
SET ANSI_NULLS ON
GO
SET
QUOTED_IDENTIFIER ON
GO
--
=============================================
--
Author: <Author,,Name>
-- Create date:
<Create Date,,>
--
Description: <Description,,>
--
=============================================
CREATE TRIGGER
[dbo].[USR_PAPERSAVE_REVENUEBATCH_MAPPING_Insert_Logs]
ON
[dbo].[USR_PAPERSAVE_REVENUEBATCH_MAPPING]
For Insert
AS
BEGIN
--
SET NOCOUNT ON added to prevent extra result sets from
--
interfering with SELECT statements.
SET
NOCOUNT ON;
INSERT INTO
[dbo].[USR_PAPERSAVE_REVENUEBATCH_MAPPING_trigger]
([ID]
,[COMMITEDBATCHREVENUEID]
,[ADDEDBYID]
,[CHANGEDBYID]
,[DATEADDED]
,[DATECHANGED]
,[TSLONG]
,[Status])
Select [ID]
,[COMMITEDBATCHREVENUEID]
,[ADDEDBYID]
,[CHANGEDBYID]
,[DATEADDED]
,[DATECHANGED]
,[TSLONG],'Inserted' from inserted;
END
GO
Delete Trigger:
USE [BBInfinity]
GO
/****** Object: Trigger
[dbo].[USR_PAPERSAVE_REVENUEBATCH_MAPPING_update_Logs] Script Date: 7/2/2015 12:20:07 PM ******/
SET ANSI_NULLS ON
GO
SET
QUOTED_IDENTIFIER ON
GO
--
=============================================
--
Author: <Author,,Name>
-- Create date:
<Create Date,,>
--
Description: <Description,,>
--
=============================================
CREATE TRIGGER
[dbo].[USR_PAPERSAVE_REVENUEBATCH_MAPPING_update_Logs]
ON
[dbo].[USR_PAPERSAVE_REVENUEBATCH_MAPPING]
for update
AS
BEGIN
--
SET NOCOUNT ON added to prevent extra result sets from
--
interfering with SELECT statements.
SET
NOCOUNT ON;
INSERT INTO
[dbo].[USR_PAPERSAVE_REVENUEBATCH_MAPPING_trigger]
([ID]
,[COMMITEDBATCHREVENUEID]
,[ADDEDBYID]
,[CHANGEDBYID]
,[DATEADDED]
,[DATECHANGED]
,[TSLONG]
,[Status])
Select [ID]
,[COMMITEDBATCHREVENUEID]
,[ADDEDBYID]
,[CHANGEDBYID]
,[DATEADDED]
,[DATECHANGED]
,[TSLONG],'Before Update' from
deleted;
END
GO
Update Trigger:
USE [BBInfinity]
GO
/****** Object: Trigger
[dbo].[USR_PAPERSAVE_REVENUEBATCH_MAPPING_After_update_Logs] Script Date: 7/2/2015 12:20:36 PM ******/
SET ANSI_NULLS ON
GO
SET
QUOTED_IDENTIFIER ON
GO
--
=============================================
--
Author: <Author,,Name>
-- Create date:
<Create Date,,>
--
Description: <Description,,>
--
=============================================
CREATE TRIGGER
[dbo].[USR_PAPERSAVE_REVENUEBATCH_MAPPING_After_update_Logs]
ON
[dbo].[USR_PAPERSAVE_REVENUEBATCH_MAPPING]
after update
AS
BEGIN
--
SET NOCOUNT ON added to prevent extra result sets from
--
interfering with SELECT statements.
SET
NOCOUNT ON;
INSERT INTO
[dbo].[USR_PAPERSAVE_REVENUEBATCH_MAPPING_trigger]
([ID]
,[COMMITEDBATCHREVENUEID]
,[ADDEDBYID]
,[CHANGEDBYID]
,[DATEADDED]
,[DATECHANGED]
,[TSLONG]
,[Status])
Select [ID]
,[COMMITEDBATCHREVENUEID]
,[ADDEDBYID]
,[CHANGEDBYID]
,[DATEADDED]
,[DATECHANGED]
,[TSLONG],'After Update' from
inserted;
END
GO
SQL Server CROSS APPLY and OUTER APPLY
When should I use Cross Apply over Inner Join?
RE: Which are more performant, CTE or temporary tables?
No comments:
Post a Comment