Microsoft SQL Server Help
Microsoft SQL Server database help,articles and t-sql Tips, Business Intelligence, Interview questions and tutorials etc..
Wednesday, June 20, 2012
Foreign key base script generation for delete
with Fkeys as (
select distinct
OnTable = OnTable.name
,AgainstTable = AgainstTable.name
from
sysforeignkeys fk
inner join sysobjects onTable
on fk.fkeyid = onTable.id
inner join sysobjects againstTable
on fk.rkeyid = againstTable.id
where 1=1
AND AgainstTable.TYPE = 'U'
AND OnTable.TYPE = 'U'
-- ignore self joins; they cause an infinite recursion
and OnTable.Name <> AgainstTable.Name
)
,MyData as (
select
OnTable = o.name
,AgainstTable = FKeys.againstTable
from
sys.objects o
left join FKeys
on o.name = FKeys.onTable
where 1=1
and o.type = 'U'
and o.name not like 'sys%'
)
,MyRecursion as (
-- base case
select
TableName = OnTable
,Lvl = 1
from
MyData
where 1=1
and AgainstTable is null
-- recursive case
union all select
TableName = OnTable
,Lvl = r.Lvl + 1
from
MyData d
inner join MyRecursion r
on d.AgainstTable = r.TableName
)
select
max(Lvl) As Lvl
,TableName
,strSql = 'delete from [' + tablename + ']'
from
MyRecursion
group by
TableName
order by
1 desc
,2 desc
Labels:
T-Sql
Wednesday, June 13, 2012
UP (Parent) and Down Level (Child Node) Hierarchy.
Declare @TestTable Table
(
ID int,
Name varchar(50),
ParentID int
)
Insert into @TestTable values
(1,'A',null),
(2,'Aa',1),
(3,'Aaa',1),
(4,'Ab',2),
(5,'Abb',4),
(6,'B',null),
(7,'Ba',6),
(8,'Bbb',7),
(9,'Abc',4),
(10,'Acc',9)
Declare @ID int = 4
;with cte
(
ID,Name,ParentID
)
As
(
Select ID,Name,ParentID
From @TestTable where ID = @ID
Union All
Select a.ID , a.Name,a.ParentID
from cte c inner join @TestTable a on c.ParentID = a.ID
)
,
cte1
(
ID,Name,ParentID
)
As
(
Select ID,Name,ParentID
From cte where ParentID is null
Union All
Select a.ID , a.Name,a.ParentID
from cte1 c inner join @TestTable a on a.ParentID = c.ID
)
Select * from Cte1 Option (MAXRECURSION 32767)
Down Level (Child Node) Hierarchy.
Declare @TestTable Table
(
ID int,
Name varchar(50),
ParentID int
)
Insert into @TestTable values
(1,'A',null),
(2,'Aa',1),
(3,'Aaa',1),
(4,'Ab',2),
(5,'Abb',4),
(6,'B',null),
(7,'Ba',6),
(8,'Bbb',7),
(9,'Abc',4),
(10,'Acc',9)
Declare @ID int = 4
;with cte
(
ID , Name,ParentID,ParentName
)
As
(
Select ID , Name,ParentID,
(select Name from @TestTable where ID = a.ParentID) parentName
From @TestTable a where ID = @ID
Union All
Select m.ID , m.Name,c.ID,c.Name
from cte c inner join @TestTable m on m.ParentID = c.ID
)
Select * from cte Option (MAXRECURSION 32767)
Labels:
T-Sql
SQL Server Interview Questions
1. What is indexing?
Types of index. details about Clustered and non clustered index.
2. What is CTE (Common Table Expression)? advantage?
Use of it, Recursive CTE?
3. Difference between Temp table and table variable?
4. What is Transaction?
Describe Isolation levels deeply.
5. What is locking ? Types of locking., locking levels
6. What is service broker?
7. Backup and restoration. Describe backup types?
one scenario to Restore using 3 backup types.
8. Query for delete duplicate records keep original.
9. Query for paging
10. Query for hierarchy structure.
11. varChar vs nvarchar vs Char
12. How to clear identity field.
13. Nth largest salary of an employee.
14. Rows to column without using loop.
15. What is Cursor ? Types of cursor?
16. Performance Tuning? Profiler,Query tuning advisor.
17. What precautions taking for Query optimization?
18. Triggers ? Instead of Trigger? Have u used DDL trigger?
19. Normalization? Denormalization? Types of normal forms?
20. Apply Clause?
21. Pivot and UnPivot?
22. Difference of SQL Server 2005 and 2008 / 2008 and 2008R2
23. DB Mail vs SQL Mail?
24. Database design for Online shopping.
25. ER diagram ? Explain what for ER Diagram? Table Relation ship.
26. Indexed view? What conditions consider when creating indexed view?
27. SSRS Architecture? Tablix? Types of Group? Chart? New Controls in SQL server 2008R2? Have u used map control?
28. SCD ?
29. What is Collation? explain sensitivities.
30. How can i pass multiple value as a parameter to SP. explain table valued type and xml.
31. What is Dirty Read? phantom read,un repeatable read , lost updates
32. Deadlocking?
33. Explain system databases?
34. Replication , logshipping , mirroring
35. what datatype for using image?
36. What is SQL Injection? how to protect sql injection?
37. Error handling?
38. Table scan Index Seek and Index Scan?
39. DBCC Commands?
40. What are Ranking Functions?
41. Date Functions / string functions?
42. What is the FILLFACTOR?
43. Correlated subquery ? Is it good method?
Wednesday, April 25, 2012
How to stop or disable Recursive Trigger/Nested Trigger in SQL Server.
A trigger execution may occured another trigger execution or same trigger execution. This trigger called Nested Trigger Or Recursive Trigger. SQL Server allows maximum 32 Level recursion.
Disable Nesting/Recursing Triggers
Method 1:
sp_CONFIGURE 'nested_triggers',0
GO
RECONFIGURE
GO
Method 2:
ALTER DATABASE databasename
SET RECURSIVE_TRIGGERS ON | OFF
Method 3:
Restrict Trigger Nesting to certain level.
Put following script in trigger code. This will stop the trigger recursion after certain levels. In following case it will stop after 5 recursion.
IF ((
SELECT TRIGGER_NESTLEVEL()) > 5 )
RETURN
Disable Nesting/Recursing Triggers
Method 1:
sp_CONFIGURE 'nested_triggers',0
GO
RECONFIGURE
GO
Method 2:
ALTER DATABASE databasename
SET RECURSIVE_TRIGGERS ON | OFF
Method 3:
Restrict Trigger Nesting to certain level.
Put following script in trigger code. This will stop the trigger recursion after certain levels. In following case it will stop after 5 recursion.
IF ((
SELECT TRIGGER_NESTLEVEL()) > 5 )
RETURN
Labels:
T-Sql
Tuesday, April 24, 2012
Comma seperated date splitting without using loop.
--Comma seperated date splitting without using loop.
--// Table variable declarion
Declare @TableVar Table
(
ID int,
CustomDates varchar(max)
)
--// Data insertion
Insert into @TableVar values(1,'May 01 2012 4:00AM,May 05 2012 4:00AM,Apr 13 2012 10:00AM')
select * from @TableVar
;with cte
(
ID,CustomDates,strDt,pos
)
As
(
select ID,
CustomDates,
substring(
CustomDates,1,case when (charindex(',',CustomDates) -1) = -1 then Len(CustomDates) else charindex(',',CustomDates) -1 end
) strDt,charindex(',',CustomDates) as pos
from @TableVar
union all
select s.ID,
s.CustomDates,
substring(s.CustomDates,cte.pos+1,
case when (charindex(',',substring(s.CustomDates,cte.pos+1,len(s.CustomDates)))-1) = -1 then len(s.CustomDates) else charindex(',',substring(s.CustomDates,cte.pos+1,len(s.CustomDates)))-1 end)
,pos + case when charindex(',',substring(s.CustomDates,cte.pos+1,len(s.CustomDates))) = 0 then len(substring(s.CustomDates,cte.pos+1,len(s.CustomDates))) else charindex(',',substring(s.CustomDates,cte.pos+1,len(s.CustomDates))) end as pos
from @TableVar s inner join cte on cte.ID = s.ID
where cte.pos < len(s.CustomDates)
)
select strDt from cte OPTION (MAXRECURSION 32767);
---------------------------------------------------------------
Another Method:
Declare @TableVar Table
(
ID int,
Data varchar(max)
)
Insert into @TableVar values(1,'May 01 2012 4:00AM,May 05 2012 4:00AM,Apr 13 2012 10:00AM')
DECLARE @XML XML
SELECT @XML = '<Root><Child><Data>'+REPLACE(Data,',','</Data></Child><Child><Data>')+'</Data></Child></Root>' FROM @TableVar
SELECT CAST(DOC.COL.value('Data[1]','NVARCHAR(400)') AS DATE) AS Data
FROM @XML.nodes('/Root/Child') DOC(COL)
--// Table variable declarion
Declare @TableVar Table
(
ID int,
CustomDates varchar(max)
)
--// Data insertion
Insert into @TableVar values(1,'May 01 2012 4:00AM,May 05 2012 4:00AM,Apr 13 2012 10:00AM')
select * from @TableVar
;with cte
(
ID,CustomDates,strDt,pos
)
As
(
select ID,
CustomDates,
substring(
CustomDates,1,case when (charindex(',',CustomDates) -1) = -1 then Len(CustomDates) else charindex(',',CustomDates) -1 end
) strDt,charindex(',',CustomDates) as pos
from @TableVar
union all
select s.ID,
s.CustomDates,
substring(s.CustomDates,cte.pos+1,
case when (charindex(',',substring(s.CustomDates,cte.pos+1,len(s.CustomDates)))-1) = -1 then len(s.CustomDates) else charindex(',',substring(s.CustomDates,cte.pos+1,len(s.CustomDates)))-1 end)
,pos + case when charindex(',',substring(s.CustomDates,cte.pos+1,len(s.CustomDates))) = 0 then len(substring(s.CustomDates,cte.pos+1,len(s.CustomDates))) else charindex(',',substring(s.CustomDates,cte.pos+1,len(s.CustomDates))) end as pos
from @TableVar s inner join cte on cte.ID = s.ID
where cte.pos < len(s.CustomDates)
)
select strDt from cte OPTION (MAXRECURSION 32767);
---------------------------------------------------------------
Another Method:
Declare @TableVar Table
(
ID int,
Data varchar(max)
)
Insert into @TableVar values(1,'May 01 2012 4:00AM,May 05 2012 4:00AM,Apr 13 2012 10:00AM')
DECLARE @XML XML
SELECT @XML = '<Root><Child><Data>'+REPLACE(Data,',','</Data></Child><Child><Data>')+'</Data></Child></Root>' FROM @TableVar
SELECT CAST(DOC.COL.value('Data[1]','NVARCHAR(400)') AS DATE) AS Data
FROM @XML.nodes('/Root/Child') DOC(COL)
Labels:
T-Sql
Wednesday, April 11, 2012
Normal query instead of MERGE operation
/****** Object: StoredProcedure [dbo].[spBlockHr_SAVE] Script Date: 04/11/2012 15:32:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Author : Nikhildas
Date : July 01 2011 10:45AM
Purpose : To Save Block HR value.
Execute : Exec [spBlockHr_SAVE] <xml Value>,@P_RET_VAL OUTPUT
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Modified By On Remarks
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
<Next Entry>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~*/
ALTER PROCEDURE [dbo].[spBlockHr_SAVE]
(
@P_XML XML,
@P_RET_VAL INT OUTPUT
)
AS
SET NOCOUNT ON
DECLARE @DocHandle INT
DECLARE @BlockHr TABLE
(
ID int ,
SECTOR nvarchar(255) ,
Route nvarchar(255) ,
CODE nvarchar(255) ,
Month01 float ,
Month02 float ,
Month03 float ,
Month04 float ,
Month05 float ,
Month06 float ,
Month07 float ,
Month08 float ,
Month09 float ,
Month10 float ,
Month11 float ,
Month12 float ,
Total float
)
BEGIN TRY
BEGIN TRANSACTION
EXEC sp_xml_preparedocument @DocHandle OUTPUT,@P_XML
INSERT INTO @BlockHr
SELECT
ID,
SECTOR ,
Route,
CODE,
Month01 ,
Month02 ,
Month03 ,
Month04 ,
Month05 ,
Month06 ,
Month07 ,
Month08 ,
Month09 ,
Month10 ,
Month11 ,
Month12 ,
Total
FROM OPENXML(@DocHandle,'/Root',2)
WITH
(
ID INT,
SECTOR NVARCHAR(1000),
Route NVARCHAR(1000),
CODE nvarchar(255),
Month01 FLOAT(53),
Month02 FLOAT(53),
Month03 FLOAT(53),
Month04 FLOAT(53),
Month05 FLOAT(53),
Month06 FLOAT(53),
Month07 FLOAT(53),
Month08 FLOAT(53),
Month09 FLOAT(53),
Month10 FLOAT(53),
Month11 FLOAT(53),
Month12 FLOAT(53),
Total FLOAT(53)
)
EXEC sp_xml_removedocument @DocHandle
/* Commented on 11-Jul-2011 (For Avoid Merge statement)
MERGE BlockHr AS T
USING @BlockHr AS S
ON S.ID = T.ID
WHEN MATCHED
THEN
UPDATE
SET SECTOR = S.SECTOR,
Route = S.Route,
CODE = S.CODE,
Month01 = S.Month01,
Month02 = S.Month02,
Month03 = S.Month03,
Month04 = S.Month04,
Month05 = S.Month05,
Month06 = S.Month06,
Month07 = S.Month07,
Month08 = S.Month08,
Month09 = S.Month09,
Month10 = S.Month10,
Month11 = S.Month11,
Month12 = S.Month12,
Total = S.Total
WHEN NOT MATCHED BY TARGET
THEN
INSERT
(
SECTOR ,
Route,
CODE,
Month01 ,
Month02 ,
Month03 ,
Month04 ,
Month05 ,
Month06 ,
Month07 ,
Month08 ,
Month09 ,
Month10 ,
Month11 ,
Month12 ,
Total
)
VALUES
(
S.SECTOR ,
S.Route ,
S.CODE ,
S.Month01 ,
S.Month02 ,
S.Month03 ,
S.Month04 ,
S.Month05 ,
S.Month06 ,
S.Month07 ,
S.Month08 ,
S.Month09 ,
S.Month10 ,
S.Month11 ,
S.Month12 ,
S.Total
);
*/
--// Updation
UPDATE T
SET SECTOR = S.SECTOR,
Route = S.Route,
CODE = S.CODE,
Month01 = S.Month01,
Month02 = S.Month02,
Month03 = S.Month03,
Month04 = S.Month04,
Month05 = S.Month05,
Month06 = S.Month06,
Month07 = S.Month07,
Month08 = S.Month08,
Month09 = S.Month09,
Month10 = S.Month10,
Month11 = S.Month11,
Month12 = S.Month12,
Total = S.Total
FROM [BlockHr] T INNER JOIN @BlockHr S
ON S.ID = T.ID
--// Insertion
INSERT INTO [BlockHr]
SELECT
S.SECTOR ,
S.Route ,
S.CODE ,
S.Month01 ,
S.Month02 ,
S.Month03 ,
S.Month04 ,
S.Month05 ,
S.Month06 ,
S.Month07 ,
S.Month08 ,
S.Month09 ,
S.Month10 ,
S.Month11 ,
S.Month12 ,
S.Total ,
1
FROM [BlockHr] T RIGHT OUTER JOIN @BlockHr S
ON S.ID = T.ID
WHERE (T.ID IS NULL)
EXEC [SPAD_TRX_LOG_SAVE] 0,'USER-COST CENTER','','MODIFY',1,'',NULL
SET @P_RET_VAL = 1
COMMIT TRANSACTION
SET @P_RET_VAL = 1
END TRY
BEGIN CATCH
SET @P_RET_VAL = -1
ROLLBACK TRANSACTION
END CATCH
Labels:
T-Sql
Subscribe to:
Posts (Atom)