|
|
|
If you're using SQL2005Express and you're completely new to SQL and SQL Server then I would recommend this book[^]for your learning pleasure.
"We've all heard that a million monkeys banging on a million typewriters will eventually reproduce the entire works of Shakespeare. Now, thanks to the internet, we know this is not true." -- Professor Robert Silensky
|
|
|
|
|
You almost always want SQL Server to do the work. If you index the table appropriately and supply a suitable query, SQL Server can find a given row by touching only a small number of pages making up the index (loading them from the disk), and one page of the main table, if the columns you're requesting aren't part of the index. It only returns one row across the wire, which will reduce the time spent getting the network to transfer the row.
If you make SQL Server give you all the rows, it will touch - and briefly lock - all the rows in the table, requiring all pages of the table to be read from the disk (slow), and the network time will be much higher. It'll take much longer to get your data. Then, you'll be using more memory on the client side to store the results while you search for the row you're after.
|
|
|
|
|
Thanks for the responses everybody.
Mark
|
|
|
|
|
I have 2 tables : Client(ClientID and other informations) and ProjectClient(ProjectID,ClientID).
I must select clients and all project id's they are working at, the project id's being in one field, separated by comma.Could you help me, pls?
Thank you.
|
|
|
|
|
So you are basically just asking how to join the two tables together?
It would look like this:
SELECT * FROM Client, ProjectClient WHERE Client.ClientID = ProjectClient.ClientID
If you want all the info for just one client then you just do this (if for example the client you wanted had a clientID of 5):
SELECT * FROM Client, ProjectClient WHERE Client.ClientID = ProjectClient.ClientID AND Client.ClientID = 5
|
|
|
|
|
I don't know why you would want something like this, but it sounds like you need a function:
CREATE FUNCTION GetProjectIDs (@nClientID int)
RETURNS varchar(1000) AS
BEGIN
DECLARE @nProjectID int
DECLARE @cProjectIDs varchar(50)
SELECT @cProjectIDs = ''
DECLARE ProjectIDs CURSOR FOR
SELECT ProjectID
FROM ProjectClient
WHERE ClientID = @nClientID
ORDER BY ProjectID
OPEN ProjectIDs
FETCH NEXT FROM ProjectIDs INTO @nProjectID
WHILE @@FETCH_STATUS = 0
BEGIN
select @cProjectIDs = @cProjectIDs + case when len(@cProjectIDs) = 0 then '' else ',' end + cast( @nProjectID as varchar)
FETCH NEXT FROM ProjectIDs INTO @nProjectID
END
CLOSE ProjectIDs
DEALLOCATE ProjectIDs
RETURN(@cProjectIDs)
END
Then call it like this:
select *, dbo.GetProjectIDs(client.clientid) as ProjectIDs from client
--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
Use SQL Server to get your data. Do the formatting in the client side program. Keep both sides doing what they're best at: SQL Server is not really that good at string manipulation.
For this task I would use a DataSet and use the version of DataAdapter.Fill which fills a DataSet. I'd write a query that returned two resultsets:
CREATE PROC uspGetClientsProjects
AS
SELECT ClientID, Field1, Field2
FROM Client
SELECT ClientID, ProjectID
FROM ProjectClient If you Fill a DataSet from that, you get two DataTables in your DataSet. You can then add a DataRelation to the DataSet's Relations property to link Client and ProjectClient together. Then, to get your output, iterate over the Client table, and use the GetChildRows method of the DataRow object to get the matching ProjectClient rows.
|
|
|
|
|
Thanks for your answers.I resolved the problem using 2 cursors.
|
|
|
|
|
How can i write a select SQL statement for the DB that uses the string from CEdit1 ?
lets say database name : "Names_City" class : CNames_City
col 1 : "name"
col 2 : "city"
col 3 : "country"
CString sName , sCity , sCountry;
CEdit1 : "m_name" sName;
CEdit2 : "m_city" sCity;
CEdit3 : "m_country" sCountry;
1) i have onChange for "m_name .When i write some txt in m_name i need to search in "Names_City > name = and if it matches i need the city in m_city and the country in m_country "
2)when i write in m_city and then in m_name i need to search in "name","city" and if m_name == "name" and m_city == "city" i need "country" in m_country.
* Remember i have ~260.000 Records ;
thank you in advance !
*please show me an example !
Bravoone
|
|
|
|
|
I am looking into starting a transactional replication process. The company I work for has aquired a property outside this country. I have to figure out how to make both sites function on their independent databases and have real time access to each other.
I figured transactional would be the best option. what are some of the factors to consider? Our Corporate system functions with alot of auto generated id numbers and the database is dynamic.
If i create a record here and i get id no 5, lets assume someone in the different country tries to do the same thing would he also get id no 5? if the databases have not syncronized since.
Please excuse my ignorance for I am trying to learn how this functions.
Thank you
|
|
|
|
|
|
How can i export data from sql/access/oracle to excel file and vice versa
in vc++
|
|
|
|
|
Hello,
I have a windows application that uses a sql server remote database.
Which is the best method to deploy an application that has to connect to a remote server on an another computer, that is part of the LAN Network.
I have been told that during installation the connection will be setup.
At the moment I am using the sqlclient connection. I have my connection string in the app.config. But when the customer installs that want to be able to browse to the server that they will have to connect to.
Well as I have never deployed, I will never know what is best.
Give you could give a short example, i would be most grateful.
Thanks
|
|
|
|
|
The best thing to do would be to create a dialog window the allows the user to choose the server and database. Eamonn Murray posted an article on CodeProject that can be used as a good tutorial on how to do this. (http://www.codeproject.com/cs/database/SubmitSQLInfoEnumerator.asp[^])
You'll probably need to modify his code to use Windows Authentication if that is what you need to support. I'm assuling that you'll also want to store the resulting connection string in a file so that the user doesn't need to select the database every time the application runs, but those are requirement details. Anyway, the above article should tell you everything you need to know.
|
|
|
|
|
Hi guys, I'm designing a sql server database for a recruitment site, I have a resumes table that stores pieces of information about every jobseeker, among this information is the employment history which includes(Job title, company name, from, to), of course one jobseeker can have multiple previous jobs. Now, I want to know how this structure would be applied? this structure suggests a field within the resume table that is by itself a table! So, any ideas how can I design this database
|
|
|
|
|
One table for JobSeekers with 1 row each and a primary key JSID (autonumber long should suffice)
Another table for JobsHeld with a foreign key field whose value is the pk (JSID) from the jobseekers table, and a jobnum field that is just the order of the jobs held (then the pk for this table can be the pk from jobseekers = the jobnum, since the combination would be unique.) The JobsHeld table has 0-n rows for each row in the JobSeekers table, they can always be retrieved and associated with the JobSeeker by a Join on the JSID field. A rather classic example of a one to many relationship.
|
|
|
|
|
Hello Rob, thanks for your reply.
Rob Graham wrote: The JobsHeld table has 0-n rows for each row in the JobSeekers table
I don't get this.
I was wondering what about using xml as the data type for a field named say(Employment history), so I can nest an xml table in this field for every record in the resumes table...any suggestion/comment?
|
|
|
|
|
That will be a mess to implement and maintain. The solution I proposed is quite common in database applications, sometimes called master-detail:
<font>JobSeekers
Jsid | lastname | firstname | Address 1 | address 2 | Telephone
---------------------------------------------------------------
1 | Smith | Robert | 123 22nd St| Apt 35 | 555-9898
----------------------------------------------------------------
2 | Jones | Bill | 1532 1st St| <null> | 555-9987
-----------------------------------------------------------------
3 | Green | James | 12 Maple St| <null> | 555-8906
JobHistory
JSID | JobNum | Start | End | Employer
------------------------------------------------------------
1 | 1 | May 1985 | June 1986 | Acme tools
------------------------------------------------------------
1 | 2 | July 1986 | March 2006 | Big Gun Software
------------------------------------------------------------
2 | 1 | march 2000| March 2006 | Acme Componets
</null></null></font>
The Jobseeker with JSID 1 had two prior jobs, the 1 with JSID 2 had only 1, the one with Jsid 3 had no prior jobs.
I can always easily get the job history for any JSID by
" Select * from JobHistory Where JSID = 2 "
I store no more and no less data than I need, It's easy to correct the JobHistory by adding or removing rows.
[added comment:]
More importantly, If I later discover that i want more data in the JobHistory Table, I can just
Add fields to contain it. With your approach, the xml structure would change, and you would have to
Query and correct each instance.
Also if I want some results based on job history, say all applicants who previously worked at Acme,
it's a simple query. With your approch, you must once again examine every record's job history xml -
a real pain.
Last modified: 26mins after originally posted --
|
|
|
|
|
Thank you very much Rob for the clarification, that was really helpful.
I'm sorry Rob just one more question:
This recruitment site is part of a large portal which has other services, so the whole portal has its own database apart from the recruitment's. Now I want to authenticate the user once I mean if this user(JobSeeker) had already been logged in from somewhere on the portal modules, I don't want to ask'em again to sign in to search jobs or modify his resume. Now, with that said I would create a FK say UserID in the resumes table in the (jobs) database whose value is the PK of the Users table of the (portal) database. So, is that possible??
Thanking you in advance..
-- modified at 21:33 Thursday 21st September, 2006
|
|
|
|
|
Sure, you can add however many FK's you want to a table. Using a single userId key from the main login table as the FK in all related table seems quite sensible.
|
|
|
|
|
Rob,
This another login table is from another database..So can this still be done? referencing a PK from a table in another database??
|
|
|
|
|
Hi guys
I've got a design question regarding how something should be modeled in SQL. I have an object hierarchy like this:
class Foo
- string
- bool
- int
- Guid
class FooInheritor : Foo
- double
- int
- int
class AnotherFooInheritor : Foo
- float
- string
And so on, with another 15 or so classes deriving from Foo.
In my SQL database, I have a Foo table that stores all Foo types (including inheritors), then another FooInheritorTable that stores FooInheritor-specific data, a AnotherFooInheritor table that stores AnotherFooInheritor-specific data, and so on.
My question is:
when doing SQL queries, what is an acceptable way to query for a Foo instance? If I do SELECT * FROM FooTable WHERE @ID = someID, that will give me all Foo-specific data, but if the Foo being returned is actually a FooInheritor, what then? I would need to do another query on the FooInheritorTable to get the FooInheritor-specific data. And if the Foo returned is actually AnotherFooInheritor, then I need to do another query on the AnotherFooInheritorTable, and so on.
Should I:
- not model the tables after the real objects?
- create a view that returns all possible data for all 15 or so tables?
- query the FooTable, then when I get the results back, do another query on the table specific for that type?
I'd love to hear any suggestions. Thanks for taking the time to read this long post.
|
|
|
|
|
I'm not sure you gave us enough information to answer this question well. Are you looking for a single resultset that shows EVERY Foo with its Foo specific data, or are you just querying one Foo at a time? How dynamic do you want the final solution to be? Are you more concerned with the results being returned quickly, or do you want the design to look real clean (which seems to be a hangup most of us have)? Are the tables changed frequently, or is the data static? How often do you retrieve the data? How many records are we talking about here? What are the relationships between the tables (i.e. do you have a field in the Foo table that has the name of the table the FooInheritor specific data is found in)?
Nonetheless, here are a couple thoughts:
Judah Himango wrote: - not model the tables after the real objects?
Speed wise, you would probably be better off with denormalization. Especially if the tables rarely change, yet you query them frequently. It isn't pretty, but redundant data isn't always a bad thing.
Judah Himango wrote: - create a view that returns all possible data for all 15 or so tables?
I tend to not like this kind of stuff. It usually looks like a mess, is not easily scaleable, and is difficult to maintain. You have to remember to change the view whenever you add a new FooInheritor type.
Judah Himango wrote: - query the FooTable, then when I get the results back, do another query on the table specific for that type?
This is probably easiest to write, along with keeping things normalized. But, two queries aren't as fun as one. 
--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
