Case Senstivity on SQL Server

I have written several stored procedures on my local SQL server database.
Through my coding i will occasionally change the case of a variable. For
example I will declare a variable @.SQL varchar(20) but later call it @.sql
(lower case).
My sql server isn't case sensitive, but other databases I have loaded the
stored procedure are. How can I change the SQL server settings to not be
case sensitive? Plus how I can I change it back if it screws something up
with other applications that use other databases on the server? Is it a
server setting or database setting?
Thanks in advance!
Matt,
You will be much better off correcting the uppercase/lowercase
inconsistencies in your stored procedures. The most reasonable
assumption is that the server and database collations were chosen for a
good reason, but it doesn't sound like there's any reason you need to
vary the case in your procedures. To answer your question, yes, it can
break things if you change the collation of a database. One of the more
likely things that can happen is that it will cause data type
conversions that make indices impossible to use, slowing down performance.
The collation of the server instance is set when the instance is
installed. You can change database collations with ALTER DATABASE, but
why risk breaking things because you don't want to take the time to be
more careful coding?
Steve Kass
Drew University
Matt Tapia wrote:

>I have written several stored procedures on my local SQL server database.
>Through my coding i will occasionally change the case of a variable. For
>example I will declare a variable @.SQL varchar(20) but later call it @.sql
>(lower case).
>My sql server isn't case sensitive, but other databases I have loaded the
>stored procedure are. How can I change the SQL server settings to not be
>case sensitive? Plus how I can I change it back if it screws something up
>with other applications that use other databases on the server? Is it a
>server setting or database setting?
>Thanks in advance!
>
>
>

Case Conditional in SQL Statement - MS SQL 2000

Hi,

I'm trying to do calculations in a SQL statement, but depending on one
variable (a.type in example) I'll need to pull another variable from
seperate tables.

Here is my code thus far:

select a.DeptCode DeptCode,
a.Type Type,
(a.ExpenseUnit / (select volume from TargetData b where b.type =
a.type)
) Expense
Fromcalc1 a

The problem... a.Type can be FYTD, Budget, or Target... and depending
on which one it is, I need to make b either FYTDData, TargetData, or
BudgetData. I'm thinking a case statement might do the trick, but I
can't find any syntax on how to use Case in an MS SQL statement. Even
If statements will work (if that's possible), though case would be
less messy.

Any suggestions would be much appriciative. Thanks...

Alex.Hi

Is it not totally clear how you are joining these tables, but this may be a
start.

SELECT a.DeptCode DeptCode,
a.Type Type,
a.ExpenseUnit / ( CASE WHEN a.Type = 'FYTD' THEN b.volume
WHEN a.Type = 'Budget' THEN
c.volume
WHEN a.Type = 'Target' THEN
d.volume
ELSE 1 END ) AS Expense
From calc1 a
LEFT JOIN FYTDData d ON b.type = a.type
LEFT JOIN BudgetData d ON c.type = a.type
LEFT JOIN TargetData d ON d.type = a.type

John

"Alex" <alex@.totallynerd.com> wrote in message
news:2ba4b4eb.0310010840.5910e221@.posting.google.c om...
> Hi,
> I'm trying to do calculations in a SQL statement, but depending on one
> variable (a.type in example) I'll need to pull another variable from
> seperate tables.
> Here is my code thus far:
> select a.DeptCode DeptCode,
> a.Type Type,
> (a.ExpenseUnit / (select volume from TargetData b where b.type =
> a.type)
> ) Expense
> From calc1 a
> The problem... a.Type can be FYTD, Budget, or Target... and depending
> on which one it is, I need to make b either FYTDData, TargetData, or
> BudgetData. I'm thinking a case statement might do the trick, but I
> can't find any syntax on how to use Case in an MS SQL statement. Even
> If statements will work (if that's possible), though case would be
> less messy.
> Any suggestions would be much appriciative. Thanks...
> Alex.|||Alex (alex@.totallynerd.com) writes:
> The problem... a.Type can be FYTD, Budget, or Target... and depending
> on which one it is, I need to make b either FYTDData, TargetData, or
> BudgetData. I'm thinking a case statement might do the trick, but I
> can't find any syntax on how to use Case in an MS SQL statement.

Books Online is a very resource for this kind of information, just
look up CASE. Be careful to notice that this is not a statement, but
an expression.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"John Bell" <jbellnewsposts@.hotmail.com> wrote in message news:<3f7b0da9$0$8765$ed9e5944@.reading.news.pipex.net>...
> Hi
> Is it not totally clear how you are joining these tables, but this may be a
> start.
> SELECT a.DeptCode DeptCode,
> a.Type Type,
> a.ExpenseUnit / ( CASE WHEN a.Type = 'FYTD' THEN b.volume
> WHEN a.Type = 'Budget' THEN
> c.volume
> WHEN a.Type = 'Target' THEN
> d.volume
> ELSE 1 END ) AS Expense
> From calc1 a
> LEFT JOIN FYTDData d ON b.type = a.type
> LEFT JOIN BudgetData d ON c.type = a.type
> LEFT JOIN TargetData d ON d.type = a.type
> John

Hi John...

I did get it going yesterday after spending about an hour testing
syntax. Below is the final SQL statement. Works great!

select a.DeptCode,
a.Type,
(((a.TotalPaidHoursUnit/(Case a.type
When 'FYTD04' Then null
When 'Budget' Then (select b.monthly from it_budvol b where
b.deptcode = a.deptcode)
When 'Prior Year' Then (select b.avemonth from it_pyvolume b
where b.deptcode = a.deptcode)
Else (select b.AveMonthVolume from solucient_dss b where
b.deptcode = a.deptcode and b.type = a.type)
end)
- a.FYTD_TotalPaidHoursUnit ) / a.Hours) * a.FYTD_Volume) LaborFTE

Fromdss_calc a

Thanks for the feedback.

Alex.|||alex@.totallynerd.com (Alex) wrote in message news:<2ba4b4eb.0310020704.4c08463e@.posting.google.com>...
> Hi John...
> I did get it going yesterday after spending about an hour testing
> syntax. Below is the final SQL statement. Works great!
>
> select a.DeptCode,
> a.Type,
> (((a.TotalPaidHoursUnit/(Case a.type
> When 'FYTD04' Then null
> When 'Budget' Then (select b.monthly from it_budvol b where
> b.deptcode = a.deptcode)
> When 'Prior Year' Then (select b.avemonth from it_pyvolume b
> where b.deptcode = a.deptcode)
> Else (select b.AveMonthVolume from solucient_dss b where
> b.deptcode = a.deptcode and b.type = a.type)
> end)
> - a.FYTD_TotalPaidHoursUnit ) / a.Hours) * a.FYTD_Volume) LaborFTE
> Fromdss_calc a
> Thanks for the feedback.
> Alex.
Hi

You should make sure that you are not dividing by zero.

John

Case conditional action

Is it possible to check a variable in a case statement then perform a select
query depending on the variable? What I have tried is something like this
which does not work. aNyone know how this can be accomplished?
declare @.@.var int, @.rtn int
set @.var = 3
select case @.var when 1 select @.rtn = catid from categories
when 2 select @.rtn = itmid from categories
when 3 select @.rtn = catmid from categorymixbjamin wrote:
> Is it possible to check a variable in a case statement then perform a sele
ct
> query depending on the variable? What I have tried is something like this
> which does not work. aNyone know how this can be accomplished?
>
> declare @.@.var int, @.rtn int
> set @.var = 3
> select case @.var when 1 select @.rtn = catid from categories
> when 2 select @.rtn = itmid from categories
> when 3 select @.rtn = catmid from categorymix
--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
A CASE expression is like this:
CASE <condition>
WHEN <evaluation result 1>
THEN <true result> ELSE <false result>
WHEN <evaluation result 1>
THEN <true result> ELSE <false result>
.. etc. ...
END
Use IF:
IF @.var = 1
SELECT @.rtn = catid FROM categories
IF @.var = 2
SELECT @.rtn = itmid FROM categories
IF @.var = 3
SELECT @.rtn = catmid FROM categorymix
These statements assume that there is only 1 row in each table, which is
probably wrong. What are you really trying to do?
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/ AwUBRAy4TYechKqOuFEgEQKYbwCgzax4m6K6c5lD
CcRV3sxStCYnhlUAoPRg
pAwpcd7fD1BWfkIGeB21eKuG
=opZI
--END PGP SIGNATURE--|||bjamin (bjamin@.discussions.microsoft.com) writes:
> Is it possible to check a variable in a case statement then perform a
> select query depending on the variable? What I have tried is something
> like this which does not work. aNyone know how this can be
> accomplished?
There isn't any CASE statement in SQL. There is a CASE expression, which is
something different.

> declare @.@.var int, @.rtn int
> set @.var = 3
> select case @.var when 1 select @.rtn = catid from categories
> when 2 select @.rtn = itmid from categories
> when 3 select @.rtn = catmid from categorymix
Could write:
SELECT @.rtn = CASE WHEN 1 THEN (SELECT catid FROM categories)
WHEN 2 THEN (SELECT itmid FROM categories)
WHEN 3 THEN (SELECT catmid FROM categorymix)
EMD
Although, I think most people would prefer to use IF/ELSE in this case.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Capturing a variable in ASP.NET

Hi all,

I am new to .NET, after many years with classic ASP I am struggling a little with something that I am sure is really easy to do.

Basically what I want to do, is execute an SQL statement, that will return a single value. I then need to store this value as a variable, so that I can pass it into another query later.

It seems easy to output the record, but how do I store it as a variable !!

This is my code to connect and run the SQL... all I need to do as retreive the value, and put it aganist a variable...

Function higher_manager_ein() As System.Data.IDataReader

Dim connectionString As String = "server='myserver'; user id='userid'; password='pwd'; database='DB'"
Dim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection(connectionString)

Dim queryString As String = "SELECT distinct MEASURE FROM [CCC_MEASURE] where ein = '" & request("man_ein2") & "'"
Dim dbCommand As System.Data.IDbCommand = New System.Data.SqlClient.SqlCommand
dbCommand.CommandText = queryString
dbCommand.Connection = dbConnection

dbConnection.Open
Dim dataReader As System.Data.IDataReader = dbCommand.ExecuteReader(System.Data.CommandBehavior.CloseConnection)

Return dataReader

End FunctionSince it sounds like all you are doing is returning one value, you might want to look at the dbCommand.ExecuteScalar method instead. That is made to return one value.

Now to explain the way you are currently doing it, you can think of the DataReader as an old ADO recordset that you used in ASP. So when this function returns the datareader, you then need to get the value out of the recordset. The firsts difference is that the DataReader object doesn't not start out at the first record. It starts right before it. So you need to call the DataReader.Read() method. That will return true or false depending on if it has read the next record. To get the value, you need to invoke the appropriate method depending on what type of value it is. So DataReader.GetString will return the value as a string. You can pass either the ordinal position of the column in the datareader or the column name.

Since what you are saying above though, I would go with the ExecuteScalar method off of the datareader instead. So again, if this is returning a string value, then you would do the following in place of the Dim dataReader As System.Data.IDataReader command above:

Dim myStringValue As String = CType( dbCommand.ExecuteScalar(), String )

You need to change the type to a string since the ExecuteScalar method returns the type Object.|||and use parameterized queries.

hth

Capture Returned Value From Exec(@Build) into another variable

I am building a SQL statement that returns a number.
when I execute the Built SQL statment EXEC(@.Build). What I need to do
now is take that number that comes back and store it in another
variable so I can do some conditional logic. Any ideas? See SQL below.

Something like @.Count=Exec(@.Build) which I know doesnt work.

Thanks,
Phil

DECLARE @.PullDate varchar(12)

SET @.PullDate=''+CAST(DATEPART(mm,getdate()-31) AS varchar(2))
+'/'+CAST(DATEPART(dd,getdate()-31)AS varchar(2))
+'/'+CAST(DATEPART(yyyy,getdate()-31) AS varchar(4))+''

PRINT(@.PullDate)

DECLARE @.COUNTER BIGINT

DECLARE @.SELECT VARCHAR(500)
DECLARE @.SELECT2 VARCHAR(1000)
DECLARE @.BUILD VARCHAR(5000)

SET @.SELECT='

SELECT COUNTER FROM
OPENQUERY(PROD,'

SET @.SELECT2='''
SELECT
COUNT(WMB.COLLECTOR_RESULTS.ACCT_NUM) AS COUNTER
FROM
COLLECTOR_RESULTS,
WHERE
WMB.COLLECTOR_RESULTS.ACTIVITY_DATE =
to_date(''+@.PullDate+'',''''mm/dd/yyyy'''')
AND WMB.COLLECT_ACCOUNT.END_DATE ) =
to_date(''''12/31/9999'''',''''mm/dd/yyyy'''')
AND WMB.COLLECT_ACCT_SYS_DATA.END_DATE =
to_date(''+@.PullDate+'',''''mm/dd/yyyy'''')
)
GROUP BY
WMB.COLLECTOR_RESULTS.ACTIVITY_DATE '')'

SET @.BUILD=@.SELECT+@.SELECT2
PRINT(@.BUILD)
EXEC(@.BUILD)

--THIS IS WHERE IM UNSURE I NEED THE COUNT RETURNED FROM @.BUILD STORED
INTO @.COUNTER so I can do a conditional statement.)
if @.COUNTER>=1
begin
print('yes')
end<philipdm@.msn.com> wrote in message
news:1107271847.396749.170840@.z14g2000cwz.googlegr oups.com...
>I am building a SQL statement that returns a number.
> when I execute the Built SQL statment EXEC(@.Build). What I need to do
> now is take that number that comes back and store it in another
> variable so I can do some conditional logic. Any ideas? See SQL below.
> Something like @.Count=Exec(@.Build) which I know doesnt work.
> Thanks,
> Phil
>
>
> DECLARE @.PullDate varchar(12)
> SET @.PullDate=''+CAST(DATEPART(mm,getdate()-31) AS varchar(2))
> +'/'+CAST(DATEPART(dd,getdate()-31)AS varchar(2))
> +'/'+CAST(DATEPART(yyyy,getdate()-31) AS varchar(4))+''
> PRINT(@.PullDate)
> DECLARE @.COUNTER BIGINT
> DECLARE @.SELECT VARCHAR(500)
> DECLARE @.SELECT2 VARCHAR(1000)
> DECLARE @.BUILD VARCHAR(5000)
>
> SET @.SELECT='
> SELECT COUNTER FROM
> OPENQUERY(PROD,'
> SET @.SELECT2='''
> SELECT
> COUNT(WMB.COLLECTOR_RESULTS.ACCT_NUM) AS COUNTER
> FROM
> COLLECTOR_RESULTS,
> WHERE
> WMB.COLLECTOR_RESULTS.ACTIVITY_DATE =
> to_date(''+@.PullDate+'',''''mm/dd/yyyy'''')
> AND WMB.COLLECT_ACCOUNT.END_DATE ) =
> to_date(''''12/31/9999'''',''''mm/dd/yyyy'''')
> AND WMB.COLLECT_ACCT_SYS_DATA.END_DATE =
> to_date(''+@.PullDate+'',''''mm/dd/yyyy'''')
> )
> GROUP BY
> WMB.COLLECTOR_RESULTS.ACTIVITY_DATE '')'
>
> SET @.BUILD=@.SELECT+@.SELECT2
> PRINT(@.BUILD)
> EXEC(@.BUILD)
> --THIS IS WHERE IM UNSURE I NEED THE COUNT RETURNED FROM @.BUILD STORED
> INTO @.COUNTER so I can do a conditional statement.)
> if @.COUNTER>=1
> begin
> print('yes')
> end

Instead of EXEC(), you can use sp_executesql with an output parameter:

declare @.sql ntext, @.counter int
set @.sql = 'select @.counter = counter from openquery(...)'
exec sp_executesql @.sql, N'@.counter int', @.counter OUTPUT
select @.counter

See here for an example:

http://www.sommarskog.se/dynamic_sql.html#sp_executesql

Simon|||I am able to get this to return a value but I cant get this to work.
Any ideas?

IF @.Counter>1
Print('Yes')

Simon Hayes wrote:
> <philipdm@.msn.com> wrote in message
> news:1107271847.396749.170840@.z14g2000cwz.googlegr oups.com...
> >I am building a SQL statement that returns a number.
> > when I execute the Built SQL statment EXEC(@.Build). What I need to
do
> > now is take that number that comes back and store it in another
> > variable so I can do some conditional logic. Any ideas? See SQL
below.
> > Something like @.Count=Exec(@.Build) which I know doesnt work.
> > Thanks,
> > Phil
> > DECLARE @.PullDate varchar(12)
> > SET @.PullDate=''+CAST(DATEPART(mm,getdate()-31) AS varchar(2))
> > +'/'+CAST(DATEPART(dd,getdate()-31)AS varchar(2))
> > +'/'+CAST(DATEPART(yyyy,getdate()-31) AS varchar(4))+''
> > PRINT(@.PullDate)
> > DECLARE @.COUNTER BIGINT
> > DECLARE @.SELECT VARCHAR(500)
> > DECLARE @.SELECT2 VARCHAR(1000)
> > DECLARE @.BUILD VARCHAR(5000)
> > SET @.SELECT='
> > SELECT COUNTER FROM
> > OPENQUERY(PROD,'
> > SET @.SELECT2='''
> > SELECT
> > COUNT(WMB.COLLECTOR_RESULTS.ACCT_NUM) AS COUNTER
> > FROM
> > COLLECTOR_RESULTS,
> > WHERE
> > WMB.COLLECTOR_RESULTS.ACTIVITY_DATE =
> > to_date(''+@.PullDate+'',''''mm/dd/yyyy'''')
> > AND WMB.COLLECT_ACCOUNT.END_DATE ) =
> > to_date(''''12/31/9999'''',''''mm/dd/yyyy'''')
> > AND WMB.COLLECT_ACCT_SYS_DATA.END_DATE =
> > to_date(''+@.PullDate+'',''''mm/dd/yyyy'''')
> > )
> > GROUP BY
> > WMB.COLLECTOR_RESULTS.ACTIVITY_DATE '')'
> > SET @.BUILD=@.SELECT+@.SELECT2
> > PRINT(@.BUILD)
> > EXEC(@.BUILD)
> > --THIS IS WHERE IM UNSURE I NEED THE COUNT RETURNED FROM @.BUILD
STORED
> > INTO @.COUNTER so I can do a conditional statement.)
> > if @.COUNTER>=1
> > begin
> > print('yes')
> > end
> Instead of EXEC(), you can use sp_executesql with an output
parameter:
> declare @.sql ntext, @.counter int
> set @.sql = 'select @.counter = counter from openquery(...)'
> exec sp_executesql @.sql, N'@.counter int', @.counter OUTPUT
> select @.counter
> See here for an example:
> http://www.sommarskog.se/dynamic_sql.html#sp_executesql
> Simon|||Never mind I figured it out. I just needed to set a variable =to
outputvariable that can be used in the rest of the code for the
conditional statement.
Thanks a bunch Simon!|||Never mind I figured it out. All I need to do is set a
@.variable=@.Output Variable.
Thanks for your help Simon!
Phil|||Never mind I figured it out. I just needed to set a variable =to
outputvariable that can be used in the rest of the code for the
conditional statement.
Thanks a bunch Simon!|||Never mind I figured it out. I just needed to set a variable =to
outputvariable that can be used in the rest of the code for the
conditional statement.
Thanks a bunch Simon!

Can't you have a variable TOP in a select statement?

Hi,
I got a stored procedure like this
CREATE PROCEDURE dbo.readImport
(
@.Start INTEGER,
@.Number INTEGER
)
AS
SELECT TOP @.Number * FROM Import WHERE RowID >= @.Start ORDER BY RowID
GO
However, it doesn't seem to like having an unknown @.Number.
Any ideas?
MortenHi Morten,
If you are using SQL 2k its not possible.
The only thing is to use dynmiac sql for that.
HTH, Jens Suessmeyer.|||Ok, thanks
Morten
On Fri, 11 Nov 2005 09:30:25 +0100, Jens <Jens@.sqlserver2005.de> wrote:

> Hi Morten,
> If you are using SQL 2k its not possible.
> The only thing is to use dynmiac sql for that.
> HTH, Jens Suessmeyer.
>|||... or SET @.@.ROWCOUNT...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1131697825.522144.101160@.g49g2000cwa.googlegroups.com...
> Hi Morten,
> If you are using SQL 2k its not possible.
> The only thing is to use dynmiac sql for that.
> HTH, Jens Suessmeyer.
>

Can't use variables in SQL-Task

Hi,

I have a problem in my SSIS-package: If I try to execute a SQL-statement which uses a variable, it say "syntax-error". Here is how I tried:

1. a) Declared a variable. Name: tableName; Bereich (Sorry, I use the german version, no clue whats that on english versions): Package2; Type: STRING; Value: staticDB.StaticData_provider.dbo.C0123

b) Assigned this variable on SQL-Task->"Parameterzuordnung" as VARCHAR, parametername= NewParameterName

c) Used this on my SQL-Statement, SQLSourceType is directinput. Statement: "DELETE
FROM @.NewParameterName"

d) Running this results in following error: "Der Parametername wird nicht erkannt" Translation: "Parametername was not recognized."

2. a) see 1.a)

b) see 1.b)

c) Used this on my SQL-Statement, SQLSourceType is directinput. Statement: "DELETE
FROM ?"

d) Running this results in following error: "Syntaxfehler, Berechtigungsversto? oder anderer allgemeiner Fehler" Translation: "Syntaxerror, permision violation or other common error."

Any ideas?

Regards,

Jan

You cannot use a parameter to substitue in the name of a table. They are typically used as arguments in WHERE clause predicates.

If you want to dynamically set the table name then use an expression. This explains how: http://blogs.conchango.com/jamiethomson/archive/2005/12/09/2480.aspx

-Jamie

|||

Thanks a lot for that link, now my package is working fine :-).

Best Regards,

Jan Wagner

Can't use variable against a partitioned view?

I'm using SQL Server 2000. I have two partitioned tables, Result1 and
Result2, and a partitioned view ResultView. This query is correctly
optimized to use Result1:
SELECT COUNT(*) FROM dbo.ResultView
where ModelInterfaceID = 1
This query scans both tables:
DECLARE @.myid int
SET @.myid = 1
SELECT COUNT(*) FROM dbo.ResultView
where ModelInterfaceID = @.myid
This means that I cannot take advantage of partitioning unless all queries
use constants for their predicates?!!! That means the entire application
would have to be build around dynamic SQL instead of simple stored procedure
parameters. Can this be true?
Here are the execution plans:
SELECT COUNT(*) FROM dbo.ResultView
where ModelInterfaceID = 1
StmtText
-----
|--Compute Scalar(DEFINE[Expr1009]=Convert([globalagg1011])))
|--Stream Aggregate(DEFINE[globalagg1011]=SUM([partialagg1010])))
|--Parallelism(Gather Streams)
|--Stream Aggregate(DEFINE[partialagg1010]=Count(*)))
|--Index
Scan(OBJECT[Toggle].[dbo].[Result1].[IX_Result1_TestID]))
DECLARE @.myid int
SET @.myid = 1
SELECT COUNT(*) FROM dbo.ResultView
where ModelInterfaceID = @.myid
StmtText
-------
|--Compute Scalar(DEFINE[Expr1009]=Convert([globalagg1011])))
|--Stream Aggregate(DEFINE[globalagg1011]=SUM([partialagg1010])))
|--Concatenation
|--Parallelism(Gather Streams)
| |--Stream Aggregate(DEFINE[partialagg1010]=Count(*)))
| |--Filter(WHERESTARTUP EXPR([@.myid]=1)))
| |--Index
Seek(OBJECT[Toggle].[dbo].[Result1].[PK_Result1]),
SEEK[Result1].[ModelInterfaceID]=[@.myid]) ORDERED FORWARD)
|--Parallelism(Gather Streams)
|--Stream Aggregate(DEFINE[partialagg1010]=Count(*)))
|--Filter(WHERESTARTUP EXPR([@.myid]=2)))
|--Index
Seek(OBJECT[Toggle].[dbo].[Result2].[PK_Result2]),
SEEK[Result2].[ModelInterfaceID]=[@.myid]) ORDERED FORWARD)
Thanks,
IB
The first execution plan removes the unneeded table reference entirely
because the partition value is known at compile time. However, the second
parameterized query is also efficient. Note the STARTUP EXPR; the
corresponding table is accessed at execution time only if the predicate
(@.myid]=1 or @.myid]=2) is true. Run the query with SET STATISTICS IO ON to
see the actual stats.
Hope this helps.
Dan Guzman
SQL Server MVP
"Itchy Brother" <Itchy Brother@.discussions.microsoft.com> wrote in message
news:8813AC97-916C-4573-9701-5EB74AE1BF71@.microsoft.com...
> I'm using SQL Server 2000. I have two partitioned tables, Result1 and
> Result2, and a partitioned view ResultView. This query is correctly
> optimized to use Result1:
> SELECT COUNT(*) FROM dbo.ResultView
> where ModelInterfaceID = 1
> This query scans both tables:
> DECLARE @.myid int
> SET @.myid = 1
> SELECT COUNT(*) FROM dbo.ResultView
> where ModelInterfaceID = @.myid
> This means that I cannot take advantage of partitioning unless all queries
> use constants for their predicates?!!! That means the entire application
> would have to be build around dynamic SQL instead of simple stored
> procedure
> parameters. Can this be true?
> Here are the execution plans:
> SELECT COUNT(*) FROM dbo.ResultView
> where ModelInterfaceID = 1
> StmtText
> -----
> |--Compute Scalar(DEFINE[Expr1009]=Convert([globalagg1011])))
> |--Stream Aggregate(DEFINE[globalagg1011]=SUM([partialagg1010])))
> |--Parallelism(Gather Streams)
> |--Stream Aggregate(DEFINE[partialagg1010]=Count(*)))
> |--Index
> Scan(OBJECT[Toggle].[dbo].[Result1].[IX_Result1_TestID]))
>
> DECLARE @.myid int
> SET @.myid = 1
> SELECT COUNT(*) FROM dbo.ResultView
> where ModelInterfaceID = @.myid
> StmtText
>
> -------
> |--Compute Scalar(DEFINE[Expr1009]=Convert([globalagg1011])))
> |--Stream Aggregate(DEFINE[globalagg1011]=SUM([partialagg1010])))
> |--Concatenation
> |--Parallelism(Gather Streams)
> | |--Stream
> Aggregate(DEFINE[partialagg1010]=Count(*)))
> | |--Filter(WHERESTARTUP EXPR([@.myid]=1)))
> | |--Index
> Seek(OBJECT[Toggle].[dbo].[Result1].[PK_Result1]),
> SEEK[Result1].[ModelInterfaceID]=[@.myid]) ORDERED FORWARD)
> |--Parallelism(Gather Streams)
> |--Stream
> Aggregate(DEFINE[partialagg1010]=Count(*)))
> |--Filter(WHERESTARTUP EXPR([@.myid]=2)))
> |--Index
> Seek(OBJECT[Toggle].[dbo].[Result2].[PK_Result2]),
> SEEK[Result2].[ModelInterfaceID]=[@.myid]) ORDERED FORWARD)
> Thanks,
> IB
|||Itchy,
The query plan has to work for all possible values of the
variable/parameter, because the query plan is cached and reused.
However, as noted by Dan, if run the query and monitor the table reads,
you will see that the irrelevant partition is not accessed.
Gert-Jan
Itchy Brother wrote:
> I'm using SQL Server 2000. I have two partitioned tables, Result1 and
> Result2, and a partitioned view ResultView. This query is correctly
> optimized to use Result1:
> SELECT COUNT(*) FROM dbo.ResultView
> where ModelInterfaceID = 1
> This query scans both tables:
> DECLARE @.myid int
> SET @.myid = 1
> SELECT COUNT(*) FROM dbo.ResultView
> where ModelInterfaceID = @.myid
> This means that I cannot take advantage of partitioning unless all queries
> use constants for their predicates?!!! That means the entire application
> would have to be build around dynamic SQL instead of simple stored procedure
> parameters. Can this be true?
> Here are the execution plans:
> SELECT COUNT(*) FROM dbo.ResultView
> where ModelInterfaceID = 1
> StmtText
> -----
> |--Compute Scalar(DEFINE[Expr1009]=Convert([globalagg1011])))
> |--Stream Aggregate(DEFINE[globalagg1011]=SUM([partialagg1010])))
> |--Parallelism(Gather Streams)
> |--Stream Aggregate(DEFINE[partialagg1010]=Count(*)))
> |--Index
> Scan(OBJECT[Toggle].[dbo].[Result1].[IX_Result1_TestID]))
> DECLARE @.myid int
> SET @.myid = 1
> SELECT COUNT(*) FROM dbo.ResultView
> where ModelInterfaceID = @.myid
> StmtText
>
> -------
> |--Compute Scalar(DEFINE[Expr1009]=Convert([globalagg1011])))
> |--Stream Aggregate(DEFINE[globalagg1011]=SUM([partialagg1010])))
> |--Concatenation
> |--Parallelism(Gather Streams)
> | |--Stream Aggregate(DEFINE[partialagg1010]=Count(*)))
> | |--Filter(WHERESTARTUP EXPR([@.myid]=1)))
> | |--Index
> Seek(OBJECT[Toggle].[dbo].[Result1].[PK_Result1]),
> SEEK[Result1].[ModelInterfaceID]=[@.myid]) ORDERED FORWARD)
> |--Parallelism(Gather Streams)
> |--Stream Aggregate(DEFINE[partialagg1010]=Count(*)))
> |--Filter(WHERESTARTUP EXPR([@.myid]=2)))
> |--Index
> Seek(OBJECT[Toggle].[dbo].[Result2].[PK_Result2]),
> SEEK[Result2].[ModelInterfaceID]=[@.myid]) ORDERED FORWARD)
> Thanks,
> IB

Can't use variable against a partitioned view?

I'm using SQL Server 2000. I have two partitioned tables, Result1 and
Result2, and a partitioned view ResultView. This query is correctly
optimized to use Result1:
SELECT COUNT(*) FROM dbo.ResultView
where ModelInterfaceID = 1
This query scans both tables:
DECLARE @.myid int
SET @.myid = 1
SELECT COUNT(*) FROM dbo.ResultView
where ModelInterfaceID = @.myid
This means that I cannot take advantage of partitioning unless all queries
use constants for their predicates?!!! That means the entire application
would have to be build around dynamic SQL instead of simple stored procedure
parameters. Can this be true?
Here are the execution plans:
SELECT COUNT(*) FROM dbo.ResultView
where ModelInterfaceID = 1
StmtText
-----
|--Compute Scalar(DEFINE:([Expr1009]=Convert([globalagg1011])))
|--Stream Aggregate(DEFINE:([globalagg1011]=SUM([partialagg1010])))
|--Parallelism(Gather Streams)
|--Stream Aggregate(DEFINE:([partialagg1010]=Count(*)))
|--Index
Scan(OBJECT:([Toggle].[dbo].[Result1].[IX_Result1_TestID]))
DECLARE @.myid int
SET @.myid = 1
SELECT COUNT(*) FROM dbo.ResultView
where ModelInterfaceID = @.myid
StmtText
-------
|--Compute Scalar(DEFINE:([Expr1009]=Convert([globalagg1011])))
|--Stream Aggregate(DEFINE:([globalagg1011]=SUM([partialagg1010])))
|--Concatenation
|--Parallelism(Gather Streams)
| |--Stream Aggregate(DEFINE:([partialagg1010]=Count(*)))
| |--Filter(WHERE:(STARTUP EXPR([@.myid]=1)))
| |--Index
Seek(OBJECT:([Toggle].[dbo].[Result1].[PK_Result1]),
SEEK:([Result1].[ModelInterfaceID]=[@.myid]) ORDERED FORWARD)
|--Parallelism(Gather Streams)
|--Stream Aggregate(DEFINE:([partialagg1010]=Count(*)))
|--Filter(WHERE:(STARTUP EXPR([@.myid]=2)))
|--Index
Seek(OBJECT:([Toggle].[dbo].[Result2].[PK_Result2]),
SEEK:([Result2].[ModelInterfaceID]=[@.myid]) ORDERED FORWARD)
Thanks,
IBThe first execution plan removes the unneeded table reference entirely
because the partition value is known at compile time. However, the second
parameterized query is also efficient. Note the STARTUP EXPR; the
corresponding table is accessed at execution time only if the predicate
(@.myid]=1 or @.myid]=2) is true. Run the query with SET STATISTICS IO ON to
see the actual stats.
Hope this helps.
Dan Guzman
SQL Server MVP
"Itchy Brother" <Itchy Brother@.discussions.microsoft.com> wrote in message
news:8813AC97-916C-4573-9701-5EB74AE1BF71@.microsoft.com...
> I'm using SQL Server 2000. I have two partitioned tables, Result1 and
> Result2, and a partitioned view ResultView. This query is correctly
> optimized to use Result1:
> SELECT COUNT(*) FROM dbo.ResultView
> where ModelInterfaceID = 1
> This query scans both tables:
> DECLARE @.myid int
> SET @.myid = 1
> SELECT COUNT(*) FROM dbo.ResultView
> where ModelInterfaceID = @.myid
> This means that I cannot take advantage of partitioning unless all queries
> use constants for their predicates?!!! That means the entire application
> would have to be build around dynamic SQL instead of simple stored
> procedure
> parameters. Can this be true?
> Here are the execution plans:
> SELECT COUNT(*) FROM dbo.ResultView
> where ModelInterfaceID = 1
> StmtText
> -----
> |--Compute Scalar(DEFINE:([Expr1009]=Convert([globalagg1011])))
> |--Stream Aggregate(DEFINE:([globalagg1011]=SUM([partialagg1010])))
> |--Parallelism(Gather Streams)
> |--Stream Aggregate(DEFINE:([partialagg1010]=Count(*)))
> |--Index
> Scan(OBJECT:([Toggle].[dbo].[Result1].[IX_Result1_TestID]))
>
> DECLARE @.myid int
> SET @.myid = 1
> SELECT COUNT(*) FROM dbo.ResultView
> where ModelInterfaceID = @.myid
> StmtText
>
> -------
> |--Compute Scalar(DEFINE:([Expr1009]=Convert([globalagg1011])))
> |--Stream Aggregate(DEFINE:([globalagg1011]=SUM([partialagg1010])))
> |--Concatenation
> |--Parallelism(Gather Streams)
> | |--Stream
> Aggregate(DEFINE:([partialagg1010]=Count(*)))
> | |--Filter(WHERE:(STARTUP EXPR([@.myid]=1)))
> | |--Index
> Seek(OBJECT:([Toggle].[dbo].[Result1].[PK_Result1]),
> SEEK:([Result1].[ModelInterfaceID]=[@.myid]) ORDERED FORWARD)
> |--Parallelism(Gather Streams)
> |--Stream
> Aggregate(DEFINE:([partialagg1010]=Count(*)))
> |--Filter(WHERE:(STARTUP EXPR([@.myid]=2)))
> |--Index
> Seek(OBJECT:([Toggle].[dbo].[Result2].[PK_Result2]),
> SEEK:([Result2].[ModelInterfaceID]=[@.myid]) ORDERED FORWARD)
> Thanks,
> IB|||Itchy,
The query plan has to work for all possible values of the
variable/parameter, because the query plan is cached and reused.
However, as noted by Dan, if run the query and monitor the table reads,
you will see that the irrelevant partition is not accessed.
Gert-Jan
Itchy Brother wrote:
> I'm using SQL Server 2000. I have two partitioned tables, Result1 and
> Result2, and a partitioned view ResultView. This query is correctly
> optimized to use Result1:
> SELECT COUNT(*) FROM dbo.ResultView
> where ModelInterfaceID = 1
> This query scans both tables:
> DECLARE @.myid int
> SET @.myid = 1
> SELECT COUNT(*) FROM dbo.ResultView
> where ModelInterfaceID = @.myid
> This means that I cannot take advantage of partitioning unless all queries
> use constants for their predicates?!!! That means the entire application
> would have to be build around dynamic SQL instead of simple stored procedure
> parameters. Can this be true?
> Here are the execution plans:
> SELECT COUNT(*) FROM dbo.ResultView
> where ModelInterfaceID = 1
> StmtText
> -----
> |--Compute Scalar(DEFINE:([Expr1009]=Convert([globalagg1011])))
> |--Stream Aggregate(DEFINE:([globalagg1011]=SUM([partialagg1010])))
> |--Parallelism(Gather Streams)
> |--Stream Aggregate(DEFINE:([partialagg1010]=Count(*)))
> |--Index
> Scan(OBJECT:([Toggle].[dbo].[Result1].[IX_Result1_TestID]))
> DECLARE @.myid int
> SET @.myid = 1
> SELECT COUNT(*) FROM dbo.ResultView
> where ModelInterfaceID = @.myid
> StmtText
>
> -------
> |--Compute Scalar(DEFINE:([Expr1009]=Convert([globalagg1011])))
> |--Stream Aggregate(DEFINE:([globalagg1011]=SUM([partialagg1010])))
> |--Concatenation
> |--Parallelism(Gather Streams)
> | |--Stream Aggregate(DEFINE:([partialagg1010]=Count(*)))
> | |--Filter(WHERE:(STARTUP EXPR([@.myid]=1)))
> | |--Index
> Seek(OBJECT:([Toggle].[dbo].[Result1].[PK_Result1]),
> SEEK:([Result1].[ModelInterfaceID]=[@.myid]) ORDERED FORWARD)
> |--Parallelism(Gather Streams)
> |--Stream Aggregate(DEFINE:([partialagg1010]=Count(*)))
> |--Filter(WHERE:(STARTUP EXPR([@.myid]=2)))
> |--Index
> Seek(OBJECT:([Toggle].[dbo].[Result2].[PK_Result2]),
> SEEK:([Result2].[ModelInterfaceID]=[@.myid]) ORDERED FORWARD)
> Thanks,
> IB

Can't use variable against a partitioned view?

I'm using SQL Server 2000. I have two partitioned tables, Result1 and
Result2, and a partitioned view ResultView. This query is correctly
optimized to use Result1:
SELECT COUNT(*) FROM dbo.ResultView
where ModelInterfaceID = 1
This query scans both tables:
DECLARE @.myid int
SET @.myid = 1
SELECT COUNT(*) FROM dbo.ResultView
where ModelInterfaceID = @.myid
This means that I cannot take advantage of partitioning unless all queries
use constants for their predicates?!!! That means the entire application
would have to be build around dynamic SQL instead of simple stored procedure
parameters. Can this be true?
Here are the execution plans:
SELECT COUNT(*) FROM dbo.ResultView
where ModelInterfaceID = 1
StmtText
----
--
|--Compute Scalar(DEFINE[Expr1009]=Convert([globalagg1011])))
|--Stream Aggregate(DEFINE[globalagg1011]=SUM([partialagg1010])))
|--Parallelism(Gather Streams)
|--Stream Aggregate(DEFINE[partialagg1010]=Count(*)))
|--Index
Scan(OBJECT[Toggle].[dbo].[Result1].[IX_Result1_TestID]))
DECLARE @.myid int
SET @.myid = 1
SELECT COUNT(*) FROM dbo.ResultView
where ModelInterfaceID = @.myid
StmtText
----
----
--
|--Compute Scalar(DEFINE[Expr1009]=Convert([globalagg1011])))
|--Stream Aggregate(DEFINE[globalagg1011]=SUM([partialagg1010])))
|--Concatenation
|--Parallelism(Gather Streams)
| |--Stream Aggregate(DEFINE[partialagg1010]=Count(*)))
| |--Filter(WHERESTARTUP EXPR([@.myid]=1)))
| |--Index
Seek(OBJECT[Toggle].[dbo].[Result1].[PK_Result1]),
SEEK[Result1].[ModelInterfaceID]=[@.myid]) ORDERED FORWARD)
|--Parallelism(Gather Streams)
|--Stream Aggregate(DEFINE[partialagg1010]=Count(*)))
|--Filter(WHERESTARTUP EXPR([@.myid]=2)))
|--Index
Seek(OBJECT[Toggle].[dbo].[Result2].[PK_Result2]),
SEEK[Result2].[ModelInterfaceID]=[@.myid]) ORDERED FORWARD)
Thanks,
IBThe first execution plan removes the unneeded table reference entirely
because the partition value is known at compile time. However, the second
parameterized query is also efficient. Note the STARTUP EXPR; the
corresponding table is accessed at execution time only if the predicate
(@.myid]=1 or @.myid]=2) is true. Run the query with SET STATISTICS IO ON to
see the actual stats.
Hope this helps.
Dan Guzman
SQL Server MVP
"Itchy Brother" <Itchy Brother@.discussions.microsoft.com> wrote in message
news:8813AC97-916C-4573-9701-5EB74AE1BF71@.microsoft.com...
> I'm using SQL Server 2000. I have two partitioned tables, Result1 and
> Result2, and a partitioned view ResultView. This query is correctly
> optimized to use Result1:
> SELECT COUNT(*) FROM dbo.ResultView
> where ModelInterfaceID = 1
> This query scans both tables:
> DECLARE @.myid int
> SET @.myid = 1
> SELECT COUNT(*) FROM dbo.ResultView
> where ModelInterfaceID = @.myid
> This means that I cannot take advantage of partitioning unless all queries
> use constants for their predicates?!!! That means the entire application
> would have to be build around dynamic SQL instead of simple stored
> procedure
> parameters. Can this be true?
> Here are the execution plans:
> SELECT COUNT(*) FROM dbo.ResultView
> where ModelInterfaceID = 1
> StmtText
> ----
--
> |--Compute Scalar(DEFINE[Expr1009]=Convert([globalagg1011])))
> |--Stream Aggregate(DEFINE[globalagg1011]=SUM([partialagg1
010])))
> |--Parallelism(Gather Streams)
> |--Stream Aggregate(DEFINE[partialagg1010]=Count(*))
)
> |--Index
> Scan(OBJECT[Toggle].[dbo].[Result1].[IX_Result1_TestID])
)
>
> DECLARE @.myid int
> SET @.myid = 1
> SELECT COUNT(*) FROM dbo.ResultView
> where ModelInterfaceID = @.myid
> StmtText
>
> ----
----
--
> |--Compute Scalar(DEFINE[Expr1009]=Convert([globalagg1011])))
> |--Stream Aggregate(DEFINE[globalagg1011]=SUM([partialagg1
010])))
> |--Concatenation
> |--Parallelism(Gather Streams)
> | |--Stream
> Aggregate(DEFINE[partialagg1010]=Count(*)))
> | |--Filter(WHERESTARTUP EXPR([@.myid]=1)))
> | |--Index
> Seek(OBJECT[Toggle].[dbo].[Result1].[PK_Result1]),
> SEEK[Result1].[ModelInterfaceID]=[@.myid]) ORDERED FORWARD)
> |--Parallelism(Gather Streams)
> |--Stream
> Aggregate(DEFINE[partialagg1010]=Count(*)))
> |--Filter(WHERESTARTUP EXPR([@.myid]=2)))
> |--Index
> Seek(OBJECT[Toggle].[dbo].[Result2].[PK_Result2]),
> SEEK[Result2].[ModelInterfaceID]=[@.myid]) ORDERED FORWARD)
> Thanks,
> IB|||Itchy,
The query plan has to work for all possible values of the
variable/parameter, because the query plan is cached and reused.
However, as noted by Dan, if run the query and monitor the table reads,
you will see that the irrelevant partition is not accessed.
Gert-Jan
Itchy Brother wrote:
> I'm using SQL Server 2000. I have two partitioned tables, Result1 and
> Result2, and a partitioned view ResultView. This query is correctly
> optimized to use Result1:
> SELECT COUNT(*) FROM dbo.ResultView
> where ModelInterfaceID = 1
> This query scans both tables:
> DECLARE @.myid int
> SET @.myid = 1
> SELECT COUNT(*) FROM dbo.ResultView
> where ModelInterfaceID = @.myid
> This means that I cannot take advantage of partitioning unless all queries
> use constants for their predicates?!!! That means the entire application
> would have to be build around dynamic SQL instead of simple stored procedu
re
> parameters. Can this be true?
> Here are the execution plans:
> SELECT COUNT(*) FROM dbo.ResultView
> where ModelInterfaceID = 1
> StmtText
> ----
--
> |--Compute Scalar(DEFINE[Expr1009]=Convert([globalagg1011])))
> |--Stream Aggregate(DEFINE[globalagg1011]=SUM([partialagg
1010])))
> |--Parallelism(Gather Streams)
> |--Stream Aggregate(DEFINE[partialagg1010]=Count(*)
))
> |--Index
> Scan(OBJECT[Toggle].[dbo].[Result1].[IX_Result1_TestID])
)
> DECLARE @.myid int
> SET @.myid = 1
> SELECT COUNT(*) FROM dbo.ResultView
> where ModelInterfaceID = @.myid
> StmtText
>
> ----
----
--
> |--Compute Scalar(DEFINE[Expr1009]=Convert([globalagg1011])))
> |--Stream Aggregate(DEFINE[globalagg1011]=SUM([partialagg
1010])))
> |--Concatenation
> |--Parallelism(Gather Streams)
> | |--Stream Aggregate(DEFINE[partialagg1010]=Cou
nt(*)))
> | |--Filter(WHERESTARTUP EXPR([@.myid]=1)))
> | |--Index
> Seek(OBJECT[Toggle].[dbo].[Result1].[PK_Result1]),
> SEEK[Result1].[ModelInterfaceID]=[@.myid]) ORDERED FORWARD)
> |--Parallelism(Gather Streams)
> |--Stream Aggregate(DEFINE[partialagg1010]=Cou
nt(*)))
> |--Filter(WHERESTARTUP EXPR([@.myid]=2)))
> |--Index
> Seek(OBJECT[Toggle].[dbo].[Result2].[PK_Result2]),
> SEEK[Result2].[ModelInterfaceID]=[@.myid]) ORDERED FORWARD)
> Thanks,
> IB