|
Have you got the result...
|
|
|
|
|
My bad,
The original code that I used as the query:
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.
|
|
|
|
|
hi to all
i lock a table in sql with belwo query
begin tran
select * from BML.LoanApplication with(TABLOCKX)
now i want to unlock this table but i cant
.
i can read from this table by nolock but i want to unlock this table
any help can be usefule
|
|
|
|
|
First of all, locking has little use when selecting. Second, the lock is released as soon as the statement completes.
What did you think it does?
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
|
|
|
|
|
ok but this is when that you use COMMIT end of my related code.
but i dont use COMMIT tran in my code.
and if use this code you can see that this table is lock:
select * from sys.dm_tran_locks inner join sys.objects on sys.dm_tran_locks.resource_associated_entity_id=sys.objects.[object_id]
|
|
|
|
|
mehdi.sabet wrote:
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 <a href="http://msdn.microsoft.com/en-us/library/ms188792.aspx">XACT ABORT</a>[<a href="http://msdn.microsoft.com/en-us/library/ms188792.aspx" target="_blank" title="New Window">^</a>] to ON. Also, don't lock an entire table, unless really, really required.
mehdi.sabet wrote: and if use this code you can see that this table is lock:
Code to close it on SO[^]
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
|
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
my code:
select top 19 * from(select ROW_NUMBER()over (order by ID ) as rowNumber,* from BML.LoanInstallment where total_amount like '%80%') tbl
|
|
|
|
|
mehdi.sabet wrote: 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).
..caused by the ROW_NUMBER function. Do you need it? Is it "just" a way of numbering the records? If yes, select into a temp-table and add the ROW_NUMBER there.
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
|
|
|
|
|
thankue for answer
i change my query to this:
create table #tblTemp(
ID int,
RowNumber int
)
insert into #tblTemp
select ID,ROW_NUMBER() over(order by ID) RowNum from BML.LoanInstallment
select top 19 * from(select payment_date,total_amount,BML.LoanInstallment.ID,RowNumber from BML.LoanInstallment
inner join
#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 ?
thanks
|
|
|
|
|
I think you should try it, and then thank Mycroft 
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
thank you
|
|
|
|
|
Additional to Eddies suggestion: Why is total amount stored as text or why are you doing a text operation on a numeric field '%80%' seems wrong!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
this where clause is an example my where claue is another clause that my field is not total amount for example for address or anything else
.
|
|
|
|
|
how to use corrupted sql server database into oracle.
|
|
|
|
|
I would call it a 'little vague' question.
To use a corrupted database, you should first try to fix it and then use it either in SQL or Oracle. AFAIK, there is no straight forward way to use a corrupted database in Oracle.
|
|
|
|
|
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.
|
|
|
|
|
thankue for your answer
but i dont thinks problem is in my database
my db is ok
|
|
|
|
|
Then what issue you are getting...
|
|
|
|
|
my table is also lock.
if use this query and you can see that table lock .
and
select * from sys.dm_tran_locks inner join sys.objects on sys.dm_tran_locks.resource_associated_entity_id=sys.objects.[object_id]
|
|
|
|
|
Hi,
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.
Hope it helped.
|
|
|
|
|
Maintain normalized database whereas you can avoid redundancy and if you are not maintaining more cities for one city then you can go for same table.
|
|
|
|
|
Based on what you say, it sounds like there is one price per city.
If so, I will differ a little from above answer.
I would have designed as:
Have a table called MstProducts: ProductID, ProductCode, ProductName, CityID, ...
Have a table called MstCities: CityID, City Name, Price
This covers 'each city has different price' and is also normalized.
|
|
|
|
|
Thank you guys.
I appreciated.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
