sql record counts: box

Showing posts with label box. Show all posts

Tuesday, March 27, 2012

Difference between Index & Statistics

2000 & 2005 (The DDL was pulled from the 2005 box, but should be the same, or
very close, on the 2000 box)
I know what statistics are: distribution of values used by the Query
Optimizer.
I know what indexes are.
I do not understand why I see both CREATE INDEX and CREATE STATISTICS on a
table, nor do I understand why Visio seem to be marking a column with
Statistics (ObjectID) as having a Unique index.
The column in question is "ObjectID" and is used in a JOIN to other tables.
REATE TABLE [dbo].[Documents](
[DocumentID] [int] IDENTITY(1,1) NOT NULL,
[ObjectID] [int] NULL,
[ObjectTypeID] [int] NULL,
[StatusID] [int] NOT NULL CONSTRAINT [DF_Documents_StatusID] DEFAULT (1),
[StatusComment] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Searchable] [bit] NOT NULL CONSTRAINT [DF_Documents_Searchable] DEFAULT
(1),
[DateEntered] [datetime] NOT NULL CONSTRAINT [DF_Documents_DateEntered]
DEFAULT (getdate()),
[DateModified] [datetime] NOT NULL CONSTRAINT [DF_Documents_DateModified]
DEFAULT (getdate()),
[ModifiedBy] [int] NULL,
[ReleaseDate] [smalldatetime] NULL,
[ExpireDate] [smalldatetime] NULL,
[ViewCount] [int] NOT NULL CONSTRAINT [DF_Documents_ViewCount] DEFAULT
((0)),
[AddedBy] [int] NULL,
[DateAuthorCreated] [datetime] NULL,
[DateAuthorRevised] [datetime] NULL,
[Title] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ShortTitle] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_Document] PRIMARY KEY CLUSTERED (
[DocumentID] ASC
)
WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY])
ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE STATISTICS [_dta_stat_29243159_1_2] ON
[dbo].[Documents]([DocumentID], [ObjectID])
GO
ALTER TABLE [dbo].[Documents] WITH CHECK ADD CONSTRAINT
[FK_Documents_ltblObjectType] FOREIGN KEY([ObjectTypeID])
REFERENCES [dbo].[ltblObjectType] ([ObjectTypeID])
GO
ALTER TABLE [dbo].[Documents] CHECK CONSTRAINT [FK_Documents_ltblObjectType]> I do not understand why I see both CREATE INDEX and CREATE STATISTICS on a
> table
That you should ask the one show created the statistics. In fact, the name implies it was done by
Database Engine Tuning Advisor. Can that be correct? Anyhow, the statistics is on the column
*combination* (DocumentID, ObjectID). The only index I see is the one created for the PK which is on
only the column DocumentID. Even though distributiution information is for only the first column,
SQL Server *does* maintain density for the two columns (see output from DBCC SHOW_STATISTICS). So my
guess is that someone did a DTA for a workload and DTA suggested to create this statistics.
> nor do I understand why Visio seem to be marking a column with
> Statistics (ObjectID) as having a Unique index.
A bug in Visio? I suggest you ask in a Visio group, since you are more likely to find Visio experts
there.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"JayKon" <JayKon@.discussions.microsoft.com> wrote in message
news:1BE2F35F-7A55-4B69-8FF7-B48CECD48E91@.microsoft.com...
> 2000 & 2005 (The DDL was pulled from the 2005 box, but should be the same, or
> very close, on the 2000 box)
> I know what statistics are: distribution of values used by the Query
> Optimizer.
> I know what indexes are.
> I do not understand why I see both CREATE INDEX and CREATE STATISTICS on a
> table, nor do I understand why Visio seem to be marking a column with
> Statistics (ObjectID) as having a Unique index.
> The column in question is "ObjectID" and is used in a JOIN to other tables.
> REATE TABLE [dbo].[Documents](
> [DocumentID] [int] IDENTITY(1,1) NOT NULL,
> [ObjectID] [int] NULL,
> [ObjectTypeID] [int] NULL,
> [StatusID] [int] NOT NULL CONSTRAINT [DF_Documents_StatusID] DEFAULT (1),
> [StatusComment] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> [Searchable] [bit] NOT NULL CONSTRAINT [DF_Documents_Searchable] DEFAULT
> (1),
> [DateEntered] [datetime] NOT NULL CONSTRAINT [DF_Documents_DateEntered]
> DEFAULT (getdate()),
> [DateModified] [datetime] NOT NULL CONSTRAINT [DF_Documents_DateModified]
> DEFAULT (getdate()),
> [ModifiedBy] [int] NULL,
> [ReleaseDate] [smalldatetime] NULL,
> [ExpireDate] [smalldatetime] NULL,
> [ViewCount] [int] NOT NULL CONSTRAINT [DF_Documents_ViewCount] DEFAULT
> ((0)),
> [AddedBy] [int] NULL,
> [DateAuthorCreated] [datetime] NULL,
> [DateAuthorRevised] [datetime] NULL,
> [Title] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> [ShortTitle] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> CONSTRAINT [PK_Document] PRIMARY KEY CLUSTERED (
> [DocumentID] ASC
> )
> WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY])
> ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
>
> GO
> CREATE STATISTICS [_dta_stat_29243159_1_2] ON
> [dbo].[Documents]([DocumentID], [ObjectID])
> GO
> ALTER TABLE [dbo].[Documents] WITH CHECK ADD CONSTRAINT
> [FK_Documents_ltblObjectType] FOREIGN KEY([ObjectTypeID])
> REFERENCES [dbo].[ltblObjectType] ([ObjectTypeID])
> GO
> ALTER TABLE [dbo].[Documents] CHECK CONSTRAINT [FK_Documents_ltblObjectType]
>|||Thanks Tibor,
After reading your reply, I decided to look at the table again. For some
reason I was expecting MSSMS to give me all the DDL to the table in a single
option. My bad.
There is a non-unique index on ObjectID of the Documents table, so Visio's
"U" means non-unique and "I" means unique :( Oh well, Visio vs. ERwin.
As to the DTA, it kinda of sounds like you don't think much of it. Yes? No?
As to the Statistics, I'm still unclear why I would want them and not an
index. I am, of course, only refering to the statistics that show up in the
DDL, not the engine stats. Am I correct in the distinction I just made, or
should it be phrased differently?
"Tibor Karaszi" wrote:
> > I do not understand why I see both CREATE INDEX and CREATE STATISTICS on a
> > table
> That you should ask the one show created the statistics. In fact, the name implies it was done by
> Database Engine Tuning Advisor. Can that be correct? Anyhow, the statistics is on the column
> *combination* (DocumentID, ObjectID). The only index I see is the one created for the PK which is on
> only the column DocumentID. Even though distributiution information is for only the first column,
> SQL Server *does* maintain density for the two columns (see output from DBCC SHOW_STATISTICS). So my
> guess is that someone did a DTA for a workload and DTA suggested to create this statistics.
>
> > nor do I understand why Visio seem to be marking a column with
> > Statistics (ObjectID) as having a Unique index.
> A bug in Visio? I suggest you ask in a Visio group, since you are more likely to find Visio experts
> there.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "JayKon" <JayKon@.discussions.microsoft.com> wrote in message
> news:1BE2F35F-7A55-4B69-8FF7-B48CECD48E91@.microsoft.com...
> > 2000 & 2005 (The DDL was pulled from the 2005 box, but should be the same, or
> > very close, on the 2000 box)
> >
> > I know what statistics are: distribution of values used by the Query
> > Optimizer.
> > I know what indexes are.
> >
> > I do not understand why I see both CREATE INDEX and CREATE STATISTICS on a
> > table, nor do I understand why Visio seem to be marking a column with
> > Statistics (ObjectID) as having a Unique index.
> >
> > The column in question is "ObjectID" and is used in a JOIN to other tables.
> >
> > REATE TABLE [dbo].[Documents](
> > [DocumentID] [int] IDENTITY(1,1) NOT NULL,
> > [ObjectID] [int] NULL,
> > [ObjectTypeID] [int] NULL,
> > [StatusID] [int] NOT NULL CONSTRAINT [DF_Documents_StatusID] DEFAULT (1),
> > [StatusComment] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> > [Searchable] [bit] NOT NULL CONSTRAINT [DF_Documents_Searchable] DEFAULT
> > (1),
> > [DateEntered] [datetime] NOT NULL CONSTRAINT [DF_Documents_DateEntered]
> > DEFAULT (getdate()),
> > [DateModified] [datetime] NOT NULL CONSTRAINT [DF_Documents_DateModified]
> > DEFAULT (getdate()),
> > [ModifiedBy] [int] NULL,
> > [ReleaseDate] [smalldatetime] NULL,
> > [ExpireDate] [smalldatetime] NULL,
> > [ViewCount] [int] NOT NULL CONSTRAINT [DF_Documents_ViewCount] DEFAULT
> > ((0)),
> > [AddedBy] [int] NULL,
> > [DateAuthorCreated] [datetime] NULL,
> > [DateAuthorRevised] [datetime] NULL,
> > [Title] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> > [ShortTitle] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> > CONSTRAINT [PK_Document] PRIMARY KEY CLUSTERED (
> > [DocumentID] ASC
> > )
> > WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY])
> > ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> >
> >
> > GO
> > CREATE STATISTICS [_dta_stat_29243159_1_2] ON
> > [dbo].[Documents]([DocumentID], [ObjectID])
> >
> > GO
> > ALTER TABLE [dbo].[Documents] WITH CHECK ADD CONSTRAINT
> > [FK_Documents_ltblObjectType] FOREIGN KEY([ObjectTypeID])
> > REFERENCES [dbo].[ltblObjectType] ([ObjectTypeID])
> > GO
> >
> > ALTER TABLE [dbo].[Documents] CHECK CONSTRAINT [FK_Documents_ltblObjectType]
> >
>|||> There is a non-unique index on ObjectID of the Documents table, so Visio's
> "U" means non-unique and "I" means unique :( Oh well, Visio vs. ERwin.
Now, that's weird. I guess different tool makers has different preferences...
> As to the DTA, it kinda of sounds like you don't think much of it.
No, that was not what I was trying to say. DTA is been much improved since Index Tuning Wizard (2000
and 7.0). IMO, a tool like this will never replace the human brain, but it is a good complement to
the work we do.
> As to the Statistics, I'm still unclear why I would want them and not an
> index. I am, of course, only refering to the statistics that show up in the
> DDL, not the engine stats. Am I correct in the distinction I just made, or
> should it be phrased differently?
Sometimes, statistics can help the optimizer pick a better plan, even in cases where an index
wouldn't be used. So, in these cases, why carry a b-tree if it won't be used?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"JayKon" <JayKon@.discussions.microsoft.com> wrote in message
news:9CAF481A-3FF4-4557-9D7E-8053544E480D@.microsoft.com...
> Thanks Tibor,
> After reading your reply, I decided to look at the table again. For some
> reason I was expecting MSSMS to give me all the DDL to the table in a single
> option. My bad.
> There is a non-unique index on ObjectID of the Documents table, so Visio's
> "U" means non-unique and "I" means unique :( Oh well, Visio vs. ERwin.
> As to the DTA, it kinda of sounds like you don't think much of it. Yes? No?
> As to the Statistics, I'm still unclear why I would want them and not an
> index. I am, of course, only refering to the statistics that show up in the
> DDL, not the engine stats. Am I correct in the distinction I just made, or
> should it be phrased differently?
> "Tibor Karaszi" wrote:
>> > I do not understand why I see both CREATE INDEX and CREATE STATISTICS on a
>> > table
>> That you should ask the one show created the statistics. In fact, the name implies it was done by
>> Database Engine Tuning Advisor. Can that be correct? Anyhow, the statistics is on the column
>> *combination* (DocumentID, ObjectID). The only index I see is the one created for the PK which is
>> on
>> only the column DocumentID. Even though distributiution information is for only the first column,
>> SQL Server *does* maintain density for the two columns (see output from DBCC SHOW_STATISTICS). So
>> my
>> guess is that someone did a DTA for a workload and DTA suggested to create this statistics.
>>
>> > nor do I understand why Visio seem to be marking a column with
>> > Statistics (ObjectID) as having a Unique index.
>> A bug in Visio? I suggest you ask in a Visio group, since you are more likely to find Visio
>> experts
>> there.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "JayKon" <JayKon@.discussions.microsoft.com> wrote in message
>> news:1BE2F35F-7A55-4B69-8FF7-B48CECD48E91@.microsoft.com...
>> > 2000 & 2005 (The DDL was pulled from the 2005 box, but should be the same, or
>> > very close, on the 2000 box)
>> >
>> > I know what statistics are: distribution of values used by the Query
>> > Optimizer.
>> > I know what indexes are.
>> >
>> > I do not understand why I see both CREATE INDEX and CREATE STATISTICS on a
>> > table, nor do I understand why Visio seem to be marking a column with
>> > Statistics (ObjectID) as having a Unique index.
>> >
>> > The column in question is "ObjectID" and is used in a JOIN to other tables.
>> >
>> > REATE TABLE [dbo].[Documents](
>> > [DocumentID] [int] IDENTITY(1,1) NOT NULL,
>> > [ObjectID] [int] NULL,
>> > [ObjectTypeID] [int] NULL,
>> > [StatusID] [int] NOT NULL CONSTRAINT [DF_Documents_StatusID] DEFAULT (1),
>> > [StatusComment] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
>> > [Searchable] [bit] NOT NULL CONSTRAINT [DF_Documents_Searchable] DEFAULT
>> > (1),
>> > [DateEntered] [datetime] NOT NULL CONSTRAINT [DF_Documents_DateEntered]
>> > DEFAULT (getdate()),
>> > [DateModified] [datetime] NOT NULL CONSTRAINT [DF_Documents_DateModified]
>> > DEFAULT (getdate()),
>> > [ModifiedBy] [int] NULL,
>> > [ReleaseDate] [smalldatetime] NULL,
>> > [ExpireDate] [smalldatetime] NULL,
>> > [ViewCount] [int] NOT NULL CONSTRAINT [DF_Documents_ViewCount] DEFAULT
>> > ((0)),
>> > [AddedBy] [int] NULL,
>> > [DateAuthorCreated] [datetime] NULL,
>> > [DateAuthorRevised] [datetime] NULL,
>> > [Title] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
>> > [ShortTitle] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
>> > CONSTRAINT [PK_Document] PRIMARY KEY CLUSTERED (
>> > [DocumentID] ASC
>> > )
>> > WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY])
>> > ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
>> >
>> >
>> > GO
>> > CREATE STATISTICS [_dta_stat_29243159_1_2] ON
>> > [dbo].[Documents]([DocumentID], [ObjectID])
>> >
>> > GO
>> > ALTER TABLE [dbo].[Documents] WITH CHECK ADD CONSTRAINT
>> > [FK_Documents_ltblObjectType] FOREIGN KEY([ObjectTypeID])
>> > REFERENCES [dbo].[ltblObjectType] ([ObjectTypeID])
>> > GO
>> >
>> > ALTER TABLE [dbo].[Documents] CHECK CONSTRAINT [FK_Documents_ltblObjectType]
>> >
>>sql

Difference between Index & Statistics

Oh well, visio vs. ERwin.
As to the DTA, it kinda of sounds like you don't think much of it. Yes? No?
As to the Statistics, I'm still unclear why I would want them and not an
index. I am, of course, only refering to the statistics that show up in the
DDL, not the engine stats. Am I correct in the distinction I just made, or
should it be phrased differently?
"Tibor Karaszi" wrote:

> That you should ask the one show created the statistics. In fact, the name
implies it was done by
> Database Engine Tuning Advisor. Can that be correct? Anyhow, the statistic
s is on the column
> *combination* (DocumentID, ObjectID). The only index I see is the one crea
ted for the PK which is on
> only the column DocumentID. Even though distributiution information is for
only the first column,
> SQL Server *does* maintain density for the two columns (see output from DB
CC SHOW_STATISTICS). So my
> guess is that someone did a DTA for a workload and DTA suggested to create
this statistics.
>
> A bug in Visio? I suggest you ask in a visio group, since you are more lik
ely to find visio experts
> there.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "JayKon" <JayKon@.discussions.microsoft.com> wrote in message
> news:1BE2F35F-7A55-4B69-8FF7-B48CECD48E91@.microsoft.com...
>|||> There is a non-unique index on ObjectID of the Documents table, so Visio's
> "U" means non-unique and "I" means unique

Oh well, visio vs. ERwin.
Now, that's weird. I guess different tool makers has different preferences..
.

> As to the DTA, it kinda of sounds like you don't think much of it.
No, that was not what I was trying to say. DTA is been much improved since I
ndex Tuning Wizard (2000
and 7.0). IMO, a tool like this will never replace the human brain, but it i
s a good complement to
the work we do.

> As to the Statistics, I'm still unclear why I would want them and not an
> index. I am, of course, only refering to the statistics that show up in th
e
> DDL, not the engine stats. Am I correct in the distinction I just made, or
> should it be phrased differently?
Sometimes, statistics can help the optimizer pick a better plan, even in cas
es where an index
wouldn't be used. So, in these cases, why carry a b-tree if it won't be used
?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"JayKon" <JayKon@.discussions.microsoft.com> wrote in message
news:9CAF481A-3FF4-4557-9D7E-8053544E480D@.microsoft.com...[vbcol=seagreen]
> Thanks Tibor,
> After reading your reply, I decided to look at the table again. For some
> reason I was expecting MSSMS to give me all the DDL to the table in a sing
le
> option. My bad.
> There is a non-unique index on ObjectID of the Documents table, so Visio's
> "U" means non-unique and "I" means unique

Oh well, visio vs. ERwin.
> As to the DTA, it kinda of sounds like you don't think much of it. Yes? No
?
> As to the Statistics, I'm still unclear why I would want them and not an
> index. I am, of course, only refering to the statistics that show up in th
e
> DDL, not the engine stats. Am I correct in the distinction I just made, or
> should it be phrased differently?
> "Tibor Karaszi" wrote:
>

Difference between Index & Statistics

Oh well, Visio vs. ERwin.
As to the DTA, it kinda of sounds like you don't think much of it. Yes? No?
As to the Statistics, I'm still unclear why I would want them and not an
index. I am, of course, only refering to the statistics that show up in the
DDL, not the engine stats. Am I correct in the distinction I just made, or
should it be phrased differently?
"Tibor Karaszi" wrote:

> That you should ask the one show created the statistics. In fact, the name implies it was done by
> Database Engine Tuning Advisor. Can that be correct? Anyhow, the statistics is on the column
> *combination* (DocumentID, ObjectID). The only index I see is the one created for the PK which is on
> only the column DocumentID. Even though distributiution information is for only the first column,
> SQL Server *does* maintain density for the two columns (see output from DBCC SHOW_STATISTICS). So my
> guess is that someone did a DTA for a workload and DTA suggested to create this statistics.
>
> A bug in Visio? I suggest you ask in a Visio group, since you are more likely to find Visio experts
> there.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "JayKon" <JayKon@.discussions.microsoft.com> wrote in message
> news:1BE2F35F-7A55-4B69-8FF7-B48CECD48E91@.microsoft.com...
>
|||> There is a non-unique index on ObjectID of the Documents table, so Visio's
> "U" means non-unique and "I" means unique

Oh well, Visio vs. ERwin.
Now, that's weird. I guess different tool makers has different preferences...

> As to the DTA, it kinda of sounds like you don't think much of it.
No, that was not what I was trying to say. DTA is been much improved since Index Tuning Wizard (2000
and 7.0). IMO, a tool like this will never replace the human brain, but it is a good complement to
the work we do.

> As to the Statistics, I'm still unclear why I would want them and not an
> index. I am, of course, only refering to the statistics that show up in the
> DDL, not the engine stats. Am I correct in the distinction I just made, or
> should it be phrased differently?
Sometimes, statistics can help the optimizer pick a better plan, even in cases where an index
wouldn't be used. So, in these cases, why carry a b-tree if it won't be used?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"JayKon" <JayKon@.discussions.microsoft.com> wrote in message
news:9CAF481A-3FF4-4557-9D7E-8053544E480D@.microsoft.com...[vbcol=seagreen]
> Thanks Tibor,
> After reading your reply, I decided to look at the table again. For some
> reason I was expecting MSSMS to give me all the DDL to the table in a single
> option. My bad.
> There is a non-unique index on ObjectID of the Documents table, so Visio's
> "U" means non-unique and "I" means unique

Oh well, Visio vs. ERwin.
> As to the DTA, it kinda of sounds like you don't think much of it. Yes? No?
> As to the Statistics, I'm still unclear why I would want them and not an
> index. I am, of course, only refering to the statistics that show up in the
> DDL, not the engine stats. Am I correct in the distinction I just made, or
> should it be phrased differently?
> "Tibor Karaszi" wrote:

Sunday, March 25, 2012

difference between 'Backup' and DB maint. plan backup?

hi.
ive not setup any backups under 'Management' -> 'Backup' on my SQL2000 box.
instead ive used the 'Complete Backup' and ' Transaction Log Backup' tab
under Database Maintenance Plans -> 'NonSystemDatabaseMaintenance'.
is setting up a backup using a maintenance plan the same as setting up a
full backup under the 'Backup' tab?
thanks.Hi,
Backup tab is just creating the Logical Backup device. What you are doing is
exacltly the right procedure to take Full database
and Transaction log backup.
Looks like you are performing backup only for non system database backup,
But i recomend you to do the backup for all the system
databases as well. This will help you in the eve of crash.
Thanks
Hari
SQL Server MVP
"mb" <mb@.discussions.microsoft.com> wrote in message
news:C67C8B52-07C6-40D0-A722-2BCE97582372@.microsoft.com...
> hi.
> ive not setup any backups under 'Management' -> 'Backup' on my SQL2000
> box.
> instead ive used the 'Complete Backup' and ' Transaction Log Backup' tab
> under Database Maintenance Plans -> 'NonSystemDatabaseMaintenance'.
> is setting up a backup using a maintenance plan the same as setting up a
> full backup under the 'Backup' tab?
> thanks.|||hmm, yes, but i dont believe i have the option of 'remove inactive entries
from transaction log' when creating transaction logs from the maintenance
plans. true?
"Hari Prasad" wrote:
> Hi,
> Backup tab is just creating the Logical Backup device. What you are doing is
> exacltly the right procedure to take Full database
> and Transaction log backup.
> Looks like you are performing backup only for non system database backup,
> But i recomend you to do the backup for all the system
> databases as well. This will help you in the eve of crash.
> Thanks
> Hari
> SQL Server MVP
>
>
> "mb" <mb@.discussions.microsoft.com> wrote in message
> news:C67C8B52-07C6-40D0-A722-2BCE97582372@.microsoft.com...
> > hi.
> >
> > ive not setup any backups under 'Management' -> 'Backup' on my SQL2000
> > box.
> > instead ive used the 'Complete Backup' and ' Transaction Log Backup' tab
> > under Database Maintenance Plans -> 'NonSystemDatabaseMaintenance'.
> >
> > is setting up a backup using a maintenance plan the same as setting up a
> > full backup under the 'Backup' tab?
> >
> > thanks.
>
>|||Btw, I've written an article about this:
http://www.karaszi.com/SQLServer/info_restore_no_truncate.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"mb" <mb@.discussions.microsoft.com> wrote in message
news:5E78EC73-4EBA-4DAD-836E-3EBAD9D3BFA8@.microsoft.com...
> hmm, yes, but i dont believe i have the option of 'remove inactive entries
> from transaction log' when creating transaction logs from the maintenance
> plans. true?
> "Hari Prasad" wrote:
>> Hi,
>> Backup tab is just creating the Logical Backup device. What you are doing is
>> exacltly the right procedure to take Full database
>> and Transaction log backup.
>> Looks like you are performing backup only for non system database backup,
>> But i recomend you to do the backup for all the system
>> databases as well. This will help you in the eve of crash.
>> Thanks
>> Hari
>> SQL Server MVP
>>
>>
>> "mb" <mb@.discussions.microsoft.com> wrote in message
>> news:C67C8B52-07C6-40D0-A722-2BCE97582372@.microsoft.com...
>> > hi.
>> >
>> > ive not setup any backups under 'Management' -> 'Backup' on my SQL2000
>> > box.
>> > instead ive used the 'Complete Backup' and ' Transaction Log Backup' tab
>> > under Database Maintenance Plans -> 'NonSystemDatabaseMaintenance'.
>> >
>> > is setting up a backup using a maintenance plan the same as setting up a
>> > full backup under the 'Backup' tab?
>> >
>> > thanks.
>>|||Maint plan will not add the NO_TRUNCATE option for the backup log command. This option is what
empties the log and when you uncheck in the backup dialog, this option is added. Terrible GUI design
in the backup dialog (not maint wiz) IMO, and this option has a very special purpose and is only
used for disaster scenarios.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"mb" <mb@.discussions.microsoft.com> wrote in message
news:5E78EC73-4EBA-4DAD-836E-3EBAD9D3BFA8@.microsoft.com...
> hmm, yes, but i dont believe i have the option of 'remove inactive entries
> from transaction log' when creating transaction logs from the maintenance
> plans. true?
> "Hari Prasad" wrote:
>> Hi,
>> Backup tab is just creating the Logical Backup device. What you are doing is
>> exacltly the right procedure to take Full database
>> and Transaction log backup.
>> Looks like you are performing backup only for non system database backup,
>> But i recomend you to do the backup for all the system
>> databases as well. This will help you in the eve of crash.
>> Thanks
>> Hari
>> SQL Server MVP
>>
>>
>> "mb" <mb@.discussions.microsoft.com> wrote in message
>> news:C67C8B52-07C6-40D0-A722-2BCE97582372@.microsoft.com...
>> > hi.
>> >
>> > ive not setup any backups under 'Management' -> 'Backup' on my SQL2000
>> > box.
>> > instead ive used the 'Complete Backup' and ' Transaction Log Backup' tab
>> > under Database Maintenance Plans -> 'NonSystemDatabaseMaintenance'.
>> >
>> > is setting up a backup using a maintenance plan the same as setting up a
>> > full backup under the 'Backup' tab?
>> >
>> > thanks.
>>

difference between 'Backup' and DB maint. plan backup?

Monday, March 19, 2012

Dictionarry order question

I have an installed sql server 2000 language English with server collation
set to SQL_Latin1_General_CP1_CI_AS on a W2k server box. There are several
applications running in production using that machine and those settings.
Now the company is getting a new program that has as the requirement for its
sql server dictionary order to be set to Custom, Case insensitive, Accent
insensitive, for use with 1252 character set.
This server has lots of capacity left and there is no way to justify
spending dollars on another processor license for this app when the server
we already have should be able to do the job.
Is it possible to change the dictionary order on the current server and its
databases and test my current apps? If they still work as before I can then
leave it like that, if not I would have to be able to come back to current
settings.
What is the expert opinion on the difference in settings between the new app
and the current settings as shown above, do you think we should try keeping
our current settings for the new app? The app coming in is using asp pages
and the users are all French, the data in the files will be French with
accented characters and also some English in some fields.
Any help would be greatly appreciated,
RD
If it was me, I'd set up a new instance of SQL Server (configured with the
collation sequence of your choice) and let 'er rip. But with SQL Server
2000, it's possible to have different collation sequences on the instance,
database, table, or even column (IIRC). So there's no reason to change your
existing apps and databases. As far as I know, changing collations is a
real pain in the butt, and involves DTS'ing or BCP'ing your data to a new
home, or out to a temporary one, and in to the re-built one.
Clint
"RD" <bdufour@.sgiims.com> wrote in message
news:#OXncM9hFHA.4000@.TK2MSFTNGP12.phx.gbl...
> I have an installed sql server 2000 language English with server collation
> set to SQL_Latin1_General_CP1_CI_AS on a W2k server box. There are several
> applications running in production using that machine and those settings.
> Now the company is getting a new program that has as the requirement for
its
> sql server dictionary order to be set to Custom, Case insensitive, Accent
> insensitive, for use with 1252 character set.
> This server has lots of capacity left and there is no way to justify
> spending dollars on another processor license for this app when the server
> we already have should be able to do the job.
> Is it possible to change the dictionary order on the current server and
its
> databases and test my current apps? If they still work as before I can
then
> leave it like that, if not I would have to be able to come back to current
> settings.
> What is the expert opinion on the difference in settings between the new
app
> and the current settings as shown above, do you think we should try
keeping
> our current settings for the new app? The app coming in is using asp pages
> and the users are all French, the data in the files will be French with
> accented characters and also some English in some fields.
> Any help would be greatly appreciated,
> RD
>

Dictionarry order question

I have an installed sql server 2000 language English with server collation
set to SQL_Latin1_General_CP1_CI_AS on a W2k server box. There are several
applications running in production using that machine and those settings.
Now the company is getting a new program that has as the requirement for its
sql server dictionary order to be set to Custom, Case insensitive, Accent
insensitive, for use with 1252 character set.
This server has lots of capacity left and there is no way to justify
spending dollars on another processor license for this app when the server
we already have should be able to do the job.
Is it possible to change the dictionary order on the current server and its
databases and test my current apps? If they still work as before I can then
leave it like that, if not I would have to be able to come back to current
settings.
What is the expert opinion on the difference in settings between the new app
and the current settings as shown above, do you think we should try keeping
our current settings for the new app? The app coming in is using asp pages
and the users are all French, the data in the files will be French with
accented characters and also some English in some fields.
Any help would be greatly appreciated,
RDIf it was me, I'd set up a new instance of SQL Server (configured with the
collation sequence of your choice) and let 'er rip. But with SQL Server
2000, it's possible to have different collation sequences on the instance,
database, table, or even column (IIRC). So there's no reason to change your
existing apps and databases. As far as I know, changing collations is a
real pain in the butt, and involves DTS'ing or BCP'ing your data to a new
home, or out to a temporary one, and in to the re-built one.
Clint
"RD" <bdufour@.sgiims.com> wrote in message
news:#OXncM9hFHA.4000@.TK2MSFTNGP12.phx.gbl...
> I have an installed sql server 2000 language English with server collation
> set to SQL_Latin1_General_CP1_CI_AS on a W2k server box. There are several
> applications running in production using that machine and those settings.
> Now the company is getting a new program that has as the requirement for
its
> sql server dictionary order to be set to Custom, Case insensitive, Accent
> insensitive, for use with 1252 character set.
> This server has lots of capacity left and there is no way to justify
> spending dollars on another processor license for this app when the server
> we already have should be able to do the job.
> Is it possible to change the dictionary order on the current server and
its
> databases and test my current apps? If they still work as before I can
then
> leave it like that, if not I would have to be able to come back to current
> settings.
> What is the expert opinion on the difference in settings between the new
app
> and the current settings as shown above, do you think we should try
keeping
> our current settings for the new app? The app coming in is using asp pages
> and the users are all French, the data in the files will be French with
> accented characters and also some English in some fields.
> Any help would be greatly appreciated,
> RD
>

Dictionarry order question

I have an installed sql server 2000 language English with server collation
set to SQL_Latin1_General_CP1_CI_AS on a W2k server box. There are several
applications running in production using that machine and those settings.
Now the company is getting a new program that has as the requirement for its
sql server dictionary order to be set to Custom, Case insensitive, Accent
insensitive, for use with 1252 character set.
This server has lots of capacity left and there is no way to justify
spending dollars on another processor license for this app when the server
we already have should be able to do the job.
Is it possible to change the dictionary order on the current server and its
databases and test my current apps? If they still work as before I can then
leave it like that, if not I would have to be able to come back to current
settings.
What is the expert opinion on the difference in settings between the new app
and the current settings as shown above, do you think we should try keeping
our current settings for the new app? The app coming in is using asp pages
and the users are all French, the data in the files will be French with
accented characters and also some English in some fields.
Any help would be greatly appreciated,
RDIf it was me, I'd set up a new instance of SQL Server (configured with the
collation sequence of your choice) and let 'er rip. But with SQL Server
2000, it's possible to have different collation sequences on the instance,
database, table, or even column (IIRC). So there's no reason to change your
existing apps and databases. As far as I know, changing collations is a
real pain in the butt, and involves DTS'ing or BCP'ing your data to a new
home, or out to a temporary one, and in to the re-built one.
Clint
"RD" <bdufour@.sgiims.com> wrote in message
news:#OXncM9hFHA.4000@.TK2MSFTNGP12.phx.gbl...
> I have an installed sql server 2000 language English with server collation
> set to SQL_Latin1_General_CP1_CI_AS on a W2k server box. There are several
> applications running in production using that machine and those settings.
> Now the company is getting a new program that has as the requirement for
its
> sql server dictionary order to be set to Custom, Case insensitive, Accent
> insensitive, for use with 1252 character set.
> This server has lots of capacity left and there is no way to justify
> spending dollars on another processor license for this app when the server
> we already have should be able to do the job.
> Is it possible to change the dictionary order on the current server and
its
> databases and test my current apps? If they still work as before I can
then
> leave it like that, if not I would have to be able to come back to current
> settings.
> What is the expert opinion on the difference in settings between the new
app
> and the current settings as shown above, do you think we should try
keeping
> our current settings for the new app? The app coming in is using asp pages
> and the users are all French, the data in the files will be French with
> accented characters and also some English in some fields.
> Any help would be greatly appreciated,
> RD
>

Wednesday, March 7, 2012

Development DB to Production DB

We have a development SQL server and a production SQL server. Is there
anyway to replicate what we create on the Dev box over to production box
when we are finished our testing?
Thanks.
TomHi,
DId you meant to replicate database in development to Production, if that
is the case you can go for any of the below options,
1. Detach the database in development and attach it in production
2. Backup the development database and Restore in production
Thanks
Hari
MCDBA
"Tom Pennington" <NONEt2pennington@.comcast.net> wrote in message
news:OKUPhQdDEHA.2768@.tk2msftngp13.phx.gbl...
> We have a development SQL server and a production SQL server. Is there
> anyway to replicate what we create on the Dev box over to production box
> when we are finished our testing?
> Thanks.
> Tom
>|||Backup and restore, detach/attach/Alter scripts, update/insert queries...
Depending on exactly what you want to Move (anywhere from entire database
with data to one stored procedure) there a number of ways to accomplish
this.
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
"Tom Pennington" <NONEt2pennington@.comcast.net> wrote in message
news:OKUPhQdDEHA.2768@.tk2msftngp13.phx.gbl...
> We have a development SQL server and a production SQL server. Is there
> anyway to replicate what we create on the Dev box over to production box
> when we are finished our testing?
> Thanks.
> Tom
>|||We use ErWin To make changes to Dev.
Then we Point ErWin to QA, Etc and have it generate "Diff Scripts" for us.
The scripts are included with our Roll to Production plan.
Just another way to do the same thing
ErWin is Not "CHEAP" but I think it is a very powerful, valuable tool.
It's primary competitor is ER-Studio which I think is a little better.
Cheers
Greg Jackson
PDX, Oregon

Tuesday, March 27, 2012

Difference between Index & Statistics

Difference between Index & Statistics

Difference between Index & Statistics

Sunday, March 25, 2012

difference between 'Backup' and DB maint. plan backup?

difference between 'Backup' and DB maint. plan backup?

difference between 'Backup' and DB maint. plan backup?

Monday, March 19, 2012

Dictionarry order question

Dictionarry order question

Dictionarry order question

Wednesday, March 7, 2012

Development DB to Production DB

sql record counts

Blog Archive

About Me