between

Hi, can I use between for nvarchar field? can some one explain more detail
please...
fd1 has: 1000, 1000a, 1000b, a1000, b1000, 2000, 2000a, 2000b, b2000.
select * from tb1 where fd1 between '1000' and 2000'Yes, you can use BETWEEN for nvarchar comparisons. "WHERE a BETWEEN x
and y" is exactly the same as saying "WHERE a >= x AND a <= y", so for
an nvarchar column that simply does an alphanumeric comparison based on
the collation the column ("a" in this example) uses.
Your example query should return
1000
1000a
1000b
2000
because those values all satisfy "fd1 >= '1000' and fd1 <= '2000'" using
an alphanumeric comparison.
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
js wrote:

>Hi, can I use between for nvarchar field? can some one explain more detail
>please...
>fd1 has: 1000, 1000a, 1000b, a1000, b1000, 2000, 2000a, 2000b, b2000.
>select * from tb1 where fd1 between '1000' and 2000'
>
>|||Thanks Mike...

better sql join

A bit out of topic but here i can find the right person who explain me...

I have 3 tables: User(ID,Name),Customer(ID,Name),Order(CustomerID,UserID,Qty)

I want to know the difference (i think in performance because are both right) between:

SELECT U.Name, C.Name

FROM User AS U, Customer AS C, Order as O

WHERE C.ID=O.CustomerID AND U.ID=O.UserID

AND U.ID=5

AND C.ID=7

SELECT U.Name, C.Name

FROM User AS U, Customer AS C, Order as O

WHERE C.ID=O.CustomerID AND U.ID=O.UserID

AND O.UserID=5

AND O.CustomerID=7

Thx

Hi Manuel

As far as I can see you second query has mutually exclusive criteria

AND O.ID=5

AND O.ID=7

Did you mean AND ( O.ID=5 OR O.ID=7) ?

And as an additional suggestion, the quesry would be easier to read if it was written with use of INNER JOINs instead of conneting tables on thhe "WHERE" clause. Its behaviour is more consistent.

NB.

|||

ok for innerjoin..

I did an error:

I mean O.CustomerID =5

O.UserID =7

|||Manuel, in the case of:
select * from A, B where A.col1 = B.col1
if A is smaller and has a good index, I think SQL Server will chose A as the inner table. Because of this we should put our restrictive where clause on B, like this:
select * from A, B where A.col1 = B.col1 and B.col2 = 5

That will have a better performance than putting the restrictive clause on A like this:
select * from A, B where A.col1 = B.col1 and A.col2 = 5

As a general rule, I normally put the restrictive where clause on the foreign key (FK)table, not on the primary key (PK) table. This is because I think SQL Server will choose the PK table as the inner table. Somebody from Microsoft SQL Server development team would be able to explain more about SQL Server's nested loop join plan strategy.

To understand what is inner table and outer table, I would recommend you to read page 90 of Peter Gulutzan's book, SQL Performance Tuning. Basically, the table size affect number of page read from disk. If the table is small, there is more chance to put all the pages in the cache.

Vincent|||

Thx for reply.

Expect Microsoft? Bah!

And if i'm not using SQLServer?

|||? Hi Manuel, Did you try both? Have you checked the execution plans to see if they are any different? I just created a small test (with a similar pattern, but on only two tables) and in my case both execution plans where exactly the same. If you observed different execution plan, I'd like to see some SQL to reproduce it here! -- Hugo Kornelis, SQL Server MVP <manuel0081@.discussions.microsoft.com> schreef in bericht news:5278193f-98a0-47a3-8978-416cf71318d5_WBRev3_@.discussions..microsoft.com...This post has been edited either by the author or a moderator in the Microsoft Forums: http://forums.microsoft.com A bit out of topic but here i can find the right person who explain me... I have 3 tables: User(ID,Name),Customer(ID,Name),Order(CustomerID,UserID,Qty) I want to know the difference (i think in performance because are both right) between: SELECT U.Name, C.Name FROM User AS U, Customer AS C, Order as O WHERE C.ID=O.CustomerID AND U.ID=O.UserID AND U.ID=5 AND C.ID=7 SELECT U.Name, C.Name FROM User AS U, Customer AS C, Order as O WHERE C.ID=O.CustomerID AND U.ID=O.UserID AND O.UserID=5 AND O.CustomerID=7 Thx

best way of making changes to a production database

HI all,
We have three verions of our database, one that is in continual development,
do'nt as why, it would take me too long to explain, but they are valid.
ANyway, we have a developer version, which we develop on test changes, crash
etc etc.
Then we have a test version where people can test the new changes to the
application and database,
Then we have the live version where the live data is, obviously.
What is the best practice to move changes from one database to another
wthout affecting or deleting the current data.
The easiest way would be to script the database, drop it then run the
script, not good because it looses all data. This is ok for the test
database, as we don't care what we loose there, but can not do that for the
live data.
Thanks
RobertThe way we do it, and I would've thought this is standard practice, is
to script the changes to the database, including any structural
changes, stored procedures, triggers etc, and apply the script to each
database.
I don't see how you could script the entire database and be expected to
run it against a database and retain data.
Kris|||We use SQL Compare from Red-Gate Software to generate change scripts.
However, I must say that my last major roll-out didn't go as planned
because I didn't review the script it generated as carefully as I
should. On the whole, though, it's a great tool for making production
changes.
Stu|||If you're willing to put the procedures in place, then in my opinion
the safest way to ensure that things getting properly transferred is to
only make changes to your databases through SQL scripts (don't add
columns etc through EM). Then save every script you run, then to roll
out to the next database, just re-run all the scripts. However this is
just my opinion. It does mean that you'd need to run every script, even
the ones that went wrong, just to make sure you achieved the same end
result.
Will|||Start with identical Test and Production schema. Promote changes from
Development to Test using only scripts (preferably source-controlled).
Before you go live, apply the scripts to a copy of Production to make sure
that all goes as expected.
Hope this helps.
Dan Guzman
SQL Server MVP
"Robert Bravery" <me@.u.com> wrote in message
news:ugAZAtqaGHA.1352@.TK2MSFTNGP05.phx.gbl...
> HI all,
> We have three verions of our database, one that is in continual
> development,
> do'nt as why, it would take me too long to explain, but they are valid.
> ANyway, we have a developer version, which we develop on test changes,
> crash
> etc etc.
> Then we have a test version where people can test the new changes to the
> application and database,
> Then we have the live version where the live data is, obviously.
> What is the best practice to move changes from one database to another
> wthout affecting or deleting the current data.
> The easiest way would be to script the database, drop it then run the
> script, not good because it looses all data. This is ok for the test
> database, as we don't care what we loose there, but can not do that for
> the
> live data.
> Thanks
> Robert
>