Question Regarding Views and Indexes

If I have a table defined as follows:
TABLE Customer_Info
Customer_ID Int NOT NULL,
Country nvarchar(225) NOT NULL,
State_Province nvarchar(225) NULL,
Customer_Name nvarchar(225) NULL
The tables primary key and unique index is on Customer_ID
There is also an index on the Country column and a separate index on
the State_Province column.
And then I have a view names USA_Customers ( a standard view, NOT an
indexed view) defines as follows:
SELECT Customer_ID, County, State_Province, Customer_Name
FROM dbo.Customer_Info
WHERE (Country = 'USA')
And I run the following query
Select * from USA_Customers
Where State_Province = 'PA'
Will this query use the index on the State_Province column of the base
table or not when the query is executed? Regardless of the answer, if
there is any documentation that you could point me to that explains
when an index will / will not be used, I would greatly appreciate it.
Please assume there is enough data in the table and enough cardinality
in the indexes that would be desirable to use the indexes.
Thanks
George<GCeaser@.aol.com> wrote in message
news:1151511288.326267.307760@.i40g2000cwc.googlegroups.com...
> If I have a table defined as follows:
> TABLE Customer_Info
> Customer_ID Int NOT NULL,
> Country nvarchar(225) NOT NULL,
> State_Province nvarchar(225) NULL,
> Customer_Name nvarchar(225) NULL
> The tables primary key and unique index is on Customer_ID
> There is also an index on the Country column and a separate index on
> the State_Province column.
> And then I have a view names USA_Customers ( a standard view, NOT an
> indexed view) defines as follows:
> SELECT Customer_ID, County, State_Province, Customer_Name
> FROM dbo.Customer_Info
> WHERE (Country = 'USA')
>
> And I run the following query
> Select * from USA_Customers
> Where State_Province = 'PA'
> Will this query use the index on the State_Province column of the base
> table or not when the query is executed? Regardless of the answer, if
> there is any documentation that you could point me to that explains
> when an index will / will not be used, I would greatly appreciate it.
> Please assume there is enough data in the table and enough cardinality
> in the indexes that would be desirable to use the indexes.
>
The important thing to know here is that views are expanded into the query
plan before optimization. So this query should behave exactly like
SELECT Customer_ID, County, State_Province, Customer_Name
FROM dbo.Customer_Info
WHERE Country = 'USA'
AND State_Province = 'PA'
SQL Server will consider usiing either, both or neither index, and then
should either use State index + Lookups, the Country Index + Lookups, Index
intersection between the two + Lookups, or do a table scan.
David|||<GCeaser@.aol.com> wrote in message
news:1151511288.326267.307760@.i40g2000cwc.googlegroups.com...
> If I have a table defined as follows:
> TABLE Customer_Info
> Customer_ID Int NOT NULL,
> Country nvarchar(225) NOT NULL,
> State_Province nvarchar(225) NULL,
> Customer_Name nvarchar(225) NULL
> The tables primary key and unique index is on Customer_ID
> There is also an index on the Country column and a separate index on
> the State_Province column.
> And then I have a view names USA_Customers ( a standard view, NOT an
> indexed view) defines as follows:
> SELECT Customer_ID, County, State_Province, Customer_Name
> FROM dbo.Customer_Info
> WHERE (Country = 'USA')
>
> And I run the following query
> Select * from USA_Customers
> Where State_Province = 'PA'
> Will this query use the index on the State_Province column of the base
> table or not when the query is executed? Regardless of the answer, if
> there is any documentation that you could point me to that explains
> when an index will / will not be used, I would greatly appreciate it.
> Please assume there is enough data in the table and enough cardinality
> in the indexes that would be desirable to use the indexes.
>
The important thing to know here is that views are expanded into the query
plan before optimization. So this query should behave exactly like
SELECT Customer_ID, County, State_Province, Customer_Name
FROM dbo.Customer_Info
WHERE Country = 'USA'
AND State_Province = 'PA'
SQL Server will consider usiing either, both or neither index, and then
should either use State index + Lookups, the Country Index + Lookups, Index
intersection between the two + Lookups, or do a table scan.
David|||GCeaser@.aol.com wrote:
> If I have a table defined as follows:
> TABLE Customer_Info
> Customer_ID Int NOT NULL,
> Country nvarchar(225) NOT NULL,
> State_Province nvarchar(225) NULL,
> Customer_Name nvarchar(225) NULL
> The tables primary key and unique index is on Customer_ID
> There is also an index on the Country column and a separate index on
> the State_Province column.
> And then I have a view names USA_Customers ( a standard view, NOT an
> indexed view) defines as follows:
> SELECT Customer_ID, County, State_Province, Customer_Name
> FROM dbo.Customer_Info
> WHERE (Country = 'USA')
>
> And I run the following query
> Select * from USA_Customers
> Where State_Province = 'PA'
> Will this query use the index on the State_Province column of the base
> table or not when the query is executed? Regardless of the answer, if
> there is any documentation that you could point me to that explains
> when an index will / will not be used, I would greatly appreciate it.
> Please assume there is enough data in the table and enough cardinality
> in the indexes that would be desirable to use the indexes.
> Thanks
> George
>
It's *probably* going to use the index on the Country column,
accompanied by a bookmark lookup to get the other fields. It depends on
several things - how much data is in the table, how the view is being
queried (directly as your example or as part of a join). The best way
to confirm is to look at the execution plan of your query.|||GCeaser@.aol.com wrote:
> If I have a table defined as follows:
> TABLE Customer_Info
> Customer_ID Int NOT NULL,
> Country nvarchar(225) NOT NULL,
> State_Province nvarchar(225) NULL,
> Customer_Name nvarchar(225) NULL
> The tables primary key and unique index is on Customer_ID
> There is also an index on the Country column and a separate index on
> the State_Province column.
> And then I have a view names USA_Customers ( a standard view, NOT an
> indexed view) defines as follows:
> SELECT Customer_ID, County, State_Province, Customer_Name
> FROM dbo.Customer_Info
> WHERE (Country = 'USA')
>
> And I run the following query
> Select * from USA_Customers
> Where State_Province = 'PA'
> Will this query use the index on the State_Province column of the base
> table or not when the query is executed? Regardless of the answer, if
> there is any documentation that you could point me to that explains
> when an index will / will not be used, I would greatly appreciate it.
> Please assume there is enough data in the table and enough cardinality
> in the indexes that would be desirable to use the indexes.
> Thanks
> George
>
It's *probably* going to use the index on the Country column,
accompanied by a bookmark lookup to get the other fields. It depends on
several things - how much data is in the table, how the view is being
queried (directly as your example or as part of a join). The best way
to confirm is to look at the execution plan of your query.

Question Regarding Views and Indexes

If I have a table defined as follows:
TABLE Customer_Info
Customer_ID Int NOT NULL,
Country nvarchar(225) NOT NULL,
State_Province nvarchar(225) NULL,
Customer_Name nvarchar(225) NULL
The tables primary key and unique index is on Customer_ID
There is also an index on the Country column and a separate index on
the State_Province column.
And then I have a view names USA_Customers ( a standard view, NOT an
indexed view) defines as follows:
SELECT Customer_ID, County, State_Province, Customer_Name
FROM dbo.Customer_Info
WHERE (Country = 'USA')
And I run the following query
Select * from USA_Customers
Where State_Province = 'PA'
Will this query use the index on the State_Province column of the base
table or not when the query is executed? Regardless of the answer, if
there is any documentation that you could point me to that explains
when an index will / will not be used, I would greatly appreciate it.
Please assume there is enough data in the table and enough cardinality
in the indexes that would be desirable to use the indexes.
Thanks
George<GCeaser@.aol.com> wrote in message
news:1151511288.326267.307760@.i40g2000cwc.googlegroups.com...
> If I have a table defined as follows:
> TABLE Customer_Info
> Customer_ID Int NOT NULL,
> Country nvarchar(225) NOT NULL,
> State_Province nvarchar(225) NULL,
> Customer_Name nvarchar(225) NULL
> The tables primary key and unique index is on Customer_ID
> There is also an index on the Country column and a separate index on
> the State_Province column.
> And then I have a view names USA_Customers ( a standard view, NOT an
> indexed view) defines as follows:
> SELECT Customer_ID, County, State_Province, Customer_Name
> FROM dbo.Customer_Info
> WHERE (Country = 'USA')
>
> And I run the following query
> Select * from USA_Customers
> Where State_Province = 'PA'
> Will this query use the index on the State_Province column of the base
> table or not when the query is executed? Regardless of the answer, if
> there is any documentation that you could point me to that explains
> when an index will / will not be used, I would greatly appreciate it.
> Please assume there is enough data in the table and enough cardinality
> in the indexes that would be desirable to use the indexes.
>
The important thing to know here is that views are expanded into the query
plan before optimization. So this query should behave exactly like
SELECT Customer_ID, County, State_Province, Customer_Name
FROM dbo.Customer_Info
WHERE Country = 'USA'
AND State_Province = 'PA'
SQL Server will consider usiing either, both or neither index, and then
should either use State index + Lookups, the Country Index + Lookups, Index
intersection between the two + Lookups, or do a table scan.
David|||GCeaser@.aol.com wrote:
> If I have a table defined as follows:
> TABLE Customer_Info
> Customer_ID Int NOT NULL,
> Country nvarchar(225) NOT NULL,
> State_Province nvarchar(225) NULL,
> Customer_Name nvarchar(225) NULL
> The tables primary key and unique index is on Customer_ID
> There is also an index on the Country column and a separate index on
> the State_Province column.
> And then I have a view names USA_Customers ( a standard view, NOT an
> indexed view) defines as follows:
> SELECT Customer_ID, County, State_Province, Customer_Name
> FROM dbo.Customer_Info
> WHERE (Country = 'USA')
>
> And I run the following query
> Select * from USA_Customers
> Where State_Province = 'PA'
> Will this query use the index on the State_Province column of the base
> table or not when the query is executed? Regardless of the answer, if
> there is any documentation that you could point me to that explains
> when an index will / will not be used, I would greatly appreciate it.
> Please assume there is enough data in the table and enough cardinality
> in the indexes that would be desirable to use the indexes.
> Thanks
> George
>
It's *probably* going to use the index on the Country column,
accompanied by a bookmark lookup to get the other fields. It depends on
several things - how much data is in the table, how the view is being
queried (directly as your example or as part of a join). The best way
to confirm is to look at the execution plan of your query.

Question on, usage of NOT IN

Hi All,
The following string comparision fails:
declare @.prodCode as nvarchar(50)
select @.prodCode = 'ABZC001'
if @.prodCode NOT IN ( 'ABZC001,CBQA03,FG0023')
BEGIN
...
...
END
The code within the BEGIN...END block is executed!
Any suggestions?
Thanks
kdIN / NOT IN takes a subquery or expression list as an argument. It won't
parse a string for you. I assume this is what you intend:
IF @.prodCode NOT IN ('ABZC001','CBQA03','FG0023')
If you want to search for substrings, use CHARINDEX or PATINDEX.
David Portas
SQL Server MVP
--

question on a stored procedure...

Hi all,
I have a basic question with a stored procedure I'm using. The procedure is as follows:
CREATE PROCEDURE CheckUserLogin(@.Login nvarchar(50), @.Password nvarchar(50)) AS
DECLARE @.UserId varchar(50)
DECLARE @.SchoolId int
DECLARE @.title varchar(50)

IF EXISTS(SELECT Login, Pass FROM Users WHERELogin=@.Login and Pass=@.Password)
BEGIN
SELECT UserId, SchoolId, title FROM Users WHERELogin=@.Login andPass=@.Password
END
ELSE
BEGIN
SELECT @.UserId ='InvalidLogin', @.SchoolId = 0, @.title = 'Applicant'
END
GO
--*******************************************************************--
The thing is that when I read the results, the Else part from the procedure doesn't return anything, so I'm assuming how I've declared the variables or the way I'm selecting them is wrong because if the password and login are correct (this is the first if statement), then when I read the results, I get to where I'm supposed to; however, if the Login and password are incorrect, then I'm supposed to return the Else part...but it doesn't return anything
I have the following when trying to check the userLogin and password

Dim resultAs SqlDataReader

result = CheckUserLoginCmd.ExecuteReader

While result.Read..
...
but I never go into the While loop if the login and password are incorrect, but I do go into the while loop if login and password are correct, and I get the expected behavior. Thanks for your help,

You'd have to use OUTPUT parameters to retrieve that data you are capturing in those @.variables. Try it like this instead:
CREATE PROCEDURE CheckUserLogin(@.Login nvarchar(50), @.Password nvarchar(50)) AS
IF EXISTS(SELECT Login, Pass FROM Users WHERELogin=@.Login and Pass=@.Password)
BEGIN
SELECT UserId, SchoolId, title FROM Users WHERELogin=@.Login andPass=@.Password
END
ELSE
BEGIN
SELECT UserId ='InvalidLogin', SchoolId = 0, title = 'Applicant'
END
GO
|||

Hi,

Thanks for your reply. I actually already tried it like that, but it gave me an error:

Input string was not in a correct format.

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details:System.FormatException: Input string was not in a correct format.
Source Error:

Line 130: With userLine 131: If Not IsDBNull(result("UserId")) ThenLine 132: .userId = CType(result("UserId"), String)Line 133: End IfLine 134: If Not IsDBNull(result("SchoolId")) Then

I thought this was because of the way I was returning the result, but maybe the error is something else?...If you have any idea, please let me know. Thanks again.