SQL SERVER 2000 & MSDE
We have a commercial app in which the customers will not have any real
computer literate users (software for dummies, who would have thought)
What should I be doing to re-index the database from time to time? There are
around 300 tables.
Does SQL Server take care of this itself or should i have some sort of code
or stored procedure that re-indexes?
TIA
Tim MorrisonCheck fragmentation and index those tables with appreciable fragmentation.
Look up dbcc showcontig in BOL for an example of such a script.
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Tim Morrison" <sales@.NOSPAM_kjmsoftware.com> wrote in message
news:uKYMrDHBHHA.1196@.TK2MSFTNGP02.phx.gbl...
> SQL SERVER 2000 & MSDE
> We have a commercial app in which the customers will not have any real
> computer literate users (software for dummies, who would have thought)
> What should I be doing to re-index the database from time to time? There
> are around 300 tables.
> Does SQL Server take care of this itself or should i have some sort of
> code or stored procedure that re-indexes?
> TIA
> Tim Morrison
>|||Tim Morrison wrote:
> SQL SERVER 2000 & MSDE
> We have a commercial app in which the customers will not have any real
> computer literate users (software for dummies, who would have thought)
> What should I be doing to re-index the database from time to time? There are
> around 300 tables.
> Does SQL Server take care of this itself or should i have some sort of code
> or stored procedure that re-indexes?
> TIA
> Tim Morrison
>
http://realsqlguy.com/serendipity/archives/12-Humpty-Dumpty-Sat-On-A-Wall...html
Tracy McKibben
MCDBA
http://www.realsqlguy.com
Showing posts with label customers. Show all posts
Showing posts with label customers. Show all posts
Monday, March 12, 2012
ReIndexing
SQL SERVER 2000 & MSDE
We have a commercial app in which the customers will not have any real
computer literate users (software for dummies, who would have thought)
What should I be doing to re-index the database from time to time? There are
around 300 tables.
Does SQL Server take care of this itself or should i have some sort of code
or stored procedure that re-indexes?
TIA
Tim Morrison
Check fragmentation and index those tables with appreciable fragmentation.
Look up dbcc showcontig in BOL for an example of such a script.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Tim Morrison" <sales@.NOSPAM_kjmsoftware.com> wrote in message
news:uKYMrDHBHHA.1196@.TK2MSFTNGP02.phx.gbl...
> SQL SERVER 2000 & MSDE
> We have a commercial app in which the customers will not have any real
> computer literate users (software for dummies, who would have thought)
> What should I be doing to re-index the database from time to time? There
> are around 300 tables.
> Does SQL Server take care of this itself or should i have some sort of
> code or stored procedure that re-indexes?
> TIA
> Tim Morrison
>
|||Tim Morrison wrote:
> SQL SERVER 2000 & MSDE
> We have a commercial app in which the customers will not have any real
> computer literate users (software for dummies, who would have thought)
> What should I be doing to re-index the database from time to time? There are
> around 300 tables.
> Does SQL Server take care of this itself or should i have some sort of code
> or stored procedure that re-indexes?
> TIA
> Tim Morrison
>
http://realsqlguy.com/serendipity/archives/12-Humpty-Dumpty-Sat-On-A-Wall...html
Tracy McKibben
MCDBA
http://www.realsqlguy.com
We have a commercial app in which the customers will not have any real
computer literate users (software for dummies, who would have thought)
What should I be doing to re-index the database from time to time? There are
around 300 tables.
Does SQL Server take care of this itself or should i have some sort of code
or stored procedure that re-indexes?
TIA
Tim Morrison
Check fragmentation and index those tables with appreciable fragmentation.
Look up dbcc showcontig in BOL for an example of such a script.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Tim Morrison" <sales@.NOSPAM_kjmsoftware.com> wrote in message
news:uKYMrDHBHHA.1196@.TK2MSFTNGP02.phx.gbl...
> SQL SERVER 2000 & MSDE
> We have a commercial app in which the customers will not have any real
> computer literate users (software for dummies, who would have thought)
> What should I be doing to re-index the database from time to time? There
> are around 300 tables.
> Does SQL Server take care of this itself or should i have some sort of
> code or stored procedure that re-indexes?
> TIA
> Tim Morrison
>
|||Tim Morrison wrote:
> SQL SERVER 2000 & MSDE
> We have a commercial app in which the customers will not have any real
> computer literate users (software for dummies, who would have thought)
> What should I be doing to re-index the database from time to time? There are
> around 300 tables.
> Does SQL Server take care of this itself or should i have some sort of code
> or stored procedure that re-indexes?
> TIA
> Tim Morrison
>
http://realsqlguy.com/serendipity/archives/12-Humpty-Dumpty-Sat-On-A-Wall...html
Tracy McKibben
MCDBA
http://www.realsqlguy.com
ReIndexing
SQL SERVER 2000 & MSDE
We have a commercial app in which the customers will not have any real
computer literate users (software for dummies, who would have thought)
What should I be doing to re-index the database from time to time? There are
around 300 tables.
Does SQL Server take care of this itself or should i have some sort of code
or stored procedure that re-indexes?
TIA
Tim MorrisonCheck fragmentation and index those tables with appreciable fragmentation.
Look up dbcc showcontig in BOL for an example of such a script.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Tim Morrison" <sales@.NOSPAM_kjmsoftware.com> wrote in message
news:uKYMrDHBHHA.1196@.TK2MSFTNGP02.phx.gbl...
> SQL SERVER 2000 & MSDE
> We have a commercial app in which the customers will not have any real
> computer literate users (software for dummies, who would have thought)
> What should I be doing to re-index the database from time to time? There
> are around 300 tables.
> Does SQL Server take care of this itself or should i have some sort of
> code or stored procedure that re-indexes?
> TIA
> Tim Morrison
>|||Tim Morrison wrote:
> SQL SERVER 2000 & MSDE
> We have a commercial app in which the customers will not have any real
> computer literate users (software for dummies, who would have thought)
> What should I be doing to re-index the database from time to time? There a
re
> around 300 tables.
> Does SQL Server take care of this itself or should i have some sort of cod
e
> or stored procedure that re-indexes?
> TIA
> Tim Morrison
>
http://realsqlguy.com/serendipity/a...realsqlguy.com
We have a commercial app in which the customers will not have any real
computer literate users (software for dummies, who would have thought)
What should I be doing to re-index the database from time to time? There are
around 300 tables.
Does SQL Server take care of this itself or should i have some sort of code
or stored procedure that re-indexes?
TIA
Tim MorrisonCheck fragmentation and index those tables with appreciable fragmentation.
Look up dbcc showcontig in BOL for an example of such a script.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Tim Morrison" <sales@.NOSPAM_kjmsoftware.com> wrote in message
news:uKYMrDHBHHA.1196@.TK2MSFTNGP02.phx.gbl...
> SQL SERVER 2000 & MSDE
> We have a commercial app in which the customers will not have any real
> computer literate users (software for dummies, who would have thought)
> What should I be doing to re-index the database from time to time? There
> are around 300 tables.
> Does SQL Server take care of this itself or should i have some sort of
> code or stored procedure that re-indexes?
> TIA
> Tim Morrison
>|||Tim Morrison wrote:
> SQL SERVER 2000 & MSDE
> We have a commercial app in which the customers will not have any real
> computer literate users (software for dummies, who would have thought)
> What should I be doing to re-index the database from time to time? There a
re
> around 300 tables.
> Does SQL Server take care of this itself or should i have some sort of cod
e
> or stored procedure that re-indexes?
> TIA
> Tim Morrison
>
http://realsqlguy.com/serendipity/a...realsqlguy.com
Monday, February 20, 2012
Registered Customers
This problem involves a company that only sells goods to customers that are registered
Two tables are concerned
1. reg_cust cust_id CHAR(6) PRIMARY KEY
2. sale_tran cust_id CHAR(6) REFERENCES reg_cust, sale_date DATE, inv_no INTEGER
A constraint must be applied so that before a record is appended to the sale_tran table a check is made to see if the customer is registered in the reg_cust table. If the customer is not registered then the sale is aborted.
One or more SQL statements need to be written to apply the constraint then to check that it is working
Has anyone got any ideas?? You don't need to do anything for this except create the foreign key constraint. At that point, they will only be able to enter a sale_tran if there is a reg_cust. There is no checking to do. It just works (unless of course you have certain flavors of mySQL).|||1 -- Create the reg_cust table --
CREATE TABLE reg_cust( cust_id CHAR(6) PRIMARY KEY);
2 -- Populate reg_cust --
INSERT INTO reg_cust VALUES('ABC123');
INSERT INTO reg_cust VALUES('DEF456');
INSERT INTO reg_cust VALUES('GHI123');
INSERT INTO reg_cust VALUES('JKL456');
3 -- Display the table --
SELECT * FROM reg_cust;
4 -- So far so good, so create the sale_tran table--
CREATE TABLE sale_tran (cust_id CHAR(6) REFERENCES reg_cust, sale_date DATE, inv_no INTEGER);
5 -- Populate sale_tran --
INSERT INTO sale_tran VALUES('DEF456', DATE('2004-06-15'), 200406123);
INSERT INTO sale_tran VALUES('GHI123', DATE('2004-06-15'), 200406124);
SELECT * FROM sale_tran;
6 -- This is the point where the constraint was requested --
The check could have been included at point 4 but what was needed was an alteration to an existing table
ALTER TABLE sale_tran
ADD CHECK (EXISTS(SELECT cust_id FROM reg_cust WHERE
cust_id = sale_tran.cust_id));
7 -- Alteration was successful - try an invalid entry --
INSERT INTO sale_tran VALUES('XYZ456', DATE('2004-06-15'), 200406125);
8 -- Constraint works, following message is given --
SQLSTATE 23000
[Sybase][ODBC Driver] Integrity constraint violation: Invalid value for column 'cust_id' in table 'sale_tran'
9 -- Try another valid value --
INSERT INTO sale_tran VALUES('JKL456', DATE('2004-06-15'), 200406126);
SELECT * FROM sale_tran;
10 -- Table is displayed as expected Task completed--
Two tables are concerned
1. reg_cust cust_id CHAR(6) PRIMARY KEY
2. sale_tran cust_id CHAR(6) REFERENCES reg_cust, sale_date DATE, inv_no INTEGER
A constraint must be applied so that before a record is appended to the sale_tran table a check is made to see if the customer is registered in the reg_cust table. If the customer is not registered then the sale is aborted.
One or more SQL statements need to be written to apply the constraint then to check that it is working
Has anyone got any ideas?? You don't need to do anything for this except create the foreign key constraint. At that point, they will only be able to enter a sale_tran if there is a reg_cust. There is no checking to do. It just works (unless of course you have certain flavors of mySQL).|||1 -- Create the reg_cust table --
CREATE TABLE reg_cust( cust_id CHAR(6) PRIMARY KEY);
2 -- Populate reg_cust --
INSERT INTO reg_cust VALUES('ABC123');
INSERT INTO reg_cust VALUES('DEF456');
INSERT INTO reg_cust VALUES('GHI123');
INSERT INTO reg_cust VALUES('JKL456');
3 -- Display the table --
SELECT * FROM reg_cust;
4 -- So far so good, so create the sale_tran table--
CREATE TABLE sale_tran (cust_id CHAR(6) REFERENCES reg_cust, sale_date DATE, inv_no INTEGER);
5 -- Populate sale_tran --
INSERT INTO sale_tran VALUES('DEF456', DATE('2004-06-15'), 200406123);
INSERT INTO sale_tran VALUES('GHI123', DATE('2004-06-15'), 200406124);
SELECT * FROM sale_tran;
6 -- This is the point where the constraint was requested --
The check could have been included at point 4 but what was needed was an alteration to an existing table
ALTER TABLE sale_tran
ADD CHECK (EXISTS(SELECT cust_id FROM reg_cust WHERE
cust_id = sale_tran.cust_id));
7 -- Alteration was successful - try an invalid entry --
INSERT INTO sale_tran VALUES('XYZ456', DATE('2004-06-15'), 200406125);
8 -- Constraint works, following message is given --
SQLSTATE 23000
[Sybase][ODBC Driver] Integrity constraint violation: Invalid value for column 'cust_id' in table 'sale_tran'
9 -- Try another valid value --
INSERT INTO sale_tran VALUES('JKL456', DATE('2004-06-15'), 200406126);
SELECT * FROM sale_tran;
10 -- Table is displayed as expected Task completed--
Subscribe to:
Posts (Atom)