29,893 reputation
874126
bio website
location United Kingdom
age
visits member for 3 years, 4 months
seen 8 hours ago

Email: stackoverflowmartinsmith@gmail.com


Jan
23
comment Multiply operation/field causing the record set to 'ungroup'?
Without an ORDER BY you can't expect any particular order.
Jan
18
comment Design: how to store lots of booleans?
What RDBMS are you using? What type of queries do you need to do against this data?
Jan
17
comment Update all rows
As far as I understand it adding a new not null column with a default is a metadata only change in Oracle. I doubt they will have optimised the "update all rows to the same value" case. Is this a common operation for you?
Jan
16
comment find all numbers that appear at least three times consecutively
MySql doesn't support windowed aggregates.
Jan
15
comment Would it be better for query plans to be split by statement for re-usability?
dbid and objectid both have is_cache_key=1 so you won't get any reuse of plans between different compiled objects.
Jan
12
comment Performance of query with complex where clause on 10M rows
What version of SQL Server are you on?
Jan
10
comment How can I make this query sargable?
Thanks. I see it now!
Jan
10
comment How can I make this query sargable?
Ah Ok. I'm sure you're right but I don't see the translation to 0x either though. It just gets translated to the next rowversion column as far as I see. 0x00000000000007D9, 0x00000000000007DA, 0x00000000000007DB
Jan
10
comment How can I make this query sargable?
Regarding the "when not nullable, varbinary(8) otherwise" is it possible to get this to happen? If I try and create a nullable timestamp column and insert an explicit NULL it still doesn't end up as NULL. The column also ends up in the fixed length data part of the row. CREATE TABLE #T (A TIMESTAMP NULL,B INT,C INT);INSERT INTO #T VALUES (NULL,1,1);SELECT * FROM #T;DROP TABLE #T;
Jan
4
comment Does SQL Server read all of a COALESCE function even if the first argument is not NULL?
And looks as though there was another issue with CASE that was quietly fixed
Jan
3
comment Writing Conditions in Left join sql
This isn't a "rare scenario". It is a straight forward application of the rules described there.
Jan
3
comment Writing Conditions in Left join sql
Does this help?
Jan
2
comment Adding Last 12 Months to a row in a table (SQL Server)
This inserts a single value to 12 rows. I think they want the other way round (12 values to a single row)
Jan
2
comment Adding Last 12 Months to a row in a table (SQL Server)
next question, why do you need to store all 12 months? Presumably you just need to store one as the others can be calculated by just doing dateadd(month? Also what is this table actually representing anyway?
Jan
2
comment Adding Last 12 Months to a row in a table (SQL Server)
Please give the table definition and example "before" and "after" states.
Jan
2
comment Resource cost of a blocked query
Though I guess it will still hold resources such as memory grants until it ends.
Jan
1
comment Error 18456 State 38 on Deleted Databases
Error 18456 is login failed isn't it?
Jan
1
comment Understanding statistics, execution plans, and 'ascending key problem'
RECOMPILE wouldn't cause a stats update anyway.
Jan
1
comment Store Repeated Data
So the proposed new structure is 100 heap tables. Each heap containing 1bn rows with a single column with a bigint value? What kind of queries do you do against this data? Purely aggregation? Or are there other columns too?
Dec
30
comment What do these parameters mean at the beginning of my SQL Server query plan?
Are suggestions 1 and 2 deliberately the same?