Reputation
Top tag
Next privilege 1,000 Rep.
See votes, expandable usercard
Badges
1 11 25
Newest
 Caucus
Impact
~98k people reached

Jul
28
comment Why is table variable forcing an index scan while temp table uses seek and bookmark lookup?
@srutzky: yes, saw the same behavior with that as well...
Jul
28
comment Why is table variable forcing an index scan while temp table uses seek and bookmark lookup?
@AaronBertrand: does the existence of statistics in the #temp table allow the optimizer to see the value that the query is joining against and thus use the statistics for the index on the real table to determine how many rows can be found?
Jul
28
comment Why is table variable forcing an index scan while temp table uses seek and bookmark lookup?
@@VERSION is SQL Sever 2008 R2 (SP2) - 10.50.4042.0 (X64) (Build 7601: Service Pack 1) (Hypervisor)
Jul
28
comment Why is table variable forcing an index scan while temp table uses seek and bookmark lookup?
@srutzky: unfortunately that didn't change the behavior.
Jul
28
comment Why is table variable forcing an index scan while temp table uses seek and bookmark lookup?
Added a SQL Fiddle. As for table variables vs. temp tables, the table variable is coming to the stored procedure as a UDDT. So I want to avoid having to write the data again to tempdb in a temp table.
Jul
28
comment Why is table variable forcing an index scan while temp table uses seek and bookmark lookup?
Both the table variable and temp table estimate 1 row. The scan estimates all rows in the table and the index seek estimates one row.
Jul
28
comment Why is table variable forcing an index scan while temp table uses seek and bookmark lookup?
The table variable represents a UDDT coming into a stored procedure. I am trying to avoid writing to a temp table because this code will be used on a large scale. Also, am curious to know the why of the behavior.
Apr
3
comment How does SQL Server's optimizer estimate the number of rows in a joined table?
Thanks @Mark, the trace flags confirmed that the statistics were coming from the indexes listed in the query plan, but that's the extent of it.
Jun
22
comment How to gracefully handle a source database restore from backup in a data warehouse
@MarkStorey-Smith: I think a rebuild is a possibility; is that how this is typically handled in the field?
Dec
14
comment ETL: extracting from 200 tables - SSIS data flow or custom T-SQL?
BIML looks very interesting. I was also considering creating each data flow as a separate package and then having them called through a master package. Do you think that's any better? Also, curious if you have an opinion on the T-SQL approach. It's slower but I've tested it and it will work.
Dec
14
comment ETL: extracting from 200 tables - SSIS data flow or custom T-SQL?
The databases reside on multiple servers which I think makes it more complicated. I have actually tried creating a view of different tables on my development box against 225 databases and reading of the data was painfully slow.
Oct
27
comment overhead for INSERTS using change tracking and/or change data capture (CDC)
You may find this link useful: databasejournal.com/features/mssql/article.php/3806001/…
Oct
25
comment Data archival strategy that handles schema changes?
Maybe substantial is the wrong word but the functionality is constantly evolving. It would not be unusual to see the addition of new tables and columns as well as modifications to existing ones.
Aug
29
comment Does surrogate key assignment for a fact table require that the source data has natural keys?
In this example, I think the rules are one order per one customer and one product (not realistic, I know).
Aug
29
comment Does surrogate key assignment for a fact table require that the source data has natural keys?
I think so. Can you elaborate on what that mapping table might look like based on the example I have?
Aug
24
comment How do you roll up header-level values on a fact table with line-item granularity?
The query makes sense. But does this mean that the roll up can only occur within a view of some sort? If you were to take the fact table and build a cube from it, it seems like the customer wouldn't be able to do these roll ups in a pivot table.
Aug
24
comment How do you roll up header-level values on a fact table with line-item granularity?
@Jamie: But if I want to do a browse query on the Product dimension (say, all Products who fall under a certain brand), this design means I'll need to somehow filter out all my "magic" products. Is that also correct? It seems like the problem can be solved by having two fact tables: one for header level and one for line-item level. But nobody seems to recommend this.
Aug
24
comment How do you roll up header-level values on a fact table with line-item granularity?
Would the "magic" Shipping product be a placeholder so that a line item with 0 quantity and 0 order amount could exist with the shipping amount? Does this mean you would then zero out the Shipping amount on the other line items?
Aug
7
comment Filtering data ordered by rowversion
How does the date field fit in? Can a row with a particular ID and Date be updated in the table? And if so, is the date also updated (like an additional timestamp?)
Aug
2
comment How to break out of error loop in transactional replication?
I agree. For the future, I've modified the replication procedures so the error is logged. But the agent's attempt to "keep trying" seems like the equivalent of a service broker poison message. I don't see what is gained from not stopping the service.