1,929 reputation
311
bio website
location
age
visits member for 2 years, 1 month
seen 5 mins ago

11h
comment Strange query results for DELETE with ROW_NUMBER
@ypercube Irrelevant.
11h
comment Strange query results for DELETE with ROW_NUMBER
@MartinSmith By unsafe, I'm referring to the fact that when attempting to use this method and attempting to inspect the output using the OUTPUT clause, you run into an unexpected bug in the current SQL Server implementation. Imagine if some logic was based on that output? eg Composable DML. That would then lead to a bug in app code. Unsafe. Regarding set-based, I am referring to the fact that my approach is a classic set-exception approach. The original approach, assigning an artificial row identifier to every record in the set using window function extensions just to delete them, is not.
11h
comment Strange query results for DELETE with ROW_NUMBER
@martinsmith No I am suggesting set-based DELETE rather than DELETE with window function. On reflection it's safer too.
15h
comment Strange query results for DELETE with ROW_NUMBER
@ypercube It's a workaround for what I believed to be the general logic requrid for the query: "Delete some rows, keep these". OP has also indicated it's a "valid workaround".
19h
answered Strange query results for DELETE with ROW_NUMBER
1d
awarded  Explainer
1d
comment Subqueries in SQL Server 2014
Where you in the right database when you ran the script? I am using 'student' as an alias for FeeStudent, as per your original question.
1d
revised Subqueries in SQL Server 2014
Code Sample included
1d
suggested suggested edit on Subqueries in SQL Server 2014
1d
answered Subqueries in SQL Server 2014
2d
comment Clustered Columnstore indexes in SQL Serer 2014 for data warehousing
Also regarding "no value", non-clustered columnstore is a separate structure, taking separate space and although you can partition switch data in it wasn't updateable in the usual way. The clustered columnstore is completely writeable and basically is the data stored in columnar format, offering two levels of compression (COLUMNSTORE and COLUMNSTORE_ARCHIVE). The documentation advertises up to 7x, but I've seen much higher values, depending of course on the data. YMMV.
Oct
16
comment Filtering the results of a SELECT query to avoid certain words in the value of a column?
Translated to MySQL here: sqlfiddle.com/#!2/94adef/13/0
Oct
10
revised Terrible Bad Query Plan
SQL Server 2008 R2 tag added
Oct
10
suggested suggested edit on Terrible Bad Query Plan
Oct
10
comment Terrible Bad Query Plan
The sql plans show build 10.50.4319.0 which is SQL 2008 R2 SP2 CU 13.
Oct
10
comment Terrible Bad Query Plan
The Actual Execution plans and table and index DDL would be genuinely useful.
Oct
10
comment Split date/time range into day ranges
Let us continue this discussion in chat.
Oct
10
revised Split date/time range into day ranges
Refactor for calendar table, new test data
Oct
10
comment Split date/time range into day ranges
lol it's ok. I've refactored to use a calendar table and it works with your new data and honours time. Before I post it I want you to promise me the next time you post a question you think properly about the data you post. People have posted perfectly good answers that work with the data you provided, but you have changed it 3-4 times.
Oct
10
comment Split date/time range into day ranges
"...once the answer has been updated to reflect this i will mark this as the correct answer...". How very munificent of you!