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.
I wanna ask if I can put just one table for both prices an cities.
In the beginning I created just table Product, but I have two different prices by cities(each city has a particular price).
After that I put two tables one for cities and one prices. but I think I don't need table cities, I'm asking if I can create one table prices with field city.
my data base NOW contains : Table Products: ProductID,ProductCode,ProductName,CityID,SubCategoryID... Table Cities: CityID,PriceID,City Name. Table Prices : PriceID,Price Table OrderDetails: OrderDetailsID, OrderID, FK ProductID, UnitPrice
Table order OrderID, CostomerID,...
The problem with prices : there are many prices for same product when they ordered (each city has a different price)
Well, there are multiple ways to solve a problem. It depends on whether you want to achieve speed or whether you want to have a normalized database. Here's what I would have done:
Have a table called Products: ProductID, ProductCode, ProductName,...
Have a table called Cities: CityID, City Name
Have a table called Prices: PriceID, Price
Have a table for mapping products with prices and cities, ProductPrice: ProductID, CityID, PriceID
Pro: This way your data remains in a normalized form which is considered to be a good design approach.
Con: You have to perform multiple joins while fetching data which will hamper performance a bit. But, I don't think you have millions of cities. So, I won't be bothered much with that.