outer join and group by clause

Nowadays don't get a lot of chance to be hands-on. The following did not fetch the outer join rows

select ordr, grp, count(ser_id) as [Sev4 Created]
from #myHPSDGrps left outer join dbo.ServiceCallView
on AssignedToWorkgroup = grp
where
[Open Date&Time] >= @begin and
[Open Date&Time] < @end and
Severity = 'Severity 4'
group by ordr, grp
order by ordr

but one below does, moved all the where clause to outer join condition

select ordr, grp, count(ser_id) as [Sev4 Created]
from #myHPSDGrps left outer join dbo.ServiceCallView
on AssignedToWorkgroup = grp and
[Open Date&Time] >= @begin and
[Open Date&Time] < @end and
Severity = 'Severity 4'
group by ordr, grp
order by ordr

Didn't have much time to analyze :0

int division in SQLServer T-SQL

Today, I'm trying to use T-SQL to do a calculation something like this

Declare @a intDeclare @b int
select @a=1, @b=2
select (@a/@b)*100

No the result was not 50, it was 0.Thats 'coz SQL Server performs an implicit data-type conversion and converts the resulting value of 1/2 as integer. Fix is to cast the operands to decimal

select ( cast(@a as decimal)/ cast(@b as decimal) )*100

OpenXML and index scan issue

Faced a peculiar issue with OpenXML, when joining with a table with an OpenXML data, it seems the index being hit is erratic.

CREATE TABLE OpenXMLTest3
(
Col1 INT NOT NULL primary key,
Col2 CHAR(1) NOT NULL,
Col3 CHAR(10)
)
GO

CREATE INDEX IDX_OpenXMLTest3 ON OpenXMLTest3
( Col2, Col3 ) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

Execution plan for the following code gives a index scan on IDX_OpenXMLTest3, but if we load a lot of data into this table, and get the execution plan, it shows a clustered index scan on primary key index. ???? Yes on the "primary key index" ????

DECLARE @hDoc INT
EXEC sp_xml_preparedocument @hDocOUTPUT, '?'

SELECT * FROM OpenXMLTest3 A,
OPENXML (@hDoc, 'ROOT/OpenXMLTest3',1)
WITH (COL2 CHAR(1), COL3 CHAR(10)) B
WHERE A.COL2 = B.COL2 AND
A.COL3 = B.COL3

When we dump the xml data into a table variable and join with that like one below, it was found to give an index seek on IDX_OpenXMLTest3. That is what we used to fix this isse as the table we are joining is a very big invoice table and OpenXML failed hands down.

DECLARE @Temp table (
Col2 CHAR(1) NOT NULL,
Col3 CHAR(10)
)

SELECT * FROM OpenXMLTest3 A, @Temp B
WHERE A.COL2 = B.COL2 AND
A.COL3 = B.COL3

OpenXML limitation - row based operation

I needed to insert a bulk of data into a table and designed to use OpenXML and there is a biz reqt to compute (Max+1) for the vdrNum column.

CREATE TABLE OpenXMLTest2
(
vdrNum INT NOT NULL,
vdrType CHAR(1) NOT NULL,
vdrName CHAR(10)
)
GO
ALTER TABLE OpenXMLTest2
ADD CONSTRAINT PK_OpenXMLTest2 PRIMARY KEY CLUSTERED
(
vdrNum,
vdrType
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
DECLARE @hDoc INT

EXEC sp_xml_preparedocument @hDoc OUTPUT,
'<ROOT><OpenXMLTest2 vdrName="Vend1" /><OpenXMLTest2 vdrName="Vend2" /></ROOT>'

INSERT INTO OpenXMLTest2 (vdrNum,vdrType,vdrName)
SELECT
(SELECT ISNULL(MAX(vdrNum),0)+1 FROM OpenXMLTest2 WHERE vdrType = 'x' )
,'x',vdrName
FROM OPENXML (&hDoc, 'ROOT/OpenXMLTest2',1) WITH (vdrName CHAR(10))

Looks good, but later found that this actually gives a primary key violation as vdrNum computed is always the same intial value and hence OpenXML doesn't work like what i expected. Workaround for the above code is


DECLARE @hDoc INT, @Val int

SELECT @Val = ISNULL(MAX(vdrNum),0)+1
FROM OpenXMLTest2 WHERE vdrType = 'x'

EXEC sp_xml_preparedocument @hDoc OUTPUT,
'<ROOT><OpenXMLTest2 vdrName="Vend1" vdrNum="1" /><OpenXMLTest2 vdrName="Vend2" vdrNum="2" /></ROOT>'

INSERT INTO OpenXMLTest2 (vdrNum,vdrType,vdrName)
SELECT vdrNum+1,'x',vdrName
FROM OPENXML (&hDoc, 'ROOT/OpenXMLTest2',1)
WITH (vdrName CHAR(10), vdrNum INT)

This reminds me an intresting problem, one of my coworker had with OpenXML, he had to delete some rows whose primary key columns values are available. Tricky part was, that table had a forignkey constraint that refered to itself (kind of hierarchical organization structure). Though he had the XML created in the correct order so that it doesn't make any foriegn key violation while deleting a row, it always throwed an foriegn key violation error. Later it was found that OpenXML doesn't actually didn't delete the rows as in the order in the XML and most probably spawned into multiple threads internal to SQLServer and hence tried to delete a parent row while its child row is still not deleted. Later when XML was dumped into a temp table and looped that to to delete in correct order as expected.

OpenXML - attribute centric XML

Surprsingly a sample for this one was hard to find in internet, i googled for a long time to catch a working code. The column i joined was a char and has spaces in before and i needed to keep the spaces for joining to that column, normal element centric approach will trim the spaces while preparing the document to table structure.

CREATE TABLE OpenXMLTest
(
VDR_NUM CHAR(9) PRIMARY KEY,
VDR_NAME CHAR(100)
)
GO


INSERT INTO OpenXMLTest VALUES(' 1','Contoso Inc.')
GO

CREATE PROC TestOpenXML
@in_TXML text
as
DECLARE @hDoc INT
EXEC sp_xml_preparedocument @hDoc OUTPUT, @in_TXML
SELECT A.VDR_NUM, A.VDR_NAME
FROM OpenXMLTest A,
OPENXML (@hDoc, 'ROOT/OpenXMLTest',2)
WITH(VDR_NUM CHAR (9)) OutXML
WHERE A.VDR_NUM = OutXML.VDR_NUM

//cs code

System.Text.StringBuilder sb = new System.Text.StringBuilder(1000);
XmlTextWriter xtWriter=new XmlTextWriter(new System.IO.StringWriter(sb));
xtWriter.WriteStartElement("ROOT");
xtWriter.WriteStartElement("OpenXMLTest");
xtWriter.WriteStartElement("VDR_NUM");
xtWriter.WriteCData(" 1");
xtWriter.WriteEndElement(); //for VDR_NUM
xtWriter.WriteEndElement(); //for OpenXMLTest
xtWriter.WriteEndElement(); //for ROOT

// mundane ADO.net code goes here, use sb.ToString() for Xml string