Question Update statistics

Hi,
1. What is the main difference between update statistics and create
statistics?
2. If I use "update statistics" on a table that does not have any statistics
created, will it create statistics? If yes, what is the difference between
update statistics and create statistics?
3. I take it that if I use update statistics it will only update statistics
that are already exiting.
4. Does update statistics, sp_update stats, sp_createstats and create
statistics create/update statistics on indexes as well?
Thanks,
Jay"Jay S." <JayS@.discussions.microsoft.com> wrote in message
news:2ECC6B03-31E6-48AF-A5E4-6338179193B0@.microsoft.com...
> Hi,
> 1. What is the main difference between update statistics and create
> statistics?
Update refreshes existing stats. Create makes new ones.
> 2. If I use "update statistics" on a table that does not have any
> statistics
> created, will it create statistics? If yes, what is the difference between
> update statistics and create statistics?
No.
> 3. I take it that if I use update statistics it will only update
> statistics
> that are already exiting.
>
Yes.
> 4. Does update statistics, sp_update stats, sp_createstats and create
> statistics create/update statistics on indexes as well?
sp_updatestats updates stats on an index(only if they are "outdated" in
2005)
> Thanks,
> Jay
Check out this for more info:
http://www.microsoft.com/technet/prodtechnol/sql/2005/qrystats.mspx
--
Jason Massie
Web: http://statisticsio.com
RSS: http://feeds.feedburner.com/statisticsio

Question to create Measure for prior sale year and month

Hi All,

I have started with SSAS last week and getting into several options. I came across following difficulty. Please help if you have any ideas.

I’m building a report that has attributes A1, A2, A3, Year and Month and measures M1 and M2

A1

A2

A3

Year

Month

M1

M2

The M1 value is such that it is M2 of Month 6 of Year – 3.

For example, for Year 2007, month 3 the value of M2 is 100

And for year 2004, month 6 the value of M2 is 66

Then the report would be like

A1

A2

A3

Year

Month

M1

M2

a

b

c

2007

3

66

100

How to create M1 measure ?

Thanks in advance.

Sorry, but I don't quite follow what M1 represents? Is M1 the M2 value at a point in time that is 3 years prior to the current point in time?

Thanks,
Bryan

Question to create Measure for prior sale year and month

Hi All,

I have started with SSAS last week and getting into several options. I came across following difficulty. Please help if you have any ideas.

I’m building a report that has attributes A1, A2, A3, Year and Month and measures M1 and M2

A1

A2

A3

Year

Month

M1

M2

The M1 value is such that it is M2 of Month 6 of Year – 3.

For example, for Year 2007, month 3 the value of M2 is 100

And for year 2004, month 6 the value of M2 is 66

Then the report would be like

A1

A2

A3

Year

Month

M1

M2

a

b

c

2007

3

66

100

How to create M1 measure ?

Thanks in advance.

Sorry, but I don't quite follow what M1 represents? Is M1 the M2 value at a point in time that is 3 years prior to the current point in time?

Thanks,
Bryan

sql

Question regarding DataSet file (xsd) like the ones used on 3-teir design tutorial

Hi everyone,

I have a question. I am using a DataSet file as my data layer in my app. I'm trying to create a event TableAdaptor where one of the queries has two paramaters (@.start & @.end). Those two variables are used to search for items that have start dates that fall in between the two.

Every time I try to use this, the smart tab always brings up three paramaters (DataTable, start as date, end as date). How the hell does that DataTable get thrown in there. I executed the query in the builder section and it only asks for the two variables.

Any idea on this one?

Thanks!

A dataset is a collection of datatables|||

That part I knew, I'm just wondering why it's asking for it. I've created numerous tableAdapter and this is the first one that inserted a variable I don't need.

Here is the query I'm using inside the xsd, as you can see I only have two variables (@.start & @.end), so why does it generate a third variable of type DataTable?

========================================================================================

SELECT eventID,
eventTitle,
eventDescription,
eventStartTimeDate,
eventEndTimeDate,
eventCategoryID,
( SELECT eventCategoryName
FROM eventCategories
WHERE eventCategoryID = events.eventCategoryID
) AS eventCategoryName
FROM events
WHERE (eventStartTimeDate >= @.start AND eventStartTimeDate < @.end)
AND active = 1

|||

Hi,

Is the DataTable variable generated for the Fill method of DataTable? If yes, it is by design.

The Fill method needs to know the destination the select data flows to. In this case, you will need to supply an argument of DataTable type.

HTH. If this does not answer your question, please feel free to mark the post as Not Answered and reply. Thank you!

Question regarding creating If Else functions in my report

Hi, I'm new and i'm having trouble trying to create this query at the "DATA" tab of my rdl.

What i'm trying to do here is passing 2 employee name from two drop

down text box(doing a filtering with "From Employee" and "To

Employee"), and assign it to a global variable which i declared.

Is there something i miss out or i did worng which cause a error

"Incorrect syntax near the keyword 'BEGIN'. (.Net SqlClient Data

Provider)".

It would be so helpful of you if links of website or previous post or

any reference are provided, which give some basic tutorial on how to

create codes as shown below.

Thanks in advance

DECLARE @.G_StartEmp AS CHAR(10), @.G_EndEmp AS CHAR(10)

IF @.StartEmp > @.EndEmp

BEGIN

Set @.G_StartEmp = @.EndEmp

Set @.G_EndEmp = @.StartEmp

END

ELSE

BEGIN

Set @.G_StartEmp = @.StartEmp

Set @.G_EndEmp = @.EndEmp

END IF

BEGIN

select * from EMPLOYEE

where employeename between G_StartEmp and G_EndEmp

END

DId you wrap that code in a stored procedure ?

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||Hi, after spending some time again on this, i realize i added an extra "IF" , which is the cause of this error.

Thanks for taking your time to reply me Jens K. Suessmeyer.

DECLARE @.G_StartEmp AS CHAR(10), @.G_EndEmp AS CHAR(10)

IF @.StartEmp > @.EndEmp

BEGIN

Set @.G_StartEmp = @.EndEmp

Set @.G_EndEmp = @.StartEmp

END

ELSE

BEGIN

Set @.G_StartEmp = @.StartEmp

Set @.G_EndEmp = @.EndEmp

END (Originally was "END IF")

BEGIN

select * from EMPLOYEE

where employeename between G_StartEmp and G_EndEmp

END

Question on using CASE

Hello All,

I am pretty new to SQL Server and I am trying to create a view to gather the necessary data I need. I am not sure if CASE is what I should be using, or if I can even do what I need, but I am trying to capture the following information (I know the data looks a bit wacky, but I cannot post the real data so it is just an example).

Example data:
ID - DESC - STARTDATE - ENDDATE
1A - Pool - 9/21/06 - 9/23/06
1A - Pool - 9/21/05 - 9/23/05
1B - Garden - 9/2/06 - 9/4/06

I want to return the following data:
ID - DESC - STARTDATE - ENDDATE
1A - Pool - 9/21/05 - 9/23/05
1B - Garden - 9/4/06 - 9/4/06

Basically in my mind I am thinking along the lines of:
IF DESC = "Pool" THEN STARTDATE = "minimum STARTDATE"
ELSE STARTDATE = "ENDDATE"

I am having an issue trying to figure out how to create the syntax for this CASE statement.

Any help is appreciated.

ThanksWell you don't need a CASE statement for this. A simple aggregate query will handle the problem you describe:
select ID,
DESC,
min(STARTDATE) as STARTDATE,
max(ENDDATE) as ENDDATE
from YourTable
group by ID,
DESC...but I bet you will find that your problem is more complex than you describe, and that you are going to have to deal with gaps between date ranges that a simple MIN and MAX will overlook.|||Thanks for the reply.

I had tried that type of query but the real issue is I need the min STARTDATE if the DESC = "Pool". If the DESC is anything else I want the STARTDATE to take the ENDDATE.

Probably not possible?|||select ID,
DESC,
case when DESC = 'Pool' then STARTDATE else ENDDATE end as STARTDATE,
ENDDATE
from (select ID,
DESC,
min(STARTDATE) as STARTDATE,
max(ENDDATE) as ENDDATE
from YourTable
group by ID,
DESC) Subquery

You could probably do this without the subquery as well, but it is a little odd mixing aggregate and non-aggregate values in a single column.

question on trigger/procedure plz reply?

Can it be possible to create trigger/procedure
in following case
2 server server A and server B
A has db1
db1 has tblA

B has db2
db2 has tblB

can it be possible to create trigger on server A.db1.tblA as
insert/update that trigger add/update record in server B.db2.tb2

thnks
----
Hitendra(hitendra15@.gmail.com) writes:
> Can it be possible to create trigger/procedure
> in following case
> 2 server server A and server B
> A has db1
> db1 has tblA
> B has db2
> db2 has tblB
> can it be possible to create trigger on server A.db1.tblA as
> insert/update that trigger add/update record in server B.db2.tb2

Yes, you can set up linked servers with sp_addlinkedserver, and
then use four-part notation to refer to B.db2.dbo.tb2. You will
have to have DTS running on both servers, for it two work.

Notice that this arrangement is likely to have some impact on
concurrency on A.db1.dbo.tblA, as the transaction will run
considerably longer than it would, if the trigger had updated a
local table.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns961E39EF36BBYazorman@.127.0.0.1...
> (hitendra15@.gmail.com) writes:
>> Can it be possible to create trigger/procedure
>> in following case
>> 2 server server A and server B
>> A has db1
>> db1 has tblA
>>
>> B has db2
>> db2 has tblB
>>
>> can it be possible to create trigger on server A.db1.tblA as
>> insert/update that trigger add/update record in server B.db2.tb2
> Yes, you can set up linked servers with sp_addlinkedserver, and
> then use four-part notation to refer to B.db2.dbo.tb2. You will
> have to have DTS running on both servers, for it two work.
> Notice that this arrangement is likely to have some impact on
> concurrency on A.db1.dbo.tblA, as the transaction will run
> considerably longer than it would, if the trigger had updated a
> local table.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp

I think you mean you need DTC running on both servers - that one letter
makes a big difference... :-)

Simon|||Simon Hayes (sql@.hayes.ch) writes:
> I think you mean you need DTC running on both servers - that one letter
> makes a big difference... :-)

You're so right, Simon!

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Question on size restriction for Sql Express and Sql 2005 Standard

If i create a database in SqlEX and then move it to a sever running Sql 2005 standard, am Is my database still limited to the 4GB size?

No, I've tested:

create a database in SQL Express, then detach it;

attach to SQL 2005 Enterprise Edition;

Increase the data file size to 5GB, no problem

Question on Microsoft Association Rules

I build a data mining model to predict what are the best studying methods for the student to pass the examinaton.

Create Mining Model StudentAssociation (
Student_No long key,
Gender text discrete predict,
PassOrFail text discrete predict,
StudyMethod table predict ( MethodName text key )
) Using Microsoft_Association_Rules ( Minimum_Support=0.02, Minimum_Probability=0.03 )

The mining table will contain all the methods that the students use, no matter their examination is passed or failed.
The value of PassOrFail will have either 'Pass' or 'Fail'.

According to the above model, can I query the best studying methods?
Or I should only train the model with the student who pass the examination, and ignore all the failed.

Thanks.
Joe.

I wouldn't use AR, try Decision Trees, Naive Bayes, or Neural Nets. Also, if you're trying to predict what study methods indicate pass or fail, you only need to make PassOrFail predictable, not the nested table.

You definately need positive and negative examples to predict the result. You could create a clustering model on only the passing students to see which types of study methods group together for passing students.

|||

As your suggestion to create a clustering model on only the passing students, then I suppose to find that student uses both "method A" and "method B" will have a highest chance to pass the examination.
However, "Method A" and "Method B" may also have the highest probability to fail if I study the failed students. Am I right?
Do I need to create two clustering models, one for passing and one for fail, in order to have a complete picture?
Or, is there any methods to accomplish this task?

Thanks Jamie.

|||

Actually, if you only use passing students, then you won't see that Method A and Method B have higher chances than any other method, since all students passed. What you will see is what methods are used together by passing students. For example, there may be a group of passing students who use methods A and B, another using methods C and D, and yet another using A and D.

You are right in that these methods could just as easily be used by failing students. You should also create a similar model for failing students. You may find that the clusters are the same, they may be different, they may be similar with different proportions. For example, you may have an A and B cluster in both models, but in the passing model you find 40% of the students in A and B and only 5% in the failing model.

Another option if you want to use clustering is to make the Pass/Fail column "Predict Only" - in this case the algorithm will cluster based on all other attributes ignoring pass/fail, and then apply statistics for pass/fail across the clusters that were created. This will show if pass/fail is independent of method groupings or not (you can use the cluster diagram for this). However, if you want specifically to predict pass/fail based on study methods, you are better off using Trees or Neural Nets.

|||

Your answer is very useful for me.

Thanks Jamie.

Question on index creation for large table

Hi all--Given a table with the current setup and structure, I would like to create a clustered index for a table that gets updated once per week values I would like to keep unique:

CREATETABLE [dbo].[DBA_AUDIT_SESSION](

[INSTANCE_NAME] [varchar](16)NULL,

[HOST_NAME] [varchar](64)NULL,

[OS_USERNAME] [varchar](255)NULL,

[USERNAME] [varchar](30)NULL,

[USERHOST] [varchar](128)NULL,

[TERMINAL] [varchar](255)NULL,

[TIMESTAMP] [datetime] NOTNULL,

[ACTION_NAME] [varchar](28)NULL,

[LOGOFF_TIME] [datetime] NULL,

[LOGOFF_LREAD] [float] NULL,

[LOGOFF_PREAD] [float] NULL,

[LOGOFF_LWRITE] [float] NULL,

[LOGOFF_DLOCK] [varchar](40)NULL,

[SESSIONID] [float] NOTNULL,

[RETURNCODE] [float] NOTNULL,

[CLIENT_ID] [varchar](64)NULL,

[SESSION_CPU] [float] NULL,

[Import_Date] [datetime] NULL

)

This is is for an existing table within a SQL Server 2005 SP1 database.

I was thinking about creating an IDENTITY column and creating that as a composite primary key with TIMESTAMP, but the following posting seems to suggest that's not a good idea for performance and not the best design:

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=9&messageid=152846

Average size for this table is 1.75-2.5 million rows, and the TIMESTAMP field doesn't carry completely unique values. The data is sourced from an Oracle 9i and/or 10gR2 database, populated by an SSIS package. Suggestions on creating a good clustered index for this table? Would I gain value by using the NEWSEQUENTIALID function in SQL Server 2005 on a uniqueidentifier datatype column?

Thanks,

Jonathan

The primary question is why do you think that this table needs a 'clustered index'.

If you want to create a 'sequential' load, then clustering on an IDENTITY field is enough.

Typically, Audit tables have a minimal need for indexing -and it rarely has a primary factor of load sequence.

|||

Hi Arnie--I'm renovating the entire repository to factor in a couple of changes:

1. The audience for this data (manager, security & network administrator) commented on how slow queries returned data once three-four months' data accumulated in this repository.

2. I also have to store this on a VMWare-based virtual server with only 1 GB of RAM. (A request for additional resources was turned down a few months ago.) Given that, I'm looking to come up with the best possible indexing strategy with a need to store at least 3-6 months' data at a time, which leaves up to 11-12 million rows to query. That's an awful lot of data to sort through, so I'm trying to also create a long-term indexing strategy without reinventing the wheel for a server with fairly tight resource restraints.

Given there's a large amount of data that doesn't change more than once per month, a clustered index seems the best candidate for this table and another, similar table called 'dba_audit_trail', based on an Oracle-derived view. Thoughts?

- Jonathan

|||

I'd cluster index the [Timestamp] or [Import_Date] field (whichever one is capturing the datetime of the insert)-and NOT bother with an IDENTITY field.

(Duplicate [Timestamp] values are not an issue for the clustered index.)

IF all queries have a [Timestamp] criteria, I'd carefully examing other criteria filters to determine indexing the remaining fields.

|||OK--Thanks for the advice.

Question on encryption/ keys/ certificates/ etc.

SQL2K5
SP1
I was able to sucessfully create a Master Key, create a Certificate, create
a Symmetric Key and assign it to the Certificate, insert encrypted data, and
then decrypt/ read that data. Pretty cool stuff. But Im reading up on the
topic and Im under the impression that I should also have needed too:
Set Force Encryption to Yes.
Configure the DB engine to use a Cert.
Reboot the box.
But I didn't have to do any of that. I just went and verified the settings
and thats not how SQL is configured. Im obviosuly missing something pretty
big here, can someone please assist?
TIA, ChrisRThat's 2 different things, what you've been doing is encrypting data stored
in the database. The other stuff you are talking about is encrypting traffic
from clients to SQL Server and vice versa. If you don't have that
requirement then don't worry about it. In SQL 2005, standard SQL logins are
encrypted anyway by a self generated certificate however general traffic
to/from the server (batches/results etc) are not. They are 2 completely
different and separate things.
HTH,
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
"ChrisR" <NotAChance@.ms.com> wrote in message
news:esEA%23%23AtGHA.1876@.TK2MSFTNGP06.phx.gbl...
> SQL2K5
> SP1
> I was able to sucessfully create a Master Key, create a Certificate,
> create
> a Symmetric Key and assign it to the Certificate, insert encrypted data,
> and
> then decrypt/ read that data. Pretty cool stuff. But Im reading up on the
> topic and Im under the impression that I should also have needed too:
> Set Force Encryption to Yes.
> Configure the DB engine to use a Cert.
> Reboot the box.
> But I didn't have to do any of that. I just went and verified the settings
> and thats not how SQL is configured. Im obviosuly missing something pretty
> big here, can someone please assist?
> TIA, ChrisR
>|||As mentioned, I was missing something pretty big. ;-)
Thanks Jasper.
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:eaL$0QCtGHA.4784@.TK2MSFTNGP04.phx.gbl...
> That's 2 different things, what you've been doing is encrypting data
stored
> in the database. The other stuff you are talking about is encrypting
traffic
> from clients to SQL Server and vice versa. If you don't have that
> requirement then don't worry about it. In SQL 2005, standard SQL logins
are
> encrypted anyway by a self generated certificate however general traffic
> to/from the server (batches/results etc) are not. They are 2 completely
> different and separate things.
> --
> HTH,
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
>
> "ChrisR" <NotAChance@.ms.com> wrote in message
> news:esEA%23%23AtGHA.1876@.TK2MSFTNGP06.phx.gbl...
the[vbcol=seagreen]
settings[vbcol=seagreen]
pretty[vbcol=seagreen]
>

Question on encryption/ keys/ certificates/ etc.

SQL2K5
SP1
I was able to sucessfully create a Master Key, create a Certificate, create
a Symmetric Key and assign it to the Certificate, insert encrypted data, and
then decrypt/ read that data. Pretty cool stuff. But Im reading up on the
topic and Im under the impression that I should also have needed too:
Set Force Encryption to Yes.
Configure the DB engine to use a Cert.
Reboot the box.
But I didn't have to do any of that. I just went and verified the settings
and thats not how SQL is configured. Im obviosuly missing something pretty
big here, can someone please assist?
TIA, ChrisRThat's 2 different things, what you've been doing is encrypting data stored
in the database. The other stuff you are talking about is encrypting traffic
from clients to SQL Server and vice versa. If you don't have that
requirement then don't worry about it. In SQL 2005, standard SQL logins are
encrypted anyway by a self generated certificate however general traffic
to/from the server (batches/results etc) are not. They are 2 completely
different and separate things.
--
HTH,
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
"ChrisR" <NotAChance@.ms.com> wrote in message
news:esEA%23%23AtGHA.1876@.TK2MSFTNGP06.phx.gbl...
> SQL2K5
> SP1
> I was able to sucessfully create a Master Key, create a Certificate,
> create
> a Symmetric Key and assign it to the Certificate, insert encrypted data,
> and
> then decrypt/ read that data. Pretty cool stuff. But Im reading up on the
> topic and Im under the impression that I should also have needed too:
> Set Force Encryption to Yes.
> Configure the DB engine to use a Cert.
> Reboot the box.
> But I didn't have to do any of that. I just went and verified the settings
> and thats not how SQL is configured. Im obviosuly missing something pretty
> big here, can someone please assist?
> TIA, ChrisR
>|||As mentioned, I was missing something pretty big. ;-)
Thanks Jasper.
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:eaL$0QCtGHA.4784@.TK2MSFTNGP04.phx.gbl...
> That's 2 different things, what you've been doing is encrypting data
stored
> in the database. The other stuff you are talking about is encrypting
traffic
> from clients to SQL Server and vice versa. If you don't have that
> requirement then don't worry about it. In SQL 2005, standard SQL logins
are
> encrypted anyway by a self generated certificate however general traffic
> to/from the server (batches/results etc) are not. They are 2 completely
> different and separate things.
> --
> HTH,
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
>
> "ChrisR" <NotAChance@.ms.com> wrote in message
> news:esEA%23%23AtGHA.1876@.TK2MSFTNGP06.phx.gbl...
> > SQL2K5
> > SP1
> >
> > I was able to sucessfully create a Master Key, create a Certificate,
> > create
> > a Symmetric Key and assign it to the Certificate, insert encrypted data,
> > and
> > then decrypt/ read that data. Pretty cool stuff. But Im reading up on
the
> > topic and Im under the impression that I should also have needed too:
> >
> > Set Force Encryption to Yes.
> > Configure the DB engine to use a Cert.
> > Reboot the box.
> >
> > But I didn't have to do any of that. I just went and verified the
settings
> > and thats not how SQL is configured. Im obviosuly missing something
pretty
> > big here, can someone please assist?
> >
> > TIA, ChrisR
> >
> >
>

question on cube hierarchies

Hi, all experts here,

Thank you for your kind attention.

I am having a question on cube hierarchies creation. Is it necessary to create two different hierarchies for the hierarchies as below?

.................................................................................

Year-Quarter-Month-Day

Year-Month-Day

..................................................................................

Should we create two different user-defined hierarchies for the above hierarchies? As one of the hierarchy is actually included in another one. Therefore I am wondering if we need to create two different ones for them or just create the one with more levels.

Hope my question is clear for your help and I am looking forward to hearing from you shortly.

With best regards,

Yours sincerely,

Hello Helen. I recommend to create the one with all levels.

Do not forget the attribute relations between the levels in the user hierarchy.

HTH

Thomas Ivarsson

|||

Hi, Thomas,

Thanks a lot for your kind advices.

With best regards,

Yours sincerely,

Question on connection to SQL Server using windows authentication

I have a domain user account through which I can log on a machine. The machine allows me to use this domain user account to create DB in SQL Server using windows authentication. How can I define a connection string to connect SQL Server? My connection string is like:
string str = "server=(local);uid='"+userName+"';password='"+password+"';database='master';connection timeout=15";

This one works on another machine where I use SA account. But this one does not work using windows authentication. I tried to use domainname\myname as user name, but failed.
Could any one give me some suggestion?I solved the problem.|||Hi bagofbones.

I have the same problem.

Please post your solution.

Thanks.

DS