Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

for example, I have tables: User and Order

User Table:
UserId, Name, Address, Phone, Age

Order Table:
OrderId, UserId, Date, Item, Quantity

SQL:

select User.Name, Order.Date
From Order 
JOIN User ON Order.UserId = User.UserId

Will the join produce a virtual table including all fields in the tables or just the two in the select?

The point to this question is if one of my fields is large (> 1GB) then I would like to place it in its own table and not leave it in a table that gets joined many times all over the place... in order to keep the large fields from being a burden across the board.

share|improve this question
2  
It is possible to give a general answer but can you state RDBMS and your actual CREATE TABLE? –  Martin Smith Feb 19 '13 at 16:49
    
SQL 2008 is the RDBMS –  sqlthing Feb 19 '13 at 16:54

2 Answers 2

The internals of any query operation can be reviewed in the query execution plan. There are many factors involved in how the data is extracted and queried from the source tables, but for the purposes of your question, there are two elements you need to be concerned about:

Seeks and Scans

The first step to any query is to extract the data from the source tables. This is performed using one of two actions (and variations within): a seek or a scan. A seek is where the query engine can leverage an index to narrow down the data retrieval. If no index is available, then the engine must perform a scan of all records in the table. In general, seeks typically perform better than scans due because it is more selective.

Physical Joins

Once data is retrieved from the tables, it must be physically joined. Note, this is not the same as logical joins (INNER and OUTER). There are three physical join operators that can be used by the engine:

The query engine will evaluate several factors to determine which physical join will be the most efficient for the query execution. As a simple example, I assembled this SQL Fiddle example, where you can view a basic execution plan using your sample schema. Note, however, that this plan could change with more data being queried. You will need to review the query plans in your environment to truly understand how your data is being retrieved.

Query plan analysis and performance tuning is beyond the scope of this answer, but I strongly recommend getting a hold of Grant Fritchey' SQL Server 2012 Query Performance Tuning for a comprehensive explanation of query execution internals and tuning approaches.

share|improve this answer
    
An index can also be scanned (and a seek is often actually a range scan), so it's a little misleading to say "if no index is available..." Also scans are sometimes the most efficient approach to obtaining data, depending on the composition of the index (not necessarily the presence of an index). While the seek itself would be more efficient on its own, it is often accompanied by an expensive lookup that outweighs the advantages of the seek. –  Aaron Bertrand Feb 19 '13 at 18:52
    
you missed the point. will the virtual table resulting from the join have all the fields of both original tables, or just the fields needed? I am not worried about execution plans and this detail is not present there anyway! –  sqlthing Feb 19 '13 at 19:04
    
@sqlthing careful there. It could be possible that you didn't explain your point well or that you are focusing on the wrong thing. –  Aaron Bertrand Feb 19 '13 at 19:12
    
@sqlthing Correct me if I'm wrong, but your question seems to revolve around the most efficient way to retrieve data from your two tables. The information regarding this is going to be contained within the execution plan for your query. If this is NOT the case, please clarify. –  Mike Fal Feb 19 '13 at 19:30

Large columns may or may not affect queries that don't reference them - there is a lot of "it depends" here.

For example, if a query performs a clustered index scan, it has to read all of the pages in the table. If you have LOB types (e.g. nvarchar(max)), the data may or may not be stored in-row, depending on size and your settings. In this case, you may have fewer rows per page, and in turn more pages have to be read to satisfy the query. But in most cases (particularly if your data really is as large as you say it is), the data will be stored off-row, and you only have the extra pointer per row, so unlikely to cause much difference.

However if your queries are satisfied and covered by indexes that do not include the LOB data, then those large columns will have no effect whatsoever on your query performance, because the pages that contain that data never have to be looked at.

As a separate point, I might ask why you want to store > 1GB of data in a column in a database in the first place. What is the nature of this data? Have you considered filestream (or filetable if you can move to SQL Server 2012)?

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.