Is using varchar() as index a bad idea?

Hi ,
I have an application whose data is stored in some other db and I am
converting
to SQL. The existing app has a table, Contractors with a field ContractorCo
de
char(10) as an index to the table. For some reason, I am against using
recognizable data as an index an instead opt for GUID. I s having a char or
varchar
field as an index slower than a GUID field index?
ThanksOpa
I personally try to avoid creating an index on GUID . ( I try keep my index
as small as possible) Having index on VARCHAR/CHAR depends on your
specific requirements . See an execution plan of the query, is an optimizer
used the index and then make a decision?
"Opa" <Opa@.discussions.microsoft.com> wrote in message
news:694D881A-1A70-4233-AC08-7392F3816A19@.microsoft.com...
> Hi ,
> I have an application whose data is stored in some other db and I am
> converting
> to SQL. The existing app has a table, Contractors with a field
> ContractorCode
> char(10) as an index to the table. For some reason, I am against using
> recognizable data as an index an instead opt for GUID. I s having a char
> or
> varchar
> field as an index slower than a GUID field index?
>
> Thanks|||There is a lot of debate on using GUIDs as primary keys. Here is an example:
http://www.sql-server-performance.c...erformance.asp. There is no
problem in using CHAR(10) as your index as long as the column helps you in
identifying the entity uniquely.
--
HTH,
SriSamp
Email: srisamp@.gmail.com
Blog: http://blogs.sqlxml.org/srinivassampath
URL: http://www32.brinkster.com/srisamp
"Opa" <Opa@.discussions.microsoft.com> wrote in message
news:694D881A-1A70-4233-AC08-7392F3816A19@.microsoft.com...
> Hi ,
> I have an application whose data is stored in some other db and I am
> converting
> to SQL. The existing app has a table, Contractors with a field
> ContractorCode
> char(10) as an index to the table. For some reason, I am against using
> recognizable data as an index an instead opt for GUID. I s having a char
> or
> varchar
> field as an index slower than a GUID field index?
>
> Thanks|||Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.programming:584774
Index on GUID will take up more storage therefore bigger index therefore
more I/0. This would be pronounced on a clustered index.
The Contarctors table with Contractor code, what is the variety of data
within the col ? and what types of searches (if any) will be commited
on that column?
Jack Vamvas
________________________________________
__________________________
Receive free SQL tips - register at www.ciquery.com/sqlserver.htm
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uS4EcQjMGHA.2040@.TK2MSFTNGP14.phx.gbl...
> Opa
> I personally try to avoid creating an index on GUID . ( I try keep my
index
> as small as possible) Having index on VARCHAR/CHAR depends on your
> specific requirements . See an execution plan of the query, is an
optimizer
> used the index and then make a decision?
>
> "Opa" <Opa@.discussions.microsoft.com> wrote in message
> news:694D881A-1A70-4233-AC08-7392F3816A19@.microsoft.com...
char
>|||Opa (Opa@.discussions.microsoft.com) writes:
> I have an application whose data is stored in some other db and I am
> converting to SQL. The existing app has a table, Contractors with a
> field ContractorCode char(10) as an index to the table. For some
> reason, I am against using recognizable data as an index an instead opt
> for GUID. I s having a char or varchar field as an index slower than a
> GUID field index?
The last question is not really meaningful, because there are always a lot
of "it depends".
But generally, as a guid is 16 bytes, it's longer than the code, and the
longer the field, the less keys you get on a page, and the bigger the
index gets, and the more pages to read.
I would suspect, though, that the Contractors table is not of a size
where this is a much of an issue. Then again, the code may be used as an
FK in a larger table where it may matter.
Personally, I rather use the code as key, than GUID which is much more
difficult to manage. If you want artificial keys, integer is probably
better.
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|||Ok so if I have varchar(16) vs GUID will this index field always perform bet
ter
or equal to GUID is SELECTS, INSERTS, JOINS etc?
BTW, I disagree with your comment stating the column should help identify
the entity uniquely in a visual way.
"SriSamp" wrote:

> There is a lot of debate on using GUIDs as primary keys. Here is an exampl
e:
> http://www.sql-server-performance.c...erformance.asp. There is no
> problem in using CHAR(10) as your index as long as the column helps you in
> identifying the entity uniquely.
> --
> HTH,
> SriSamp
> Email: srisamp@.gmail.com
> Blog: http://blogs.sqlxml.org/srinivassampath
> URL: http://www32.brinkster.com/srisamp
> "Opa" <Opa@.discussions.microsoft.com> wrote in message
> news:694D881A-1A70-4233-AC08-7392F3816A19@.microsoft.com...
>
>|||The performance aspect is something that you need to check for. Regarding my
other comment, what I meant was, if the CHAR(10) field is the "natural" key
for your table, you should go ahead and use it, rather than defining another
key.
--
HTH,
SriSamp
Email: srisamp@.gmail.com
Blog: http://blogs.sqlxml.org/srinivassampath
URL: http://www32.brinkster.com/srisamp
"Opa" <Opa@.discussions.microsoft.com> wrote in message
news:824B3723-E44D-410F-930F-58A4F31F5DB1@.microsoft.com...
> Ok so if I have varchar(16) vs GUID will this index field always perform
> better
> or equal to GUID is SELECTS, INSERTS, JOINS etc?
> BTW, I disagree with your comment stating the column should help identify
> the entity uniquely in a visual way.
>
> "SriSamp" wrote:
>|||Have a look at this excellent article from Kimberly L Tripp
http://www.sqlskills.com/blogs/kimb...
f-a290db645159
Regards
Roji. P. Thomas
http://toponewithties.blogspot.com
"Opa" <Opa@.discussions.microsoft.com> wrote in message
news:694D881A-1A70-4233-AC08-7392F3816A19@.microsoft.com...
> Hi ,
> I have an application whose data is stored in some other db and I am
> converting
> to SQL. The existing app has a table, Contractors with a field
> ContractorCode
> char(10) as an index to the table. For some reason, I am against using
> recognizable data as an index an instead opt for GUID. I s having a char
> or
> varchar
> field as an index slower than a GUID field index?
>
> Thanks|||You, sir, are a moron. I usually don't say that about people, but I'll
make an exception in your case.
Stu|||When you use "index", I'm assuming you really mean to say "primary key". A
primary key is a type of index, but an index is not a primary key.
I don't believe there is any basic performance benefit to using integer
based keys over char based keys. As far as SQL Server is concerned, a key is
just a block of bytes, and the smaller the better. A GUID is typically
displayed as a string of text, but is stored internally as a 16 byte
integer. Consider instead an int (4 bytes) or smallint (2 bytes) identity.
The only use I can see for using GUID (globally unique) keys is if the
Contractor data is to be merged with Contractor data from another system and
you want to retain the same surrogate key values. However, this could be
better implemented by adding a SystemCode to the Contractors table and have
SystemCode + ContractorCode as the primary key.
Perhaps keeping ContractorCode as the primary key would be the best solution
unless there is a strong and compelling reason to implement a surrogate key.
"Opa" <Opa@.discussions.microsoft.com> wrote in message
news:694D881A-1A70-4233-AC08-7392F3816A19@.microsoft.com...
> Hi ,
> I have an application whose data is stored in some other db and I am
> converting
> to SQL. The existing app has a table, Contractors with a field
> ContractorCode
> char(10) as an index to the table. For some reason, I am against using
> recognizable data as an index an instead opt for GUID. I s having a char
> or
> varchar
> field as an index slower than a GUID field index?
>
> Thanks

Is this Index supposed to make view faster?

Ok,
When I did this to add the index on the view, the view is no faster at
all...Did I do something wrong? :
USE tsNess
GO
SET NUMERIC_ROUNDABORT OFF
GO
SET
ANSI_PADDING,ANSI_WARNINGS,CONCAT_NULL_Y
IELDS_NULL,ARITHABORT,QUOTED_IDENTIF
IER,ANSI_NULLS
ON
GO
CREATE VIEW V1
WITH SCHEMABINDING
AS
SELECT t1.MemberId, t1.PeriodID, t8.start_date, t6.amount_type_id,
t6.amount_type,
SUM(CASE WHEN t2.amountTypeId = 7 THEN t2.amount
WHEN t2.amountTypeId = 23 THEN - t2.amount END) AS Purchase,
SUM(CASE WHEN t2.amountTypeId = 8 THEN t2.amount
WHEN t2.amountTypeId = 24 THEN - t2.amount END) AS Matrix,
SUM(CASE WHEN t2.amountTypeId = 20 THEN t2.amount
WHEN t2.amountTypeId = 21 THEN - t2.amount END) AS QualiFly,
SUM(CASE WHEN t2.amountTypeId = 9 THEN t2.amount
WHEN t2.amountTypeId = 25 THEN - t2.amount END) AS Dist,
SUM(CASE WHEN t2.amountTypeId = 10 THEN t2.amount
WHEN t2.amountTypeId = 26 THEN - t2.amount END) AS SM,
SUM(CASE WHEN t2.amountTypeId = 11 THEN t2.amount
WHEN t2.amountTypeId = 27 THEN - t2.amount END) AS BreakAway,
SUM(CASE WHEN t2.amountTypeId = 13 THEN t2.amount
WHEN t2.amountTypeId = 14 THEN - t2.amount END) AS Transfer,
SUM(CASE WHEN t2.amountTypeId = 28 THEN t2.amount
WHEN t2.amountTypeId = 15 THEN - t2.amount END) AS Spent
FROM dbo.tblTravelDetail t1 INNER JOIN
dbo.tblTravelDetailAmount t2 ON t1.TravelDetailId
= t2.TravelDetailId INNER JOIN
dbo.tblTravelDetailMember t4 ON t1.TravelDetailId
= t4.TravelDetailId INNER JOIN
dbo.tblTravelEvent t5 ON t1.TravelEventId =
t5.TravelEventId INNER JOIN
dbo.amount_type t6 ON t2.amountTypeId =
t6.amount_type_id INNER JOIN
dbo.period t8 ON t1.PeriodID = t8.period_id
WHERE (t1.MemberId = '222') AND (t2.amount <> 0)
GROUP BY t1.MemberId, t1.PeriodID, t8.start_date, t6.amount_type_id,
t6.amount_type
Thanks,
TrintHi
select <column lists> from V1 with (noexpand)
See an execution plan for the query
"trint" <trinity.smith@.gmail.com> wrote in message
news:1123505778.551931.242730@.o13g2000cwo.googlegroups.com...
> Ok,
> When I did this to add the index on the view, the view is no faster at
> all...Did I do something wrong? :
> USE tsNess
> GO
> SET NUMERIC_ROUNDABORT OFF
> GO
> SET
> ANSI_PADDING,ANSI_WARNINGS,CONCAT_NULL_Y
IELDS_NULL,ARITHABORT,QUOTED_IDENT
IFIER,ANSI_NULLS
> ON
> GO
> CREATE VIEW V1
> WITH SCHEMABINDING
> AS
> SELECT t1.MemberId, t1.PeriodID, t8.start_date, t6.amount_type_id,
> t6.amount_type,
> SUM(CASE WHEN t2.amountTypeId = 7 THEN t2.amount
> WHEN t2.amountTypeId = 23 THEN - t2.amount END) AS Purchase,
> SUM(CASE WHEN t2.amountTypeId = 8 THEN t2.amount
> WHEN t2.amountTypeId = 24 THEN - t2.amount END) AS Matrix,
> SUM(CASE WHEN t2.amountTypeId = 20 THEN t2.amount
> WHEN t2.amountTypeId = 21 THEN - t2.amount END) AS QualiFly,
> SUM(CASE WHEN t2.amountTypeId = 9 THEN t2.amount
> WHEN t2.amountTypeId = 25 THEN - t2.amount END) AS Dist,
> SUM(CASE WHEN t2.amountTypeId = 10 THEN t2.amount
> WHEN t2.amountTypeId = 26 THEN - t2.amount END) AS SM,
> SUM(CASE WHEN t2.amountTypeId = 11 THEN t2.amount
> WHEN t2.amountTypeId = 27 THEN - t2.amount END) AS BreakAway,
> SUM(CASE WHEN t2.amountTypeId = 13 THEN t2.amount
> WHEN t2.amountTypeId = 14 THEN - t2.amount END) AS Transfer,
> SUM(CASE WHEN t2.amountTypeId = 28 THEN t2.amount
> WHEN t2.amountTypeId = 15 THEN - t2.amount END) AS Spent
> FROM dbo.tblTravelDetail t1 INNER JOIN
> dbo.tblTravelDetailAmount t2 ON t1.TravelDetailId
> = t2.TravelDetailId INNER JOIN
> dbo.tblTravelDetailMember t4 ON t1.TravelDetailId
> = t4.TravelDetailId INNER JOIN
> dbo.tblTravelEvent t5 ON t1.TravelEventId =
> t5.TravelEventId INNER JOIN
> dbo.amount_type t6 ON t2.amountTypeId =
> t6.amount_type_id INNER JOIN
> dbo.period t8 ON t1.PeriodID = t8.period_id
> WHERE (t1.MemberId = '222') AND (t2.amount <> 0)
> GROUP BY t1.MemberId, t1.PeriodID, t8.start_date, t6.amount_type_id,
> t6.amount_type
> Thanks,
> Trint
>|||Did you create indexes on the view?
Once you create an indexed view, make sure your view does not use the base
table indexes.
The way to do that is use the option (noexpand)
eg:-
Select PeriodID from V1 (noexpand)
where ...
To get the adv of indexed view, you should make sure its using the indexes
on the view rather than the old base table indexes.
The next thing is see the reads/Cpu and Dur with and without the option
noexpand. Moreover dont leave out exe plan.
Before you go for indexed view it wud be good to see how much time it would
take to create an index on a prod server.
The retireval performance should not be an overhead while saving
or in other words see whether the tables used in your indexed view are
updated/inserted frequently. If so I dont think its a good idea to go for
indexed view.
Thanks,
Prad
"trint" <trinity.smith@.gmail.com> wrote in message
news:1123505778.551931.242730@.o13g2000cwo.googlegroups.com...
> Ok,
> When I did this to add the index on the view, the view is no faster at
> all...Did I do something wrong? :
> USE tsNess
> GO
> SET NUMERIC_ROUNDABORT OFF
> GO
> SET
> ANSI_PADDING,ANSI_WARNINGS,CONCAT_NULL_Y
IELDS_NULL,ARITHABORT,QUOTED_IDENT
IFIER,ANSI_NULLS
> ON
> GO
> CREATE VIEW V1
> WITH SCHEMABINDING
> AS
> SELECT t1.MemberId, t1.PeriodID, t8.start_date, t6.amount_type_id,
> t6.amount_type,
> SUM(CASE WHEN t2.amountTypeId = 7 THEN t2.amount
> WHEN t2.amountTypeId = 23 THEN - t2.amount END) AS Purchase,
> SUM(CASE WHEN t2.amountTypeId = 8 THEN t2.amount
> WHEN t2.amountTypeId = 24 THEN - t2.amount END) AS Matrix,
> SUM(CASE WHEN t2.amountTypeId = 20 THEN t2.amount
> WHEN t2.amountTypeId = 21 THEN - t2.amount END) AS QualiFly,
> SUM(CASE WHEN t2.amountTypeId = 9 THEN t2.amount
> WHEN t2.amountTypeId = 25 THEN - t2.amount END) AS Dist,
> SUM(CASE WHEN t2.amountTypeId = 10 THEN t2.amount
> WHEN t2.amountTypeId = 26 THEN - t2.amount END) AS SM,
> SUM(CASE WHEN t2.amountTypeId = 11 THEN t2.amount
> WHEN t2.amountTypeId = 27 THEN - t2.amount END) AS BreakAway,
> SUM(CASE WHEN t2.amountTypeId = 13 THEN t2.amount
> WHEN t2.amountTypeId = 14 THEN - t2.amount END) AS Transfer,
> SUM(CASE WHEN t2.amountTypeId = 28 THEN t2.amount
> WHEN t2.amountTypeId = 15 THEN - t2.amount END) AS Spent
> FROM dbo.tblTravelDetail t1 INNER JOIN
> dbo.tblTravelDetailAmount t2 ON t1.TravelDetailId
> = t2.TravelDetailId INNER JOIN
> dbo.tblTravelDetailMember t4 ON t1.TravelDetailId
> = t4.TravelDetailId INNER JOIN
> dbo.tblTravelEvent t5 ON t1.TravelEventId =
> t5.TravelEventId INNER JOIN
> dbo.amount_type t6 ON t2.amountTypeId =
> t6.amount_type_id INNER JOIN
> dbo.period t8 ON t1.PeriodID = t8.period_id
> WHERE (t1.MemberId = '222') AND (t2.amount <> 0)
> GROUP BY t1.MemberId, t1.PeriodID, t8.start_date, t6.amount_type_id,
> t6.amount_type
> Thanks,
> Trint
>|||Ok,
Uri and Pradeep, I get this error when trying to create an index on
view one or V1:
An index cannot be created on the view 'V1' because the view definition
includes an unknown value (the sum of a nullable expression).
Thanks,
Trint|||Trint,
Another reason why we dont go for indexed view always...
See BOL you have a lot of requirements to be satisfied in creating one apart
from the time it takes and the ovehead while saving...
One more thing that I have obsereved is the fields on which u intend to
create index should not have duplicates...
You cannot have :
a.. A derived table.
a.. Rowset functions.
a.. UNION operator.
a.. Subqueries.
a.. Outer or self joins.
a.. TOP clause.
a.. ORDER BY clause.
a.. DISTINCT keyword.
a.. COUNT(*) but (COUNT_BIG(*) is allowed.)
a.. A SUM function that references a nullable expression.
a.. The full-text predicates CONTAINS or FREETEXT.
a.. COMPUTE or COMPUTE BY clause.
a.. If GROUP BY is not specified, the view select list cannot contain
aggregate expressions.
a.. If GROUP BY is specified, the view select list must contain a
COUNT_BIG(*) expression, and the view definition cannot specify HAVING,
CUBE, or ROLLUP.
a.. A column resulting from an expression that either evaluates to a float
value or uses float expressions for its evaluation cannot be a key of an
index in an indexed view or a table.
From BOL
Thanks,
Pradeep Kutty
"trint" <trinity.smith@.gmail.com> wrote in message
news:1123514588.376478.113330@.g43g2000cwa.googlegroups.com...
> Ok,
> Uri and Pradeep, I get this error when trying to create an index on
> view one or V1:
> An index cannot be created on the view 'V1' because the view definition
> includes an unknown value (the sum of a nullable expression).
> Thanks,
> Trint
>|||Trint,
To start off, I would like to ask if you have already indexed the base
tables? Indexing a view is not the place to start. Under normal
conditions, view performance is just fine if you properly index the base
tables.
For the rest of the reply, I will assume you have a properly normalized
data model that is properly indexed.
If you must index the view, then make sure sure:
- dbo.tblTravelDetailAmount.amount is defined as NOT NULL;
- the expressions never evaluate to NULL. Currently your CASE
expressions will evaluate to NULL for each amountTypeID that is not
explicitely mentioned in the expression. You could add "ELSE 0" to each
CASE expression to solve that;
- in the case of dbo.tblTravelDetail.MemberId that you match the
literal's data type to the column's. For example, if the MemberId is
defined as int, then change the predicate to WHERE t1.MemberId = 222;
- you add COUNT_BIG(*) to the selection list.
Maybe then you can index the view.
Note that if only few rows in table dbo.tblTravelDetailAmount have
amount=0, then the predicate WHERE t2.amount<>0 may not help performance
(it may even hurt performance) if you select from the base tables.
HTH,
Gert-Jan
trint wrote:
> Ok,
> When I did this to add the index on the view, the view is no faster at
> all...Did I do something wrong? :
> USE tsNess
> GO
> SET NUMERIC_ROUNDABORT OFF
> GO
> SET
> ANSI_PADDING,ANSI_WARNINGS,CONCAT_NULL_Y
IELDS_NULL,ARITHABORT,QUOTED_IDENT
IFIER,ANSI_NULLS
> ON
> GO
> CREATE VIEW V1
> WITH SCHEMABINDING
> AS
> SELECT t1.MemberId, t1.PeriodID, t8.start_date, t6.amount_type_id,
> t6.amount_type,
> SUM(CASE WHEN t2.amountTypeId = 7 THEN t2.amount
> WHEN t2.amountTypeId = 23 THEN - t2.amount END) AS Purchase,
> SUM(CASE WHEN t2.amountTypeId = 8 THEN t2.amount
> WHEN t2.amountTypeId = 24 THEN - t2.amount END) AS Matrix,
> SUM(CASE WHEN t2.amountTypeId = 20 THEN t2.amount
> WHEN t2.amountTypeId = 21 THEN - t2.amount END) AS QualiFly,
> SUM(CASE WHEN t2.amountTypeId = 9 THEN t2.amount
> WHEN t2.amountTypeId = 25 THEN - t2.amount END) AS Dist,
> SUM(CASE WHEN t2.amountTypeId = 10 THEN t2.amount
> WHEN t2.amountTypeId = 26 THEN - t2.amount END) AS SM,
> SUM(CASE WHEN t2.amountTypeId = 11 THEN t2.amount
> WHEN t2.amountTypeId = 27 THEN - t2.amount END) AS BreakAway,
> SUM(CASE WHEN t2.amountTypeId = 13 THEN t2.amount
> WHEN t2.amountTypeId = 14 THEN - t2.amount END) AS Transfer,
> SUM(CASE WHEN t2.amountTypeId = 28 THEN t2.amount
> WHEN t2.amountTypeId = 15 THEN - t2.amount END) AS Spent
> FROM dbo.tblTravelDetail t1 INNER JOIN
> dbo.tblTravelDetailAmount t2 ON t1.TravelDetailId
> = t2.TravelDetailId INNER JOIN
> dbo.tblTravelDetailMember t4 ON t1.TravelDetailId
> = t4.TravelDetailId INNER JOIN
> dbo.tblTravelEvent t5 ON t1.TravelEventId =
> t5.TravelEventId INNER JOIN
> dbo.amount_type t6 ON t2.amountTypeId =
> t6.amount_type_id INNER JOIN
> dbo.period t8 ON t1.PeriodID = t8.period_id
> WHERE (t1.MemberId = '222') AND (t2.amount <> 0)
> GROUP BY t1.MemberId, t1.PeriodID, t8.start_date, t6.amount_type_id,
> t6.amount_type
> Thanks,
> Trint

Is this index redundant?

Let's say I have a table:
create table example (
id int,
createtime datetime,
name varchar(20))
go
create index ix_createtime on example(createtime)
go
create index ix_createtime_name on example(createtime, name)
go
Is the index ix_createtime redundant because the createtime column is
part of the composite index ix_createtime_name. If someone whee to
seach based only on the createtime column would the composite index be
as useful as the index on just the createtime column?
Thanks<pshroads@.gmail.com> wrote in message
news:1148056676.401254.153400@.y43g2000cwc.googlegroups.com...
> Is the index ix_createtime redundant because the createtime column is
> part of the composite index ix_createtime_name. If someone whee to
> seach based only on the createtime column would the composite index be
> as useful as the index on just the createtime column?
Yes, it probably is redundant. There are a few very limited edge cases
where it might not be -- for instance, if you're doing a lot of analysis
based on only a single column and every I/O counts -- but they are very few
and very far between. The composite index will certainly work for a query
based only on the createtime column.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--|||Index ix_createtime is leaner than index ix_createtime_name. As such,
it is slightly more useful. However, its advantage is not too
noticeable, and under most circumstances you can safely drop it. you
can figure it out yourself, run your own tests, such as
select * from example where createtime between '20060101' and
'20060202'
there might be ranges for which access via ix_createtime is still
cheaper than scanning the whole clustered index, but access via
ix_createtime_name is already more expensive than table scan.
Good luck!|||Unless the table is read-only, you also need to consider maintenance costs.
With the redundant index, you will have extra work to do for almost every
modification to the table, and you'll need to determine if that cost is
worth the small benefit of having the narrower index for some queries.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
<pshroads@.gmail.com> wrote in message
news:1148056676.401254.153400@.y43g2000cwc.googlegroups.com...
> Let's say I have a table:
> create table example (
> id int,
> createtime datetime,
> name varchar(20))
> go
> create index ix_createtime on example(createtime)
> go
> create index ix_createtime_name on example(createtime, name)
> go
> Is the index ix_createtime redundant because the createtime column is
> part of the composite index ix_createtime_name. If someone whee to
> seach based only on the createtime column would the composite index be
> as useful as the index on just the createtime column?
> Thanks
>|||Kalen,
I agree maintenance costs need to be taken in account. Yet I don't see
why you are calling the benefit of having a lean index "small" without
knowing much about the OP's system. I think it depends on the workload.
Suppose there is a query that runs very frequently. As such, small 10%
savings in its real execution costs may result in, say, 6% reduction of
overall workload for the whole system...|||Thanks. Am I correct in thinking that it's only redundant because
statistics are maintained on only the first column of an index? So if
the composite index had been on (name, createtime) then it would not be
redundant because the optimizer would be unlikely to choose that index
when searching on createtime because there would be no statistics?|||How will said "lean" index be used? I don't think dropping 20 bytes from an
index like this will result in "10% savings" in 99% of the cases, because
most likely a query will involve more than just the datetime column. Keep
in mind that bookmark lookups are much more expensive, in most cases, than
the additional I/Os that would be incurred reading the larger index pages.
We of course don't know what the clustering key is, or any other information
about the use, so this is all speculation, but unless this is a very
specialized situation I'd venture a guess that having both indexes is not
beneficial.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Alexander Kuznetsov" <AK_TIREDOFSPAM@.hotmail.COM> wrote in message
news:1148061719.429023.48180@.i40g2000cwc.googlegroups.com...
> Kalen,
> I agree maintenance costs need to be taken in account. Yet I don't see
> why you are calling the benefit of having a lean index "small" without
> knowing much about the OP's system. I think it depends on the workload.
> Suppose there is a query that runs very frequently. As such, small 10%
> savings in its real execution costs may result in, say, 6% reduction of
> overall workload for the whole system...
>|||well I'm not speaking in terms of 99% of the cases or 99.999% of the
case or whatever else. I am speaking about one particular system we
know almost nothing about - the OP's system. Suppose that system is
very simple and the primary goal of that simple system is to run
something like this:
select count(distinct id) from example where createtime between
@.fromdate and @.todate
as fast as possible. In that case the savings would be substantial, as
there would be no bookmark lookups. However rare, such cases do exist.
That's why I was trying to encourage the original poster to benchmark
by himself rather than rely on our advices. I think in our trade all
rules have exceptions.
That's all I was trying to say...|||On 19 May 2006 09:37:56 -0700, pshroads@.gmail.com wrote:

>Let's say I have a table:
>create table example (
> id int,
> createtime datetime,
> name varchar(20))
>go
>create index ix_createtime on example(createtime)
>go
>create index ix_createtime_name on example(createtime, name)
>go
>Is the index ix_createtime redundant because the createtime column is
>part of the composite index ix_createtime_name. If someone whee to
>seach based only on the createtime column would the composite index be
>as useful as the index on just the createtime column?
If there are a million rows with the same time, and you might have a
query "select * from example where createtime = @.t and name like
'foo%', then, um, is SQLServer smart enough to do its work on the
index in a case like that?
Only reason I can think of you might want that second.
Josh|||On 19 May 2006 11:06:19 -0700, pshroads@.gmail.com wrote:

>Thanks. Am I correct in thinking that it's only redundant because
>statistics are maintained on only the first column of an index? So if
>the composite index had been on (name, createtime) then it would not be
>redundant because the optimizer would be unlikely to choose that index
>when searching on createtime because there would be no statistics?
Hi pshroads,
You are right that the index on (createtime) would not be redundant if
the composite index had been on (name, createtime), but you're wrong
about the reason.
Suppose I give you a stack of paper with one-paper sized biographies of
all famous people of the last millenium, ordered by birthdate. I'll also
give you a much smaller stack of paper that includes just the name and
the page number(s) where a person with that name is described, sorted by
last name, then first name. And I give you another index that holds just
the first name and the pages where someone with that first name is
found.
What would your strategy be if I asked you for some details about
someone with first name "Kathlyn"?
Hugo Kornelis, SQL Server MVP

Is this index redundant?

Let's say I have a table:
create table example (
id int,
createtime datetime,
name varchar(20))
go
create index ix_createtime on example(createtime)
go
create index ix_createtime_name on example(createtime, name)
go
Is the index ix_createtime redundant because the createtime column is
part of the composite index ix_createtime_name. If someone whee to
seach based only on the createtime column would the composite index be
as useful as the index on just the createtime column?
Thanks<pshroads@.gmail.com> wrote in message
news:1148056676.401254.153400@.y43g2000cwc.googlegroups.com...
> Is the index ix_createtime redundant because the createtime column is
> part of the composite index ix_createtime_name. If someone whee to
> seach based only on the createtime column would the composite index be
> as useful as the index on just the createtime column?
Yes, it probably is redundant. There are a few very limited edge cases
where it might not be -- for instance, if you're doing a lot of analysis
based on only a single column and every I/O counts -- but they are very few
and very far between. The composite index will certainly work for a query
based only on the createtime column.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--|||Index ix_createtime is leaner than index ix_createtime_name. As such,
it is slightly more useful. However, its advantage is not too
noticeable, and under most circumstances you can safely drop it. you
can figure it out yourself, run your own tests, such as
select * from example where createtime between '20060101' and
'20060202'
there might be ranges for which access via ix_createtime is still
cheaper than scanning the whole clustered index, but access via
ix_createtime_name is already more expensive than table scan.
Good luck!|||Unless the table is read-only, you also need to consider maintenance costs.
With the redundant index, you will have extra work to do for almost every
modification to the table, and you'll need to determine if that cost is
worth the small benefit of having the narrower index for some queries.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
<pshroads@.gmail.com> wrote in message
news:1148056676.401254.153400@.y43g2000cwc.googlegroups.com...
> Let's say I have a table:
> create table example (
> id int,
> createtime datetime,
> name varchar(20))
> go
> create index ix_createtime on example(createtime)
> go
> create index ix_createtime_name on example(createtime, name)
> go
> Is the index ix_createtime redundant because the createtime column is
> part of the composite index ix_createtime_name. If someone whee to
> seach based only on the createtime column would the composite index be
> as useful as the index on just the createtime column?
> Thanks
>|||Kalen,
I agree maintenance costs need to be taken in account. Yet I don't see
why you are calling the benefit of having a lean index "small" without
knowing much about the OP's system. I think it depends on the workload.
Suppose there is a query that runs very frequently. As such, small 10%
savings in its real execution costs may result in, say, 6% reduction of
overall workload for the whole system...|||Thanks. Am I correct in thinking that it's only redundant because
statistics are maintained on only the first column of an index? So if
the composite index had been on (name, createtime) then it would not be
redundant because the optimizer would be unlikely to choose that index
when searching on createtime because there would be no statistics?|||How will said "lean" index be used? I don't think dropping 20 bytes from an
index like this will result in "10% savings" in 99% of the cases, because
most likely a query will involve more than just the datetime column. Keep
in mind that bookmark lookups are much more expensive, in most cases, than
the additional I/Os that would be incurred reading the larger index pages.
We of course don't know what the clustering key is, or any other information
about the use, so this is all speculation, but unless this is a very
specialized situation I'd venture a guess that having both indexes is not
beneficial.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Alexander Kuznetsov" <AK_TIREDOFSPAM@.hotmail.COM> wrote in message
news:1148061719.429023.48180@.i40g2000cwc.googlegroups.com...
> Kalen,
> I agree maintenance costs need to be taken in account. Yet I don't see
> why you are calling the benefit of having a lean index "small" without
> knowing much about the OP's system. I think it depends on the workload.
> Suppose there is a query that runs very frequently. As such, small 10%
> savings in its real execution costs may result in, say, 6% reduction of
> overall workload for the whole system...
>|||well I'm not speaking in terms of 99% of the cases or 99.999% of the
case or whatever else. I am speaking about one particular system we
know almost nothing about - the OP's system. Suppose that system is
very simple and the primary goal of that simple system is to run
something like this:
select count(distinct id) from example where createtime between
@.fromdate and @.todate
as fast as possible. In that case the savings would be substantial, as
there would be no bookmark lookups. However rare, such cases do exist.
That's why I was trying to encourage the original poster to benchmark
by himself rather than rely on our advices. I think in our trade all
rules have exceptions.
That's all I was trying to say...|||On 19 May 2006 09:37:56 -0700, pshroads@.gmail.com wrote:

>Let's say I have a table:
>create table example (
> id int,
> createtime datetime,
> name varchar(20))
>go
>create index ix_createtime on example(createtime)
>go
>create index ix_createtime_name on example(createtime, name)
>go
>Is the index ix_createtime redundant because the createtime column is
>part of the composite index ix_createtime_name. If someone whee to
>seach based only on the createtime column would the composite index be
>as useful as the index on just the createtime column?
If there are a million rows with the same time, and you might have a
query "select * from example where createtime = @.t and name like
'foo%', then, um, is SQLServer smart enough to do its work on the
index in a case like that?
Only reason I can think of you might want that second.
Josh|||On 19 May 2006 11:06:19 -0700, pshroads@.gmail.com wrote:

>Thanks. Am I correct in thinking that it's only redundant because
>statistics are maintained on only the first column of an index? So if
>the composite index had been on (name, createtime) then it would not be
>redundant because the optimizer would be unlikely to choose that index
>when searching on createtime because there would be no statistics?
Hi pshroads,
You are right that the index on (createtime) would not be redundant if
the composite index had been on (name, createtime), but you're wrong
about the reason.
Suppose I give you a stack of paper with one-paper sized biographies of
all famous people of the last millenium, ordered by birthdate. I'll also
give you a much smaller stack of paper that includes just the name and
the page number(s) where a person with that name is described, sorted by
last name, then first name. And I give you another index that holds just
the first name and the pages where someone with that first name is
found.
What would your strategy be if I asked you for some details about
someone with first name "Kathlyn"?
Hugo Kornelis, SQL Server MVPsql

Is this index redundant?

Let's say I have a table:
create table example (
id int,
createtime datetime,
name varchar(20))
go
create index ix_createtime on example(createtime)
go
create index ix_createtime_name on example(createtime, name)
go
Is the index ix_createtime redundant because the createtime column is
part of the composite index ix_createtime_name. If someone whee to
seach based only on the createtime column would the composite index be
as useful as the index on just the createtime column?
Thanks<pshroads@.gmail.com> wrote in message
news:1148056676.401254.153400@.y43g2000cwc.googlegroups.com...
> Is the index ix_createtime redundant because the createtime column is
> part of the composite index ix_createtime_name. If someone whee to
> seach based only on the createtime column would the composite index be
> as useful as the index on just the createtime column?
Yes, it probably is redundant. There are a few very limited edge cases
where it might not be -- for instance, if you're doing a lot of analysis
based on only a single column and every I/O counts -- but they are very few
and very far between. The composite index will certainly work for a query
based only on the createtime column.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--|||Index ix_createtime is leaner than index ix_createtime_name. As such,
it is slightly more useful. However, its advantage is not too
noticeable, and under most circumstances you can safely drop it. you
can figure it out yourself, run your own tests, such as
select * from example where createtime between '20060101' and
'20060202'
there might be ranges for which access via ix_createtime is still
cheaper than scanning the whole clustered index, but access via
ix_createtime_name is already more expensive than table scan.
Good luck!|||Unless the table is read-only, you also need to consider maintenance costs.
With the redundant index, you will have extra work to do for almost every
modification to the table, and you'll need to determine if that cost is
worth the small benefit of having the narrower index for some queries.
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
<pshroads@.gmail.com> wrote in message
news:1148056676.401254.153400@.y43g2000cwc.googlegroups.com...
> Let's say I have a table:
> create table example (
> id int,
> createtime datetime,
> name varchar(20))
> go
> create index ix_createtime on example(createtime)
> go
> create index ix_createtime_name on example(createtime, name)
> go
> Is the index ix_createtime redundant because the createtime column is
> part of the composite index ix_createtime_name. If someone whee to
> seach based only on the createtime column would the composite index be
> as useful as the index on just the createtime column?
> Thanks
>|||Kalen,
I agree maintenance costs need to be taken in account. Yet I don't see
why you are calling the benefit of having a lean index "small" without
knowing much about the OP's system. I think it depends on the workload.
Suppose there is a query that runs very frequently. As such, small 10%
savings in its real execution costs may result in, say, 6% reduction of
overall workload for the whole system...|||Thanks. Am I correct in thinking that it's only redundant because
statistics are maintained on only the first column of an index? So if
the composite index had been on (name, createtime) then it would not be
redundant because the optimizer would be unlikely to choose that index
when searching on createtime because there would be no statistics?|||How will said "lean" index be used? I don't think dropping 20 bytes from an
index like this will result in "10% savings" in 99% of the cases, because
most likely a query will involve more than just the datetime column. Keep
in mind that bookmark lookups are much more expensive, in most cases, than
the additional I/Os that would be incurred reading the larger index pages.
We of course don't know what the clustering key is, or any other information
about the use, so this is all speculation, but unless this is a very
specialized situation I'd venture a guess that having both indexes is not
beneficial.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Alexander Kuznetsov" <AK_TIREDOFSPAM@.hotmail.COM> wrote in message
news:1148061719.429023.48180@.i40g2000cwc.googlegroups.com...
> Kalen,
> I agree maintenance costs need to be taken in account. Yet I don't see
> why you are calling the benefit of having a lean index "small" without
> knowing much about the OP's system. I think it depends on the workload.
> Suppose there is a query that runs very frequently. As such, small 10%
> savings in its real execution costs may result in, say, 6% reduction of
> overall workload for the whole system...
>|||well I'm not speaking in terms of 99% of the cases or 99.999% of the
case or whatever else. I am speaking about one particular system we
know almost nothing about - the OP's system. Suppose that system is
very simple and the primary goal of that simple system is to run
something like this:
select count(distinct id) from example where createtime between
@.fromdate and @.todate
as fast as possible. In that case the savings would be substantial, as
there would be no bookmark lookups. However rare, such cases do exist.
That's why I was trying to encourage the original poster to benchmark
by himself rather than rely on our advices. I think in our trade all
rules have exceptions.
That's all I was trying to say...|||On 19 May 2006 09:37:56 -0700, pshroads@.gmail.com wrote:
>Let's say I have a table:
>create table example (
> id int,
> createtime datetime,
> name varchar(20))
>go
>create index ix_createtime on example(createtime)
>go
>create index ix_createtime_name on example(createtime, name)
>go
>Is the index ix_createtime redundant because the createtime column is
>part of the composite index ix_createtime_name. If someone whee to
>seach based only on the createtime column would the composite index be
>as useful as the index on just the createtime column?
If there are a million rows with the same time, and you might have a
query "select * from example where createtime = @.t and name like
'foo%', then, um, is SQLServer smart enough to do its work on the
index in a case like that?
Only reason I can think of you might want that second.
Josh|||On 19 May 2006 11:06:19 -0700, pshroads@.gmail.com wrote:
>Thanks. Am I correct in thinking that it's only redundant because
>statistics are maintained on only the first column of an index? So if
>the composite index had been on (name, createtime) then it would not be
>redundant because the optimizer would be unlikely to choose that index
>when searching on createtime because there would be no statistics?
Hi pshroads,
You are right that the index on (createtime) would not be redundant if
the composite index had been on (name, createtime), but you're wrong
about the reason.
Suppose I give you a stack of paper with one-paper sized biographies of
all famous people of the last millenium, ordered by birthdate. I'll also
give you a much smaller stack of paper that includes just the name and
the page number(s) where a person with that name is described, sorted by
last name, then first name. And I give you another index that holds just
the first name and the pages where someone with that first name is
found.
What would your strategy be if I asked you for some details about
someone with first name "Kathlyn"?
--
Hugo Kornelis, SQL Server MVP|||I suppose it would be to look up the name Kathlyn in the third index
and get the page number and then go to that page number in the large
stack. Perhaps I'm being dense but I still need the reason explained :)
Thanks!|||On 19 May 2006 16:12:32 -0700, pshroads@.gmail.com wrote:
>I suppose it would be to look up the name Kathlyn in the third index
>and get the page number and then go to that page number in the large
>stack. Perhaps I'm being dense but I still need the reason explained :)
>Thanks!
Hi pshroads,
The first stack of paper equates to the table. The second stack equates
to an index on (last name, first name). And the third stack equates to
an index on only (first name). You'll use the index on (first name) to
find all people with first name Kathlyn, and you'll use the index on
(last name, first name) to find all people with last name Harrison, or
all people named George Washington. Both indexes are useful, without
being redundant.
Now replace the index on (first name) with an index on (last name) [i.e.
a list of last names and page numbers where these last names are
mentioned]. You'll still use the (last name, first name) index for
finding George Washington. You could use the new (last name) index to
locate every Mr or Mrs Harrison, but you can still do that with the
(last name, first name) index as well. And when looking for Kathlyn,
your only option is to sit down and flip each and every page of the
biggest stack of papers (the infamous table scan). So now, you have an
index that is only usable for a kind of search that can also be done
with the other index - clearly the (last name) index is redundant.
Database searches work exactly the same as yoour searches in those
stacks of paper. The only difference is that the computer turns the
pages somewhat faster. :-)
--
Hugo Kornelis, SQL Server MVP

Is there such thing as cross-table index or something?

CREATE TABLE [SalesForecast] (
[SaleDate] [datetime] NOT NULL ,
[CustID] [varchar] (10) NOT NULL ,
[F1] [money] NOT NULL
) ON [PRIMARY]
CREATE TABLE [Sales] (
[SaleDate] [datetime] NOT NULL ,
[CustID] [varchar] (10) NOT NULL ,
[S1] [money] NOT NULL
) ON [PRIMARY]
CREATE NONCLUSTERED INDEX SalesForecast_CustID ON SalesForecast (CustID)
CREATE NONCLUSTERED INDEX Sales_CustID ON Sales (CustID)
CREATE NONCLUSTERED INDEX SalesForecast_SaleDate ON SalesForecast (SaleDate)
CREATE NONCLUSTERED INDEX Sales_SaleDate ON Sales (SaleDate)
When I mix a query with both tables like this its really slow:
SELECT A.S1 * B.F1
FROM Sales A, SalesForecast B
WHERE A.SaleDate = B.SaleDate
AND A.CustID = B.CustID
AND A.SaleDate > '20050101'
What am I doing wrong, this seems to be correct..It would help to have a clustered index on the date column. You don't look
like you have any clustered indexes at all, which is not a good practice.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Rich" <no@.spam.invalid> wrote in message
news:44HGe.53673$4o.23499@.fed1read06...
> CREATE TABLE [SalesForecast] (
> [SaleDate] [datetime] NOT NULL ,
> [CustID] [varchar] (10) NOT NULL ,
> [F1] [money] NOT NULL
> ) ON [PRIMARY]
> CREATE TABLE [Sales] (
> [SaleDate] [datetime] NOT NULL ,
> [CustID] [varchar] (10) NOT NULL ,
> [S1] [money] NOT NULL
> ) ON [PRIMARY]
>
> CREATE NONCLUSTERED INDEX SalesForecast_CustID ON SalesForecast (CustID)
> CREATE NONCLUSTERED INDEX Sales_CustID ON Sales (CustID)
> CREATE NONCLUSTERED INDEX SalesForecast_SaleDate ON SalesForecast
> (SaleDate)
> CREATE NONCLUSTERED INDEX Sales_SaleDate ON Sales (SaleDate)
>
> When I mix a query with both tables like this its really slow:
> SELECT A.S1 * B.F1
> FROM Sales A, SalesForecast B
> WHERE A.SaleDate = B.SaleDate
> AND A.CustID = B.CustID
> AND A.SaleDate > '20050101'
> What am I doing wrong, this seems to be correct..
>|||What are the keys in each case? Do you have any? A unique key could
make a significant difference.
David Portas
SQL Server MVP
--|||Do not use the proprietary MONEY data type. Declare a primary key for
the tables. Most codes are fixed length to make them easier to validate
so I find it hard to believe that your customer id is really VARCHAR(n)
CREATE TABLE SalesForecast
(sale_date DATETIME NOT NULL,
cust_id CHAR(10) NOT NULL,
forecast_amt DECIMAL (12,4) NOT NULL,
PRIMARY KEY (sale_date, cust_id));
CREATE TABLE Sales
(sale_date DATETIME NOT NULL,
cust_id CHAR(10) NOT NULL,
sales_amt DECIMAL (12,4) NOT NULL,
PRIMARY KEY (sale_date, cust_id));
This gives you a covering index for your query. If the join is still
slow, use a clustered option on the keys.|||Thanks I'll give that a try. CustID is anywhere from 6 to 10 characters
long.. Most of the time it is 6, but sometimes it is 9 or 10! Should I still
use char instead of varchar?
Thanks.
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1122732638.691124.46520@.g14g2000cwa.googlegroups.com...
> Do not use the proprietary MONEY data type. Declare a primary key for
> the tables. Most codes are fixed length to make them easier to validate
> so I find it hard to believe that your customer id is really VARCHAR(n)
> CREATE TABLE SalesForecast
> (sale_date DATETIME NOT NULL,
> cust_id CHAR(10) NOT NULL,
> forecast_amt DECIMAL (12,4) NOT NULL,
> PRIMARY KEY (sale_date, cust_id));
> CREATE TABLE Sales
> (sale_date DATETIME NOT NULL,
> cust_id CHAR(10) NOT NULL,
> sales_amt DECIMAL (12,4) NOT NULL,
> PRIMARY KEY (sale_date, cust_id));
> This gives you a covering index for your query. If the join is still
> slow, use a clustered option on the keys.
>

Is there anyway to drop and re create all the index of a database ???

I wanna do that without usinf DBCC REINDEX
ThanksYou can write your own script which uses a couple of loops and the system tables and then drop and
create the indexes. But it gets more complicated as some indexes are created by constraints.
In fact, DBREINDEX uses the same code internally in SQL Server as CREATE INDEX, so why don't you
want to use DBREINDEX?
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Rubens Naves" <rubensnaves@.hotmail.comm> wrote in message
news:%231baqYJxDHA.556@.TK2MSFTNGP11.phx.gbl...
> I wanna do that without usinf DBCC REINDEX
> Thanks
>

Is there any syntax like this in SQL Server ?

Query :
select * from Item1 a (NOLOCK, Index(ItemIn1))
join Item2 b (NOLOCK, Index(ItemData1))
Please explain the above nolock etc after the alias in the given query. What it does ?
Sam.They are table hints (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/instsql/in_backcomp_6rzt.asp), basically user supplied suggestions for how the optimizer ought to process the table.

NOLOCK requests that no new locks be placed, and existing locks be ignored. In my opinion, NOLOCK is a simple way to get wrong answers quickly.

Index(foo) requests that the optimizer select the index named foo on this table for processing the query. This is almost always a sign of poorly maintained database statistics or a brute force attempt to speed up a particular query. Maybe one instance in 500 is actually needed/helpful because of oddly skewed data that the developer understands but that the optimizer isn't smart enough to recognize.

As you've probably guessed, I don't think highly of table hints. There were times that they were absolutely crucial to getting work done. There are still times that they are helpful. In the VAST majority of cases with SQL-2000, while they might help in a specific circumstance, they are actually counter-productive in the long run.

-PatP|||Well, from what I've seen (quite a bit actually) about 90% of all SELECTs in a business system are or should be done with the lowest possible transaction isolation level. Reading COMMITTED ONLY data is absolutely required in situations where the most recent data vs. data being altered makes the most sense. But what is the true definition of "the most recent data"? Is it when the users clicks on OK button? If that's the case, than the result that could have been produced a split of a second earlier is different from the result that was actually acquired which in turn may be different from the result that would have been acquired if the user waited for another second...I don't believe in dogmatic approach to isolation levels, it all needs to be carefully tailored to the business needs and the way of application usage.|||See Pat...I thought this was a test or interview question...

especially since it didn't have a real example...

So how would they have know to get it syntactically correct?|||Well, from what I've seen (quite a bit actually) about 90% of all SELECTs in a business system are or should be done with the lowest possible transaction isolation level.Granted, but it should be done at the spid level using SET TRANSACTION ISOLATION LEVEL (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_set-set_74bw.asp), not for one query in a batch or even worse than that for one table within a query.

I think that you and I are on the "same page" here, but that is a tough concept to explain to someone asking what NOLOCK means. As long as the isolation level is applied consistantly for a transaction, it can be done safely. I don't agree that reading uncommitted data is anywhere near safe, but that's because I've spent literally months proving out statements that were corrupted due to the big rush at the end of the year where people were frantically entering and querying data... Those kind of memories tend to stick with me!

I feel very strongly that changing the isolation level for a single table within a query is a receipe for disaster. Every time I've found it, it has been a culprit.

-PatP|||Please explain to me how the example below may be used as a "receipe for disaster"? And activities like this one are the most typical for cut-over manufacturing+sales analysis for the day ;)

select o.order_id, o.order_date, o.customer_id, c.customer_name, d.item_id, im.item_description
from orders o (/*!!!*/TABLOCK/*!!!*/)
inner join customers c (/*!!!*/NOLOCK/*!!!*/)
on o.customer_id = c.customer_id
inner join order_detail d (/*!!!*/NOLOCK/*!!!*/)
on o.order_id = d.order_id
inner join item_master im (/*!!!*/NOLOCK/*!!!*/)
on d.item_id = im.item_id
where ship_date = convert(char(10), getdate(), 101)|||The easiest way to imagine a disaster is to think about how this kind of query is normally used. The "batch rollover" typically happens at a point in time when there is very little or no user interaction with the system... Other batch updates are often in progress. This transaction takes a full table lock on the orders table, but it does not take or respect any other locks.

Several customers that were input today got their names switched during the entry process. Two vendors sent updated product descriptions that need to be incorporated into your inventory master. As all these queries are running, you get everything absolutely correct from the orders table, but only some of the customer and item master information is corrupted... This can take months to find, but it destroys the user's faith in the system.

A more technical and more insideous problem is the "phantom deadlock"... If one of these queries is run by a scheduled task (with no UI) and another is run by a user task (with a UI) and both of them have existing locks on different tables that this query accesses with NOLOCK, even though it shouldn't produce a deadlock, on multiple CPU machines it can still produce one (unless you set MAXDOP to 1). This can cause a spooky error that is nearly impossible to resolve because of its timing sensitivity.

There are more ways to hurt yourself with this kind of programming practice, but they get progressively more difficult to reproduce.

Using NOLOCK with UPDATE statements is actually a larger problem, because it can cause code to break in ways that you can't reproduce. This is more visible to the average user, so it draws more attention and further deteriorates user faith in your application.

I don't mind running low isolation levels for "yardstick" queries. When you need a quick guestimate of today's volume, they are great. When you need to balance, to the penny, with absolutely accurate data, they are just problems waiting to happen. Think about how you'd feel about your bank if they promised you statements that were "pretty close, most of the time"!

-PatP|||Sorry, can't write such large posts, very little memory and information retention. But just to pick the bone, - phantom deadlock?? How can it happen with tables that don't have any footprint in syslocks? It's called PARANOIA, Pat. Relax, it must be something else that you're mistaking for the reasult of TABLOCK/NOLOCK combination ;)|||That's what the first two MS-PSS folks thought... My TAM was confident enough in me and my observations to push the issue until we reached a developer. After a LOT of work, we figured out how it could happen and what they had to change so we could remove the MAXDOP hint and have the code work without "assistance".

-PatP