737 reputation
515
bio website
location
age 32
visits member for 3 years
seen Dec 20 at 11:54
sql must be lowercase.

Feb
15
asked Filtered indices: Why include the filtered-on field?
Feb
10
answered Restrict SQL Server 2008 R2 User to restore only one database (but not others)
Feb
10
comment How does objectproperty() figure out if a function is deterministic in sql server?
@BrianDishaw I'm not sure, to be honest. Apparently because any data-accessing function is not deterministic unless schema bound, and SQL Server expands this behaviour to functions that don't in fact access data. Maybe it's not able to see they don't.
Feb
9
answered How does objectproperty() figure out if a function is deterministic in sql server?
Feb
8
comment Window functions cause awful execution plan when called from a view with external parametrized 'where' clause
@MartinSmith Yes, pretty much so.
Feb
8
comment Window functions cause awful execution plan when called from a view with external parametrized 'where' clause
One of the requirement was the query is consumable by end users using Excel (that is, by MS Query), and MS Query will not let you do that, at least in versions up to 2003.
Feb
8
comment Window functions cause awful execution plan when called from a view with external parametrized 'where' clause
@MartinSmith I understand all that; what wonders me is the peculiar combination of four things: 1) the index on order_number has utter selectivity; 2) the window function partitioned on exactly that field so it is known prior to execution that all other order_numbers are not relevant to it; 3) when the window function is removed from the view the query does the right thing in all cases, sniffing or not; 4) estimated totally equals actual on all stages - the server KNOWS it's about to dig through the whole database, at it seems to enjoy it.
Feb
8
comment Window functions cause awful execution plan when called from a view with external parametrized 'where' clause
Yet another workaround, yes. I couldn't do that though because not all clients were able to consume a table-valued function.
Feb
8
comment Window functions cause awful execution plan when called from a view with external parametrized 'where' clause
@MartinSmith option(recompile) does help. That'd be another workaround I guess (the one I couldn't use because not all target cliens could append an option to a query).
Feb
8
comment Window functions cause awful execution plan when called from a view with external parametrized 'where' clause
order_number is not a primary key. It is int not null with nonclustered index on it in both tables.
Feb
8
asked Window functions cause awful execution plan when called from a view with external parametrized 'where' clause
Feb
5
comment What computations can NOT be performed in standard SQL?
Related: Is SQL or even TSQL Turing Complete?
Feb
4
awarded  Commentator
Feb
4
comment Adding SPARSE made table much bigger
Awesome. Should we take it as a bug in the documentation? "The SQL Server Database Engine uses the following procedure to accomplish this change: 1) Adds a new column to the table in the new storage size and format. 2) For each row in the table, updates and copies the value stored in the old column to the new column. 3) Removes the old column from the table schema. 4) Rebuilds the table to reclaim space used by the old column."
Feb
4
awarded  Scholar
Feb
4
accepted Adding SPARSE made table much bigger
Feb
4
awarded  Student
Feb
4
comment Adding SPARSE made table much bigger
@MartinSmith Does have a clustered index rowid int not null identity(1,1) primary key clustered.
Feb
4
asked Adding SPARSE made table much bigger
Feb
2
comment Does SQL Server have Date_Format function?
Possible duplicate of stackoverflow.com/questions/8202257/…