Question when subtracting two queries

I want to return the difference number of records.

Here are the queries:

--subtracting columns with columns and descriptions (columns - columns and descriptions)
--difference of 30 records
--243
select
h.name as 'DataBase Name'
,t.name as 'Table Name'
,c.name as 'Column Name'
from sys.tables t
inner join sys.schemas h
on h.schema_id = t.schema_id
inner join sys.columns c
on t.object_id = c.object_id
and not exists
(
--213
select
h.name as 'DataBase Name'
,t.name as 'Table Name'
,c.name as 'Column Name'
from sys.extended_properties s
inner join sys.tables t
on s.major_id = t.object_id
inner join sys.schemas h
on h.schema_id = t.schema_id
inner join sys.columns c
on s.major_id = c.object_id
and s.minor_id = c.column_id
)

I'm not getting anything back. I should be getting back 30 records that have null descriptions.

Please help.Problem resolved. Thinking was off.

Quote:

Originally Posted by parkc

I want to return the difference number of records.

Here are the queries:

--subtracting columns with columns and descriptions (columns - columns and descriptions)
--difference of 30 records
--243
select
h.name as 'DataBase Name'
,t.name as 'Table Name'
,c.name as 'Column Name'
from sys.tables t
inner join sys.schemas h
on h.schema_id = t.schema_id
inner join sys.columns c
on t.object_id = c.object_id
and not exists
(
--213
select
h.name as 'DataBase Name'
,t.name as 'Table Name'
,c.name as 'Column Name'
from sys.extended_properties s
inner join sys.tables t
on s.major_id = t.object_id
inner join sys.schemas h
on h.schema_id = t.schema_id
inner join sys.columns c
on s.major_id = c.object_id
and s.minor_id = c.column_id
)

I'm not getting anything back. I should be getting back 30 records that have null descriptions.

Please help.

|||

Quote:

Originally Posted by parkc

I want to return the difference number of records.

Here are the queries:

--subtracting columns with columns and descriptions (columns - columns and descriptions)
--difference of 30 records
--243
select
h.name as 'DataBase Name'
,t.name as 'Table Name'
,c.name as 'Column Name'
from sys.tables t
inner join sys.schemas h
on h.schema_id = t.schema_id
inner join sys.columns c
on t.object_id = c.object_id
and not exists
(
--213
select
h.name as 'DataBase Name'
,t.name as 'Table Name'
,c.name as 'Column Name'
from sys.extended_properties s
inner join sys.tables t
on s.major_id = t.object_id
inner join sys.schemas h
on h.schema_id = t.schema_id
inner join sys.columns c
on s.major_id = c.object_id
and s.minor_id = c.column_id
)

I'm not getting anything back. I should be getting back 30 records that have null descriptions.

Please help.

TRY below query and check if that works...

select * from
(
select h.name as 'DataBase Name',t.name as 'Table Name',c.name as 'Column Name'
from sys.tables t
inner join sys.schemas h on h.schema_id = t.schema_id
inner join sys.columns c on t.object_id = c.object_id
) x
left outer join
(
--213
select h.name as 'DataBase Name',t.name as 'Table Name',c.name as 'Column Name'
from sys.extended_properties s
inner join sys.tables t on s.major_id = t.object_id
inner join sys.schemas h on h.schema_id = t.schema_id
inner join sys.columns c
on s.major_id = c.object_id
and s.minor_id = c.column_id
) y on x.[DataBase Name] = y.[DataBase Name]
where y.[DataBase Name] is null

Question regarding Identitiy field

Guys,
I have a identity flag set on a column. When i am testing the system,
inserting records the identity goes all the way to 60 and so on. I need to
put the table in production system now. However i need to reset the identity
field to start from 1. How can i clear the identity field and make it start
from the begining.
Thanks
MannyUse this
DBCC CheckIdent('TableName')
"Manny Chohan" wrote:

> Guys,
> I have a identity flag set on a column. When i am testing the system,
> inserting records the identity goes all the way to 60 and so on. I need to
> put the table in production system now. However i need to reset the identi
ty
> field to start from 1. How can i clear the identity field and make it star
t
> from the begining.
> Thanks
> Manny|||I'm not sure why you care about the starting value but you can either
truncate the table or execute DBCC CHECKIDENT with the RESEED option.
Hope this helps.
Dan Guzman
SQL Server MVP
"Manny Chohan" <MannyChohan@.discussions.microsoft.com> wrote in message
news:6C4D0EBE-7AA5-4F18-9EAA-ED2547B1BD23@.microsoft.com...
> Guys,
> I have a identity flag set on a column. When i am testing the system,
> inserting records the identity goes all the way to 60 and so on. I need to
> put the table in production system now. However i need to reset the
> identity
> field to start from 1. How can i clear the identity field and make it
> start
> from the begining.
> Thanks
> Manny|||Dan,
Just fyi, dbcc checkident defaults to reseed... so,
checkident ('TableName')
is equivilent to
checkident ('TableName', RESEED)
"Dan Guzman" wrote:

> I'm not sure why you care about the starting value but you can either
> truncate the table or execute DBCC CHECKIDENT with the RESEED option.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Manny Chohan" <MannyChohan@.discussions.microsoft.com> wrote in message
> news:6C4D0EBE-7AA5-4F18-9EAA-ED2547B1BD23@.microsoft.com...
>
>|||Thanks All
"CBretana" wrote:
> Dan,
> Just fyi, dbcc checkident defaults to reseed... so,
> checkident ('TableName')
> is equivilent to
> checkident ('TableName', RESEED)
> "Dan Guzman" wrote:
>|||I am with Dan all the way on this one. Why do you care about the value of
the identity? I admit that I would probably want to reset it myself when
going into production, just because it looks more "tidy." It is however,
always concerning when someone states that they want to know how to do this
because it often means they are using these values in some manner where the
user will care about the value, and identities are pretty bad for this sort
of thing (users HATE gaps, and gaps are just generally part of the identity
experience, since row with the identity property cannot be updated (ever).)
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"Manny Chohan" <MannyChohan@.discussions.microsoft.com> wrote in message
news:6C4D0EBE-7AA5-4F18-9EAA-ED2547B1BD23@.microsoft.com...
> Guys,
> I have a identity flag set on a column. When i am testing the system,
> inserting records the identity goes all the way to 60 and so on. I need to
> put the table in production system now. However i need to reset the
> identity
> field to start from 1. How can i clear the identity field and make it
> start
> from the begining.
> Thanks
> Manny|||In theory, I agree 100% with you and Dan on this... I do not believe the
value of any surrogate key, much less an Identoty, should be significant to
anyone...
However, in practice, simply out ofa sense of esthetics, I find myself doing
exactly the same thing this gentleman asked about...
In theory, if we really didn't care about the actual values, we wouldn't
always set the seed value for Identity columns = 1, we'd set it to the lowes
t
legal value for the underlying datatype, (TinyInt, SmallInt, Int).
(Either zero, (0), -32,768, or -2,147,483,648, respectively)
But practically, we do care what the value is, because in coding, and
debugging, and manipulating the data in Query Analyzer, we use these values,
and so they do matter.
"Louis Davidson" wrote:

> I am with Dan all the way on this one. Why do you care about the value of
> the identity? I admit that I would probably want to reset it myself when
> going into production, just because it looks more "tidy." It is however,
> always concerning when someone states that they want to know how to do thi
s
> because it often means they are using these values in some manner where th
e
> user will care about the value, and identities are pretty bad for this sor
t
> of thing (users HATE gaps, and gaps are just generally part of the identit
y
> experience, since row with the identity property cannot be updated (ever).
)
> --
> ----
--
> Louis Davidson - drsql@.hotmail.com
> SQL Server MVP
> Compass Technology Management - www.compass.net
> Pro SQL Server 2000 Database Design -
> http://www.apress.com/book/bookDisplay.html?bID=266
> Blog - http://spaces.msn.com/members/drsql/
> Note: Please reply to the newsgroups only unless you are interested in
> consulting services. All other replies may be ignored :)
> "Manny Chohan" <MannyChohan@.discussions.microsoft.com> wrote in message
> news:6C4D0EBE-7AA5-4F18-9EAA-ED2547B1BD23@.microsoft.com...
>
>|||That was pretty much what I meant too. It is really a weird thing too.
Sure when we start out programming it is easier to have the value start out
at 1, since it is easier to type while doing initial programming, but by all
logic if we really mean that we don't care about the value then starting
at -minvalue would be better (if we are lucky our values will get that big
anyhow!)
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
news:7EF6C6ED-EE40-4AA2-8173-8A99226E0CEA@.microsoft.com...
> In theory, I agree 100% with you and Dan on this... I do not believe the
> value of any surrogate key, much less an Identoty, should be significant
> to
> anyone...
> However, in practice, simply out ofa sense of esthetics, I find myself
> doing
> exactly the same thing this gentleman asked about...
> In theory, if we really didn't care about the actual values, we wouldn't
> always set the seed value for Identity columns = 1, we'd set it to the
> lowest
> legal value for the underlying datatype, (TinyInt, SmallInt, Int).
> (Either zero, (0), -32,768, or -2,147,483,648, respectively)
> But practically, we do care what the value is, because in coding, and
> debugging, and manipulating the data in Query Analyzer, we use these
> values,
> and so they do matter.
>
> "Louis Davidson" wrote:
>|||Manny,
you have two options:
1. use DBCC CHECKIDENT statement with RESEED option (see Books OnLine),
2. use TRUNCATE TABLE on your table (however THIS WILL DELETE ALL ROWS IN A
TABLE WITHOUT ROLLBACK OPTION!)
Pawel
"Manny Chohan" wrote:

> Guys,
> I have a identity flag set on a column. When i am testing the system,
> inserting records the identity goes all the way to 60 and so on. I need to
> put the table in production system now. However i need to reset the identi
ty
> field to start from 1. How can i clear the identity field and make it star
t
> from the begining.
> Thanks
> Manny|||> 2. use TRUNCATE TABLE on your table (however THIS WILL DELETE ALL ROWS IN
> A
> TABLE WITHOUT ROLLBACK OPTION!)
TRUNCATE does allow a ROLLBACK. However, like other SQL data modification
statements, the transaction will need to be started explicitly unless
IMPLICIT_TRANSACTIONS is on.
CREATE TABLE MyTable(Col1 int NOT NULL)
INSERT INTO MyTable VALUES(1)
BEGIN TRAN
TRUNCATE TABLE MyTable
SELECT Col1 FROM MyTable
ROLLBACK
SELECT Col1 FROM MyTable
Hope this helps.
Dan Guzman
SQL Server MVP
"Pawel Potasinski" <PawePotasiski@.discussions.microsoft.com> wrote in
message news:11BF931F-D7BC-41A3-B0FD-E2F85943B14B@.microsoft.com...
> Manny,
> you have two options:
> 1. use DBCC CHECKIDENT statement with RESEED option (see Books OnLine),
> 2. use TRUNCATE TABLE on your table (however THIS WILL DELETE ALL ROWS IN
> A
> TABLE WITHOUT ROLLBACK OPTION!)
> Pawel
> "Manny Chohan" wrote:
>

question please

please i hav a question..can uoy answer me?

i have a workers table which contains 4 records such as (id,name,category, status) ...

category like : a,b,c

for example :

id - name - category - status
1 - Jone - A - free
2 - Tom - B - busy
3 - Adm - c - free
4 - Raul - B - free
5 - Sami -A - busy

i want to write query to obtain 3 workers that their status = free and belong to each category (a,b and c )

such as here :
1 - Jone - A - free
3 - Adm - c - free
4 - Raul - B - free

can you help me ?Hope this work for you.
Good luck!

SELECT id,
name,
category
FROM WORKERS
WHERE status = 'free'

|||

Use this query...

select Workers.* From Workers Join
(
Select Category,Max(main.id) id From Workers main Where 3 =
(Select Count(*) From Workers Sub Where Main.id <= Sub.ID And Main.Category = Sub.Category And Main.Status=Sub.Status) And Status='Free'
Group BY
Category
Union All
Select Category,Max(main.id) From Workers main Where 2 =
(Select Count(*) From Workers Sub Where Main.id <= Sub.ID And Main.Category = Sub.Category And Main.Status=Sub.Status) And Status='Free'
Group BY
Category
Union All
Select Category,Max(main.id) From Workers main Where 1 =
(Select Count(*) From Workers Sub Where Main.id <= Sub.ID And Main.Category = Sub.Category And Main.Status=Sub.Status) And Status='Free'
Group BY
Category
) as Data On Data.id = Workers.Id
Order By 3

It will exactly obtain 3 workers that their status = free and belong to each category

|||

-- --
-- Kanjo:
--
-- The things that I understand from your request are:
-- 1. You do want to filter out any status other
-- than the 'free' status.
-- 2. You want to select the worker information
-- for 'free' workers -- especially the category
-- of the worker.
--
-- The things that I am not sure of are:
-- 1. Do you want to filter out any worker that is
-- not in catories 'A', 'B' or 'C'?
-- 2. Do you want all free users in any of the
-- categories or
-- 3. Do you only want the 'Top' free worker of
-- each of the categories?
-- 4. Are you running SQL Server 2000 or 2005?
-- (In this case it might not matter.)
--
-- Each of these isues has an impact on the way that
-- the query needs to be written.
-- --
set nocount on
declare @.workers table
( id integer not null,
name varchar (20) not null,
category char (1) not null,
status varchar (10) not null
)

insert into @.workers values (1, 'Jone', 'A', 'free')
insert into @.workers values (2, 'Tom' , 'B', 'busy')
insert into @.workers values (3, 'Adm', 'C', 'free')
insert into @.workers values (4, 'Raul', 'B', 'free')
insert into @.workers values (5, 'Sami', 'A', 'busy')
insert into @.workers values (6, 'Roni', 'A', 'free')
insert into @.workers values (7, 'Suzy', 'D', 'free')

--select * from @.workers

-- --
-- If there is no need to filter based on category
-- Vincent's query will work just fine:
-- --
select id,
name,
category
from @.workers
where status = 'free'
-- and category in ('A','B','C')

-- Sample Output:

-- id name category
-- -- -- --
-- 1 Jone A
-- 3 Adm C
-- 4 Raul B
-- 6 Roni A
-- 7 Suzy D

-- --
-- However, if you do need to filter based on the
-- category then the where clause will need to be
-- modified to reflect that need
-- --
select id,
name,
category,
status
from @.workers
where status = 'free'
and category in ('A','B','C')

-- - Sample Output --

-- id name category status
-- -- -- -- -
-- 1 Jone A free
-- 3 Adm C free
-- 4 Raul B free
-- 6 Roni A free

-- --
-- There are other ways of obtaing the "top" list if
-- that is the requirement; this is just one of the
-- ways.
-- --
select a.id,
a.name,
a.category,
a.status
from @.workers a
inner join
( select category,
min (id) as min_id
from @.workers
where category in ('A','B','C')
group by category
) b
on a.id = b.min_id

-- -- Sample Output:

-- id name category status
-- -- -- -- -
-- 1 Jone A free
-- 2 Tom B busy
-- 3 Adm C free

-- --
-- Mani:
--
-- HELP! I am not getting the expected results and
-- I am still trying to figure out why. Can you spot
-- where I went wrong?
-- --
select workers.* From @.workers workers Join
(
Select Category,Max(main.id) id From @.workers main Where 3 =
(Select Count(*) From @.workers Sub Where Main.id < Sub.ID And Main.Category = Sub.Category And Main.Status=Sub.Status) And Status='Free'
Group BY
Category
Union All
Select Category,Max(main.id) From @.workers main Where 2 =
(Select Count(*) From @.workers Sub Where Main.id < Sub.ID And Main.Category = Sub.Category And Main.Status=Sub.Status) And Status='Free'
Group BY
Category
Union All
Select Category,Max(main.id) From @.workers main Where 1 =
(Select Count(*) From @.workers Sub Where Main.id < Sub.ID And Main.Category = Sub.Category And Main.Status=Sub.Status) And Status='Free'
Group BY
Category
) as Data On Data.id = workers.Id
Order By 3

-- Unexpected Output: -

-- id name category status
-- -- -- -- -
-- 1 Jone A free

|||

Fixed... ThanQ!

select workers.* From @.workers workers Join
(

Select Category,Max(main.id) id From @.workers main Where 3 =
(Select Count(*) From @.workers Sub Where Main.id <= Sub.ID And Main.Category = Sub.Category And Main.Status=Sub.Status) And main.Status='Free'
Group BY
Category
Union All
Select Category,Max(main.id) From @.workers main Where 2 =
(Select Count(*) From @.workers Sub Where Main.id <= Sub.ID And Main.Category = Sub.Category And Main.Status=Sub.Status) And main.Status='Free'
Group BY
Category
Union All
Select Category,Max(main.id) From @.workers main Where 1 =
(Select Count(*) From @.workers Sub Where Main.id <= Sub.ID And Main.Category = Sub.Category And Main.Status=Sub.Status) And main.Status='Free'
Group BY
Category
) as Data On Data.id = workers.Id
Order By 3

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 SQL experission for complicated query

Hello All,

I have a problem on how to retrieve records from a database using SQL query. It is a little bit complicated query. I do not know what SQL query experission is correct.

In the database table, there are four fields "ClassID", "Personal Nmae", "Age", "Score". The following is an example for illustration.

Class Name Age Score
0 Bob 20 78 <====
0 John 26 66 <****
2 Wilson 28 88
1 John 26 77 <****
3 Alice 25 56
1 Bob 20 89 <====

In the above example table, I would like to know the scores of students who share the same name, at the same age, BUT in different class (ClassID is specified by the user). Actually, I need a SQL SELECT experission which will lead to a query result (a recordset) like the following

Bob 78 89
John 66 77select T.name
, T.score
from daTable as T
inner
join (
select name
, age
from daTable
group
by name
, age
having count(*) > 1
) as D
on D.name = T.name
and D.age = T.age|||r937,

Thank you for your reply, i will try your code and report latter.|||yes, it is correct. However, what is the SQL experission like if we use 'self join' to implement the same thing.|||Curiously is there anything wrong with doing the following?

SELECT s1.Name,s2.Score
FROM student_results s1
JOIN student_results s2
ON s1.Name=s2.Name
AND s1.Age=s2.Age
AND s1.Class<>s2.Class|||not "wrong" per se, but what if a given name has three scores -- what does your query return? what does mine?|||Thanks aschk abd r937,

Actually, in my application there are only two scores for a given name. One more further question,

If we have another table 'ClassTotalScore' consisting of two fields "Class" , "TotalScore"

Can I add some clause to the above suggested SQL experissions to get the sum of total scores of Class=0 and Class=2. Or I have to use another SQL statement to get the sum of total scores of two classes.

Moreover, if either way is possible, I would like to know whether ONE SQL statement is necessarilyfaster than TWO SQL statements.

Actually, those data in the two tables originally are stored in varibles in my VC++ 6.0 program. However, as more and more data are produced, the PC memory is almost exhaused. Hence, i have to resort to the database technology. I notice that the running speed of the databased based program is 10 times slower than the original program. I wonder if this is normal.|||Sounds like your database abstraction layer might be doing something odd (either that or your database system is setup badly/running slowly).
I would still recommend rudy's method of SQL Query by the way. I haven't tested mine with more than two results (because i deleted the tables) but from experience I know that rudy tends to be right ;)|||Unless your programme is using some really nasty inefficient way of storing all your SQL results?|||aschk,

thanks.

I think i did not express my problem clearly in the previous posts. Actually, the speed of storing data (write) is ok for me. I am compliant with the speed of retrieving data (read data)-- more than 10 times slower than the memory based method.

aschk, are you sure this is not normal.

BY the way, there are 1200 plus records in the database.

10 times slower than the original program|||Is the database on the same machine as the program? Is your database cache limit really low? 1200 is nothing really. If you said 12 Million records then I would question it. If your database is on a seperate machine and the records are big then transferring all that data across a network might be your slowdown point. Where does your program take the longest?|||aschk,

thanks.

Yes, I did the tests (both reading--retrieving data and writing--storing data) in the same program on a DELL Optiplex GX620 (3Ghz CPU), 2G memory.

I am new to SQL server 2000, where is the setting for database cache?|||What else is your server doing? Because for it to write quicker than it reads is definitely odd. If you run the query directly in Query analyser how long does it take?|||write = 1 row

read = all 1200 rows

of course it will be slower :cool:

cy, what indexes have you crated?

Question on SP_MakeWebTask

I'm using SP_MakeWebTask it's working OK. The problem is that if a
particular query returns no records, the string that gets written from
the:
<%begindetail%>
<%insert_data_here%>
<<%enddetail%>
part of the template file is: "The specified statement did not generate
any data"
Is there a way to substitute this string with a different one?
Thanks,
JoeHi
Have you tried using a UNION that returns an default record if your original
query returned nothing?
John
"joef" wrote:

> I'm using SP_MakeWebTask it's working OK. The problem is that if a
> particular query returns no records, the string that gets written from
> the:
> <%begindetail%>
> <%insert_data_here%>
> <<%enddetail%>
> part of the template file is: "The specified statement did not generate
> any data"
> Is there a way to substitute this string with a different one?
> Thanks,
> Joe
>sql

Question on returning all records when using parameters...

I'm trying to fully utilize parameters on a report.

I have 3 parameters, a start date, end date, and a cause.

The start and the end date are working as expected. What I would like is if a user fails to select a cause (queried from a lookup dataset) I would like to return all records, not filtering by cause. I'm using parameters handed to the dataset to do this and the report will not allow me not to select a value.

Should I be using Filter, Report Parameters, or some other means to solve this?

Any advice would be appreciated as I don't want to "KLUDGE" up the system with a bunch of reports each with varying levels of criteria.

Thanx!

TR

I handled a similar situation with creating an "All" choice in my optional parameter and setting this as the default value. If I understand what you are looking to accomplish, I believe this may work for you as well.|||Simone's suggestion will work, you may also use the 'multi-value' drop down which will automatically create the 'All' option. Assuming you are using SSRS 2005...|||

Say that I create an ALL choice on my drop down... What parameter do I put behind it such that my query will know to return all records as opposed to 'all' the string...

IE: Select * from myTable where myField = 'All'

would obviously try looking for the string value 'All' am I missing something really obvious? I have seen others suggest the all method so I have no doubt it works and I think I'm just missing part of the equation.

Thanks for the help.

TR

|||If you use a multivalued parameter, it will send in the values for the 'cause' parameter, so you can use an IN statement.

For example:

SELECT * FROM tblMyTable Where Cause in ('value1, value2, value3')|||

Hi Andy,

The problem with a multival box is that I will have between 24 and 60 values. Obviously too many to have a user selecting.

Thanks for the reply though.

TR

|||You can always go down the path of using dynamic SQL and generating your statment on the fly. You'll want to consider the performance impact, but on relatively basic queries, it should be negligble.

In that situation, set the 'All' label of the Cause parameter to a value of NULL (or -1, or 'All', whatever float your boat), check for the Cause query parameter value corresponding to All and if so, omit it from the WHERE clause.

Something like this:

If @.Cause IS Null then

Set @.SQL = 'SELECT * FROM Table'

Else

Set @.SQL = 'SELECT * FROM Table WHERE Cause = ' + @.Cause

Exec (@.SQL)|||We use dynamic SQL all the time....for further information see: http://sommarskog.se/|||

The SQL that I use when I want the 'all' option is as follows:

Code Snippet

Select * from table where id = case when @.id = -1(all) then id else @.id end

Hope this helps.

Simone

Question on repeated query

I have to select a number of fields for a report. Name Address, city,
state zip, etc. from a list of customers.
I have to select 2000 records from each of 29 states.
Is there a way to do this without repeating and repeating the code in
QA, or by changing the state manually and running it each time?
I have to select 2000 customers from WV, 2000 from VA, 2000 from FL,
2000 from TX and so on.
It's more of just a random selection of customers, with no other
requirements other than the name & address are a good one (i.e. non-null
last name, good zip code, etc).
Any help appreciated.Try,
select
Name Address, city, state, zip
from
t1 as a
where
customerid in (
select top 2000 customerid
from t1 as b
where b.state = a.state
)
go
If you run this query again, do not expect to pull same result because I am
not using "order by" clause.
AMB
"Blasting Cap" wrote:

> I have to select a number of fields for a report. Name Address, city,
> state zip, etc. from a list of customers.
> I have to select 2000 records from each of 29 states.
> Is there a way to do this without repeating and repeating the code in
> QA, or by changing the state manually and running it each time?
> I have to select 2000 customers from WV, 2000 from VA, 2000 from FL,
> 2000 from TX and so on.
> It's more of just a random selection of customers, with no other
> requirements other than the name & address are a good one (i.e. non-null
> last name, good zip code, etc).
> Any help appreciated.
>

question on reindex

Hi,
i have several tables in production whose contents are renewd totally in 1
week. So everyd day we delete ~15% records and then insert 15% new.
And after a few days, the performances drops :

TABLE level scan performed.
- Pages Scanned........................: 169617
- Extents Scanned.......................: 21630
- Extent Switches.......................: 153827
- Avg. Pages per Extent..................: 7.8
- Scan Density [Best Count:Actual Count]......: 13.78% [21203:153828]
- Logical Scan Fragmentation ..............: 45.06%
- Extent Scan Fragmentation ...............: 52.66%
- Avg. Bytes Free per Page................: 5042.5
- Avg. Page Density (full)................: 37.70%

I can't program a dbcc reindex every day because of concurrent access (it
locks the tables too long), actually i can only program it on sunday.

What else can i do ? I can adjust the fill factor but how to find the good
value if i don't want to waste space.

The total size of the database is ~150GB.

ThxHave you tried defragmenting by using DBCC INDEXDEFRAG?

Unlike DBCC DBREINDEX (or the index building operation in general),
DBCC INDEXDEFRAG is an online operation. It does not hold locks long
term and thus will not block running queries or updates

Denis the SQL Menace
http://sqlservercode.blogspot.com/
paul wrote:

Quote:

Originally Posted by

Hi,
i have several tables in production whose contents are renewd totally in 1
week. So everyd day we delete ~15% records and then insert 15% new.
And after a few days, the performances drops :
>
TABLE level scan performed.
- Pages Scanned........................: 169617
- Extents Scanned.......................: 21630
- Extent Switches.......................: 153827
- Avg. Pages per Extent..................: 7.8
- Scan Density [Best Count:Actual Count]......: 13.78% [21203:153828]
- Logical Scan Fragmentation ..............: 45.06%
- Extent Scan Fragmentation ...............: 52.66%
- Avg. Bytes Free per Page................: 5042.5
- Avg. Page Density (full)................: 37.70%
>
I can't program a dbcc reindex every day because of concurrent access (it
locks the tables too long), actually i can only program it on sunday.
>
What else can i do ? I can adjust the fill factor but how to find the good
value if i don't want to waste space.
>
The total size of the database is ~150GB.
>
Thx

Question on displaying large number of record?

I have datagrid that needs to display a log table which has more than million records.

Since it it huge number, it is not possible to get dataset using "select * from log_table" to fill and to bind to datagrid.

Is there anyway to display first 100 rows on first page and show next 100 rows if use clicks on page 2?

Thank you very much in advance!

Justin

since its simply not feasible to bring that much data back from sql, you should look at performing the paging logic in sql server itself.
sql would only return one page of data at a time

here's some links to see how it can be done:

http://weblogs.asp.net/pwilson/archive/2003/10/10/31456.aspx

http://www.sqlmag.com/articles/index.cfm?articleid=40505

http://www.4guysfromrolla.com/webtech/062899-1.shtml

i'm sure there are plenty more examples floating around..

|||

You could use the TOP clause in your query

or

You could use .net assemblies with SQL server......(CREATE ASSEMBLY ...)

|||I am using Oracle but thank you anyway... I got idea...

|||

In PL/SQL you can use row_number() function, or if you don't need to sort the records, simply use the ROWNUM column.

Question on analysis services cube partiton duration

Hi, all experts here,

It is really strange as I only got a small partition for my cube which only got around 150 rows records. But when I processed the cueb, it took a long while to finish? Why is that? Any advices?

Thanks a lot and I am looking forward to hearing from you shortly.

With best regards,

Yours sincerely,

How long does it take to run the query the AS executes in the relational database?|||

I think that is because the low space on my local system drive.

Thanks a lot.

With best regards,

Yours sincerely,