interesting.

The top query also fails when you do this

exec sp_cursorprepare @p1 output,NULL,N'select pk from t11 order by pk', 1, 16400, 8193

but not when you do this

exec sp_cursorprepare @p1 output,NULL,N'select *...

GilaMonster (12/22/2015)

---

Very unlikely. Partitioning is not for performance. It's for data management.

Will partition gain performance for...

James Goodwin (12/21/2015)

---

Instead of:

select [col1], [col2] from Customer

where BranchId = CASE when @BranchId IS NULL THEN BranchId else @BranchId end

I usually go with

select [col1], [col2] from Customer

where (@BranchID is...

Yes

Somehow this fact is not well understood.

IF something is not working and you want to prove it to us you need a couple of things.

First, ask, how do I prove this to people who cant see...

?

For general best practices and rules of thumb, you can look on the stairways on this site, or articles.

A great site is www.SqlInTheWild.co.za

Read there what an absolute pro thinks is...

Do you have a read-only filegroup living on an NTFS compression enabled folder?

This post has already been posted here.

http://www.sqlservercentral.com/Forums/Topic1745319-3412-1.aspx

I think filestream may make it impossible to change the files without doing inside a transaction from SQL.

http://www.sqlservercentral.com/articles/SQL+Server+2008/64088/

Jeff Moden (12/20/2015)

---

If you think you have problems with statistics, imagine code that automatically generates statistics for every bloody table column in every bloody database and then regenerates them all...

USP1 should create 1 plan.

USP2 creates millions cos you hard coding the String value into the query. Since it is not simple because of the like %%. If it were...

http://bfy.tw/3Nxw

https://msdn.microsoft.com/en-GB/library/ms186755.aspx

--Transact-SQL Scalar Function Syntax

CREATE FUNCTION [ schema_name. ] function_name

( [ { @parameter_name [ AS ][ type_schema_name. ] parameter_data_type

...

why not

create procedure GetCustomerByBranchID(@BranchId int)

AS

BEGIN

SELECT [col1], [col2] from Customer where BranchId = @BranchId

END

GO

create procedure GetAllCustomers

AS

BEGIN

SELECT [col1], [col2] from Customer

END

GO

if @BranchId is not null

Begin

exec GetCustomerByBranchID @BranchId

END

ELSE

BEGIN

...

I'm guessing you want to pivot on values that you do not know until the proc executes.

This might work, may be a syntax error but this is the general idea...