I have a SQL Server project that I'm currently adding code to using SSMSE2k8 and beside the fact that this is all TSQL, there's this creep that's occuring in the facility. Namely the .xml. Now, here's the more technical side of TSQL as related to XML, using a FOR XML PATH juncture. I can do a basic SELECT query and get output of xml (pseudo output really because ... this is the creep; in ssmse that's as far as development WENT) but I'm constrained by all these substitutions in he editor:
Could have knocked me over with a feather ... the reason that my editor (was) going south was because of the type I had used to reassign parsed lines of singleline .xml while striking it up as data in a table!.
[nvarchar](MAX) throws (bad use of an alliteration) the phantom formatter into overdrive, while [xml] is silently overlooked. Thus arrows are preserved.
SELECT [xmliform] AS'data()'FROM [database].[xml].[tblXMLAsTypeNvarchar] FOR XML PATH('')
So, as you can see, an editor will try to format anything from a table where the type is [nvarchar] using it's xml parser when I specify "FOR XML PATH" regardless of how bad I "want" it as xml. And that is with substituted control characters.
ok but this is when that you use COMMIT end of my related code.
but i dont use COMMIT tran in my code.
First, it's a lousy example; there's no way that the server can "guess" whether it should be rolled back or comitted automatically. Second, there is nothing to commit or rollback, since a SELECT statement doesn't change the data. Don't use a transaction when selecting. Also, set XACT ABORT[^] to ON. Also, don't lock an entire table, unless really, really required.
and if use this code you can see that this table is lock:
hi to all
im face with low performance by below query in sql server 2008 any one can help me.
my query is goon work when top has larg number but when top has less row (for example top 19) this query has low performance for table loaninstallment with amount of records(10000000 R).
any one can help me
thanks for any help
selecttop19 * from(select ROW_NUMBER()over (orderby ID ) as rowNumber,* from BML.LoanInstallment where total_amount like'%80%') tbl
select ID,ROW_NUMBER() over(orderby ID) RowNum from BML.LoanInstallment
selecttop19 * from(select payment_date,total_amount,BML.LoanInstallment.ID,RowNumber from BML.LoanInstallment
#tblTemp on #tblTemp.ID = BML.LoanInstallment.ID
where total_amount like'%80%') tbl
this run completed a large of second
i think the reason of this is my where clause that i use like in this clause and if i remove it my query is ok and response in reasonable time.
what you thinks ?
Find the problem for corrupted database and make it clean then convert the DB to another DB.If you are changing the corrupted database then there is an possibilities to affect in new database also.So clear and Shift.