I have a 2005 database with Auto Create Statistics set to true and Auto
Update Statistics set to true. Am I correct that it should not be necessary
for me to manually execute any 'Create Statistics' statements to create
statistics to help in query plan generation? My assumption is that with
these two statements set to true, SQL 2005 will create whatever statistics
it needs, and I don't need to create any.
Thanks, Amos.
There may be some exceptions but in general that is true.
Andrew J. Kelly SQL MVP
"Amos Soma" <amos_j_soma@.yahoo.com> wrote in message
news:ucZp4neJGHA.740@.TK2MSFTNGP12.phx.gbl...
>I have a 2005 database with Auto Create Statistics set to true and Auto
>Update Statistics set to true. Am I correct that it should not be necessary
>for me to manually execute any 'Create Statistics' statements to create
>statistics to help in query plan generation? My assumption is that with
>these two statements set to true, SQL 2005 will create whatever statistics
>it needs, and I don't need to create any.
> Thanks, Amos.
>
Showing posts with label statistics. Show all posts
Showing posts with label statistics. Show all posts
Thursday, March 22, 2012
Create Statistics Question
I have a 2005 database with Auto Create Statistics set to true and Auto
Update Statistics set to true. Am I correct that it should not be necessary
for me to manually execute any 'Create Statistics' statements to create
statistics to help in query plan generation? My assumption is that with
these two statements set to true, SQL 2005 will create whatever statistics
it needs, and I don't need to create any.
Thanks, Amos.There may be some exceptions but in general that is true.
--
Andrew J. Kelly SQL MVP
"Amos Soma" <amos_j_soma@.yahoo.com> wrote in message
news:ucZp4neJGHA.740@.TK2MSFTNGP12.phx.gbl...
>I have a 2005 database with Auto Create Statistics set to true and Auto
>Update Statistics set to true. Am I correct that it should not be necessary
>for me to manually execute any 'Create Statistics' statements to create
>statistics to help in query plan generation? My assumption is that with
>these two statements set to true, SQL 2005 will create whatever statistics
>it needs, and I don't need to create any.
> Thanks, Amos.
>
Update Statistics set to true. Am I correct that it should not be necessary
for me to manually execute any 'Create Statistics' statements to create
statistics to help in query plan generation? My assumption is that with
these two statements set to true, SQL 2005 will create whatever statistics
it needs, and I don't need to create any.
Thanks, Amos.There may be some exceptions but in general that is true.
--
Andrew J. Kelly SQL MVP
"Amos Soma" <amos_j_soma@.yahoo.com> wrote in message
news:ucZp4neJGHA.740@.TK2MSFTNGP12.phx.gbl...
>I have a 2005 database with Auto Create Statistics set to true and Auto
>Update Statistics set to true. Am I correct that it should not be necessary
>for me to manually execute any 'Create Statistics' statements to create
>statistics to help in query plan generation? My assumption is that with
>these two statements set to true, SQL 2005 will create whatever statistics
>it needs, and I don't need to create any.
> Thanks, Amos.
>
Create Statistics Question
I have a 2005 database with Auto Create Statistics set to true and Auto
Update Statistics set to true. Am I correct that it should not be necessary
for me to manually execute any 'Create Statistics' statements to create
statistics to help in query plan generation? My assumption is that with
these two statements set to true, SQL 2005 will create whatever statistics
it needs, and I don't need to create any.
Thanks, Amos.There may be some exceptions but in general that is true.
Andrew J. Kelly SQL MVP
"Amos Soma" <amos_j_soma@.yahoo.com> wrote in message
news:ucZp4neJGHA.740@.TK2MSFTNGP12.phx.gbl...
>I have a 2005 database with Auto Create Statistics set to true and Auto
>Update Statistics set to true. Am I correct that it should not be necessary
>for me to manually execute any 'Create Statistics' statements to create
>statistics to help in query plan generation? My assumption is that with
>these two statements set to true, SQL 2005 will create whatever statistics
>it needs, and I don't need to create any.
> Thanks, Amos.
>
Update Statistics set to true. Am I correct that it should not be necessary
for me to manually execute any 'Create Statistics' statements to create
statistics to help in query plan generation? My assumption is that with
these two statements set to true, SQL 2005 will create whatever statistics
it needs, and I don't need to create any.
Thanks, Amos.There may be some exceptions but in general that is true.
Andrew J. Kelly SQL MVP
"Amos Soma" <amos_j_soma@.yahoo.com> wrote in message
news:ucZp4neJGHA.740@.TK2MSFTNGP12.phx.gbl...
>I have a 2005 database with Auto Create Statistics set to true and Auto
>Update Statistics set to true. Am I correct that it should not be necessary
>for me to manually execute any 'Create Statistics' statements to create
>statistics to help in query plan generation? My assumption is that with
>these two statements set to true, SQL 2005 will create whatever statistics
>it needs, and I don't need to create any.
> Thanks, Amos.
>
Create Statistics on NonIndexed Column
Hello. Could someone explain to me why it might be desirable to create a
statistic on a non-indexed column?
Thank you."Amos Soma" <amos_j_soma@.yahoo.com> wrote in message
news:%23X4AxZWBGHA.3604@.TK2MSFTNGP09.phx.gbl...
> Hello. Could someone explain to me why it might be desirable to create a
> statistic on a non-indexed column?
> Thank you.
>
The more information the query optimizer has, the better decisions it can
make.
Having statistics on a column that is not part of an index may help it make
better decisions about join strategies, table-scans versus index
seeks/scans, bookmark lookups and so forth.
Rick Sawtell
MCT, MCSD, MCDBA|||Here are some examples to illustrate Rick's statement. Suppose you have
the following table:
CREATE TABLE Persons
(PersonID int not null PRIMARY KEY CLUSTERED
,LastName varchar(100) not null
,FirstName varchar(100) not null
,Address varchar(100) null
-- many other columns, very wide table
,CONSTRAINT UQ_Persons_Name UNIQUE (LastName, FirstName)
)
Now, the DDL above will create a nonclustered index on (LastName,
FirstName). Now suppose you have the following query:
SELECT *
FROM Persons
WHERE FirstName='Amos'
With the default table settings, SQL Server will auto create statistics
of column FirstName.
With these statistics, the optimizer can estimate how many rows there
are with FirstName 'Amos'. If this is a small percentage, then the
strategy would be to scan the nonclustered index and retrieve the
matching rows from the table (bookmark lookups). If it is a large
percentage, the clustered index will be scanned (basically a table
scan).
Without these statistics, SQL Server will optimizer for the worst case
situation, which in this case means scanning the table (clustered index
scan).
Another example. Suppose you also have this table:
-- just an example. Relation might not make sense
CREATE TABLE Hobbies
(HobbyID int not null PRIMARY KEY CLUSTERED
,Hobby varchar(100) not null UNIQUE
,Comments varchar(100) null
,PersonID int not null REFERENCES Persons
)
-- index on the foreign key
CREATE INDEX IX_Hobbies_PersonsID ON Hobbies(PersonID)
with this query:
SELECT Hobby
FROM Hobbies
INNER JOIN Persons
ON Persons.PersonID = Hobbies.PersonID
WHERE Address = 'Main Street'
AND Comments = 'Todo'
In this case statistics of the columns Persons.Address and
Hobbies.Comments can determine the access path for the query. If column
Comments has a high occurrence of 'Todo' and Address a low occurrence of
'Main Street', then the optimizer might choose to access (scan) the
Persons table first, and then lookup matching rows in Hobbies (and
filter out remaining rows). With different statistics the optimizer
might access (scan) the Hobbies table and then lookup the matching rows
in Persons (and filter out the remaining unwanted rows).
HTH,
Gert-Jan
Amos Soma wrote:
> Hello. Could someone explain to me why it might be desirable to create a
> statistic on a non-indexed column?
> Thank you.sql
statistic on a non-indexed column?
Thank you."Amos Soma" <amos_j_soma@.yahoo.com> wrote in message
news:%23X4AxZWBGHA.3604@.TK2MSFTNGP09.phx.gbl...
> Hello. Could someone explain to me why it might be desirable to create a
> statistic on a non-indexed column?
> Thank you.
>
The more information the query optimizer has, the better decisions it can
make.
Having statistics on a column that is not part of an index may help it make
better decisions about join strategies, table-scans versus index
seeks/scans, bookmark lookups and so forth.
Rick Sawtell
MCT, MCSD, MCDBA|||Here are some examples to illustrate Rick's statement. Suppose you have
the following table:
CREATE TABLE Persons
(PersonID int not null PRIMARY KEY CLUSTERED
,LastName varchar(100) not null
,FirstName varchar(100) not null
,Address varchar(100) null
-- many other columns, very wide table
,CONSTRAINT UQ_Persons_Name UNIQUE (LastName, FirstName)
)
Now, the DDL above will create a nonclustered index on (LastName,
FirstName). Now suppose you have the following query:
SELECT *
FROM Persons
WHERE FirstName='Amos'
With the default table settings, SQL Server will auto create statistics
of column FirstName.
With these statistics, the optimizer can estimate how many rows there
are with FirstName 'Amos'. If this is a small percentage, then the
strategy would be to scan the nonclustered index and retrieve the
matching rows from the table (bookmark lookups). If it is a large
percentage, the clustered index will be scanned (basically a table
scan).
Without these statistics, SQL Server will optimizer for the worst case
situation, which in this case means scanning the table (clustered index
scan).
Another example. Suppose you also have this table:
-- just an example. Relation might not make sense
CREATE TABLE Hobbies
(HobbyID int not null PRIMARY KEY CLUSTERED
,Hobby varchar(100) not null UNIQUE
,Comments varchar(100) null
,PersonID int not null REFERENCES Persons
)
-- index on the foreign key
CREATE INDEX IX_Hobbies_PersonsID ON Hobbies(PersonID)
with this query:
SELECT Hobby
FROM Hobbies
INNER JOIN Persons
ON Persons.PersonID = Hobbies.PersonID
WHERE Address = 'Main Street'
AND Comments = 'Todo'
In this case statistics of the columns Persons.Address and
Hobbies.Comments can determine the access path for the query. If column
Comments has a high occurrence of 'Todo' and Address a low occurrence of
'Main Street', then the optimizer might choose to access (scan) the
Persons table first, and then lookup matching rows in Hobbies (and
filter out remaining rows). With different statistics the optimizer
might access (scan) the Hobbies table and then lookup the matching rows
in Persons (and filter out the remaining unwanted rows).
HTH,
Gert-Jan
Amos Soma wrote:
> Hello. Could someone explain to me why it might be desirable to create a
> statistic on a non-indexed column?
> Thank you.sql
Create Statistics on NonIndexed Column
Hello. Could someone explain to me why it might be desirable to create a
statistic on a non-indexed column?
Thank you.
"Amos Soma" <amos_j_soma@.yahoo.com> wrote in message
news:%23X4AxZWBGHA.3604@.TK2MSFTNGP09.phx.gbl...
> Hello. Could someone explain to me why it might be desirable to create a
> statistic on a non-indexed column?
> Thank you.
>
The more information the query optimizer has, the better decisions it can
make.
Having statistics on a column that is not part of an index may help it make
better decisions about join strategies, table-scans versus index
seeks/scans, bookmark lookups and so forth.
Rick Sawtell
MCT, MCSD, MCDBA
|||Here are some examples to illustrate Rick's statement. Suppose you have
the following table:
CREATE TABLE Persons
(PersonID int not null PRIMARY KEY CLUSTERED
,LastName varchar(100) not null
,FirstName varchar(100) not null
,Address varchar(100) null
-- many other columns, very wide table
,CONSTRAINT UQ_Persons_Name UNIQUE (LastName, FirstName)
)
Now, the DDL above will create a nonclustered index on (LastName,
FirstName). Now suppose you have the following query:
SELECT *
FROM Persons
WHERE FirstName='Amos'
With the default table settings, SQL Server will auto create statistics
of column FirstName.
With these statistics, the optimizer can estimate how many rows there
are with FirstName 'Amos'. If this is a small percentage, then the
strategy would be to scan the nonclustered index and retrieve the
matching rows from the table (bookmark lookups). If it is a large
percentage, the clustered index will be scanned (basically a table
scan).
Without these statistics, SQL Server will optimizer for the worst case
situation, which in this case means scanning the table (clustered index
scan).
Another example. Suppose you also have this table:
-- just an example. Relation might not make sense
CREATE TABLE Hobbies
(HobbyID int not null PRIMARY KEY CLUSTERED
,Hobby varchar(100) not null UNIQUE
,Comments varchar(100) null
,PersonID int not null REFERENCES Persons
)
-- index on the foreign key
CREATE INDEX IX_Hobbies_PersonsID ON Hobbies(PersonID)
with this query:
SELECT Hobby
FROM Hobbies
INNER JOIN Persons
ON Persons.PersonID = Hobbies.PersonID
WHERE Address = 'Main Street'
AND Comments = 'Todo'
In this case statistics of the columns Persons.Address and
Hobbies.Comments can determine the access path for the query. If column
Comments has a high occurrence of 'Todo' and Address a low occurrence of
'Main Street', then the optimizer might choose to access (scan) the
Persons table first, and then lookup matching rows in Hobbies (and
filter out remaining rows). With different statistics the optimizer
might access (scan) the Hobbies table and then lookup the matching rows
in Persons (and filter out the remaining unwanted rows).
HTH,
Gert-Jan
Amos Soma wrote:
> Hello. Could someone explain to me why it might be desirable to create a
> statistic on a non-indexed column?
> Thank you.
statistic on a non-indexed column?
Thank you.
"Amos Soma" <amos_j_soma@.yahoo.com> wrote in message
news:%23X4AxZWBGHA.3604@.TK2MSFTNGP09.phx.gbl...
> Hello. Could someone explain to me why it might be desirable to create a
> statistic on a non-indexed column?
> Thank you.
>
The more information the query optimizer has, the better decisions it can
make.
Having statistics on a column that is not part of an index may help it make
better decisions about join strategies, table-scans versus index
seeks/scans, bookmark lookups and so forth.
Rick Sawtell
MCT, MCSD, MCDBA
|||Here are some examples to illustrate Rick's statement. Suppose you have
the following table:
CREATE TABLE Persons
(PersonID int not null PRIMARY KEY CLUSTERED
,LastName varchar(100) not null
,FirstName varchar(100) not null
,Address varchar(100) null
-- many other columns, very wide table
,CONSTRAINT UQ_Persons_Name UNIQUE (LastName, FirstName)
)
Now, the DDL above will create a nonclustered index on (LastName,
FirstName). Now suppose you have the following query:
SELECT *
FROM Persons
WHERE FirstName='Amos'
With the default table settings, SQL Server will auto create statistics
of column FirstName.
With these statistics, the optimizer can estimate how many rows there
are with FirstName 'Amos'. If this is a small percentage, then the
strategy would be to scan the nonclustered index and retrieve the
matching rows from the table (bookmark lookups). If it is a large
percentage, the clustered index will be scanned (basically a table
scan).
Without these statistics, SQL Server will optimizer for the worst case
situation, which in this case means scanning the table (clustered index
scan).
Another example. Suppose you also have this table:
-- just an example. Relation might not make sense
CREATE TABLE Hobbies
(HobbyID int not null PRIMARY KEY CLUSTERED
,Hobby varchar(100) not null UNIQUE
,Comments varchar(100) null
,PersonID int not null REFERENCES Persons
)
-- index on the foreign key
CREATE INDEX IX_Hobbies_PersonsID ON Hobbies(PersonID)
with this query:
SELECT Hobby
FROM Hobbies
INNER JOIN Persons
ON Persons.PersonID = Hobbies.PersonID
WHERE Address = 'Main Street'
AND Comments = 'Todo'
In this case statistics of the columns Persons.Address and
Hobbies.Comments can determine the access path for the query. If column
Comments has a high occurrence of 'Todo' and Address a low occurrence of
'Main Street', then the optimizer might choose to access (scan) the
Persons table first, and then lookup matching rows in Hobbies (and
filter out remaining rows). With different statistics the optimizer
might access (scan) the Hobbies table and then lookup the matching rows
in Persons (and filter out the remaining unwanted rows).
HTH,
Gert-Jan
Amos Soma wrote:
> Hello. Could someone explain to me why it might be desirable to create a
> statistic on a non-indexed column?
> Thank you.
Create Statistics on NonIndexed Column
Hello. Could someone explain to me why it might be desirable to create a
statistic on a non-indexed column?
Thank you."Amos Soma" <amos_j_soma@.yahoo.com> wrote in message
news:%23X4AxZWBGHA.3604@.TK2MSFTNGP09.phx.gbl...
> Hello. Could someone explain to me why it might be desirable to create a
> statistic on a non-indexed column?
> Thank you.
>
The more information the query optimizer has, the better decisions it can
make.
Having statistics on a column that is not part of an index may help it make
better decisions about join strategies, table-scans versus index
seeks/scans, bookmark lookups and so forth.
Rick Sawtell
MCT, MCSD, MCDBA|||Here are some examples to illustrate Rick's statement. Suppose you have
the following table:
CREATE TABLE Persons
(PersonID int not null PRIMARY KEY CLUSTERED
,LastName varchar(100) not null
,FirstName varchar(100) not null
,Address varchar(100) null
-- many other columns, very wide table
,CONSTRAINT UQ_Persons_Name UNIQUE (LastName, FirstName)
)
Now, the DDL above will create a nonclustered index on (LastName,
FirstName). Now suppose you have the following query:
SELECT *
FROM Persons
WHERE FirstName='Amos'
With the default table settings, SQL Server will auto create statistics
of column FirstName.
With these statistics, the optimizer can estimate how many rows there
are with FirstName 'Amos'. If this is a small percentage, then the
strategy would be to scan the nonclustered index and retrieve the
matching rows from the table (bookmark lookups). If it is a large
percentage, the clustered index will be scanned (basically a table
scan).
Without these statistics, SQL Server will optimizer for the worst case
situation, which in this case means scanning the table (clustered index
scan).
Another example. Suppose you also have this table:
-- just an example. Relation might not make sense
CREATE TABLE Hobbies
(HobbyID int not null PRIMARY KEY CLUSTERED
,Hobby varchar(100) not null UNIQUE
,Comments varchar(100) null
,PersonID int not null REFERENCES Persons
)
-- index on the foreign key
CREATE INDEX IX_Hobbies_PersonsID ON Hobbies(PersonID)
with this query:
SELECT Hobby
FROM Hobbies
INNER JOIN Persons
ON Persons.PersonID = Hobbies.PersonID
WHERE Address = 'Main Street'
AND Comments = 'Todo'
In this case statistics of the columns Persons.Address and
Hobbies.Comments can determine the access path for the query. If column
Comments has a high occurrence of 'Todo' and Address a low occurrence of
'Main Street', then the optimizer might choose to access (scan) the
Persons table first, and then lookup matching rows in Hobbies (and
filter out remaining rows). With different statistics the optimizer
might access (scan) the Hobbies table and then lookup the matching rows
in Persons (and filter out the remaining unwanted rows).
HTH,
Gert-Jan
Amos Soma wrote:
> Hello. Could someone explain to me why it might be desirable to create a
> statistic on a non-indexed column?
> Thank you.
statistic on a non-indexed column?
Thank you."Amos Soma" <amos_j_soma@.yahoo.com> wrote in message
news:%23X4AxZWBGHA.3604@.TK2MSFTNGP09.phx.gbl...
> Hello. Could someone explain to me why it might be desirable to create a
> statistic on a non-indexed column?
> Thank you.
>
The more information the query optimizer has, the better decisions it can
make.
Having statistics on a column that is not part of an index may help it make
better decisions about join strategies, table-scans versus index
seeks/scans, bookmark lookups and so forth.
Rick Sawtell
MCT, MCSD, MCDBA|||Here are some examples to illustrate Rick's statement. Suppose you have
the following table:
CREATE TABLE Persons
(PersonID int not null PRIMARY KEY CLUSTERED
,LastName varchar(100) not null
,FirstName varchar(100) not null
,Address varchar(100) null
-- many other columns, very wide table
,CONSTRAINT UQ_Persons_Name UNIQUE (LastName, FirstName)
)
Now, the DDL above will create a nonclustered index on (LastName,
FirstName). Now suppose you have the following query:
SELECT *
FROM Persons
WHERE FirstName='Amos'
With the default table settings, SQL Server will auto create statistics
of column FirstName.
With these statistics, the optimizer can estimate how many rows there
are with FirstName 'Amos'. If this is a small percentage, then the
strategy would be to scan the nonclustered index and retrieve the
matching rows from the table (bookmark lookups). If it is a large
percentage, the clustered index will be scanned (basically a table
scan).
Without these statistics, SQL Server will optimizer for the worst case
situation, which in this case means scanning the table (clustered index
scan).
Another example. Suppose you also have this table:
-- just an example. Relation might not make sense
CREATE TABLE Hobbies
(HobbyID int not null PRIMARY KEY CLUSTERED
,Hobby varchar(100) not null UNIQUE
,Comments varchar(100) null
,PersonID int not null REFERENCES Persons
)
-- index on the foreign key
CREATE INDEX IX_Hobbies_PersonsID ON Hobbies(PersonID)
with this query:
SELECT Hobby
FROM Hobbies
INNER JOIN Persons
ON Persons.PersonID = Hobbies.PersonID
WHERE Address = 'Main Street'
AND Comments = 'Todo'
In this case statistics of the columns Persons.Address and
Hobbies.Comments can determine the access path for the query. If column
Comments has a high occurrence of 'Todo' and Address a low occurrence of
'Main Street', then the optimizer might choose to access (scan) the
Persons table first, and then lookup matching rows in Hobbies (and
filter out remaining rows). With different statistics the optimizer
might access (scan) the Hobbies table and then lookup the matching rows
in Persons (and filter out the remaining unwanted rows).
HTH,
Gert-Jan
Amos Soma wrote:
> Hello. Could someone explain to me why it might be desirable to create a
> statistic on a non-indexed column?
> Thank you.
CREATE STATISTICS locking up tables?
Hi
I'm just wondering if executing CREATE STATISTICS (full scan) would cause
problems to the user operations by locking up tables or rows. Thanks!It certainly does. Note the create statistics are updated as part of an
index rebuild, so you might not have to do this.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Zen" <zen@.nononospam.com> wrote in message
news:OHVSBq6bGHA.1208@.TK2MSFTNGP02.phx.gbl...
> Hi
> I'm just wondering if executing CREATE STATISTICS (full scan) would cause
> problems to the user operations by locking up tables or rows. Thanks!
>
I'm just wondering if executing CREATE STATISTICS (full scan) would cause
problems to the user operations by locking up tables or rows. Thanks!It certainly does. Note the create statistics are updated as part of an
index rebuild, so you might not have to do this.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Zen" <zen@.nononospam.com> wrote in message
news:OHVSBq6bGHA.1208@.TK2MSFTNGP02.phx.gbl...
> Hi
> I'm just wondering if executing CREATE STATISTICS (full scan) would cause
> problems to the user operations by locking up tables or rows. Thanks!
>
CREATE STATISTICS locking up tables?
Hi
I'm just wondering if executing CREATE STATISTICS (full scan) would cause
problems to the user operations by locking up tables or rows. Thanks!It certainly does. Note the create statistics are updated as part of an
index rebuild, so you might not have to do this.
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Zen" <zen@.nononospam.com> wrote in message
news:OHVSBq6bGHA.1208@.TK2MSFTNGP02.phx.gbl...
> Hi
> I'm just wondering if executing CREATE STATISTICS (full scan) would cause
> problems to the user operations by locking up tables or rows. Thanks!
>
I'm just wondering if executing CREATE STATISTICS (full scan) would cause
problems to the user operations by locking up tables or rows. Thanks!It certainly does. Note the create statistics are updated as part of an
index rebuild, so you might not have to do this.
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Zen" <zen@.nononospam.com> wrote in message
news:OHVSBq6bGHA.1208@.TK2MSFTNGP02.phx.gbl...
> Hi
> I'm just wondering if executing CREATE STATISTICS (full scan) would cause
> problems to the user operations by locking up tables or rows. Thanks!
>
CREATE STATISTICS - use...
What is the use of running the command "CREATE STATISTICS"?
SQL 2K.
Thanks,
HarryHi,
You can use the CREATE STATISTICS command to create statistics on nonindexed
columns. Also, you can execute
the sp_createstats stored procedure, which creates single-column statistics
for all eligible columns for all user tables in the current database.
Thanks
Hari
SQL Server MVP
"HarrySmith" <HarrySmith_56@.hotmail.com> wrote in message
news:u2UUTBdrFHA.3604@.tk2msftngp13.phx.gbl...
> What is the use of running the command "CREATE STATISTICS"?
> SQL 2K.
> Thanks,
> Harry
>|||Further to Hari's post, statistics are used by the query optimiser when
it's calculating the best possible way to access the data you're after.
If you're after more info about stats, this whitepaper is really good (I
just read it last week):
Statistics Used by the Query Optimiser in Microsoft SQL Server 2005
<http://www.microsoft.com/technet/pr...5/qrystats.mspx>
It talks specifically about SQL 2005 but the concepts are mostly related
pretty closely to SQL 2000 too.
*mike hodgson*
blog: http://sqlnerd.blogspot.com
Hari Prasad wrote:
>Hi,
>You can use the CREATE STATISTICS command to create statistics on nonindexe
d
>columns. Also, you can execute
>the sp_createstats stored procedure, which creates single-column statistics
>for all eligible columns for all user tables in the current database.
>Thanks
>Hari
>SQL Server MVP
>
>"HarrySmith" <HarrySmith_56@.hotmail.com> wrote in message
>news:u2UUTBdrFHA.3604@.tk2msftngp13.phx.gbl...
>
>
>|||Thank you very much to both of you.
Harry
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message news:eFg
PIQerFHA.3884@.TK2MSFTNGP11.phx.gbl...
Further to Hari's post, statistics are used by the query optimiser when it's
calculating the best possible way to access the data you're after. If you'
re after more info about stats, this whitepaper is really good (I just read
it last week):
Statistics Used by the Query Optimiser in Microsoft SQL Server 2005
It talks specifically about SQL 2005 but the concepts are mostly related pre
tty closely to SQL 2000 too.
mike hodgson
blog: http://sqlnerd.blogspot.com
Hari Prasad wrote:
Hi,
You can use the CREATE STATISTICS command to create statistics on nonindexed
columns. Also, you can execute
the sp_createstats stored procedure, which creates single-column statistics
for all eligible columns for all user tables in the current database.
Thanks
Hari
SQL Server MVP
"HarrySmith" <HarrySmith_56@.hotmail.com> wrote in message
news:u2UUTBdrFHA.3604@.tk2msftngp13.phx.gbl...
What is the use of running the command "CREATE STATISTICS"?
SQL 2K.
Thanks,
Harrysql
SQL 2K.
Thanks,
HarryHi,
You can use the CREATE STATISTICS command to create statistics on nonindexed
columns. Also, you can execute
the sp_createstats stored procedure, which creates single-column statistics
for all eligible columns for all user tables in the current database.
Thanks
Hari
SQL Server MVP
"HarrySmith" <HarrySmith_56@.hotmail.com> wrote in message
news:u2UUTBdrFHA.3604@.tk2msftngp13.phx.gbl...
> What is the use of running the command "CREATE STATISTICS"?
> SQL 2K.
> Thanks,
> Harry
>|||Further to Hari's post, statistics are used by the query optimiser when
it's calculating the best possible way to access the data you're after.
If you're after more info about stats, this whitepaper is really good (I
just read it last week):
Statistics Used by the Query Optimiser in Microsoft SQL Server 2005
<http://www.microsoft.com/technet/pr...5/qrystats.mspx>
It talks specifically about SQL 2005 but the concepts are mostly related
pretty closely to SQL 2000 too.
*mike hodgson*
blog: http://sqlnerd.blogspot.com
Hari Prasad wrote:
>Hi,
>You can use the CREATE STATISTICS command to create statistics on nonindexe
d
>columns. Also, you can execute
>the sp_createstats stored procedure, which creates single-column statistics
>for all eligible columns for all user tables in the current database.
>Thanks
>Hari
>SQL Server MVP
>
>"HarrySmith" <HarrySmith_56@.hotmail.com> wrote in message
>news:u2UUTBdrFHA.3604@.tk2msftngp13.phx.gbl...
>
>
>|||Thank you very much to both of you.
Harry
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message news:eFg
PIQerFHA.3884@.TK2MSFTNGP11.phx.gbl...
Further to Hari's post, statistics are used by the query optimiser when it's
calculating the best possible way to access the data you're after. If you'
re after more info about stats, this whitepaper is really good (I just read
it last week):
Statistics Used by the Query Optimiser in Microsoft SQL Server 2005
It talks specifically about SQL 2005 but the concepts are mostly related pre
tty closely to SQL 2000 too.
mike hodgson
blog: http://sqlnerd.blogspot.com
Hari Prasad wrote:
Hi,
You can use the CREATE STATISTICS command to create statistics on nonindexed
columns. Also, you can execute
the sp_createstats stored procedure, which creates single-column statistics
for all eligible columns for all user tables in the current database.
Thanks
Hari
SQL Server MVP
"HarrySmith" <HarrySmith_56@.hotmail.com> wrote in message
news:u2UUTBdrFHA.3604@.tk2msftngp13.phx.gbl...
What is the use of running the command "CREATE STATISTICS"?
SQL 2K.
Thanks,
Harrysql
CREATE STATISTICS - use...
What is the use of running the command "CREATE STATISTICS"?
SQL 2K.
Thanks,
Harry
Hi,
You can use the CREATE STATISTICS command to create statistics on nonindexed
columns. Also, you can execute
the sp_createstats stored procedure, which creates single-column statistics
for all eligible columns for all user tables in the current database.
Thanks
Hari
SQL Server MVP
"HarrySmith" <HarrySmith_56@.hotmail.com> wrote in message
news:u2UUTBdrFHA.3604@.tk2msftngp13.phx.gbl...
> What is the use of running the command "CREATE STATISTICS"?
> SQL 2K.
> Thanks,
> Harry
>
|||Further to Hari's post, statistics are used by the query optimiser when
it's calculating the best possible way to access the data you're after.
If you're after more info about stats, this whitepaper is really good (I
just read it last week):
Statistics Used by the Query Optimiser in Microsoft SQL Server 2005
<http://www.microsoft.com/technet/pro.../qrystats.mspx>
It talks specifically about SQL 2005 but the concepts are mostly related
pretty closely to SQL 2000 too.
*mike hodgson*
blog: http://sqlnerd.blogspot.com
Hari Prasad wrote:
>Hi,
>You can use the CREATE STATISTICS command to create statistics on nonindexed
>columns. Also, you can execute
>the sp_createstats stored procedure, which creates single-column statistics
>for all eligible columns for all user tables in the current database.
>Thanks
>Hari
>SQL Server MVP
>
>"HarrySmith" <HarrySmith_56@.hotmail.com> wrote in message
>news:u2UUTBdrFHA.3604@.tk2msftngp13.phx.gbl...
>
>
>
|||Thank you very much to both of you.
Harry
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message news:eFgPIQerFHA.3884@.TK2MSFTNGP11.phx.gbl...
Further to Hari's post, statistics are used by the query optimiser when it's calculating the best possible way to access the data you're after. If you're after more info about stats, this whitepaper is really good (I just read it last week):
Statistics Used by the Query Optimiser in Microsoft SQL Server 2005
It talks specifically about SQL 2005 but the concepts are mostly related pretty closely to SQL 2000 too.
mike hodgson
blog: http://sqlnerd.blogspot.com
Hari Prasad wrote:
Hi,
You can use the CREATE STATISTICS command to create statistics on nonindexed
columns. Also, you can execute
the sp_createstats stored procedure, which creates single-column statistics
for all eligible columns for all user tables in the current database.
Thanks
Hari
SQL Server MVP
"HarrySmith" <HarrySmith_56@.hotmail.com> wrote in message
news:u2UUTBdrFHA.3604@.tk2msftngp13.phx.gbl...
What is the use of running the command "CREATE STATISTICS"?
SQL 2K.
Thanks,
Harry
SQL 2K.
Thanks,
Harry
Hi,
You can use the CREATE STATISTICS command to create statistics on nonindexed
columns. Also, you can execute
the sp_createstats stored procedure, which creates single-column statistics
for all eligible columns for all user tables in the current database.
Thanks
Hari
SQL Server MVP
"HarrySmith" <HarrySmith_56@.hotmail.com> wrote in message
news:u2UUTBdrFHA.3604@.tk2msftngp13.phx.gbl...
> What is the use of running the command "CREATE STATISTICS"?
> SQL 2K.
> Thanks,
> Harry
>
|||Further to Hari's post, statistics are used by the query optimiser when
it's calculating the best possible way to access the data you're after.
If you're after more info about stats, this whitepaper is really good (I
just read it last week):
Statistics Used by the Query Optimiser in Microsoft SQL Server 2005
<http://www.microsoft.com/technet/pro.../qrystats.mspx>
It talks specifically about SQL 2005 but the concepts are mostly related
pretty closely to SQL 2000 too.
*mike hodgson*
blog: http://sqlnerd.blogspot.com
Hari Prasad wrote:
>Hi,
>You can use the CREATE STATISTICS command to create statistics on nonindexed
>columns. Also, you can execute
>the sp_createstats stored procedure, which creates single-column statistics
>for all eligible columns for all user tables in the current database.
>Thanks
>Hari
>SQL Server MVP
>
>"HarrySmith" <HarrySmith_56@.hotmail.com> wrote in message
>news:u2UUTBdrFHA.3604@.tk2msftngp13.phx.gbl...
>
>
>
|||Thank you very much to both of you.
Harry
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message news:eFgPIQerFHA.3884@.TK2MSFTNGP11.phx.gbl...
Further to Hari's post, statistics are used by the query optimiser when it's calculating the best possible way to access the data you're after. If you're after more info about stats, this whitepaper is really good (I just read it last week):
Statistics Used by the Query Optimiser in Microsoft SQL Server 2005
It talks specifically about SQL 2005 but the concepts are mostly related pretty closely to SQL 2000 too.
mike hodgson
blog: http://sqlnerd.blogspot.com
Hari Prasad wrote:
Hi,
You can use the CREATE STATISTICS command to create statistics on nonindexed
columns. Also, you can execute
the sp_createstats stored procedure, which creates single-column statistics
for all eligible columns for all user tables in the current database.
Thanks
Hari
SQL Server MVP
"HarrySmith" <HarrySmith_56@.hotmail.com> wrote in message
news:u2UUTBdrFHA.3604@.tk2msftngp13.phx.gbl...
What is the use of running the command "CREATE STATISTICS"?
SQL 2K.
Thanks,
Harry
Subscribe to:
Posts (Atom)