further informations

Just for the record:
- the statements are viewed exactly before execution with an unicode aware
application and they contain the right unicodes.
- the db columns supposed to store the unicodes are defined as nvarchar
- the db viewer (SQL Query Analizer) is also unicode aware. It shows right
unicodes for values inserted with "preparedStatements" but ''?...' for
those inserted with plain statements.
?Hint?
In statements we don't use the N prefix for the unicode values. F.ex.:
we do:
UPDATE Table1 SET Col1 = 'New Text' WHERE id = 1
not:
UPDATE Table1 SET Col1 = N'New Text' WHERE id = 1
Is there a way to eliminate the necessity of using N by means of DB/SQL
Server settings?
Many thanks in advance.I think the '? show up because you insert non-unicode strings, and
there's a mismatch between the code pages of the process that changes the
data and the process that reads the data.
The ideal way to fix this would be to stop generating UPDATE statements, if
you can't change your code like that then you need to insert the values as
Unicode.
As a side note, you'll want to pay attention to injection attacks.
In the statement below:
UPDATE Table1 SET Col1 = N'New Text' WHERE id = 1
If the value of 'Nex Text' comes from the user then you need to escape it
with QUOTENAME
UPDATE Table1 SET Col1 = QUOTENAME(N'New Text','''') WHERE id = 1
Otherwise it can be injected with >>>>New Text' do_a_bad_thing_here --<<<<
UPDATE Table1 SET Col1 = N'New Text' do_a_bad_thing_here -- WHERE id = 1
Ciprian Gerea
SDE, SqlServer
This posting is provided "AS IS" with no warranties, and confers no rights.
"Cristian Senchiu" <Cristian Senchiu@.discussions.microsoft.com> wrote in
message news:88F7BF72-A378-4A4D-9D42-E2F9F98C697A@.microsoft.com...
> Just for the record:
> - the statements are viewed exactly before execution with an unicode aware
> application and they contain the right unicodes.
> - the db columns supposed to store the unicodes are defined as nvarchar
> - the db viewer (SQL Query Analizer) is also unicode aware. It shows right
> unicodes for values inserted with "preparedStatements" but ''?...' for
> those inserted with plain statements.
> ?Hint?
> In statements we don't use the N prefix for the unicode values. F.ex.:
> we do:
> UPDATE Table1 SET Col1 = 'New Text' WHERE id = 1
> not:
> UPDATE Table1 SET Col1 = N'New Text' WHERE id = 1
> Is there a way to eliminate the necessity of using N by means of DB/SQL
> Server settings?
> Many thanks in advance.

Functions and Execution Plan

The cost of query with usage of functions is as same as that of without
functions

In the below code, the query cost of insert is 0.02% and two select
statements costs same 0.04%

Declare @.t table(mydate datetime)
Declare @.i int
set @.i=1
while @.i<=5000
Begin
insert into @.t values(getdate())
set @.i=@.i+1EndSelect mydate from @.t
Select convert(varchar,mydate,112) from @.t

But I thought usage of convert function will take more query cost
What do you think of this?

MadhivananAdding a CONVERT() to the output is very little extra work, but I
suspect that you're referring to the fact that using a function on a
column in the WHERE clause can prevent MSSQL from using an index. That
can have a significant impact on the query plan, eg:

create table dbo.m (mydate datetime primary key)

Declare @.i int
set @.i=1
while @.i<=5000
Begin
insert into m values(getdate() + @.i)
set @.i=@.i+1
End

-- now run these two queries in the same batch

select *
from m
where mydate between '20100815' and '20100917'

select *
from m
where convert(char(8), mydate, 112) between '20100815' and '20100916'

On my test server, the first query takes 11% of the batch, the second
is 89% - although they are functionally equivalent, the first one can
do a seek in the clustered index, but the second must scan it. That's
not to say that functions in the SELECT will never affect the query
plan or cost, but when reviewing code it's probably more important to
look at the WHERE clause first.

Simon|||Thanks Simon

So only in Where condition it affects the performance and not in select
isnt it?

Madhivanan|||I'm sure that functions in the SELECT clause can affect the query cost
- nested string functions, nested CASE expressions, a scalar UDF which
looks up other tables etc. And all other things being equal, "SELECT
col1" will be more efficient than "SELECT somefunc(col1)", simply
because MSSQL has less work to do.

But the difference may be extremely small (as in your example), and I
guess that in most cases, any really big differences in performance
would come from functions in the WHERE clause, not the SELECT clause.
Of course there are many other reasons why a query might run slowly -
missing indexes, out-of-date statistics and so on - which have nothing
to do with functions at all, so if you have a performance problem with
a specific query, then it's best to start by looking at the query plan
before you think about how to re-write the code.

Simon|||Madhivanan (madhivanan2001@.gmail.com) writes:
> So only in Where condition it affects the performance and not in select
> isnt it?

What matters is that if you put an indexed column into an expression,
the index can no longer be used for searches.

If you have

where mydate between '20100815' and '20100917'

and there is an index on mydate, SQL Server can use that index to
find the matching rows. But if you say:

convert(char(8), mydate, 112) between '20100815' and '20100916'

that index can no longer be used, because that index holds datetime
values, and this is a string expression.

Note that if mydate is not indexed, the only cost for the function
call is the function call itself. In this case, it's not more expensive
that having it in the SELECT list. (Except that if it's in a WHERE
clause, it may be applied to more values.)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Well
Thanks for the suggesstions

Madhivanan

function naming and execution

Hi,

I am converting a database from Oracle to SQL-Server 2005 and in that context I needed to implement a function in the database. My database default uses the dbo schema and the function is there too. I define a function called "Translate" and wanted to use it like this :

SELECT
translate(Phonenumber, '0123456789 ()+-', '0123456789') as Phonenumber
FROM
Customer

but what pussled my was that in order to make this work I had to prefix the translate method with "dbo." like this :

SELECT

dbo.translate(Phonenumber, '0123456789 ()+-', '0123456789') as Phonenumber

FROM

Customer

as all "objects" I'm using resides in the dbo schema - why must I prefiks only the function and not the table?Yes, functions will defintely need the owner / schema prefix.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||Why not use SQL Server Migration Assistant (SSMA) for Oracle ?|||Howcome tables and views does not? - this seems inconsistent to me when they are all in the same schema...

Function execution

Hello,
I am trying to optimize a function, and need some
information on functions, can anyone help me out.
1. Is function a compiled object of SQL.
2. If yes how do we recompile it.
Thanks,
ManojManoj,
Are these user defined functions? Or using functions like MAX, MIN, etc.
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.

Function execution

Hello,
I am trying to optimize a function, and need some
information on functions, can anyone help me out.
1. Is function a compiled object of SQL.
2. If yes how do we recompile it.
Thanks,
Manoj
Manoj,
Are these user defined functions? Or using functions like MAX, MIN, etc.
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.

Fully qualified object names help reuse of execution plans.

Fully qualified object names help reuse of execution plans.
Is this true? Discuss.
Also, what constitutes fully qualified?
Is it
server.database.owner.object
or can you get away with
database.owner.object
and still resuse your execution plans?
ThanksYou should read this KB. Even though it is for 2005 most of the same
principles still apply.
http://www.microsoft.com/technet/pr...005/recomp.mspx
As for your question of what does fully mean well that depends. Mostly it
means you should always specify the object owner along with the object.
dbo.yoursp or dbo.yourtable etc.
If you are accessing an object from within the same db then just specify the
owner and the object. Do not specify the database as it is no necessary and
actually invokes a few more lines of code than necessary. If you need to
specify an object in another db on the same server than you must specify
that as well.
OtherDB.dbo.Object
Andrew J. Kelly SQL MVP
"Damien" <Damien@.discussions.microsoft.com> wrote in message
news:2BBC6EA6-2E85-4643-8E07-16E031D8E8A9@.microsoft.com...
> Fully qualified object names help reuse of execution plans.
> Is this true? Discuss.
> Also, what constitutes fully qualified?
> Is it
> server.database.owner.object
> or can you get away with
> database.owner.object
> and still resuse your execution plans?
> Thanks
>|||Hi
http://msdn.microsoft.com/library/d...br />
4azp.asp
If you want more information, get yourself "Inside SQL Server 2000" by Kalen
Delaney.
owner.object is good enough for re-use.
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Damien" <Damien@.discussions.microsoft.com> wrote in message
news:2BBC6EA6-2E85-4643-8E07-16E031D8E8A9@.microsoft.com...
> Fully qualified object names help reuse of execution plans.
> Is this true? Discuss.
> Also, what constitutes fully qualified?
> Is it
> server.database..object
> or can you get away with
> database.owner.object
> and still resuse your execution plans?
> Thanks
>

Fulltext Query Cost

Hi,
I have a query which have fulltext search. The query cost of this query is
around 3-4 only (Show in Execution Plan). I am using ASP calling VBCOM adodb
way to execute this query. Why I always hit timeout for the first search
(using Machine A). There is no timeout after that. Looks like there is some
caching there but bear in mind, I am not searching the same characters after
the first search. Now Machine A don't hit any timeout and then I try Machine
B to search and Machine B hit timeout for the first time now. Any idea?
Thanks,
Kenny
Its hard to say without looking at your query - could you post it here?
I suspect the timeout's are probably caused due to the fact that the catalog
must be read off disk and then for subsequent queries whether they are
different or not, the catalog pages are cached.
You might want to do run a job which warms up the catalogs before searches
hit them.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Kenny" <keejh@.hotmail.com> wrote in message
news:%23Q$ojtU7GHA.1256@.TK2MSFTNGP04.phx.gbl...
> Hi,
> I have a query which have fulltext search. The query cost of this query is
> around 3-4 only (Show in Execution Plan). I am using ASP calling VBCOM
> adodb way to execute this query. Why I always hit timeout for the first
> search (using Machine A). There is no timeout after that. Looks like there
> is some caching there but bear in mind, I am not searching the same
> characters after the first search. Now Machine A don't hit any timeout and
> then I try Machine B to search and Machine B hit timeout for the first
> time now. Any idea?
> Thanks,
> Kenny
>
|||Thank you ... it looks working fine after I rebuild catalog.
Kenny
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%23%23w02RV7GHA.3604@.TK2MSFTNGP02.phx.gbl...
> Its hard to say without looking at your query - could you post it here?
> I suspect the timeout's are probably caused due to the fact that the
> catalog must be read off disk and then for subsequent queries whether they
> are different or not, the catalog pages are cached.
> You might want to do run a job which warms up the catalogs before searches
> hit them.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Kenny" <keejh@.hotmail.com> wrote in message
> news:%23Q$ojtU7GHA.1256@.TK2MSFTNGP04.phx.gbl...
>