Saturday, August 24, 2013

[T-SQL] xml casting issue

[T-SQL] xml casting issue

xml casting issue

Posted: 23 Aug 2013 05:01 PM PDT

Hi All ,why below syntax is giving me error ..; with xmlnamespaces ('http://my/schemas/20120701' as cmp)select cast('<cmp:O b="16" />' as xml)and below is working fine..; with xmlnamespaces ('http://my/schemas/20120701' as cmp)select cast('<O b="16" />' as xml)Thanks,Saurabh

Techniques for improving stored procedure performance

Posted: 23 Aug 2013 02:24 AM PDT

Hi, SQL land . . .I realize that this is a general and vague question; for this, I apologize in advance. I'm looking to educate myself, and would appreciate any advice, tips, tricks, and helpful links.Here's my scenario: I have SQL code that I'm writing (that is getting increasingly complex).When I run it in SSMS as a standalone query, it flies. I get my results in just a few seconds.However, when I put the exact same query into a stored procedure (no changes to the code AT ALL, other than putting it into a stored proc and testing it in another query window), it slows to a crawl -- 30+ seconds.I've already specified the WITH RECOMPILE option. Is there anything else I should look into?

CTE and Group By

Posted: 23 Aug 2013 01:28 AM PDT

I have a table where 'X' = Delivered and '' is not Delivered. I am trying to group the deliveries by PO Item, so that if all rows have been Delivered then we get an X.. otherwise ''.So a SQL would return.. for the data below4900239800, 0000, 4900239825, 0010, XSo the table.. stripped down.. would look like this:/****** Object: Table [dbo].[PO_Delivery_Hist] Script Date: 08/23/2013 10:16:20 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ONGOCREATE TABLE [dbo].[PO_Delivery_Hist]( [Purchasing_Doc_Num] [varchar](50) NOT NULL, [Purchasing_Req_Item_Num] [varchar](5) NOT NULL, [Sequential_Num] [tinyint] NOT NULL, [Delivery_Ind] [char](1) NULL, CONSTRAINT [PK_PO_Delivery_Hist] PRIMARY KEY CLUSTERED ( [Purchasing_Doc_Num] ASC, [Purchasing_Req_Item_Num] ASC, [Sequential_Num] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]GOSET ANSI_PADDING OFFGOData:insert into PO_Delivery_Histvalues ('4900239825', '0010', '1', 'X')insert into PO_Delivery_Histvalues ('4900239825', '0010', '2', 'X')insert into PO_Delivery_Histvalues ('4900239825', '0010', '3', 'X')insert into PO_Delivery_Histvalues ('4900239800', '0000', '1', 'X')insert into PO_Delivery_Histvalues ('4900239800', '0000', '2', '')insert into PO_Delivery_Histvalues ('4900239800', '0000', '3', 'X')
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog