Hi. I'm writing delete queries and I have the following question: if a table
has foreign keys, and I delete the fields in the tables that references the
table I want to delete, can I delete the table or must I delete the
restrictions too?
I'm having errors and I'm thinking if that's the problem.
Regards,
Diego F.You cannot drop the table that has a column referenced by another table. You
will have to remove the constraint before you can do that.
Anith|||There are basically two general ways to do this:
1) you can declare foreign keys with the ON DELETE CASCADE option - that way
when you delete the referenced (primary key table) row the referring (foreig
n
key table) rows are deleted; or
2) you delete referring rows manually (in the procedure) or use triggers to
delete referring rows when attempting to delete the referenced row.
Of course there are a few other ways to do this, however, the most important
thing to consider is the logical goal you are trying to achieve. Are you
deleteing erroneous records or are you deleting old records - the former and
the latter can also be archived, deactivated etc. Consider all options, and
use the appropriate one(s).
ML
Showing posts with label tables. Show all posts
Showing posts with label tables. Show all posts
Wednesday, March 28, 2012
Question about restoring transaction log
Hello everyone
I have accidentally deleted a whole bunch of tables with
data from a SQL Server 2000 database (I kwon this soesn't
look not very clever but ... I thought a DSN was
pointing to my MSDE on my PC but it was pointing to the
Win 2000 server). I hadn't made any backup copies yet but
have read that all the information the SQL DBMS manages
is saved in a file called the transaction log. My
transaction log is about 120 MB (the real one on the
server) and hope that the info I have deleted is still
stored there somehow.
The question I pose here is obviously how can I restore
back this tables if posible.
Thank you in advance for any reply
Antonio MiguelHi,
This is assuming your database's recovery model is not set to "Simple".
You are going to do a point-in-time restore. First do a full database
backup make sure "Delete Inactive Entries from Transaction Log" is not
checked; then, backup the transaction Log.
I usually use Enterprise Manager for restoring databases, BOL has the steps
for using Query Analyzer. Using EM, when restoring the database check the
box beside "Point in time restore" enter the time prior to when you deleted
the tables.
"Antonio Miguel" <amgarcia@.dephimatica.es> wrote in message
news:00d001c39276$27386770$a401280a@.phx.gbl...
> Hello everyone
> I have accidentally deleted a whole bunch of tables with
> data from a SQL Server 2000 database (I kwon this soesn't
> look not very clever but ... I thought a DSN was
> pointing to my MSDE on my PC but it was pointing to the
> Win 2000 server). I hadn't made any backup copies yet but
> have read that all the information the SQL DBMS manages
> is saved in a file called the transaction log. My
> transaction log is about 120 MB (the real one on the
> server) and hope that the info I have deleted is still
> stored there somehow.
> The question I pose here is obviously how can I restore
> back this tables if posible.
> Thank you in advance for any reply
> Antonio Miguel|||If Antonio never did a database backup, then the log is truncated at 70% full even if the recovery
mode is full. This is called the "auto-truncate mode" and is not the same thing as simple recovery
mode.
So, Antonio's best bet might be Log Explorer from www.lumigent.com.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Bianca Blount" <blountb@.noemail.com> wrote in message
news:u0%23aBMokDHA.2776@.tk2msftngp13.phx.gbl...
> Hi,
> This is assuming your database's recovery model is not set to "Simple".
> You are going to do a point-in-time restore. First do a full database
> backup make sure "Delete Inactive Entries from Transaction Log" is not
> checked; then, backup the transaction Log.
> I usually use Enterprise Manager for restoring databases, BOL has the steps
> for using Query Analyzer. Using EM, when restoring the database check the
> box beside "Point in time restore" enter the time prior to when you deleted
> the tables.
>
> "Antonio Miguel" <amgarcia@.dephimatica.es> wrote in message
> news:00d001c39276$27386770$a401280a@.phx.gbl...
> > Hello everyone
> >
> > I have accidentally deleted a whole bunch of tables with
> > data from a SQL Server 2000 database (I kwon this soesn't
> > look not very clever but ... I thought a DSN was
> > pointing to my MSDE on my PC but it was pointing to the
> > Win 2000 server). I hadn't made any backup copies yet but
> > have read that all the information the SQL DBMS manages
> > is saved in a file called the transaction log. My
> > transaction log is about 120 MB (the real one on the
> > server) and hope that the info I have deleted is still
> > stored there somehow.
> > The question I pose here is obviously how can I restore
> > back this tables if posible.
> >
> > Thank you in advance for any reply
> > Antonio Miguel
>|||Thanks for the information. I have not heard of "Auto-Recovery" mode. What
is it and is it something that can be controlled by the DBA?
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:O6OQzgukDHA.708@.TK2MSFTNGP10.phx.gbl...
> If Antonio never did a database backup, then the log is truncated at 70%
full even if the recovery
> mode is full. This is called the "auto-truncate mode" and is not the same
thing as simple recovery
> mode.
> So, Antonio's best bet might be Log Explorer from www.lumigent.com.
> --
> Tibor Karaszi, SQL Server MVP
> Archive at: http://groups.google.com/groups?oi=djq&as
ugroup=microsoft.public.sqlserver
>|||Not "auto recovery". It is called "auto-truncate". This means that if SQL Server know that a backup
of the transaction log will be useless (say you never performed a database backup in the first
place, for instance), then SQL Server will empty the log when it is 70% full. This is badly
documented in Books Online. Last time I looked it took a long time before I found the few word that
BOL mentions on this.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Bianca Blount" <blountb@.noemail.com> wrote in message news:ei$zeDxkDHA.2776@.tk2msftngp13.phx.gbl...
> Thanks for the information. I have not heard of "Auto-Recovery" mode. What
> is it and is it something that can be controlled by the DBA?
> "Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
> wrote in message news:O6OQzgukDHA.708@.TK2MSFTNGP10.phx.gbl...
> > If Antonio never did a database backup, then the log is truncated at 70%
> full even if the recovery
> > mode is full. This is called the "auto-truncate mode" and is not the same
> thing as simple recovery
> > mode.
> >
> > So, Antonio's best bet might be Log Explorer from www.lumigent.com.
> > --
> > Tibor Karaszi, SQL Server MVP
> > Archive at: http://groups.google.com/groups?oi=djq&as
> ugroup=microsoft.public.sqlserver
> >
> >
>
I have accidentally deleted a whole bunch of tables with
data from a SQL Server 2000 database (I kwon this soesn't
look not very clever but ... I thought a DSN was
pointing to my MSDE on my PC but it was pointing to the
Win 2000 server). I hadn't made any backup copies yet but
have read that all the information the SQL DBMS manages
is saved in a file called the transaction log. My
transaction log is about 120 MB (the real one on the
server) and hope that the info I have deleted is still
stored there somehow.
The question I pose here is obviously how can I restore
back this tables if posible.
Thank you in advance for any reply
Antonio MiguelHi,
This is assuming your database's recovery model is not set to "Simple".
You are going to do a point-in-time restore. First do a full database
backup make sure "Delete Inactive Entries from Transaction Log" is not
checked; then, backup the transaction Log.
I usually use Enterprise Manager for restoring databases, BOL has the steps
for using Query Analyzer. Using EM, when restoring the database check the
box beside "Point in time restore" enter the time prior to when you deleted
the tables.
"Antonio Miguel" <amgarcia@.dephimatica.es> wrote in message
news:00d001c39276$27386770$a401280a@.phx.gbl...
> Hello everyone
> I have accidentally deleted a whole bunch of tables with
> data from a SQL Server 2000 database (I kwon this soesn't
> look not very clever but ... I thought a DSN was
> pointing to my MSDE on my PC but it was pointing to the
> Win 2000 server). I hadn't made any backup copies yet but
> have read that all the information the SQL DBMS manages
> is saved in a file called the transaction log. My
> transaction log is about 120 MB (the real one on the
> server) and hope that the info I have deleted is still
> stored there somehow.
> The question I pose here is obviously how can I restore
> back this tables if posible.
> Thank you in advance for any reply
> Antonio Miguel|||If Antonio never did a database backup, then the log is truncated at 70% full even if the recovery
mode is full. This is called the "auto-truncate mode" and is not the same thing as simple recovery
mode.
So, Antonio's best bet might be Log Explorer from www.lumigent.com.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Bianca Blount" <blountb@.noemail.com> wrote in message
news:u0%23aBMokDHA.2776@.tk2msftngp13.phx.gbl...
> Hi,
> This is assuming your database's recovery model is not set to "Simple".
> You are going to do a point-in-time restore. First do a full database
> backup make sure "Delete Inactive Entries from Transaction Log" is not
> checked; then, backup the transaction Log.
> I usually use Enterprise Manager for restoring databases, BOL has the steps
> for using Query Analyzer. Using EM, when restoring the database check the
> box beside "Point in time restore" enter the time prior to when you deleted
> the tables.
>
> "Antonio Miguel" <amgarcia@.dephimatica.es> wrote in message
> news:00d001c39276$27386770$a401280a@.phx.gbl...
> > Hello everyone
> >
> > I have accidentally deleted a whole bunch of tables with
> > data from a SQL Server 2000 database (I kwon this soesn't
> > look not very clever but ... I thought a DSN was
> > pointing to my MSDE on my PC but it was pointing to the
> > Win 2000 server). I hadn't made any backup copies yet but
> > have read that all the information the SQL DBMS manages
> > is saved in a file called the transaction log. My
> > transaction log is about 120 MB (the real one on the
> > server) and hope that the info I have deleted is still
> > stored there somehow.
> > The question I pose here is obviously how can I restore
> > back this tables if posible.
> >
> > Thank you in advance for any reply
> > Antonio Miguel
>|||Thanks for the information. I have not heard of "Auto-Recovery" mode. What
is it and is it something that can be controlled by the DBA?
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:O6OQzgukDHA.708@.TK2MSFTNGP10.phx.gbl...
> If Antonio never did a database backup, then the log is truncated at 70%
full even if the recovery
> mode is full. This is called the "auto-truncate mode" and is not the same
thing as simple recovery
> mode.
> So, Antonio's best bet might be Log Explorer from www.lumigent.com.
> --
> Tibor Karaszi, SQL Server MVP
> Archive at: http://groups.google.com/groups?oi=djq&as
ugroup=microsoft.public.sqlserver
>|||Not "auto recovery". It is called "auto-truncate". This means that if SQL Server know that a backup
of the transaction log will be useless (say you never performed a database backup in the first
place, for instance), then SQL Server will empty the log when it is 70% full. This is badly
documented in Books Online. Last time I looked it took a long time before I found the few word that
BOL mentions on this.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Bianca Blount" <blountb@.noemail.com> wrote in message news:ei$zeDxkDHA.2776@.tk2msftngp13.phx.gbl...
> Thanks for the information. I have not heard of "Auto-Recovery" mode. What
> is it and is it something that can be controlled by the DBA?
> "Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
> wrote in message news:O6OQzgukDHA.708@.TK2MSFTNGP10.phx.gbl...
> > If Antonio never did a database backup, then the log is truncated at 70%
> full even if the recovery
> > mode is full. This is called the "auto-truncate mode" and is not the same
> thing as simple recovery
> > mode.
> >
> > So, Antonio's best bet might be Log Explorer from www.lumigent.com.
> > --
> > Tibor Karaszi, SQL Server MVP
> > Archive at: http://groups.google.com/groups?oi=djq&as
> ugroup=microsoft.public.sqlserver
> >
> >
>
Question about pulling Price from multiple tables.
Pricing is a little confusing at my work. My works database from the point of sale has multiple places for price to be. Right now the tables in question are in a SQL database. I am trying to create a user defined function so I can get a list of prices for specific items and for the customer logged into the web page. The tables look like this:
Inventory: ItemID, ItemCompany, Description, ListPrice, PublicPrice
ContractID: ContractNum, ItemID, ItemCompany, MinQty, ContractPrice
Customer: CustomerNumber, CustomerDepartment, Contract1 – Contract4
ContractNum '99' is a global contract to all customers. I was able to get the price for this generic contract price, but not for contracts customer may have.
Here is what I have so far (with an example item already being pulled up). I am at a loss on how to get the customers contract price with the same function.
SELECT Inventory.ItemNumber, Inventory.Company, Inventory.Description,
Contracts.QuantityAS MinContractQty,COALESCE (Contracts.ContractPrice, Inventory.WhlCatalogPrice)
AS Price, Inventory.WhlCatalogPriceAS ListPrice
FROM InventoryLEFTOUTER JOIN ContractsON Inventory.ItemNumber = Contracts.ItemNumberAND Inventory.Company = Contracts.CompanyAND'99' = Contracts.ContractNumber
WHERE (Inventory.ItemNumber = N'444')AND (Inventory.Company = N'035')
Assuming my grasp of your schema is correct, is there a reason why this wouldn't work?
SELECT ContractID.ContractPrice, Inventory.DescriptionFROM Customer, ContractID, InventorWHERE (Customer.Contract1 = ContractID.ContractNumORCustomer.Contract2 = ContractID.ContractNumORCustomer.Contract3 = ContractID.ContractNumORCustomer.Contract4 = ContractID.ContractNum)AND ContractID.ItemID = Inventory.ItemIDThis should get all the prices and descriptions for the items that have a contract number. Add another AND clause to the WHERE and narrow it down to the CustomerID you want. You can add the global contract by addind 99 = ContractID.ContractNum in the WHERE statement (put it in the parentheses).|||The only problem I am running into is if there is no contract price under '99' or under one for the customer, it would not bring up the item. The '99' group is select items, the contract price just applies to everyone. Thanks for the reply.|||Can you post some sample data too?|||
Sure. I think I have all of the different situations that might come up.
Contract Table:
"ItemNumber","Company","ContractNumber","ContractPrice","Qty"
"01541","NAT","BB",$4.99,1
"01541","NAT","DD",$4.50,1
"26300","CRD","DD",$6.00,1
"26300","CRD","BB",$6.29,1
Customers:
"CustomerNumber","Department","CustomerName","Contract1","Contract2","Contract3","Contract4"
" 15576","","Company1","DD","","",""
" 15577","","Company2","BB","","",""
" 15601","","Company3","","",,""
" 15602",,"Company4","C1","DD","",""
Product Table:
"ItemNumber","Company","Description","WhlCatalogPrice"
"06342","MEA","BOOK,NOTE,SCIENTIFIC,11X8.5",$11.69
"24133","SAN","PEN,LIQUID EXPRS 8PK,AST",$18.50
"26300","CCS","CASE,DRIVE,NEO-SPORT,BK",$30.73
"33350","KMW","DOCK,ENTERTAINMENT,IPOD 500",$162.50
"01541","NAT","CLIPBOARD,LTR,RECY,PLAST,RD",$7.35
"01593","COX","WIPE,DISINFECTING,CLOROX",$52.80
"26300","CRD","Item 26300",$9.95
"20021","IVR","INKCART,MFC3100, 950YD,BK",$16.79
"45005","CCS","TOWER,CD,ONETOUCH,40 CAP",$64.95
"5500","TOP","FORM,P/OUT,1PT,8.5X11,35C",$128.47
"5500","LAK","HEATER,RADIATOR,GY",$199.95
"5500","OSP","CHAIR,MANAGERIAL,MESH,BK",$355.00|||
Tealc:
The only problem I am running into is if there is no contract price under '99' or under one for the customer, it would not bring up the item. The '99' group is select items, the contract price just applies to everyone. Thanks for the reply.
I'm not quite getting what you're trying to do. Can you explain it again for me?
Thanks.
|||I ment to add these to the contract table:
"06342","MEA","99",$9.69,1
"24133","SAN","99",$16.99,1
I am looking for the function to return both "List Price" (which is WhlCatalogPrice), and a price specific to the '99' group or customer contract if they have any.
Thanks for everyones reply so far.
|||SELECT i.Itemnumber, i.Company, i.Description,COALESCE(Contract1.MinQty,Contract2.MinQty,Contract3.MinQty,Contract4.MinQty,GlobalContract.MinQty) AS MinContractQty,COALESCE(Contract1.ContractPrice,Contract2.ContractPrice,Contract3.ContractPrice,Contract4.ContractPrice,GlobalContract.ContractPrice,Inventory.WhlCatalogPrice) AS Price,I.WhlCatalogPrice AS ListPrice
FROM Inventory i
CROSS JOIN Customer cust
LEFT JOIN Contracts Contract1 ON Contract1.ContractNum=cust.Contract1 AND Contract1.ItemID=i.ItemID
LEFT JOIN Contracts Contract2 ON Contract2.ContractNum=cust.Contract2 AND Contract2.ItemID=i.ItemID
LEFT JOIN Contracts Contract3 ON Contract3.ContractNum=cust.Contract3 AND Contract3.ItemID=i.ItemID
LEFT JOIN Contracts Contract4 ON Contract4.ContractNum=cust.Contract4 AND Contract4.ItemID=i.ItemID
LEFT JOIN Contracts GlobalContract ON GlobalContract.ContractNum=99 AND GlobalContract.ItemID=i.ItemID
WHEREcust.CustomerNumber=@.CustID
|||That query seems to be working, but I keep getting duplication. I am going to look at each of the tables to make sure there is no duplication in them. I am still trying to learn what is what in the database from this new to me point of sale system (DDMS).sql
Monday, March 26, 2012
Question about procedures to create procedures in a different database
I'm trying to write a procedure that having created a new database,
will then create within that new database all the tables and
procedures that go with it.
will then create within that new database all the tables and
procedures that go with it.
In doing this I'm hitting the problem that you can't issue a USE
command within a procedure.
So my question is either
- how do I get around this?
- if I can't, how can I create procedures etc in a *different*
(i.e. the newly created) database
or
- is there a better way to do all this (*)
I have SQL files that do this currently, but I need to edit in the
name of the database each time before execution, so I thought a
procedure would be better. Also I'd like eventually to expose some
of this functionality via a web interface.
Although I'm a newbie, I feel I'm diving in the deep end. Any good
pointers to all the issues involved in this aspect of database
management would be appreciated.
(*) One thought that occurs to me is to have a "template" database,
and to then somehow copy all procedures, tables, view etc from that.
--
HTML-to-text and markup removal with Detagger
http://www.jafsoft.com/detagger/"John A Fotheringham" <jaf@.jafsoft.com> wrote in message
news:0b9au0p44guoe2mshj4cvr8i5pngm51k46@.4ax.com...
> I'm trying to write a procedure that having created a new database,
> will then create within that new database all the tables and
> procedures that go with it.
> In doing this I'm hitting the problem that you can't issue a USE
> command within a procedure.
> So my question is either
> - how do I get around this?
> - if I can't, how can I create procedures etc in a *different*
> (i.e. the newly created) database
> or
> - is there a better way to do all this (*)
> I have SQL files that do this currently, but I need to edit in the
> name of the database each time before execution, so I thought a
> procedure would be better. Also I'd like eventually to expose some
> of this functionality via a web interface.
> Although I'm a newbie, I feel I'm diving in the deep end. Any good
> pointers to all the issues involved in this aspect of database
> management would be appreciated.
> (*) One thought that occurs to me is to have a "template" database,
> and to then somehow copy all procedures, tables, view etc from that.
> --
> HTML-to-text and markup removal with Detagger
> http://www.jafsoft.com/detagger/
A 'template' database is a relatively easy solution - backup a source
database, restore it on your target system (you can change the database name
and file locations during restore if you want) and it's done. But you don't
have any flexibility, and there's no way to 'upgrade' to a new version of
your tables, procs, functions etc.
The best way is usually to treat every CREATE script as source code, and use
a source control system (VSS or whatever). Then you can get the files to
create each object, run them against your target database with a custom
script or osql.exe, and build the database that way. You choose the server
and database name when you run the script (eg. the -S and -d switches for
osql.exe), so it's also easier to run against multiple databases. This is
much more flexible and a better long-term solution, but of course you do
have to invest more time up front.
Erland has a solution described here, which should give you some good ideas:
http://www.abaris.se/abaperls/index.html
Simon|||"Simon Hayes" <sql@.hayes.ch> wrote:
>> (*) One thought that occurs to me is to have a "template" database,
>> and to then somehow copy all procedures, tables, view etc from that.
>> --
>A 'template' database is a relatively easy solution - backup a source
>database, restore it on your target system (you can change the database name
>and file locations during restore if you want) and it's done. But you don't
>have any flexibility, and there's no way to 'upgrade' to a new version of
>your tables, procs, functions etc.
All the databases would be on the one server. Also there are other
parameters involved. For example when I create a new database I also
add a new user login to allow access to this database, and have to
grant privs on the new database and also on some shared databases.
Would I be able to pass such parameters to osql.exe? I think that's
a minor issue, as adding users and granting privs could easily be
separated out into a different procedure once the database and tables
have been created.
I'd prefer the procedure route for two reasons
- easier to change the procedures and have them loaded
into the new (or existing) databases
- better prospects of invoking such a script from a web
interface (though there may not be much in it).
>Then you can get the files to
>create each object, run them against your target database with a custom
>script or osql.exe, and build the database that way. You choose the server
>and database name when you run the script (eg. the -S and -d switches for
>osql.exe), so it's also easier to run against multiple databases.
Thanks for that. That would do what I wanted, but I hoped there was
some equivalent way of running a procedure within a named database
--
HTML-to-text and markup removal with Detagger
http://www.jafsoft.com/detagger/|||"John A Fotheringham" <jaf@.jafsoft.com> wrote in message
news:ba8bu0hq4qdc0vpaniqpqmt85b35b55p6f@.4ax.com...
> "Simon Hayes" <sql@.hayes.ch> wrote:
>>> (*) One thought that occurs to me is to have a "template" database,
>>> and to then somehow copy all procedures, tables, view etc from that.
>>> --
>>
>>A 'template' database is a relatively easy solution - backup a source
>>database, restore it on your target system (you can change the database
>>name
>>and file locations during restore if you want) and it's done. But you
>>don't
>>have any flexibility, and there's no way to 'upgrade' to a new version of
>>your tables, procs, functions etc.
> All the databases would be on the one server. Also there are other
> parameters involved. For example when I create a new database I also
> add a new user login to allow access to this database, and have to
> grant privs on the new database and also on some shared databases.
> Would I be able to pass such parameters to osql.exe? I think that's
> a minor issue, as adding users and granting privs could easily be
> separated out into a different procedure once the database and tables
> have been created.
It depends on what the parameters would be - osql.exe is a bit limited in
that respect, but if it's something like database name or current login,
then you can just use functions like db_name() or suser_sname() inside your
script. Otherwise, a stored proc is a reasonable option - you can execute it
from your main/wrapper script after it and all the other objects are
created, and with whatever parameters you need.
> I'd prefer the procedure route for two reasons
> - easier to change the procedures and have them loaded
> into the new (or existing) databases
> - better prospects of invoking such a script from a web
> interface (though there may not be much in it).
If you need to run things from ASP or whatever, you might want to look into
ADO, and/or consider using it from Perl/Python/C# etc. for your deployment
scripting.
>>Then you can get the files to
>>create each object, run them against your target database with a custom
>>script or osql.exe, and build the database that way. You choose the server
>>and database name when you run the script (eg. the -S and -d switches for
>>osql.exe), so it's also easier to run against multiple databases.
> Thanks for that. That would do what I wanted, but I hoped there was
> some equivalent way of running a procedure within a named database
Is this the sort of thing you want?
exec db1.dbo.myproc
exec db2.dbo.myproc
But the procedure has to exist first, of course, so you still need some way
of creating it there.
Simon|||John A Fotheringham (jaf@.jafsoft.com) writes:
> I'm trying to write a procedure that having created a new database,
> will then create within that new database all the tables and
> procedures that go with it.
> In doing this I'm hitting the problem that you can't issue a USE
> command within a procedure.
> So my question is either
> - how do I get around this?
> - if I can't, how can I create procedures etc in a *different*
> (i.e. the newly created) database
> or
> - is there a better way to do all this (*)
Indeed.
Only try to do this in SQL only, if you really like to hurt yourself.
It's much better to do this from a client program, written in Perl,
VBscript, C++ or whatever your favourite. The nice thing is that
the program does not need to contain any SQL code - it could just
read of list of files to run. So if you need to create a new procedure,
you just add the file to the list. And if a stored procedure changes,
you simply edit the file for that procedure.
If you were to put this in a stored procedure, you would be in for an
massive amount of dynamic SQL, and maintenance would be a nightmare.
> I have SQL files that do this currently, but I need to edit in the
> name of the database each time before execution, so I thought a
> procedure would be better. Also I'd like eventually to expose some
> of this functionality via a web interface.
Web programming is not my game, but I am quite confident that there
is something called CGI that permits you to run scripts on the
web server. Surely web servers are not restricted to running stored
procedures only, are they?
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||>Is this the sort of thing you want?
>exec db1.dbo.myproc
>exec db2.dbo.myproc
>But the procedure has to exist first, of course, so you still need some way
>of creating it there.
That's the catch-22 I'm banging my head against.
I'd like a procedure that creates procedures in a named database.
What I want is the equivalent of
create procedure db1.dbo.newproc
or
use db1
create procedure newproc
but neither option seems to work (I could be wrong though - I am a
newbie after all :-). If the first one worked I could embed it in an
EXEC statement with the db passed in as follows
EXEC ('
create procedure ' + @.dbname + '.dbo.newproc...
')
I have procedures that use this approach for other things already.
--
HTML-to-text and markup removal with Detagger
http://www.jafsoft.com/detagger/|||John A Fotheringham (jaf@.jafsoft.com) writes:
> I'd like a procedure that creates procedures in a named database.
> What I want is the equivalent of
> create procedure db1.dbo.newproc
> or
> use db1
> create procedure newproc
> but neither option seems to work (I could be wrong though - I am a
> newbie after all :-). If the first one worked I could embed it in an
> EXEC statement with the db passed in as follows
> EXEC ('
> create procedure ' + @.dbname + '.dbo.newproc...
> ')
> I have procedures that use this approach for other things already.
You could do:
EXEC ('USE ' + @.dbname + '
EXEC (''CREATE PROCEDURE '' + @.dbname + ''.dbo.newproc AS
...
'')')
The double level of nested quotes are likely to drive you mad, but you
could do:
SELECT @.myproc = 'CREATE PROCEDURE ' + @.dbname + 'dbo.newproc AS
...
'
EXEC ('USE ' + @.dbnmame + ' ' + @.sql)
But if the procedure is more than 8000 characters you can't fit it into
a varchar(8000), but would have to split it up on more variables.
As I said, only go this road if you really enjoy hurting yourself.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||>You could do:
> EXEC ('USE ' + @.dbname + '
> EXEC (''CREATE PROCEDURE '' + @.dbname + ''.dbo.newproc AS
> ...
> '')')
I didn't think that
CREATE PROCEDURE otherDatabase.dbo.test
worked. On my system I get
'CREATE PROCEDURE' does not allow specifying the database name
as a prefix to the object name.
but if the nested EXEC works with the USE, then I shouldn't need the
database name on the CRATE PROCEDURE command. As for being hell as
regards quotes, I'm used to that from other contexts.
[goes away and experiments]
Thanks! The following works
USE db1
GO
CREATE PROCEDURE test_1 AS
BEGIN
EXEC ('USE db2
EXEC (''CREATE PROCEDURE test_2 (@.var varchar(10))
AS
BEGIN
PRINT @.var
END
'')
')
END
GO
EXEC test_1
GO
USE db2
EXEC test_2 'it works!!'
I think I can live with that, and do everything I want with no more
that two levels of nested EXECs :-)
--
HTML-to-text and markup removal with Detagger
http://www.jafsoft.com/detagger/|||>> - is there a better way to do all this (*)
>Indeed.
>Only try to do this in SQL only, if you really like to hurt yourself.
>It's much better to do this from a client program, written in Perl,
>VBscript, C++ or whatever your favourite. The nice thing is that
>the program does not need to contain any SQL code - it could just
>read of list of files to run. So if you need to create a new procedure,
>you just add the file to the list. And if a stored procedure changes,
>you simply edit the file for that procedure.
>If you were to put this in a stored procedure, you would be in for an
>massive amount of dynamic SQL,
I don't think the performance is an issue in this context, as adding
new databases will be a rare event, and in any case the databases and
procedures concerned are not that extensive.
>and maintenance would be a nightmare.
That may well be true :-)
Thanks for the advice.
>> I have SQL files that do this currently, but I need to edit in the
>> name of the database each time before execution, so I thought a
>> procedure would be better. Also I'd like eventually to expose some
>> of this functionality via a web interface.
>Web programming is not my game, but I am quite confident that there
>is something called CGI that permits you to run scripts on the
>web server. Surely web servers are not restricted to running stored
>procedures only, are they?
No, but I'm currently learning SQL, C# and ASP.NET simultaneously, and
so am looking for the path of least resistance for now, and C#/ASP.NET
make running a stored procedure fairly simple (in the sense that this
is something I have already got working :-)
--
HTML-to-text and markup removal with Detagger
http://www.jafsoft.com/detagger/|||John A Fotheringham (jaf@.jafsoft.com) writes:
>>If you were to put this in a stored procedure, you would be in for an
>>massive amount of dynamic SQL,
> I don't think the performance is an issue in this context, as adding
> new databases will be a rare event, and in any case the databases and
> procedures concerned are not that extensive.
I didn't mean to say that it wold be a performance issue. It's clear
that this is not a performance-critical thing. And in any case, the
overhead for dynamic SQL for the server is minimal.
The overhead for a human brain to cope with string embedded in string
literals that are embedded in string literals, on the other hand, is
considerable.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||>I didn't mean to say that it wold be a performance issue. It's clear
>that this is not a performance-critical thing. And in any case, the
>overhead for dynamic SQL for the server is minimal.
>The overhead for a human brain to cope with string embedded in string
>literals that are embedded in string literals, on the other hand, is
>considerable.
Ah! I see what you mean :-)
--
HTML-to-text and markup removal with Detagger
http://www.jafsoft.com/detagger/
question about performance of partition tables
1. When I partition a table ( 90 million rows ) into
five partitions. (create view as select * from table1
union select * from table 2 union ...)
I get a 2 x times query time increase, but by the time
#partitions = 10, the query time is same as the large
table. when #partitions = 15 query time > large table.
Ideally I would want to partition it into 50 states.
Why is it that parallel query execution does not speedup
the process.
The indexes are virtually the same ( most cases covering
non-clustered indexes.)
Is it because of merging the results and sorting them
( because of the group by/order by clauses )? however
the query plan says those are 0%.
You can ask me any question about the query statement,
table format and query plan execution.
I thought about this for a week and don't have an answer.There will always come a point where the resources will be overwhelmed byt
he requests. The more queries you attempt to do simultaneously the less
chances are they will each perform as well as running by them selves. At
some point the cpu or disk queues will start playing a factor. You also
have more overhead when trying to put all the results together and have more
chance of tempdb being a factor.
Andrew J. Kelly
SQL Server MVP
"Ramesh" <anonymous@.discussions.microsoft.com> wrote in message
news:119d201c3f56c$fba82640$a001280a@.phx
.gbl...
> 1. When I partition a table ( 90 million rows ) into
> five partitions. (create view as select * from table1
> union select * from table 2 union ...)
> I get a 2 x times query time increase, but by the time
> #partitions = 10, the query time is same as the large
> table. when #partitions = 15 query time > large table.
> Ideally I would want to partition it into 50 states.
> Why is it that parallel query execution does not speedup
> the process.
> The indexes are virtually the same ( most cases covering
> non-clustered indexes.)
> Is it because of merging the results and sorting them
> ( because of the group by/order by clauses )? however
> the query plan says those are 0%.
> You can ask me any question about the query statement,
> table format and query plan execution.
> I thought about this for a week and don't have an answer.|||cpu utilization seems low : < 40 %
disk I/O queues < 15 on RAID-5 containing tables
(6 disks) and < 3 on RAID-10 containing indexes
(6 disks)
tempdb and log on seperate RAID-10 ( disk queues < 2)
(4 disks)
It is true that queries which only use indexes (covering)
work well upto 10 partitions
queries which would need to use leaf keys and then use
clustering keys work well upto 5 partitions.
Is there something I should know on creating the
clustered/non-clustered indexes different on the
partitioned tables?
Is there some material I can read on?
Is the Windows 2000 server be part of the bottleneck?
Is there any specific parameters I should target and
resolve. ( I am aiming for 60 partitions : each with
1-2 million rows each to reduce skewness). I thought this
would speed up the queries 2 orders of magnitude. Sadly
this does not work the way I calculated.
>--Original Message--
>There will always come a point where the resources will
be overwhelmed byt
>he requests. The more queries you attempt to do
simultaneously the less
>chances are they will each perform as well as running by
them selves. At
>some point the cpu or disk queues will start playing a
factor. You also
>have more overhead when trying to put all the results
together and have more
>chance of tempdb being a factor.
>--
>Andrew J. Kelly
>SQL Server MVP
>
>"Ramesh" <anonymous@.discussions.microsoft.com> wrote in
message
> news:119d201c3f56c$fba82640$a001280a@.phx
.gbl...
time
speedup
covering
answer.
>
>.
>|||Disk queues of 15 or so on a 6 disk Raid 5 are still high enough to warrant
paying attention to them. That means you are in fact waiting on disk I/O.
You might get better results from combining all 12 disks from the indexes
and data into one Raid 10. As for the partitioning that is tough to say.
Most partitioning requires a lot of testing under your specific conditions
to determine which is best for you. Are you sure you need to partition that
table at all? 50 partitions is a lot and you only have 90 million rows.
What are the typical queries like against this table? Maybe just adjusting
the clustered index will do.
Andrew J. Kelly
SQL Server MVP
"Ramesh Krishnan" <anonymous@.discussions.microsoft.com> wrote in message
news:11d6201c3f62b$ea334100$a301280a@.phx
.gbl...
> cpu utilization seems low : < 40 %
> disk I/O queues < 15 on RAID-5 containing tables
> (6 disks) and < 3 on RAID-10 containing indexes
> (6 disks)
> tempdb and log on seperate RAID-10 ( disk queues < 2)
> (4 disks)
> It is true that queries which only use indexes (covering)
> work well upto 10 partitions
> queries which would need to use leaf keys and then use
> clustering keys work well upto 5 partitions.
> Is there something I should know on creating the
> clustered/non-clustered indexes different on the
> partitioned tables?
> Is there some material I can read on?
> Is the Windows 2000 server be part of the bottleneck?
> Is there any specific parameters I should target and
> resolve. ( I am aiming for 60 partitions : each with
> 1-2 million rows each to reduce skewness). I thought this
> would speed up the queries 2 orders of magnitude. Sadly
> this does not work the way I calculated.
>
> be overwhelmed byt
> simultaneously the less
> them selves. At
> factor. You also
> together and have more
> message
> time
> speedup
> covering
> answer.
five partitions. (create view as select * from table1
union select * from table 2 union ...)
I get a 2 x times query time increase, but by the time
#partitions = 10, the query time is same as the large
table. when #partitions = 15 query time > large table.
Ideally I would want to partition it into 50 states.
Why is it that parallel query execution does not speedup
the process.
The indexes are virtually the same ( most cases covering
non-clustered indexes.)
Is it because of merging the results and sorting them
( because of the group by/order by clauses )? however
the query plan says those are 0%.
You can ask me any question about the query statement,
table format and query plan execution.
I thought about this for a week and don't have an answer.There will always come a point where the resources will be overwhelmed byt
he requests. The more queries you attempt to do simultaneously the less
chances are they will each perform as well as running by them selves. At
some point the cpu or disk queues will start playing a factor. You also
have more overhead when trying to put all the results together and have more
chance of tempdb being a factor.
Andrew J. Kelly
SQL Server MVP
"Ramesh" <anonymous@.discussions.microsoft.com> wrote in message
news:119d201c3f56c$fba82640$a001280a@.phx
.gbl...
> 1. When I partition a table ( 90 million rows ) into
> five partitions. (create view as select * from table1
> union select * from table 2 union ...)
> I get a 2 x times query time increase, but by the time
> #partitions = 10, the query time is same as the large
> table. when #partitions = 15 query time > large table.
> Ideally I would want to partition it into 50 states.
> Why is it that parallel query execution does not speedup
> the process.
> The indexes are virtually the same ( most cases covering
> non-clustered indexes.)
> Is it because of merging the results and sorting them
> ( because of the group by/order by clauses )? however
> the query plan says those are 0%.
> You can ask me any question about the query statement,
> table format and query plan execution.
> I thought about this for a week and don't have an answer.|||cpu utilization seems low : < 40 %
disk I/O queues < 15 on RAID-5 containing tables
(6 disks) and < 3 on RAID-10 containing indexes
(6 disks)
tempdb and log on seperate RAID-10 ( disk queues < 2)
(4 disks)
It is true that queries which only use indexes (covering)
work well upto 10 partitions
queries which would need to use leaf keys and then use
clustering keys work well upto 5 partitions.
Is there something I should know on creating the
clustered/non-clustered indexes different on the
partitioned tables?
Is there some material I can read on?
Is the Windows 2000 server be part of the bottleneck?
Is there any specific parameters I should target and
resolve. ( I am aiming for 60 partitions : each with
1-2 million rows each to reduce skewness). I thought this
would speed up the queries 2 orders of magnitude. Sadly
this does not work the way I calculated.
>--Original Message--
>There will always come a point where the resources will
be overwhelmed byt
>he requests. The more queries you attempt to do
simultaneously the less
>chances are they will each perform as well as running by
them selves. At
>some point the cpu or disk queues will start playing a
factor. You also
>have more overhead when trying to put all the results
together and have more
>chance of tempdb being a factor.
>--
>Andrew J. Kelly
>SQL Server MVP
>
>"Ramesh" <anonymous@.discussions.microsoft.com> wrote in
message
> news:119d201c3f56c$fba82640$a001280a@.phx
.gbl...
time
speedup
covering
answer.
>
>.
>|||Disk queues of 15 or so on a 6 disk Raid 5 are still high enough to warrant
paying attention to them. That means you are in fact waiting on disk I/O.
You might get better results from combining all 12 disks from the indexes
and data into one Raid 10. As for the partitioning that is tough to say.
Most partitioning requires a lot of testing under your specific conditions
to determine which is best for you. Are you sure you need to partition that
table at all? 50 partitions is a lot and you only have 90 million rows.
What are the typical queries like against this table? Maybe just adjusting
the clustered index will do.
Andrew J. Kelly
SQL Server MVP
"Ramesh Krishnan" <anonymous@.discussions.microsoft.com> wrote in message
news:11d6201c3f62b$ea334100$a301280a@.phx
.gbl...
> cpu utilization seems low : < 40 %
> disk I/O queues < 15 on RAID-5 containing tables
> (6 disks) and < 3 on RAID-10 containing indexes
> (6 disks)
> tempdb and log on seperate RAID-10 ( disk queues < 2)
> (4 disks)
> It is true that queries which only use indexes (covering)
> work well upto 10 partitions
> queries which would need to use leaf keys and then use
> clustering keys work well upto 5 partitions.
> Is there something I should know on creating the
> clustered/non-clustered indexes different on the
> partitioned tables?
> Is there some material I can read on?
> Is the Windows 2000 server be part of the bottleneck?
> Is there any specific parameters I should target and
> resolve. ( I am aiming for 60 partitions : each with
> 1-2 million rows each to reduce skewness). I thought this
> would speed up the queries 2 orders of magnitude. Sadly
> this does not work the way I calculated.
>
> be overwhelmed byt
> simultaneously the less
> them selves. At
> factor. You also
> together and have more
> message
> time
> speedup
> covering
> answer.
question about performance of partition tables
1. When I partition a table ( 90 million rows ) into
five partitions. (create view as select * from table1
union select * from table 2 union ...)
I get a 2 x times query time increase, but by the time
#partitions = 10, the query time is same as the large
table. when #partitions = 15 query time > large table.
Ideally I would want to partition it into 50 states.
Why is it that parallel query execution does not speedup
the process.
The indexes are virtually the same ( most cases covering
non-clustered indexes.)
Is it because of merging the results and sorting them
( because of the group by/order by clauses )? however
the query plan says those are 0%.
You can ask me any question about the query statement,
table format and query plan execution.
I thought about this for a week and don't have an answer.There will always come a point where the resources will be overwhelmed byt
he requests. The more queries you attempt to do simultaneously the less
chances are they will each perform as well as running by them selves. At
some point the cpu or disk queues will start playing a factor. You also
have more overhead when trying to put all the results together and have more
chance of tempdb being a factor.
--
Andrew J. Kelly
SQL Server MVP
"Ramesh" <anonymous@.discussions.microsoft.com> wrote in message
news:119d201c3f56c$fba82640$a001280a@.phx.gbl...
> 1. When I partition a table ( 90 million rows ) into
> five partitions. (create view as select * from table1
> union select * from table 2 union ...)
> I get a 2 x times query time increase, but by the time
> #partitions = 10, the query time is same as the large
> table. when #partitions = 15 query time > large table.
> Ideally I would want to partition it into 50 states.
> Why is it that parallel query execution does not speedup
> the process.
> The indexes are virtually the same ( most cases covering
> non-clustered indexes.)
> Is it because of merging the results and sorting them
> ( because of the group by/order by clauses )? however
> the query plan says those are 0%.
> You can ask me any question about the query statement,
> table format and query plan execution.
> I thought about this for a week and don't have an answer.|||cpu utilization seems low : < 40 %
disk I/O queues < 15 on RAID-5 containing tables
(6 disks) and < 3 on RAID-10 containing indexes
(6 disks)
tempdb and log on seperate RAID-10 ( disk queues < 2)
(4 disks)
It is true that queries which only use indexes (covering)
work well upto 10 partitions
queries which would need to use leaf keys and then use
clustering keys work well upto 5 partitions.
Is there something I should know on creating the
clustered/non-clustered indexes different on the
partitioned tables?
Is there some material I can read on?
Is the windows 2000 server be part of the bottleneck?
Is there any specific parameters I should target and
resolve. ( I am aiming for 60 partitions : each with
1-2 million rows each to reduce skewness). I thought this
would speed up the queries 2 orders of magnitude. Sadly
this does not work the way I calculated.
>--Original Message--
>There will always come a point where the resources will
be overwhelmed byt
>he requests. The more queries you attempt to do
simultaneously the less
>chances are they will each perform as well as running by
them selves. At
>some point the cpu or disk queues will start playing a
factor. You also
>have more overhead when trying to put all the results
together and have more
>chance of tempdb being a factor.
>--
>Andrew J. Kelly
>SQL Server MVP
>
>"Ramesh" <anonymous@.discussions.microsoft.com> wrote in
message
>news:119d201c3f56c$fba82640$a001280a@.phx.gbl...
>> 1. When I partition a table ( 90 million rows ) into
>> five partitions. (create view as select * from table1
>> union select * from table 2 union ...)
>> I get a 2 x times query time increase, but by the
time
>> #partitions = 10, the query time is same as the large
>> table. when #partitions = 15 query time > large table.
>> Ideally I would want to partition it into 50 states.
>> Why is it that parallel query execution does not
speedup
>> the process.
>> The indexes are virtually the same ( most cases
covering
>> non-clustered indexes.)
>> Is it because of merging the results and sorting them
>> ( because of the group by/order by clauses )? however
>> the query plan says those are 0%.
>> You can ask me any question about the query statement,
>> table format and query plan execution.
>> I thought about this for a week and don't have an
answer.
>
>.
>|||Disk queues of 15 or so on a 6 disk Raid 5 are still high enough to warrant
paying attention to them. That means you are in fact waiting on disk I/O.
You might get better results from combining all 12 disks from the indexes
and data into one Raid 10. As for the partitioning that is tough to say.
Most partitioning requires a lot of testing under your specific conditions
to determine which is best for you. Are you sure you need to partition that
table at all? 50 partitions is a lot and you only have 90 million rows.
What are the typical queries like against this table? Maybe just adjusting
the clustered index will do.
--
Andrew J. Kelly
SQL Server MVP
"Ramesh Krishnan" <anonymous@.discussions.microsoft.com> wrote in message
news:11d6201c3f62b$ea334100$a301280a@.phx.gbl...
> cpu utilization seems low : < 40 %
> disk I/O queues < 15 on RAID-5 containing tables
> (6 disks) and < 3 on RAID-10 containing indexes
> (6 disks)
> tempdb and log on seperate RAID-10 ( disk queues < 2)
> (4 disks)
> It is true that queries which only use indexes (covering)
> work well upto 10 partitions
> queries which would need to use leaf keys and then use
> clustering keys work well upto 5 partitions.
> Is there something I should know on creating the
> clustered/non-clustered indexes different on the
> partitioned tables?
> Is there some material I can read on?
> Is the windows 2000 server be part of the bottleneck?
> Is there any specific parameters I should target and
> resolve. ( I am aiming for 60 partitions : each with
> 1-2 million rows each to reduce skewness). I thought this
> would speed up the queries 2 orders of magnitude. Sadly
> this does not work the way I calculated.
> >--Original Message--
> >There will always come a point where the resources will
> be overwhelmed byt
> >he requests. The more queries you attempt to do
> simultaneously the less
> >chances are they will each perform as well as running by
> them selves. At
> >some point the cpu or disk queues will start playing a
> factor. You also
> >have more overhead when trying to put all the results
> together and have more
> >chance of tempdb being a factor.
> >
> >--
> >
> >Andrew J. Kelly
> >SQL Server MVP
> >
> >
> >"Ramesh" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:119d201c3f56c$fba82640$a001280a@.phx.gbl...
> >> 1. When I partition a table ( 90 million rows ) into
> >> five partitions. (create view as select * from table1
> >> union select * from table 2 union ...)
> >> I get a 2 x times query time increase, but by the
> time
> >> #partitions = 10, the query time is same as the large
> >> table. when #partitions = 15 query time > large table.
> >>
> >> Ideally I would want to partition it into 50 states.
> >> Why is it that parallel query execution does not
> speedup
> >> the process.
> >>
> >> The indexes are virtually the same ( most cases
> covering
> >> non-clustered indexes.)
> >> Is it because of merging the results and sorting them
> >> ( because of the group by/order by clauses )? however
> >> the query plan says those are 0%.
> >>
> >> You can ask me any question about the query statement,
> >> table format and query plan execution.
> >>
> >> I thought about this for a week and don't have an
> answer.
> >
> >
> >.
> >sql
five partitions. (create view as select * from table1
union select * from table 2 union ...)
I get a 2 x times query time increase, but by the time
#partitions = 10, the query time is same as the large
table. when #partitions = 15 query time > large table.
Ideally I would want to partition it into 50 states.
Why is it that parallel query execution does not speedup
the process.
The indexes are virtually the same ( most cases covering
non-clustered indexes.)
Is it because of merging the results and sorting them
( because of the group by/order by clauses )? however
the query plan says those are 0%.
You can ask me any question about the query statement,
table format and query plan execution.
I thought about this for a week and don't have an answer.There will always come a point where the resources will be overwhelmed byt
he requests. The more queries you attempt to do simultaneously the less
chances are they will each perform as well as running by them selves. At
some point the cpu or disk queues will start playing a factor. You also
have more overhead when trying to put all the results together and have more
chance of tempdb being a factor.
--
Andrew J. Kelly
SQL Server MVP
"Ramesh" <anonymous@.discussions.microsoft.com> wrote in message
news:119d201c3f56c$fba82640$a001280a@.phx.gbl...
> 1. When I partition a table ( 90 million rows ) into
> five partitions. (create view as select * from table1
> union select * from table 2 union ...)
> I get a 2 x times query time increase, but by the time
> #partitions = 10, the query time is same as the large
> table. when #partitions = 15 query time > large table.
> Ideally I would want to partition it into 50 states.
> Why is it that parallel query execution does not speedup
> the process.
> The indexes are virtually the same ( most cases covering
> non-clustered indexes.)
> Is it because of merging the results and sorting them
> ( because of the group by/order by clauses )? however
> the query plan says those are 0%.
> You can ask me any question about the query statement,
> table format and query plan execution.
> I thought about this for a week and don't have an answer.|||cpu utilization seems low : < 40 %
disk I/O queues < 15 on RAID-5 containing tables
(6 disks) and < 3 on RAID-10 containing indexes
(6 disks)
tempdb and log on seperate RAID-10 ( disk queues < 2)
(4 disks)
It is true that queries which only use indexes (covering)
work well upto 10 partitions
queries which would need to use leaf keys and then use
clustering keys work well upto 5 partitions.
Is there something I should know on creating the
clustered/non-clustered indexes different on the
partitioned tables?
Is there some material I can read on?
Is the windows 2000 server be part of the bottleneck?
Is there any specific parameters I should target and
resolve. ( I am aiming for 60 partitions : each with
1-2 million rows each to reduce skewness). I thought this
would speed up the queries 2 orders of magnitude. Sadly
this does not work the way I calculated.
>--Original Message--
>There will always come a point where the resources will
be overwhelmed byt
>he requests. The more queries you attempt to do
simultaneously the less
>chances are they will each perform as well as running by
them selves. At
>some point the cpu or disk queues will start playing a
factor. You also
>have more overhead when trying to put all the results
together and have more
>chance of tempdb being a factor.
>--
>Andrew J. Kelly
>SQL Server MVP
>
>"Ramesh" <anonymous@.discussions.microsoft.com> wrote in
message
>news:119d201c3f56c$fba82640$a001280a@.phx.gbl...
>> 1. When I partition a table ( 90 million rows ) into
>> five partitions. (create view as select * from table1
>> union select * from table 2 union ...)
>> I get a 2 x times query time increase, but by the
time
>> #partitions = 10, the query time is same as the large
>> table. when #partitions = 15 query time > large table.
>> Ideally I would want to partition it into 50 states.
>> Why is it that parallel query execution does not
speedup
>> the process.
>> The indexes are virtually the same ( most cases
covering
>> non-clustered indexes.)
>> Is it because of merging the results and sorting them
>> ( because of the group by/order by clauses )? however
>> the query plan says those are 0%.
>> You can ask me any question about the query statement,
>> table format and query plan execution.
>> I thought about this for a week and don't have an
answer.
>
>.
>|||Disk queues of 15 or so on a 6 disk Raid 5 are still high enough to warrant
paying attention to them. That means you are in fact waiting on disk I/O.
You might get better results from combining all 12 disks from the indexes
and data into one Raid 10. As for the partitioning that is tough to say.
Most partitioning requires a lot of testing under your specific conditions
to determine which is best for you. Are you sure you need to partition that
table at all? 50 partitions is a lot and you only have 90 million rows.
What are the typical queries like against this table? Maybe just adjusting
the clustered index will do.
--
Andrew J. Kelly
SQL Server MVP
"Ramesh Krishnan" <anonymous@.discussions.microsoft.com> wrote in message
news:11d6201c3f62b$ea334100$a301280a@.phx.gbl...
> cpu utilization seems low : < 40 %
> disk I/O queues < 15 on RAID-5 containing tables
> (6 disks) and < 3 on RAID-10 containing indexes
> (6 disks)
> tempdb and log on seperate RAID-10 ( disk queues < 2)
> (4 disks)
> It is true that queries which only use indexes (covering)
> work well upto 10 partitions
> queries which would need to use leaf keys and then use
> clustering keys work well upto 5 partitions.
> Is there something I should know on creating the
> clustered/non-clustered indexes different on the
> partitioned tables?
> Is there some material I can read on?
> Is the windows 2000 server be part of the bottleneck?
> Is there any specific parameters I should target and
> resolve. ( I am aiming for 60 partitions : each with
> 1-2 million rows each to reduce skewness). I thought this
> would speed up the queries 2 orders of magnitude. Sadly
> this does not work the way I calculated.
> >--Original Message--
> >There will always come a point where the resources will
> be overwhelmed byt
> >he requests. The more queries you attempt to do
> simultaneously the less
> >chances are they will each perform as well as running by
> them selves. At
> >some point the cpu or disk queues will start playing a
> factor. You also
> >have more overhead when trying to put all the results
> together and have more
> >chance of tempdb being a factor.
> >
> >--
> >
> >Andrew J. Kelly
> >SQL Server MVP
> >
> >
> >"Ramesh" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:119d201c3f56c$fba82640$a001280a@.phx.gbl...
> >> 1. When I partition a table ( 90 million rows ) into
> >> five partitions. (create view as select * from table1
> >> union select * from table 2 union ...)
> >> I get a 2 x times query time increase, but by the
> time
> >> #partitions = 10, the query time is same as the large
> >> table. when #partitions = 15 query time > large table.
> >>
> >> Ideally I would want to partition it into 50 states.
> >> Why is it that parallel query execution does not
> speedup
> >> the process.
> >>
> >> The indexes are virtually the same ( most cases
> covering
> >> non-clustered indexes.)
> >> Is it because of merging the results and sorting them
> >> ( because of the group by/order by clauses )? however
> >> the query plan says those are 0%.
> >>
> >> You can ask me any question about the query statement,
> >> table format and query plan execution.
> >>
> >> I thought about this for a week and don't have an
> answer.
> >
> >
> >.
> >sql
Friday, March 23, 2012
question about linked servers and Access tables
Having a little problem with linking to Access tables. I have an Access database that has in it, some linked tables. After setting up the linked server in SQL server, none of the linked tables in Access show up, only the tables that were created or imported show up.
Is there anything you can do to get the linked tables in Access to show up under the tables list in the SQL linked server?Can you query the Access table(s) using the 4 part naming convention?
select * from <server>.<db>.<owner>.<table>|||haven't tried that, didn't consider it since the tables do not show up. but will give it a shot.|||Got an error saying the file was open exclusively by another or i dont have permissions, which is not the case. The linked tables are from an Oracle database. So the permission problem might be with it.
Wednesday, March 21, 2012
Question about joining 2 tables??
Hi everyone,
I have problem to join 2 tables together to show the selected results
into a datagrid.
table1 hosts all customer personal information.
table2 hosts all the trasaction records for each customer
table1 has fields such as "customerID", "CustomerName" and "TelNumber".
(customerID is primary key in table1)
table2 has fields such as "cusomterID", "ComponentPurchase" and
"Quantity"
table1
CustomerID CustomerName TelNumber
55 John 1234566
56 David 6589211
table2
CustomerID ComponentPurchase Quantity
55 componentA 10
55 componentB 5
55 componentC 1
56 componentA 2
how can i join these two tables and get the result datagrid that list
each customer personal information
and the component he/she purchase listed below his/her personal
information' such as look like following
one
John 1234566
componentA 10
componentB 5
componentC 1
David 6589211
componentA 2
Could everyone give me some suggestion, or any article i can read?
thanks for your time
WingWing wrote:
> Hi everyone,
> I have problem to join 2 tables together to show the selected results
> into a datagrid.
> table1 hosts all customer personal information.
> table2 hosts all the trasaction records for each customer
> table1 has fields such as "customerID", "CustomerName" and
> "TelNumber". (customerID is primary key in table1)
> table2 has fields such as "cusomterID", "ComponentPurchase" and
> "Quantity"
> table1
> CustomerID CustomerName TelNumber
> 55 John 1234566
> 56 David 6589211
>
> table2
> CustomerID ComponentPurchase Quantity
> 55 componentA 10
> 55 componentB 5
> 55 componentC 1
> 56 componentA 2
>
> how can i join these two tables and get the result datagrid that list
> each customer personal information
> and the component he/she purchase listed below his/her personal
> information' such as look like following
> one
> John 1234566
> componentA 10
> componentB 5
> componentC 1
> David 6589211
> componentA 2
> Could everyone give me some suggestion, or any article i can read?
> thanks for your time
> Wing
This is a UI design issue and not a SQL Server one. I assume you know
how to write a SELECT statement that joins two tables together. How that
information is evenutally displays in the UI depends on what controls
you are using and your code. This is not really a SQL issue. Many data
grids support a parent-child style layout, but that's a question for
another group.
David Gugick - SQL Server MVP
Quest Software|||Thanks for your comment
Wing
I have problem to join 2 tables together to show the selected results
into a datagrid.
table1 hosts all customer personal information.
table2 hosts all the trasaction records for each customer
table1 has fields such as "customerID", "CustomerName" and "TelNumber".
(customerID is primary key in table1)
table2 has fields such as "cusomterID", "ComponentPurchase" and
"Quantity"
table1
CustomerID CustomerName TelNumber
55 John 1234566
56 David 6589211
table2
CustomerID ComponentPurchase Quantity
55 componentA 10
55 componentB 5
55 componentC 1
56 componentA 2
how can i join these two tables and get the result datagrid that list
each customer personal information
and the component he/she purchase listed below his/her personal
information' such as look like following
one
John 1234566
componentA 10
componentB 5
componentC 1
David 6589211
componentA 2
Could everyone give me some suggestion, or any article i can read?
thanks for your time
WingWing wrote:
> Hi everyone,
> I have problem to join 2 tables together to show the selected results
> into a datagrid.
> table1 hosts all customer personal information.
> table2 hosts all the trasaction records for each customer
> table1 has fields such as "customerID", "CustomerName" and
> "TelNumber". (customerID is primary key in table1)
> table2 has fields such as "cusomterID", "ComponentPurchase" and
> "Quantity"
> table1
> CustomerID CustomerName TelNumber
> 55 John 1234566
> 56 David 6589211
>
> table2
> CustomerID ComponentPurchase Quantity
> 55 componentA 10
> 55 componentB 5
> 55 componentC 1
> 56 componentA 2
>
> how can i join these two tables and get the result datagrid that list
> each customer personal information
> and the component he/she purchase listed below his/her personal
> information' such as look like following
> one
> John 1234566
> componentA 10
> componentB 5
> componentC 1
> David 6589211
> componentA 2
> Could everyone give me some suggestion, or any article i can read?
> thanks for your time
> Wing
This is a UI design issue and not a SQL Server one. I assume you know
how to write a SELECT statement that joins two tables together. How that
information is evenutally displays in the UI depends on what controls
you are using and your code. This is not really a SQL issue. Many data
grids support a parent-child style layout, but that's a question for
another group.
David Gugick - SQL Server MVP
Quest Software|||Thanks for your comment
Wing
Question about joining 2 tables??
Hi everyone,
I have problem to join 2 tables together to show the selected results
into a datagrid.
table1 hosts all customer personal information.
table2 hosts all the trasaction records for each customer
table1 has fields such as "customerID", "CustomerName" and "TelNumber".
(customerID is primary key in table1)
table2 has fields such as "cusomterID", "ComponentPurchase" and
"Quantity"
table1
CustomerID CustomerName TelNumber
55 John 1234566
56 David 6589211
table2
CustomerID ComponentPurchase Quantity
55 componentA 10
55 componentB 5
55 componentC 1
56 componentA 2
how can i join these two tables and get the result datagrid that list
each customer personal information
and the component he/she purchase listed below his/her personal
information' such as look like following
one
John 1234566
componentA 10
componentB 5
componentC 1
David 6589211
componentA 2
Could everyone give me some suggestion, or any article i can read?
thanks for your time
WingWing wrote:
> Hi everyone,
> I have problem to join 2 tables together to show the selected results
> into a datagrid.
> table1 hosts all customer personal information.
> table2 hosts all the trasaction records for each customer
> table1 has fields such as "customerID", "CustomerName" and
> "TelNumber". (customerID is primary key in table1)
> table2 has fields such as "cusomterID", "ComponentPurchase" and
> "Quantity"
> table1
> CustomerID CustomerName TelNumber
> 55 John 1234566
> 56 David 6589211
>
> table2
> CustomerID ComponentPurchase Quantity
> 55 componentA 10
> 55 componentB 5
> 55 componentC 1
> 56 componentA 2
>
> how can i join these two tables and get the result datagrid that list
> each customer personal information
> and the component he/she purchase listed below his/her personal
> information' such as look like following
> one
> John 1234566
> componentA 10
> componentB 5
> componentC 1
> David 6589211
> componentA 2
> Could everyone give me some suggestion, or any article i can read?
> thanks for your time
> Wing
This is a UI design issue and not a SQL Server one. I assume you know
how to write a SELECT statement that joins two tables together. How that
information is evenutally displays in the UI depends on what controls
you are using and your code. This is not really a SQL issue. Many data
grids support a parent-child style layout, but that's a question for
another group.
--
David Gugick - SQL Server MVP
Quest Software|||Thanks for your comment
Wing
I have problem to join 2 tables together to show the selected results
into a datagrid.
table1 hosts all customer personal information.
table2 hosts all the trasaction records for each customer
table1 has fields such as "customerID", "CustomerName" and "TelNumber".
(customerID is primary key in table1)
table2 has fields such as "cusomterID", "ComponentPurchase" and
"Quantity"
table1
CustomerID CustomerName TelNumber
55 John 1234566
56 David 6589211
table2
CustomerID ComponentPurchase Quantity
55 componentA 10
55 componentB 5
55 componentC 1
56 componentA 2
how can i join these two tables and get the result datagrid that list
each customer personal information
and the component he/she purchase listed below his/her personal
information' such as look like following
one
John 1234566
componentA 10
componentB 5
componentC 1
David 6589211
componentA 2
Could everyone give me some suggestion, or any article i can read?
thanks for your time
WingWing wrote:
> Hi everyone,
> I have problem to join 2 tables together to show the selected results
> into a datagrid.
> table1 hosts all customer personal information.
> table2 hosts all the trasaction records for each customer
> table1 has fields such as "customerID", "CustomerName" and
> "TelNumber". (customerID is primary key in table1)
> table2 has fields such as "cusomterID", "ComponentPurchase" and
> "Quantity"
> table1
> CustomerID CustomerName TelNumber
> 55 John 1234566
> 56 David 6589211
>
> table2
> CustomerID ComponentPurchase Quantity
> 55 componentA 10
> 55 componentB 5
> 55 componentC 1
> 56 componentA 2
>
> how can i join these two tables and get the result datagrid that list
> each customer personal information
> and the component he/she purchase listed below his/her personal
> information' such as look like following
> one
> John 1234566
> componentA 10
> componentB 5
> componentC 1
> David 6589211
> componentA 2
> Could everyone give me some suggestion, or any article i can read?
> thanks for your time
> Wing
This is a UI design issue and not a SQL Server one. I assume you know
how to write a SELECT statement that joins two tables together. How that
information is evenutally displays in the UI depends on what controls
you are using and your code. This is not really a SQL issue. Many data
grids support a parent-child style layout, but that's a question for
another group.
--
David Gugick - SQL Server MVP
Quest Software|||Thanks for your comment
Wing
Question about joining 2 tables??
Hi everyone,
I have problem to join 2 tables together to show the selected results
into a datagrid.
table1 hosts all customer personal information.
table2 hosts all the trasaction records for each customer
table1 has fields such as "customerID", "CustomerName" and "TelNumber".
(customerID is primary key in table1)
table2 has fields such as "cusomterID", "ComponentPurchase" and
"Quantity"
table1
CustomerID CustomerName TelNumber
55 John 1234566
56 David 6589211
table2
CustomerID ComponentPurchase Quantity
55 componentA 10
55 componentB 5
55 componentC 1
56 componentA 2
how can i join these two tables and get the result datagrid that list
each customer personal information
and the component he/she purchase listed below his/her personal
information? such as look like following
one
John 1234566
componentA 10
componentB 5
componentC 1
David 6589211
componentA 2
Could everyone give me some suggestion, or any article i can read?
thanks for your time
Wing
Wing wrote:
> Hi everyone,
> I have problem to join 2 tables together to show the selected results
> into a datagrid.
> table1 hosts all customer personal information.
> table2 hosts all the trasaction records for each customer
> table1 has fields such as "customerID", "CustomerName" and
> "TelNumber". (customerID is primary key in table1)
> table2 has fields such as "cusomterID", "ComponentPurchase" and
> "Quantity"
> table1
> CustomerID CustomerName TelNumber
> 55 John 1234566
> 56 David 6589211
>
> table2
> CustomerID ComponentPurchase Quantity
> 55 componentA 10
> 55 componentB 5
> 55 componentC 1
> 56 componentA 2
>
> how can i join these two tables and get the result datagrid that list
> each customer personal information
> and the component he/she purchase listed below his/her personal
> information? such as look like following
> one
> John 1234566
> componentA 10
> componentB 5
> componentC 1
> David 6589211
> componentA 2
> Could everyone give me some suggestion, or any article i can read?
> thanks for your time
> Wing
This is a UI design issue and not a SQL Server one. I assume you know
how to write a SELECT statement that joins two tables together. How that
information is evenutally displays in the UI depends on what controls
you are using and your code. This is not really a SQL issue. Many data
grids support a parent-child style layout, but that's a question for
another group.
David Gugick - SQL Server MVP
Quest Software
|||Thanks for your comment
Wing
I have problem to join 2 tables together to show the selected results
into a datagrid.
table1 hosts all customer personal information.
table2 hosts all the trasaction records for each customer
table1 has fields such as "customerID", "CustomerName" and "TelNumber".
(customerID is primary key in table1)
table2 has fields such as "cusomterID", "ComponentPurchase" and
"Quantity"
table1
CustomerID CustomerName TelNumber
55 John 1234566
56 David 6589211
table2
CustomerID ComponentPurchase Quantity
55 componentA 10
55 componentB 5
55 componentC 1
56 componentA 2
how can i join these two tables and get the result datagrid that list
each customer personal information
and the component he/she purchase listed below his/her personal
information? such as look like following
one
John 1234566
componentA 10
componentB 5
componentC 1
David 6589211
componentA 2
Could everyone give me some suggestion, or any article i can read?
thanks for your time
Wing
Wing wrote:
> Hi everyone,
> I have problem to join 2 tables together to show the selected results
> into a datagrid.
> table1 hosts all customer personal information.
> table2 hosts all the trasaction records for each customer
> table1 has fields such as "customerID", "CustomerName" and
> "TelNumber". (customerID is primary key in table1)
> table2 has fields such as "cusomterID", "ComponentPurchase" and
> "Quantity"
> table1
> CustomerID CustomerName TelNumber
> 55 John 1234566
> 56 David 6589211
>
> table2
> CustomerID ComponentPurchase Quantity
> 55 componentA 10
> 55 componentB 5
> 55 componentC 1
> 56 componentA 2
>
> how can i join these two tables and get the result datagrid that list
> each customer personal information
> and the component he/she purchase listed below his/her personal
> information? such as look like following
> one
> John 1234566
> componentA 10
> componentB 5
> componentC 1
> David 6589211
> componentA 2
> Could everyone give me some suggestion, or any article i can read?
> thanks for your time
> Wing
This is a UI design issue and not a SQL Server one. I assume you know
how to write a SELECT statement that joins two tables together. How that
information is evenutally displays in the UI depends on what controls
you are using and your code. This is not really a SQL issue. Many data
grids support a parent-child style layout, but that's a question for
another group.
David Gugick - SQL Server MVP
Quest Software
|||Thanks for your comment
Wing
Question about Joining 2 tables?
Hi everyone,
I have problem to join 2 tables together to show the selected results
into a datagrid.
table1 hosts all customer personal information.
table2 hosts all the trasaction records for each customer
table1 has fields such as "customerID", "CustomerName" and "TelNumber".
(customerID is primary key in table1)
table2 has fields such as "cusomterID", "ComponentPurchase" and
"Quantity"
table1
CustomerID CustomerName TelNumber
55 John 1234566
56 David 6589211
table2
CustomerID ComponentPurchase Quantity
55 componentA 10
55 componentB 5
55 componentC 1
56 componentA 2
how can i join these two tables and get the result datagrid that list
each customer personal information and the component he/she purchased
listed below his/her personal information?? such as look like following
one
--------------
John 1234566
componentA 10
componentB 5
componentC 1
--------------
David 6589211
componentA 2
--------------
Could everyone give me some suggestion, or any article i can read?
thanks for your time
Wing"Wing" <li.alwin@.gmail.com> wrote in news:1140828715.240420.308800
@.u72g2000cwu.googlegroups.com:
> Hi everyone,
> I have problem to join 2 tables together to show the selected results
> into a datagrid.
>
> Could everyone give me some suggestion, or any article i can read?
My suggestion:
Do your own homework. You'll learn more from the class that way.|||I am doing my own homework, just need some knowledge that to start my
work.
thanks for comment.sql
Question about JOIN syntax
what is the syntax to join a table with the result of antoher query.
For example i have two tables
Create Table Customers (
CustomerID int,
LastPaymentDate Datetime )
Create Table Payments (
PaymentID int,
CustomerID int,
PaymentDate Datetime )
What query will bring me the customers whose lastpaymentdate in the
customers table is not correct.
That can only be checked by comparing it with the max paymentdate for each
customer in the payments table.
I want this to be done by ansi standard sql. Not using any specific feature
of sql server.
thxSELECT *
FROM Customers AS C
WHERE lastpaymentdate <>
(SELECT MAX(paymentdate)
FROM Payments AS P
WHERE P.customerid=C.customerid)
--
David Portas
--
Please reply only to the newsgroup
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:2vydnRekZvROvmiiRVn-sw@.giganews.com...
> SELECT *
> FROM Customers AS C
> WHERE lastpaymentdate <>
> (SELECT MAX(paymentdate)
> FROM Payments AS P
> WHERE P.customerid=C.customerid)
>
oh may be my example was not the best!
what if I have
Create Table Customers (
CustomerID int,
LastPaymentAmount int,
LastPaymentDate Datetime )
Create Table Payments (
PaymentID int,
CustomerID int,
Amount int,
PaymentDate Datetime )
and I want to see if the LastPaymentAmount field holds correct values.
I couldnt use the max function for this obviously.
thx|||> I couldnt use the max function for this obviously.
Why not? My query should still work.
If you want a query that uses a join then try this:
SELECT C.*
FROM Customers AS C
LEFT JOIN
(SELECT customerid, MAX(paymentdate) AS lastpaymentdate
FROM Payments
GROUP BY customerid) AS P
ON C.customerid=P.customerid
AND C.lastpaymentdate=P.lastpaymentdate
WHERE C.lastpaymentdate IS NOT NULL
AND P.customerid IS NULL
This has a possible advantage over the correlated subquery version. It will
include Customers who have a Lastpaymentdate but don't have any non-NULL
dates in the Payments table.
--
David Portas
--
Please reply only to the newsgroup
--|||Oops, you wanted the *correct* values:
SELECT C.*
FROM Customers AS C
JOIN
(SELECT customerid, MAX(paymentdate) AS lastpaymentdate
FROM Payments
GROUP BY customerid) AS P
ON C.customerid=P.customerid
AND C.lastpaymentdate=P.lastpaymentdate
--
David Portas
--
Please reply only to the newsgroup
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:4eOdnfkpJ63Bqmii4p2dnA@.giganews.com...
> Oops, you wanted the *correct* values:
> SELECT C.*
> FROM Customers AS C
> JOIN
> (SELECT customerid, MAX(paymentdate) AS lastpaymentdate
> FROM Payments
> GROUP BY customerid) AS P
> ON C.customerid=P.customerid
> AND C.lastpaymentdate=P.lastpaymentdate
> --
> David Portas
> --
> Please reply only to the newsgroup
> --
I want the LastPaymentAmount of Customers table to be compaired with the
Amount of the last payment in Payments table.
Ofcourse the last payment is the one with the maximum PaymentDate but the
Amount fields should be compared in both tables to see if they are equal.|||You didn't include any keys or constraints with your DDL but I'll have to
assume that (customerid, paymentdate) is unique in Payments - otherwise how
will you define which payment is the latest?
SELECT C.customerid, C.lastpaymentamount, C.lastpaymentdate
FROM
(SELECT customerid,
MAX(paymentdate) AS paymentdate
FROM Payments
GROUP BY customerid) AS P1
JOIN Payments AS P2
ON P1.customerid = P2.customerid
AND P1.paymentdate = P2.paymentdate
JOIN Customers AS C
ON P2.customerid = C.customerid
AND P2.paymentdate = C.lastpaymentdate
AND P2.amount = C.lastpaymentamount
--
David Portas
--
Please reply only to the newsgroup
--|||>> what query will bring me the customers whose last_payment_date in
the
customers table is not correct. <<
Your design is wrong; this is a computed value and you do not store
computed values in an RDBMS. Your Customers table should not exist at
all! And you do not have keys or anything that makes payments into a
table. Let's get the basics right:
CREATE TABLE Payments
(payment_id INTEGER NOT NULL PRIMARY KEY,
customer_id INTEGER NOT NULL
REFERENCES Customers
ON DELETE CASCADE
ON UPDATE CASCADE,
payment_date DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL);
Now create a VIEW which will always be correct:
CREATE VIEW CurrentPayments (payment_id, customer_id, payment_date)
AS
SELECT payment_id, customer_id, payment_date
FROM Payments AS P1
WHERE P1.payment_date
= (SELECT MAX(payment_date)
FROM Payments AS P2
WHERE P1.customer_id = P2.customer_id);|||--CELKO-- (joe.celko@.northface.edu) writes:
> Your design is wrong; this is a computed value and you do not store
> computed values in an RDBMS.
Maybe in some small ideal model of a database you don't.
In real-world databases, you often find that you can save a lot of
cycles by storing computed data. For instance, if you have a transactions
table for a bank account, you probably want to store the balance on the
account after each transaction, because you will have several functions
in you system where you display this information. Having it computed
means that this function will run faster, and that the likelyhood that
they will show the wrong value because of bugs is less.
The one problem with storing computed values, is that if an underlying
value changes, the computed value changes too. But in the example I
just gave, this never happens due to business rules.
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp|||>> this is a computed value and you do not store computed values in an
RDBMS. <<
May I know why so? Snapshots and stored joins, as recommended by the
relational model are typical examples where computed values are stored in an
RDBMS.
In SQL Server, you can materialize computed columns and views by physically
clustering them and I don't see why this should not be done, when required.
--
- Anith
( Please reply to newsgroups only )
For example i have two tables
Create Table Customers (
CustomerID int,
LastPaymentDate Datetime )
Create Table Payments (
PaymentID int,
CustomerID int,
PaymentDate Datetime )
What query will bring me the customers whose lastpaymentdate in the
customers table is not correct.
That can only be checked by comparing it with the max paymentdate for each
customer in the payments table.
I want this to be done by ansi standard sql. Not using any specific feature
of sql server.
thxSELECT *
FROM Customers AS C
WHERE lastpaymentdate <>
(SELECT MAX(paymentdate)
FROM Payments AS P
WHERE P.customerid=C.customerid)
--
David Portas
--
Please reply only to the newsgroup
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:2vydnRekZvROvmiiRVn-sw@.giganews.com...
> SELECT *
> FROM Customers AS C
> WHERE lastpaymentdate <>
> (SELECT MAX(paymentdate)
> FROM Payments AS P
> WHERE P.customerid=C.customerid)
>
oh may be my example was not the best!
what if I have
Create Table Customers (
CustomerID int,
LastPaymentAmount int,
LastPaymentDate Datetime )
Create Table Payments (
PaymentID int,
CustomerID int,
Amount int,
PaymentDate Datetime )
and I want to see if the LastPaymentAmount field holds correct values.
I couldnt use the max function for this obviously.
thx|||> I couldnt use the max function for this obviously.
Why not? My query should still work.
If you want a query that uses a join then try this:
SELECT C.*
FROM Customers AS C
LEFT JOIN
(SELECT customerid, MAX(paymentdate) AS lastpaymentdate
FROM Payments
GROUP BY customerid) AS P
ON C.customerid=P.customerid
AND C.lastpaymentdate=P.lastpaymentdate
WHERE C.lastpaymentdate IS NOT NULL
AND P.customerid IS NULL
This has a possible advantage over the correlated subquery version. It will
include Customers who have a Lastpaymentdate but don't have any non-NULL
dates in the Payments table.
--
David Portas
--
Please reply only to the newsgroup
--|||Oops, you wanted the *correct* values:
SELECT C.*
FROM Customers AS C
JOIN
(SELECT customerid, MAX(paymentdate) AS lastpaymentdate
FROM Payments
GROUP BY customerid) AS P
ON C.customerid=P.customerid
AND C.lastpaymentdate=P.lastpaymentdate
--
David Portas
--
Please reply only to the newsgroup
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:4eOdnfkpJ63Bqmii4p2dnA@.giganews.com...
> Oops, you wanted the *correct* values:
> SELECT C.*
> FROM Customers AS C
> JOIN
> (SELECT customerid, MAX(paymentdate) AS lastpaymentdate
> FROM Payments
> GROUP BY customerid) AS P
> ON C.customerid=P.customerid
> AND C.lastpaymentdate=P.lastpaymentdate
> --
> David Portas
> --
> Please reply only to the newsgroup
> --
I want the LastPaymentAmount of Customers table to be compaired with the
Amount of the last payment in Payments table.
Ofcourse the last payment is the one with the maximum PaymentDate but the
Amount fields should be compared in both tables to see if they are equal.|||You didn't include any keys or constraints with your DDL but I'll have to
assume that (customerid, paymentdate) is unique in Payments - otherwise how
will you define which payment is the latest?
SELECT C.customerid, C.lastpaymentamount, C.lastpaymentdate
FROM
(SELECT customerid,
MAX(paymentdate) AS paymentdate
FROM Payments
GROUP BY customerid) AS P1
JOIN Payments AS P2
ON P1.customerid = P2.customerid
AND P1.paymentdate = P2.paymentdate
JOIN Customers AS C
ON P2.customerid = C.customerid
AND P2.paymentdate = C.lastpaymentdate
AND P2.amount = C.lastpaymentamount
--
David Portas
--
Please reply only to the newsgroup
--|||>> what query will bring me the customers whose last_payment_date in
the
customers table is not correct. <<
Your design is wrong; this is a computed value and you do not store
computed values in an RDBMS. Your Customers table should not exist at
all! And you do not have keys or anything that makes payments into a
table. Let's get the basics right:
CREATE TABLE Payments
(payment_id INTEGER NOT NULL PRIMARY KEY,
customer_id INTEGER NOT NULL
REFERENCES Customers
ON DELETE CASCADE
ON UPDATE CASCADE,
payment_date DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL);
Now create a VIEW which will always be correct:
CREATE VIEW CurrentPayments (payment_id, customer_id, payment_date)
AS
SELECT payment_id, customer_id, payment_date
FROM Payments AS P1
WHERE P1.payment_date
= (SELECT MAX(payment_date)
FROM Payments AS P2
WHERE P1.customer_id = P2.customer_id);|||--CELKO-- (joe.celko@.northface.edu) writes:
> Your design is wrong; this is a computed value and you do not store
> computed values in an RDBMS.
Maybe in some small ideal model of a database you don't.
In real-world databases, you often find that you can save a lot of
cycles by storing computed data. For instance, if you have a transactions
table for a bank account, you probably want to store the balance on the
account after each transaction, because you will have several functions
in you system where you display this information. Having it computed
means that this function will run faster, and that the likelyhood that
they will show the wrong value because of bugs is less.
The one problem with storing computed values, is that if an underlying
value changes, the computed value changes too. But in the example I
just gave, this never happens due to business rules.
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp|||>> this is a computed value and you do not store computed values in an
RDBMS. <<
May I know why so? Snapshots and stored joins, as recommended by the
relational model are typical examples where computed values are stored in an
RDBMS.
In SQL Server, you can materialize computed columns and views by physically
clustering them and I don't see why this should not be done, when required.
--
- Anith
( Please reply to newsgroups only )
Question about indexdefrag
Hi,
Can anyone explain me:
I have a few tables (some with and some without indexes +
some with and some without PK (ex i have a table that
doesnt have a primary key NOR indexes)) yes these tables a
fragmented (dbcc showcontig shows fragmentation). I
created a job to run the following commands on the tabled
in order to defrag the tables:
DBCC DBREINDEX
DBCC CHECKTABLE
DBCC Cleantable
followed by,
DBCC CHECKALLOC
DBCC UPDATEUSAGE
Howevere the tables stay the same (fragmented). I must be
very careful with these tables because they are tables of
a production server that is constantly being used. If
suffers alot of updates/inserts/delete and i cannot seem
to find a way to defrag the tables.
Can anyone help me?You cannot defrag datapages which aren't part of an index using DBREINDEX no
r INDEXDEFRAG. You can
create a clustered index, and possibly drop it. Or export and import the dat
a.
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=...ls
erver
"Claudia" <anonymous@.discussions.microsoft.com> wrote in message
news:530901c3e4c6$724a10e0$a601280a@.phx.gbl...
indexes to defrag the tables... will this cause a big
burden on my server... i know it depends on the size of
the table and amount of data but in a "general point of
view"...... is that really the ONLY possible choice?
using DBREINDEX nor INDEXDEFRAG. You can
and import the data.
oi=djq&as_ugroup=microsoft.public.sqlserver
message
exclusive lock), and
naturally, SQL Server has to do the sort and all the I/O etc. Also, all non-
clustered indexes are
re-created when you create or drop a clustered index. So, you really have to
test this if it is
feasible in your environment. Also, test that against the export/import stra
tegy.
As I mentioned, INDEXDEFRAG or DBREINDEX will not shuffle or re-claim storag
e in any way for pages
which aren't part of an index. I do not right now see any other way to do th
is but the clustered
index or export/import options.
This behavior is for some a part of the reason to have a clustered index on
the table in the first
place.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=...ls
erver
"Claudia" <anonymous@.discussions.microsoft.com> wrote in message
news:48eb01c3e4ca$459c1710$a301280a@.phx.gbl...[QUOTE]
> What kind of "impact" would i have if i create/drop
> indexes to defrag the tables... will this cause a big
> burden on my server... i know it depends on the size of
> the table and amount of data but in a "general point of
> view"...... is that really the ONLY possible choice?
> using DBREINDEX nor INDEXDEFRAG. You can
> and import the data.
> oi=djq&as_ugroup=microsoft.public.sqlserver
> message
> +
> tables a
> tabled
> be
> of|||Furthe to what Tibor has already said, you should read the whitepaper at
http://www.microsoft.com/technet/tr...ze/ss2kidbp.asp
I'm assuming you're running SQL Server 2000. Can you explain why you run the
the sequence of commands you give below?
Regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Claudia" <anonymous@.discussions.microsoft.com> wrote in message
news:530901c3e4c6$724a10e0$a601280a@.phx.gbl...
Can anyone explain me:
I have a few tables (some with and some without indexes +
some with and some without PK (ex i have a table that
doesnt have a primary key NOR indexes)) yes these tables a
fragmented (dbcc showcontig shows fragmentation). I
created a job to run the following commands on the tabled
in order to defrag the tables:
DBCC DBREINDEX
DBCC CHECKTABLE
DBCC Cleantable
followed by,
DBCC CHECKALLOC
DBCC UPDATEUSAGE
Howevere the tables stay the same (fragmented). I must be
very careful with these tables because they are tables of
a production server that is constantly being used. If
suffers alot of updates/inserts/delete and i cannot seem
to find a way to defrag the tables.
Can anyone help me?You cannot defrag datapages which aren't part of an index using DBREINDEX no
r INDEXDEFRAG. You can
create a clustered index, and possibly drop it. Or export and import the dat
a.
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=...ls
erver
"Claudia" <anonymous@.discussions.microsoft.com> wrote in message
news:530901c3e4c6$724a10e0$a601280a@.phx.gbl...
quote:|||What kind of "impact" would i have if i create/drop
> Hi,
> Can anyone explain me:
> I have a few tables (some with and some without indexes +
> some with and some without PK (ex i have a table that
> doesnt have a primary key NOR indexes)) yes these tables a
> fragmented (dbcc showcontig shows fragmentation). I
> created a job to run the following commands on the tabled
> in order to defrag the tables:
> DBCC DBREINDEX
> DBCC CHECKTABLE
> DBCC Cleantable
> followed by,
> DBCC CHECKALLOC
> DBCC UPDATEUSAGE
> Howevere the tables stay the same (fragmented). I must be
> very careful with these tables because they are tables of
> a production server that is constantly being used. If
> suffers alot of updates/inserts/delete and i cannot seem
> to find a way to defrag the tables.
> Can anyone help me?
indexes to defrag the tables... will this cause a big
burden on my server... i know it depends on the size of
the table and amount of data but in a "general point of
view"...... is that really the ONLY possible choice?
quote:
>--Original Message--
>You cannot defrag datapages which aren't part of an index
using DBREINDEX nor INDEXDEFRAG. You can
quote:
>create a clustered index, and possibly drop it. Or export
and import the data.
quote:
>--
>Tibor Karaszi, SQL Server MVP
>Archive at: http://groups.google.com/groups?
oi=djq&as_ugroup=microsoft.public.sqlserver
quote:
>
>"Claudia" <anonymous@.discussions.microsoft.com> wrote in
message
quote:|||BOL documents what locks are acquired when you create a clustered index (an
>news:530901c3e4c6$724a10e0$a601280a@.phx.gbl...
+[QUOTE]
tables a[QUOTE]
tabled[QUOTE]
be[QUOTE]
of[QUOTE]
>
>.
>
exclusive lock), and
naturally, SQL Server has to do the sort and all the I/O etc. Also, all non-
clustered indexes are
re-created when you create or drop a clustered index. So, you really have to
test this if it is
feasible in your environment. Also, test that against the export/import stra
tegy.
As I mentioned, INDEXDEFRAG or DBREINDEX will not shuffle or re-claim storag
e in any way for pages
which aren't part of an index. I do not right now see any other way to do th
is but the clustered
index or export/import options.
This behavior is for some a part of the reason to have a clustered index on
the table in the first
place.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=...ls
erver
"Claudia" <anonymous@.discussions.microsoft.com> wrote in message
news:48eb01c3e4ca$459c1710$a301280a@.phx.gbl...[QUOTE]
> What kind of "impact" would i have if i create/drop
> indexes to defrag the tables... will this cause a big
> burden on my server... i know it depends on the size of
> the table and amount of data but in a "general point of
> view"...... is that really the ONLY possible choice?
> using DBREINDEX nor INDEXDEFRAG. You can
> and import the data.
> oi=djq&as_ugroup=microsoft.public.sqlserver
> message
> +
> tables a
> tabled
> be
> of|||Furthe to what Tibor has already said, you should read the whitepaper at
http://www.microsoft.com/technet/tr...ze/ss2kidbp.asp
I'm assuming you're running SQL Server 2000. Can you explain why you run the
the sequence of commands you give below?
Regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Claudia" <anonymous@.discussions.microsoft.com> wrote in message
news:530901c3e4c6$724a10e0$a601280a@.phx.gbl...
quote:
> Hi,
> Can anyone explain me:
> I have a few tables (some with and some without indexes +
> some with and some without PK (ex i have a table that
> doesnt have a primary key NOR indexes)) yes these tables a
> fragmented (dbcc showcontig shows fragmentation). I
> created a job to run the following commands on the tabled
> in order to defrag the tables:
> DBCC DBREINDEX
> DBCC CHECKTABLE
> DBCC Cleantable
> followed by,
> DBCC CHECKALLOC
> DBCC UPDATEUSAGE
> Howevere the tables stay the same (fragmented). I must be
> very careful with these tables because they are tables of
> a production server that is constantly being used. If
> suffers alot of updates/inserts/delete and i cannot seem
> to find a way to defrag the tables.
> Can anyone help me?
Question about indexdefrag
Hi,
Can anyone explain me:
I have a few tables (some with and some without indexes +
some with and some without PK (ex i have a table that
doesnt have a primary key NOR indexes)) yes these tables a
fragmented (dbcc showcontig shows fragmentation). I
created a job to run the following commands on the tabled
in order to defrag the tables:
DBCC DBREINDEX
DBCC CHECKTABLE
DBCC Cleantable
followed by,
DBCC CHECKALLOC
DBCC UPDATEUSAGE
Howevere the tables stay the same (fragmented). I must be
very careful with these tables because they are tables of
a production server that is constantly being used. If
suffers alot of updates/inserts/delete and i cannot seem
to find a way to defrag the tables.
Can anyone help me?You cannot defrag datapages which aren't part of an index using DBREINDEX nor INDEXDEFRAG. You can
create a clustered index, and possibly drop it. Or export and import the data.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Claudia" <anonymous@.discussions.microsoft.com> wrote in message
news:530901c3e4c6$724a10e0$a601280a@.phx.gbl...
> Hi,
> Can anyone explain me:
> I have a few tables (some with and some without indexes +
> some with and some without PK (ex i have a table that
> doesnt have a primary key NOR indexes)) yes these tables a
> fragmented (dbcc showcontig shows fragmentation). I
> created a job to run the following commands on the tabled
> in order to defrag the tables:
> DBCC DBREINDEX
> DBCC CHECKTABLE
> DBCC Cleantable
> followed by,
> DBCC CHECKALLOC
> DBCC UPDATEUSAGE
> Howevere the tables stay the same (fragmented). I must be
> very careful with these tables because they are tables of
> a production server that is constantly being used. If
> suffers alot of updates/inserts/delete and i cannot seem
> to find a way to defrag the tables.
> Can anyone help me?|||What kind of "impact" would i have if i create/drop
indexes to defrag the tables... will this cause a big
burden on my server... i know it depends on the size of
the table and amount of data but in a "general point of
view"...... is that really the ONLY possible choice?
>--Original Message--
>You cannot defrag datapages which aren't part of an index
using DBREINDEX nor INDEXDEFRAG. You can
>create a clustered index, and possibly drop it. Or export
and import the data.
>--
>Tibor Karaszi, SQL Server MVP
>Archive at: http://groups.google.com/groups?
oi=djq&as_ugroup=microsoft.public.sqlserver
>
>"Claudia" <anonymous@.discussions.microsoft.com> wrote in
message
>news:530901c3e4c6$724a10e0$a601280a@.phx.gbl...
>> Hi,
>> Can anyone explain me:
>> I have a few tables (some with and some without indexes
+
>> some with and some without PK (ex i have a table that
>> doesnt have a primary key NOR indexes)) yes these
tables a
>> fragmented (dbcc showcontig shows fragmentation). I
>> created a job to run the following commands on the
tabled
>> in order to defrag the tables:
>> DBCC DBREINDEX
>> DBCC CHECKTABLE
>> DBCC Cleantable
>> followed by,
>> DBCC CHECKALLOC
>> DBCC UPDATEUSAGE
>> Howevere the tables stay the same (fragmented). I must
be
>> very careful with these tables because they are tables
of
>> a production server that is constantly being used. If
>> suffers alot of updates/inserts/delete and i cannot seem
>> to find a way to defrag the tables.
>> Can anyone help me?
>
>.
>|||BOL documents what locks are acquired when you create a clustered index (an exclusive lock), and
naturally, SQL Server has to do the sort and all the I/O etc. Also, all non-clustered indexes are
re-created when you create or drop a clustered index. So, you really have to test this if it is
feasible in your environment. Also, test that against the export/import strategy.
As I mentioned, INDEXDEFRAG or DBREINDEX will not shuffle or re-claim storage in any way for pages
which aren't part of an index. I do not right now see any other way to do this but the clustered
index or export/import options.
This behavior is for some a part of the reason to have a clustered index on the table in the first
place.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Claudia" <anonymous@.discussions.microsoft.com> wrote in message
news:48eb01c3e4ca$459c1710$a301280a@.phx.gbl...
> What kind of "impact" would i have if i create/drop
> indexes to defrag the tables... will this cause a big
> burden on my server... i know it depends on the size of
> the table and amount of data but in a "general point of
> view"...... is that really the ONLY possible choice?
> >--Original Message--
> >You cannot defrag datapages which aren't part of an index
> using DBREINDEX nor INDEXDEFRAG. You can
> >create a clustered index, and possibly drop it. Or export
> and import the data.
> >
> >--
> >Tibor Karaszi, SQL Server MVP
> >Archive at: http://groups.google.com/groups?
> oi=djq&as_ugroup=microsoft.public.sqlserver
> >
> >
> >"Claudia" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:530901c3e4c6$724a10e0$a601280a@.phx.gbl...
> >> Hi,
> >>
> >> Can anyone explain me:
> >>
> >> I have a few tables (some with and some without indexes
> +
> >> some with and some without PK (ex i have a table that
> >> doesnt have a primary key NOR indexes)) yes these
> tables a
> >> fragmented (dbcc showcontig shows fragmentation). I
> >> created a job to run the following commands on the
> tabled
> >> in order to defrag the tables:
> >>
> >> DBCC DBREINDEX
> >> DBCC CHECKTABLE
> >> DBCC Cleantable
> >>
> >> followed by,
> >>
> >> DBCC CHECKALLOC
> >> DBCC UPDATEUSAGE
> >>
> >> Howevere the tables stay the same (fragmented). I must
> be
> >> very careful with these tables because they are tables
> of
> >> a production server that is constantly being used. If
> >> suffers alot of updates/inserts/delete and i cannot seem
> >> to find a way to defrag the tables.
> >>
> >> Can anyone help me?
> >
> >
> >.
> >|||Furthe to what Tibor has already said, you should read the whitepaper at
http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/sql/maintain/optimize/ss2kidbp.asp
I'm assuming you're running SQL Server 2000. Can you explain why you run the
the sequence of commands you give below?
Regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Claudia" <anonymous@.discussions.microsoft.com> wrote in message
news:530901c3e4c6$724a10e0$a601280a@.phx.gbl...
> Hi,
> Can anyone explain me:
> I have a few tables (some with and some without indexes +
> some with and some without PK (ex i have a table that
> doesnt have a primary key NOR indexes)) yes these tables a
> fragmented (dbcc showcontig shows fragmentation). I
> created a job to run the following commands on the tabled
> in order to defrag the tables:
> DBCC DBREINDEX
> DBCC CHECKTABLE
> DBCC Cleantable
> followed by,
> DBCC CHECKALLOC
> DBCC UPDATEUSAGE
> Howevere the tables stay the same (fragmented). I must be
> very careful with these tables because they are tables of
> a production server that is constantly being used. If
> suffers alot of updates/inserts/delete and i cannot seem
> to find a way to defrag the tables.
> Can anyone help me?
Can anyone explain me:
I have a few tables (some with and some without indexes +
some with and some without PK (ex i have a table that
doesnt have a primary key NOR indexes)) yes these tables a
fragmented (dbcc showcontig shows fragmentation). I
created a job to run the following commands on the tabled
in order to defrag the tables:
DBCC DBREINDEX
DBCC CHECKTABLE
DBCC Cleantable
followed by,
DBCC CHECKALLOC
DBCC UPDATEUSAGE
Howevere the tables stay the same (fragmented). I must be
very careful with these tables because they are tables of
a production server that is constantly being used. If
suffers alot of updates/inserts/delete and i cannot seem
to find a way to defrag the tables.
Can anyone help me?You cannot defrag datapages which aren't part of an index using DBREINDEX nor INDEXDEFRAG. You can
create a clustered index, and possibly drop it. Or export and import the data.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Claudia" <anonymous@.discussions.microsoft.com> wrote in message
news:530901c3e4c6$724a10e0$a601280a@.phx.gbl...
> Hi,
> Can anyone explain me:
> I have a few tables (some with and some without indexes +
> some with and some without PK (ex i have a table that
> doesnt have a primary key NOR indexes)) yes these tables a
> fragmented (dbcc showcontig shows fragmentation). I
> created a job to run the following commands on the tabled
> in order to defrag the tables:
> DBCC DBREINDEX
> DBCC CHECKTABLE
> DBCC Cleantable
> followed by,
> DBCC CHECKALLOC
> DBCC UPDATEUSAGE
> Howevere the tables stay the same (fragmented). I must be
> very careful with these tables because they are tables of
> a production server that is constantly being used. If
> suffers alot of updates/inserts/delete and i cannot seem
> to find a way to defrag the tables.
> Can anyone help me?|||What kind of "impact" would i have if i create/drop
indexes to defrag the tables... will this cause a big
burden on my server... i know it depends on the size of
the table and amount of data but in a "general point of
view"...... is that really the ONLY possible choice?
>--Original Message--
>You cannot defrag datapages which aren't part of an index
using DBREINDEX nor INDEXDEFRAG. You can
>create a clustered index, and possibly drop it. Or export
and import the data.
>--
>Tibor Karaszi, SQL Server MVP
>Archive at: http://groups.google.com/groups?
oi=djq&as_ugroup=microsoft.public.sqlserver
>
>"Claudia" <anonymous@.discussions.microsoft.com> wrote in
message
>news:530901c3e4c6$724a10e0$a601280a@.phx.gbl...
>> Hi,
>> Can anyone explain me:
>> I have a few tables (some with and some without indexes
+
>> some with and some without PK (ex i have a table that
>> doesnt have a primary key NOR indexes)) yes these
tables a
>> fragmented (dbcc showcontig shows fragmentation). I
>> created a job to run the following commands on the
tabled
>> in order to defrag the tables:
>> DBCC DBREINDEX
>> DBCC CHECKTABLE
>> DBCC Cleantable
>> followed by,
>> DBCC CHECKALLOC
>> DBCC UPDATEUSAGE
>> Howevere the tables stay the same (fragmented). I must
be
>> very careful with these tables because they are tables
of
>> a production server that is constantly being used. If
>> suffers alot of updates/inserts/delete and i cannot seem
>> to find a way to defrag the tables.
>> Can anyone help me?
>
>.
>|||BOL documents what locks are acquired when you create a clustered index (an exclusive lock), and
naturally, SQL Server has to do the sort and all the I/O etc. Also, all non-clustered indexes are
re-created when you create or drop a clustered index. So, you really have to test this if it is
feasible in your environment. Also, test that against the export/import strategy.
As I mentioned, INDEXDEFRAG or DBREINDEX will not shuffle or re-claim storage in any way for pages
which aren't part of an index. I do not right now see any other way to do this but the clustered
index or export/import options.
This behavior is for some a part of the reason to have a clustered index on the table in the first
place.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Claudia" <anonymous@.discussions.microsoft.com> wrote in message
news:48eb01c3e4ca$459c1710$a301280a@.phx.gbl...
> What kind of "impact" would i have if i create/drop
> indexes to defrag the tables... will this cause a big
> burden on my server... i know it depends on the size of
> the table and amount of data but in a "general point of
> view"...... is that really the ONLY possible choice?
> >--Original Message--
> >You cannot defrag datapages which aren't part of an index
> using DBREINDEX nor INDEXDEFRAG. You can
> >create a clustered index, and possibly drop it. Or export
> and import the data.
> >
> >--
> >Tibor Karaszi, SQL Server MVP
> >Archive at: http://groups.google.com/groups?
> oi=djq&as_ugroup=microsoft.public.sqlserver
> >
> >
> >"Claudia" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:530901c3e4c6$724a10e0$a601280a@.phx.gbl...
> >> Hi,
> >>
> >> Can anyone explain me:
> >>
> >> I have a few tables (some with and some without indexes
> +
> >> some with and some without PK (ex i have a table that
> >> doesnt have a primary key NOR indexes)) yes these
> tables a
> >> fragmented (dbcc showcontig shows fragmentation). I
> >> created a job to run the following commands on the
> tabled
> >> in order to defrag the tables:
> >>
> >> DBCC DBREINDEX
> >> DBCC CHECKTABLE
> >> DBCC Cleantable
> >>
> >> followed by,
> >>
> >> DBCC CHECKALLOC
> >> DBCC UPDATEUSAGE
> >>
> >> Howevere the tables stay the same (fragmented). I must
> be
> >> very careful with these tables because they are tables
> of
> >> a production server that is constantly being used. If
> >> suffers alot of updates/inserts/delete and i cannot seem
> >> to find a way to defrag the tables.
> >>
> >> Can anyone help me?
> >
> >
> >.
> >|||Furthe to what Tibor has already said, you should read the whitepaper at
http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/sql/maintain/optimize/ss2kidbp.asp
I'm assuming you're running SQL Server 2000. Can you explain why you run the
the sequence of commands you give below?
Regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Claudia" <anonymous@.discussions.microsoft.com> wrote in message
news:530901c3e4c6$724a10e0$a601280a@.phx.gbl...
> Hi,
> Can anyone explain me:
> I have a few tables (some with and some without indexes +
> some with and some without PK (ex i have a table that
> doesnt have a primary key NOR indexes)) yes these tables a
> fragmented (dbcc showcontig shows fragmentation). I
> created a job to run the following commands on the tabled
> in order to defrag the tables:
> DBCC DBREINDEX
> DBCC CHECKTABLE
> DBCC Cleantable
> followed by,
> DBCC CHECKALLOC
> DBCC UPDATEUSAGE
> Howevere the tables stay the same (fragmented). I must be
> very careful with these tables because they are tables of
> a production server that is constantly being used. If
> suffers alot of updates/inserts/delete and i cannot seem
> to find a way to defrag the tables.
> Can anyone help me?
Tuesday, March 20, 2012
Question about GROUP BY and Views
Hi All (Again)
Another quick question...
I have created a view (1), which extracts info from about 11 tables... this part works fine
The next thing I do is using a Group By, I break down the View (1) that I have created.
For the Group By, I have three different selections, and it all depends on which option the user selects, I will run a certain Group By.
At the moment I have other Views(2,3,4) which references the first view(1) and does the Group By. I have done it this way because I kinder don't know any other way to do this.
I know this is not the best way to do this type of SQL, but it works. SO!!!
I have a couple of questions about this way of doing the statements in this manner:
1. How fast is this way of doing this type of SQL? Referenceing views from views.
2. Is there a faster way?
3. Is there another way I could achieve the same result but with another method? Maybe with using less views...
Thanks in Advance...1. How fast is this way of doing this type of SQL? Referenceing views from views.
it is fast
2. Is there a faster way?
not if what you want is a view of a view
3. Is there another way I could achieve the same result but with another method? Maybe with using less views...
you could combine a view of a view into one view
Another quick question...
I have created a view (1), which extracts info from about 11 tables... this part works fine
The next thing I do is using a Group By, I break down the View (1) that I have created.
For the Group By, I have three different selections, and it all depends on which option the user selects, I will run a certain Group By.
At the moment I have other Views(2,3,4) which references the first view(1) and does the Group By. I have done it this way because I kinder don't know any other way to do this.
I know this is not the best way to do this type of SQL, but it works. SO!!!
I have a couple of questions about this way of doing the statements in this manner:
1. How fast is this way of doing this type of SQL? Referenceing views from views.
2. Is there a faster way?
3. Is there another way I could achieve the same result but with another method? Maybe with using less views...
Thanks in Advance...1. How fast is this way of doing this type of SQL? Referenceing views from views.
it is fast
2. Is there a faster way?
not if what you want is a view of a view
3. Is there another way I could achieve the same result but with another method? Maybe with using less views...
you could combine a view of a view into one view
Question about getting data from multiple tables
I have a report that receives a Soc. Sec. No. parameter. The first table I
need data from is keyed on SSN. This table also has Id. column.
I need to use the value in the ID column to retreive data from a different
data table for another part of the report.
From a design point of view how should I approach this.
--
DonThis will most likely work, depending on the nature of your report:
Simply join your tables in your source query (data tab). You can group your
layout based on data in the first table. Or you can base different parts of
the report on different source queries, but make both queries filtered on
the same data.
--
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"Don" <Don@.discussions.microsoft.com> wrote in message
news:A2E283F5-EFDA-487A-B03E-D5469582871A@.microsoft.com...
>I have a report that receives a Soc. Sec. No. parameter. The first table I
> need data from is keyed on SSN. This table also has Id. column.
> I need to use the value in the ID column to retreive data from a different
> data table for another part of the report.
> From a design point of view how should I approach this.
> --
> Don
need data from is keyed on SSN. This table also has Id. column.
I need to use the value in the ID column to retreive data from a different
data table for another part of the report.
From a design point of view how should I approach this.
--
DonThis will most likely work, depending on the nature of your report:
Simply join your tables in your source query (data tab). You can group your
layout based on data in the first table. Or you can base different parts of
the report on different source queries, but make both queries filtered on
the same data.
--
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"Don" <Don@.discussions.microsoft.com> wrote in message
news:A2E283F5-EFDA-487A-B03E-D5469582871A@.microsoft.com...
>I have a report that receives a Soc. Sec. No. parameter. The first table I
> need data from is keyed on SSN. This table also has Id. column.
> I need to use the value in the ID column to retreive data from a different
> data table for another part of the report.
> From a design point of view how should I approach this.
> --
> Don
Monday, March 12, 2012
question about designing tables
Hi,
I want to define a table that has "order details" about one order where I can store a variable length list of prodcuts ordered in one order:
[prodID][qty][price]
That is to attch a collection of products to one order entry.
Thank you,
shlomi711Then you should create a table that looks like this:
(RowID Integer Identity) - Not really necessary, but I like to have unique row number
OrderID Integer - The order number (to which the details are connected)
prodID Integer - The product ID
qty money - the amount of products
price money - the price of the products.
Regards
Fredrik
question about designing big tables...
sql2k.
i'm preparing a sql table with 125 fields (columns) to import a huge
text file. i have calculated the max length needed for each of the
field.
but when i issued the create the table statement, i've got the
following message:
Warning: The table 'loc' has been created but its maximum row size
(64933) exceeds the maximum number of bytes per row (8060). INSERT or
UPDATE of a row in this table will fail if the resulting row length
exceeds 8060 bytes.
what can i do to work around this? suggestions are appreciated. thank
youA few options:
1 - Redesign the table. Are you sure all those columns are really attributes
of the entity? Can this table be changed to a relational design?
2- Create a 1:1 with another table. Move some of the columns to another
table that links with the first using a FK (The FK is also the PK in the
table)
3- Change your longer character columns from char/nchar/varchar/nvarchar to
text/ntext.
David Gugick
Imceda Software
www.imceda.com
"=== Steve L ===" <steve.lin@.powells.com> wrote in message
news:1112303316.246506.173500@.g14g2000cwa.googlegroups.com...
> sql2k.
> i'm preparing a sql table with 125 fields (columns) to import a huge
> text file. i have calculated the max length needed for each of the
> field.
> but when i issued the create the table statement, i've got the
> following message:
>
> Warning: The table 'loc' has been created but its maximum row size
> (64933) exceeds the maximum number of bytes per row (8060). INSERT or
> UPDATE of a row in this table will fail if the resulting row length
> exceeds 8060 bytes.
> what can i do to work around this? suggestions are appreciated. thank
> you
>|||If in fact even one row actual contains morethan 8040 bytes, then You'll nee
d
to split the data up into two or more individual tables. How that should be
done (what type of tables, how they relate to one another, what data should
go into each one...) depends to a great degree on exactly what this data
is... It's hard to imagine a single data abstraction containing 64k+ data i
n
a single row... I suspect that this data is very far from normalized... If
that is the case, you'd be better off doing some data normalization before
you import it...
If you want some ideas, please post more about the structure of the data ...
"=== Steve L ===" wrote:
> sql2k.
> i'm preparing a sql table with 125 fields (columns) to import a huge
> text file. i have calculated the max length needed for each of the
> field.
> but when i issued the create the table statement, i've got the
> following message:
>
> Warning: The table 'loc' has been created but its maximum row size
> (64933) exceeds the maximum number of bytes per row (8060). INSERT or
> UPDATE of a row in this table will fail if the resulting row length
> exceeds 8060 bytes.
> what can i do to work around this? suggestions are appreciated. thank
> you
>|||ok, guys, you asked for it!
the data source is libary of congress data.
i'll post the data strucutre here (althought that's not the source data
is constructed, the source data is all in text format so split the data
will be difficult)
i'm aslo posting a few records here (i parsed it to record number,
column name, and data in the column. can someone also tell me how to
transpose or crosss tab a query to populate the table structure? i know
this is not a easy question but any suggestions would be appreciated.
the table strucutre is like
CREATE TABLE [loc] (
[Affiliation] [varchar] (69) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[Agency responsible for reproduction] [varchar] (180) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Arranged statement for music] [varchar] (35) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Author] [varchar] (5174) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Author notes] [varchar] (92) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Award] [varchar] (104) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Bibliography note] [varchar] (1361) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Biography] [varchar] (33) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Biography/Autobiography/Memoir] [varchar] (10) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Born] [varchar] (63) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Bulk dates] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[Canceled/invalid ISBN] [varchar] (92) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Century] [varchar] (106) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Character name] [varchar] (281) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Conference publication] [varchar] (3) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Corporate or govt. name] [varchar] (326) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Creation/production credits note] [varchar] (1032) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Date of a work] [varchar] (56) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Date of distribution/release/issue and production/recordingsession
when different] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[Date of manufacture] [varchar] (80) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Date of meeting or treaty signing] [varchar] (95) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Date of reproduction] [varchar] (322) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Date of treaty signing] [varchar] (39) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Date of work] [varchar] (51) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Dates associated with a name] [varchar] (14) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Dates of publication and/or sequential designation of issues
reproduced] [varchar] (407) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Dates unknown] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Description] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[Detailed date] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Dissertation note] [varchar] (450) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Edition statement] [varchar] (782) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Expansion of summary note] [varchar] (1054) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Festschrift] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[Form] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Form of item] [varchar] (11) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Form subheading] [varchar] (244) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Formatted contents] [varchar] (7826) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Full LCCN] [varchar] (41) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Fuller form of name] [varchar] (55) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[General note] [varchar] (3781) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Genre] [varchar] (1198) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Geographic area] [varchar] (68) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Government publication] [varchar] (46) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Illustrations] [varchar] (26) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Inclusive dates] [varchar] (104) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Inclusive dates of collection] [varchar] (9) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Institution to which field applies] [varchar] (137) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[International standard serial number] [varchar] (167) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[ISBN] [varchar] (152) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ISSN] [varchar] (146) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Key for music] [varchar] (63) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Language] [varchar] (58) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Language code of accompanying material other than librettos]
[varchar] (62) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Language code of summary or abstract/overprinted title or subtitle]
[varchar] (92) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Language code of sung or spoken text] [varchar] (134) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Language of a work] [varchar] (135) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Language of librettos] [varchar] (134) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Language of table of contents] [varchar] (51) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[LCCN] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Library of Congress call number] [varchar] (117) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Literary form] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Location of meeting] [varchar] (117) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Location of unit] [varchar] (97) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Manufacturer] [varchar] (176) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Materials specified] [varchar] (65) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Medium] [varchar] (79) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Medium of performance for music] [varchar] (77) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Miscellaneous information] [varchar] (1488) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Multiple dates] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Music. Appl. au.] [varchar] (207) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Name of part/section of a work] [varchar] (619) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Name of part/section of work] [varchar] (259) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Nature of record] [varchar] (38) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[No dates given] [varchar] (29) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Non-geographic setting] [varchar] (1514) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Note about reproduction] [varchar] (860) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Number of part/section of a work] [varchar] (437) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Number of part/section of work] [varchar] (69) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Number of part/section/meeting] [varchar] (103) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Number of references] [varchar] (17) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Numeration] [varchar] (151) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[Original language] [varchar] (105) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Participate or performer note] [varchar] (1391) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Physical] [varchar] (8000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[Physical description of reproduction] [varchar] (143) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Place of manufacture] [varchar] (116) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Place of reproduction] [varchar] (117) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Plate or publisher's number for music] [varchar] (48) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Primary Author] [varchar] (1215) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Pub. and (c) dates] [varchar] (9) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Publication date] [varchar] (189) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Publication date and copyright date] [varchar] (9) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Published in] [varchar] (324) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Publisher] [varchar] (508) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[Publisher number] [varchar] (236) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Questionable date] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Range of years of bulk of collection] [varchar] (9) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Relator code] [varchar] (54) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Relator term] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Remainder of edition statement] [varchar] (1252) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Reprint & original date] [varchar] (9) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Serial item ceased publication] [varchar] (9) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Serial status unknown] [varchar] (9) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Series] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Series statement] [varchar] (831) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Series statement of reproduction] [varchar] (1062) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Source] [varchar] (340) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Statement of responsibility] [varchar] (721) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Sub-genre] [varchar] (87) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Subject] [varchar] (756) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Subordinate unit] [varchar] (364) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Summary note] [varchar] (8000) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Target audience] [varchar] (26) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Title] [varchar] (1827) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Title of a work] [varchar] (440) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Titles and other words associated with a name] [varchar] (36) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Topic] [varchar] (14) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Type of reproduction] [varchar] (339) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Uncontrolled related/analytical title] [varchar] (603) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Uniform title] [varchar] (441) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Version] [varchar] (78) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Volume] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Volume number/sequential designation] [varchar] (404) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[With index] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Year] [varchar] (358) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
and the sample data from the data source is in another table in the
format: (record number, column name, and data in the column)
1 Primary Author Zapf, Hermann
1 Title ABC-XYZapf :fifty years in alphabet design : professional and
personal contributions selected for Hermann Zapf
1 Author edited by John Dreyfus and Knut Erichson
1 Author Zapf, Hermann, Dreyfus, John, Erichson, Knut,
1 LCCN 90229151
1 Full LCCN a90229151
1 Subject Zapf, Hermann.
1 Subject Type and type-founding -- History -- 20th century.
1 Subject Calligraphy -- History -- 20th century.
1 Subject Alphabets.
1 Year 1989
1 Published in England
1 Illustrations Yes
1 Publisher Wynkyn de Worde Society ;Bund Deutscher Buchkeunstler
;Distribution by Kelly/Winterton Press,
1 Physical 251 p. :ill. (some col.), facsims. (some col.) ;28 cm.
1 Genre qr02 input 12-19-90; sent to SpecMat 12-19-90; vj05 12-24-90;
SCD notified 1/15/91; fd11 01-22-91; fp04 03-11-91
1 ISBN 3922715354 (Bund Deutscher Buchkeunstler)
1 Language of a work English, German
1 Published in London :
1 Published in Offenbach :
1 Published in New York :
1 Publication date c1989
1 Place of manufacture (Frankfurt :
1 Manufacturer Oehms Druck GmbH)
1 General note Articles in English and German; pref. and notes in
English.
1 General note Source: Purchase, July 27, 1990.
1 Institution to which field applies DLC
1 Uncontrolled related/analytical title ABCXYZapf.
1 Award rarebk/rbc
2 Primary Author Finley, Eric J
2 Title The old U.S. 80 highway traveler's guide (Phoenix-San Diego)
2 Author by Eric J. Finley ; photos and maps by Eric J. Finley ;
illustrations by Kevin Finley ; foreword by Jerry Jacka & Lois Essary
Jacka
2 LCCN 97222356
2 Full LCCN a97222356
2 Subject Arizona -- Guidebooks.
2 Subject California -- Guidebooks.
2 Subject Automobile travel -- Arizona -- Guidebooks.
2 Subject Automobile travel -- California -- Guidebooks.
2 Subject Interstate 80 -- Guidebooks.
2 Year 1997
2 Published in Arizona
2 Illustrations Maps
2 With index yes
2 Publisher Narrow Road Communications ;Inkling Communications Design,
2 Physical xx, 130 p. :ill., maps ;32 cm.
2 Genre jk27/jk15 (desc); (F809.3); lk03 to sl 02-04-98; lk18 2-23-98
2 ISBN 0965235807
2 Geographic area Arizona
2 Geographic area California
2 Published in Phoenix, AZ :
2 Published in Mesa, AZ :
2 Publication date c1997.
2 Bibliography note Includes bibliographical references (p. 115-117)
and index.
2 Character name **LC HAS REQ'D # OF SHELF COPIES**
3 Primary Author Mundy, Talbot
3 Title Old Ugly-face,
3 Author by Talbot Mundy
3 LCCN 40004503
3 Full LCCN a40004503
3 Year 1940
3 Published in New York (State)
3 Publisher D. Appleton-Century Company, incorporated,
3 Physical 5 p. l., 3-544 p.21 cm.
3 Born 1879-1940.
3 Published in New York,
3 Publication date 1940.
3 General note Illustrated lining-papers.|||Wow, and the columns are even in alphabetical order. Nice!
I agree with the others. Do something to normalize this data a little bit
better before trying to deal with it in the database.
Is every single row going to have an "Arranged statement for music"? Is
"Author" really ever going to require 5174 bytes? Is there always going to
be 1.3 KB worth of "Bibliography note"? I didn't even get through B and I'm
already having nightmares.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"=== Steve L ===" <steve.lin@.powells.com> wrote in message
news:1112305705.741957.216250@.l41g2000cwc.googlegroups.com...
> ok, guys, you asked for it!
> the data source is libary of congress data.
> i'll post the data strucutre here (althought that's not the source data
> is constructed, the source data is all in text format so split the data
> will be difficult)
> i'm aslo posting a few records here (i parsed it to record number,
> column name, and data in the column. can someone also tell me how to
> transpose or crosss tab a query to populate the table structure? i know
> this is not a easy question but any suggestions would be appreciated.
> the table strucutre is like
> CREATE TABLE [loc] (
> [Affiliation] [varchar] (69) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ,
> [Agency responsible for reproduction] [varchar] (180) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Arranged statement for music] [varchar] (35) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Author] [varchar] (5174) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Author notes] [varchar] (92) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [Award] [varchar] (104) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Bibliography note] [varchar] (1361) COLLATE|||On Thu, 31 Mar 2005 16:54:21 -0500, Aaron [SQL Server MVP] wrote:
>Wow, and the columns are even in alphabetical order. Nice!
>I agree with the others. Do something to normalize this data a little bit
>better before trying to deal with it in the database.
>Is every single row going to have an "Arranged statement for music"? Is
>"Author" really ever going to require 5174 bytes? Is there always going to
>be 1.3 KB worth of "Bibliography note"? I didn't even get through B and I'
m
>already having nightmares.
Hi Aaron,
Be glad you stopped reading at B - I skimmed over the whole list, and I
am now haunted by the vision of varchar(322) dates, varchar(152) ISBN,
varchar(17) numbers, and (my favorite) varchar(358) years.
(shudder)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Whew!
Just as I suspected... Steve, what you should seriously consider doing
is investing some time in normalizing the data structure... For Example, You
don't need Autor Info in every record, Create an Authors table,
(coincidently SQL Server Ships with a "Pubs" Database that might give you
some ideas) and only put the key to the Author record in the main loc table
,
voila ! That does away with 8-10k
etc. etc.
Until you do this you will not easily resolve this issue...
"Hugo Kornelis" wrote:
> On Thu, 31 Mar 2005 16:54:21 -0500, Aaron [SQL Server MVP] wrote:
>
> Hi Aaron,
> Be glad you stopped reading at B - I skimmed over the whole list, and I
> am now haunted by the vision of varchar(322) dates, varchar(152) ISBN,
> varchar(17) numbers, and (my favorite) varchar(358) years.
> (shudder)
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>|||That is a work of art!!!!
I'd hate to be a developer writing reports against this beast.
SELECT
[Date of distribution/release/issue and
production/recordingsession when different]
,[Dates of publication and/or sequential designation of issues
reproduced]
,[Language code of accompanying material other than librettos]
,[Language code of summary or abstract/overprinted title or subtitle]
,[Series statement of reproduction]
,[Titles and other words associated with a name]
FROM
loc
WHERE
ISDATE(SUBSTRING([Dates of publication and/or sequential
designation of issues reproduced], 1, 8...
That's alot of typing!!!
You need to normalize that before anyone gets hurt!
Simon Worth
=== Steve L === wrote:
> ok, guys, you asked for it!
> the data source is libary of congress data.
> i'll post the data strucutre here (althought that's not the source data
> is constructed, the source data is all in text format so split the data
> will be difficult)
> i'm aslo posting a few records here (i parsed it to record number,
> column name, and data in the column. can someone also tell me how to
> transpose or crosss tab a query to populate the table structure? i know
> this is not a easy question but any suggestions would be appreciated.
> the table strucutre is like
> CREATE TABLE [loc] (
> [Affiliation] [varchar] (69) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ,
> [Agency responsible for reproduction] [varchar] (180) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Arranged statement for music] [varchar] (35) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Author] [varchar] (5174) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Author notes] [varchar] (92) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [Award] [varchar] (104) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Bibliography note] [varchar] (1361) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Biography] [varchar] (33) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Biography/Autobiography/Memoir] [varchar] (10) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Born] [varchar] (63) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Bulk dates] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ,
> [Canceled/invalid ISBN] [varchar] (92) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Century] [varchar] (106) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Character name] [varchar] (281) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [Conference publication] [varchar] (3) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Corporate or govt. name] [varchar] (326) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Creation/production credits note] [varchar] (1032) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Date of a work] [varchar] (56) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [Date of distribution/release/issue and production/recordingsession
> when different] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ,
> [Date of manufacture] [varchar] (80) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Date of meeting or treaty signing] [varchar] (95) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Date of reproduction] [varchar] (322) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Date of treaty signing] [varchar] (39) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Date of work] [varchar] (51) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [Dates associated with a name] [varchar] (14) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Dates of publication and/or sequential designation of issues
> reproduced] [varchar] (407) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Dates unknown] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [Description] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ,
> [Detailed date] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [Dissertation note] [varchar] (450) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Edition statement] [varchar] (782) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Expansion of summary note] [varchar] (1054) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Festschrift] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ,
> [Form] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Form of item] [varchar] (11) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [Form subheading] [varchar] (244) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [Formatted contents] [varchar] (7826) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Full LCCN] [varchar] (41) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Fuller form of name] [varchar] (55) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [General note] [varchar] (3781) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [Genre] [varchar] (1198) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Geographic area] [varchar] (68) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [Government publication] [varchar] (46) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Illustrations] [varchar] (26) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [Inclusive dates] [varchar] (104) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [Inclusive dates of collection] [varchar] (9) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Institution to which field applies] [varchar] (137) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [International standard serial number] [varchar] (167) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [ISBN] [varchar] (152) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [ISSN] [varchar] (146) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Key for music] [varchar] (63) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [Language] [varchar] (58) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Language code of accompanying material other than librettos]
> [varchar] (62) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Language code of summary or abstract/overprinted title or subtitle]
> [varchar] (92) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Language code of sung or spoken text] [varchar] (134) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Language of a work] [varchar] (135) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Language of librettos] [varchar] (134) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Language of table of contents] [varchar] (51) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [LCCN] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Library of Congress call number] [varchar] (117) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Literary form] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [Location of meeting] [varchar] (117) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Location of unit] [varchar] (97) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [Manufacturer] [varchar] (176) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [Materials specified] [varchar] (65) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Medium] [varchar] (79) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Medium of performance for music] [varchar] (77) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Miscellaneous information] [varchar] (1488) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Multiple dates] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [Music. Appl. au.] [varchar] (207) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Name of part/section of a work] [varchar] (619) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Name of part/section of work] [varchar] (259) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Nature of record] [varchar] (38) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [No dates given] [varchar] (29) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [Non-geographic setting] [varchar] (1514) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Note about reproduction] [varchar] (860) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Number of part/section of a work] [varchar] (437) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Number of part/section of work] [varchar] (69) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Number of part/section/meeting] [varchar] (103) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Number of references] [varchar] (17) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Numeration] [varchar] (151) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ,
> [Original language] [varchar] (105) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Participate or performer note] [varchar] (1391) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Physical] [varchar] (8000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ,
> [Physical description of reproduction] [varchar] (143) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Place of manufacture] [varchar] (116) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Place of reproduction] [varchar] (117) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Plate or publisher's number for music] [varchar] (48) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Primary Author] [varchar] (1215) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [Pub. and (c) dates] [varchar] (9) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Publication date] [varchar] (189) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Publication date and copyright date] [varchar] (9) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Published in] [varchar] (324) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [Publisher] [varchar] (508) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ,
> [Publisher number] [varchar] (236) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Questionable date] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [Range of years of bulk of collection] [varchar] (9) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Relator code] [varchar] (54) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [Relator term] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [Remainder of edition statement] [varchar] (1252) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Reprint & original date] [varchar] (9) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Serial item ceased publication] [varchar] (9) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Serial status unknown] [varchar] (9) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Series] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Series statement] [varchar] (831) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Series statement of reproduction] [varchar] (1062) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Source] [varchar] (340) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Statement of responsibility] [varchar] (721) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Sub-genre] [varchar] (87) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Subject] [varchar] (756) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Subordinate unit] [varchar] (364) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Summary note] [varchar] (8000) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [Target audience] [varchar] (26) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [Title] [varchar] (1827) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Title of a work] [varchar] (440) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [Titles and other words associated with a name] [varchar] (36) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Topic] [varchar] (14) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Type of reproduction] [varchar] (339) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Uncontrolled related/analytical title] [varchar] (603) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Uniform title] [varchar] (441) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [Version] [varchar] (78) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Volume] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Volume number/sequential designation] [varchar] (404) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [With index] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Year] [varchar] (358) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
>
> and the sample data from the data source is in another table in the
> format: (record number, column name, and data in the column)
> 1 Primary Author Zapf, Hermann
> 1 Title ABC-XYZapf :fifty years in alphabet design : professional and
> personal contributions selected for Hermann Zapf
> 1 Author edited by John Dreyfus and Knut Erichson
> 1 Author Zapf, Hermann, Dreyfus, John, Erichson, Knut,
> 1 LCCN 90229151
> 1 Full LCCN a90229151
> 1 Subject Zapf, Hermann.
> 1 Subject Type and type-founding -- History -- 20th century.
> 1 Subject Calligraphy -- History -- 20th century.
> 1 Subject Alphabets.
> 1 Year 1989
> 1 Published in England
> 1 Illustrations Yes
> 1 Publisher Wynkyn de Worde Society ;Bund Deutscher Buchkeunstler
> ;Distribution by Kelly/Winterton Press,
> 1 Physical 251 p. :ill. (some col.), facsims. (some col.) ;28 cm.
> 1 Genre qr02 input 12-19-90; sent to SpecMat 12-19-90; vj05 12-24-90;
> SCD notified 1/15/91; fd11 01-22-91; fp04 03-11-91
> 1 ISBN 3922715354 (Bund Deutscher Buchkeunstler)
> 1 Language of a work English, German
> 1 Published in London :
> 1 Published in Offenbach :
> 1 Published in New York :
> 1 Publication date c1989
> 1 Place of manufacture (Frankfurt :
> 1 Manufacturer Oehms Druck GmbH)
> 1 General note Articles in English and German; pref. and notes in
> English.
> 1 General note Source: Purchase, July 27, 1990.
> 1 Institution to which field applies DLC
> 1 Uncontrolled related/analytical title ABCXYZapf.
> 1 Award rarebk/rbc
> 2 Primary Author Finley, Eric J
> 2 Title The old U.S. 80 highway traveler's guide (Phoenix-San Diego)
> 2 Author by Eric J. Finley ; photos and maps by Eric J. Finley ;
> illustrations by Kevin Finley ; foreword by Jerry Jacka & Lois Essary
> Jacka
> 2 LCCN 97222356
> 2 Full LCCN a97222356
> 2 Subject Arizona -- Guidebooks.
> 2 Subject California -- Guidebooks.
> 2 Subject Automobile travel -- Arizona -- Guidebooks.
> 2 Subject Automobile travel -- California -- Guidebooks.
> 2 Subject Interstate 80 -- Guidebooks.
> 2 Year 1997
> 2 Published in Arizona
> 2 Illustrations Maps
> 2 With index yes
> 2 Publisher Narrow Road Communications ;Inkling Communications Design,
> 2 Physical xx, 130 p. :ill., maps ;32 cm.
> 2 Genre jk27/jk15 (desc); (F809.3); lk03 to sl 02-04-98; lk18 2-23-98
> 2 ISBN 0965235807
> 2 Geographic area Arizona
> 2 Geographic area California
> 2 Published in Phoenix, AZ :
> 2 Published in Mesa, AZ :
> 2 Publication date c1997.
> 2 Bibliography note Includes bibliographical references (p. 115-117)
> and index.
> 2 Character name **LC HAS REQ'D # OF SHELF COPIES**
> 3 Primary Author Mundy, Talbot
> 3 Title Old Ugly-face,
> 3 Author by Talbot Mundy
> 3 LCCN 40004503
> 3 Full LCCN a40004503
> 3 Year 1940
> 3 Published in New York (State)
> 3 Publisher D. Appleton-Century Company, incorporated,
> 3 Physical 5 p. l., 3-544 p.21 cm.
> 3 Born 1879-1940.
> 3 Published in New York,
> 3 Publication date 1940.
> 3 General note Illustrated lining-papers.
>|||hey, thank you guys. for all the good suggestions!
i'm preparing a sql table with 125 fields (columns) to import a huge
text file. i have calculated the max length needed for each of the
field.
but when i issued the create the table statement, i've got the
following message:
Warning: The table 'loc' has been created but its maximum row size
(64933) exceeds the maximum number of bytes per row (8060). INSERT or
UPDATE of a row in this table will fail if the resulting row length
exceeds 8060 bytes.
what can i do to work around this? suggestions are appreciated. thank
youA few options:
1 - Redesign the table. Are you sure all those columns are really attributes
of the entity? Can this table be changed to a relational design?
2- Create a 1:1 with another table. Move some of the columns to another
table that links with the first using a FK (The FK is also the PK in the
table)
3- Change your longer character columns from char/nchar/varchar/nvarchar to
text/ntext.
David Gugick
Imceda Software
www.imceda.com
"=== Steve L ===" <steve.lin@.powells.com> wrote in message
news:1112303316.246506.173500@.g14g2000cwa.googlegroups.com...
> sql2k.
> i'm preparing a sql table with 125 fields (columns) to import a huge
> text file. i have calculated the max length needed for each of the
> field.
> but when i issued the create the table statement, i've got the
> following message:
>
> Warning: The table 'loc' has been created but its maximum row size
> (64933) exceeds the maximum number of bytes per row (8060). INSERT or
> UPDATE of a row in this table will fail if the resulting row length
> exceeds 8060 bytes.
> what can i do to work around this? suggestions are appreciated. thank
> you
>|||If in fact even one row actual contains morethan 8040 bytes, then You'll nee
d
to split the data up into two or more individual tables. How that should be
done (what type of tables, how they relate to one another, what data should
go into each one...) depends to a great degree on exactly what this data
is... It's hard to imagine a single data abstraction containing 64k+ data i
n
a single row... I suspect that this data is very far from normalized... If
that is the case, you'd be better off doing some data normalization before
you import it...
If you want some ideas, please post more about the structure of the data ...
"=== Steve L ===" wrote:
> sql2k.
> i'm preparing a sql table with 125 fields (columns) to import a huge
> text file. i have calculated the max length needed for each of the
> field.
> but when i issued the create the table statement, i've got the
> following message:
>
> Warning: The table 'loc' has been created but its maximum row size
> (64933) exceeds the maximum number of bytes per row (8060). INSERT or
> UPDATE of a row in this table will fail if the resulting row length
> exceeds 8060 bytes.
> what can i do to work around this? suggestions are appreciated. thank
> you
>|||ok, guys, you asked for it!
the data source is libary of congress data.
i'll post the data strucutre here (althought that's not the source data
is constructed, the source data is all in text format so split the data
will be difficult)
i'm aslo posting a few records here (i parsed it to record number,
column name, and data in the column. can someone also tell me how to
transpose or crosss tab a query to populate the table structure? i know
this is not a easy question but any suggestions would be appreciated.
the table strucutre is like
CREATE TABLE [loc] (
[Affiliation] [varchar] (69) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[Agency responsible for reproduction] [varchar] (180) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Arranged statement for music] [varchar] (35) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Author] [varchar] (5174) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Author notes] [varchar] (92) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Award] [varchar] (104) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Bibliography note] [varchar] (1361) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Biography] [varchar] (33) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Biography/Autobiography/Memoir] [varchar] (10) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Born] [varchar] (63) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Bulk dates] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[Canceled/invalid ISBN] [varchar] (92) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Century] [varchar] (106) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Character name] [varchar] (281) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Conference publication] [varchar] (3) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Corporate or govt. name] [varchar] (326) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Creation/production credits note] [varchar] (1032) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Date of a work] [varchar] (56) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Date of distribution/release/issue and production/recordingsession
when different] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[Date of manufacture] [varchar] (80) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Date of meeting or treaty signing] [varchar] (95) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Date of reproduction] [varchar] (322) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Date of treaty signing] [varchar] (39) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Date of work] [varchar] (51) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Dates associated with a name] [varchar] (14) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Dates of publication and/or sequential designation of issues
reproduced] [varchar] (407) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Dates unknown] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Description] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[Detailed date] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Dissertation note] [varchar] (450) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Edition statement] [varchar] (782) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Expansion of summary note] [varchar] (1054) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Festschrift] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[Form] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Form of item] [varchar] (11) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Form subheading] [varchar] (244) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Formatted contents] [varchar] (7826) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Full LCCN] [varchar] (41) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Fuller form of name] [varchar] (55) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[General note] [varchar] (3781) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Genre] [varchar] (1198) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Geographic area] [varchar] (68) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Government publication] [varchar] (46) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Illustrations] [varchar] (26) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Inclusive dates] [varchar] (104) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Inclusive dates of collection] [varchar] (9) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Institution to which field applies] [varchar] (137) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[International standard serial number] [varchar] (167) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[ISBN] [varchar] (152) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ISSN] [varchar] (146) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Key for music] [varchar] (63) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Language] [varchar] (58) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Language code of accompanying material other than librettos]
[varchar] (62) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Language code of summary or abstract/overprinted title or subtitle]
[varchar] (92) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Language code of sung or spoken text] [varchar] (134) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Language of a work] [varchar] (135) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Language of librettos] [varchar] (134) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Language of table of contents] [varchar] (51) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[LCCN] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Library of Congress call number] [varchar] (117) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Literary form] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Location of meeting] [varchar] (117) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Location of unit] [varchar] (97) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Manufacturer] [varchar] (176) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Materials specified] [varchar] (65) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Medium] [varchar] (79) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Medium of performance for music] [varchar] (77) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Miscellaneous information] [varchar] (1488) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Multiple dates] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Music. Appl. au.] [varchar] (207) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Name of part/section of a work] [varchar] (619) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Name of part/section of work] [varchar] (259) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Nature of record] [varchar] (38) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[No dates given] [varchar] (29) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Non-geographic setting] [varchar] (1514) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Note about reproduction] [varchar] (860) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Number of part/section of a work] [varchar] (437) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Number of part/section of work] [varchar] (69) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Number of part/section/meeting] [varchar] (103) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Number of references] [varchar] (17) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Numeration] [varchar] (151) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[Original language] [varchar] (105) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Participate or performer note] [varchar] (1391) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Physical] [varchar] (8000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[Physical description of reproduction] [varchar] (143) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Place of manufacture] [varchar] (116) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Place of reproduction] [varchar] (117) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Plate or publisher's number for music] [varchar] (48) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Primary Author] [varchar] (1215) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Pub. and (c) dates] [varchar] (9) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Publication date] [varchar] (189) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Publication date and copyright date] [varchar] (9) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Published in] [varchar] (324) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Publisher] [varchar] (508) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[Publisher number] [varchar] (236) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Questionable date] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Range of years of bulk of collection] [varchar] (9) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Relator code] [varchar] (54) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Relator term] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Remainder of edition statement] [varchar] (1252) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Reprint & original date] [varchar] (9) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Serial item ceased publication] [varchar] (9) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Serial status unknown] [varchar] (9) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Series] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Series statement] [varchar] (831) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Series statement of reproduction] [varchar] (1062) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Source] [varchar] (340) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Statement of responsibility] [varchar] (721) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Sub-genre] [varchar] (87) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Subject] [varchar] (756) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Subordinate unit] [varchar] (364) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Summary note] [varchar] (8000) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Target audience] [varchar] (26) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Title] [varchar] (1827) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Title of a work] [varchar] (440) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Titles and other words associated with a name] [varchar] (36) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Topic] [varchar] (14) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Type of reproduction] [varchar] (339) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Uncontrolled related/analytical title] [varchar] (603) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Uniform title] [varchar] (441) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Version] [varchar] (78) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Volume] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Volume number/sequential designation] [varchar] (404) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[With index] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Year] [varchar] (358) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
and the sample data from the data source is in another table in the
format: (record number, column name, and data in the column)
1 Primary Author Zapf, Hermann
1 Title ABC-XYZapf :fifty years in alphabet design : professional and
personal contributions selected for Hermann Zapf
1 Author edited by John Dreyfus and Knut Erichson
1 Author Zapf, Hermann, Dreyfus, John, Erichson, Knut,
1 LCCN 90229151
1 Full LCCN a90229151
1 Subject Zapf, Hermann.
1 Subject Type and type-founding -- History -- 20th century.
1 Subject Calligraphy -- History -- 20th century.
1 Subject Alphabets.
1 Year 1989
1 Published in England
1 Illustrations Yes
1 Publisher Wynkyn de Worde Society ;Bund Deutscher Buchkeunstler
;Distribution by Kelly/Winterton Press,
1 Physical 251 p. :ill. (some col.), facsims. (some col.) ;28 cm.
1 Genre qr02 input 12-19-90; sent to SpecMat 12-19-90; vj05 12-24-90;
SCD notified 1/15/91; fd11 01-22-91; fp04 03-11-91
1 ISBN 3922715354 (Bund Deutscher Buchkeunstler)
1 Language of a work English, German
1 Published in London :
1 Published in Offenbach :
1 Published in New York :
1 Publication date c1989
1 Place of manufacture (Frankfurt :
1 Manufacturer Oehms Druck GmbH)
1 General note Articles in English and German; pref. and notes in
English.
1 General note Source: Purchase, July 27, 1990.
1 Institution to which field applies DLC
1 Uncontrolled related/analytical title ABCXYZapf.
1 Award rarebk/rbc
2 Primary Author Finley, Eric J
2 Title The old U.S. 80 highway traveler's guide (Phoenix-San Diego)
2 Author by Eric J. Finley ; photos and maps by Eric J. Finley ;
illustrations by Kevin Finley ; foreword by Jerry Jacka & Lois Essary
Jacka
2 LCCN 97222356
2 Full LCCN a97222356
2 Subject Arizona -- Guidebooks.
2 Subject California -- Guidebooks.
2 Subject Automobile travel -- Arizona -- Guidebooks.
2 Subject Automobile travel -- California -- Guidebooks.
2 Subject Interstate 80 -- Guidebooks.
2 Year 1997
2 Published in Arizona
2 Illustrations Maps
2 With index yes
2 Publisher Narrow Road Communications ;Inkling Communications Design,
2 Physical xx, 130 p. :ill., maps ;32 cm.
2 Genre jk27/jk15 (desc); (F809.3); lk03 to sl 02-04-98; lk18 2-23-98
2 ISBN 0965235807
2 Geographic area Arizona
2 Geographic area California
2 Published in Phoenix, AZ :
2 Published in Mesa, AZ :
2 Publication date c1997.
2 Bibliography note Includes bibliographical references (p. 115-117)
and index.
2 Character name **LC HAS REQ'D # OF SHELF COPIES**
3 Primary Author Mundy, Talbot
3 Title Old Ugly-face,
3 Author by Talbot Mundy
3 LCCN 40004503
3 Full LCCN a40004503
3 Year 1940
3 Published in New York (State)
3 Publisher D. Appleton-Century Company, incorporated,
3 Physical 5 p. l., 3-544 p.21 cm.
3 Born 1879-1940.
3 Published in New York,
3 Publication date 1940.
3 General note Illustrated lining-papers.|||Wow, and the columns are even in alphabetical order. Nice!
I agree with the others. Do something to normalize this data a little bit
better before trying to deal with it in the database.
Is every single row going to have an "Arranged statement for music"? Is
"Author" really ever going to require 5174 bytes? Is there always going to
be 1.3 KB worth of "Bibliography note"? I didn't even get through B and I'm
already having nightmares.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"=== Steve L ===" <steve.lin@.powells.com> wrote in message
news:1112305705.741957.216250@.l41g2000cwc.googlegroups.com...
> ok, guys, you asked for it!
> the data source is libary of congress data.
> i'll post the data strucutre here (althought that's not the source data
> is constructed, the source data is all in text format so split the data
> will be difficult)
> i'm aslo posting a few records here (i parsed it to record number,
> column name, and data in the column. can someone also tell me how to
> transpose or crosss tab a query to populate the table structure? i know
> this is not a easy question but any suggestions would be appreciated.
> the table strucutre is like
> CREATE TABLE [loc] (
> [Affiliation] [varchar] (69) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ,
> [Agency responsible for reproduction] [varchar] (180) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Arranged statement for music] [varchar] (35) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Author] [varchar] (5174) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Author notes] [varchar] (92) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [Award] [varchar] (104) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Bibliography note] [varchar] (1361) COLLATE|||On Thu, 31 Mar 2005 16:54:21 -0500, Aaron [SQL Server MVP] wrote:
>Wow, and the columns are even in alphabetical order. Nice!
>I agree with the others. Do something to normalize this data a little bit
>better before trying to deal with it in the database.
>Is every single row going to have an "Arranged statement for music"? Is
>"Author" really ever going to require 5174 bytes? Is there always going to
>be 1.3 KB worth of "Bibliography note"? I didn't even get through B and I'
m
>already having nightmares.
Hi Aaron,
Be glad you stopped reading at B - I skimmed over the whole list, and I
am now haunted by the vision of varchar(322) dates, varchar(152) ISBN,
varchar(17) numbers, and (my favorite) varchar(358) years.
(shudder)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Whew!
Just as I suspected... Steve, what you should seriously consider doing
is investing some time in normalizing the data structure... For Example, You
don't need Autor Info in every record, Create an Authors table,
(coincidently SQL Server Ships with a "Pubs" Database that might give you
some ideas) and only put the key to the Author record in the main loc table
,
voila ! That does away with 8-10k
etc. etc.
Until you do this you will not easily resolve this issue...
"Hugo Kornelis" wrote:
> On Thu, 31 Mar 2005 16:54:21 -0500, Aaron [SQL Server MVP] wrote:
>
> Hi Aaron,
> Be glad you stopped reading at B - I skimmed over the whole list, and I
> am now haunted by the vision of varchar(322) dates, varchar(152) ISBN,
> varchar(17) numbers, and (my favorite) varchar(358) years.
> (shudder)
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>|||That is a work of art!!!!
I'd hate to be a developer writing reports against this beast.
SELECT
[Date of distribution/release/issue and
production/recordingsession when different]
,[Dates of publication and/or sequential designation of issues
reproduced]
,[Language code of accompanying material other than librettos]
,[Language code of summary or abstract/overprinted title or subtitle]
,[Series statement of reproduction]
,[Titles and other words associated with a name]
FROM
loc
WHERE
ISDATE(SUBSTRING([Dates of publication and/or sequential
designation of issues reproduced], 1, 8...
That's alot of typing!!!
You need to normalize that before anyone gets hurt!
Simon Worth
=== Steve L === wrote:
> ok, guys, you asked for it!
> the data source is libary of congress data.
> i'll post the data strucutre here (althought that's not the source data
> is constructed, the source data is all in text format so split the data
> will be difficult)
> i'm aslo posting a few records here (i parsed it to record number,
> column name, and data in the column. can someone also tell me how to
> transpose or crosss tab a query to populate the table structure? i know
> this is not a easy question but any suggestions would be appreciated.
> the table strucutre is like
> CREATE TABLE [loc] (
> [Affiliation] [varchar] (69) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ,
> [Agency responsible for reproduction] [varchar] (180) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Arranged statement for music] [varchar] (35) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Author] [varchar] (5174) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Author notes] [varchar] (92) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [Award] [varchar] (104) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Bibliography note] [varchar] (1361) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Biography] [varchar] (33) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Biography/Autobiography/Memoir] [varchar] (10) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Born] [varchar] (63) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Bulk dates] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ,
> [Canceled/invalid ISBN] [varchar] (92) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Century] [varchar] (106) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Character name] [varchar] (281) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [Conference publication] [varchar] (3) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Corporate or govt. name] [varchar] (326) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Creation/production credits note] [varchar] (1032) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Date of a work] [varchar] (56) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [Date of distribution/release/issue and production/recordingsession
> when different] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ,
> [Date of manufacture] [varchar] (80) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Date of meeting or treaty signing] [varchar] (95) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Date of reproduction] [varchar] (322) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Date of treaty signing] [varchar] (39) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Date of work] [varchar] (51) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [Dates associated with a name] [varchar] (14) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Dates of publication and/or sequential designation of issues
> reproduced] [varchar] (407) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Dates unknown] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [Description] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ,
> [Detailed date] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [Dissertation note] [varchar] (450) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Edition statement] [varchar] (782) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Expansion of summary note] [varchar] (1054) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Festschrift] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ,
> [Form] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Form of item] [varchar] (11) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [Form subheading] [varchar] (244) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [Formatted contents] [varchar] (7826) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Full LCCN] [varchar] (41) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Fuller form of name] [varchar] (55) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [General note] [varchar] (3781) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [Genre] [varchar] (1198) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Geographic area] [varchar] (68) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [Government publication] [varchar] (46) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Illustrations] [varchar] (26) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [Inclusive dates] [varchar] (104) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [Inclusive dates of collection] [varchar] (9) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Institution to which field applies] [varchar] (137) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [International standard serial number] [varchar] (167) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [ISBN] [varchar] (152) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [ISSN] [varchar] (146) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Key for music] [varchar] (63) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [Language] [varchar] (58) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Language code of accompanying material other than librettos]
> [varchar] (62) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Language code of summary or abstract/overprinted title or subtitle]
> [varchar] (92) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Language code of sung or spoken text] [varchar] (134) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Language of a work] [varchar] (135) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Language of librettos] [varchar] (134) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Language of table of contents] [varchar] (51) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [LCCN] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Library of Congress call number] [varchar] (117) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Literary form] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [Location of meeting] [varchar] (117) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Location of unit] [varchar] (97) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [Manufacturer] [varchar] (176) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [Materials specified] [varchar] (65) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Medium] [varchar] (79) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Medium of performance for music] [varchar] (77) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Miscellaneous information] [varchar] (1488) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Multiple dates] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [Music. Appl. au.] [varchar] (207) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Name of part/section of a work] [varchar] (619) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Name of part/section of work] [varchar] (259) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Nature of record] [varchar] (38) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [No dates given] [varchar] (29) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [Non-geographic setting] [varchar] (1514) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Note about reproduction] [varchar] (860) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Number of part/section of a work] [varchar] (437) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Number of part/section of work] [varchar] (69) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Number of part/section/meeting] [varchar] (103) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Number of references] [varchar] (17) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Numeration] [varchar] (151) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ,
> [Original language] [varchar] (105) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Participate or performer note] [varchar] (1391) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Physical] [varchar] (8000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ,
> [Physical description of reproduction] [varchar] (143) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Place of manufacture] [varchar] (116) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Place of reproduction] [varchar] (117) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Plate or publisher's number for music] [varchar] (48) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Primary Author] [varchar] (1215) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [Pub. and (c) dates] [varchar] (9) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Publication date] [varchar] (189) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Publication date and copyright date] [varchar] (9) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Published in] [varchar] (324) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [Publisher] [varchar] (508) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ,
> [Publisher number] [varchar] (236) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Questionable date] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [Range of years of bulk of collection] [varchar] (9) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Relator code] [varchar] (54) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [Relator term] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [Remainder of edition statement] [varchar] (1252) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Reprint & original date] [varchar] (9) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Serial item ceased publication] [varchar] (9) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Serial status unknown] [varchar] (9) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Series] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Series statement] [varchar] (831) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Series statement of reproduction] [varchar] (1062) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Source] [varchar] (340) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Statement of responsibility] [varchar] (721) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Sub-genre] [varchar] (87) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Subject] [varchar] (756) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Subordinate unit] [varchar] (364) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Summary note] [varchar] (8000) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [Target audience] [varchar] (26) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [Title] [varchar] (1827) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Title of a work] [varchar] (440) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [Titles and other words associated with a name] [varchar] (36) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Topic] [varchar] (14) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Type of reproduction] [varchar] (339) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Uncontrolled related/analytical title] [varchar] (603) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Uniform title] [varchar] (441) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [Version] [varchar] (78) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Volume] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Volume number/sequential designation] [varchar] (404) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [With index] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Year] [varchar] (358) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
>
> and the sample data from the data source is in another table in the
> format: (record number, column name, and data in the column)
> 1 Primary Author Zapf, Hermann
> 1 Title ABC-XYZapf :fifty years in alphabet design : professional and
> personal contributions selected for Hermann Zapf
> 1 Author edited by John Dreyfus and Knut Erichson
> 1 Author Zapf, Hermann, Dreyfus, John, Erichson, Knut,
> 1 LCCN 90229151
> 1 Full LCCN a90229151
> 1 Subject Zapf, Hermann.
> 1 Subject Type and type-founding -- History -- 20th century.
> 1 Subject Calligraphy -- History -- 20th century.
> 1 Subject Alphabets.
> 1 Year 1989
> 1 Published in England
> 1 Illustrations Yes
> 1 Publisher Wynkyn de Worde Society ;Bund Deutscher Buchkeunstler
> ;Distribution by Kelly/Winterton Press,
> 1 Physical 251 p. :ill. (some col.), facsims. (some col.) ;28 cm.
> 1 Genre qr02 input 12-19-90; sent to SpecMat 12-19-90; vj05 12-24-90;
> SCD notified 1/15/91; fd11 01-22-91; fp04 03-11-91
> 1 ISBN 3922715354 (Bund Deutscher Buchkeunstler)
> 1 Language of a work English, German
> 1 Published in London :
> 1 Published in Offenbach :
> 1 Published in New York :
> 1 Publication date c1989
> 1 Place of manufacture (Frankfurt :
> 1 Manufacturer Oehms Druck GmbH)
> 1 General note Articles in English and German; pref. and notes in
> English.
> 1 General note Source: Purchase, July 27, 1990.
> 1 Institution to which field applies DLC
> 1 Uncontrolled related/analytical title ABCXYZapf.
> 1 Award rarebk/rbc
> 2 Primary Author Finley, Eric J
> 2 Title The old U.S. 80 highway traveler's guide (Phoenix-San Diego)
> 2 Author by Eric J. Finley ; photos and maps by Eric J. Finley ;
> illustrations by Kevin Finley ; foreword by Jerry Jacka & Lois Essary
> Jacka
> 2 LCCN 97222356
> 2 Full LCCN a97222356
> 2 Subject Arizona -- Guidebooks.
> 2 Subject California -- Guidebooks.
> 2 Subject Automobile travel -- Arizona -- Guidebooks.
> 2 Subject Automobile travel -- California -- Guidebooks.
> 2 Subject Interstate 80 -- Guidebooks.
> 2 Year 1997
> 2 Published in Arizona
> 2 Illustrations Maps
> 2 With index yes
> 2 Publisher Narrow Road Communications ;Inkling Communications Design,
> 2 Physical xx, 130 p. :ill., maps ;32 cm.
> 2 Genre jk27/jk15 (desc); (F809.3); lk03 to sl 02-04-98; lk18 2-23-98
> 2 ISBN 0965235807
> 2 Geographic area Arizona
> 2 Geographic area California
> 2 Published in Phoenix, AZ :
> 2 Published in Mesa, AZ :
> 2 Publication date c1997.
> 2 Bibliography note Includes bibliographical references (p. 115-117)
> and index.
> 2 Character name **LC HAS REQ'D # OF SHELF COPIES**
> 3 Primary Author Mundy, Talbot
> 3 Title Old Ugly-face,
> 3 Author by Talbot Mundy
> 3 LCCN 40004503
> 3 Full LCCN a40004503
> 3 Year 1940
> 3 Published in New York (State)
> 3 Publisher D. Appleton-Century Company, incorporated,
> 3 Physical 5 p. l., 3-544 p.21 cm.
> 3 Born 1879-1940.
> 3 Published in New York,
> 3 Publication date 1940.
> 3 General note Illustrated lining-papers.
>|||hey, thank you guys. for all the good suggestions!
Subscribe to:
Posts (Atom)