difference between dbreindex and drop existing clause of create index

Hi all,
Could any one of you tell me if there is any difference
between dbcc dbreindex and create index with drop_existing
option for a table with one clustered index and multiple
non clustered indexes..?
Also, please let me know what is the best way to reduce
fragmentation in tables that do not have non-clustered
indexes...?bharath,
A light view of the differences can be found at:
Rebuilding an Index
http://msdn.microsoft.com/library/default.asp?
url=/library/en-us/createdb/cm_8_des_05_271x.asp
For rebuilding several indexes I would stick with DBCC as a general rule.
The create index ... drop_existing requires that you have all the metadata
for the indexes in your script. The DBCC requires you to know nothing more
than the table that you are concerned with. As I understand it, both should
use parallel processing if available.
Russell Fields
"bharath" <barathsing@.hotmail.com> wrote in message
news:88ef01c3b57b$fdcc37d0$a601280a@.phx.gbl...
> Hi all,
> Could any one of you tell me if there is any difference
> between dbcc dbreindex and create index with drop_existing
> option for a table with one clustered index and multiple
> non clustered indexes..?
> Also, please let me know what is the best way to reduce
> fragmentation in tables that do not have non-clustered
> indexes...?

Difference b/w SPs & Functions

1. cannot hv tran in functions
2. cannot include SPs in select queries or in from clause whereas functions
can be included. (exception using OPENQUERY for including SP in from clause)
What other differences?Hi Rakesh
Difference between Stored Procedure and UDF:
Returning Parameters:
UDF Should return a parameter
Stored Procedure need not return any value
UDF Returns only one parameter
Stored Procedures can return more than one parameters
UDF can return a table
Stored Procedure returns a resultset
Usage
UDFs can be used a inline functions and can also be executed explicitly
eg: inline function: SELECT getdate()
Stored Procedures cannot be used as inline functions
Hope this helps you.
Please let me know if u have any questions
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
---
"Rakesh" wrote:

> 1. cannot hv tran in functions
> 2. cannot include SPs in select queries or in from clause whereas function
s
> can be included. (exception using OPENQUERY for including SP in from claus
e)
>
> What other differences?|||You cannot use INSERT/UPDATE/DELETE on base tables.
You cannot Use NonDeterministic functions inside UDF
A scalar valued user defined function can be used Anywhere a value is
expected, as in WHERE clause, check contraint etc.
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"Rakesh" <Rakesh@.discussions.microsoft.com> wrote in message
news:8DDCFBA3-C85D-47A4-A2CE-CDC22EEA364F@.microsoft.com...
> 1. cannot hv tran in functions
> 2. cannot include SPs in select queries or in from clause whereas
> functions
> can be included. (exception using OPENQUERY for including SP in from
> clause)
>
> What other differences?|||Rakesh
Visit at http://www.sql-server-performance.com/
"Rakesh" <Rakesh@.discussions.microsoft.com> wrote in message
news:8DDCFBA3-C85D-47A4-A2CE-CDC22EEA364F@.microsoft.com...
> 1. cannot hv tran in functions
> 2. cannot include SPs in select queries or in from clause whereas
> functions
> can be included. (exception using OPENQUERY for including SP in from
> clause)
>
> What other differences?|||"Chandra" wrote in message

> Difference between Stored Procedure and UDF:
> Returning Parameters:
> UDF Should return a parameter
> Stored Procedure need not return any value
Stored Procedures RETURN a value, even if you dont explicitly specify (0 by
default)

> UDF Returns only one parameter
> Stored Procedures can return more than one parameters
Stored Procedure can RETURN one one value and that must be integer.
You can use Output Parameters to get other datatypes back from the SP

> UDF can return a table
> Stored Procedure returns a resultset
Stored Procs can return multiple recordsets, UDF can return only one (table
valued udfs)

> UDFs can be used a inline functions and can also be executed explicitly
> eg: inline function: SELECT getdate()
What you mean by inline?

> Stored Procedures cannot be used as inline functions
EXEC yourvar= ProcName <ParamList>
Just plain Nitpicking :)
Regards
Roji. P. Thomas
http://toponewithties.blogspot.com

Differ bet WHERE clause & INNER JOIN?

In simple terms, if possible, what is the difference between using the WHERE clause in a SELECT statement vs an INNER JOIN? According to Rob Viera's book the WHERE is "inclusive" in nature, meaning that all records that meet the WHERE condition are included in the result set. The text further stated that an INNER JOIN is "exclusive" in nature meaning that data not meeting the JOIN condition is excluded from the result set.

In layman's terms, what is the difference? Any examples? Thanks in advance.

ddaveSo, I saw the question about Inner Join vs Where in Queries. I'm using all the "Joins" in clause where, is there problem to do it or I should be using Inner Join ?|||Linking tables should be done using a JOIN clause. Filtering on individual data elements should be done with WHERE clauses.

For simple queries, the SQL Server Optimizer will convert a statement like

select * from a, b where a.key = b.key

into

select * from a inner join b on a.key = b.key

...and will make optimium use of the indexes on the two tables to create an efficient execution plan. For more complicated queries the optimizer may not be able to make this conversion.

The JOIN syntax leads to better organized and more easily readable and debuggable code, because it clearly establishes the relationships between tables.|||Originally posted by blindman
...and will make optimium use of the indexes on the two tables to create an efficient execution plan. For more complicated queries the optimizer may not be able to make this conversion.


Ya think? Not so sure...but youcan test it and check out the plan the optimizer chooses by looking at the query plan

The JOIN syntax leads to better organized and more easily readable and debuggable code, because it clearly establishes the relationships between tables.

100%|||Not so sure it will reevalutate the simple query, or not so sure it won't be able to evaluate a complex query?|||I'm not sure that it will give you 2 different plans...have you seen this?|||It won't for simple queries. I've heard that it might not be able to for complex queries. At some point of complexity there must be a statement that it is beyond its capacity to reduce to simple join clauses. I mean, complexity is theoretically infinite, right? I suspect that it may have difficulty reevaluating WHERE clauses containing conditional syntax, but I haven't formally tested it.sql