nt5, sql2k
i have a vendor application on one of my sql servers.
this application has many tables, and each tables have many indexes. it's
very write intensive (because when a record is inserted, it's insereted to
many many other tables, and that behavior can't be changed). the problem is
page split happened often, but when i tried to reindex tables once a week,
the log file run out of space.
can anyone offers some good solutions to my problem?
thank you!!
Steve
You can try changing the recovery mode to Bulk Logged or Simple and see if
that helps. Do only a few tables at a time and backup the log in between.
Andrew J. Kelly SQL MVP
"Steve Lin" <lins@.nospam.portptld.com> wrote in message
news:#otAPXEGEHA.3288@.TK2MSFTNGP12.phx.gbl...
> nt5, sql2k
>
> i have a vendor application on one of my sql servers.
> this application has many tables, and each tables have many indexes. it's
> very write intensive (because when a record is inserted, it's insereted to
> many many other tables, and that behavior can't be changed). the problem
is
> page split happened often, but when i tried to reindex tables once a week,
> the log file run out of space.
>
> can anyone offers some good solutions to my problem?
> thank you!!
>
> Steve
>
|||as i said, the database is write intensive. so simple recovery mode might be
too risky. is there a way to run a scheduled re-index job as a non-logged
operation?
thank you so much for the help.
steve
"Andrew J. Kelly" <sqlmvpnoooospam@.shadhawk.com> wrote in message
news:ujUS9aFGEHA.2876@.TK2MSFTNGP09.phx.gbl...
> You can try changing the recovery mode to Bulk Logged or Simple and see if
> that helps. Do only a few tables at a time and backup the log in between.
>
> --
> Andrew J. Kelly SQL MVP
>
> "Steve Lin" <lins@.nospam.portptld.com> wrote in message
> news:#otAPXEGEHA.3288@.TK2MSFTNGP12.phx.gbl...
it's
to
> is
week,
>
|||Checkout DBCC INDEXDEFRAG, which does online defragmentation. You can look
in BOL for details. There's also an excellent whitepaper at
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
which will give you a bunch of details, hints, and explanations - including
working out whether you even need to bother getting rid of fragmentation,
based on your workload.
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Steve Lin" <lins@.nospam.portptld.com> wrote in message
news:eDkxfqPGEHA.3324@.TK2MSFTNGP09.phx.gbl...
> as i said, the database is write intensive. so simple recovery mode might
be
> too risky. is there a way to run a scheduled re-index job as a non-logged
> operation?
> thank you so much for the help.
> steve
> "Andrew J. Kelly" <sqlmvpnoooospam@.shadhawk.com> wrote in message
> news:ujUS9aFGEHA.2876@.TK2MSFTNGP09.phx.gbl...
if
between.
> it's
insereted
> to
problem
> week,
>
Showing posts with label tables. Show all posts
Showing posts with label tables. Show all posts
Friday, March 30, 2012
is it possible to re-build index without logging?
nt5, sql2k
i have a vendor application on one of my sql servers.
this application has many tables, and each tables have many indexes. it's
very write intensive (because when a record is inserted, it's insereted to
many many other tables, and that behavior can't be changed). the problem is
page split happened often, but when i tried to reindex tables once a week,
the log file run out of space.
can anyone offers some good solutions to my problem?
thank you!!
SteveYou can try changing the recovery mode to Bulk Logged or Simple and see if
that helps. Do only a few tables at a time and backup the log in between.
Andrew J. Kelly SQL MVP
"Steve Lin" <lins@.nospam.portptld.com> wrote in message
news:#otAPXEGEHA.3288@.TK2MSFTNGP12.phx.gbl...
> nt5, sql2k
>
> i have a vendor application on one of my sql servers.
> this application has many tables, and each tables have many indexes. it's
> very write intensive (because when a record is inserted, it's insereted to
> many many other tables, and that behavior can't be changed). the problem
is
> page split happened often, but when i tried to reindex tables once a week,
> the log file run out of space.
>
> can anyone offers some good solutions to my problem?
> thank you!!
>
> Steve
>|||as i said, the database is write intensive. so simple recovery mode might be
too risky. is there a way to run a scheduled re-index job as a non-logged
operation?
thank you so much for the help.
steve
"Andrew J. Kelly" <sqlmvpnoooospam@.shadhawk.com> wrote in message
news:ujUS9aFGEHA.2876@.TK2MSFTNGP09.phx.gbl...
> You can try changing the recovery mode to Bulk Logged or Simple and see if
> that helps. Do only a few tables at a time and backup the log in between.
>
> --
> Andrew J. Kelly SQL MVP
>
> "Steve Lin" <lins@.nospam.portptld.com> wrote in message
> news:#otAPXEGEHA.3288@.TK2MSFTNGP12.phx.gbl...
it's
to
> is
week,
>|||Checkout DBCC INDEXDEFRAG, which does online defragmentation. You can look
in BOL for details. There's also an excellent whitepaper at
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
which will give you a bunch of details, hints, and explanations - including
working out whether you even need to bother getting rid of fragmentation,
based on your workload.
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Steve Lin" <lins@.nospam.portptld.com> wrote in message
news:eDkxfqPGEHA.3324@.TK2MSFTNGP09.phx.gbl...
> as i said, the database is write intensive. so simple recovery mode might
be
> too risky. is there a way to run a scheduled re-index job as a non-logged
> operation?
> thank you so much for the help.
> steve
> "Andrew J. Kelly" <sqlmvpnoooospam@.shadhawk.com> wrote in message
> news:ujUS9aFGEHA.2876@.TK2MSFTNGP09.phx.gbl...
if
between.
> it's
insereted
> to
problem
> week,
>sql
i have a vendor application on one of my sql servers.
this application has many tables, and each tables have many indexes. it's
very write intensive (because when a record is inserted, it's insereted to
many many other tables, and that behavior can't be changed). the problem is
page split happened often, but when i tried to reindex tables once a week,
the log file run out of space.
can anyone offers some good solutions to my problem?
thank you!!
SteveYou can try changing the recovery mode to Bulk Logged or Simple and see if
that helps. Do only a few tables at a time and backup the log in between.
Andrew J. Kelly SQL MVP
"Steve Lin" <lins@.nospam.portptld.com> wrote in message
news:#otAPXEGEHA.3288@.TK2MSFTNGP12.phx.gbl...
> nt5, sql2k
>
> i have a vendor application on one of my sql servers.
> this application has many tables, and each tables have many indexes. it's
> very write intensive (because when a record is inserted, it's insereted to
> many many other tables, and that behavior can't be changed). the problem
is
> page split happened often, but when i tried to reindex tables once a week,
> the log file run out of space.
>
> can anyone offers some good solutions to my problem?
> thank you!!
>
> Steve
>|||as i said, the database is write intensive. so simple recovery mode might be
too risky. is there a way to run a scheduled re-index job as a non-logged
operation?
thank you so much for the help.
steve
"Andrew J. Kelly" <sqlmvpnoooospam@.shadhawk.com> wrote in message
news:ujUS9aFGEHA.2876@.TK2MSFTNGP09.phx.gbl...
> You can try changing the recovery mode to Bulk Logged or Simple and see if
> that helps. Do only a few tables at a time and backup the log in between.
>
> --
> Andrew J. Kelly SQL MVP
>
> "Steve Lin" <lins@.nospam.portptld.com> wrote in message
> news:#otAPXEGEHA.3288@.TK2MSFTNGP12.phx.gbl...
it's
to
> is
week,
>|||Checkout DBCC INDEXDEFRAG, which does online defragmentation. You can look
in BOL for details. There's also an excellent whitepaper at
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
which will give you a bunch of details, hints, and explanations - including
working out whether you even need to bother getting rid of fragmentation,
based on your workload.
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Steve Lin" <lins@.nospam.portptld.com> wrote in message
news:eDkxfqPGEHA.3324@.TK2MSFTNGP09.phx.gbl...
> as i said, the database is write intensive. so simple recovery mode might
be
> too risky. is there a way to run a scheduled re-index job as a non-logged
> operation?
> thank you so much for the help.
> steve
> "Andrew J. Kelly" <sqlmvpnoooospam@.shadhawk.com> wrote in message
> news:ujUS9aFGEHA.2876@.TK2MSFTNGP09.phx.gbl...
if
between.
> it's
insereted
> to
problem
> week,
>sql
Is it possible to read data from a table with exclusive lock ?
Hello,
We currently use SQL Serv 2000 with an ERP application and VB applications.
Both system work with the same database & tables but the ERP application seems to put exclusive lock on all tables it needs during processes. So our VB applications are not able to read these data and we receive TIMEOUT error.
What can I do ?
thank you
Nico
Is the ERP a third party app? If so, since it is the one doing the locking, you will need to talk to that vendor and see if they can/will do a more permissive lock.
Since that is 'unlikely to happen' (vendors are funny that way) You might increase the timeout on your connection object to see if waiting a few more seconds will allow the lock to clear.
Your next option is to add a transaction to the process and set the isolation level of the transaction to "readuncommited". This will allow you to 'by pass' the exclusive lock, but the RISK is that you may get obsolete (incorrect) data.
|||Hello,
Just to confirm you it's seems to work and this a great new for our users :)
Thank you very much for you help
regards
Nico
|||hmmm.. "Nico" & "Polleveys".. sounds like I should open up answers for ouzo 
Seriously, glad to help, would you please remember to mark a question as "answered" once you are satisfied. It will help others find answers, and allow answerers to move on to other unanswered questions.
Is it possible to read data from a table with exclusive lock ?
Hello,
We currently use SQL Serv 2000 with an ERP application and VB applications.
Both system work with the same database & tables but the ERP application seems to put exclusive lock on all tables it needs during processes. So our VB applications are not able to read these data and we receive TIMEOUT error.
What can I do ?
thank you
Nico
Is the ERP a third party app? If so, since it is the one doing the locking, you will need to talk to that vendor and see if they can/will do a more permissive lock.
Since that is 'unlikely to happen' (vendors are funny that way) You might increase the timeout on your connection object to see if waiting a few more seconds will allow the lock to clear.
Your next option is to add a transaction to the process and set the isolation level of the transaction to "readuncommited". This will allow you to 'by pass' the exclusive lock, but the RISK is that you may get obsolete (incorrect) data.
|||Hello,
Just to confirm you it's seems to work and this a great new for our users :)
Thank you very much for you help
regards
Nico
|||hmmm.. "Nico" & "Polleveys".. sounds like I should open up answers for ouzo
Seriously, glad to help, would you please remember to mark a question as "answered" once you are satisfied. It will help others find answers, and allow answerers to move on to other unanswered questions.
sqlWednesday, March 28, 2012
Is it possible to merge date from 2 or 3 tables into a View?
Suppose I have three tables Type1Events, Type2Events, and Type3Events where
each table has a DateTime column for the date and time the event occurs,
along with some data specific to the event. Can I pull all events from the
three tables into a single View so that the data appears as if it were one
single table, and use a where condition for the date range so that only
events in a certain date range for all three tables appear? So my output
might look like this
EventDate Type
=================
1/1/2001 1
1/2/2001 2
2/1/2003 3
... etc...
Not sure if this makes sense but. I really don't care about the data in the
tables for this example, only the ocurrances in dates ranges for all three.
thanks,
JIM
EventTables
CREATE TABLE dbo.Type1Events (
id int NOT NULL IDENTITY (1, 1),
eventDate datetime NOT NULL,
somedata text NOT NULL
) ON [PRIMARY]
TEXTIMAGE_ON [PRIMARY]
GO
CREATE TABLE dbo.Type2Events (
id int NOT NULL IDENTITY (1, 1),
eventDate datetime NOT NULL,
somedata text NOT NULL
) ON [PRIMARY]
TEXTIMAGE_ON [PRIMARY]
GO
CREATE TABLE dbo.Type3Events (
id int NOT NULL IDENTITY (1, 1),
eventDate datetime NOT NULL,
somedata text NOT NULL
) ON [PRIMARY]
TEXTIMAGE_ON [PRIMARY]
GO
INSERT INTO Type1Events ( eventDate, someData ) values ( '1/1/2001',
'data1' )
INSERT INTO Type1Events ( eventDate, someData ) values ( '1/1/2002',
'data2' )
INSERT INTO Type2Events ( eventDate, someData ) values ( '1/1/2003',
'data3' )
INSERT INTO Type2Events ( eventDate, someData ) values ( '2/1/2002',
'data4' )
INSERT INTO Type3Events ( eventDate, someData ) values ( '3/1/2001',
'data5' )
INSERT INTO Type3Events ( eventDate, someData ) values ( '4/1/2002',
'data6' )
INSERT INTO Type2Events ( eventDate, someData ) values ( '5/1/2006',
'data7' )
INSERT INTO Type1Events ( eventDate, someData ) values ( '6/1/2004',
'data8' )
INSERT INTO Type3Events ( eventDate, someData ) values ( '7/1/2005',
'data9' )
GOcreate view events
as
select eventDate, someData from Type1Events
union all
select eventDate, someData from Type2Events
union all
select eventDate, someData from Type3Events
go|||Sorry, a more complete solution is:
create view events
as
select 1 as eventType, eventDate, someData from Type1Events
union all
select 2, eventDate, someData from Type2Events
union all
select 3, eventDate, someData from Type3Events
go|||Jeff,
Thanks a lot!, I figured it should be easy but havn't used union much so...
thanks,
JIM
"JeffB" <jeff.bolton@.citigatehudson.com> wrote in message
news:1142016783.270339.21280@.u72g2000cwu.googlegroups.com...
> Sorry, a more complete solution is:
> create view events
> as
> select 1 as eventType, eventDate, someData from Type1Events
> union all
> select 2, eventDate, someData from Type2Events
> union all
> select 3, eventDate, someData from Type3Events
> go
>
each table has a DateTime column for the date and time the event occurs,
along with some data specific to the event. Can I pull all events from the
three tables into a single View so that the data appears as if it were one
single table, and use a where condition for the date range so that only
events in a certain date range for all three tables appear? So my output
might look like this
EventDate Type
=================
1/1/2001 1
1/2/2001 2
2/1/2003 3
... etc...
Not sure if this makes sense but. I really don't care about the data in the
tables for this example, only the ocurrances in dates ranges for all three.
thanks,
JIM
EventTables
CREATE TABLE dbo.Type1Events (
id int NOT NULL IDENTITY (1, 1),
eventDate datetime NOT NULL,
somedata text NOT NULL
) ON [PRIMARY]
TEXTIMAGE_ON [PRIMARY]
GO
CREATE TABLE dbo.Type2Events (
id int NOT NULL IDENTITY (1, 1),
eventDate datetime NOT NULL,
somedata text NOT NULL
) ON [PRIMARY]
TEXTIMAGE_ON [PRIMARY]
GO
CREATE TABLE dbo.Type3Events (
id int NOT NULL IDENTITY (1, 1),
eventDate datetime NOT NULL,
somedata text NOT NULL
) ON [PRIMARY]
TEXTIMAGE_ON [PRIMARY]
GO
INSERT INTO Type1Events ( eventDate, someData ) values ( '1/1/2001',
'data1' )
INSERT INTO Type1Events ( eventDate, someData ) values ( '1/1/2002',
'data2' )
INSERT INTO Type2Events ( eventDate, someData ) values ( '1/1/2003',
'data3' )
INSERT INTO Type2Events ( eventDate, someData ) values ( '2/1/2002',
'data4' )
INSERT INTO Type3Events ( eventDate, someData ) values ( '3/1/2001',
'data5' )
INSERT INTO Type3Events ( eventDate, someData ) values ( '4/1/2002',
'data6' )
INSERT INTO Type2Events ( eventDate, someData ) values ( '5/1/2006',
'data7' )
INSERT INTO Type1Events ( eventDate, someData ) values ( '6/1/2004',
'data8' )
INSERT INTO Type3Events ( eventDate, someData ) values ( '7/1/2005',
'data9' )
GOcreate view events
as
select eventDate, someData from Type1Events
union all
select eventDate, someData from Type2Events
union all
select eventDate, someData from Type3Events
go|||Sorry, a more complete solution is:
create view events
as
select 1 as eventType, eventDate, someData from Type1Events
union all
select 2, eventDate, someData from Type2Events
union all
select 3, eventDate, someData from Type3Events
go|||Jeff,
Thanks a lot!, I figured it should be easy but havn't used union much so...
thanks,
JIM
"JeffB" <jeff.bolton@.citigatehudson.com> wrote in message
news:1142016783.270339.21280@.u72g2000cwu.googlegroups.com...
> Sorry, a more complete solution is:
> create view events
> as
> select 1 as eventType, eventDate, someData from Type1Events
> union all
> select 2, eventDate, someData from Type2Events
> union all
> select 3, eventDate, someData from Type3Events
> go
>
Is it possible to make "proxy tables" win SQL-server 2000?
Hello
I am coming from Sybase and are learning SQL-Server now.
In Sybase there is a feature called "proxy table" which makes it possible to create a proxy table that really exists in another database. But it appears a ordinary local table to a user. Does a similar feature exists in SQL-server? Anyone knows?
Thanks
Per
Per,
No, but you can fully qualify the object in the other database to access
it. i.e.
select * from database2.dbo.objectname
You will need the appropriate permissions on the other object.
You may also wish to look at cross-database ownership chaining in the
updated Books online available from www.microsoft.com/sql
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Per wrote:
> Hello
> I am coming from Sybase and are learning SQL-Server now.
> In Sybase there is a feature called "proxy table" which makes it possible to create a proxy table that really exists in another database. But it appears a ordinary local table to a user. Does a similar feature exists in SQL-server? Anyone knows?
> Thanks
> Per
|||Hi
To add to Marks post...you can also create a view in the "current" database.
That would mean that the three part name is only require in the view
definition.
John
"Per" <anonymous@.discussions.microsoft.com> wrote in message
news:CD9FFDA6-BCE1-4D83-8960-1D612462424D@.microsoft.com...
> Hello
> I am coming from Sybase and are learning SQL-Server now.
> In Sybase there is a feature called "proxy table" which makes it possible
to create a proxy table that really exists in another database. But it
appears a ordinary local table to a user. Does a similar feature exists in
SQL-server? Anyone knows?
> Thanks
> Per
I am coming from Sybase and are learning SQL-Server now.
In Sybase there is a feature called "proxy table" which makes it possible to create a proxy table that really exists in another database. But it appears a ordinary local table to a user. Does a similar feature exists in SQL-server? Anyone knows?
Thanks
Per
Per,
No, but you can fully qualify the object in the other database to access
it. i.e.
select * from database2.dbo.objectname
You will need the appropriate permissions on the other object.
You may also wish to look at cross-database ownership chaining in the
updated Books online available from www.microsoft.com/sql
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Per wrote:
> Hello
> I am coming from Sybase and are learning SQL-Server now.
> In Sybase there is a feature called "proxy table" which makes it possible to create a proxy table that really exists in another database. But it appears a ordinary local table to a user. Does a similar feature exists in SQL-server? Anyone knows?
> Thanks
> Per
|||Hi
To add to Marks post...you can also create a view in the "current" database.
That would mean that the three part name is only require in the view
definition.
John
"Per" <anonymous@.discussions.microsoft.com> wrote in message
news:CD9FFDA6-BCE1-4D83-8960-1D612462424D@.microsoft.com...
> Hello
> I am coming from Sybase and are learning SQL-Server now.
> In Sybase there is a feature called "proxy table" which makes it possible
to create a proxy table that really exists in another database. But it
appears a ordinary local table to a user. Does a similar feature exists in
SQL-server? Anyone knows?
> Thanks
> Per
Is it possible to make "proxy tables" win SQL-server 2000?
Hello
I am coming from Sybase and are learning SQL-Server now.
In Sybase there is a feature called "proxy table" which makes it possible to
create a proxy table that really exists in another database. But it appears
a ordinary local table to a user. Does a similar feature exists in SQL-serv
er? Anyone knows?
Thanks
PerPer,
No, but you can fully qualify the object in the other database to access
it. i.e.
select * from database2.dbo.objectname
You will need the appropriate permissions on the other object.
You may also wish to look at cross-database ownership chaining in the
updated Books online available from www.microsoft.com/sql
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Per wrote:
> Hello
> I am coming from Sybase and are learning SQL-Server now.
> In Sybase there is a feature called "proxy table" which makes it possible
to create a proxy table that really exists in another database. But it appea
rs a ordinary local table to a user. Does a similar feature exists in SQL-se
rver? Anyone knows?
> Thanks
> Per|||Hi
To add to Marks post...you can also create a view in the "current" database.
That would mean that the three part name is only require in the view
definition.
John
"Per" <anonymous@.discussions.microsoft.com> wrote in message
news:CD9FFDA6-BCE1-4D83-8960-1D612462424D@.microsoft.com...
> Hello
> I am coming from Sybase and are learning SQL-Server now.
> In Sybase there is a feature called "proxy table" which makes it possible
to create a proxy table that really exists in another database. But it
appears a ordinary local table to a user. Does a similar feature exists in
SQL-server? Anyone knows?
> Thanks
> Per
I am coming from Sybase and are learning SQL-Server now.
In Sybase there is a feature called "proxy table" which makes it possible to
create a proxy table that really exists in another database. But it appears
a ordinary local table to a user. Does a similar feature exists in SQL-serv
er? Anyone knows?
Thanks
PerPer,
No, but you can fully qualify the object in the other database to access
it. i.e.
select * from database2.dbo.objectname
You will need the appropriate permissions on the other object.
You may also wish to look at cross-database ownership chaining in the
updated Books online available from www.microsoft.com/sql
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Per wrote:
> Hello
> I am coming from Sybase and are learning SQL-Server now.
> In Sybase there is a feature called "proxy table" which makes it possible
to create a proxy table that really exists in another database. But it appea
rs a ordinary local table to a user. Does a similar feature exists in SQL-se
rver? Anyone knows?
> Thanks
> Per|||Hi
To add to Marks post...you can also create a view in the "current" database.
That would mean that the three part name is only require in the view
definition.
John
"Per" <anonymous@.discussions.microsoft.com> wrote in message
news:CD9FFDA6-BCE1-4D83-8960-1D612462424D@.microsoft.com...
> Hello
> I am coming from Sybase and are learning SQL-Server now.
> In Sybase there is a feature called "proxy table" which makes it possible
to create a proxy table that really exists in another database. But it
appears a ordinary local table to a user. Does a similar feature exists in
SQL-server? Anyone knows?
> Thanks
> Per
is it possible to just restore particular tables from a back up of a database
Hi,
I backed my sql server database on Fri at 3:15pm and then tried importing some tables individually from a different database and but that didnt work out too good, so is there a way i can just restore particular tables from the backup.
any help will be appreciated.
regards,
Karen
AFAIK, you need to restore the db first to retrieve any information from the files.|||I don't think it is possible to just restore one table. What I have done in the past is to restore the backed up database with a new name and then copy the table from the backup to the operational.|||Restore database with different name and next copy data from tables you need to your destination
Thanks
|||thanks a lot for your answers i just restored the database to it previous state
Regards,
Karen
sqlMonday, March 26, 2012
Is it possible to generate alter Table statements using SMO
Hi
I'm trying to modify existing tables in a database.
How can I create alter Table scripts using SMO/DMO
Thank you
Yep, you can use the following to either execute and capture, just execute (which is the default) or just capture the executed commands:Server s = new Server(".");
s.ConnectionContext.SqlExecutionModes = Microsoft.SqlServer.Management.Common.SqlExecutionModes.CaptureSql
//Microsoft.SqlServer.Management.Common.SqlExecutionModes.CaptureSql
//Microsoft.SqlServer.Management.Common.SqlExecutionModes.ExecuteAndCaptureSql
//Microsoft.SqlServer.Management.Common.SqlExecutionModes.ExecuteSql
//s.ConnectionContext.CapturedSql.Text; //Get the Text
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
|||Thanks again Jens
I'm trying the follwong code
Server server1 = new Server(".");
Database db= server1.Databases["master"];
server1.ConnectionContext.SqlExecutionModes = SqlExecutionModes.CaptureSql;
foreach (Table Tbl in db.Tables)
{
tabl.Alter ();
}
db1.Refresh();
//writing to a file
writeToFile(server1.ConnectionContext.CapturedSql.Text, "alter", "tables");
But it is not generating Alter statments.
But if I use Create(), in place of alter(), it's generating Create statments.
|||Hi,
if you do not change anything, what are you supposed to see in the ALTER script :-) ?
In this sample I added a column to the table resulting in a script with an ALTER Script and an ADD column command.
Server s = new Server(".");
s.ConnectionContext.SqlExecutionModes = Microsoft.SqlServer.Management.Common.SqlExecutionModes.CaptureSql;
Table t = s.Databases["SMOTest"].Tables["TestTable"];
t.Columns.Add(new Column(t,"SomeSMOTest",DataType.DateTime));
t.Alter();
foreach (string st in s.ConnectionContext.CapturedSql.Text)
{
Console.WriteLine(st);
}
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
|||Thanks Jens
Got it.
while comparing a table in one database to other table (identical) in other database,
if the Source table has some modified(altered) columns and need to be modified in the target table.
How to solve this problem.Any Idea.
I generated the alter scripts manually for each column.
Like
ALTER TABLE [dbo].[wo]
ADD [requested-time] varchar (8 ) NULL
Thank you
|||You will have to do this manually. Load the two schemas and compare the columns (if you just want to check the columns) with each other. Change the columns appropiately with SMO and get the script from the Context. if you want an integrated tool which can do this on its own use Visual Studio for database professionals, this does have a comparer and script generator for keeping the databases in sync.HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de|||
Thanks alot Jens.
I'll try for this
Friday, March 23, 2012
is it possible to define in sql server linked tables as in access?
is it possible to define in sql server linked tables to odbc data sources as it is possible to do in access?
except Access and vfp, is there another database that allows to create linked tables to odbc data sources?
I need to combine in a database native tables with odbc linked tables, but it would need to use a database more powerful that access or vfpYes, see sp_addlinkedserver (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_adda_8gqa.asp) for ideas.
-PatP|||is it possible to define in sql server linked tables to odbc data sources as it is possible to do in access?
except Access and vfp, is there another database that allows to create linked tables to odbc data sources?
I need to combine in a database native tables with odbc linked tables, but it would need to use a database more powerful that access or vfp
You can use SQL Linked servers to achieve your goals. A Linked Server can be any ODBC-compliant database for which there are adequate drivers.
I would advise a bit of caution regarding linked servers; they work well, but use complex queries with caution as you can't always predict with certainty where the processing will occur (especially when there are joins and subqueries involved).
Regards,
hmscottsql
except Access and vfp, is there another database that allows to create linked tables to odbc data sources?
I need to combine in a database native tables with odbc linked tables, but it would need to use a database more powerful that access or vfpYes, see sp_addlinkedserver (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_adda_8gqa.asp) for ideas.
-PatP|||is it possible to define in sql server linked tables to odbc data sources as it is possible to do in access?
except Access and vfp, is there another database that allows to create linked tables to odbc data sources?
I need to combine in a database native tables with odbc linked tables, but it would need to use a database more powerful that access or vfp
You can use SQL Linked servers to achieve your goals. A Linked Server can be any ODBC-compliant database for which there are adequate drivers.
I would advise a bit of caution regarding linked servers; they work well, but use complex queries with caution as you can't always predict with certainty where the processing will occur (especially when there are joins and subqueries involved).
Regards,
hmscottsql
Wednesday, March 21, 2012
Is it possible to create clustered index for multi tables within a SQL Server 2005 database with
Hi, all,
I am having up to serveral hundred tables within a SQL Server 2005 database, up to 200 of them are without any clustered index. Is it possible to create clustered indexes for all of them together in a same query? :)
Thanks a lot for any guidance and advices for that.
With best regards,
Yours sincerely,
Yes, it is possible with WHILE loop and dynamic SQL (with eithersp_executesql or EXEC statement) that calls CREATE INDEX. However, the tricky part is you have to specify the column(s) for each table to be included in the index in the CREATE INDEX statement. So if you have a way to programmatically get the table names and the index column(s) for each table, it is easy to do the rest.|||Hi, Hugh Qu,
Thank you very much for your kind guidance and advices. Got the ideas, very appreciated.
With best regards,
Yours sincerely,
Is it possible to create clustered index for multi tables within a SQL Server 2005 database with
Hi, all,
I am having up to serveral hundred tables within a SQL Server 2005 database, up to 200 of them are without any clustered index. Is it possible to create clustered indexes for all of them together in a same query? :)
Thanks a lot for any guidance and advices for that.
With best regards,
Yours sincerely,
Yes, it is possible with WHILE loop and dynamic SQL (with eithersp_executesql or EXEC statement) that calls CREATE INDEX. However, the tricky part is you have to specify the column(s) for each table to be included in the index in the CREATE INDEX statement. So if you have a way to programmatically get the table names and the index column(s) for each table, it is easy to do the rest.|||Hi, Hugh Qu,
Thank you very much for your kind guidance and advices. Got the ideas, very appreciated.
With best regards,
Yours sincerely,
Is it possible to create clustered index for multi tables within a SQL Server 2005 database
Hi, all,
I am having up to serveral hundred tables within a SQL Server 2005 database, up to 200 of them are without any clustered index. Is it possible to create clustered indexes for all of them together in a same query? :)
Thanks a lot for any guidance and advices for that.
With best regards,
Yours sincerely,
Yes, it is possible with WHILE loop and dynamic SQL (with either sp_executesql or EXEC statement) that calls CREATE INDEX. However, the tricky part is you have to specify the column(s) for each table to be included in the index in the CREATE INDEX statement. So if you have a way to programmatically get the table names and the index column(s) for each table, it is easy to do the rest.|||Hi, Hugh Qu,
Thank you very much for your kind guidance and advices. Got the ideas, very appreciated.
With best regards,
Yours sincerely,
Monday, March 19, 2012
Is it possible to alter snapshot agent to not drop tables?
We've got transactional replication set up from 2000 to 05. Is there a
way to not let the snapshot agent to drop and recreate the destination
tables, but rather truncate them?
Thank you,
Igor
*** Sent via Developersdex http://www.codecomments.com ***
Have a look at the article properties (@.pre_creation_cmd ). By default it is
Drop, but it can be converted to delete or truncate (or none).
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Right ON!
Thank you.
*** Sent via Developersdex http://www.codecomments.com ***
way to not let the snapshot agent to drop and recreate the destination
tables, but rather truncate them?
Thank you,
Igor
*** Sent via Developersdex http://www.codecomments.com ***
Have a look at the article properties (@.pre_creation_cmd ). By default it is
Drop, but it can be converted to delete or truncate (or none).
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Right ON!
Thank you.
*** Sent via Developersdex http://www.codecomments.com ***
Wednesday, March 7, 2012
Is it a complex sql query?
Hello
I am using stored procedure with sql 2005 (with Visual studio 2005)
I have two tables .. TABLE1 And TABLE2
>From TABLE1 i need to retrive the OrderID's of the 4 most top rows. so
i did:
SELECT TOP 4 OrderID FROM TABLE1 order by OrderID desc
Now what i am trying to do is take the 4 row results (4 OrderID's) i
got from
TABLE1 and check if the 4 rows (4 OrderID's) exist in TABLE2 for a
specific
userID i get by INPUT varible (@.UserId)..
What i want to return is only which OrderID'S existed in TABLE2 for the
specific user.
If only 2 OrderID'S i retrived from TABLE1 exist in TABLE2 i will
return only 2 OrderID's (so i can do my output in visual studio 2005
using the reader())
I would appreciate this if anyone knows how to do this sql query , is
it possible to do this in 1 query? i want to put it in a stored
procedure.I tried to use this query-
SELECT TOP 4 OrderID FROM TABLE1 WHERE exists (SELECT * From TABLE2
where @.UserId=TABLE2.UserID)
But this query shows me the all 4 OrderID's if it finds the USERID in
TABLE2..
What i want to return is only which OrderID'S existes in TABLE2 for the
specific user.
if i have in TABLE1:
OrderID
1
2
3
4
TABLE2:
OrderID UserId
1 1001
2 1002
I want it to return only "2" if the INPUT Parameter of @.UserID is 1002|||Another example..
if i have in TABLE1:
OrderID
1
2
3
4
TABLE2:
OrderID UserId
1 1001
2 1002
3 1002
I want it to return only "2" and "3" if the INPUT Parameter of @.UserID
is 1002|||Hi
Check out http://www.aspfaq.com/etiquette.asp?id=5006 on how to post DDL and
sample data in a usable form
You can use something like:
SELECT TOP 4 t1.OrderID
FROM TABLE1 t1
WHERE exists (SELECT * FROM TABLE2 t2
WHERE @.UserId=T2.UserId
AND T1.OrderId = T2.OrderId )
Or (better!)
SELECT TOP 4 t1.OrderID
FROM TABLE1 t1
JOIN TABLE2 t2 ON T1.OrderId = T2.OrderId AND @.UserId=T2.UserId
Check out the topics "Using Joins" and "Join Fundamentals" in books online
John
<stockblaster@.gmail.com> wrote in message
news:1137312110.273304.240990@.g47g2000cwa.googlegr oups.com...
> Another example..
> if i have in TABLE1:
> OrderID
> 1
> 2
> 3
> 4
>
> TABLE2:
> OrderID UserId
> 1 1001
> 2 1002
> 3 1002
>
> I want it to return only "2" and "3" if the INPUT Parameter of @.UserID
> is 1002|||Excellent!
Thanks a lot John, it seems to work just fine.. i used the second
example.|||Hello again
Finally i decieded to use SELECT TOP 4 t1.OrderID
FROM TABLE1 t1
WHERE exists (SELECT * FROM TABLE2 t2
WHERE @.UserId=T2.UserId
AND T1.OrderId = T2.OrderId )
and modifed it to:
SELECT TOP 4 t1.OrderID
FROM TABLE1 t1
WHERE NOT exists (SELECT * FROM TABLE2 t2
WHERE @.UserId=T2.UserId
AND T1.OrderId = T2.OrderId )
(Notice the "NOT")
Because i wanted it to return me the OrderID's (from the top 4 of
course) that does not exist in TABLE2 ..
I couldn't do it with the JOIN thingy even if i changed OrderId <>
T2.OrderId ..|||I tried to find this in the documents on the web ..I couldn't find a
way of how to perform this only for the TOP 4 of TABLE1.
Now what happenes:
stockblas...@.gmail.com
Jan 15, 10:01 am show options
Newsgroups: comp.databases.ms-sqlserver
From: stockblas...@.gmail.com - Find messages by this author
Date: 15 Jan 2006 00:01:50 -0800
Local: Sun, Jan 15 2006 10:01 am
Subject: Re: Is it a complex sql query?
Reply | Reply to Author | Forward | Print | Individual Message | Show
original | Remove | Report Abuse
Another example..
TABLE1:
OrderID
1
2
3
4
5
6
7
8
9
10
TABLE2:
OrderID UserId
1 1001
2 1002
3 1002
It will return me: 4 5 6 7 (the top 4 of what it finds)
i use now:
SELECT TOP 4 t1.OrderID
FROM TABLE1 t1
WHERE NOT exists (SELECT * FROM TABLE2 t2
WHERE @.UserId=T2.UserId
AND T1.OrderId = T2.OrderId )
Any ideas?|||Hi
I should have said that TOP without and ORDER BY clause is a bit
meaningless.
SELECT TOP 4 t1.OrderID
FROM TABLE1 t1
WHERE NOT EXISTS (SELECT * FROM TABLE2 t2
WHERE @.UserId=T2.UserId
AND T1.OrderId = T2.OrderId )
ORDER BY t1.OrderID
Will return you all rows OrderIds from Table1 where a row in Table2 does not
exist for that OrderId AND has a UserId of @.UserId. With the ORDER BY means
1, 4, 5 and 6 are returned.
To do this using a JOIN, an OUTER JOIN is required.
SELECT TOP 4 t1.OrderID
FROM TABLE1 t1
LEFT JOIN TABLE2 t2 ON t1.OrderId = t2.OrderId AND @.UserId = t2.UserId
WHERE t2.OrderID IS NULL
ORDER BY t1.OrderID
John
<stockblaster@.gmail.com> wrote in message
news:1137325768.003889.45140@.g47g2000cwa.googlegro ups.com...
> Hello again
> Finally i decieded to use SELECT TOP 4 t1.OrderID
> FROM TABLE1 t1
> WHERE exists (SELECT * FROM TABLE2 t2
> WHERE @.UserId=T2.UserId
> AND T1.OrderId = T2.OrderId )
> and modifed it to:
> SELECT TOP 4 t1.OrderID
> FROM TABLE1 t1
> WHERE NOT exists (SELECT * FROM TABLE2 t2
> WHERE @.UserId=T2.UserId
> AND T1.OrderId = T2.OrderId )
> (Notice the "NOT")
> Because i wanted it to return me the OrderID's (from the top 4 of
> course) that does not exist in TABLE2 ..
> I couldn't do it with the JOIN thingy even if i changed OrderId <>
> T2.OrderId ..|||Hello
I am sorry, i didn't explain my self what i wanted to acchive exactly.
For this query:
SELECT TOP 4 t1.OrderID
FROM TABLE1 t1
LEFT JOIN TABLE2 t2 ON t1.OrderId = t2.OrderId AND @.UserId = t2.UserId
WHERE t2.OrderID IS NULL
ORDER BY t1.OrderID DESC
There is a problem with that..
For example:
Table1:
OrderID
1
2
3
4
5
6
7
8
Table2:
OrderID UserID
6 1001
7 1001
3 1002
4 1002
the result will be:
for user 1001
8
5
4
3
I only need to get 8 and 5 which are the two orderID's the user didn't
have from the top 4 in table 1 ..
can't figure that out :(|||On 15 Jan 2006 10:09:18 -0800, stockblaster@.gmail.com wrote:
>Hello
>I am sorry, i didn't explain my self what i wanted to acchive exactly.
(snip)
Hi Stockblaster,
That's exactly the reason why John suggested you to read the information
at www.aspfaq.com/5006 in his first post to you - posting CREATE TABLE
and INSERT statements and expected output is a much better way to
explain your needs than pure narrative.
If I understand your requirements correctly, then maybe something like
this will work:
SELECT t1.OrderId
FROM (SELECT TOP 4 OrderID
FROM Table1
ORDER BY OrderID DESC) AS t1
LEFT JOIN Table2 AS t2
ON t2.OrderId = t1.OrderId
AND t2.UserId = @.UserId
WHERE t2.OrderID IS NULL
(untested - see www.aspfafq.com/5006 if you prefer a tested reply)
--
Hugo Kornelis, SQL Server MVP|||Hello Hugo..
Very nice! i believe this 1 did the work..
Thanks a lot.. this 1 was stiff.
Is there any good book you can recommend me for sql 2005 (with SQL
Server Management Studio) ... How to upload to a shared web hosting,
when to use relationships, some sql querys examples? all the basics.|||Hi
I am not sure if my interpretation is the same as Hugos!
If all 4 rows returned are below the maximum what should happen?
SELECT TOP 4 t1a.OrderID
FROM TABLE1 t1a
LEFT JOIN TABLE2 t2a ON t1.OrderId = t2a.OrderId AND @.UserId =
t2a.UserId
WHERE t2.OrderID IS NULL
AND t1a.OrderID > ( SELECT MAX(t1b.OrderID) FROM TABLE1 t1b
JOIN TABLE2 t2b ON t1b.OrderId = t2b.OrderId AND @.UserId = t2b.UserId )
ORDER BY t1a.OrderID DESC
John
stockblaster@.gmail.com wrote:
> Hello
> I am sorry, i didn't explain my self what i wanted to acchive exactly.
> For this query:
> SELECT TOP 4 t1.OrderID
> FROM TABLE1 t1
> LEFT JOIN TABLE2 t2 ON t1.OrderId = t2.OrderId AND @.UserId = t2.UserId
> WHERE t2.OrderID IS NULL
> ORDER BY t1.OrderID DESC
>
> There is a problem with that..
> For example:
> Table1:
> OrderID
> 1
> 2
> 3
> 4
> 5
> 6
> 7
> 8
> Table2:
> OrderID UserID
> 6 1001
> 7 1001
> 3 1002
> 4 1002
> the result will be:
> for user 1001
> 8
> 5
> 4
> 3
>
> I only need to get 8 and 5 which are the two orderID's the user didn't
> have from the top 4 in table 1 ..
> can't figure that out :(|||Hi
I don't think you will get a single books to cover all these topics,
and you will have to be careful of books based on the pre-release
versions. You may want to check out THe Microsoft SQL Server 2005
Administrator's Pocket Consultant ISDN 0735621071 for configuration
information, and there is always books online. Also check out SQL
Server magazine which has many articles that will be benificial
http://www.windowsitpro.com/SQLServer/
John|||On 15 Jan 2006 15:27:13 -0800, stockblaster@.gmail.com wrote:
>Hello Hugo..
>Very nice! i believe this 1 did the work..
>Thanks a lot.. this 1 was stiff.
>Is there any good book you can recommend me for sql 2005 (with SQL
>Server Management Studio) ... How to upload to a shared web hosting,
>when to use relationships, some sql querys examples? all the basics.
Hi Stockblaster,
I'm sorry, I can't help you here.
Personally, I'm going to wait for Inside SQL Server 2005, that Kalen
Delaney is (hopefully) working on right now. However, the "Inside..."
series are "how does it work" kind of books; you seem to be seeking the
"how do I operate it" kind of books.
--
Hugo Kornelis, SQL Server MVP|||Hello John.
I am not sure i understand, do you mean if table1 contains only two
records? so the top 4 will not work?|||Hi
Sorry for the delayed reply, this one slipped through the net.
My question was related to
There is a problem with that..
For example:
Table1:
OrderID
1
2
3
4
5
6
7
8
Table2:
OrderID UserID
6 1001
7 1001
3 1002
4 1002
the result will be:
for user 1001
8
5
4
3
Do you actually want 3,4,5 as this is less than the maximum for 1001 which
is already 7?
John
<stockblaster@.gmail.com> wrote in message
news:1137449155.173528.136550@.g14g2000cwa.googlegr oups.com...
> Hello John.
> I am not sure i understand, do you mean if table1 contains only two
> records? so the top 4 will not work?
is it a bug ? (relationships in report builder)
Hi friends
am having some weird problem.am using sql server 2005 standard edition.
does it mean that even if some one adds a field from tab3 accidentally ,he has to create the whole report from scratch so that he can see data that matches its criteria ?|||Bob
I think ,i kind of achieved what i want. please advise if i go into any problems in future.
am having some weird problem.am using sql server 2005 standard edition.
i've a report model with tables (tab1,tab2,tab3). tab1 and tab2 (actually these are views) relate to same table but each has different columns from same table.
the third table i.e. tab3 is child of tab1.
in my report model project,i set cardinality of this role in tab3 as "one" and in tab1 as "Optionalmany".
when creating a report in reportbuilder.if select columns from either tab1 and/or tab2 i get to see 100 records which is correct.if add any column from tab3 i get to see only 1 record which also correct as i've only 1 row tab3 at the moment.
now ,fun begins , what should happen if i delete column(s) of tab3 from report designer ? i should see all 100 records ,right ? bcoz all my columns coming from either tab1 or tab3 but when i run report i get see only 1 record !!! why ? is it a bug or am i missing something.
Thanks for your help.no one ever faced this situation ?
any ideas on this one much appreciated.|||
The difference between the first report that returned 100 rows and the third report that returned 1 row is the primary entity, which changed when you added a column from tab3, but did not revert when you deleted that column.
The primary entity of the first report is tab1, which means that report is fundamentally about tab1 and the data related to it.
The primary entity of the third report is tab3, which means that report is fundamentally about tab3 and the data related to it. Basically, the third report is a summary report for the data in tab3, grouped by tab1, which explains why there is only one row.
|||Thanks for the reply Bob.does it mean that even if some one adds a field from tab3 accidentally ,he has to create the whole report from scratch so that he can see data that matches its criteria ?|||Bob
I think ,i kind of achieved what i want. please advise if i go into any problems in future.
what i did was i set cardinality for the roles on parent table side as "optionalone" and child entity side as "Optionalmany". (normally its other way round ,right?)
now my reports work just fine. i mean i get data am expecting and top of it if i remove the field from tab3 still my report worked displaying all records from tab1,tab2.
BTW when i deploy (using BI) i get a warning like below
"The Relation property of the Role 'tab3 detail' refers to the Target end of the Relation 'dd_tab1-dd_tab3', which is not bound to a set of uniquely constrained columns for the Table 'dbo.dd__tab3'. Roles with Cardinality of One or OptionalOne require relations bound to uniquely constrained columns of the table."
is it a problem ?
i checked sql the report builder making (in sql profiler) and they look fine as its placing joins correctly on both parent and child tables.
any suggestions on this much appreciated.
Thank you very much|||
No, you should not swap the cardinality of your report model roles to get different joins. RB relies on this information in many ways to provide a consistent and appropriately constrained query design experience to the user.
You are right that there is currently no way to revert the primary entity other than rebuilding your report. This feature was slated for SQL 2005 at one point, but unfortunately did not make it into this release.
Is is necessary for a table which will be part of the Replication process to have a and Primary
Hi there,
We're going to use replication on our database. There are tables without any primary key.
Is a primary key necessary for replication?
I have also antoher question: We want to replicate the whole OLTP database (size 30 GB).
Is snapshot replication a suitable mechanisme for this? I thougt snapshot replication overwrites all of the data, not only changed data. Or should we use another replication method?
Thanks in advance,
Patrick de Jong
A primary key is necessary to replicate an article so that a unique record can always be found. Snapshotting a 30gb everytime might not be such a great idea. Transactional is probably a better way to go, depending on your situation.Tim|||
Yes you need to have a primary key on each table for replication.
Depending on how often your data changes and how frequently you need to have the subscriber updated is the best way to decide if snapshot replication is the best method.
Typically snapshot is not the best option unless you have a very high data change rate and you only need the subscriber updated once or twice per day (or less frequently).
|||Thanks for the answer's. The really helped me.
1.) My customer has te deside on which fields the pk must come.
2.) I think we will use transactional replication.
Greetz,
Patrick de Jong
|||Hi Friends,
1.Yes a table involved in Transactional replication should have a primary key defined.Also if u hav opted for transactional publication with updating subscriber,a unique identifier is required for a table.
2.Since it is a OLTP Database it keeps changing frequently,my suggestion would be to go for Transactional Replication.
Always prefer Snapshot replication for datas which do not change frequently (OLAP environment)
Rgds
Deepak
|||Thanks Deepak,
Today we've made a list of tables without a PK. It seems to be no problem to add the missing PK's because the tabels had an identity which was used as some sort of PK. Tomorrow we will test transactional replication.
Greetz,
Patrick de Jong
Friday, February 24, 2012
is Exists faster than a Join
From http://www.sql-server-performance.co...sis_tuning.asp
B) Can standard JOIN's be replaced with EXISTS statements?
If you need to join tables to ensure XYZ value exists or a specific set of
rows exists, then considering porting to EXISTS or NOT EXISTS statements.
At the same time is may also alleviate the need for DISTINCT clauses.
So is a Join really potentiall slower?
The Exists clause can be faster than a join because it just searches long
enough to find the first "Hit" as opposed to returning all rows that meet
the join criteria.
Greg Jackson
PDX, Oregon
|||Could you please elaborate? An Exists will meet all the rows that meet the
query criteria. Given that the criteria should return the same data as a
join, how could it be faster?
"pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
news:eZWWYB7aFHA.3848@.TK2MSFTNGP10.phx.gbl...
> The Exists clause can be faster than a join because it just searches long
> enough to find the first "Hit" as opposed to returning all rows that meet
> the join criteria.
>
> Greg Jackson
> PDX, Oregon
>
|||I say
SELECT * FROM Main
WHERE Exists(SELECT 1 FROM MyJoinTable a WHERE a.foo = main.foo)
then it will only check for the first hit.
still probably not making sense.
show us YOUR query and I can probably explain a bit better
GAJ
|||create table #Table1(T1C1 int identity (1,1), T1C2 char(10))
create table #Table2(T2C1 int, T2C2 char(10))
insert into #Table1 (T1C2) values ('T1C2')
insert into #Table1 (T1C2) values ('T1C2-2')
insert into #Table2 (T2C1,T2C2) values (1,'T1C2')
select t1.T1C1,T1C2
from #Table1 t1
inner join #Table2 t2 on t1.t1c1 = t2.t2c1
or
select t1.T1C1,T1C2
from #table1 t1
where exists
(select * from #Table2 t2
where t2.T2C1 = T1.T1C1)
"pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
news:%23fZBCd7aFHA.464@.TK2MSFTNGP15.phx.gbl...
>I say
> SELECT * FROM Main
> WHERE Exists(SELECT 1 FROM MyJoinTable a WHERE a.foo = main.foo)
> then it will only check for the first hit.
> still probably not making sense.
> show us YOUR query and I can probably explain a bit better
>
> GAJ
>
|||that's faster than a Join.
the where clause will return TRUE as soon as it finds a SINGLE record in
TABLE 2 that mathces Table1 (It will not bother returning more than 1 record
that matches to table 1)
A join will return ALL Records in T2 that match T1. IF there are millions of
records in each table, this would be exponentially faster.
Greg Jackson
PDX, Oregon
|||On Tue, 7 Jun 2005 15:11:21 -0700, ChrisR wrote:
>From http://www.sql-server-performance.co...sis_tuning.asp
>B) Can standard JOIN's be replaced with EXISTS statements?
>If you need to join tables to ensure XYZ value exists or a specific set of
>rows exists, then considering porting to EXISTS or NOT EXISTS statements.
>At the same time is may also alleviate the need for DISTINCT clauses.
>
>So is a Join really potentiall slower?
Hi Chris,
The question is actually irrelevant. A join is different: a query such
as
SELECT a.*
FROM a
WHERE EXISTS (SELECT * FROM b WHERE b.xx = a.xx)
will never return more than one copy of each row in a. Using a join
instead, such as this:
SELECT a.*
FROM a
JOIN b ON b.xx = a.xx
might return multiple copies of rows in a for values of xx that are not
unique in table b.
If no value of xx is ever duplicated in b, then the results will be the
same, but the EXISTS version might still be faster. Since SQL Server
doesn't know that there is only one row in b with a particular value of
xx, it still has to search the rest of the table after encoutering one;
if EXISTS is used, the search is aborted after the first match.
If SQL Server knows that values of b.xx are unique (because there is a
PRIMARY KEY or UNIQUE constraint on b.xx), then the performance MIGHT be
the same - the optimizer MIGHT decide to use the knowledge of this
constraint and createt the same execution plan it'll also use for the
EXISTS version.
I have never seen or heard of any situation where the JOIN would be
fafster than the "equivalent" EXISTS version. And I don't think I ever
will.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Hi Hugo and Chris,
"Hugo Kornelis" wrote:
> On Tue, 7 Jun 2005 15:11:21 -0700, ChrisR wrote:
>
> Hi Chris,
> The question is actually irrelevant. A join is different: a query such
> as
> SELECT a.*
> FROM a
> WHERE EXISTS (SELECT * FROM b WHERE b.xx = a.xx)
> will never return more than one copy of each row in a. Using a join
> instead, such as this:
> SELECT a.*
> FROM a
> JOIN b ON b.xx = a.xx
> might return multiple copies of rows in a for values of xx that are not
> unique in table b.
> If no value of xx is ever duplicated in b, then the results will be the
> same, but the EXISTS version might still be faster. Since SQL Server
> doesn't know that there is only one row in b with a particular value of
> xx, it still has to search the rest of the table after encoutering one;
> if EXISTS is used, the search is aborted after the first match.
> If SQL Server knows that values of b.xx are unique (because there is a
> PRIMARY KEY or UNIQUE constraint on b.xx), then the performance MIGHT be
> the same - the optimizer MIGHT decide to use the knowledge of this
> constraint and createt the same execution plan it'll also use for the
> EXISTS version.
> I have never seen or heard of any situation where the JOIN would be
> fafster than the "equivalent" EXISTS version. And I don't think I ever
> will.
>
I think you have to try the statement both ways.
I'm not sure about EXISTS, but I have a fairly complex multi-way join that
is much faster with
a LEFT JOIN b on a.id = b.id ... WHERE b.id is null
than with
WHERE NOT EXISTS (select 1 from b where a.id = b.id)
Regards,
Jonathan
|||On Thu, 9 Jun 2005 12:52:03 -0700, Jonathan Levine wrote:
(snip)
>I'm not sure about EXISTS, but I have a fairly complex multi-way join that
>is much faster with
>a LEFT JOIN b on a.id = b.id ... WHERE b.id is null
>than with
>WHERE NOT EXISTS (select 1 from b where a.id = b.id)
Hi Jonathan,
Thanks for adding this. This is not the first time I witness or hear
reports of this either. It seems that the SQL Server development team
put most effort in the optimization of join strategies, since they are
used most often in typical queries.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Also, be careful of the Optimizer Estimated Execution Plans. I especially
see this in comparisons between LEFT OUTER JOINS versus the use of EXISTS or
NOT EXISTS.
Even though the JOIN may produce a better ESTIMATED execution cost, it will
often create a HASH table for the JOIN, where the EXISTS solution will often
use a Nested Loop JOIN. The HASH will ALWAYS create temporary object, which
will have physical impact that the Optimizer does not take into account.
You're best bet would be to SET STATISTICS time and I/O on and run them both
ways, making sure to flush the Buffer Pool before each execution. This will
give you ACTUAL run information, not just ESTIMATED.
Sincerely,
Anthony Thomas
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:mnfha11l8fukurbcocmi9rg30s47q0lj6j@.4ax.com...
On Thu, 9 Jun 2005 12:52:03 -0700, Jonathan Levine wrote:
(snip)
>I'm not sure about EXISTS, but I have a fairly complex multi-way join that
>is much faster with
>a LEFT JOIN b on a.id = b.id ... WHERE b.id is null
>than with
>WHERE NOT EXISTS (select 1 from b where a.id = b.id)
Hi Jonathan,
Thanks for adding this. This is not the first time I witness or hear
reports of this either. It seems that the SQL Server development team
put most effort in the optimization of join strategies, since they are
used most often in typical queries.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
B) Can standard JOIN's be replaced with EXISTS statements?
If you need to join tables to ensure XYZ value exists or a specific set of
rows exists, then considering porting to EXISTS or NOT EXISTS statements.
At the same time is may also alleviate the need for DISTINCT clauses.
So is a Join really potentiall slower?
The Exists clause can be faster than a join because it just searches long
enough to find the first "Hit" as opposed to returning all rows that meet
the join criteria.
Greg Jackson
PDX, Oregon
|||Could you please elaborate? An Exists will meet all the rows that meet the
query criteria. Given that the criteria should return the same data as a
join, how could it be faster?
"pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
news:eZWWYB7aFHA.3848@.TK2MSFTNGP10.phx.gbl...
> The Exists clause can be faster than a join because it just searches long
> enough to find the first "Hit" as opposed to returning all rows that meet
> the join criteria.
>
> Greg Jackson
> PDX, Oregon
>
|||I say
SELECT * FROM Main
WHERE Exists(SELECT 1 FROM MyJoinTable a WHERE a.foo = main.foo)
then it will only check for the first hit.
still probably not making sense.
show us YOUR query and I can probably explain a bit better
GAJ
|||create table #Table1(T1C1 int identity (1,1), T1C2 char(10))
create table #Table2(T2C1 int, T2C2 char(10))
insert into #Table1 (T1C2) values ('T1C2')
insert into #Table1 (T1C2) values ('T1C2-2')
insert into #Table2 (T2C1,T2C2) values (1,'T1C2')
select t1.T1C1,T1C2
from #Table1 t1
inner join #Table2 t2 on t1.t1c1 = t2.t2c1
or
select t1.T1C1,T1C2
from #table1 t1
where exists
(select * from #Table2 t2
where t2.T2C1 = T1.T1C1)
"pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
news:%23fZBCd7aFHA.464@.TK2MSFTNGP15.phx.gbl...
>I say
> SELECT * FROM Main
> WHERE Exists(SELECT 1 FROM MyJoinTable a WHERE a.foo = main.foo)
> then it will only check for the first hit.
> still probably not making sense.
> show us YOUR query and I can probably explain a bit better
>
> GAJ
>
|||that's faster than a Join.
the where clause will return TRUE as soon as it finds a SINGLE record in
TABLE 2 that mathces Table1 (It will not bother returning more than 1 record
that matches to table 1)
A join will return ALL Records in T2 that match T1. IF there are millions of
records in each table, this would be exponentially faster.
Greg Jackson
PDX, Oregon
|||On Tue, 7 Jun 2005 15:11:21 -0700, ChrisR wrote:
>From http://www.sql-server-performance.co...sis_tuning.asp
>B) Can standard JOIN's be replaced with EXISTS statements?
>If you need to join tables to ensure XYZ value exists or a specific set of
>rows exists, then considering porting to EXISTS or NOT EXISTS statements.
>At the same time is may also alleviate the need for DISTINCT clauses.
>
>So is a Join really potentiall slower?
Hi Chris,
The question is actually irrelevant. A join is different: a query such
as
SELECT a.*
FROM a
WHERE EXISTS (SELECT * FROM b WHERE b.xx = a.xx)
will never return more than one copy of each row in a. Using a join
instead, such as this:
SELECT a.*
FROM a
JOIN b ON b.xx = a.xx
might return multiple copies of rows in a for values of xx that are not
unique in table b.
If no value of xx is ever duplicated in b, then the results will be the
same, but the EXISTS version might still be faster. Since SQL Server
doesn't know that there is only one row in b with a particular value of
xx, it still has to search the rest of the table after encoutering one;
if EXISTS is used, the search is aborted after the first match.
If SQL Server knows that values of b.xx are unique (because there is a
PRIMARY KEY or UNIQUE constraint on b.xx), then the performance MIGHT be
the same - the optimizer MIGHT decide to use the knowledge of this
constraint and createt the same execution plan it'll also use for the
EXISTS version.
I have never seen or heard of any situation where the JOIN would be
fafster than the "equivalent" EXISTS version. And I don't think I ever
will.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Hi Hugo and Chris,
"Hugo Kornelis" wrote:
> On Tue, 7 Jun 2005 15:11:21 -0700, ChrisR wrote:
>
> Hi Chris,
> The question is actually irrelevant. A join is different: a query such
> as
> SELECT a.*
> FROM a
> WHERE EXISTS (SELECT * FROM b WHERE b.xx = a.xx)
> will never return more than one copy of each row in a. Using a join
> instead, such as this:
> SELECT a.*
> FROM a
> JOIN b ON b.xx = a.xx
> might return multiple copies of rows in a for values of xx that are not
> unique in table b.
> If no value of xx is ever duplicated in b, then the results will be the
> same, but the EXISTS version might still be faster. Since SQL Server
> doesn't know that there is only one row in b with a particular value of
> xx, it still has to search the rest of the table after encoutering one;
> if EXISTS is used, the search is aborted after the first match.
> If SQL Server knows that values of b.xx are unique (because there is a
> PRIMARY KEY or UNIQUE constraint on b.xx), then the performance MIGHT be
> the same - the optimizer MIGHT decide to use the knowledge of this
> constraint and createt the same execution plan it'll also use for the
> EXISTS version.
> I have never seen or heard of any situation where the JOIN would be
> fafster than the "equivalent" EXISTS version. And I don't think I ever
> will.
>
I think you have to try the statement both ways.
I'm not sure about EXISTS, but I have a fairly complex multi-way join that
is much faster with
a LEFT JOIN b on a.id = b.id ... WHERE b.id is null
than with
WHERE NOT EXISTS (select 1 from b where a.id = b.id)
Regards,
Jonathan
|||On Thu, 9 Jun 2005 12:52:03 -0700, Jonathan Levine wrote:
(snip)
>I'm not sure about EXISTS, but I have a fairly complex multi-way join that
>is much faster with
>a LEFT JOIN b on a.id = b.id ... WHERE b.id is null
>than with
>WHERE NOT EXISTS (select 1 from b where a.id = b.id)
Hi Jonathan,
Thanks for adding this. This is not the first time I witness or hear
reports of this either. It seems that the SQL Server development team
put most effort in the optimization of join strategies, since they are
used most often in typical queries.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Also, be careful of the Optimizer Estimated Execution Plans. I especially
see this in comparisons between LEFT OUTER JOINS versus the use of EXISTS or
NOT EXISTS.
Even though the JOIN may produce a better ESTIMATED execution cost, it will
often create a HASH table for the JOIN, where the EXISTS solution will often
use a Nested Loop JOIN. The HASH will ALWAYS create temporary object, which
will have physical impact that the Optimizer does not take into account.
You're best bet would be to SET STATISTICS time and I/O on and run them both
ways, making sure to flush the Buffer Pool before each execution. This will
give you ACTUAL run information, not just ESTIMATED.
Sincerely,
Anthony Thomas
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:mnfha11l8fukurbcocmi9rg30s47q0lj6j@.4ax.com...
On Thu, 9 Jun 2005 12:52:03 -0700, Jonathan Levine wrote:
(snip)
>I'm not sure about EXISTS, but I have a fairly complex multi-way join that
>is much faster with
>a LEFT JOIN b on a.id = b.id ... WHERE b.id is null
>than with
>WHERE NOT EXISTS (select 1 from b where a.id = b.id)
Hi Jonathan,
Thanks for adding this. This is not the first time I witness or hear
reports of this either. It seems that the SQL Server development team
put most effort in the optimization of join strategies, since they are
used most often in typical queries.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
is Exists faster than a Join
From http://www.sql-server-performance.c...ysis_tuning.asp
B) Can standard JOIN's be replaced with EXISTS statements?
If you need to join tables to ensure XYZ value exists or a specific set of
rows exists, then considering porting to EXISTS or NOT EXISTS statements.
At the same time is may also alleviate the need for DISTINCT clauses.
So is a Join really potentiall slower?The Exists clause can be faster than a join because it just searches long
enough to find the first "Hit" as opposed to returning all rows that meet
the join criteria.
Greg Jackson
PDX, Oregon|||Could you please elaborate? An Exists will meet all the rows that meet the
query criteria. Given that the criteria should return the same data as a
join, how could it be faster?
"pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
news:eZWWYB7aFHA.3848@.TK2MSFTNGP10.phx.gbl...
> The Exists clause can be faster than a join because it just searches long
> enough to find the first "Hit" as opposed to returning all rows that meet
> the join criteria.
>
> Greg Jackson
> PDX, Oregon
>|||I say
SELECT * FROM Main
WHERE Exists(SELECT 1 FROM MyJoinTable a WHERE a.foo = main.foo)
then it will only check for the first hit.
still probably not making sense.
show us YOUR query and I can probably explain a bit better
GAJ|||create table #Table1(T1C1 int identity (1,1), T1C2 char(10))
create table #Table2(T2C1 int, T2C2 char(10))
insert into #Table1 (T1C2) values ('T1C2')
insert into #Table1 (T1C2) values ('T1C2-2')
insert into #Table2 (T2C1,T2C2) values (1,'T1C2')
select t1.T1C1,T1C2
from #Table1 t1
inner join #Table2 t2 on t1.t1c1 = t2.t2c1
or
select t1.T1C1,T1C2
from #table1 t1
where exists
(select * from #Table2 t2
where t2.T2C1 = T1.T1C1)
"pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
news:%23fZBCd7aFHA.464@.TK2MSFTNGP15.phx.gbl...
>I say
> SELECT * FROM Main
> WHERE Exists(SELECT 1 FROM MyJoinTable a WHERE a.foo = main.foo)
> then it will only check for the first hit.
> still probably not making sense.
> show us YOUR query and I can probably explain a bit better
>
> GAJ
>|||that's faster than a Join.
the where clause will return TRUE as soon as it finds a SINGLE record in
TABLE 2 that mathces Table1 (It will not bother returning more than 1 record
that matches to table 1)
A join will return ALL Records in T2 that match T1. IF there are millions of
records in each table, this would be exponentially faster.
Greg Jackson
PDX, Oregon|||On Tue, 7 Jun 2005 15:11:21 -0700, ChrisR wrote:
>From http://www.sql-server-performance.c...ysis_tuning.asp
>B) Can standard JOIN's be replaced with EXISTS statements?
>If you need to join tables to ensure XYZ value exists or a specific set of
>rows exists, then considering porting to EXISTS or NOT EXISTS statements.
>At the same time is may also alleviate the need for DISTINCT clauses.
>
>So is a Join really potentiall slower?
Hi Chris,
The question is actually irrelevant. A join is different: a query such
as
SELECT a.*
FROM a
WHERE EXISTS (SELECT * FROM b WHERE b.xx = a.xx)
will never return more than one copy of each row in a. Using a join
instead, such as this:
SELECT a.*
FROM a
JOIN b ON b.xx = a.xx
might return multiple copies of rows in a for values of xx that are not
unique in table b.
If no value of xx is ever duplicated in b, then the results will be the
same, but the EXISTS version might still be faster. Since SQL Server
doesn't know that there is only one row in b with a particular value of
xx, it still has to search the rest of the table after encoutering one;
if EXISTS is used, the search is aborted after the first match.
If SQL Server knows that values of b.xx are unique (because there is a
PRIMARY KEY or UNIQUE constraint on b.xx), then the performance MIGHT be
the same - the optimizer MIGHT decide to use the knowledge of this
constraint and createt the same execution plan it'll also use for the
EXISTS version.
I have never seen or heard of any situation where the JOIN would be
fafster than the "equivalent" EXISTS version. And I don't think I ever
will.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hi Hugo and Chris,
"Hugo Kornelis" wrote:
> On Tue, 7 Jun 2005 15:11:21 -0700, ChrisR wrote:
>
> Hi Chris,
> The question is actually irrelevant. A join is different: a query such
> as
> SELECT a.*
> FROM a
> WHERE EXISTS (SELECT * FROM b WHERE b.xx = a.xx)
> will never return more than one copy of each row in a. Using a join
> instead, such as this:
> SELECT a.*
> FROM a
> JOIN b ON b.xx = a.xx
> might return multiple copies of rows in a for values of xx that are not
> unique in table b.
> If no value of xx is ever duplicated in b, then the results will be the
> same, but the EXISTS version might still be faster. Since SQL Server
> doesn't know that there is only one row in b with a particular value of
> xx, it still has to search the rest of the table after encoutering one;
> if EXISTS is used, the search is aborted after the first match.
> If SQL Server knows that values of b.xx are unique (because there is a
> PRIMARY KEY or UNIQUE constraint on b.xx), then the performance MIGHT be
> the same - the optimizer MIGHT decide to use the knowledge of this
> constraint and createt the same execution plan it'll also use for the
> EXISTS version.
> I have never seen or heard of any situation where the JOIN would be
> fafster than the "equivalent" EXISTS version. And I don't think I ever
> will.
>
I think you have to try the statement both ways.
I'm not sure about EXISTS, but I have a fairly complex multi-way join that
is much faster with
a LEFT JOIN b on a.id = b.id ... WHERE b.id is null
than with
WHERE NOT EXISTS (select 1 from b where a.id = b.id)
Regards,
Jonathan|||On Thu, 9 Jun 2005 12:52:03 -0700, Jonathan Levine wrote:
(snip)
>I'm not sure about EXISTS, but I have a fairly complex multi-way join that
>is much faster with
>a LEFT JOIN b on a.id = b.id ... WHERE b.id is null
>than with
>WHERE NOT EXISTS (select 1 from b where a.id = b.id)
Hi Jonathan,
Thanks for adding this. This is not the first time I witness or hear
reports of this either. It seems that the SQL Server development team
put most effort in the optimization of join strategies, since they are
used most often in typical queries.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Also, be careful of the Optimizer Estimated Execution Plans. I especially
see this in comparisons between LEFT OUTER JOINS versus the use of EXISTS or
NOT EXISTS.
Even though the JOIN may produce a better ESTIMATED execution cost, it will
often create a HASH table for the JOIN, where the EXISTS solution will often
use a Nested Loop JOIN. The HASH will ALWAYS create temporary object, which
will have physical impact that the Optimizer does not take into account.
You're best bet would be to SET STATISTICS time and I/O on and run them both
ways, making sure to flush the Buffer Pool before each execution. This will
give you ACTUAL run information, not just ESTIMATED.
Sincerely,
Anthony Thomas
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:mnfha11l8fukurbcocmi9rg30s47q0lj6j@.
4ax.com...
On Thu, 9 Jun 2005 12:52:03 -0700, Jonathan Levine wrote:
(snip)
>I'm not sure about EXISTS, but I have a fairly complex multi-way join that
>is much faster with
>a LEFT JOIN b on a.id = b.id ... WHERE b.id is null
>than with
>WHERE NOT EXISTS (select 1 from b where a.id = b.id)
Hi Jonathan,
Thanks for adding this. This is not the first time I witness or hear
reports of this either. It seems that the SQL Server development team
put most effort in the optimization of join strategies, since they are
used most often in typical queries.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
B) Can standard JOIN's be replaced with EXISTS statements?
If you need to join tables to ensure XYZ value exists or a specific set of
rows exists, then considering porting to EXISTS or NOT EXISTS statements.
At the same time is may also alleviate the need for DISTINCT clauses.
So is a Join really potentiall slower?The Exists clause can be faster than a join because it just searches long
enough to find the first "Hit" as opposed to returning all rows that meet
the join criteria.
Greg Jackson
PDX, Oregon|||Could you please elaborate? An Exists will meet all the rows that meet the
query criteria. Given that the criteria should return the same data as a
join, how could it be faster?
"pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
news:eZWWYB7aFHA.3848@.TK2MSFTNGP10.phx.gbl...
> The Exists clause can be faster than a join because it just searches long
> enough to find the first "Hit" as opposed to returning all rows that meet
> the join criteria.
>
> Greg Jackson
> PDX, Oregon
>|||I say
SELECT * FROM Main
WHERE Exists(SELECT 1 FROM MyJoinTable a WHERE a.foo = main.foo)
then it will only check for the first hit.
still probably not making sense.
show us YOUR query and I can probably explain a bit better
GAJ|||create table #Table1(T1C1 int identity (1,1), T1C2 char(10))
create table #Table2(T2C1 int, T2C2 char(10))
insert into #Table1 (T1C2) values ('T1C2')
insert into #Table1 (T1C2) values ('T1C2-2')
insert into #Table2 (T2C1,T2C2) values (1,'T1C2')
select t1.T1C1,T1C2
from #Table1 t1
inner join #Table2 t2 on t1.t1c1 = t2.t2c1
or
select t1.T1C1,T1C2
from #table1 t1
where exists
(select * from #Table2 t2
where t2.T2C1 = T1.T1C1)
"pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
news:%23fZBCd7aFHA.464@.TK2MSFTNGP15.phx.gbl...
>I say
> SELECT * FROM Main
> WHERE Exists(SELECT 1 FROM MyJoinTable a WHERE a.foo = main.foo)
> then it will only check for the first hit.
> still probably not making sense.
> show us YOUR query and I can probably explain a bit better
>
> GAJ
>|||that's faster than a Join.
the where clause will return TRUE as soon as it finds a SINGLE record in
TABLE 2 that mathces Table1 (It will not bother returning more than 1 record
that matches to table 1)
A join will return ALL Records in T2 that match T1. IF there are millions of
records in each table, this would be exponentially faster.
Greg Jackson
PDX, Oregon|||On Tue, 7 Jun 2005 15:11:21 -0700, ChrisR wrote:
>From http://www.sql-server-performance.c...ysis_tuning.asp
>B) Can standard JOIN's be replaced with EXISTS statements?
>If you need to join tables to ensure XYZ value exists or a specific set of
>rows exists, then considering porting to EXISTS or NOT EXISTS statements.
>At the same time is may also alleviate the need for DISTINCT clauses.
>
>So is a Join really potentiall slower?
Hi Chris,
The question is actually irrelevant. A join is different: a query such
as
SELECT a.*
FROM a
WHERE EXISTS (SELECT * FROM b WHERE b.xx = a.xx)
will never return more than one copy of each row in a. Using a join
instead, such as this:
SELECT a.*
FROM a
JOIN b ON b.xx = a.xx
might return multiple copies of rows in a for values of xx that are not
unique in table b.
If no value of xx is ever duplicated in b, then the results will be the
same, but the EXISTS version might still be faster. Since SQL Server
doesn't know that there is only one row in b with a particular value of
xx, it still has to search the rest of the table after encoutering one;
if EXISTS is used, the search is aborted after the first match.
If SQL Server knows that values of b.xx are unique (because there is a
PRIMARY KEY or UNIQUE constraint on b.xx), then the performance MIGHT be
the same - the optimizer MIGHT decide to use the knowledge of this
constraint and createt the same execution plan it'll also use for the
EXISTS version.
I have never seen or heard of any situation where the JOIN would be
fafster than the "equivalent" EXISTS version. And I don't think I ever
will.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hi Hugo and Chris,
"Hugo Kornelis" wrote:
> On Tue, 7 Jun 2005 15:11:21 -0700, ChrisR wrote:
>
> Hi Chris,
> The question is actually irrelevant. A join is different: a query such
> as
> SELECT a.*
> FROM a
> WHERE EXISTS (SELECT * FROM b WHERE b.xx = a.xx)
> will never return more than one copy of each row in a. Using a join
> instead, such as this:
> SELECT a.*
> FROM a
> JOIN b ON b.xx = a.xx
> might return multiple copies of rows in a for values of xx that are not
> unique in table b.
> If no value of xx is ever duplicated in b, then the results will be the
> same, but the EXISTS version might still be faster. Since SQL Server
> doesn't know that there is only one row in b with a particular value of
> xx, it still has to search the rest of the table after encoutering one;
> if EXISTS is used, the search is aborted after the first match.
> If SQL Server knows that values of b.xx are unique (because there is a
> PRIMARY KEY or UNIQUE constraint on b.xx), then the performance MIGHT be
> the same - the optimizer MIGHT decide to use the knowledge of this
> constraint and createt the same execution plan it'll also use for the
> EXISTS version.
> I have never seen or heard of any situation where the JOIN would be
> fafster than the "equivalent" EXISTS version. And I don't think I ever
> will.
>
I think you have to try the statement both ways.
I'm not sure about EXISTS, but I have a fairly complex multi-way join that
is much faster with
a LEFT JOIN b on a.id = b.id ... WHERE b.id is null
than with
WHERE NOT EXISTS (select 1 from b where a.id = b.id)
Regards,
Jonathan|||On Thu, 9 Jun 2005 12:52:03 -0700, Jonathan Levine wrote:
(snip)
>I'm not sure about EXISTS, but I have a fairly complex multi-way join that
>is much faster with
>a LEFT JOIN b on a.id = b.id ... WHERE b.id is null
>than with
>WHERE NOT EXISTS (select 1 from b where a.id = b.id)
Hi Jonathan,
Thanks for adding this. This is not the first time I witness or hear
reports of this either. It seems that the SQL Server development team
put most effort in the optimization of join strategies, since they are
used most often in typical queries.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Also, be careful of the Optimizer Estimated Execution Plans. I especially
see this in comparisons between LEFT OUTER JOINS versus the use of EXISTS or
NOT EXISTS.
Even though the JOIN may produce a better ESTIMATED execution cost, it will
often create a HASH table for the JOIN, where the EXISTS solution will often
use a Nested Loop JOIN. The HASH will ALWAYS create temporary object, which
will have physical impact that the Optimizer does not take into account.
You're best bet would be to SET STATISTICS time and I/O on and run them both
ways, making sure to flush the Buffer Pool before each execution. This will
give you ACTUAL run information, not just ESTIMATED.
Sincerely,
Anthony Thomas
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:mnfha11l8fukurbcocmi9rg30s47q0lj6j@.
4ax.com...
On Thu, 9 Jun 2005 12:52:03 -0700, Jonathan Levine wrote:
(snip)
>I'm not sure about EXISTS, but I have a fairly complex multi-way join that
>is much faster with
>a LEFT JOIN b on a.id = b.id ... WHERE b.id is null
>than with
>WHERE NOT EXISTS (select 1 from b where a.id = b.id)
Hi Jonathan,
Thanks for adding this. This is not the first time I witness or hear
reports of this either. It seems that the SQL Server development team
put most effort in the optimization of join strategies, since they are
used most often in typical queries.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
Monday, February 20, 2012
Is Autonumber Possible
I have an SQL database that was an Access database. In a
few of the tables, in the Access database, I used an auto
number. When I converted it to SQL I noticed that there
is not an auto number data type. I was wondering if there
is an equivalent to Auto number in SQL or if there was any
way of replicating that data type.
Thanks,
Aaron ShoverCheck out Identity in Books Online.
"Aaron Shover" <ashover@.state.pa.us> wrote in message
news:079201c35a8e$35f79620$a401280a@.phx.gbl...
> I have an SQL database that was an Access database. In a
> few of the tables, in the Access database, I used an auto
> number. When I converted it to SQL I noticed that there
> is not an auto number data type. I was wondering if there
> is an equivalent to Auto number in SQL or if there was any
> way of replicating that data type.
> Thanks,
> Aaron Shover|||Aaron,
Refer 'IDENTITY' property in BooksOnLine.A sample is
CREATE TABLE products
(
pid int IDENTITY(1,1),
pname varchar (35),
)
GO
INSERT products VALUES('book')
GO
--
Dinesh.
SQL Server FAQ at
http://www.tkdinesh.com
"Aaron Shover" <ashover@.state.pa.us> wrote in message
news:079201c35a8e$35f79620$a401280a@.phx.gbl...
> I have an SQL database that was an Access database. In a
> few of the tables, in the Access database, I used an auto
> number. When I converted it to SQL I noticed that there
> is not an auto number data type. I was wondering if there
> is an equivalent to Auto number in SQL or if there was any
> way of replicating that data type.
> Thanks,
> Aaron Shover|||In SQL Server, it is called an Identity column.
Depending on how you are making your tables, you either
set IDENTITY to YES once you have set the column data
type to integer, or from script you define the table
something like
CREATE TABLE {tablename}
(
tablenameID int identity(1,1) primary key clustered,
.
.
.
)
Either way will give an auto-incremented table. Note
that you are able to set the starting value, and the
incremental jump amount independently. In that example
it starts at 1 and increments by 1 (pretty common).
Paladin
>--Original Message--
>I have an SQL database that was an Access database. In
a
>few of the tables, in the Access database, I used an
auto
>number. When I converted it to SQL I noticed that there
>is not an auto number data type. I was wondering if
there
>is an equivalent to Auto number in SQL or if there was
any
>way of replicating that data type.
>Thanks,
>Aaron Shover
>.
>
few of the tables, in the Access database, I used an auto
number. When I converted it to SQL I noticed that there
is not an auto number data type. I was wondering if there
is an equivalent to Auto number in SQL or if there was any
way of replicating that data type.
Thanks,
Aaron ShoverCheck out Identity in Books Online.
"Aaron Shover" <ashover@.state.pa.us> wrote in message
news:079201c35a8e$35f79620$a401280a@.phx.gbl...
> I have an SQL database that was an Access database. In a
> few of the tables, in the Access database, I used an auto
> number. When I converted it to SQL I noticed that there
> is not an auto number data type. I was wondering if there
> is an equivalent to Auto number in SQL or if there was any
> way of replicating that data type.
> Thanks,
> Aaron Shover|||Aaron,
Refer 'IDENTITY' property in BooksOnLine.A sample is
CREATE TABLE products
(
pid int IDENTITY(1,1),
pname varchar (35),
)
GO
INSERT products VALUES('book')
GO
--
Dinesh.
SQL Server FAQ at
http://www.tkdinesh.com
"Aaron Shover" <ashover@.state.pa.us> wrote in message
news:079201c35a8e$35f79620$a401280a@.phx.gbl...
> I have an SQL database that was an Access database. In a
> few of the tables, in the Access database, I used an auto
> number. When I converted it to SQL I noticed that there
> is not an auto number data type. I was wondering if there
> is an equivalent to Auto number in SQL or if there was any
> way of replicating that data type.
> Thanks,
> Aaron Shover|||In SQL Server, it is called an Identity column.
Depending on how you are making your tables, you either
set IDENTITY to YES once you have set the column data
type to integer, or from script you define the table
something like
CREATE TABLE {tablename}
(
tablenameID int identity(1,1) primary key clustered,
.
.
.
)
Either way will give an auto-incremented table. Note
that you are able to set the starting value, and the
incremental jump amount independently. In that example
it starts at 1 and increments by 1 (pretty common).
Paladin
>--Original Message--
>I have an SQL database that was an Access database. In
a
>few of the tables, in the Access database, I used an
auto
>number. When I converted it to SQL I noticed that there
>is not an auto number data type. I was wondering if
there
>is an equivalent to Auto number in SQL or if there was
any
>way of replicating that data type.
>Thanks,
>Aaron Shover
>.
>
Subscribe to:
Posts (Atom)