Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
rizo
Starting Member
16 Posts |
 Posted - 2011-09-12 : 11:51:44
|
| Hi guys,Instead of creating tables to use in SQL, is it not possible to create and use queries just like in in Access?Basically is it possible to use a query within a query in SQL Server just like it is possible in Access?rizo |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-09-12 : 11:53:31
|
| its possible. it is called derived table.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
rizo
Starting Member
16 Posts |
Posted - 2011-09-12 : 12:13:13
|
| thanks for the prompt reply.I have around 20 tables and 90 queries, would the SQL server handle it well?I know there isn't a straight forward answer but I'm looking for ideas.rizo |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2011-09-12 : 12:38:20
|
| SQL Server has an optimiser which is part of how it decides how to find the data you are looking for.If you give it a big query there is a good chance that the optimiser will find smart ways to find the data. It will certainly take into account whether using Index-A is likely to be faster than using Index-B. If there is some part of a sub-query that is not needed, this time around (because of the parameters you are using, for example), then it will exclude it from the query.If you give SQL a small query, and store the results in a temporary table, and then use that #TempTable in your next query SQL Optimiser has no ability to help - it will just do it the way you are forcing SQL server to work.When you've got your "really big query", and you want to make it faster, you can ask SQL Server to recommend additional indexes (that's a bit simplistic - you can look at how the query planner has decided to execute your query, and you can then tune the query - commonly by adding an index).Back to your #TempTable approach: that would be a bit like tying SQL Server's hands behind its back ... it would be unable to help, much, with index suggestions etc.But, yeah, "it depends". Your monster query may sink the battleship! but 20 tables sounds "normal" to me, rather than "exceptional" |
|
|
|
rizo
Starting Member
16 Posts |
Posted - 2011-09-12 : 12:42:01
|
| Certainly gave me a something to think aboutwill (likely) come back if i have further questionsthanks for thatrizo |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2011-09-12 : 12:57:54
|
One thing you might try, if you are coming from Access.If you have been used to creating a Query (called "Query1") which portrays a sub-set of your data, and then Query2 that refines that, you could create Query1 and Query2 as VIEWs in SQL Server.CREATE VIEW Query1ASSELECT Col1, Col2, ...FROM MyTableWHERE Col3 = 'XXX'GOCREATE VIEW Query2ASSELECT Col1, Col2, ...FROM Query1WHERE Col2 = 'YYY'GO and then you can just SELECT * FROM Query2. This approach might help you build up your query "in stages".There are probably better ways of doing of achieving what you want to in SQL Server, but you have to start somewhere ... and starting with something reasonably familiar will hopefully help get you going. SQL Server optimiser should be able to sort out what's-what from the resulting nested-VIEWs' query |
|
|
|
|
|
|
|
|
Temp tables or queries within queries?