707 reputation
410
bio website
location
age 31
visits member for 2 years, 4 months
seen Mar 24 at 13:26
sql must be lowercase.

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/…
Jan
26
comment Insert 4 tinyint values into varbinary(4)
Possible duplicate of stackoverflow.com/questions/695568/…
Jan
26
answered Insert 4 tinyint values into varbinary(4)
Jan
24
comment t-sql - combinatorics
Sources of inspiration: 1, 2.
Jan
24
comment Can I call SMO from pure T-SQL?
Well. I've finally tricked my SQL Server project into allowing me to reference SMO. And what do I get when I execute my CLR function on the server: System.Exception: This functionality is disabled in the SQLCLR. It is recommended that you execute from your client application. (at Microsoft.SqlServer.Management.Common.ConnectionManager..ctor(SqlConnection sqlConnectionObject)). Apparently your advice to employ CLR for this particular purpose is not valid after all :(
Jan
24
comment Can I call SMO from pure T-SQL?
It is installed on my dev box, I can reference the assembly from a "normal" project and it works fine. But for "SQL Server" projects (the ones you have for creating stored procedures), the SMO assembly is not on the list of allowed references (which are few). So I wondered if it was possible to copy SMO assemblies to SQL Server and whether it was a sensible thing to do.
Jan
24
comment Can I call SMO from pure T-SQL?
Alas, it's not. I've got a CLR stored procedure, from inside of which I must figure the default value of the given parameter of the given SQL stored procedure. This information is not available via SQL, because sys.parameters only contains default values for CLR objects; for SQL objects documentation suggests parsing the result of OBJECT_DEFINITION. On contrary, SMO returns the default value of an SQL object's parameter just fine.