Indexing question

I have a basic rudimentary question concerning creating indexes.
Assuming a table :
FieldA varchar(1)
FieldB varchar(254)
FieldC varchar(25)
FieldD int (identity)
Now assuming we want to query
Select * from table1 where fieldb like '%b%' and fieldA is null
Should I create an index on FieldA and a separate index on FieldB
OR
create an index with FieldA AND FieldB
Basically do I create several individual indexes or create one index for
each type of query (as I may run several different kinds on the same table)
How does SQLServer know to use which index..'
Sorry for the newbieness...
Thanks,
-CraigCraig,
First of all, the only advantage of including fieldB in an index is
if you can thereby have a covering index either for the full WHERE
clause or for the SELECT list. Since you have SELECT *, and the table
has columns besides fieldA and fieldB, the entire table will have to be
accessed in any case. I would suggest these three as the most
reasonable possibilities:
Clustered index on (FieldA) or (FieldA, other columns)
This will always help unless (fieldA is null) is true for most of the
table, but since you can have only one clustered index on the table, it
only makes sense if no other clustered index is more compelling.
or
Nonclustered index on (fieldA, fieldB)
This can only help if the number of rows for which (fieldA is null) and
(fieldB like '%b%') is relatively small - definitely it would have to be
less than the number of data pages in the entire table, which could mean
between about 1-4% of the rows of the table or fewer.
or
Nonclustered index on (fieldA, fieldB, fieldC, fieldD)
This can help unless (fieldA is null) is true for most of the rows, and
allows a clustered index on some other column(s).
Other considerations include the activity on the table. For example, if
fieldC or fieldD is frequently updated, any index including those
columns will result in extra work from the updates.
There are no simple answers - the index tuning wizard may help you out,
and you could look through some books, such as Ken Henderson's Guru's
Guide to Transact-SQL or Kalen Delaney's Inside SQL Server 2000.
SK
Craig Stadler wrote:

quote:

---

>I have a basic rudimentary question concerning creating indexes.
>Assuming a table :
>FieldA varchar(1)
>FieldB varchar(254)
>FieldC varchar(25)
>FieldD int (identity)
>Now assuming we want to query
>Select * from table1 where fieldb like '%b%' and fieldA is null
>Should I create an index on FieldA and a separate index on FieldB
>OR
>create an index with FieldA AND FieldB
>Basically do I create several individual indexes or create one index for
>each type of query (as I may run several different kinds on the same table)
>How does SQLServer know to use which index..'
>Sorry for the newbieness...
>Thanks,
>-Craig
>
>

---

|||Craig,
Creating a clustered index on FieldA (or FieldA and some other
column(s)) is about the only way to slightly improve performance for
this query. This is because:
- the predicate FieldB LIKE '%b%' cannot use partial index scan or index
seek
- any index on FieldB will be almost as big as the table itself
A nonclustered index on FieldA could be an option if there are very few
rows where FieldA IS NULL (let's say, less than 3% of all rows).
Hope this helps,
Gert-Jan
Craig Stadler wrote:

quote:

---

> I have a basic rudimentary question concerning creating indexes.
> Assuming a table :
> FieldA varchar(1)
> FieldB varchar(254)
> FieldC varchar(25)
> FieldD int (identity)
> Now assuming we want to query
> Select * from table1 where fieldb like '%b%' and fieldA is null
> Should I create an index on FieldA and a separate index on FieldB
> OR
> create an index with FieldA AND FieldB
> Basically do I create several individual indexes or create one index for
> each type of query (as I may run several different kinds on the same table
)
> How does SQLServer know to use which index..'
> Sorry for the newbieness...
> Thanks,
> -Craig

---

sql

Indexing question

I have a basic rudimentary question concerning creating indexes.
Assuming a table :
FieldA varchar(1)
FieldB varchar(254)
FieldC varchar(25)
FieldD int (identity)
Now assuming we want to query
Select * from table1 where fieldb like '%b%' and fieldA is null
Should I create an index on FieldA and a separate index on FieldB
OR
create an index with FieldA AND FieldB
Basically do I create several individual indexes or create one index for
each type of query (as I may run several different kinds on the same table)
How does SQLServer know to use which index..'
Sorry for the newbieness...
Thanks,
-CraigCraig,
First of all, the only advantage of including fieldB in an index is
if you can thereby have a covering index either for the full WHERE
clause or for the SELECT list. Since you have SELECT *, and the table
has columns besides fieldA and fieldB, the entire table will have to be
accessed in any case. I would suggest these three as the most
reasonable possibilities:
Clustered index on (FieldA) or (FieldA, other columns)
This will always help unless (fieldA is null) is true for most of the
table, but since you can have only one clustered index on the table, it
only makes sense if no other clustered index is more compelling.
or
Nonclustered index on (fieldA, fieldB)
This can only help if the number of rows for which (fieldA is null) and
(fieldB like '%b%') is relatively small - definitely it would have to be
less than the number of data pages in the entire table, which could mean
between about 1-4% of the rows of the table or fewer.
or
Nonclustered index on (fieldA, fieldB, fieldC, fieldD)
This can help unless (fieldA is null) is true for most of the rows, and
allows a clustered index on some other column(s).
Other considerations include the activity on the table. For example, if
fieldC or fieldD is frequently updated, any index including those
columns will result in extra work from the updates.
There are no simple answers - the index tuning wizard may help you out,
and you could look through some books, such as Ken Henderson's Guru's
Guide to Transact-SQL or Kalen Delaney's Inside SQL Server 2000.
SK
Craig Stadler wrote:
>I have a basic rudimentary question concerning creating indexes.
>Assuming a table :
>FieldA varchar(1)
>FieldB varchar(254)
>FieldC varchar(25)
>FieldD int (identity)
>Now assuming we want to query
>Select * from table1 where fieldb like '%b%' and fieldA is null
>Should I create an index on FieldA and a separate index on FieldB
>OR
>create an index with FieldA AND FieldB
>Basically do I create several individual indexes or create one index for
>each type of query (as I may run several different kinds on the same table)
>How does SQLServer know to use which index..'
>Sorry for the newbieness...
>Thanks,
>-Craig
>
>|||Craig,
Creating a clustered index on FieldA (or FieldA and some other
column(s)) is about the only way to slightly improve performance for
this query. This is because:
- the predicate FieldB LIKE '%b%' cannot use partial index scan or index
seek
- any index on FieldB will be almost as big as the table itself
A nonclustered index on FieldA could be an option if there are very few
rows where FieldA IS NULL (let's say, less than 3% of all rows).
Hope this helps,
Gert-Jan
Craig Stadler wrote:
> I have a basic rudimentary question concerning creating indexes.
> Assuming a table :
> FieldA varchar(1)
> FieldB varchar(254)
> FieldC varchar(25)
> FieldD int (identity)
> Now assuming we want to query
> Select * from table1 where fieldb like '%b%' and fieldA is null
> Should I create an index on FieldA and a separate index on FieldB
> OR
> create an index with FieldA AND FieldB
> Basically do I create several individual indexes or create one index for
> each type of query (as I may run several different kinds on the same table)
> How does SQLServer know to use which index..'
> Sorry for the newbieness...
> Thanks,
> -Craig

Indexing a table

I've created a new table and added data to it. Now I want to index a given column. Will simply creating and saving the index index the column or do I need to do something else to create the actual index?

Barkingdog

you must run a create index tsql statement to actially create an index

however you can use the enterprise manager or the sql server management studio

the create the index using the GUI

|||

Once the index is created it will be maintained by itself, you don′t need to refresh the index. ON the other side, index can get fragmented leading to throttle your response time on the server.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

Indexing a table

I've created a new table and added data to it. Now I want to index a given column. Will simply creating and saving the index index the column or do I need to do something else to create the actual index?

Barkingdog

Basically all you have to do is expand the table right click Indexes select New Index Select your type clustered or non-clustered and add the index key column field. There are many more option depending on what your needs are. How this helped.

Indexing

Hi All,
I got Visual Studio 2005 and SQL Express comes with it. I am creating
table from the data Connections.
Is there an auto number in sql server express? Right now I am just
using the data type int at the present.
Thank you.
RBYou can use identity property of the column for auto increment of your
column.
Look at books online for more information of it.
Indexing is different concept, it never changes or automate data in the
column.
Regards
Amish

Indexes

Say you have a table with 10 fields, 50,000-100,000 records, and 1 primary key field.

Is there any performance difference between creating a "covered" index and versus creating 9 individual indexes (not 10 b/c i'm assuming the PK field will already have an index created for it), one for each non key field.What is a "covered" index?|||A covered Index is an index which includes many columns in it. For example if you have an application which can search on 5 out of 10 fields you could create an index on those 5 fields and the result is supposed to be quicker searches on those 5 fields.|||Then I guess it might depend on how often rows are inserted, and how often these columns are updated, since that is when index rows would be inserted or updated.

It sounded like you were suggesting putting all the columns of the table in one index -- this would not accomplish anything, would it?

I see what you are saying about combining some columns in an index, particularly if your search would be filtering on more than one of the columns.

It's difficult to make a suggestion without more info on activity, column size, and filtering methods.|||Assume that you'll be reading most of the time. Given that is there a difference between the two methods.. If any?|||Do we speak about read or write operations here?

If we speak read, then it's all coming about what queries are going to be used.

If there's gonna be a SELECT statement that will be utilizing a scope that will use a WHERE with 5 fields, the optimum is to have an index that will cover those 5 fields instead of having those 9 seperate indexes.

As for the write operation I am not that sure that there'll be any differences anyways.

I think it all comes to the query optimizer really and the execution plan.|||I've always thought you should cover the columns in a popular where clause.

In a few cases where the data is inserted or updated more than read, then fewer indexes is better.|||I think it depends on your query.
Also, the index tuning wizard may helpful in your case.

Indexed Views - Group By Redundancy

I have a table that I want to have a precalulcate length on a character field
and group and sum up. Thought I could do this by creating a view with a group
by clause that includes the sum function. Unfortunately, the compiler
complains with:

A clustered index cannot be created on the view 'MyView' because the index
key includes columns which are not in the GROUP BY clause.

Wish I could verbalize the problem a little better, but the following pared
down example should serve as a demonstration:

SET ANSI_WARNINGS ON
SET ANSI_PADDING ON
SET ANSI_NULLS ON
SET ARITHABORT ON
SET CONCAT_NULL_YIELDS_NULL ON
SET QUOTED_IDENTIFIER ON
SET NUMERIC_ROUNDABORT OFF
GO

CREATE TABLE myTable(
myID INT NOT NULL,
RecNum INT NOT NULL,
TestString VARCHAR(80) NOT NULL)
GO

INSERT INTO myTable VALUES(1, 1, 'a')
INSERT INTO myTable VALUES(1, 2, 'ab')
INSERT INTO myTable VALUES(2, 2, 'abc')
GO

CREATE VIEW dbo.MyView WITH SCHEMABINDING AS
SELECT
myID = myID,
slen = SUM(LEN(TestString)),
recn = COUNT_BIG(*)
FROM dbo.myTable
GROUP BY myID
GO

CREATE UNIQUE CLUSTERED INDEX IX_MyView ON MyView(myID, slen)
-- A clustered index cannot be created on the view 'MyView' because
-- the index key includes columns which are not in the GROUP BY clause.
GO

DROP VIEW MyView
GO

DROP TABLE myTable
GO

Thanks,
Chris Rathman[posted and mailed, please reply in news]

ChrisRath (chrisrath@.aol.com) writes:
> Wish I could verbalize the problem a little better, but the following
> pared down example should serve as a demonstration:

Very good repro, thanks.

As for your problem, I think the cure is simple. Just change:

> CREATE UNIQUE CLUSTERED INDEX IX_MyView ON MyView(myID, slen)

to

> CREATE UNIQUE CLUSTERED INDEX IX_MyView ON MyView(myID)

Alas, this rewards with a new message:

Server: Msg 8662, Level 16, State 1, Line 1
An index cannot be created on the view 'MyView' because the view
definition includes an unknown value (the sum of a nullable expression).

A little funny, since the expression is not nullable. But alright, change
the definition to:

CREATE VIEW dbo.MyView WITH SCHEMABINDING AS
SELECT
myID = myID,
slen = SUM(coalesce(LEN(TestString), 0),
recn = COUNT_BIG(*)
FROM dbo.myTable
GROUP BY myID
GO

But the message is the same.

At this point I will have to admit defeat, but I ask around with my
MVP colleauages and Microsoft contacts, to find out if this is a bug
or by design (with an improper error message).

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||[posted and mailed, please reply in news]

ChrisRath (chrisrath@.aol.com) writes:
> I have a table that I want to have a precalulcate length on a character
> field and group and sum up. Thought I could do this by creating a view
> with a group by clause that includes the sum function.

I have yet to get answer if the error message I got was by design or due
to a bug. However, my MVP colleage Steve Kass offered a working solution,
although it requires an auxillary table:

SET ANSI_WARNINGS ON
SET ANSI_PADDING ON
SET ANSI_NULLS ON
SET ARITHABORT ON
SET CONCAT_NULL_YIELDS_NULL ON
SET QUOTED_IDENTIFIER ON
SET NUMERIC_ROUNDABORT OFF
GO

CREATE TABLE myTable(
myID INT NOT NULL,
RecNum INT NOT NULL,
TestString VARCHAR(80) NOT NULL,
blafs int NOT NULL)
GO
SELECT TOP 8000 Number = IDENTITY(int, 1, 1)
INTO Numbers
FROM pubs..authors t1, pubs..authors t2, pubs..authors t3
go
INSERT INTO myTable VALUES(1, 1, 'a', 9)
INSERT INTO myTable VALUES(1, 2, 'ab', 9)
INSERT INTO myTable VALUES(2, 2, 'abc', 12)
GO

CREATE VIEW dbo.MyView WITH SCHEMABINDING AS
SELECT
myID = m.myID,
slen = SUM(N.Number),
recn = COUNT_BIG(*)
FROM dbo.myTable m
JOIN dbo.Numbers N ON N.Number = LEN(m.TestString)
GROUP BY m.myID
GO

CREATE UNIQUE CLUSTERED INDEX IX_MyView ON MyView(myID)
-- Server: Msg 8662, Level 16, State 1, Line 1
-- An index cannot be created on the view 'MyView' because the view
-- definition includes an unknown value (the sum of a nullable
-- expression).
GO

DROP VIEW MyView
GO
DROP TABLE myTable
GO
DROP TABLE Numbers
GO

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||I've got a pivot table I use for other purposes that will come in handy. Seems
to compile ok. Will run some performance tests on the target to see if I get
the desired boost.

Thanks for the help. And thank your colleague for me for providing a creative
solution. :-)

> CREATE VIEW dbo.MyView WITH SCHEMABINDING AS
> SELECT
> myID = m.myID,
> slen = SUM(N.Number),
> recn = COUNT_BIG(*)
> FROM dbo.myTable m
> JOIN dbo.Numbers N ON N.Number = LEN(m.TestString)
> GROUP BY m.myID
> GO