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 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/pr...5/qrystats.mspx
Jason Massie
Web: http://statisticsio.com
RSS: http://feeds.feedburner.com/statisticsio

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 regarding using the sql update command.

Here I have the following command

Dim dtNowAs DateTime = DateTime.Now
SqlDataSource1.UpdateCommand="Update [db] Set [LW]='TRUE', LWD=dtnow Where [PK]=@.PK"

What I was ttrying to accomplish was , in my grid view, when someone clicks update, it would automatically set LW to true and set LWD to today's date. No user intervention required. However, I figured the above script would not work. What would I have to do to make LWD = dtnow? I do not want to give the user the option to update anything.

Make it GetDate(). This is sql function to give u current date. No need to use a variable...

in case if u want to use variable

Dim dtNowAs DateTime = DateTime.Now

SqlDataSource1.UpdateCommand="Update [db] Set [LW]='TRUE', LWD=' " + dtnow.ToString() + " ' Where [PK]=@.PK"

|||

NICE! Thanks, anyway to clock this 3 hours ahead? The hosting company is 3 hours difference.

|||

ya that's correct os till its using my UK location

question regarding referenced views and replication order

You can update the view definition and hope that this updates the
dependencies correctly. If it doesn't you can use sp_addscriptexec to add
all the views in the correct order. Note that in SQL Server 2005 you can
specify the article order (@.processing_order) to get round this type of
issue.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
Stefan,
replication of views and procs doesn't follow the usual paradigm in
replication. Changes to these programming objects aren't picked up by the
log-reader, so we have to resort to some sort of manual solution to pick up
changes to replicated objects and apply them which ever way we go about it.
The script to generate the view creation scripts could be based on the view
definition - in which case it'll always be upto date if you even need to run
the whole thing again. Ad-hoc changes can meanwhile be done using individual
views.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com

Question on using patindex

Hello,

I am trying to update a column in a table with an 8 digit string.
The 8 digit number is obtained from another column which is a comments
field.

e.g.

Comments Field :

1) RD-V0216970 RQST NON SMKING KING Unknown # : 22338921
2) Received verbal authorization Authorization # 040345
3) international plaza, singapore # 96722540

The code that I am using is

UPDATE U SET U.NUM =
CASE
WHEN U.BOOKED_COMMENTS_TXT LIKE
('%[0123456789][0123456789][0123456789][0123456789][0123456789][0123456789]%')
THEN
SUBSTRING(U.BOOKED_COMMENTS_TXT,
PATINDEX('%[0123456789][0123456789][0123456789][0123456789][0123456789][0123456789]%',
U.BOOKED_COMMENTS_TXT), 8)
ELSE NULL
END
FROM UNKNOWN1_RESERVATIONS U

Here's what my result set looks like

1)V0216970
2)040345
3)96722540

But this is how I want my result set to look like

1)22338921
2)null
3)96722540

What I need is a way to restrict the search criteria to exactly 8
numeric digits. Any suggestions will be helpful.

Thanks in advanceSQL_developer (vlavanya@.gmail.com) writes:
> I am trying to update a column in a table with an 8 digit string.
> The 8 digit number is obtained from another column which is a comments
> field.
> e.g.
> Comments Field :
> 1) RD-V0216970 RQST NON SMKING KING Unknown # : 22338921
> 2) Received verbal authorization Authorization # 040345
> 3) international plaza, singapore # 96722540
> The code that I am using is
>...
> Here's what my result set looks like
> 1)V0216970
> 2)040345
> 3)96722540
> But this is how I want my result set to look like
> 1)22338921
> 2)null
> 3)96722540
> What I need is a way to restrict the search criteria to exactly 8
> numeric digits. Any suggestions will be helpful.

From the sample it appears that the string you are looking for is
always at the end of the comments field. In this case, this could do:

UPDATE tbl
SET col = CASE WHEN reverse(str)
LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][^0-9]'
THEN right(str, 8)
ELSE NULL
END

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thank your for your response Erland.

There are times when the string is in the middle or beginning of the
comments field. That's the reason I had to go with Patindex.
Is there anyway I can include the length check into the code ?|||On 31 Mar 2005 06:32:21 -0800, SQL_developer wrote:

>Thank your for your response Erland.
>There are times when the string is in the middle or beginning of the
>comments field. That's the reason I had to go with Patindex.
>Is there anyway I can include the length check into the code ?

Hi SQL_developer,

The reason your query failed is that you searched for 6 numeric
characters instead of 8. Changing that solves it. I also replace the
proprietary UPDATE FROM syntax with the much more portable ANSI-standard
UPDATE syntax - in this case, there is no reason at all to use UPDATE
FROM!

CREATE TABLE UNKNOWN1_RESERVATIONS
(ID int NOT NULL PRIMARY KEY,
NUM char(8),
BOOKED_COMMENTS_TXT varchar(200))
go
INSERT INTO UNKNOWN1_RESERVATIONS (ID, BOOKED_COMMENTS_TXT)
SELECT 1, 'RD-V0216970 RQST NON SMKING KING Unknown # : 22338921'
UNION ALL
SELECT 2, 'Received verbal authorization Authorization # 040345'
UNION ALL
SELECT 3, 'international plaza, singapore # 96722540 Not in the middle'
go
UPDATE UNKNOWN1_RESERVATIONS
SET NUM = CASE
WHEN BOOKED_COMMENTS_TXT LIKE
'%[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]%'
THEN SUBSTRING(BOOKED_COMMENTS_TXT,

PATINDEX('%[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]%',
BOOKED_COMMENTS_TXT), 8)
ELSE NULL
END
go
SELECT * FROM UNKNOWN1_RESERVATIONS
go
DROP TABLE UNKNOWN1_RESERVATIONS
go

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hugo Kornelis (hugo@.pe_NO_rFact.in_SPAM_fo) writes:
> The reason your query failed is that you searched for 6 numeric
> characters instead of 8. Changing that solves it.

Not really. Consider:

INSERT INTO UNKNOWN1_RESERVATIONS (ID, BOOKED_COMMENTS_TXT)
SELECT 1, 'RD-V02169709 RQST NON SMKING KING Unknown # : 22338921'

Do we really want the number after the RD-V?

Here is an improved version of your query - built on the assumption
that the number must be surrounded by blanks:

UPDATE UNKNOWN1_RESERVATIONS
SET NUM = CASE
WHEN BOOKED_COMMENTS_TXT + ' ' LIKE
'% [0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9] %'
THEN SUBSTRING(BOOKED_COMMENTS_TXT,
PATINDEX('% [0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9] %',
BOOKED_COMMENTS_TXT + ' ') + 1, 8)
ELSE NULL
END

> I also replace the proprietary UPDATE FROM syntax with the much more
> portable ANSI-standard UPDATE syntax - in this case, there is no reason
> at all to use UPDATE FROM!

So what! In the general case in SQL Server there is all reason to use
FROM in UPDATE statements, since it gives you so much power and flexibility.
Sure, for this query it's an overkill, but if you think that always
using an alias is good programming style, then you need a FROM clause.
(And that is just one of the reasons that FROM with UPDATE is so good,
it permits you to specify an alias for the table being updated.)

Yeah, it's correct, if your join conditions are incomplete, your UPDATE
will be imcomplete. And I've seen more than one who had gone wrong with
his correlated subqueries. Nevermind that the performance even with
correct correlated subqueries is generally a lot poorer.

I really wish everyone would stop people from bashing people from using
FROM in UPDATE statements. This is a newsgroup for MS SQL Server. If
you don't like FROM in UPDATE clauses, go elsewhere.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||On Thu, 31 Mar 2005 21:26:02 +0000 (UTC), Erland Sommarskog wrote:

>Hugo Kornelis (hugo@.pe_NO_rFact.in_SPAM_fo) writes:
>> The reason your query failed is that you searched for 6 numeric
>> characters instead of 8. Changing that solves it.
>Not really. Consider:
> INSERT INTO UNKNOWN1_RESERVATIONS (ID, BOOKED_COMMENTS_TXT)
> SELECT 1, 'RD-V02169709 RQST NON SMKING KING Unknown # : 22338921'
>Do we really want the number after the RD-V?
>Here is an improved version of your query - built on the assumption
>that the number must be surrounded by blanks:
(snip)

Hi Erland,

Thanks for that!

I assumed that the OP wanted to use the first 8-digit number. Upon
rereading his post, I now see that he writes "exactly 8 numeric digits".

Let's hope that your assumption about the number always being surrounded
by spaces is correct. If not, a further change would be:

UPDATE UNKNOWN1_RESERVATIONS
SET NUM = CASE
WHEN ' ' + BOOKED_COMMENTS_TXT + ' ' LIKE

'%[^0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][^0-9]%'
THEN SUBSTRING(BOOKED_COMMENTS_TXT,

PATINDEX('%[^0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][^0-9]%',
' ' + BOOKED_COMMENTS_TXT + ' '), 8)
ELSE NULL
END
(untested)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thank you for your response Erland and Hugo!

I used the UPDATE FROM statement because this code will be used as a
step in a DTS package and hence I need all the flexibility I can get :)
.. I also find that using an Alias makes it easy for me to read my code
at a later point of time.

The table I am getting my comments field from, gets updated daily and I
will be running the DTS to pick up the 8 digit number every week.
As for the number itself, it may or may not have blanks surrounding it.
I used Hugo's code and it seems to work for most cases.
There are a few cases where the comments field looks like

" Confirmation number 4234235608532, Room for 4"

So when I run this code, it picks up the first 8 digits. But I don't
want it to pick up the 8 digits in this case. It should return null
when the number is greater than 8 digits. Sorry I didn't mention this
earlier. It's just that there are so many records and I keep running
into more situations like these as I modify the code.

Thanks for all your help!|||On 31 Mar 2005 14:36:23 -0800, SQL_developer wrote:

(snip)
>I used Hugo's code and it seems to work for most cases.
>There are a few cases where the comments field looks like
>" Confirmation number 4234235608532, Room for 4"
>So when I run this code, it picks up the first 8 digits. But I don't
>want it to pick up the 8 digits in this case. It should return null
>when the number is greater than 8 digits.
(snip)

Hi SQL_developer,

Did you test it with the code in my first post (addressed to you), or
the code in my second post (replied to Erland)? The latter *SHOULD*
exclude the example above - but I've already shut down my server, so I
can't test it now. Let me know if you saw this happening with the code
in my second post - it's probably a typo; I'll look into it tomorrow.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hello Hugo,

I used the code from your second post. To make it easier for you to run
it in a query analyser I will post the entire code to you.

CREATE TABLE UNKNOWN1_RESERVATIONS
(ID int NOT NULL PRIMARY KEY,
NUM char(8),
BOOKED_COMMENTS_TXT varchar(200))
go
INSERT INTO UNKNOWN1_RESERVATIONS (ID, BOOKED_COMMENTS_TXT)
SELECT 1, 'RD-V0216970 RQST NON SMKING KING Unknown # : 22338921'
UNION ALL
SELECT 2, 'Received verbal authorization Authorization # 0403455'
UNION ALL
SELECT 3, 'international plaza, singapore #96722540Not in the middle'
UNION ALL
select 4, 'Confirmation number 4234235608532, Room for 4'
go
UPDATE UNKNOWN1_RESERVATIONS
SET NUM = CASE
WHEN '' + BOOKED_COMMENTS_TXT + '' LIKE
'%[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]%'
THEN
SUBSTRING(BOOKED_COMMENTS_TXT,
PATINDEX('%[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]%', '' +
BOOKED_COMMENTS_TXT + ''), 8)
ELSE NULL
END
go
SELECT * FROM UNKNOWN1_RESERVATIONS
go
DROP TABLE UNKNOWN1_RESERVATIONS
go

Thanks again!|||Hello Hugo,

I used the code from your second post. To make it easier for you to run
it in a query analyser I will post the entire code to you.

CREATE TABLE UNKNOWN1_RESERVATIONS
(ID int NOT NULL PRIMARY KEY,
NUM char(8),
BOOKED_COMMENTS_TXT varchar(200))
go
INSERT INTO UNKNOWN1_RESERVATIONS (ID, BOOKED_COMMENTS_TXT)
SELECT 1, 'RD-V0216970 RQST NON SMKING KING Unknown # : 22338921'
UNION ALL
SELECT 2, 'Received verbal authorization Authorization # 0403455'
UNION ALL
SELECT 3, 'international plaza, singapore #96722540Not in the middle'
UNION ALL
select 4, 'Confirmation number 4234235608532, Room for 4'
go
UPDATE UNKNOWN1_RESERVATIONS
SET NUM = CASE
WHEN '' + BOOKED_COMMENTS_TXT + '' LIKE
'%[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]%'
THEN
SUBSTRING(BOOKED_COMMENTS_TXT,
PATINDEX('%[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]%', '' +
BOOKED_COMMENTS_TXT + ''), 8)
ELSE NULL
END
go
SELECT * FROM UNKNOWN1_RESERVATIONS
go
DROP TABLE UNKNOWN1_RESERVATIONS
go

Thanks again!|||On 31 Mar 2005 14:53:50 -0800, SQL_developer wrote:

>Hello Hugo,
>I used the code from your second post. To make it easier for you to run
>it in a query analyser I will post the entire code to you.
(snip)

Hi SQL_developer,

The code you posed was NOT the code from my second post; it was a mix of
code from my first and second post. I've copied the script from your
post into Query Analyzer and corrected the errors - here it is:

Note - use copy and paste to copy it to QA at your end, don't copy it by
hand. One of the errors in your version was that you changed ' ' (quote,
space, quote) to '' (quote, quote). These spaces are very significant!
Another error was that you left out the additional [^0-9] (note the
caret!!) in the LIKE and PATINDEX pattern.

CREATE TABLE UNKNOWN1_RESERVATIONS
(ID int NOT NULL PRIMARY KEY,
NUM char(8),
BOOKED_COMMENTS_TXT varchar(200))
go
INSERT INTO UNKNOWN1_RESERVATIONS (ID, BOOKED_COMMENTS_TXT)
SELECT 1, 'RD-V0216970 RQST NON SMKING KING Unknown # : 22338921'
UNION ALL
SELECT 2, 'Received verbal authorization Authorization # 0403455'
UNION ALL
SELECT 3, 'international plaza, singapore #96722540Not in the middle'
UNION ALL
select 4, 'Confirmation number 4234235608532, Room for 4'
go
UPDATE UNKNOWN1_RESERVATIONS
SET NUM = CASE
WHEN ' ' + BOOKED_COMMENTS_TXT + ' ' LIKE
'%[^0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][^0-9]%'
THEN
SUBSTRING(BOOKED_COMMENTS_TXT,
PATINDEX('%[^0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][^0-9]%', ' ' +
BOOKED_COMMENTS_TXT + ' '), 8)
ELSE NULL
END
go
SELECT * FROM UNKNOWN1_RESERVATIONS
go
DROP TABLE UNKNOWN1_RESERVATIONS
go

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hi Hugo,

Thanks for your reply.

For some reason when I copy and paste data from the post to the DTS
package the Space between the Quotes disappears. That explains why I
did not have the spaces in my code.

I'm sorry about the missing Claret in my code. But I tested the latest
version and it works like a charm. Thank you so much for all your help!

Question on SQL Update behaviour

I have a question on how SQL Server updates records. Say assuming if I have
a table with 50000 records.. and i need to update a particular field C in
this table, based on field A and field B on the same table. I can either
write a set based SP or a cursor based SP to do that.
If I update it using cursors, I can see the records being updated 1 by 1. If
I update the whole thing using a set based SP, am I right to say that I will
not see any records updated till the process is over? Does SQL Server does
the transaction commitment itself if I write it using the set-based
approach?
Thanks.
Hi,
Update with set will do a batch updat based on the condition you are gving
in the where clause. So until the transaction is commited
you will not be able to see change. Where as Cursor does a row by row
operation. So after each record update you could see the
change.
Have a look into ISOLATION LEVEL topic in books online.
Thanks
Hari
SQL Server MVP
"Nestor" <n3570r@.yahoo.com> wrote in message
news:uncViVfgFHA.2904@.tk2msftngp13.phx.gbl...
>I have a question on how SQL Server updates records. Say assuming if I have
>a table with 50000 records.. and i need to update a particular field C in
>this table, based on field A and field B on the same table. I can either
>write a set based SP or a cursor based SP to do that.
> If I update it using cursors, I can see the records being updated 1 by 1.
> If I update the whole thing using a set based SP, am I right to say that I
> will not see any records updated till the process is over? Does SQL Server
> does the transaction commitment itself if I write it using the set-based
> approach?
> Thanks.
>
|||thanks for the prompt response... i'm really curious about how the table
will be locked up since i'm updating the table based on conditions from
fields that are in the same table as well...
will cursors work better in this case than batch insert due to the lockups?
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:uGQThcfgFHA.576@.TK2MSFTNGP15.phx.gbl...
> Hi,
> Update with set will do a batch updat based on the condition you are gving
> in the where clause. So until the transaction is commited
> you will not be able to see change. Where as Cursor does a row by row
> operation. So after each record update you could see the
> change.
> Have a look into ISOLATION LEVEL topic in books online.
> Thanks
> Hari
> SQL Server MVP
> "Nestor" <n3570r@.yahoo.com> wrote in message
> news:uncViVfgFHA.2904@.tk2msftngp13.phx.gbl...
>
|||you can actually see the changes taking place by running a query with the
table hint nolock as in: select count(*) from mytable with (nolock) where...
I use this approach at times to see how far in a large insert or update is so
I can calculate how much longer the statement will take.
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Build, Comparison and Synchronization from Source Control = Database change
management for SQL Server
"Nestor" wrote:

> thanks for the prompt response... i'm really curious about how the table
> will be locked up since i'm updating the table based on conditions from
> fields that are in the same table as well...
> will cursors work better in this case than batch insert due to the lockups?
>
>
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:uGQThcfgFHA.576@.TK2MSFTNGP15.phx.gbl...
>
>

Question on SQL Update behaviour

I have a question on how SQL Server updates records. Say assuming if I have
a table with 50000 records.. and i need to update a particular field C in
this table, based on field A and field B on the same table. I can either
write a set based SP or a cursor based SP to do that.
If I update it using cursors, I can see the records being updated 1 by 1. If
I update the whole thing using a set based SP, am I right to say that I will
not see any records updated till the process is over? Does SQL Server does
the transaction commitment itself if I write it using the set-based
approach?
Thanks.Hi,
Update with set will do a batch updat based on the condition you are gving
in the where clause. So until the transaction is commited
you will not be able to see change. Where as Cursor does a row by row
operation. So after each record update you could see the
change.
Have a look into ISOLATION LEVEL topic in books online.
Thanks
Hari
SQL Server MVP
"Nestor" <n3570r@.yahoo.com> wrote in message
news:uncViVfgFHA.2904@.tk2msftngp13.phx.gbl...
>I have a question on how SQL Server updates records. Say assuming if I have
>a table with 50000 records.. and i need to update a particular field C in
>this table, based on field A and field B on the same table. I can either
>write a set based SP or a cursor based SP to do that.
> If I update it using cursors, I can see the records being updated 1 by 1.
> If I update the whole thing using a set based SP, am I right to say that I
> will not see any records updated till the process is over? Does SQL Server
> does the transaction commitment itself if I write it using the set-based
> approach?
> Thanks.
>|||thanks for the prompt response... i'm really curious about how the table
will be locked up since i'm updating the table based on conditions from
fields that are in the same table as well...
will cursors work better in this case than batch insert due to the lockups?
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:uGQThcfgFHA.576@.TK2MSFTNGP15.phx.gbl...
> Hi,
> Update with set will do a batch updat based on the condition you are gving
> in the where clause. So until the transaction is commited
> you will not be able to see change. Where as Cursor does a row by row
> operation. So after each record update you could see the
> change.
> Have a look into ISOLATION LEVEL topic in books online.
> Thanks
> Hari
> SQL Server MVP
> "Nestor" <n3570r@.yahoo.com> wrote in message
> news:uncViVfgFHA.2904@.tk2msftngp13.phx.gbl...
>|||you can actually see the changes taking place by running a query with the
table hint nolock as in: select count(*) from mytable with (nolock) where...
I use this approach at times to see how far in a large insert or update is s
o
I can calculate how much longer the statement will take.
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Build, Comparison and Synchronization from Source Control = Database change
management for SQL Server
"Nestor" wrote:

> thanks for the prompt response... i'm really curious about how the table
> will be locked up since i'm updating the table based on conditions from
> fields that are in the same table as well...
> will cursors work better in this case than batch insert due to the lockups
?
>
>
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:uGQThcfgFHA.576@.TK2MSFTNGP15.phx.gbl...
>
>

Question on SQL Update behaviour

I have a question on how SQL Server updates records. Say assuming if I have
a table with 50000 records.. and i need to update a particular field C in
this table, based on field A and field B on the same table. I can either
write a set based SP or a cursor based SP to do that.
If I update it using cursors, I can see the records being updated 1 by 1. If
I update the whole thing using a set based SP, am I right to say that I will
not see any records updated till the process is over? Does SQL Server does
the transaction commitment itself if I write it using the set-based
approach?
Thanks.Hi,
Update with set will do a batch updat based on the condition you are gving
in the where clause. So until the transaction is commited
you will not be able to see change. Where as Cursor does a row by row
operation. So after each record update you could see the
change.
Have a look into ISOLATION LEVEL topic in books online.
Thanks
Hari
SQL Server MVP
"Nestor" <n3570r@.yahoo.com> wrote in message
news:uncViVfgFHA.2904@.tk2msftngp13.phx.gbl...
>I have a question on how SQL Server updates records. Say assuming if I have
>a table with 50000 records.. and i need to update a particular field C in
>this table, based on field A and field B on the same table. I can either
>write a set based SP or a cursor based SP to do that.
> If I update it using cursors, I can see the records being updated 1 by 1.
> If I update the whole thing using a set based SP, am I right to say that I
> will not see any records updated till the process is over? Does SQL Server
> does the transaction commitment itself if I write it using the set-based
> approach?
> Thanks.
>|||thanks for the prompt response... i'm really curious about how the table
will be locked up since i'm updating the table based on conditions from
fields that are in the same table as well...
will cursors work better in this case than batch insert due to the lockups?
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:uGQThcfgFHA.576@.TK2MSFTNGP15.phx.gbl...
> Hi,
> Update with set will do a batch updat based on the condition you are gving
> in the where clause. So until the transaction is commited
> you will not be able to see change. Where as Cursor does a row by row
> operation. So after each record update you could see the
> change.
> Have a look into ISOLATION LEVEL topic in books online.
> Thanks
> Hari
> SQL Server MVP
> "Nestor" <n3570r@.yahoo.com> wrote in message
> news:uncViVfgFHA.2904@.tk2msftngp13.phx.gbl...
>>I have a question on how SQL Server updates records. Say assuming if I
>>have a table with 50000 records.. and i need to update a particular field
>>C in this table, based on field A and field B on the same table. I can
>>either write a set based SP or a cursor based SP to do that.
>> If I update it using cursors, I can see the records being updated 1 by 1.
>> If I update the whole thing using a set based SP, am I right to say that
>> I will not see any records updated till the process is over? Does SQL
>> Server does the transaction commitment itself if I write it using the
>> set-based approach?
>> Thanks.
>|||you can actually see the changes taking place by running a query with the
table hint nolock as in: select count(*) from mytable with (nolock) where...
I use this approach at times to see how far in a large insert or update is so
I can calculate how much longer the statement will take.
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Build, Comparison and Synchronization from Source Control = Database change
management for SQL Server
"Nestor" wrote:
> thanks for the prompt response... i'm really curious about how the table
> will be locked up since i'm updating the table based on conditions from
> fields that are in the same table as well...
> will cursors work better in this case than batch insert due to the lockups?
>
>
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:uGQThcfgFHA.576@.TK2MSFTNGP15.phx.gbl...
> > Hi,
> >
> > Update with set will do a batch updat based on the condition you are gving
> > in the where clause. So until the transaction is commited
> > you will not be able to see change. Where as Cursor does a row by row
> > operation. So after each record update you could see the
> > change.
> >
> > Have a look into ISOLATION LEVEL topic in books online.
> >
> > Thanks
> > Hari
> > SQL Server MVP
> >
> > "Nestor" <n3570r@.yahoo.com> wrote in message
> > news:uncViVfgFHA.2904@.tk2msftngp13.phx.gbl...
> >>I have a question on how SQL Server updates records. Say assuming if I
> >>have a table with 50000 records.. and i need to update a particular field
> >>C in this table, based on field A and field B on the same table. I can
> >>either write a set based SP or a cursor based SP to do that.
> >>
> >> If I update it using cursors, I can see the records being updated 1 by 1.
> >> If I update the whole thing using a set based SP, am I right to say that
> >> I will not see any records updated till the process is over? Does SQL
> >> Server does the transaction commitment itself if I write it using the
> >> set-based approach?
> >>
> >> Thanks.
> >>
> >
> >
>
>

Question on Querys

Ok in Replication for sql 2000,'

what happens when a query such as 'update tableA set a=1 where id=5' affects 1 row. What will happen when it does not affect any row.

Aaron

hi

1) with one row

in case of a merge replication => the updated Data replicated to the subscriber

in case of a transaction replication => the statement replicated and affects 1 rows on the subscriber again

2) with no row

in case of a merge replication => NOTHING

in case of a transaction replication => the statement replicated and affects 0 rows on the subscriber again

In Case of only one row, there is not big difference

in case of 100000 rows there is a big difference, because in case of merge replication, all of 100000 rows replicated step by step, in case of transaction replication, the statement will block the replication a long time, because the statement (not the result) will be replicate to the subscriber

bye

Thorsten Ueberschaer

Question on parameter.Add for an SQL Insert

I'm trying to update my database table from my dataset.

In the following statement, the ? represents the "size as integer" of the SqlDbType of DateTime and I can't fiqure out what to put there.

command.Parameters.Add("@.ItemDate", SqlDbType.DateTime, ?,"ItemDate")

In the following statement I understand the "50" since it is the size of the field, but I don't understand what to do with the DateTime.

command.Parameters.Add("@.ItemText", SqlDbType.NVarChar, 50, "ItemText")

Thank you for your help

This chart might help:

http://www.carlprothman.net/Technology/DataTypeMapping/tabid/97/Default.aspx

Buck Woody

Question on parameter.Add for an SQL Insert

I'm trying to update my database table from my dataset.

In the following statement, the ? represents the "size as integer" of the SqlDbType of DateTime and I can't fiqure out what to put there.

command.Parameters.Add("@.ItemDate", SqlDbType.DateTime, ?,"ItemDate")

In the following statement I understand the "50" since it is the size of the field, but I don't understand what to do with the DateTime.

command.Parameters.Add("@.ItemText", SqlDbType.NVarChar, 50, "ItemText")

Thank you for your help

This chart might help:

http://www.carlprothman.net/Technology/DataTypeMapping/tabid/97/Default.aspx

Buck Woody

Question on OUTPUT feature of DML

I need to audit inserts/updates/deletes on active tables to audit tables on a set of tables that have foreign key constaints with the update cascade and delete cascade defined. I can explicitly code the delete/update on the parent table to perform an OUTPUT to an audit table, but how do I OUTPUT the cascaded delete/update that happens on the child table because of the FK constraint with delete cascade defined without having to resort to triggers.

Thanks,

-chiraj

You might want to check into the use of DML triggers for this kind of issue, but this is not something that I have used much.

Dave

|||

Thanks for the response.

Using DML triggers is a no-brainer. I have used them all my life. I was wondering if it could be accomplished with the OUTPUT clause. I believe it is a limitation of the OUTPUT clause unless someone can show me otherwise.

Thanks,

-chiraj.

Question on large cube update processing

Hi, all experts,

Is there any way for us to process the updated cube partly ? In my case I only want to add KPIs to my cube which are related to a few measure groups. In this case, it seems like we have to process the whole partitions related to these measure groups which the new added KPIs are related to. But as to large cube with miliions of records, this could take a reasonable time. Therefore, I am wondering if there is any way that we are able to update and process only the new added properties such as these new added KPIs to the cube instead of having to process the large chunk of the cube data? I personally think it will be really great to have this feature.

Is there any way to achieve this in SQL Server 2005 Analysis Services? I am looking forward to hearing from you shortly and thanks a lot for your advices.

With best regards,

Yours sincerely,

Hi,

I think you just need to deploy your project.

Regards

|||

Hi,

Thanks. But as what I have always seen in my BIDS , whenever I deploy a project, the system process it first and then deploy it.

I am not quite sure what you really mean by deploy here if that is not the 'deploy' I understand.

Regards,

|||

Lucas is right, you just need to deploy without processing.

There is an option under the deployment settings of the project that sets whether the project is processed after it is deployed. I think the default setting is "Always Process", but you can change this to "Never" and the project will be deployed without initiating a processing operation.

For changes to things like the calc script and KPI's, you do not need to reprocess your data, deploying your changes is enough.

Question on data optimizations

The database maintenance plan wizards present the ability to do either
data/index page reorgs or update statistics.
Is there any reason you can't do both?
Is it because an update of the statistics is automatically done on a
reorg?
Thanks in advance.
Gerry
Hi

> Is it because an update of the statistics is automatically done on a
> reorg?
When SQL Server rebuild indexes ,statistics ares automatically to be
updated
"DataPro" <datapro01@.yahoo.com> wrote in message
news:1164806696.954151.173330@.l39g2000cwd.googlegr oups.com...
> The database maintenance plan wizards present the ability to do either
> data/index page reorgs or update statistics.
> Is there any reason you can't do both?
> Is it because an update of the statistics is automatically done on a
> reorg?
> Thanks in advance.
> Gerry
>
|||DataPro wrote:
> The database maintenance plan wizards present the ability to do either
> data/index page reorgs or update statistics.
> Is there any reason you can't do both?
> Is it because an update of the statistics is automatically done on a
> reorg?
> Thanks in advance.
> Gerry
>
Reindexing will update the statistics...
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||Hello,
You are correct; Statistics will be updated as part of reindex.
Thanks
Hari
"DataPro" <datapro01@.yahoo.com> wrote in message
news:1164806696.954151.173330@.l39g2000cwd.googlegr oups.com...
> The database maintenance plan wizards present the ability to do either
> data/index page reorgs or update statistics.
> Is there any reason you can't do both?
> Is it because an update of the statistics is automatically done on a
> reorg?
> Thanks in advance.
> Gerry
>
|||Thanks much to all.

Question on data optimizations

The database maintenance plan wizards present the ability to do either
data/index page reorgs or update statistics.
Is there any reason you can't do both?
Is it because an update of the statistics is automatically done on a
reorg?
Thanks in advance.
GerryHi

> Is it because an update of the statistics is automatically done on a
> reorg?
When SQL Server rebuild indexes ,statistics ares automatically to be
updated
"DataPro" <datapro01@.yahoo.com> wrote in message
news:1164806696.954151.173330@.l39g2000cwd.googlegroups.com...
> The database maintenance plan wizards present the ability to do either
> data/index page reorgs or update statistics.
> Is there any reason you can't do both?
> Is it because an update of the statistics is automatically done on a
> reorg?
> Thanks in advance.
> Gerry
>|||DataPro wrote:
> The database maintenance plan wizards present the ability to do either
> data/index page reorgs or update statistics.
> Is there any reason you can't do both?
> Is it because an update of the statistics is automatically done on a
> reorg?
> Thanks in advance.
> Gerry
>
Reindexing will update the statistics...
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Hello,
You are correct; Statistics will be updated as part of reindex.
Thanks
Hari
"DataPro" <datapro01@.yahoo.com> wrote in message
news:1164806696.954151.173330@.l39g2000cwd.googlegroups.com...
> The database maintenance plan wizards present the ability to do either
> data/index page reorgs or update statistics.
> Is there any reason you can't do both?
> Is it because an update of the statistics is automatically done on a
> reorg?
> Thanks in advance.
> Gerry
>|||Thanks much to all.

Question on data optimizations

The database maintenance plan wizards present the ability to do either
data/index page reorgs or update statistics.
Is there any reason you can't do both?
Is it because an update of the statistics is automatically done on a
reorg?
Thanks in advance.
GerryHi
> Is it because an update of the statistics is automatically done on a
> reorg?
When SQL Server rebuild indexes ,statistics ares automatically to be
updated
"DataPro" <datapro01@.yahoo.com> wrote in message
news:1164806696.954151.173330@.l39g2000cwd.googlegroups.com...
> The database maintenance plan wizards present the ability to do either
> data/index page reorgs or update statistics.
> Is there any reason you can't do both?
> Is it because an update of the statistics is automatically done on a
> reorg?
> Thanks in advance.
> Gerry
>|||DataPro wrote:
> The database maintenance plan wizards present the ability to do either
> data/index page reorgs or update statistics.
> Is there any reason you can't do both?
> Is it because an update of the statistics is automatically done on a
> reorg?
> Thanks in advance.
> Gerry
>
Reindexing will update the statistics...
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Hello,
You are correct; Statistics will be updated as part of reindex.
Thanks
Hari
"DataPro" <datapro01@.yahoo.com> wrote in message
news:1164806696.954151.173330@.l39g2000cwd.googlegroups.com...
> The database maintenance plan wizards present the ability to do either
> data/index page reorgs or update statistics.
> Is there any reason you can't do both?
> Is it because an update of the statistics is automatically done on a
> reorg?
> Thanks in advance.
> Gerry
>|||Thanks much to all.