Does SQL Server move records between partitions when updating the partition key column?

If I run an UPDATE query on a table which is partitioned by the column I am updating - will the records be moved to another partition?

ie. I have a table where Historical bit column marks whether a particular record should go to Partition1 (=0) or Partition2 (=1). Now, I update a record in that table and change the Historical column value from 0 to 1. What happens with that record?

It will move to the other partition.

Thanks,|||Thank you. That's the kind of behavior I was looking for.

Does SQL Server creates some temporary table during query execution?

Hi,
I want to know when does SQL Server use temporary tables for query
processing?
Does it use for all queries or for some complex queries? Or doesn't use at
all?
Thanks
PushkarYes, it does. If you show the query plan, you'll see icons such as Table
Spool/Eager Spool. That implies that a temporary table is being created by
the optimizer behind the scenes.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Pushkar" <pushkartiwari@.gmail.com> wrote in message
news:ekuGHMiNGHA.2320@.TK2MSFTNGP11.phx.gbl...
Hi,
I want to know when does SQL Server use temporary tables for query
processing?
Does it use for all queries or for some complex queries? Or doesn't use at
all?
Thanks
Pushkar|||Pushkar
I think SQL Server decides internally to perform some operations in tempdb
database. For sure I know that if your query has ORDER BY ,GROUP BY clauses
and it has to operate in large amount of data , so SQL Server will create a
work tables to perfom that.
I'd suggest to visit at Aaron's web site to get more explanation
www.aspfaq.com
"Pushkar" <pushkartiwari@.gmail.com> wrote in message
news:ekuGHMiNGHA.2320@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I want to know when does SQL Server use temporary tables for query
> processing?
> Does it use for all queries or for some complex queries? Or doesn't use at
> all?
> Thanks
> Pushkar
>|||Thanks !!!
Pushkar
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:urnhSSiNGHA.2916@.tk2msftngp13.phx.gbl...
> Pushkar
> I think SQL Server decides internally to perform some operations in
> tempdb database. For sure I know that if your query has ORDER BY ,GROUP BY
> clauses and it has to operate in large amount of data , so SQL Server will
> create a work tables to perfom that.
> I'd suggest to visit at Aaron's web site to get more explanation
> www.aspfaq.com
>
>
>
> "Pushkar" <pushkartiwari@.gmail.com> wrote in message
> news:ekuGHMiNGHA.2320@.TK2MSFTNGP11.phx.gbl...
>

Does SQL 2005 take XML query time into acount?

if there's an [untyped] XField.Query() in a select, does SQL 2005 do
anything (internally) like an automatic sub-select if there's standard
fields to compare, saving the XML analysis for later, (to avoid uneccessary
calculations)?Hello Jerry,
XML queries do cache their query plans, of course, like any other T-SQL quer
y.
However, unindexed XML has to reconstruct the node table each time. That's
an expensive unit of work (usually), so the best thing you can (usually)
do is create an primary XML index over those instances. That "kind of" does
that you're looking for here.
Cheers,
Kent|||> XML queries do cache their query plans, of course, like any other T-SQL
> query. However, unindexed XML has to reconstruct the node table each time.
> That's an expensive unit of work (usually), so the best thing you can
> (usually) do is create an primary XML index over those instances. That
> "kind of" does that you're looking for here.
Thanks for all, Kent ;)

Does SQL 2005 take XML query time into acount?

if there's an [untyped] XField.Query() in a select, does SQL 2005 do
anything (internally) like an automatic sub-select if there's standard
fields to compare, saving the XML analysis for later, (to avoid uneccessary
calculations)?
Hello Jerry,
XML queries do cache their query plans, of course, like any other T-SQL query.
However, unindexed XML has to reconstruct the node table each time. That's
an expensive unit of work (usually), so the best thing you can (usually)
do is create an primary XML index over those instances. That "kind of" does
that you're looking for here.
Cheers,
Kent
|||> XML queries do cache their query plans, of course, like any other T-SQL
> query. However, unindexed XML has to reconstruct the node table each time.
> That's an expensive unit of work (usually), so the best thing you can
> (usually) do is create an primary XML index over those instances. That
> "kind of" does that you're looking for here.
Thanks for all, Kent ;)
sql

Does Sql 2005 allow ORDER BY @Variable ?

I am writing a stored proc and do not want to use dynamic sql. Does Sql
2005 allow ORDER BY @.Variable? The query compiler accepts it, but it
doesn't work. My @.Variable is a VARCHAR(50).
Thanks!This is a multi-part message in MIME format.
--=_NextPart_000_0924_01C71867.3AEC95F0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Not directly. You could use a CASE structure to allow alternative =orderings. Here is one idea:
USE Northwind
GO
DECLARE @.OrderVar varchar(20)
SET @.OrderVar =3D 'LastName'
SELECT LastName,
FirstName
FROM Employees
ORDER BY CASE @.OrderVar
WHEN 'LastName' THEN LastName
WHEN 'FirstName' THEN FirstName
END ASC,
CASE @.OrderVar
WHEN 'LastName' THEN FirstName
WHEN 'FirstName' THEN LastName
END ASC
-- Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience. Most experience comes from bad judgment. - Anonymous
You can't help someone get up a hill without getting a little closer to =the top yourself.
- H. Norman Schwarzkopf
"Dan E" <dan_english2@.cox.net> wrote in message =news:u1AfegKGHHA.1280@.TK2MSFTNGP04.phx.gbl...
>I am writing a stored proc and do not want to use dynamic sql. Does =Sql > 2005 allow ORDER BY @.Variable? The query compiler accepts it, but it > doesn't work. My @.Variable is a VARCHAR(50).
> > Thanks!
> >
--=_NextPart_000_0924_01C71867.3AEC95F0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Not directly. You could use a CASE =structure to allow alternative orderings. Here is one idea:
USE =NorthwindGO
DECLARE @.OrderVar =varchar(20)SET @.OrderVar =3D 'LastName'
SELECT LastName, FirstNameFROM EmployeesORDER BY CASE @.OrderVar = WHEN 'LastName' THEN LastName &=nbsp; WHEN 'FirstName' THEN FirstName END ASC, CASE @.OrderVar = WHEN 'LastName' THEN FirstName = WHEN 'FirstName' THEN LastName END ASC
-- Arnie Rowland, =Ph.D.Westwood Consulting, Inc
Most good judgment comes from =experience. Most experience comes from bad judgment. - Anonymous
You can't help someone get up a hill =without getting a little closer to the top yourself.- H. Norman Schwarzkopf
"Dan E" =wrote in message news:u1AfegKGHHA.1280@.TK2MSFTNGP04.phx.gbl...>I =am writing a stored proc and do not want to use dynamic sql. Does Sql > =2005 allow ORDER BY @.Variable? The query compiler accepts it, but it => doesn't work. My @.Variable is a VARCHAR(50).> > Thanks!> >

---

--=_NextPart_000_0924_01C71867.3AEC95F0--|||http://databases.aspfaq.com/database/how-do-i-use-a-variable-in-an-order-by-clause.html
"Dan E" <dan_english2@.cox.net> wrote in message
news:u1AfegKGHHA.1280@.TK2MSFTNGP04.phx.gbl...
>I am writing a stored proc and do not want to use dynamic sql. Does Sql
>2005 allow ORDER BY @.Variable? The query compiler accepts it, but it
>doesn't work. My @.Variable is a VARCHAR(50).
> Thanks!
>

Does Sql 2005 allow ORDER BY @Variable ?

I am writing a stored proc and do not want to use dynamic sql. Does Sql
2005 allow ORDER BY @.Variable? The query compiler accepts it, but it
doesn't work. My @.Variable is a VARCHAR(50).
Thanks!
Not directly. You could use a CASE structure to allow alternative orderings. Here is one idea:
USE Northwind
GO
DECLARE @.OrderVar varchar(20)
SET @.OrderVar = 'LastName'
SELECT
LastName,
FirstName
FROM Employees
ORDER BY CASE @.OrderVar
WHEN 'LastName' THEN LastName
WHEN 'FirstName' THEN FirstName
END ASC,
CASE @.OrderVar
WHEN 'LastName' THEN FirstName
WHEN 'FirstName' THEN LastName
END ASC
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the top yourself.
- H. Norman Schwarzkopf
"Dan E" <dan_english2@.cox.net> wrote in message news:u1AfegKGHHA.1280@.TK2MSFTNGP04.phx.gbl...
>I am writing a stored proc and do not want to use dynamic sql. Does Sql
> 2005 allow ORDER BY @.Variable? The query compiler accepts it, but it
> doesn't work. My @.Variable is a VARCHAR(50).
> Thanks!
>
|||http://databases.aspfaq.com/database/how-do-i-use-a-variable-in-an-order-by-clause.html
"Dan E" <dan_english2@.cox.net> wrote in message
news:u1AfegKGHHA.1280@.TK2MSFTNGP04.phx.gbl...
>I am writing a stored proc and do not want to use dynamic sql. Does Sql
>2005 allow ORDER BY @.Variable? The query compiler accepts it, but it
>doesn't work. My @.Variable is a VARCHAR(50).
> Thanks!
>

Does Sql 2005 allow ORDER BY @Variable ?

I am writing a stored proc and do not want to use dynamic sql. Does Sql
2005 allow ORDER BY @.Variable? The query compiler accepts it, but it
doesn't work. My @.Variable is a VARCHAR(50).
Thanks!Not directly. You could use a CASE structure to allow alternative orderings.
Here is one idea:
USE Northwind
GO
DECLARE @.OrderVar varchar(20)
SET @.OrderVar = 'LastName'
SELECT
LastName,
FirstName
FROM Employees
ORDER BY CASE @.OrderVar
WHEN 'LastName' THEN LastName
WHEN 'FirstName' THEN FirstName
END ASC,
CASE @.OrderVar
WHEN 'LastName' THEN FirstName
WHEN 'FirstName' THEN LastName
END ASC
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Dan E" <dan_english2@.cox.net> wrote in message news:u1AfegKGHHA.1280@.TK2MSFTNGP04.phx.gbl..
.
>I am writing a stored proc and do not want to use dynamic sql. Does Sql
> 2005 allow ORDER BY @.Variable? The query compiler accepts it, but it
> doesn't work. My @.Variable is a VARCHAR(50).
>
> Thanks!
>
>|||http://databases.aspfaq.com/databas...se
.html
"Dan E" <dan_english2@.cox.net> wrote in message
news:u1AfegKGHHA.1280@.TK2MSFTNGP04.phx.gbl...
>I am writing a stored proc and do not want to use dynamic sql. Does Sql
>2005 allow ORDER BY @.Variable? The query compiler accepts it, but it
>doesn't work. My @.Variable is a VARCHAR(50).
> Thanks!
>

Does RS recognize CHAR(13)?

I am making a query for mailing labels that I will generate in Reporting
Services. I have a SQL Server stored procedure that returns the address using
SQL like this
SELECT Address + CHAR(13) + Address2 + CHAR(13) + City + ' ' + State + ' '
+ Zip AS FullAddress
FROM ...
Assume the data is:
Address: 123 Main Street
Address2: Suite 3
City: Bellevue
State: WA
Zip: 98111
I assign the FullAddress field to a textbox in Reporting Services. I resize
it to be several lines high. The result I get is something like this:
123 Main StreetSuite3Bellevue
WA 98111
I have tried using CHAR(10) + CHAR(13) instead of just CHAR(10), but I get
double spaces. Does RS handle CHAR() output? Do I need to use separate fields?I've done this before but in the actual field expression not in the query.
Use Fields!Address.Value + CHR(10) + Fields!Address.Value + ...
"Rick" wrote:
> I am making a query for mailing labels that I will generate in Reporting
> Services. I have a SQL Server stored procedure that returns the address using
> SQL like this
> SELECT Address + CHAR(13) + Address2 + CHAR(13) + City + ' ' + State + ' '
> + Zip AS FullAddress
> FROM ...
> Assume the data is:
> Address: 123 Main Street
> Address2: Suite 3
> City: Bellevue
> State: WA
> Zip: 98111
> I assign the FullAddress field to a textbox in Reporting Services. I resize
> it to be several lines high. The result I get is something like this:
> 123 Main StreetSuite3Bellevue
> WA 98111
> I have tried using CHAR(10) + CHAR(13) instead of just CHAR(10), but I get
> double spaces. Does RS handle CHAR() output? Do I need to use separate fields?|||Thanks. Your answer got me going in the right direction. I didn't get the
Chr(10) to work in the actual field expression, but chr(10) + chr(13) did.
For the benefit of future thread readers...I went a step further and created
a code block in the Report|Report Parameters command to make things a little
cleaner. It easily handles the common case where the second address line is
empty (Note: my query returns an empty string rather than a NULL if there is
no Address2). My code is this:
Function BuildAddress(ByVal A1 As String, ByVal A2 As String, ByVal C AS
String) As String
Dim strReturn As String
=Code.BuildAddress(Fields!Address.Value, Fields!Address2.Value,
Fields!CSZ.Value)
If A2.Length > 0 Then
strReturn += A2 + chr(10) + chr(13)
End If
strReturn += C
Return strReturn
End Function
And then I used the following expression in the textbox:
=Code.BuildAddress(
"David Bienstock" wrote:
> I've done this before but in the actual field expression not in the query.
> Use Fields!Address.Value + CHR(10) + Fields!Address.Value + ...
> "Rick" wrote:
> > I am making a query for mailing labels that I will generate in Reporting
> > Services. I have a SQL Server stored procedure that returns the address using
> > SQL like this
> >
> > SELECT Address + CHAR(13) + Address2 + CHAR(13) + City + ' ' + State + ' '
> > + Zip AS FullAddress
> > FROM ...
> >
> > Assume the data is:
> > Address: 123 Main Street
> > Address2: Suite 3
> > City: Bellevue
> > State: WA
> > Zip: 98111
> >
> > I assign the FullAddress field to a textbox in Reporting Services. I resize
> > it to be several lines high. The result I get is something like this:
> >
> > 123 Main StreetSuite3Bellevue
> > WA 98111
> >
> > I have tried using CHAR(10) + CHAR(13) instead of just CHAR(10), but I get
> > double spaces. Does RS handle CHAR() output? Do I need to use separate fields?

Does RS allow querying between tables?

I have a very complex query that needs to return monthly and year-to-date
data. I'm using one table (table A) to correctly return the monthly data. I
would now like to create another table and use a result from table A to
correctly filter and display the year-to-date data along side the monthly
data.
Does RS allow querying between tables?
Example of desired results
Account July 2004 Year-to-Date
Ace Hardware $3000.00 $8452.21
--
DCountYou can't really use table A to display results into a different table. But
what you may want to try is to use the same dataset used in table A to create
a new table B and then on table B goto the properties of the table and then
goto the filters entry there and apply the filters you want to the table.
That should get you the results you're looking for.
> I have a very complex query that needs to return monthly and year-to-date
> data. I'm using one table (table A) to correctly return the monthly data. I
> would now like to create another table and use a result from table A to
> correctly filter and display the year-to-date data along side the monthly
> data.
> Does RS allow querying between tables?
> Example of desired results
> Account July 2004 Year-to-Date
> Ace Hardware $3000.00 $8452.21
>
> --
> DCount

Does Query plans change on static tables

I have attached two query plans , which are generated by Production server
at different times (1 day apart). ( i could not send mail with attached
plans I had to remove them)
Same query I have executed every day over two weeks time to see how the
query is performing in prod. server. I got two different plans. I didnt
understand how come the plan is changing so drastic when there are not many
data updates on the tables referred in the query. These tables are very
static and i am running dbcc reindex every night on these tables. Plan 1 has
index scan which is taking long time to execute (11 Sec), Plan 2 has no
index scan and so it is executing fast (<200ms). I didnt get a clue why it
is going for index scan some time and no index scan other times. I hope some
one will have answer for it.
Windows 2000 Server/SQL Server 2000 Standard SP4, 2 cpu, 1GB RAM, RAID 1
Disk
Thanks,
Subbu.
underprocessable|||underprocessable|||Statistics can influence the plan choice. Not all stats are tied to
indexes. So, you likely will want to make sure that all statistics are
up-to-date before making any conclusions. (update statistics <tablename>)
If you are close to a boundary between two plan choices, small changes can
push you from one plan to another on a recompile.
Hope that helps,
Conor
"subbu" <subbaiahd@.hotmail.com> wrote in message
news:%23wq2y6NWGHA.1192@.TK2MSFTNGP03.phx.gbl...
> Plan 1: No index SCAN
> Plan 2: Index SCAN
> I had to send in two separate mail as it crossed max limit size
> Thanks,
> Subbu.
> "subbu" <subbaiahd@.hotmail.com> wrote in message
> news:%238V3L3NWGHA.4212@.TK2MSFTNGP03.phx.gbl...
> many
> has
> it
> some
>
>

Does Query plans change on static tables

I have attached two query plans , which are generated by Production server
at different times (1 day apart). ( i could not send mail with attached
plans I had to remove them)
Same query I have executed every day over two weeks time to see how the
query is performing in prod. server. I got two different plans. I didnt
understand how come the plan is changing so drastic when there are not many
data updates on the tables referred in the query. These tables are very
static and i am running dbcc reindex every night on these tables. Plan 1 has
index scan which is taking long time to execute (11 Sec), Plan 2 has no
index scan and so it is executing fast (<200ms). I didnt get a clue why it
is going for index scan some time and no index scan other times. I hope some
one will have answer for it.
Windows 2000 Server/SQL Server 2000 Standard SP4, 2 cpu, 1GB RAM, RAID 1
Disk
Thanks,
Subbu.underprocessable|||underprocessable|||Statistics can influence the plan choice. Not all stats are tied to
indexes. So, you likely will want to make sure that all statistics are
up-to-date before making any conclusions. (update statistics <tablename> )
If you are close to a boundary between two plan choices, small changes can
push you from one plan to another on a recompile.
Hope that helps,
Conor
"subbu" <subbaiahd@.hotmail.com> wrote in message
news:%23wq2y6NWGHA.1192@.TK2MSFTNGP03.phx.gbl...
> Plan 1: No index SCAN
> Plan 2: Index SCAN
> I had to send in two separate mail as it crossed max limit size
> Thanks,
> Subbu.
> "subbu" <subbaiahd@.hotmail.com> wrote in message
> news:%238V3L3NWGHA.4212@.TK2MSFTNGP03.phx.gbl...
> many
> has
> it
> some
>
>

Does Query plans change on static tables

I have attached two query plans , which are generated by Production server
at different times (1 day apart). ( i could not send mail with attached
plans I had to remove them)
Same query I have executed every day over two weeks time to see how the
query is performing in prod. server. I got two different plans. I didnt
understand how come the plan is changing so drastic when there are not many
data updates on the tables referred in the query. These tables are very
static and i am running dbcc reindex every night on these tables. Plan 1 has
index scan which is taking long time to execute (11 Sec), Plan 2 has no
index scan and so it is executing fast (<200ms). I didnt get a clue why it
is going for index scan some time and no index scan other times. I hope some
one will have answer for it.
Windows 2000 Server/SQL Server 2000 Standard SP4, 2 cpu, 1GB RAM, RAID 1
Disk
Thanks,
Subbu.Statistics can influence the plan choice. Not all stats are tied to
indexes. So, you likely will want to make sure that all statistics are
up-to-date before making any conclusions. (update statistics <tablename>)
If you are close to a boundary between two plan choices, small changes can
push you from one plan to another on a recompile.
Hope that helps,
Conor
"subbu" <subbaiahd@.hotmail.com> wrote in message
news:%23wq2y6NWGHA.1192@.TK2MSFTNGP03.phx.gbl...
> Plan 1: No index SCAN
> Plan 2: Index SCAN
> I had to send in two separate mail as it crossed max limit size
> Thanks,
> Subbu.
> "subbu" <subbaiahd@.hotmail.com> wrote in message
> news:%238V3L3NWGHA.4212@.TK2MSFTNGP03.phx.gbl...
>> I have attached two query plans , which are generated by Production
>> server
>> at different times (1 day apart). ( i could not send mail with attached
>> plans I had to remove them)
>> Same query I have executed every day over two weeks time to see how the
>> query is performing in prod. server. I got two different plans. I didnt
>> understand how come the plan is changing so drastic when there are not
> many
>> data updates on the tables referred in the query. These tables are very
>> static and i am running dbcc reindex every night on these tables. Plan 1
> has
>> index scan which is taking long time to execute (11 Sec), Plan 2 has no
>> index scan and so it is executing fast (<200ms). I didnt get a clue why
> it
>> is going for index scan some time and no index scan other times. I hope
> some
>> one will have answer for it.
>>
>> Windows 2000 Server/SQL Server 2000 Standard SP4, 2 cpu, 1GB RAM, RAID 1
>> Disk
>>
>> Thanks,
>> Subbu.
>>
>>
>>
>
>

Does not work on page

I can test it in query builder but when i preview the page and go to do the search i get nothing

Here is my statement

SELECT Employees.Last_Name, Employees.First_Name, Job_Transaction.Name, Seq_Descript.Seq_Description
FROM Call_List INNER JOIN
Call_Group ON Call_List.Group_ID = Call_Group.Group_ID AND Call_List.Group_ID = Call_Group.Group_ID INNER JOIN
Employees ON Call_List.Clock = Employees.Clock INNER JOIN
Job_Transaction ON Call_Group.Group_ID = Job_Transaction.Group_ID INNER JOIN
Seq_Descript ON Call_List.Sequence = Seq_Descript.Sequence
WHERE (Call_List.Clock = @.Clock)

Mike

Themlruts:

I can test it in query builder but when i preview the page and go to do the search i get nothing

You mean that you run this in query builder and get data but when you use it in a page you get nothing? Then (a) you're using different values of @.clock or (b) your page is pointing to a different database or (c) there is something wrong with your display (maybe you aren't binding the control to the data source)

|||

Its weird it should be binding fine. When i choose control i select the from the drop down box the input text field.

I dont understand

Mike

sql

does not match with a table name or alias name used in the query

I am trying to create a trigger on a table. The idea is
that the trigger will stop duplicates being entered but
will allow null values. I am trying to use the inserted
table but am receiving the following error.
The column prefix 'T2' does not match with a table name
or alias name used in the query.
The table and trigger creation script follows.
Thanks in advance.
========= Table
=========
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Class]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[Class]
GO
CREATE TABLE [dbo].[Class] (
[ClassID] [int] IDENTITY (1, 1) NOT NULL ,
[Name] [varchar] (60) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Symbol] [T_STD_SYMBOL] NULL ,
[ClassTypeID] [int] NOT NULL ,
[Description] [varchar] (40) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
========= Trigger
=========
if exists (select 1
from sysobjects
where id = object_id('uqSymbol_class')
and type = 'TR')
drop trigger uqSymbol_class
go
/* Trigger to stop duplicate Symbols being entered in
the Symbol column in the class table. */
create trigger uqSymbol_class on Class for insert, update
as
begin
declare
@.numrows int,
@.errno int,
@.errmsg varchar(255)
select @.numrows = @.@.rowcount
if @.numrows = 0
return
/* Check to see if the inserted updated symbol
already exists */
if update(symbol)
begin
-- Check weather the Symbol value is Null.
Ignore if so.
-- if ((select symbol from inserted) != null)
begin -- IF((SELECT COUNT(*) FROM INSERTED WHERE
Symbol IS NOT NULL) > 0)
if (select count(*)
from Class t1, inserted t2
where t1.symbol = t2.symbol) >1 AND
T2.Symbol IS NOT NULL
begin
select @.errno = 30003,
@.errmsg = 'The Symbol you have
entered or just updated already exists.'
goto error
end
end
-- end
return
/* Error handling */
error:
raiserror @.errno @.errmsg
rollback transaction
end
ThanX :-)You are not giving us any code which uses t2 as an alias... please repost
complete code...
also if there are ever multiple triggers, and one of the other triggers does
a select insert update or delete, then @.@.rowcount in this trigger will not
be correct. It is probably safer to count records in inserted and deleted...
"Jamie" <jamie.downs@.risk.sungard.com> wrote in message
news:0d4101c360b0$976a42e0$a101280a@.phx.gbl...
> I am trying to create a trigger on a table. The idea is
> that the trigger will stop duplicates being entered but
> will allow null values. I am trying to use the inserted
> table but am receiving the following error.
> The column prefix 'T2' does not match with a table name
> or alias name used in the query.
> The table and trigger creation script follows.
> Thanks in advance.
> =========> Table
> =========> if exists (select * from dbo.sysobjects where id => object_id(N'[dbo].[Class]') and OBJECTPROPERTY(id,
> N'IsUserTable') = 1)
> drop table [dbo].[Class]
> GO
> CREATE TABLE [dbo].[Class] (
> [ClassID] [int] IDENTITY (1, 1) NOT NULL ,
> [Name] [varchar] (60) COLLATE
> SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [Symbol] [T_STD_SYMBOL] NULL ,
> [ClassTypeID] [int] NOT NULL ,
> [Description] [varchar] (40) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
>
> =========> Trigger
> =========> if exists (select 1
> from sysobjects
> where id = object_id('uqSymbol_class')
> and type = 'TR')
> drop trigger uqSymbol_class
> go
> /* Trigger to stop duplicate Symbols being entered in
> the Symbol column in the class table. */
> create trigger uqSymbol_class on Class for insert, update
> as
> begin
> declare
> @.numrows int,
> @.errno int,
> @.errmsg varchar(255)
> select @.numrows = @.@.rowcount
> if @.numrows = 0
> return
>
> /* Check to see if the inserted updated symbol
> already exists */
> if update(symbol)
> begin
> -- Check weather the Symbol value is Null.
> Ignore if so.
> -- if ((select symbol from inserted) != null)
>
----
--
> ThanX :-)|||You missed out the relevant bit of code.
In fact you don't need a trigger to do this. You can use an indexed view to
enforce uniqueness only for non-NULL values:
CREATE VIEW Symbols
WITH SCHEMABINDING
AS
SELECT symbol
FROM dbo.class
WHERE symbol IS NOT NULL
GO
CREATE UNIQUE CLUSTERED INDEX uclsymbol ON Symbols (symbol)
--
David Portas
--
Please reply only to the newsgroup
--|||Hi Jamie,
Posting messages with attachments usually isn't very useful because most
people do not trust them, won't open them and so don't have enough
information to answer your question. Post everything in plain text instead.
If you are on SQL Server 2000 you don't have to use a trigger to achieve
this, but you can instead create an indexed view to check for duplicates:
CREATE VIEW Class_Symbol_check
AS
SELECT Symbol FROM Class
WHERE Symbol IS NOT NULL
GO
CREATE UNIQUE INDEX ON Class_Symbol_check (Symbol )
GO
Read the topic "Indexed views" in Books online for more information.
The fact that a UNIQUE index can only have one NULL (otherwise you could
just create a unique constraint on the Symbol column in the class table) is
a problem in SQL Server and one I sincerly hope will be addressed in the
next version.
--
Jacco Schalkwijk MCDBA, MCSD, MCSE
Database Administrator
Eurostop Ltd.
"Jamie" <jamie.downs@.risk.sungard.com> wrote in message
news:0d4101c360b0$976a42e0$a101280a@.phx.gbl...
> I am trying to create a trigger on a table. The idea is
> that the trigger will stop duplicates being entered but
> will allow null values. I am trying to use the inserted
> table but am receiving the following error.
> The column prefix 'T2' does not match with a table name
> or alias name used in the query.
> The table and trigger creation script follows.
> Thanks in advance.
> =========> Table
> =========> if exists (select * from dbo.sysobjects where id => object_id(N'[dbo].[Class]') and OBJECTPROPERTY(id,
> N'IsUserTable') = 1)
> drop table [dbo].[Class]
> GO
> CREATE TABLE [dbo].[Class] (
> [ClassID] [int] IDENTITY (1, 1) NOT NULL ,
> [Name] [varchar] (60) COLLATE
> SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [Symbol] [T_STD_SYMBOL] NULL ,
> [ClassTypeID] [int] NOT NULL ,
> [Description] [varchar] (40) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
>
> =========> Trigger
> =========> if exists (select 1
> from sysobjects
> where id = object_id('uqSymbol_class')
> and type = 'TR')
> drop trigger uqSymbol_class
> go
> /* Trigger to stop duplicate Symbols being entered in
> the Symbol column in the class table. */
> create trigger uqSymbol_class on Class for insert, update
> as
> begin
> declare
> @.numrows int,
> @.errno int,
> @.errmsg varchar(255)
> select @.numrows = @.@.rowcount
> if @.numrows = 0
> return
>
> /* Check to see if the inserted updated symbol
> already exists */
> if update(symbol)
> begin
> -- Check weather the Symbol value is Null.
> Ignore if so.
> -- if ((select symbol from inserted) != null)
>
----
--
> ThanX :-)|||> a problem in SQL Server and one I sincerly hope will be addressed in the
> next version.
Full SQL92 constraints would solve this problem and more. I hope Yukon will
support the ANSI-style constraints.
--
David Portas
--
Please reply only to the newsgroup
--|||Yeah,
In the all the info I have read and heard about Yukon there is a lot of talk
about CLR support, and only very general remarks about improvements in
T-SQL. I guess the marketing people at Microsoft think that (full ANSI
constraints etc) is "hard core SQL" and too difficult for 95% of the
developers, and they are probably right. Euan Garden (Program manager for
SQL Server) said during a presentation "that people think that Microsoft
will drop support for T-SQL now that there's CLR support, but that's not the
case", so that tells you the level they are targeting. </rant>
Of the new SQL features in Yukon that I know of I like the idea in Yukon
that you can write your own datatypes, I just don't like the idea that you
have to write them in a CLR language instead of being able to declare them
in SQL, although I expect that there will soon be a cottage industry in
datatypes for SQL Server (postcodes, telephone numbers, credit card numbers,
ISBN etc), just like there was with ActiveX controls for VB 6.
But there is little or no information on structural improvement to T-SQL and
further compliance with ANSI standards. Do we get ANSI style constraints?
row constructors? ...?
--
Jacco Schalkwijk MCDBA, MCSD, MCSE
Database Administrator
Eurostop Ltd.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:#ZqfnQLYDHA.1004@.TK2MSFTNGP12.phx.gbl...
> > a problem in SQL Server and one I sincerly hope will be addressed in the
> > next version.
> Full SQL92 constraints would solve this problem and more. I hope Yukon
will
> support the ANSI-style constraints.
> --
> David Portas
> --
> Please reply only to the newsgroup
> --
>
>|||Thanks for all your help. I have implemented the view as
the trigger.
Jacco, what did you mean by CLR.
Thanks again.

does not install SQL Query Analyzer

Hi,

I am developing and application in pocket pc windows mobile 2003. I have added a reference to System.Data.SqlServerCe.dll but when i deploy the application query analyzer doesnt seem to install. Any help please?

cheers,

michael

Hi,

Sorry... for info I am using CS VS2005 and SQL Mobile.

Thank you.

michael

|||

Query Analyzer 3.0 should deploy automatically if you are building and deploying a DEBUG build in VS2005 given your reference to System.Data.SqlServerCe.dll version 2.0. You can also just grab the dev tools CAB and install it to your device/emulator to save time. it is located (depending on your device's WinCE kernel) at:

<drive:>\Program Files\Microsoft Visual Studio 8\SmartDevices\SDK\SQL Server\Mobile\v3.0\wce400\armv4\sqlce30.dev.ENU.ppc.wce4.armv4.CAB

or

C:\Program Files\Microsoft Visual Studio 8\SmartDevices\SDK\SQL Server\Mobile\v3.0\wce500\<device CPU architecture>\

Darren

Does MS know SQL2005 query performance is slower than SQL2000?

Hi,

I tested a simple query like Select colA from TableB where colC= 'XX' with SQL2000 and SQL2005.

Of course, Index is same, number of records are same.

After I execute that query and checked it with profiler. SQL2000 just took 18 ms but SQL2005 took 118 ms in my environment. Actually, the machine that is installed SQL2005 has better H/W spec than SQL2000.

I could not belive that so I tested several times but SQL2005 was slow.

After I searched this forum, I found that some guys had same problem with SQL2005 performance. I rebuilt index in SQL2005 but didn't work.

Even though I am using SP2, it is still slow than SQL2000.

Am I missing somthing? I could not understand how it could happen.

Does anybody have any solution?

Thank you in advance

James

did you clear the proc cache before running the procedures? Running the commands below will ensure that you are running both sprocs on common ground:

DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE

Also, you can't always go by the time it takes to run a query. You really need to compare the logical reads returned by running the command SET STATISTICS IO ON before you run the statements to compare the reads. If your reads are drastically different, something may be funky.

Tim|||

Tim,

Thank you for answer.

Actually, before I tested, I restarted all services so it was not a problem of cache.

In addition, reads in profile log of SQL2005 is more than SQL2000 which is not strange based on the result.

Funny thing is speed is smiliar after data is cached. This problem happened when I tried data from disk.

I am not sure what is wrong.

I should discuss it with MS support soon.

Thank you

James

|||After you upgraded the database to SQL 2005 did you update the stats or rebuild the indexes? It's recommended to update the stats on the tables and indexes after upgrading to SQL 2005 to get proper query plans in SQL 2005.|||

Thank you Denny for replying

Unfortunately, it didn't work

Actually, I didn't migrate DB from SQL2000. I just created exactly same DB as SQL2000.

100ms is not a big deal for a SQL statement but if a stored procedure has 1000 sql statements. It will be 100,000ms which is a big.

This can explain why same stored procedure is slower than SQL2000.

James.

|||

Try optimising the data disks:

1) Set the disk to Basic disk

2) If the disk subsystem is RAID, set the stripe size to 64k, and the controller cache to 100% write.

3) Using DISKPART, create the partition using the command CREATE PARTITION PRIMARY ALIGN=64

4) Format the data disk with a cluster size of 64k.

Now you're ready to go from the disk side of things!

|||Does the execution plans show the SQL Servers taking the same path to the data? Where do the cost differences show up?|||

Danny and BigE

Thank you for replying.

Well. Execution plan is exactly same. I am not sure where it comes from.

As I said the machine that has SQL2005 is better H/W spec so I don't think it is a problem of H/W as BigE said.

I might try to do as BigE suggested but I could not agree we should set this for running SQL2005.

Think about that. SQL2005 is advanced version than SQL2000 which is 7 years ago!! Why does user consider about those kinds of disk setting? Even though it is ture, what is the big benefit of upgrading to user who is using small or medium application?

Anyway, If you have two machine that has SQL2000 and SQL2005, just try a select statement and check read and duration.

You will notice what I am saying..

James.|||

Hi James,

Did you get the solution as you described?

I have same problem and I cannot find any solutions.

Please help.

Clara

|||

Thank you BigE

I think your suggestion might improve performance but here is my concern about using SQL2005.

If it is a problem of Disk speed, Why does MS provide a fucntion to make DATABASE on top of the MEMORY DISK?

In other words, MS can create MEMORY DISK DATABASE in SQL2008 for better performance !

Maybe they will say to me that I am crazy but... If you can use UPS, then SQL server can dump that memory database to Disk during UPS is working.

Anyway, I could not buy that reason becaue , As I said, the SQL is running slower server than SQL2005.

By copying Clara,

Sorry, I could not find the solution yet. One of MS consultant that I know gave to me some suggestion but it doesn't work.

Maybe I should try SQL2008 CTP instead of SQL2005

Regards,

James Lim

Does MS know SQL2005 query performance is slower than SQL2000?

Hi,

I tested a simple query like Select colA from TableB where colC= 'XX' with SQL2000 and SQL2005.

Of course, Index is same, number of records are same.

After I execute that query and checked it with profiler. SQL2000 just took 18 ms but SQL2005 took 118 ms in my environment. Actually, the machine that is installed SQL2005 has better H/W spec than SQL2000.

I could not belive that so I tested several times but SQL2005 was slow.

After I searched this forum, I found that some guys had same problem with SQL2005 performance. I rebuilt index in SQL2005 but didn't work.

Even though I am using SP2, it is still slow than SQL2000.

Am I missing somthing? I could not understand how it could happen.

Does anybody have any solution?

Thank you in advance

James

did you clear the proc cache before running the procedures? Running the commands below will ensure that you are running both sprocs on common ground:

DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE

Also, you can't always go by the time it takes to run a query. You really need to compare the logical reads returned by running the command SET STATISTICS IO ON before you run the statements to compare the reads. If your reads are drastically different, something may be funky.

Tim|||

Tim,

Thank you for answer.

Actually, before I tested, I restarted all services so it was not a problem of cache.

In addition, reads in profile log of SQL2005 is more than SQL2000 which is not strange based on the result.

Funny thing is speed is smiliar after data is cached. This problem happened when I tried data from disk.

I am not sure what is wrong.

I should discuss it with MS support soon.

Thank you

James

|||After you upgraded the database to SQL 2005 did you update the stats or rebuild the indexes? It's recommended to update the stats on the tables and indexes after upgrading to SQL 2005 to get proper query plans in SQL 2005.|||

Thank you Denny for replying

Unfortunately, it didn't work

Actually, I didn't migrate DB from SQL2000. I just created exactly same DB as SQL2000.

100ms is not a big deal for a SQL statement but if a stored procedure has 1000 sql statements. It will be 100,000ms which is a big.

This can explain why same stored procedure is slower than SQL2000.

James.

|||

Try optimising the data disks:

1) Set the disk to Basic disk

2) If the disk subsystem is RAID, set the stripe size to 64k, and the controller cache to 100% write.

3) Using DISKPART, create the partition using the command CREATE PARTITION PRIMARY ALIGN=64

4) Format the data disk with a cluster size of 64k.

Now you're ready to go from the disk side of things!

|||Does the execution plans show the SQL Servers taking the same path to the data? Where do the cost differences show up?|||

Danny and BigE

Thank you for replying.

Well. Execution plan is exactly same. I am not sure where it comes from.

As I said the machine that has SQL2005 is better H/W spec so I don't think it is a problem of H/W as BigE said.

I might try to do as BigE suggested but I could not agree we should set this for running SQL2005.

Think about that. SQL2005 is advanced version than SQL2000 which is 7 years ago!! Why does user consider about those kinds of disk setting? Even though it is ture, what is the big benefit of upgrading to user who is using small or medium application?

Anyway, If you have two machine that has SQL2000 and SQL2005, just try a select statement and check read and duration.

You will notice what I am saying..

James.|||

Hi James,

Did you get the solution as you described?

I have same problem and I cannot find any solutions.

Please help.

Clara

|||

Thank you BigE

I think your suggestion might improve performance but here is my concern about using SQL2005.

If it is a problem of Disk speed, Why does MS provide a fucntion to make DATABASE on top of the MEMORY DISK?

In other words, MS can create MEMORY DISK DATABASE in SQL2008 for better performance !

Maybe they will say to me that I am crazy but... If you can use UPS, then SQL server can dump that memory database to Disk during UPS is working.

Anyway, I could not buy that reason becaue , As I said, the SQL is running slower server than SQL2005.

By copying Clara,

Sorry, I could not find the solution yet. One of MS consultant that I know gave to me some suggestion but it doesn't work.

Maybe I should try SQL2008 CTP instead of SQL2005

Regards,

James Lim

Does MS know SQL2005 query performance is slower than SQL2000?

Hi,

I tested a simple query like Select colA from TableB where colC= 'XX' with SQL2000 and SQL2005.

Of course, Index is same, number of records are same.

After I execute that query and checked it with profiler. SQL2000 just took 18 ms but SQL2005 took 118 ms in my environment. Actually, the machine that is installed SQL2005 has better H/W spec than SQL2000.

I could not belive that so I tested several times but SQL2005 was slow.

After I searched this forum, I found that some guys had same problem with SQL2005 performance. I rebuilt index in SQL2005 but didn't work.

Even though I am using SP2, it is still slow than SQL2000.

Am I missing somthing? I could not understand how it could happen.

Does anybody have any solution?

Thank you in advance

James

did you clear the proc cache before running the procedures? Running the commands below will ensure that you are running both sprocs on common ground:

DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE

Also, you can't always go by the time it takes to run a query. You really need to compare the logical reads returned by running the command SET STATISTICS IO ON before you run the statements to compare the reads. If your reads are drastically different, something may be funky.

Tim|||

Tim,

Thank you for answer.

Actually, before I tested, I restarted all services so it was not a problem of cache.

In addition, reads in profile log of SQL2005 is more than SQL2000 which is not strange based on the result.

Funny thing is speed is smiliar after data is cached. This problem happened when I tried data from disk.

I am not sure what is wrong.

I should discuss it with MS support soon.

Thank you

James

|||After you upgraded the database to SQL 2005 did you update the stats or rebuild the indexes? It's recommended to update the stats on the tables and indexes after upgrading to SQL 2005 to get proper query plans in SQL 2005.|||

Thank you Denny for replying

Unfortunately, it didn't work

Actually, I didn't migrate DB from SQL2000. I just created exactly same DB as SQL2000.

100ms is not a big deal for a SQL statement but if a stored procedure has 1000 sql statements. It will be 100,000ms which is a big.

This can explain why same stored procedure is slower than SQL2000.

James.

|||

Try optimising the data disks:

1) Set the disk to Basic disk

2) If the disk subsystem is RAID, set the stripe size to 64k, and the controller cache to 100% write.

3) Using DISKPART, create the partition using the command CREATE PARTITION PRIMARY ALIGN=64

4) Format the data disk with a cluster size of 64k.

Now you're ready to go from the disk side of things!

|||Does the execution plans show the SQL Servers taking the same path to the data? Where do the cost differences show up?|||

Danny and BigE

Thank you for replying.

Well. Execution plan is exactly same. I am not sure where it comes from.

As I said the machine that has SQL2005 is better H/W spec so I don't think it is a problem of H/W as BigE said.

I might try to do as BigE suggested but I could not agree we should set this for running SQL2005.

Think about that. SQL2005 is advanced version than SQL2000 which is 7 years ago!! Why does user consider about those kinds of disk setting? Even though it is ture, what is the big benefit of upgrading to user who is using small or medium application?

Anyway, If you have two machine that has SQL2000 and SQL2005, just try a select statement and check read and duration.

You will notice what I am saying..

James.|||

Hi James,

Did you get the solution as you described?

I have same problem and I cannot find any solutions.

Please help.

Clara

|||

Thank you BigE

I think your suggestion might improve performance but here is my concern about using SQL2005.

If it is a problem of Disk speed, Why does MS provide a fucntion to make DATABASE on top of the MEMORY DISK?

In other words, MS can create MEMORY DISK DATABASE in SQL2008 for better performance !

Maybe they will say to me that I am crazy but... If you can use UPS, then SQL server can dump that memory database to Disk during UPS is working.

Anyway, I could not buy that reason becaue , As I said, the SQL is running slower server than SQL2005.

By copying Clara,

Sorry, I could not find the solution yet. One of MS consultant that I know gave to me some suggestion but it doesn't work.

Maybe I should try SQL2008 CTP instead of SQL2005

Regards,

James Lim

does linked server solve performance issue??

Hi,
I've this query using four tables of 3 different databases residing on
the same server
select top 5 * from dblezen.dbo.ads ta
left outer join dbalgemeen.dbo.users tb on ta.ads_usrid = tb.usr_id
left outer join dbingeven.dbo.ads tc on ta.ads_ovid = tc.ads_adid
left outer join dbingeven.dbo.edition td on tc.ads_editionid = td.ed_id
where ta.ads_boonline > dateadd(n, -100, getdate())
In the near future we'll propably put the 3 databases on 3 different
physical servers. So I'll have to create linked servers, meaning I've
got to execute something like this :
select top 5 * from server1.dblezen.dbo.ads ta
left outer join server2.dbalgemeen.dbo.users tb on ta.ads_usrid = tb.usr_id
left outer join server3.dbingeven.dbo.ads tc on ta.ads_ovid = tc.ads_adid
left outer join server3.dbingeven.dbo.edition td on tc.ads_editionid =
td.ed_id
where ta.ads_boonline > dateadd(n, -100, getdate())
First question:
Can I fix that the servernames server1, server2 and server3 are logical
names (different than the computername) all refering to the same local
server?
If this is possible I guess I can adapt all my stored procedures before
effectively put the 3 databases on 3 different servers, and if
necessary, I can go back to 1 server again afterwards, without having to
change my stored procedures again.
Second question:
If it can be done, will it have consequences on the execution time of
the queries? Will there be overhead 1) because SQL server is going to
use distributed transaction instead of local transactions or 2) because
SQL server has to translate the logical server names into physical
server names while it's actually not necessary when the databases
reside on the same server.
Third question:
Is moving the 3 databases to 3 different servers and start using linked
server the obvious best option to resolve the performance problem of our
database server?
At the moment we have one IIS-server, running ASP.NET and one database
server, running SQL Server, using 3 databases.
- dbingeven is mainly used to insert new rows
- dblezen is mainly used to read rows (full text indexed)
- dbalgemeen contains general data used by the other two (user data,
parameters, statistical data, ...)
Data is continuously inserted in dbingeven and continuously copied
(after processing) to dblezen.
All 3 databases contain stored procedures refering each other all the
time (joins as in the query above as well as calling each others stored
procedures).
Fourth question:
Is it predictable the gain of performance win (on CPU, and disk access)
by spreading the data will be lost on network traffic and distributed
transactions processes, meaning our problem will not be really solved?
Thanks in Advance,
Peter Van Wilrijk.
Why don't you replicate the data back to the server that you're query is run
on, and select from there?
"Peter Van Wilrijk" <pro@.koopjeskrant.be> wrote in message
news:N_Vte.126388$I62.7018547@.phobos.telenet-ops.be...
> Hi,
> I've this query using four tables of 3 different databases residing on the
> same server
> select top 5 * from dblezen.dbo.ads ta
> left outer join dbalgemeen.dbo.users tb on ta.ads_usrid = tb.usr_id
> left outer join dbingeven.dbo.ads tc on ta.ads_ovid = tc.ads_adid
> left outer join dbingeven.dbo.edition td on tc.ads_editionid = td.ed_id
> where ta.ads_boonline > dateadd(n, -100, getdate())
> In the near future we'll propably put the 3 databases on 3 different
> physical servers. So I'll have to create linked servers, meaning I've
> got to execute something like this :
> select top 5 * from server1.dblezen.dbo.ads ta
> left outer join server2.dbalgemeen.dbo.users tb on ta.ads_usrid =
> tb.usr_id
> left outer join server3.dbingeven.dbo.ads tc on ta.ads_ovid = tc.ads_adid
> left outer join server3.dbingeven.dbo.edition td on tc.ads_editionid =
> td.ed_id
> where ta.ads_boonline > dateadd(n, -100, getdate())
> First question:
> Can I fix that the servernames server1, server2 and server3 are logical
> names (different than the computername) all refering to the same local
> server?
> If this is possible I guess I can adapt all my stored procedures before
> effectively put the 3 databases on 3 different servers, and if necessary,
> I can go back to 1 server again afterwards, without having to change my
> stored procedures again.
> Second question:
> If it can be done, will it have consequences on the execution time of the
> queries? Will there be overhead 1) because SQL server is going to use
> distributed transaction instead of local transactions or 2) because SQL
> server has to translate the logical server names into physical server
> names while it's actually not necessary when the databases
> reside on the same server.
> Third question:
> Is moving the 3 databases to 3 different servers and start using linked
> server the obvious best option to resolve the performance problem of our
> database server?
> At the moment we have one IIS-server, running ASP.NET and one database
> server, running SQL Server, using 3 databases.
> - dbingeven is mainly used to insert new rows
> - dblezen is mainly used to read rows (full text indexed)
> - dbalgemeen contains general data used by the other two (user data,
> parameters, statistical data, ...)
> Data is continuously inserted in dbingeven and continuously copied (after
> processing) to dblezen.
> All 3 databases contain stored procedures refering each other all the time
> (joins as in the query above as well as calling each others stored
> procedures).
> Fourth question:
> Is it predictable the gain of performance win (on CPU, and disk access)
> by spreading the data will be lost on network traffic and distributed
> transactions processes, meaning our problem will not be really solved?
>
> Thanks in Advance,
> Peter Van Wilrijk.
>
>
>
>
>
|||ChrisR wrote:
> Why don't you replicate the data back to the server that you're query is run
> on, and select from there?
>
Thanks, Good question?
I surely must start checking out how to implement replication ... but I
guess it will not be an option for our website, because our users, while
surfing ... read, insert and update data in all three databases. I
guess this means we should replicate continuously in two directions, so
updates, deletes and inserts on server 2 must be immediately available
on server 1 and vice versa. Can replication do that?
Kind regards,
Peter Roothans.
|||Yes. Look up Transactional Replication in BOL. You will want to use the
Immediate Updating option.
"Peter Van Wilrijk" <pro@.koopjeskrant.be> wrote in message
news:Tu9ue.126847$Xj7.7072901@.phobos.telenet-ops.be...
> ChrisR wrote:
> Thanks, Good question?
> I surely must start checking out how to implement replication ... but I
> guess it will not be an option for our website, because our users, while
> surfing ... read, insert and update data in all three databases. I guess
> this means we should replicate continuously in two directions, so updates,
> deletes and inserts on server 2 must be immediately available on server 1
> and vice versa. Can replication do that?
> Kind regards,
> Peter Roothans.
>
|||Thanks ChrisR.
I just found out you can give 1 server multiple names as follows.
sp_addlinkedserver N'SRVDBI', ' ', N'SQLOLEDB', N'SRV-WEBDB'
sp_addlinkedserver N'SRVDBL', ' ', N'SQLOLEDB', N'SRV-WEBDB'
sp_addlinkedserver N'SRVDBA', ' ', N'SQLOLEDB', N'SRV-WEBDB'
So, this way I can move my database to any server without the need to
adapt all code. The only thing to do than, is to let refer the logical
name to another physical server.
I've read BOL concerning Transactional Replication. I'm certainly going
to try it out, but since I'm not familiair with it and since the
document warns for loopback detection when replicating multiple related
databases, I'll start with the linked server solution.
Thanks,
Kind regards,
Peter Van Wilrijk
ChrisR wrote:
> Yes. Look up Transactional Replication in BOL. You will want to use the
> Immediate Updating option.
>
> "Peter Van Wilrijk" <pro@.koopjeskrant.be> wrote in message
> news:Tu9ue.126847$Xj7.7072901@.phobos.telenet-ops.be...
>
>

does linked server solve performance issue??

Hi,
I've this query using four tables of 3 different databases residing on
the same server
select top 5 * from dblezen.dbo.ads ta
left outer join dbalgemeen.dbo.users tb on ta.ads_usrid = tb.usr_id
left outer join dbingeven.dbo.ads tc on ta.ads_ovid = tc.ads_adid
left outer join dbingeven.dbo.edition td on tc.ads_editionid = td.ed_id
where ta.ads_boonline > dateadd(n, -100, getdate())
In the near future we'll propably put the 3 databases on 3 different
physical servers. So I'll have to create linked servers, meaning I've
got to execute something like this :
select top 5 * from server1.dblezen.dbo.ads ta
left outer join server2.dbalgemeen.dbo.users tb on ta.ads_usrid = tb.usr_id
left outer join server3.dbingeven.dbo.ads tc on ta.ads_ovid = tc.ads_adid
left outer join server3.dbingeven.dbo.edition td on tc.ads_editionid =
td.ed_id
where ta.ads_boonline > dateadd(n, -100, getdate())
First question:
Can I fix that the servernames server1, server2 and server3 are logical
names (different than the computername) all refering to the same local
server?
If this is possible I guess I can adapt all my stored procedures before
effectively put the 3 databases on 3 different servers, and if
necessary, I can go back to 1 server again afterwards, without having to
change my stored procedures again.
Second question:
If it can be done, will it have consequences on the execution time of
the queries? Will there be overhead 1) because SQL server is going to
use distributed transaction instead of local transactions or 2) because
SQL server has to translate the logical server names into physical
server names while it's actually not necessary when the databases
reside on the same server.
Third question:
Is moving the 3 databases to 3 different servers and start using linked
server the obvious best option to resolve the performance problem of our
database server'
At the moment we have one IIS-server, running ASP.NET and one database
server, running SQL Server, using 3 databases.
- dbingeven is mainly used to insert new rows
- dblezen is mainly used to read rows (full text indexed)
- dbalgemeen contains general data used by the other two (user data,
parameters, statistical data, ...)
Data is continuously inserted in dbingeven and continuously copied
(after processing) to dblezen.
All 3 databases contain stored procedures refering each other all the
time (joins as in the query above as well as calling each others stored
procedures).
Fourth question:
Is it predictable the gain of performance win (on CPU, and disk access)
by spreading the data will be lost on network traffic and distributed
transactions processes, meaning our problem will not be really solved'
Thanks in Advance,
Peter Van Wilrijk.Why don't you replicate the data back to the server that you're query is run
on, and select from there?
"Peter Van Wilrijk" <pro@.koopjeskrant.be> wrote in message
news:N_Vte.126388$I62.7018547@.phobos.telenet-ops.be...
> Hi,
> I've this query using four tables of 3 different databases residing on the
> same server
> select top 5 * from dblezen.dbo.ads ta
> left outer join dbalgemeen.dbo.users tb on ta.ads_usrid = tb.usr_id
> left outer join dbingeven.dbo.ads tc on ta.ads_ovid = tc.ads_adid
> left outer join dbingeven.dbo.edition td on tc.ads_editionid = td.ed_id
> where ta.ads_boonline > dateadd(n, -100, getdate())
> In the near future we'll propably put the 3 databases on 3 different
> physical servers. So I'll have to create linked servers, meaning I've
> got to execute something like this :
> select top 5 * from server1.dblezen.dbo.ads ta
> left outer join server2.dbalgemeen.dbo.users tb on ta.ads_usrid =
> tb.usr_id
> left outer join server3.dbingeven.dbo.ads tc on ta.ads_ovid = tc.ads_adid
> left outer join server3.dbingeven.dbo.edition td on tc.ads_editionid =
> td.ed_id
> where ta.ads_boonline > dateadd(n, -100, getdate())
> First question:
> Can I fix that the servernames server1, server2 and server3 are logical
> names (different than the computername) all refering to the same local
> server?
> If this is possible I guess I can adapt all my stored procedures before
> effectively put the 3 databases on 3 different servers, and if necessary,
> I can go back to 1 server again afterwards, without having to change my
> stored procedures again.
> Second question:
> If it can be done, will it have consequences on the execution time of the
> queries? Will there be overhead 1) because SQL server is going to use
> distributed transaction instead of local transactions or 2) because SQL
> server has to translate the logical server names into physical server
> names while it's actually not necessary when the databases
> reside on the same server.
> Third question:
> Is moving the 3 databases to 3 different servers and start using linked
> server the obvious best option to resolve the performance problem of our
> database server'
> At the moment we have one IIS-server, running ASP.NET and one database
> server, running SQL Server, using 3 databases.
> - dbingeven is mainly used to insert new rows
> - dblezen is mainly used to read rows (full text indexed)
> - dbalgemeen contains general data used by the other two (user data,
> parameters, statistical data, ...)
> Data is continuously inserted in dbingeven and continuously copied (after
> processing) to dblezen.
> All 3 databases contain stored procedures refering each other all the time
> (joins as in the query above as well as calling each others stored
> procedures).
> Fourth question:
> Is it predictable the gain of performance win (on CPU, and disk access)
> by spreading the data will be lost on network traffic and distributed
> transactions processes, meaning our problem will not be really solved'
>
> Thanks in Advance,
> Peter Van Wilrijk.
>
>
>
>
>|||ChrisR wrote:
> Why don't you replicate the data back to the server that you're query is r
un
> on, and select from there?
>
Thanks, Good question?
I surely must start checking out how to implement replication ... but I
guess it will not be an option for our website, because our users, while
surfing ... read, insert and update data in all three databases. I
guess this means we should replicate continuously in two directions, so
updates, deletes and inserts on server 2 must be immediately available
on server 1 and vice versa. Can replication do that?
Kind regards,
Peter Roothans.|||Yes. Look up Transactional Replication in BOL. You will want to use the
Immediate Updating option.
"Peter Van Wilrijk" <pro@.koopjeskrant.be> wrote in message
news:Tu9ue.126847$Xj7.7072901@.phobos.telenet-ops.be...
> ChrisR wrote:
> Thanks, Good question?
> I surely must start checking out how to implement replication ... but I
> guess it will not be an option for our website, because our users, while
> surfing ... read, insert and update data in all three databases. I guess
> this means we should replicate continuously in two directions, so updates,
> deletes and inserts on server 2 must be immediately available on server 1
> and vice versa. Can replication do that?
> Kind regards,
> Peter Roothans.
>|||Thanks ChrisR.
I just found out you can give 1 server multiple names as follows.
sp_addlinkedserver N'SRVDBI', ' ', N'SQLOLEDB', N'SRV-WEBDB'
sp_addlinkedserver N'SRVDBL', ' ', N'SQLOLEDB', N'SRV-WEBDB'
sp_addlinkedserver N'SRVDBA', ' ', N'SQLOLEDB', N'SRV-WEBDB'
So, this way I can move my database to any server without the need to
adapt all code. The only thing to do than, is to let refer the logical
name to another physical server.
I've read BOL concerning Transactional Replication. I'm certainly going
to try it out, but since I'm not familiair with it and since the
document warns for loopback detection when replicating multiple related
databases, I'll start with the linked server solution.
Thanks,
Kind regards,
Peter Van Wilrijk
ChrisR wrote:
> Yes. Look up Transactional Replication in BOL. You will want to use the
> Immediate Updating option.
>
> "Peter Van Wilrijk" <pro@.koopjeskrant.be> wrote in message
> news:Tu9ue.126847$Xj7.7072901@.phobos.telenet-ops.be...
>
>
>