Reg. MS SLQSRVER 2000 driver (sp 1) driver for jdbc

Hi
I am writing jsp pages on JRUN (DM2k MX) server connecting to MS SQLServer 2000 dbase. using
MS SQLServer 2000 driver for JDBC (SP 1).
Everything is working fine, except when I try to access the result set, ( which is storing my query results),
I have a JSP scriptlet as below
//successfully connect a
// exceutet the query a
// store the result in ResultSet rs
<%
while (rs.next())
{
%><option value=<%=rs.getString("DeptName")%></option><%
}
%>
Each time this code runs, it skips the first record. I tried placing the cursor before the first record , prior to entering the
while loop, by
1) rs.beforeFirst()
or
2)
if (rs.getRow() == 2)
{
rs.absolute(1);
//rs.first();
}
which should force it to goto the first record. but botjh 1 & 2 failed and the error was
MS SQLServer 2000 driver for jdbc do not support beforeFirst / First
How do I get around this problem? Is this a bug in the driver or am I missing something?
Thanks in advance
How exactly are you populating the resultset? Do you see the same behavior
from a standalone JDBC program? Below is a code snippet that connects to
Northwind database. It retrieves the data without skipping the first row:
Class.forName("com.microsoft.jdbc.sqlserver.SQLSer verDriver");
con = java.sql.DriverManager.getConnection(url, userName, password);
String query = "SELECT * from Customers";
//Option 1
PreparedStatement stmt = con.prepareStatement(query);
ResultSet rs = stmt.executeQuery();
//Option 2
//Statement stmt = con.createStatement();
//ResultSet rs = stmt.executeQuery(query);
while(rs.next())
{
System.out.println("Contact Name: " + rs.getString("ContactName"));
}
rs.close();
stmt.close();
con.close();
con=null;
Give this code a try to see if the behavior is the same. Do you see the
same behavior with JDBC SP2?
Carb Simien, MCSE MCDBA MCAD
Microsoft Developer Support - Web Data
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection
Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.
| Thread-Topic: Reg. MS SLQSRVER 2000 driver (sp 1) driver for jdbc
| thread-index: AcQiWoPDPDw+LtmiRSmrZYKbSsks1w==
| X-WN-Post: microsoft.public.sqlserver.jdbcdriver
| From: "=?Utf-8?B?U3VtYUpEQkM=?=" <anonymous@.discussions.microsoft.com>
| Subject: Reg. MS SLQSRVER 2000 driver (sp 1) driver for jdbc
| Date: Wed, 14 Apr 2004 12:56:02 -0700
| Lines: 42
| Message-ID: <F9963AEF-09BA-428A-B5C9-12505D15AE9F@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.jdbcdriver
| Path: cpmsftngxa06.phx.gbl
| Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.jdbcdriver:5891
| NNTP-Posting-Host: tk2msftcmty1.phx.gbl 10.40.1.180
| X-Tomcat-NG: microsoft.public.sqlserver.jdbcdriver
|
| Hi
I am writing jsp pages on JRUN (DM2k MX) server connecting to MS SQLServer
2000 dbase. using
MS SQLServer 2000 driver for JDBC (SP 1).
Everything is working fine, except when I try to access the result set, (
which is storing my query results),
I have a JSP scriptlet as below
//successfully connect a
// exceutet the query a
// store the result in ResultSet rs
<%
while (rs.next())
{
%><option value=<%=rs.getString("DeptName")%></option><%
}
%>
Each time this code runs, it skips the first record. I tried placing the
cursor before the first record , prior to entering the
while loop, by
1) rs.beforeFirst()
or
2)
if (rs.getRow() == 2)
{
rs.absolute(1);
//rs.first();
}
which should force it to goto the first record. but botjh 1 & 2 failed and
the error was
MS SQLServer 2000 driver for jdbc do not support beforeFirst / First
How do I get around this problem? Is this a bug in the driver or am I
missing something?
Thanks in advance
|
sql

Refreshing Links in Access doesn't allow me to Add Records!

Hi
I have an application in Access2003 with linked tables. When I create the
links manually (picking DNS etc) I have no problems. When I then try to
recreate the links programatically (different users) I am not able to add an
y
records to any of the tables! If I erase all the linked tables and reconnect
manually with the other user I have no problems!
I am using the relink code from
http://support.microsoft.com/defaul...kb;en-us;159691
Any and all suggestions Welcome!!!
Regards
Meir
Suggestions?There is a seperate newgroup for acceess/adp/sql server integration.
Post there
--
Regards
R.D
--Knowledge gets doubled when shared
"mrrcomp" wrote:

> Hi
> I have an application in Access2003 with linked tables. When I create the
> links manually (picking DNS etc) I have no problems. When I then try to
> recreate the links programatically (different users) I am not able to add
any
> records to any of the tables! If I erase all the linked tables and reconne
ct
> manually with the other user I have no problems!
> I am using the relink code from
> http://support.microsoft.com/defaul...kb;en-us;159691
>
> Any and all suggestions Welcome!!!
> Regards
> Meir
>
> Suggestions?
>|||As long as the user has permissions in the database and you have a
primary key defined on the table, inserts an dupdates shouldn't be a
problem. However, instead of using a DSN, you can supply connection
information in your code. This should get you started:
Public Sub LinkODBConnectionString()
Dim strConnection As String
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Set db = CurrentDb
' Specify the driver, the server, and the connection
strConnection = "ODBC;Driver={SQL Server};" & _
" Server=(local);Database=SqlDbName;Truste
d_Connection=Yes"
' Specifying a SQLS user/password instead of integrated security
' strConnection = "ODBC;Driver={SQL Server};" & _
' " Server=(Local);Database=SqlDbName;UID=Us
erName;PWD=password"
' Create Linked Table. The LinkedTableName and the
' ServerTableName can be the same.
Set tdf = db.CreateTableDef("LinkedTableName")
tdf.Connect = strConnection
tdf.SourceTableName = "ServerTableName"
db.TableDefs.Append tdf
Set tdf = Nothing
End Sub
On Fri, 7 Oct 2005 05:17:03 -0700, "mrrcomp"
<mrrcomp@.discussions.microsoft.com> wrote:

>Hi
>I have an application in Access2003 with linked tables. When I create the
>links manually (picking DNS etc) I have no problems. When I then try to
>recreate the links programatically (different users) I am not able to add a
ny
>records to any of the tables! If I erase all the linked tables and reconnec
t
>manually with the other user I have no problems!
>I am using the relink code from
>http://support.microsoft.com/defaul...kb;en-us;159691
>
>Any and all suggestions Welcome!!!
>Regards
>Meir
>
>Suggestions?sql

Refresh with jsp doesnt work

Hi!

I've created a simple jsp page with a crystal report viewer on it. I used Crystal Reports Developer to design the report, as db I use access and IBM WSED to create and run the jsp file on a server. That works fine, but when I would refresh the report it shows no recordset (and also no error message appears). Why I can't refresh the data?

The code:

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<%@. page import="com.crystaldecisions.report.web.viewer.CrystalReportViewer" %>
<%@. page import="com.crystaldecisions.sdk.occa.report.data.*" %>
<%@. page import="com.crystaldecisions.reports.reportengineinterface.JPEReportSourceFactory,
com.crystaldecisions.sdk.occa.report.reportsource.IReportSourceFactory2,com.crystaldecisions.sdk.occa.report.reportsource.IReportSource"
%>
<HTML>
<HEAD>
</HEAD>
<BODY>
CrystalTest.jsp

<%
try {

IReportSourceFactory2 rptSrcFactory = new JPEReportSourceFactory();

String report = "/CrystalReport/Report1.rpt";

Object reportSource = rptSrcFactory.createReportSource(report, request.getLocale())

CrystalReportViewer viewer = new CrystalReportViewer();
viewer.setReportSource(reportSource);

viewer.setOwnPage(true);
viewer.setTop(80);
viewer.setDisplayGroupTree(false);
viewer.setHasLogo(false);
viewer.setHasRefreshButton(true);

viewer.processHttpRequest(request, response, getServletConfig().getServletContext(), null);
viewer.dispose();
} catch(Exception e)
{

out.println("CrystalTest: "+e);
}
%>

</BODY
</HTML>hi there!

I've now solved the problem with a jdbc/db2 connection.

The solution is, that I must connect the Crystal Report with jdbc/db2 to the database and also connect in java/jsp to the database as the follows (only samplecode from helpfile):

setDbLogonViewReport.jsp

<%@. page import= "com.crystaldecisions.report.web.viewer.*,
com.crystaldecisions.sdk.occa.report.data.*" %>
<%@. page import="com.crystaldecisions.report.web.viewer.*" %>
<%@. page import="com.crystaldecisions.sdk.occa.report.data.*" %>
<%@. page import="com.crystaldecisions.reports.reportengineinterface.JPEReportSourceFactory" %>
<%@. page import="com.crystaldecisions.sdk.occa.report.reportsource.IReportSourceFactory2" %>

<%
Object reportSource = session.getAttribute("reportSource");
if (reportSource == null)
{
String report = "/reports/sample.rpt";
IReportSourceFactory2 rptSrcFactory = new JPEReportSourceFactory();
reportSource = rptSrcFactory.createReportSource(report, request.getLocale());
session.setAttribute("reportSource", reportSource);
}

ConnectionInfos connInfos = new ConnectionInfos();
IConnectionInfo connInfo1 = new ConnectionInfo();
connInfo1.setUserName("reportLogin");
connInfo1.setPassword("");
connInfos.add(connInfo1);

CrystalReportViewer viewer = new CrystalReportViewer();

viewer.setReportSource(reportSource);
&n bsp; viewer.setEnableLogonPrompt(false);
viewer.setDatabaseLogonInfos(connInfos);

if (session.getAttribute("refreshed") == null)
{
viewer.refresh();
session.setAttribute("refreshed", "true");
}

viewer.setOwnPage(true);

viewer.processHttpRequest(request, response, getServletConfig().getServletContext(), null);
%>

Ask if you've more questions to that.

refrence

hi
i am new in sql .could you direct me a web site that learn sql
thank youhttp://www.aspfaq.com/2423
"bijan" <bijan@.discussions.microsoft.com> wrote in message
news:17EBD077-7005-483D-9A12-7CF22547F415@.microsoft.com...
> hi
> i am new in sql .could you direct me a web site that learn sql
> thank you

Reformed access user needs advise for future

Hi
I have a vba/vb/access background but I have finally seen the light and have
decided to take up my next project with sql server backend (vb.net front).
So here is
my question;
What are the recommended db development guidelines to achieve both a) a good
user experience of being able to scroll to any record using record
navigation buttons and b) the db efficiency requirement of not loading too
many records in dataset at one time. If there is such a strategy to which
many agree then there should be a sample code app somewhere. It would help
me enormously to see the guts of an actual well written db app - no matter
how trivial as long as it covers the necessary detail - instead of advise
like don't do this or that without the coding specifics.
So here is a chance for the worthy to lead a recent convert (albeit
reluctant due to self deficiency on sql server side).
Thanks
Regards
For the database side, see http://www.aspfaq.com/2120
Unfortunately, I wrote the article long before .NET came about, so you won't
get any client app coding specifics, but it should still be helpful.
If you have the opportunity for additional learning curve, I recommend
learning C# as opposed to VB.Net...
"John" <John@.nospam.infovis.co.uk> wrote in message
news:eGUQVFeaIHA.4684@.TK2MSFTNGP06.phx.gbl...
> Hi
> I have a vba/vb/access background but I have finally seen the light and
> have decided to take up my next project with sql server backend (vb.net
> front). So here is
> my question;
> What are the recommended db development guidelines to achieve both a) a
> good
> user experience of being able to scroll to any record using record
> navigation buttons and b) the db efficiency requirement of not loading too
> many records in dataset at one time. If there is such a strategy to which
> many agree then there should be a sample code app somewhere. It would help
> me enormously to see the guts of an actual well written db app - no matter
> how trivial as long as it covers the necessary detail - instead of advise
> like don't do this or that without the coding specifics.
> So here is a chance for the worthy to lead a recent convert (albeit
> reluctant due to self deficiency on sql server side).
> Thanks
> Regards
>
>
|||As a person who used ASP\VBScript\VBA and had a comfort zone myself I agree
with Aaron. Adopting C# now is the smartest thing you can do now that you're
starting to see that the light can burn even brighter.
Give us a break because we're tired of explaining why over and over. Just
get out of that comfort zone and do it now while you're in transition and
trying to learn OOP.
As for your questions I can briefly say searching the web always works for
me when I have broad open ended questions...
"John" <John@.nospam.infovis.co.uk> wrote in message
news:eGUQVFeaIHA.4684@.TK2MSFTNGP06.phx.gbl...
> Hi
> I have a vba/vb/access background but I have finally seen the light and
> have decided to take up my next project with sql server backend (vb.net
> front). So here is
> my question;
> What are the recommended db development guidelines to achieve both a) a
> good
> user experience of being able to scroll to any record using record
> navigation buttons and b) the db efficiency requirement of not loading too
> many records in dataset at one time. If there is such a strategy to which
> many agree then there should be a sample code app somewhere. It would help
> me enormously to see the guts of an actual well written db app - no matter
> how trivial as long as it covers the necessary detail - instead of advise
> like don't do this or that without the coding specifics.
> So here is a chance for the worthy to lead a recent convert (albeit
> reluctant due to self deficiency on sql server side).
> Thanks
> Regards
>
>

Referential integrity constraints on indexed views

Hi
I was wondering whether we can create referential integrity constraints on
indexed views in SQL Server 2000. We are trying to create a data model and
would like to know whether we can enforce constraints on indexed views,
otherwise we may have to go for tables.
Please inform.
Thanks
BobBob,
Not that I'm aware of - never seen anyone add a constraint to a
view(indexed). Would probably have to handle this at the table level with
constraints or triggers.
HTH
Jerry
"Bob" <Bob@.discussions.microsoft.com> wrote in message
news:B911616E-39CC-4879-B485-58ED9E951E23@.microsoft.com...
> Hi
> I was wondering whether we can create referential integrity constraints on
> indexed views in SQL Server 2000. We are trying to create a data model and
> would like to know whether we can enforce constraints on indexed views,
> otherwise we may have to go for tables.
> Please inform.
> Thanks
> Bob|||The indexed view ultimately gets it data from the underlying tables. So if
they have the proper constraints the view will never see incorrect data.
Andrew J. Kelly SQL MVP
"Bob" <Bob@.discussions.microsoft.com> wrote in message
news:B911616E-39CC-4879-B485-58ED9E951E23@.microsoft.com...
> Hi
> I was wondering whether we can create referential integrity constraints on
> indexed views in SQL Server 2000. We are trying to create a data model and
> would like to know whether we can enforce constraints on indexed views,
> otherwise we may have to go for tables.
> Please inform.
> Thanks
> Bob

Referential integrity constraints on indexed views

Hi
I was wondering whether we can create referential integrity constraints on
indexed views in SQL Server 2000. We are trying to create a data model and
would like to know whether we can enforce constraints on indexed views,
otherwise we may have to go for tables.
Please inform.
Thanks
Bob
Bob,
Not that I'm aware of - never seen anyone add a constraint to a
view(indexed). Would probably have to handle this at the table level with
constraints or triggers.
HTH
Jerry
"Bob" <Bob@.discussions.microsoft.com> wrote in message
news:B911616E-39CC-4879-B485-58ED9E951E23@.microsoft.com...
> Hi
> I was wondering whether we can create referential integrity constraints on
> indexed views in SQL Server 2000. We are trying to create a data model and
> would like to know whether we can enforce constraints on indexed views,
> otherwise we may have to go for tables.
> Please inform.
> Thanks
> Bob
|||The indexed view ultimately gets it data from the underlying tables. So if
they have the proper constraints the view will never see incorrect data.
Andrew J. Kelly SQL MVP
"Bob" <Bob@.discussions.microsoft.com> wrote in message
news:B911616E-39CC-4879-B485-58ED9E951E23@.microsoft.com...
> Hi
> I was wondering whether we can create referential integrity constraints on
> indexed views in SQL Server 2000. We are trying to create a data model and
> would like to know whether we can enforce constraints on indexed views,
> otherwise we may have to go for tables.
> Please inform.
> Thanks
> Bob
sql

Referencing destination table in INSERT SELECT statement

Hi
I hava a following piece of code:
CREATE TABLE #s(s varchar)
INSERT INTO #s VALUES ('a')
INSERT INTO #s VALUES ('b')
INSERT INTO #s VALUES ('c')
CREATE TABLE #d(s varchar, d int)
INSERT INTO #d SELECT s, ISNULL((SELECT MAX(d) + 1 FROM #d), 0) FROM #s
SELECT d FROM #d
DROP TABLE #d
DROP TABLE #s
As you can see I'm trying to copy data from the source table to the
destination one, and while doing so, assign each row a subsequent
number (d) starting from 0.
I assumed, that the SELECT clause will be run for each inserted row -
however - when I run this code as the result I receive only zeros in
the d column for all rows.
I've already tried WITH (NOLOCK/ROWLOCK) clauses to no avail.
Is there any way to get this query running?
Thanks
Szymon
Hi
CREATE TABLE #s(s varchar)
INSERT INTO #s VALUES ('a')
INSERT INTO #s VALUES ('b')
INSERT INTO #s VALUES ('c')
CREATE TABLE #d(s varchar, d int)
INSERT INTO #d SELECT s,(select count(*) from #s s where s.s<=#s.s) FROM
#s
SELECT d FROM #d
DROP TABLE #d
DROP TABLE #s
<joozeq@.gmail.com> wrote in message
news:1163408290.886457.14770@.h54g2000cwb.googlegro ups.com...
> Hi
> I hava a following piece of code:
> CREATE TABLE #s(s varchar)
> INSERT INTO #s VALUES ('a')
> INSERT INTO #s VALUES ('b')
> INSERT INTO #s VALUES ('c')
> CREATE TABLE #d(s varchar, d int)
> INSERT INTO #d SELECT s, ISNULL((SELECT MAX(d) + 1 FROM #d), 0) FROM #s
> SELECT d FROM #d
> DROP TABLE #d
> DROP TABLE #s
> As you can see I'm trying to copy data from the source table to the
> destination one, and while doing so, assign each row a subsequent
> number (d) starting from 0.
> I assumed, that the SELECT clause will be run for each inserted row -
> however - when I run this code as the result I receive only zeros in
> the d column for all rows.
> I've already tried WITH (NOLOCK/ROWLOCK) clauses to no avail.
> Is there any way to get this query running?
> Thanks
> Szymon
>
|||Hi,
You can try the following query:
CREATE TABLE #s(s varchar)
INSERT INTO #s VALUES ('a')
INSERT INTO #s VALUES ('b')
INSERT INTO #s VALUES ('c')
Without creating the #d table you can create and insert the data of #s table
in #d by the following query:
SELECT s, ROWID=IDENTITY(int,0,1) into #d from #s
Regards
Swaprakash
"joozeq@.gmail.com" wrote:

> Hi
> I hava a following piece of code:
> CREATE TABLE #s(s varchar)
> INSERT INTO #s VALUES ('a')
> INSERT INTO #s VALUES ('b')
> INSERT INTO #s VALUES ('c')
> CREATE TABLE #d(s varchar, d int)
> INSERT INTO #d SELECT s, ISNULL((SELECT MAX(d) + 1 FROM #d), 0) FROM #s
> SELECT d FROM #d
> DROP TABLE #d
> DROP TABLE #s
> As you can see I'm trying to copy data from the source table to the
> destination one, and while doing so, assign each row a subsequent
> number (d) starting from 0.
> I assumed, that the SELECT clause will be run for each inserted row -
> however - when I run this code as the result I receive only zeros in
> the d column for all rows.
> I've already tried WITH (NOLOCK/ROWLOCK) clauses to no avail.
> Is there any way to get this query running?
> Thanks
> Szymon
>
|||joozeq@.gmail.com wrote:
> Hi
> I hava a following piece of code:
> CREATE TABLE #s(s varchar)
> INSERT INTO #s VALUES ('a')
> INSERT INTO #s VALUES ('b')
> INSERT INTO #s VALUES ('c')
> CREATE TABLE #d(s varchar, d int)
> INSERT INTO #d SELECT s, ISNULL((SELECT MAX(d) + 1 FROM #d), 0) FROM #s
> SELECT d FROM #d
> DROP TABLE #d
> DROP TABLE #s
> As you can see I'm trying to copy data from the source table to the
> destination one, and while doing so, assign each row a subsequent
> number (d) starting from 0.
> I assumed, that the SELECT clause will be run for each inserted row -
> however - when I run this code as the result I receive only zeros in
> the d column for all rows.
> I've already tried WITH (NOLOCK/ROWLOCK) clauses to no avail.
> Is there any way to get this query running?
> Thanks
> Szymon
>
Make the column "d" an identity column, and let SQL assign the
incremental value:
CREATE TABLE #s(s varchar)
INSERT INTO #s VALUES ('a')
INSERT INTO #s VALUES ('b')
INSERT INTO #s VALUES ('c')
CREATE TABLE #d(s varchar, d int identity(0,1))
INSERT INTO #d SELECT s FROM #s
SELECT d FROM #d
DROP TABLE #d
DROP TABLE #s
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||Why not add an IDENTITY column to table #s?
CREATE TABLE #s
( RowID int IDENTITY,
s varchar(20)
)
INSERT INTO #s VALUES ('a')
INSERT INTO #s VALUES ('b')
INSERT INTO #s VALUES ('c')
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
<joozeq@.gmail.com> wrote in message news:1163408290.886457.14770@.h54g2000cwb.googlegro ups.com...
> Hi
> I hava a following piece of code:
> CREATE TABLE #s(s varchar)
> INSERT INTO #s VALUES ('a')
> INSERT INTO #s VALUES ('b')
> INSERT INTO #s VALUES ('c')
> CREATE TABLE #d(s varchar, d int)
> INSERT INTO #d SELECT s, ISNULL((SELECT MAX(d) + 1 FROM #d), 0) FROM #s
> SELECT d FROM #d
> DROP TABLE #d
> DROP TABLE #s
> As you can see I'm trying to copy data from the source table to the
> destination one, and while doing so, assign each row a subsequent
> number (d) starting from 0.
> I assumed, that the SELECT clause will be run for each inserted row -
> however - when I run this code as the result I receive only zeros in
> the d column for all rows.
> I've already tried WITH (NOLOCK/ROWLOCK) clauses to no avail.
> Is there any way to get this query running?
> Thanks
> Szymon
>
|||I can't use IDENTITY because the problem is a bit more complex that I
might have described it at the beginning of this thread. Here's a bit
less abstract story:
I have a table with about 150 000 rows, which holds geographical
coordinates of a certain GPS receiver (placed in a vehicle) in a
certain moment. The table structure is as follows:
IdCoordinates int IDENTITY PRIMARY KEY
CarId nvarchar(50)
Longitude float
Latitude float
Date datetime (indexed)
Quite often I need to derive the speed of a car throughout a day,
basing on this data. This involves finding for each row in a day the
preceeding one (to calculate the distance driven and time elapsed
between the two). The simplest way is to construct a query basing on
NOT EXISTS operator but this prooves itself to be terribly slow.
So I figured out I'd add to the table additional column, SequenceNo
int, which for a certain row would hold number of all rows gathered
from the same car with dates lesser the the row's date. This makes the
query both simple and efficient.
However - with 150 000 rows (and very soon I expect it to be over 500
000) - adding this column requires first to properly update the
SequenceNo field for all rows.
First I thought that simple UPDATE Coordinates SET SequenceNo = SELECT
MAX ... would do, but this query sets all values to 1 (providing all
the values are NULL at the beginning) - apparently some row/table
locking issue (is it?).
Then I tried to create something like Oracle's sequence that would
return next sequence number for the given car id, but this requires one
of the following:
- SELECT trigger (I'd create it on a Sequence(CarId, NextSequenceNo)
table )
- UDF (so I can use it in the SET clause OF the UPDATE query) that can
issue DML statements (so it can remember current seqence number for a
certain car and return the next one)
- SP (they can ofcourse use DML) that can return value and be used in a
SET clause
AFAIK none of these exist in SQL Server 2000 (though there's a hack
somewhere on the web to create a SELECT trigger). So now I've ended up
with a SP that simply reads all data from the Coordinates table with a
cursor (the data being read must be ordered by date), fetches next
available SequenceNo from a temp table and inserts it to another temp
table with the SequenceNo field set properly (I know that updating the
source table would be more appropriate here but it's very slow).
It's dirty, slow and resource consuming - my best solution so far is to
use an update curosor and update the row in place with WHERE CURRENT OF
clause - but apparently SQL Server 2000 doesn't support curors with
ORDER BY and FOR UPDATE clauses put together, so this works only for
SQL Server 2005.
I'd appreciate any suggestions on how to solve this nicely.
Szymon
Arnie Rowland napisal(a):
> Why not add an IDENTITY column to table #s?
> CREATE TABLE #s
> ( RowID int IDENTITY,
> s varchar(20)
> )
> INSERT INTO #s VALUES ('a')
> INSERT INTO #s VALUES ('b')
> INSERT INTO #s VALUES ('c')
> --
> 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
>
|||Sorry - I CAN use ordered update cursors with SQL 2000 - I simply
forgot to add PK to the table and got misleading error message.
If anyone knows a better solution to the problem then the one with
update cursor I'd appreciate suggestions.
Szymon
joozeq@.gmail.com napisal(a):[vbcol=seagreen]
> I can't use IDENTITY because the problem is a bit more complex that I
> might have described it at the beginning of this thread. Here's a bit
> less abstract story:
> I have a table with about 150 000 rows, which holds geographical
> coordinates of a certain GPS receiver (placed in a vehicle) in a
> certain moment. The table structure is as follows:
> IdCoordinates int IDENTITY PRIMARY KEY
> CarId nvarchar(50)
> Longitude float
> Latitude float
> Date datetime (indexed)
> Quite often I need to derive the speed of a car throughout a day,
> basing on this data. This involves finding for each row in a day the
> preceeding one (to calculate the distance driven and time elapsed
> between the two). The simplest way is to construct a query basing on
> NOT EXISTS operator but this prooves itself to be terribly slow.
> So I figured out I'd add to the table additional column, SequenceNo
> int, which for a certain row would hold number of all rows gathered
> from the same car with dates lesser the the row's date. This makes the
> query both simple and efficient.
> However - with 150 000 rows (and very soon I expect it to be over 500
> 000) - adding this column requires first to properly update the
> SequenceNo field for all rows.
> First I thought that simple UPDATE Coordinates SET SequenceNo = SELECT
> MAX ... would do, but this query sets all values to 1 (providing all
> the values are NULL at the beginning) - apparently some row/table
> locking issue (is it?).
> Then I tried to create something like Oracle's sequence that would
> return next sequence number for the given car id, but this requires one
> of the following:
> - SELECT trigger (I'd create it on a Sequence(CarId, NextSequenceNo)
> table )
> - UDF (so I can use it in the SET clause OF the UPDATE query) that can
> issue DML statements (so it can remember current seqence number for a
> certain car and return the next one)
> - SP (they can ofcourse use DML) that can return value and be used in a
> SET clause
> AFAIK none of these exist in SQL Server 2000 (though there's a hack
> somewhere on the web to create a SELECT trigger). So now I've ended up
> with a SP that simply reads all data from the Coordinates table with a
> cursor (the data being read must be ordered by date), fetches next
> available SequenceNo from a temp table and inserts it to another temp
> table with the SequenceNo field set properly (I know that updating the
> source table would be more appropriate here but it's very slow).
> It's dirty, slow and resource consuming - my best solution so far is to
> use an update curosor and update the row in place with WHERE CURRENT OF
> clause - but apparently SQL Server 2000 doesn't support curors with
> ORDER BY and FOR UPDATE clauses put together, so this works only for
> SQL Server 2005.
> I'd appreciate any suggestions on how to solve this nicely.
> Szymon
> Arnie Rowland napisal(a):
|||Perhaps one of these articles may help:
Row Number (or Rank) from a SELECT Transact-SQL statement (includes Paging)
http://support.microsoft.com/default.aspx?scid=kb;en-us;186133
http://sqljunkies.com/WebLog/amachanic/archive/2004/11/03/4945.aspx
http://www.projectdmx.com/tsql/ranking.aspx
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
<joozeq@.gmail.com> wrote in message
news:1164136011.455451.90600@.b28g2000cwb.googlegro ups.com...
>I can't use IDENTITY because the problem is a bit more complex that I
> might have described it at the beginning of this thread. Here's a bit
> less abstract story:
> I have a table with about 150 000 rows, which holds geographical
> coordinates of a certain GPS receiver (placed in a vehicle) in a
> certain moment. The table structure is as follows:
> IdCoordinates int IDENTITY PRIMARY KEY
> CarId nvarchar(50)
> Longitude float
> Latitude float
> Date datetime (indexed)
> Quite often I need to derive the speed of a car throughout a day,
> basing on this data. This involves finding for each row in a day the
> preceeding one (to calculate the distance driven and time elapsed
> between the two). The simplest way is to construct a query basing on
> NOT EXISTS operator but this prooves itself to be terribly slow.
> So I figured out I'd add to the table additional column, SequenceNo
> int, which for a certain row would hold number of all rows gathered
> from the same car with dates lesser the the row's date. This makes the
> query both simple and efficient.
> However - with 150 000 rows (and very soon I expect it to be over 500
> 000) - adding this column requires first to properly update the
> SequenceNo field for all rows.
> First I thought that simple UPDATE Coordinates SET SequenceNo = SELECT
> MAX ... would do, but this query sets all values to 1 (providing all
> the values are NULL at the beginning) - apparently some row/table
> locking issue (is it?).
> Then I tried to create something like Oracle's sequence that would
> return next sequence number for the given car id, but this requires one
> of the following:
> - SELECT trigger (I'd create it on a Sequence(CarId, NextSequenceNo)
> table )
> - UDF (so I can use it in the SET clause OF the UPDATE query) that can
> issue DML statements (so it can remember current seqence number for a
> certain car and return the next one)
> - SP (they can ofcourse use DML) that can return value and be used in a
> SET clause
> AFAIK none of these exist in SQL Server 2000 (though there's a hack
> somewhere on the web to create a SELECT trigger). So now I've ended up
> with a SP that simply reads all data from the Coordinates table with a
> cursor (the data being read must be ordered by date), fetches next
> available SequenceNo from a temp table and inserts it to another temp
> table with the SequenceNo field set properly (I know that updating the
> source table would be more appropriate here but it's very slow).
> It's dirty, slow and resource consuming - my best solution so far is to
> use an update curosor and update the row in place with WHERE CURRENT OF
> clause - but apparently SQL Server 2000 doesn't support curors with
> ORDER BY and FOR UPDATE clauses put together, so this works only for
> SQL Server 2005.
> I'd appreciate any suggestions on how to solve this nicely.
> Szymon
> Arnie Rowland napisal(a):
>

Referencing destination table in INSERT SELECT statement

Hi
I hava a following piece of code:
CREATE TABLE #s(s varchar)
INSERT INTO #s VALUES ('a')
INSERT INTO #s VALUES ('b')
INSERT INTO #s VALUES ('c')
CREATE TABLE #d(s varchar, d int)
INSERT INTO #d SELECT s, ISNULL((SELECT MAX(d) + 1 FROM #d), 0) FROM #s
SELECT d FROM #d
DROP TABLE #d
DROP TABLE #s
As you can see I'm trying to copy data from the source table to the
destination one, and while doing so, assign each row a subsequent
number (d) starting from 0.
I assumed, that the SELECT clause will be run for each inserted row -
however - when I run this code as the result I receive only zeros in
the d column for all rows.
I've already tried WITH (NOLOCK/ROWLOCK) clauses to no avail.
Is there any way to get this query running?
Thanks
SzymonHi
CREATE TABLE #s(s varchar)
INSERT INTO #s VALUES ('a')
INSERT INTO #s VALUES ('b')
INSERT INTO #s VALUES ('c')
CREATE TABLE #d(s varchar, d int)
INSERT INTO #d SELECT s,(select count(*) from #s s where s.s<=#s.s) FROM
#s
SELECT d FROM #d
DROP TABLE #d
DROP TABLE #s
<joozeq@.gmail.com> wrote in message
news:1163408290.886457.14770@.h54g2000cwb.googlegroups.com...
> Hi
> I hava a following piece of code:
> CREATE TABLE #s(s varchar)
> INSERT INTO #s VALUES ('a')
> INSERT INTO #s VALUES ('b')
> INSERT INTO #s VALUES ('c')
> CREATE TABLE #d(s varchar, d int)
> INSERT INTO #d SELECT s, ISNULL((SELECT MAX(d) + 1 FROM #d), 0) FROM #s
> SELECT d FROM #d
> DROP TABLE #d
> DROP TABLE #s
> As you can see I'm trying to copy data from the source table to the
> destination one, and while doing so, assign each row a subsequent
> number (d) starting from 0.
> I assumed, that the SELECT clause will be run for each inserted row -
> however - when I run this code as the result I receive only zeros in
> the d column for all rows.
> I've already tried WITH (NOLOCK/ROWLOCK) clauses to no avail.
> Is there any way to get this query running?
> Thanks
> Szymon
>|||Hi,
You can try the following query:
CREATE TABLE #s(s varchar)
INSERT INTO #s VALUES ('a')
INSERT INTO #s VALUES ('b')
INSERT INTO #s VALUES ('c')
Without creating the #d table you can create and insert the data of #s table
in #d by the following query:
SELECT s, ROWID=IDENTITY(int,0,1) into #d from #s
Regards
Swaprakash
"joozeq@.gmail.com" wrote:

> Hi
> I hava a following piece of code:
> CREATE TABLE #s(s varchar)
> INSERT INTO #s VALUES ('a')
> INSERT INTO #s VALUES ('b')
> INSERT INTO #s VALUES ('c')
> CREATE TABLE #d(s varchar, d int)
> INSERT INTO #d SELECT s, ISNULL((SELECT MAX(d) + 1 FROM #d), 0) FROM #s
> SELECT d FROM #d
> DROP TABLE #d
> DROP TABLE #s
> As you can see I'm trying to copy data from the source table to the
> destination one, and while doing so, assign each row a subsequent
> number (d) starting from 0.
> I assumed, that the SELECT clause will be run for each inserted row -
> however - when I run this code as the result I receive only zeros in
> the d column for all rows.
> I've already tried WITH (NOLOCK/ROWLOCK) clauses to no avail.
> Is there any way to get this query running?
> Thanks
> Szymon
>|||joozeq@.gmail.com wrote:
> Hi
> I hava a following piece of code:
> CREATE TABLE #s(s varchar)
> INSERT INTO #s VALUES ('a')
> INSERT INTO #s VALUES ('b')
> INSERT INTO #s VALUES ('c')
> CREATE TABLE #d(s varchar, d int)
> INSERT INTO #d SELECT s, ISNULL((SELECT MAX(d) + 1 FROM #d), 0) FROM #s
> SELECT d FROM #d
> DROP TABLE #d
> DROP TABLE #s
> As you can see I'm trying to copy data from the source table to the
> destination one, and while doing so, assign each row a subsequent
> number (d) starting from 0.
> I assumed, that the SELECT clause will be run for each inserted row -
> however - when I run this code as the result I receive only zeros in
> the d column for all rows.
> I've already tried WITH (NOLOCK/ROWLOCK) clauses to no avail.
> Is there any way to get this query running?
> Thanks
> Szymon
>
Make the column "d" an identity column, and let SQL assign the
incremental value:
CREATE TABLE #s(s varchar)
INSERT INTO #s VALUES ('a')
INSERT INTO #s VALUES ('b')
INSERT INTO #s VALUES ('c')
CREATE TABLE #d(s varchar, d int identity(0,1))
INSERT INTO #d SELECT s FROM #s
SELECT d FROM #d
DROP TABLE #d
DROP TABLE #s
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Why not add an IDENTITY column to table #s?
CREATE TABLE #s
( RowID int IDENTITY,
s varchar(20)
)
INSERT INTO #s VALUES ('a')
INSERT INTO #s VALUES ('b')
INSERT INTO #s VALUES ('c')
--
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
<joozeq@.gmail.com> wrote in message news:1163408290.886457.14770@.h54g2000cwb.googlegroups.co
m...
> Hi
>
> I hava a following piece of code:
>
> CREATE TABLE #s(s varchar)
> INSERT INTO #s VALUES ('a')
> INSERT INTO #s VALUES ('b')
> INSERT INTO #s VALUES ('c')
>
> CREATE TABLE #d(s varchar, d int)
>
> INSERT INTO #d SELECT s, ISNULL((SELECT MAX(d) + 1 FROM #d), 0) FROM #s
>
> SELECT d FROM #d
> DROP TABLE #d
> DROP TABLE #s
>
> As you can see I'm trying to copy data from the source table to the
> destination one, and while doing so, assign each row a subsequent
> number (d) starting from 0.
>
> I assumed, that the SELECT clause will be run for each inserted row -
> however - when I run this code as the result I receive only zeros in
> the d column for all rows.
>
> I've already tried WITH (NOLOCK/ROWLOCK) clauses to no avail.
>
> Is there any way to get this query running?
>
> Thanks
> Szymon
>|||I can't use IDENTITY because the problem is a bit more complex that I
might have described it at the beginning of this thread. Here's a bit
less abstract story:
I have a table with about 150 000 rows, which holds geographical
coordinates of a certain GPS receiver (placed in a vehicle) in a
certain moment. The table structure is as follows:
IdCoordinates int IDENTITY PRIMARY KEY
CarId nvarchar(50)
Longitude float
Latitude float
Date datetime (indexed)
Quite often I need to derive the speed of a car throughout a day,
basing on this data. This involves finding for each row in a day the
preceeding one (to calculate the distance driven and time elapsed
between the two). The simplest way is to construct a query basing on
NOT EXISTS operator but this prooves itself to be terribly slow.
So I figured out I'd add to the table additional column, SequenceNo
int, which for a certain row would hold number of all rows gathered
from the same car with dates lesser the the row's date. This makes the
query both simple and efficient.
However - with 150 000 rows (and very soon I expect it to be over 500
000) - adding this column requires first to properly update the
SequenceNo field for all rows.
First I thought that simple UPDATE Coordinates SET SequenceNo = SELECT
MAX ... would do, but this query sets all values to 1 (providing all
the values are NULL at the beginning) - apparently some row/table
locking issue (is it?).
Then I tried to create something like Oracle's sequence that would
return next sequence number for the given car id, but this requires one
of the following:
- SELECT trigger (I'd create it on a Sequence(CarId, NextSequenceNo)
table )
- UDF (so I can use it in the SET clause OF the UPDATE query) that can
issue DML statements (so it can remember current seqence number for a
certain car and return the next one)
- SP (they can ofcourse use DML) that can return value and be used in a
SET clause
AFAIK none of these exist in SQL Server 2000 (though there's a hack
somewhere on the web to create a SELECT trigger). So now I've ended up
with a SP that simply reads all data from the Coordinates table with a
cursor (the data being read must be ordered by date), fetches next
available SequenceNo from a temp table and inserts it to another temp
table with the SequenceNo field set properly (I know that updating the
source table would be more appropriate here but it's very slow).
It's dirty, slow and resource consuming - my best solution so far is to
use an update curosor and update the row in place with WHERE CURRENT OF
clause - but apparently SQL Server 2000 doesn't support curors with
ORDER BY and FOR UPDATE clauses put together, so this works only for
SQL Server 2005.
I'd appreciate any suggestions on how to solve this nicely.
Szymon
Arnie Rowland napisal(a):
> Why not add an IDENTITY column to table #s?
> CREATE TABLE #s
> ( RowID int IDENTITY,
> s varchar(20)
> )
> INSERT INTO #s VALUES ('a')
> INSERT INTO #s VALUES ('b')
> INSERT INTO #s VALUES ('c')
> --
> 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 th
e top yourself.
> - H. Norman Schwarzkopf
>|||Sorry - I CAN use ordered update cursors with SQL 2000 - I simply
forgot to add PK to the table and got misleading error message.
If anyone knows a better solution to the problem then the one with
update cursor I'd appreciate suggestions.
Szymon
joozeq@.gmail.com napisal(a):[vbcol=seagreen]
> I can't use IDENTITY because the problem is a bit more complex that I
> might have described it at the beginning of this thread. Here's a bit
> less abstract story:
> I have a table with about 150 000 rows, which holds geographical
> coordinates of a certain GPS receiver (placed in a vehicle) in a
> certain moment. The table structure is as follows:
> IdCoordinates int IDENTITY PRIMARY KEY
> CarId nvarchar(50)
> Longitude float
> Latitude float
> Date datetime (indexed)
> Quite often I need to derive the speed of a car throughout a day,
> basing on this data. This involves finding for each row in a day the
> preceeding one (to calculate the distance driven and time elapsed
> between the two). The simplest way is to construct a query basing on
> NOT EXISTS operator but this prooves itself to be terribly slow.
> So I figured out I'd add to the table additional column, SequenceNo
> int, which for a certain row would hold number of all rows gathered
> from the same car with dates lesser the the row's date. This makes the
> query both simple and efficient.
> However - with 150 000 rows (and very soon I expect it to be over 500
> 000) - adding this column requires first to properly update the
> SequenceNo field for all rows.
> First I thought that simple UPDATE Coordinates SET SequenceNo = SELECT
> MAX ... would do, but this query sets all values to 1 (providing all
> the values are NULL at the beginning) - apparently some row/table
> locking issue (is it?).
> Then I tried to create something like Oracle's sequence that would
> return next sequence number for the given car id, but this requires one
> of the following:
> - SELECT trigger (I'd create it on a Sequence(CarId, NextSequenceNo)
> table )
> - UDF (so I can use it in the SET clause OF the UPDATE query) that can
> issue DML statements (so it can remember current seqence number for a
> certain car and return the next one)
> - SP (they can ofcourse use DML) that can return value and be used in a
> SET clause
> AFAIK none of these exist in SQL Server 2000 (though there's a hack
> somewhere on the web to create a SELECT trigger). So now I've ended up
> with a SP that simply reads all data from the Coordinates table with a
> cursor (the data being read must be ordered by date), fetches next
> available SequenceNo from a temp table and inserts it to another temp
> table with the SequenceNo field set properly (I know that updating the
> source table would be more appropriate here but it's very slow).
> It's dirty, slow and resource consuming - my best solution so far is to
> use an update curosor and update the row in place with WHERE CURRENT OF
> clause - but apparently SQL Server 2000 doesn't support curors with
> ORDER BY and FOR UPDATE clauses put together, so this works only for
> SQL Server 2005.
> I'd appreciate any suggestions on how to solve this nicely.
> Szymon
> Arnie Rowland napisal(a):|||Perhaps one of these articles may help:
Row Number (or Rank) from a SELECT Transact-SQL statement (includes Paging)
http://support.microsoft.com/defaul...kb;en-us;186133
http://sqljunkies.com/WebLog/amacha...11/03/4945.aspx
http://www.projectdmx.com/tsql/ranking.aspx
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
<joozeq@.gmail.com> wrote in message
news:1164136011.455451.90600@.b28g2000cwb.googlegroups.com...
>I can't use IDENTITY because the problem is a bit more complex that I
> might have described it at the beginning of this thread. Here's a bit
> less abstract story:
> I have a table with about 150 000 rows, which holds geographical
> coordinates of a certain GPS receiver (placed in a vehicle) in a
> certain moment. The table structure is as follows:
> IdCoordinates int IDENTITY PRIMARY KEY
> CarId nvarchar(50)
> Longitude float
> Latitude float
> Date datetime (indexed)
> Quite often I need to derive the speed of a car throughout a day,
> basing on this data. This involves finding for each row in a day the
> preceeding one (to calculate the distance driven and time elapsed
> between the two). The simplest way is to construct a query basing on
> NOT EXISTS operator but this prooves itself to be terribly slow.
> So I figured out I'd add to the table additional column, SequenceNo
> int, which for a certain row would hold number of all rows gathered
> from the same car with dates lesser the the row's date. This makes the
> query both simple and efficient.
> However - with 150 000 rows (and very soon I expect it to be over 500
> 000) - adding this column requires first to properly update the
> SequenceNo field for all rows.
> First I thought that simple UPDATE Coordinates SET SequenceNo = SELECT
> MAX ... would do, but this query sets all values to 1 (providing all
> the values are NULL at the beginning) - apparently some row/table
> locking issue (is it?).
> Then I tried to create something like Oracle's sequence that would
> return next sequence number for the given car id, but this requires one
> of the following:
> - SELECT trigger (I'd create it on a Sequence(CarId, NextSequenceNo)
> table )
> - UDF (so I can use it in the SET clause OF the UPDATE query) that can
> issue DML statements (so it can remember current seqence number for a
> certain car and return the next one)
> - SP (they can ofcourse use DML) that can return value and be used in a
> SET clause
> AFAIK none of these exist in SQL Server 2000 (though there's a hack
> somewhere on the web to create a SELECT trigger). So now I've ended up
> with a SP that simply reads all data from the Coordinates table with a
> cursor (the data being read must be ordered by date), fetches next
> available SequenceNo from a temp table and inserts it to another temp
> table with the SequenceNo field set properly (I know that updating the
> source table would be more appropriate here but it's very slow).
> It's dirty, slow and resource consuming - my best solution so far is to
> use an update curosor and update the row in place with WHERE CURRENT OF
> clause - but apparently SQL Server 2000 doesn't support curors with
> ORDER BY and FOR UPDATE clauses put together, so this works only for
> SQL Server 2005.
> I'd appreciate any suggestions on how to solve this nicely.
> Szymon
> Arnie Rowland napisal(a):
>

REFERENCEing two columns to the same key

Hi

I'm just wondering whether SQL Server will let me link two columns in one table to the same column in another table. Basically, my table looks like this:

BOOKINGS
-----
BookingID (PK)
BorrowerID (FK1)
ModuleID (FK2)
LecturerID (FK3)
CollectionDateTime
ReturnDateTime
Authorised
TakenUp

FK1 and FK3 are related to BorrowerID - both lecturers and students are stored in the same table because lecturers are able to book and borrow equipment as well as students. Will SQL Server let me create a Foreign Key on BorrowerID and LecturerID that both point to Borrowers.BorrowerID?

My understanding of Foreign Keys is that they're just a way of enforcing referential integrity, so they'll only come into play when data is being deleted that might cause orphaned rows in the dependent table. So there shouldn't be too much of a problem with this, is that right?

Thanks
JonYes, absolutely. SQL Server will let you link FK1 and FK3 to the same table. However, if you want to enforce referential integrity systematically (ie cascading updates and deletes), you will only be able to do so on one of the foreign keys. With the other foreign key you will have to use a trigger. This is one of SQL's annoying "personalities" .

Terri|||Cool - thanks for your reply

You might be able to tell, but I'm quite a SQL novice. I'm guessing the trigger would be in effect on INSERTs and would check that one of the Foreign Keys is a valid value? Would it need to be effective on anything else? I guess UPDATES, but what about DELETES?

Great, now I have to learn triggers!!!

Cheers
Jon|||This KB article describes the problem you will encounter:PRB: Error Message 1785 Occurs When You Create a FOREIGN KEY Constraint That May Cause Multiple Cascade Paths

So, in your case you cannot use what is referred to as Declarative Referential Integrity (DRI).

This is an area where I am not particularly knowledgeable. I have not used DRI nor triggers for referential integrity. For better or worse, the stored procedure doing the data modification is where I have put such code.

Terri

Redundant Indexes?

Hi
I would get rid or A and B. If there is no clustered index on that table,
make C clustered too, assuming it has good selectivity.
Without the data types, it is not possible to tell and be 100% sure (your
attachment gets blocked by Outlook Express)
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Jorge Luzarraga Castro" <jluzarraga@.fidens.cl> wrote in message
news:eM97v2b6EHA.2032@.tk2msftngp13.phx.gbl...
> Hello,
>
> Ive got a table called 'prd_movcomision' (Ive attached a script with its
> structure) which has several indexes as you can see. There are several
> indexes which are contained in others. For example:
> CREATE INDEX [A] ON [dbo].[prd_movcomision]([pers_rutcia]
, [poli_codigo],
> [itpo_codigo]) ON [PRIMARY]
> GO
> CREATE INDEX [B] ON [dbo].[prd_movcomision]([poli_codigo],[/vbco
l]
[itpo_codigo])[vbcol=seagreen]
> ON [PRIMARY]
> GO
> CREATE INDEX [C] ON [dbo].[prd_movcomision]([prop_codigo
]) ON [PRIMARY]
> GO
>
> Given the previous indexes, [B] is contained within [A] and &#
91;C] is
> contained within [B]. The question is if this is really neccessary or
once
> I have an index A the other indexes (B and C) are redundant. Im having
too
> many problems with the size of the database, it is growing too fast.
>
> Any advice on this would be very much appreciated.
>
> Jorge Luzarraga
>
>Hello Mike,
Are you talking about getting rid of the one which contains the others? Any
help is very much appreciated.
this is the script:
****************************************
******************************
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[prd_movcomision]') and OBJECTPROPERTY(id, N'IsUse
rTable')
= 1)
drop table [dbo].[prd_movcomision]
GO
CREATE TABLE [dbo].[prd_movcomision] (
[movc_codigo] [numeric](18, 0) NOT NULL ,
[movc_fecmov] [datetime] NOT NULL ,
[pers_rutcia] [numeric](9, 0) NULL ,
[infi_codigo] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NUL
L ,
[doco_numero] [numeric](18, 0) NULL ,
[poli_codigo] [numeric](18, 0) NULL ,
[itpo_codigo] [numeric](18, 0) NULL ,
[esta_codigo] [numeric](4, 0) NULL ,
[mone_codigo] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NU
LL ,
[prop_codigo] [numeric](18, 0) NULL ,
[itpr_codigo] [numeric](18, 0) NULL ,
[cone_codigo] [numeric](18, 0) NULL ,
[prop_fecdocum] [datetime] NULL ,
[prop_fecemision] [datetime] NULL ,
[prop_fecinicio] [datetime] NULL ,
[itpr_fecinicio] [datetime] NULL ,
[poli_fecemision] [datetime] NULL ,
[poli_fecinicio] [datetime] NULL ,
[itpo_fecinicio] [datetime] NULL ,
[movc_concepto] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[movc_tipointerm] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NU
LL ,
[movc_periodo] [numeric](6, 0) NULL ,
[movc_motnopago] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_A
S NULL
,
[movc_indcriterio] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS N
ULL ,
[movc_tipo] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[movc_unidadcom] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NUL
L ,
[movc_fijo] [numeric](18, 4) NULL ,
[movc_variable] [numeric](7, 3) NULL ,
[movc_prima] [numeric](18, 4) NULL ,
[movc_montomo] [numeric](18, 4) NOT NULL ,
[movc_montomc] [numeric](18, 4) NULL ,
[movc_fecvalor] [datetime] NULL ,
[movc_feccierre] [datetime] NULL ,
[movc_observacion] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI
_AS
NULL ,
[movc_tiporango] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NUL
L ,
[cier_tipo] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[cier_correlativo] [numeric](18, 0) NULL ,
[cuot_codigo] [numeric](18, 0) NULL ,
[cuot_numero] [numeric](4, 0) NULL ,
[movc_fecmeta] [datetime] NULL ,
[movc_primaneta] [t_dom_monto] NULL ,
[movc_fecpagocomis] [t_dom_fecha] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[prd_movcomision] WITH NOCHECK ADD
CONSTRAINT [pk_movcomision] PRIMARY KEY CLUSTERED
(
[movc_codigo]
) ON [PRIMARY]
GO
CREATE INDEX [AK_prd_movcomision] ON
[dbo].[prd_movcomision]([movc_tipointerm], [pers_rutcia], &#
91;movc_concepto],
[cone_codigo], [poli_codigo]) ON [PRIMARY]
GO
CREATE INDEX [Ai_prd_movcomision2] ON
[dbo].[prd_movcomision]([pers_rutcia], [poli_codigo], [i
tpo_codigo]) ON
[PRIMARY]
GO
CREATE INDEX [IX_prd_movcomision] ON
[dbo].[prd_movcomision]([poli_codigo], [itpo_codigo]) ON
1;PRIMARY]
GO
CREATE INDEX [IX_prd_movcomision1] ON
[dbo].[prd_movcomision]([prop_codigo]) ON [PRIMARY]
GO
CREATE INDEX [IX_prd_movcomision2] ON
[dbo].[prd_movcomision]([poli_codigo]) ON [PRIMARY]
GO
/****** The index created by the following statement is for internal use
only. ******/
/****** It is not a real index but exists as statistics only. ******/
if (@.@.microsoftversion > 0x07000000 )
EXEC ('CREATE STATISTICS [Statistic_movc_fecpagocomis] ON
[dbo].[prd_movcomision] ([movc_fecpagocomis]) ')
GO
ALTER TABLE [dbo].[prd_movcomision] ADD
CONSTRAINT [fk_r_cone_movcom] FOREIGN KEY
(
[cone_codigo]
) REFERENCES [dbo].[prd_comnegocio] (
[cone_codigo]
),
CONSTRAINT [fk_r_cuot_movcom] FOREIGN KEY
(
[cuot_codigo],
[cuot_numero]
) REFERENCES [dbo].[prd_cuota] (
[cuot_codigo],
[cuot_numero]
),
CONSTRAINT [fk_r_doccom_movcom] FOREIGN KEY
(
[pers_rutcia],
[infi_codigo],
[doco_numero]
) REFERENCES [dbo].[prd_doccomision] (
[pers_rutcia],
[infi_codigo],
[doco_numero]
),
CONSTRAINT [fk_r_est_movcom] FOREIGN KEY
(
[esta_codigo]
) REFERENCES [dbo].[adm_estado] (
[esta_codigo]
),
CONSTRAINT [fk_r_estcier_movcom] FOREIGN KEY
(
[movc_periodo],
[cier_tipo],
[cier_correlativo]
) REFERENCES [dbo].[prd_estadocierre] (
[cier_periodo],
[cier_tipo],
[cier_correlativo]
),
CONSTRAINT [fk_r_itmpr_movcom] FOREIGN KEY
(
[prop_codigo],
[itpr_codigo]
) REFERENCES [dbo].[vta_itmpropuesta] (
[prop_codigo],
[itpr_codigo]
),
CONSTRAINT [fk_r_mnd_movcom] FOREIGN KEY
(
[mone_codigo]
) REFERENCES [dbo].[adm_moneda] (
[mone_codigo]
)
GO
alter table [dbo].[prd_movcomision] nocheck constraint [fk_r_est
_movcom]
GO
alter table [dbo].[prd_movcomision] nocheck constraint [fk_r_est
cier_movcom]
GO
alter table [dbo].[prd_movcomision] nocheck constraint [fk_r_itm
pr_movcom]
GO
alter table [dbo].[prd_movcomision] nocheck constraint [fk_r_mnd
_movcom]
GO
****************************************
*************************
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> escribi en el mensaje
news:ODK5Ric6EHA.1392@.tk2msftngp13.phx.gbl...
> Hi
> I would get rid or A and B. If there is no clustered index on that table,
> make C clustered too, assuming it has good selectivity.
> Without the data types, it is not possible to tell and be 100% sure (your
> attachment gets blocked by Outlook Express)
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Jorge Luzarraga Castro" <jluzarraga@.fidens.cl> wrote in message
> news:eM97v2b6EHA.2032@.tk2msftngp13.phx.gbl...
> [itpo_codigo])
> once
> too
>|||Hi
All those columns are nullable, so making a compound index (multiple
columns) is not a good idea. From my experience, the query processor will
think of them as 2nd choice.
Having 1 index on poli_codigo and one on poli_codigo will cover your
queries, and will improve Insert/Update performance as compound index add a
lot to index maintenance.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Jorge Luzarraga Castro" <jluzarraga@.fidens.cl> wrote in message
news:u0fD$1c6EHA.208@.TK2MSFTNGP12.phx.gbl...
> Hello Mike,
> Are you talking about getting rid of the one which contains the others?
Any
> help is very much appreciated.
> this is the script:
> ****************************************
******************************
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[prd_movcomision]') and OBJECTPROPERTY(id,
N'IsUserTable')
> = 1)
> drop table [dbo].[prd_movcomision]
> GO
> CREATE TABLE [dbo].[prd_movcomision] (
> [movc_codigo] [numeric](18, 0) NOT NULL ,
> [movc_fecmov] [datetime] NOT NULL ,
> [pers_rutcia] [numeric](9, 0) NULL ,
> [infi_codigo] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
> [doco_numero] [numeric](18, 0) NULL ,
> [poli_codigo] [numeric](18, 0) NULL ,
> [itpo_codigo] [numeric](18, 0) NULL ,
> [esta_codigo] [numeric](4, 0) NULL ,
> [mone_codigo] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
> [prop_codigo] [numeric](18, 0) NULL ,
> [itpr_codigo] [numeric](18, 0) NULL ,
> [cone_codigo] [numeric](18, 0) NULL ,
> [prop_fecdocum] [datetime] NULL ,
> [prop_fecemision] [datetime] NULL ,
> [prop_fecinicio] [datetime] NULL ,
> [itpr_fecinicio] [datetime] NULL ,
> [poli_fecemision] [datetime] NULL ,
> [poli_fecinicio] [datetime] NULL ,
> [itpo_fecinicio] [datetime] NULL ,
> [movc_concepto] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS N
ULL ,
> [movc_tipointerm] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
> [movc_periodo] [numeric](6, 0) NULL ,
> [movc_motnopago] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS[/vb
col]
NULL[vbcol=seagreen]
> ,
> [movc_indcriterio] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_A
S NULL ,
> [movc_tipo] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
> [movc_unidadcom] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
> [movc_fijo] [numeric](18, 4) NULL ,
> [movc_variable] [numeric](7, 3) NULL ,
> [movc_prima] [numeric](18, 4) NULL ,
> [movc_montomo] [numeric](18, 4) NOT NULL ,
> [movc_montomc] [numeric](18, 4) NULL ,
> [movc_fecvalor] [datetime] NULL ,
> [movc_feccierre] [datetime] NULL ,
> [movc_observacion] [varchar] (255) COLLATE SQL_Latin1_General_CP1
_CI_AS
> NULL ,
> [movc_tiporango] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
> [cier_tipo] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
> [cier_correlativo] [numeric](18, 0) NULL ,
> [cuot_codigo] [numeric](18, 0) NULL ,
> [cuot_numero] [numeric](4, 0) NULL ,
> [movc_fecmeta] [datetime] NULL ,
> [movc_primaneta] [t_dom_monto] NULL ,
> [movc_fecpagocomis] [t_dom_fecha] NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[prd_movcomision] WITH NOCHECK ADD
> CONSTRAINT [pk_movcomision] PRIMARY KEY CLUSTERED
> (
> [movc_codigo]
> ) ON [PRIMARY]
> GO
> CREATE INDEX [AK_prd_movcomision] ON
> [dbo].[prd_movcomision]([movc_tipointerm], [pers_rutcia],
[movc_concepto],
> [cone_codigo], [poli_codigo]) ON [PRIMARY]
> GO
> CREATE INDEX [Ai_prd_movcomision2] ON
> [dbo].[prd_movcomision]([pers_rutcia], [poli_codigo], [
;itpo_codigo]) ON
> [PRIMARY]
> GO
> CREATE INDEX [IX_prd_movcomision] ON
> [dbo].[prd_movcomision]([poli_codigo], [itpo_codigo]) ON &
#91;PRIMARY]
> GO
> CREATE INDEX [IX_prd_movcomision1] ON
> [dbo].[prd_movcomision]([prop_codigo]) ON [PRIMARY]
> GO
> CREATE INDEX [IX_prd_movcomision2] ON
> [dbo].[prd_movcomision]([poli_codigo]) ON [PRIMARY]
> GO
> /****** The index created by the following statement is for internal use
> only. ******/
> /****** It is not a real index but exists as statistics only. ******/
> if (@.@.microsoftversion > 0x07000000 )
> EXEC ('CREATE STATISTICS [Statistic_movc_fecpagocomis] ON
> [dbo].[prd_movcomision] ([movc_fecpagocomis]) ')
> GO
> ALTER TABLE [dbo].[prd_movcomision] ADD
> CONSTRAINT [fk_r_cone_movcom] FOREIGN KEY
> (
> [cone_codigo]
> ) REFERENCES [dbo].[prd_comnegocio] (
> [cone_codigo]
> ),
> CONSTRAINT [fk_r_cuot_movcom] FOREIGN KEY
> (
> [cuot_codigo],
> [cuot_numero]
> ) REFERENCES [dbo].[prd_cuota] (
> [cuot_codigo],
> [cuot_numero]
> ),
> CONSTRAINT [fk_r_doccom_movcom] FOREIGN KEY
> (
> [pers_rutcia],
> [infi_codigo],
> [doco_numero]
> ) REFERENCES [dbo].[prd_doccomision] (
> [pers_rutcia],
> [infi_codigo],
> [doco_numero]
> ),
> CONSTRAINT [fk_r_est_movcom] FOREIGN KEY
> (
> [esta_codigo]
> ) REFERENCES [dbo].[adm_estado] (
> [esta_codigo]
> ),
> CONSTRAINT [fk_r_estcier_movcom] FOREIGN KEY
> (
> [movc_periodo],
> [cier_tipo],
> [cier_correlativo]
> ) REFERENCES [dbo].[prd_estadocierre] (
> [cier_periodo],
> [cier_tipo],
> [cier_correlativo]
> ),
> CONSTRAINT [fk_r_itmpr_movcom] FOREIGN KEY
> (
> [prop_codigo],
> [itpr_codigo]
> ) REFERENCES [dbo].[vta_itmpropuesta] (
> [prop_codigo],
> [itpr_codigo]
> ),
> CONSTRAINT [fk_r_mnd_movcom] FOREIGN KEY
> (
> [mone_codigo]
> ) REFERENCES [dbo].[adm_moneda] (
> [mone_codigo]
> )
> GO
> alter table [dbo].[prd_movcomision] nocheck constraint [fk_r_e
st_movcom]
> GO
> alter table [dbo].[prd_movcomision] nocheck constraint
& #91;fk_r_estcier_movcom]
> GO
> alter table [dbo].[prd_movcomision] nocheck constraint [fk_r_i
tmpr_movcom]
> GO
> alter table [dbo].[prd_movcomision] nocheck constraint [fk_r_m
nd_movcom]
> GO
> ****************************************
*************************
> "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> escribi en el mensaje
> news:ODK5Ric6EHA.1392@.tk2msftngp13.phx.gbl...
table,[vbcol=seagreen]
(your[vbcol=seagreen]
[PRIMARY][vbcol=seagreen]
>|||It looks like Mike has this handled, but I'd like to interject here.
First, histogram statistics are only stored for the first attribute in any
index, composite or not. That means the QA selectivity evaluation is based
on the leading attribute...alone, regardless if it is singlet or composite.
Second, since SQL Server version 7.0, the Query Optimizer can use index
intersection to make better search conditions...viz., you are not limited to
using a single index in a query expression. Either an index is highly
selective, or it is not. Having multiple single-attribute indexes is more
desirble than fewer composite indexes. And, never should you require
overlapping indexes; SQL Server will only choose one based on selectivity,
the others would only be a waste of space.
That being said, after a set of well chosen, single-attribute indexes have
been created, and every table--and I mean EVERY table--should have a
clustered index defined, then a few--VERY FEW--customized, query targeted,
mission-critical, composite indexes can be built, but only after proving the
multiple single-attribute indexes are not producing sufficient response.
Everything else will only produce storage and processing overhead whenever
you do CRUD or reorg operations.
Sincerely,
Anthony Thomas
"Jorge Luzarraga Castro" <jluzarraga@.fidens.cl> wrote in message
news:u0fD$1c6EHA.208@.TK2MSFTNGP12.phx.gbl...
Hello Mike,
Are you talking about getting rid of the one which contains the others? Any
help is very much appreciated.
this is the script:
****************************************
******************************
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[prd_movcomision]') and OBJECTPROPERTY(id, N'IsUse
rTable')
= 1)
drop table [dbo].[prd_movcomision]
GO
CREATE TABLE [dbo].[prd_movcomision] (
[movc_codigo] [numeric](18, 0) NOT NULL ,
[movc_fecmov] [datetime] NOT NULL ,
[pers_rutcia] [numeric](9, 0) NULL ,
[infi_codigo] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NUL
L ,
[doco_numero] [numeric](18, 0) NULL ,
[poli_codigo] [numeric](18, 0) NULL ,
[itpo_codigo] [numeric](18, 0) NULL ,
[esta_codigo] [numeric](4, 0) NULL ,
[mone_codigo] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NU
LL ,
[prop_codigo] [numeric](18, 0) NULL ,
[itpr_codigo] [numeric](18, 0) NULL ,
[cone_codigo] [numeric](18, 0) NULL ,
[prop_fecdocum] [datetime] NULL ,
[prop_fecemision] [datetime] NULL ,
[prop_fecinicio] [datetime] NULL ,
[itpr_fecinicio] [datetime] NULL ,
[poli_fecemision] [datetime] NULL ,
[poli_fecinicio] [datetime] NULL ,
[itpo_fecinicio] [datetime] NULL ,
[movc_concepto] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[movc_tipointerm] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NU
LL ,
[movc_periodo] [numeric](6, 0) NULL ,
[movc_motnopago] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_A
S NULL
,
[movc_indcriterio] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS N
ULL ,
[movc_tipo] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[movc_unidadcom] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NUL
L ,
[movc_fijo] [numeric](18, 4) NULL ,
[movc_variable] [numeric](7, 3) NULL ,
[movc_prima] [numeric](18, 4) NULL ,
[movc_montomo] [numeric](18, 4) NOT NULL ,
[movc_montomc] [numeric](18, 4) NULL ,
[movc_fecvalor] [datetime] NULL ,
[movc_feccierre] [datetime] NULL ,
[movc_observacion] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI
_AS
NULL ,
[movc_tiporango] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NUL
L ,
[cier_tipo] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[cier_correlativo] [numeric](18, 0) NULL ,
[cuot_codigo] [numeric](18, 0) NULL ,
[cuot_numero] [numeric](4, 0) NULL ,
[movc_fecmeta] [datetime] NULL ,
[movc_primaneta] [t_dom_monto] NULL ,
[movc_fecpagocomis] [t_dom_fecha] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[prd_movcomision] WITH NOCHECK ADD
CONSTRAINT [pk_movcomision] PRIMARY KEY CLUSTERED
(
[movc_codigo]
) ON [PRIMARY]
GO
CREATE INDEX [AK_prd_movcomision] ON
[dbo].[prd_movcomision]([movc_tipointerm], [pers_rutcia], &#
91;movc_concepto],
[cone_codigo], [poli_codigo]) ON [PRIMARY]
GO
CREATE INDEX [Ai_prd_movcomision2] ON
[dbo].[prd_movcomision]([pers_rutcia], [poli_codigo], [i
tpo_codigo]) ON
[PRIMARY]
GO
CREATE INDEX [IX_prd_movcomision] ON
[dbo].[prd_movcomision]([poli_codigo], [itpo_codigo]) ON
1;PRIMARY]
GO
CREATE INDEX [IX_prd_movcomision1] ON
[dbo].[prd_movcomision]([prop_codigo]) ON [PRIMARY]
GO
CREATE INDEX [IX_prd_movcomision2] ON
[dbo].[prd_movcomision]([poli_codigo]) ON [PRIMARY]
GO
/****** The index created by the following statement is for internal use
only. ******/
/****** It is not a real index but exists as statistics only. ******/
if (@.@.microsoftversion > 0x07000000 )
EXEC ('CREATE STATISTICS [Statistic_movc_fecpagocomis] ON
[dbo].[prd_movcomision] ([movc_fecpagocomis]) ')
GO
ALTER TABLE [dbo].[prd_movcomision] ADD
CONSTRAINT [fk_r_cone_movcom] FOREIGN KEY
(
[cone_codigo]
) REFERENCES [dbo].[prd_comnegocio] (
[cone_codigo]
),
CONSTRAINT [fk_r_cuot_movcom] FOREIGN KEY
(
[cuot_codigo],
[cuot_numero]
) REFERENCES [dbo].[prd_cuota] (
[cuot_codigo],
[cuot_numero]
),
CONSTRAINT [fk_r_doccom_movcom] FOREIGN KEY
(
[pers_rutcia],
[infi_codigo],
[doco_numero]
) REFERENCES [dbo].[prd_doccomision] (
[pers_rutcia],
[infi_codigo],
[doco_numero]
),
CONSTRAINT [fk_r_est_movcom] FOREIGN KEY
(
[esta_codigo]
) REFERENCES [dbo].[adm_estado] (
[esta_codigo]
),
CONSTRAINT [fk_r_estcier_movcom] FOREIGN KEY
(
[movc_periodo],
[cier_tipo],
[cier_correlativo]
) REFERENCES [dbo].[prd_estadocierre] (
[cier_periodo],
[cier_tipo],
[cier_correlativo]
),
CONSTRAINT [fk_r_itmpr_movcom] FOREIGN KEY
(
[prop_codigo],
[itpr_codigo]
) REFERENCES [dbo].[vta_itmpropuesta] (
[prop_codigo],
[itpr_codigo]
),
CONSTRAINT [fk_r_mnd_movcom] FOREIGN KEY
(
[mone_codigo]
) REFERENCES [dbo].[adm_moneda] (
[mone_codigo]
)
GO
alter table [dbo].[prd_movcomision] nocheck constraint [fk_r_est
_movcom]
GO
alter table [dbo].[prd_movcomision] nocheck constraint [fk_r_est
cier_movcom]
GO
alter table [dbo].[prd_movcomision] nocheck constraint [fk_r_itm
pr_movcom]
GO
alter table [dbo].[prd_movcomision] nocheck constraint [fk_r_mnd
_movcom]
GO
****************************************
*************************
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> escribi en el mensaje
news:ODK5Ric6EHA.1392@.tk2msftngp13.phx.gbl...
> Hi
> I would get rid or A and B. If there is no clustered index on that table,
> make C clustered too, assuming it has good selectivity.
> Without the data types, it is not possible to tell and be 100% sure (your
> attachment gets blocked by Outlook Express)
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Jorge Luzarraga Castro" <jluzarraga@.fidens.cl> wrote in message
> news:eM97v2b6EHA.2032@.tk2msftngp13.phx.gbl...
> [itpo_codigo])
> once
> too
>|||Jorge Luzarraga Castro (jluzarraga@.fidens.cl) writes:
> Ive got a table called 'prd_movcomision' (Ive attached a script with its
> structure) which has several indexes as you can see. There are several
> indexes which are contained in others. For example:
> CREATE INDEX [A] ON [dbo].[prd_movcomision]([pers_rutcia]
, [poli_codigo],
> [itpo_codigo]) ON [PRIMARY]
> GO
> CREATE INDEX [B] ON [dbo].[prd_movcomision]([poli_codigo
],
> [itpo_codigo])
> ON [PRIMARY]
> GO
> CREATE INDEX [C] ON [dbo].[prd_movcomision]([prop_codigo
]) ON [PRIMARY]
> GO
>
> Given the previous indexes, [B] is contained within [A] and &#
91;C] is
> contained within [B]. The question is if this is really neccessary or
> once I have an index A the other indexes (B and C) are redundant.
There are no redudant indexes in your table. There may be unused indexes,
that I can't tell, because that depends on your queries.
While the columns index B is included in index A, a query like:
SELECT * FROM tbl WHERE poli_coidgo = @.val1 AND itpo_codigo = @.val2
cannot seek index A to find any rows. It can scan the index, though.
The difference here is that "seek" means that SQL Server finds the
value by walking the fast way through the index tree. "scan" means
that SQL Server searches all leaf pages of the index, which is considerably
slower. In reality, SQL Server will probably scan the entire table instead,
which is even slower. (As a scan. Beside the scanning the index, SQL Server
will also have to access data pages, and with many hits, that can be more
data-pages access than a table scan.)
The same goes for index C, assuming that you mean to have itpo_codigo hear.
So the important issue here, is whether you actually have any queries for
which these indexes are useful.

> Im having too many problems with the size of the database, it is
> growing too fast.
I don't think dropping indexes is a very good help to address that. In
such case it's better to examine whether there is data that is inserted
multiple times, data which is not removed as it should be etc.
Dropping an index can conserve some space - but it can also kill
performance, if there is a query which needs the index.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Redundancy requiring Identity columns on Subscriber - please help

Hi
I am just getting up to speed on replication so please forgive me if I
am missing something simple here......
Scenario
Application server (call it Test) that has SQL Server 2000 database
I am setting up replication for backup pupopses
(If Server A dies - point Application (Test) to Server B )
My Problem -
Many tables in the database have the identiy column set to YES
As the TEST operates, many rows uniquely added to the db using Identity
column as PKeys
I was using snapshot replication for test purposes....(no license for
Transactional)
On subscriber - created blank DB with YES (Not for Replication) option
set for the tables in question.
The Snapshot I created from Publisher, when I push to the subscriber -
it resets the Identify (YES - NFR) back to No
I redireecte dteh Aopplication server to Server B an dit fails to work
corrcectly.- cannot insert into row s where Identity column is set to
No
Identify needs to be set to YES (or YES not for Repl) in order for my
application server to work correctly.
Any ideas on how I could best achieve this?
Thanks in advance........
Kevha
right click on your publication, select properties, in the articles tab
select the browse button to the right of your tables. Click on the snapshot
tab. In the name conflicts section, select the keep delete existing data.
Now return to your subscriber, fix things the way you want them, and then
rereun your snapshot.
It should work this time.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<kevha@.hotmail.com> wrote in message
news:1132178886.227006.217160@.o13g2000cwo.googlegr oups.com...
> Hi
> I am just getting up to speed on replication so please forgive me if I
> am missing something simple here......
> Scenario
> Application server (call it Test) that has SQL Server 2000 database
> I am setting up replication for backup pupopses
> (If Server A dies - point Application (Test) to Server B )
> My Problem -
> Many tables in the database have the identiy column set to YES
> As the TEST operates, many rows uniquely added to the db using Identity
> column as PKeys
> I was using snapshot replication for test purposes....(no license for
> Transactional)
> On subscriber - created blank DB with YES (Not for Replication) option
> set for the tables in question.
> The Snapshot I created from Publisher, when I push to the subscriber -
> it resets the Identify (YES - NFR) back to No
> I redireecte dteh Aopplication server to Server B an dit fails to work
> corrcectly.- cannot insert into row s where Identity column is set to
> No
> Identify needs to be set to YES (or YES not for Repl) in order for my
> application server to work correctly.
> Any ideas on how I could best achieve this?
> Thanks in advance........
> Kevha
>
|||Hi Hilary
I still can't seem to get the problem solved.
Let me take it back a step -
Server A (source)
Server B (subscriber)
I want to replicate server A to server B
I set up Server A with Snapshot Replicationa sPublisher and
Distributor.
Database created on Server A - some tables with Columns set with
Identity = Yes
I set up Snapshot Replication
In the SnapShot Properties/ Articles I set 'Keep Existing Table
unchanged' option on.
I added a custom script in the Snapshot Properties tab that - Drops the
tables, recreates them with the IDENTITY NOT FOR REPLICATION option
included during table creation.
When I conduct the Synchronize - I get the following error -
Violation of Primary Key constraint 'x' insert duplicate key in object
'x'
My replication could happen once an hour (not real time critical so any
option that keeps the Identity filed intact appreciated)