Difference Between Clustered Index and non Clustered Index

hi all, can u tell me what is basic difference beween
clusterd index and non clusterd index?
Thanks in advance
hrishikeshHello Hrishikesh !
Quioted from DBA-Forum:
--
Assuming SQL 7 or 2K:
A clustered index orders the table and is loaded into memory. SQL Svr
accesses data in the table by directly accessing the Index.
A non clustered index does NOT order the data. It establishes a list of the
locations of the data pages containing the column(s) of the index.
So a clustered index organizes the data for maximum efficient access and a
non clustered index does not.
If you create an index on a field that is NOT in the WHERE clause of a
query, you gain absolutely nothing. Actually, you take a hit because you are
wasting drive space, query time and memory space on a useless index.
If you create an index (Clustered or non clustered) that IS in the WHERE
clause, you will gain a benefit because the index points to the data.
--
HTH, Jens Süßmeyer.|||BOL says:
A clustered index determines the physical order of data in a table. A
clustered index is analogous to a telephone directory, which arranges data
by last name. Because the clustered index dictates the physical storage
order of the data in the table, a table can contain only one clustered
index. However, the index can comprise multiple columns (a composite index),
like the way a telephone directory is organized by last name and first name.
Nonclustered indexes have the same B-tree structure as clustered indexes,
with two significant differences:
a.. The data rows are not sorted and stored in order based on their
nonclustered keys.
b.. The leaf layer of a nonclustered index does not consist of the data
pages.
"Hrishikesh Musale" <musaleh@.mahindrabt.com> wrote in message
news:0da301c36d4f$2ca746f0$a101280a@.phx.gbl...
> hi all, can u tell me what is basic difference beween
> clusterd index and non clusterd index?
> Thanks in advance
> hrishikeshsql

Difference about running and runnable

Hi,
Anybody know the difference about running and runnable when I execute sp_who
?
Thanks in advance,
VitorFrom what I know the status column of sysprocesses table can have one of the
following values:
Status Meaning
------
Background SPID is performing a background task.
Sleeping SPID is not currently executing. This usually indicates
that the SPID is awaiting a command from the application.
Runnable SPID is currently executing.
Dormant Same as Sleeping, except Dormant also indicates that the
SPID has been reset after completing an RPC event. The reset cleans up
resources used during the RPC event. This is a normal state and the SPID is
available and waiting to execute further commands.
Rollback The SPID is in rollback of a transaction.
Defwakeup Indicates that a SPID is waiting on a resource that is in
the process of being freed. The waitresource field should indicate the
resource in question.
Spinloop Process is waiting while attempting to acquire a
spinlock used for concurrency control on SMP systems.
I found a description for "Running" status in a Sybase Manual:
"running:Actively running on one of the server engines" and in the same
manual "runnable: In the queue of runnable processes".
"Killing processes"
http://manuals.sybase.com/onlinebooks/group-as/asg1250e/sag/@.Generic__BookTextView/5162
HTH,
Cristian Lefter, SQL Server MVP
"Vitor Mauricio de N. Silva" <vitor_mauricio@.terra.com.br> wrote in message
news:u3A6edKXFHA.1384@.TK2MSFTNGP09.phx.gbl...
> Hi,
> Anybody know the difference about running and runnable when I execute
> sp_who ?
> Thanks in advance,
> Vitor
>

Development Examples for SQL Server 2000

Can you point me in direction of Applicaiton examples for SQL Server 2000?
Thanks in Advance,
Marcel K, MCDBAwhat exactly are you asking ?
Examples of Applications that USE Sql Server ?
Greg Jackson
PDX, Oregon

dettache fulltext

Is it possible like Sybase to have the fulltext engine on another host ? If
yes how to proceed, if not why ?
Thanks by advance for your responses.ljbx,
You can't put the catalogs on a separate server, however you can put
them on separate RAID arrays, or on a LUN on your SAN. This might be a
good way to improve performance.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
ljbx wrote:
> Is it possible like Sybase to have the fulltext engine on another host ? I
f
> yes how to proceed, if not why ?
> Thanks by advance for your responses.
>

dettache fulltext

Is it possible like Sybase to have the fulltext engine on another host ? If
yes how to proceed, if not why ?
Thanks by advance for your responses.
ljbx,
You can't put the catalogs on a separate server, however you can put
them on separate RAID arrays, or on a LUN on your SAN. This might be a
good way to improve performance.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
ljbx wrote:
> Is it possible like Sybase to have the fulltext engine on another host ? If
> yes how to proceed, if not why ?
> Thanks by advance for your responses.
>

dettache fulltext

Is it possible like Sybase to have the fulltext engine on another host ? If
yes how to proceed, if not why ?
Thanks by advance for your responses.ljbx,
You can't put the catalogs on a separate server, however you can put
them on separate RAID arrays, or on a LUN on your SAN. This might be a
good way to improve performance.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
ljbx wrote:
> Is it possible like Sybase to have the fulltext engine on another host ? If
> yes how to proceed, if not why ?
> Thanks by advance for your responses.
>

Determining the nature of partitioned tables

I apologize in advance if this is something obvious I've missed ... fresh eyes/brain and all that.

If I have a table that is using a particular partition scheme/function, is there a quick and easy way to determine which column of that table is being used for partitioning? We're examining a number of legacy structures and we're hoping to reduce the time it's going to take us to get the report management wants.

Thanks.

The partition column information is saved in sys.index_columns catalog view. use the following script to check the partition scheme and column:

select c.name AS PartitionColumn,ic.partition_ordinal AS PartitionColOrder,dsidx.[name] AS PartitionScheme
from sys.indexes AS idx, sys.columns c,
sys.index_columns ic, sys.data_spaces AS dsidx
where idx.[object_id]=object_id('dbo.YouTable')
and idx.index_id<2
and idx.[object_id]=ic.[object_id]
and idx.index_id=ic.index_id
and dsidx.data_space_id = idx.data_space_id
and dsidx.type=N'PS'
and c.object_id=ic.[object_id]
and c.column_id=ic.column_id
and ic.partition_ordinal>0

Determining the nature of partitioned tables

I apologize in advance if this is something obvious I've missed ... fresh eyes/brain and all that.

If I have a table that is using a particular partition scheme/function, is there a quick and easy way to determine which column of that table is being used for partitioning? We're examining a number of legacy structures and we're hoping to reduce the time it's going to take us to get the report management wants.

Thanks.

The partition column information is saved in sys.index_columns catalog view. use the following script to check the partition scheme and column:

select c.name AS PartitionColumn,ic.partition_ordinal AS PartitionColOrder,dsidx.[name] AS PartitionScheme
from sys.indexes AS idx, sys.columns c,
sys.index_columns ic, sys.data_spaces AS dsidx
where idx.[object_id]=object_id('dbo.YouTable')
and idx.index_id<2
and idx.[object_id]=ic.[object_id]
and idx.index_id=ic.index_id
and dsidx.data_space_id = idx.data_space_id
and dsidx.type=N'PS'
and c.object_id=ic.[object_id]
and c.column_id=ic.column_id
and ic.partition_ordinal>0

Determining numbers of rows affected in advance

Hi,
When workinf on a SQL2005 Db through Sql Server Management studio, is
there a way to determine in advance how many rows will be affected as
a result of an Update,Insert,or Delete statement without actually
performing the query?
thanks "in advance"
> When workinf on a SQL2005 Db through Sql Server Management studio, is
> there a way to determine in advance how many rows will be affected as
> a result of an Update,Insert,or Delete statement without actually
> performing the query?
No, but you can execute the DML in a transaction, select @.@.ROWCOUNT and then
rollback. Another method is to determine the number of rows that will be
affected is to change the DML to a SELECT COUNT(*) query.
Hope this helps.
Dan Guzman
SQL Server MVP
"Aamir Ghanchi" <aamirghanchi@.gmail.com> wrote in message
news:6b8c041a-2093-43c4-b294-3f4b908f36c3@.r60g2000hsc.googlegroups.com...
> Hi,
> When workinf on a SQL2005 Db through Sql Server Management studio, is
> there a way to determine in advance how many rows will be affected as
> a result of an Update,Insert,or Delete statement without actually
> performing the query?
> thanks "in advance"
|||Aamir
I'm not sure what you mean by 'through SQL Server Management Studio' but I
strongly recommend you don't do any data modification through a graphical
window, but always do it through TSQL code, using a query window or
application. You have much more control, and the ability to use the
techniques Dan suggested.
If you only want an estimate, you could also just look at the estimated
query plan after entering your query in a query window. You can get this
plan with Cntl-L
If you hold your cursor over the estimated plan's first icon, it should
display the estimated number of rows to be returned.
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"Aamir Ghanchi" <aamirghanchi@.gmail.com> wrote in message
news:6b8c041a-2093-43c4-b294-3f4b908f36c3@.r60g2000hsc.googlegroups.com...
> Hi,
> When workinf on a SQL2005 Db through Sql Server Management studio, is
> there a way to determine in advance how many rows will be affected as
> a result of an Update,Insert,or Delete statement without actually
> performing the query?
> thanks "in advance"
|||The SELECT COUNT(*) method can be much more efficient than your actual DML
statement because it will probably (hopefully!!) have a very tight, cheap
query plan using indexes since it just needs a count. And if you then DO
choose to run the actual statement then these pages will already have been
pulled into RAM hopefully making the run of the DML faster. Note that this
is still only recommended if for some reason you really do need the count
prior to the execution.
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:34560279-FE9B-4EA1-BBBF-36ABFCB77F22@.microsoft.com...
> No, but you can execute the DML in a transaction, select @.@.ROWCOUNT and
> then rollback. Another method is to determine the number of rows that
> will be affected is to change the DML to a SELECT COUNT(*) query.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Aamir Ghanchi" <aamirghanchi@.gmail.com> wrote in message
> news:6b8c041a-2093-43c4-b294-3f4b908f36c3@.r60g2000hsc.googlegroups.com...
>
|||But for anything but really trivial quries, that optimizer estimate is
probably so off that it may not be very useful.
Linchi
"Kalen Delaney" wrote:

> Aamir
> I'm not sure what you mean by 'through SQL Server Management Studio' but I
> strongly recommend you don't do any data modification through a graphical
> window, but always do it through TSQL code, using a query window or
> application. You have much more control, and the ability to use the
> techniques Dan suggested.
> If you only want an estimate, you could also just look at the estimated
> query plan after entering your query in a query window. You can get this
> plan with Cntl-L
> If you hold your cursor over the estimated plan's first icon, it should
> display the estimated number of rows to be returned.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://blog.kalendelaney.com
>
> "Aamir Ghanchi" <aamirghanchi@.gmail.com> wrote in message
> news:6b8c041a-2093-43c4-b294-3f4b908f36c3@.r60g2000hsc.googlegroups.com...
>
>
|||Well, I did say it was only an estimate. :-)
It may be way off, but it might not be. I think it would be better than
nothing, and perhaps better than completely running the whole query just to
get a rowcount, depending on how exact the OP needs the number to be.
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:237D20BB-D329-464B-974E-D74B48C80E6B@.microsoft.com...[vbcol=seagreen]
> But for anything but really trivial quries, that optimizer estimate is
> probably so off that it may not be very useful.
> Linchi
> "Kalen Delaney" wrote:
|||Thanks for all the responses.
And I'm sorry, should have clarified it. I am actually running a query
from query window in the management studio.
The estimate query plan was really way off and was in decimals ?
I liked the Rollback Transaction solution and it fits my needs. I know
it may be costly but I am not worried about that in my circumstances.
The Select and Selct Count statements are good but still not the same
as running the actual queries (some of them much complex with multiple
joins & subqueries)
Once again, thanks all.
On Jan 7, 12:21Xam, "Kalen Delaney" <replies@.public_newsgroups.com>
wrote:
> Well, I did say it was only an estimate. :-)
> It may be way off, but it might not be. I think it would be better than
> nothing, and perhaps better than completely running the whole query just to
> get a rowcount, depending on how exact the OP needs the number to be.
> --
> HTH
> Kalen Delaney, SQL Server MVPwww.InsideSQLServer.comhttp://blog.kalendelaney.com
> "Linchi Shea" <LinchiS...@.discussions.microsoft.com> wrote in message
> news:237D20BB-D329-464B-974E-D74B48C80E6B@.microsoft.com...
>
>
>
>
>
>
>
> - Show quoted text -

Determining numbers of rows affected in advance

Hi,
When workinf on a SQL2005 Db through Sql Server Management studio, is
there a way to determine in advance how many rows will be affected as
a result of an Update,Insert,or Delete statement without actually
performing the query?
thanks "in advance"> When workinf on a SQL2005 Db through Sql Server Management studio, is
> there a way to determine in advance how many rows will be affected as
> a result of an Update,Insert,or Delete statement without actually
> performing the query?
No, but you can execute the DML in a transaction, select @.@.ROWCOUNT and then
rollback. Another method is to determine the number of rows that will be
affected is to change the DML to a SELECT COUNT(*) query.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Aamir Ghanchi" <aamirghanchi@.gmail.com> wrote in message
news:6b8c041a-2093-43c4-b294-3f4b908f36c3@.r60g2000hsc.googlegroups.com...
> Hi,
> When workinf on a SQL2005 Db through Sql Server Management studio, is
> there a way to determine in advance how many rows will be affected as
> a result of an Update,Insert,or Delete statement without actually
> performing the query?
> thanks "in advance"|||Aamir
I'm not sure what you mean by 'through SQL Server Management Studio' but I
strongly recommend you don't do any data modification through a graphical
window, but always do it through TSQL code, using a query window or
application. You have much more control, and the ability to use the
techniques Dan suggested.
If you only want an estimate, you could also just look at the estimated
query plan after entering your query in a query window. You can get this
plan with Cntl-L
If you hold your cursor over the estimated plan's first icon, it should
display the estimated number of rows to be returned.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"Aamir Ghanchi" <aamirghanchi@.gmail.com> wrote in message
news:6b8c041a-2093-43c4-b294-3f4b908f36c3@.r60g2000hsc.googlegroups.com...
> Hi,
> When workinf on a SQL2005 Db through Sql Server Management studio, is
> there a way to determine in advance how many rows will be affected as
> a result of an Update,Insert,or Delete statement without actually
> performing the query?
> thanks "in advance"|||The SELECT COUNT(*) method can be much more efficient than your actual DML
statement because it will probably (hopefully!!) have a very tight, cheap
query plan using indexes since it just needs a count. And if you then DO
choose to run the actual statement then these pages will already have been
pulled into RAM hopefully making the run of the DML faster. Note that this
is still only recommended if for some reason you really do need the count
prior to the execution.
--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:34560279-FE9B-4EA1-BBBF-36ABFCB77F22@.microsoft.com...
>> When workinf on a SQL2005 Db through Sql Server Management studio, is
>> there a way to determine in advance how many rows will be affected as
>> a result of an Update,Insert,or Delete statement without actually
>> performing the query?
> No, but you can execute the DML in a transaction, select @.@.ROWCOUNT and
> then rollback. Another method is to determine the number of rows that
> will be affected is to change the DML to a SELECT COUNT(*) query.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Aamir Ghanchi" <aamirghanchi@.gmail.com> wrote in message
> news:6b8c041a-2093-43c4-b294-3f4b908f36c3@.r60g2000hsc.googlegroups.com...
>> Hi,
>> When workinf on a SQL2005 Db through Sql Server Management studio, is
>> there a way to determine in advance how many rows will be affected as
>> a result of an Update,Insert,or Delete statement without actually
>> performing the query?
>> thanks "in advance"
>|||But for anything but really trivial quries, that optimizer estimate is
probably so off that it may not be very useful.
Linchi
"Kalen Delaney" wrote:
> Aamir
> I'm not sure what you mean by 'through SQL Server Management Studio' but I
> strongly recommend you don't do any data modification through a graphical
> window, but always do it through TSQL code, using a query window or
> application. You have much more control, and the ability to use the
> techniques Dan suggested.
> If you only want an estimate, you could also just look at the estimated
> query plan after entering your query in a query window. You can get this
> plan with Cntl-L
> If you hold your cursor over the estimated plan's first icon, it should
> display the estimated number of rows to be returned.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://blog.kalendelaney.com
>
> "Aamir Ghanchi" <aamirghanchi@.gmail.com> wrote in message
> news:6b8c041a-2093-43c4-b294-3f4b908f36c3@.r60g2000hsc.googlegroups.com...
> > Hi,
> >
> > When workinf on a SQL2005 Db through Sql Server Management studio, is
> > there a way to determine in advance how many rows will be affected as
> > a result of an Update,Insert,or Delete statement without actually
> > performing the query?
> >
> > thanks "in advance"
>
>|||Well, I did say it was only an estimate. :-)
It may be way off, but it might not be. I think it would be better than
nothing, and perhaps better than completely running the whole query just to
get a rowcount, depending on how exact the OP needs the number to be.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:237D20BB-D329-464B-974E-D74B48C80E6B@.microsoft.com...
> But for anything but really trivial quries, that optimizer estimate is
> probably so off that it may not be very useful.
> Linchi
> "Kalen Delaney" wrote:
>> Aamir
>> I'm not sure what you mean by 'through SQL Server Management Studio' but
>> I
>> strongly recommend you don't do any data modification through a graphical
>> window, but always do it through TSQL code, using a query window or
>> application. You have much more control, and the ability to use the
>> techniques Dan suggested.
>> If you only want an estimate, you could also just look at the estimated
>> query plan after entering your query in a query window. You can get this
>> plan with Cntl-L
>> If you hold your cursor over the estimated plan's first icon, it should
>> display the estimated number of rows to be returned.
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> www.InsideSQLServer.com
>> http://blog.kalendelaney.com
>>
>> "Aamir Ghanchi" <aamirghanchi@.gmail.com> wrote in message
>> news:6b8c041a-2093-43c4-b294-3f4b908f36c3@.r60g2000hsc.googlegroups.com...
>> > Hi,
>> >
>> > When workinf on a SQL2005 Db through Sql Server Management studio, is
>> > there a way to determine in advance how many rows will be affected as
>> > a result of an Update,Insert,or Delete statement without actually
>> > performing the query?
>> >
>> > thanks "in advance"
>>|||Thanks for all the responses.
And I'm sorry, should have clarified it. I am actually running a query
from query window in the management studio.
The estimate query plan was really way off and was in decimals ?
I liked the Rollback Transaction solution and it fits my needs. I know
it may be costly but I am not worried about that in my circumstances.
The Select and Selct Count statements are good but still not the same
as running the actual queries (some of them much complex with multiple
joins & subqueries)
Once again, thanks all.
On Jan 7, 12:21=A0am, "Kalen Delaney" <replies@.public_newsgroups.com>
wrote:
> Well, I did say it was only an estimate. :-)
> It may be way off, but it might not be. I think it would be better than
> nothing, and perhaps better than completely running the whole query just t=o
> get a rowcount, depending on how exact the OP needs the number to be.
> --
> HTH
> Kalen Delaney, SQL Server MVPwww.InsideSQLServer.comhttp://blog.kalendelan=
ey.com
> "Linchi Shea" <LinchiS...@.discussions.microsoft.com> wrote in message
> news:237D20BB-D329-464B-974E-D74B48C80E6B@.microsoft.com...
>
> > But for anything but really trivial quries, that optimizer estimate is
> > probably so off that it may not be very useful.
> > Linchi
> > "Kalen Delaney" wrote:
> >> Aamir
> >> I'm not sure what you mean by 'through SQL Server Management Studio' bu=t
> >> I
> >> strongly recommend you don't do any data modification through a graphic=al
> >> window, but always do it through TSQL code, using a query window or
> >> application. You have much more control, and the ability to use the
> >> techniques Dan suggested.
> >> If you only want an estimate, you could also just look at the estimated=
> >> query plan after entering your query in a query window. You can get thi=s
> >> plan with Cntl-L
> >> If you hold your cursor over the estimated plan's first icon, it should=
> >> display the estimated number of rows to be returned.
> >> --
> >> HTH
> >> Kalen Delaney, SQL Server MVP
> >>www.InsideSQLServer.com
> >>http://blog.kalendelaney.com
> >> "Aamir Ghanchi" <aamirghan...@.gmail.com> wrote in message
> >>news:6b8c041a-2093-43c4-b294-3f4b908f36c3@.r60g2000hsc.googlegroups.com..=.
> >> > Hi,
> >> > When workinf on a SQL2005 Db through Sql Server Management studio, is=
> >> > there a way to determine in advance how many rows will be affected as=
> >> > a result of an Update,Insert,or Delete statement without actually
> >> > performing the query?
> >> > thanks "in advance"- Hide quoted text -
> - Show quoted text -