Knowing when <NULL>

I'm trying to change the <NULL> fields of my table, but I don't know how to tell the query to look for <NULL
For example:

select * from MyTable
where fieldx = <NULL
This doesn't work.
How should it be?Try this:

select * from MyTable where fieldx IS NULL

Terri

Kind of cross-tab query

Hi
I'd like to get the results below from this sort of data. I might also have
additional tables that need another 'prods' type column.
Thanks
Andrew
declare @.docs table (docID int primary key, docname varchar(25))
declare @.prods table (prodID int primary key, docID int, prodname
varchar(25))
insert @.docs values (1, 'doc1')
insert @.docs values (2, 'doc2')
insert @.docs values (3, 'doc3')
insert @.prods values (1, 1, 'prod1')
insert @.prods values (2, 2, 'prod2')
insert @.prods values (3, 1, 'prod3')
insert @.prods values (4, 2, 'prod4')
insert @.prods values (5, 3, 'prod5')
insert @.prods values (6, 2, 'prod6')
/*
docID docname prods
-- -- --
1 doc1 prod1, prod3
2 doc2 prod2, prod4, prod6
3 doc3 prod5
*/In general, a recommended approach is to extract the resultset outside the
server and massage the data to appropriate display format using some client.
Regarding the workarounds for forcing this at the server, you can check out
the following links:
( For SQL 2005 only )
http://groups.google.com/group/micr...br />
9b9b968a
( For SQL 2000 & 2005 )
http://groups.google.com/group/micr...br />
6dd9e73e
Anith|||For all types of static and dynamic crosstabs server side it's recommended
you check out RAC.Powerful and easy.
www.rac4sql.net

Killing a User Process

Hi All
Here is the scenario, a user has a query running which has
taken far too long and needs to be killed. This is a front
end and the user does not have access to SQL Server.
1. Can that user Kill the Process.
2. What wuld be the code to do that?
Many Thanks
PaulHi Paul,
1. Most likely not. To kill a SQL Server process you have to be a member of
the sysadmin or processadmin roles, and ordinary users usually aren't.
2. KILL <spid> where spid is the process is of the process.
Note that KILLing a process will cause all the transactions that have been
started by the process to be rolled back, so it can take some time before
the process is actually really killed.
--
Jacco Schalkwijk MCDBA, MCSD, MCSE
Database Administrator
Eurostop Ltd.
"paul Blackler" <pblackler@.themis.co.uk> wrote in message
news:06ae01c37081$75949630$a601280a@.phx.gbl...
> Hi All
> Here is the scenario, a user has a query running which has
> taken far too long and needs to be killed. This is a front
> end and the user does not have access to SQL Server.
> 1. Can that user Kill the Process.
> 2. What wuld be the code to do that?
> Many Thanks
> Paul

Killing a process does not help table that can't be read

I have a table that is locked by a process, but when I try to kill it using
Query Analyzer, I get the following message:
"SPID 136: transaction rollback in progress. Estimated rollback completion:
0%. Estimated time remaining: 0 seconds."
It has been saying this for a long time now and I need to get this table
functioning because it's my main customer table. Any help is appreciated.
I had a similar problem yesterday with a view yesterday, and I couldn't get
it cleared out until I restarted the server. It doesn't make sense to me
that I would have to restart an entire server to unlock this one table, does
it?
JeremiahHi
It looks like people have contacted PSS regarding this in the past!
http://tinyurl.com/5opp6
You may want to check your version number and see if there are any more
up-to-date patches that fix it.
John
"Jeremiah Traxler" wrote:
> I have a table that is locked by a process, but when I try to kill it using
> Query Analyzer, I get the following message:
> "SPID 136: transaction rollback in progress. Estimated rollback completion:
> 0%. Estimated time remaining: 0 seconds."
> It has been saying this for a long time now and I need to get this table
> functioning because it's my main customer table. Any help is appreciated.
> I had a similar problem yesterday with a view yesterday, and I couldn't get
> it cleared out until I restarted the server. It doesn't make sense to me
> that I would have to restart an entire server to unlock this one table, does
> it?
> Jeremiah

Killer Union

I have two queries joined with a union, one query by itself takes 34ms to
complete and the other runs by itself in 340ms but when they are joined by
the union (or a union all) the combined query takes an incredible one minute
and 15 seconds. Why does the union com with such an incredible cost?
The query is:
select
U.[Name] COLLATE SQL_Latin1_General_CP1_CI_AS as UserID
,U.LastName COLLATE SQL_Latin1_General_CP1_CI_AS + ', ' + U.FirstName
COLLATE SQL_Latin1_General_CP1_CI_AS + ' ' + U.MiddleName COLLATE
SQL_Latin1_General_CP1_CI_AS + ' (' + P.PartnerName COLLATE
SQL_Latin1_General_CP1_CI_AS + ')' as UserName
from Team..Users U with (nolock)
Left Join vwTeamPartners P with (nolock) on U.ID = P.UserID
where P.UserID is not null and Len(U.Name) = 6 and Len(U.FirstName) > 0 and
Len(U.LastName) > 0 and Lower(Substring(U.Name,1,1)) ='v' and
IsNumeric(Substring(U.Name,2,5))=1
union all
select
Case Len(E.EmplID)
When 6 then E.EmplID
When 5 then 'C' + E.Emplid
else null
end as UserID
,E.Full_Name + ' (' + E.DeptID + ')' as UserName
from vwPS_Employees E with (nolock)
left join vwTeamUsers T with (nolock) on
Case Len(E.EmplID)
When 6 then E.EmplID
When 5 then 'C' + E.EmplID
end = T.[Name] collate database_default
where E.Empl_Status in('A','P','L','S') and E.DeptID <> '000' and
Len(E.EmplID) in (5,6) and T.[ID] is not null
Order by UserNameWe are not really going to be able to tell why, unless we can see the view
statements, structure of base tables, query plans, etc. I do have a couple
of questions though... why all the collate clauses? Why not let the front
end deal with parentheses, concatenation, etc.? Why left join with
vwTeamPartners and then make it an inner join by including it in the where
clause? Why left join with vwTeamUsers and then make it an inner join by
including it in the where clause?
"Roy Sinclair" <RoySinclair@.discussions.microsoft.com> wrote in message
news:53062774-BF60-415F-9043-33DEE1EC07EC@.microsoft.com...
>I have two queries joined with a union, one query by itself takes 34ms to
> complete and the other runs by itself in 340ms but when they are joined
> by
> the union (or a union all) the combined query takes an incredible one
> minute
> and 15 seconds. Why does the union com with such an incredible cost?
> The query is:
> select
> U.[Name] COLLATE SQL_Latin1_General_CP1_CI_AS as UserID
> ,U.LastName COLLATE SQL_Latin1_General_CP1_CI_AS + ', ' + U.FirstName
> COLLATE SQL_Latin1_General_CP1_CI_AS + ' ' + U.MiddleName COLLATE
> SQL_Latin1_General_CP1_CI_AS + ' (' + P.PartnerName COLLATE
> SQL_Latin1_General_CP1_CI_AS + ')' as UserName
> from Team..Users U with (nolock)
> Left Join vwTeamPartners P with (nolock) on U.ID = P.UserID
> where P.UserID is not null and Len(U.Name) = 6 and Len(U.FirstName) > 0
> and
> Len(U.LastName) > 0 and Lower(Substring(U.Name,1,1)) ='v' and
> IsNumeric(Substring(U.Name,2,5))=1
> union all
> select
> Case Len(E.EmplID)
> When 6 then E.EmplID
> When 5 then 'C' + E.Emplid
> else null
> end as UserID
> ,E.Full_Name + ' (' + E.DeptID + ')' as UserName
> from vwPS_Employees E with (nolock)
> left join vwTeamUsers T with (nolock) on
> Case Len(E.EmplID)
> When 6 then E.EmplID
> When 5 then 'C' + E.EmplID
> end = T.[Name] collate database_default
> where E.Empl_Status in('A','P','L','S') and E.DeptID <> '000' and
> Len(E.EmplID) in (5,6) and T.[ID] is not null
> Order by UserName
>|||UNIONS and anything but Inner joins are always expensive.
There is alwasy a better way to do it, as long as you are using stored
procedures as the method of access.
If you are not, then you have bigger problems
The biggest issue is that both selects have to complete in entirity before
the union can begin.
Things I noticed about your Query:
Your Collates are in series in the same column of the select.
Only the last one would count, and it is the default for SQL.
They should be omitted.
The only time Collate is normally seen is when you have different
collations in the return from multiple linked servers.
Performance Hit 2 )
always specify the schema, Database..Table Only works if the only schema
is dbo.
It forces QA to check the sys.objects table for table ownership and
access
WAIT WAIT WAIT
Your using a case statment in a join '
Your Joining to Views, I bet they are well written as this one.
Did you put indexes on your views.
If we are Left joining P but P.userid can't be null, THAT's AN Inner
I understand now this is an example of how to get a 3 minute execution on
2 tables with 2 rows of data each.
Hire A DBA
SELECT
U.[Name] as UserID
, U.LastName + ', ' + U.FirstName + ' ' + U.MiddleName + ' (' +
P.PartnerName + ')' as UserName
FROM
Team..Users U with (nolock)
Left Join vwTeamPartners P with (nolock) on U.ID = P.UserID
WHERE
P.UserID is not null
and Len(U.Name) = 6
and Len(U.FirstName) = 0
and Len(U.LastName)=0
and Lower(Substring(U.Name,1,1)) ='v'
and IsNumeric(Substring(U.Name,2,5))=1
UNION ALL
SELECT
Case Len(E.EmplID)
When 6 then E.EmplID
When 5 then 'C' + E.Emplid
else null
end as UserID
,E.Full_Name + ' (' + E.DeptID + ')' as UserName
from
vwPS_Employees E with (nolock)
left join vwTeamUsers T with (nolock) on
Case Len(E.EmplID)
When 6 then E.EmplID
When 5 then 'C' + E.EmplID
end = T.[Name]
where
E.Empl_Status in('A','P','L','S')
and E.DeptID < '000'
and Len(E.EmplID) in (5,6)
and T.[ID] is not null
Order by
UserName
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:5E67B352-323C-4433-8126-F7C3B4A5FC17@.microsoft.com...
> We are not really going to be able to tell why, unless we can see the view
> statements, structure of base tables, query plans, etc. I do have a
> couple of questions though... why all the collate clauses? Why not let
> the front end deal with parentheses, concatenation, etc.? Why left join
> with vwTeamPartners and then make it an inner join by including it in the
> where clause? Why left join with vwTeamUsers and then make it an inner
> join by including it in the where clause?
>
> "Roy Sinclair" <RoySinclair@.discussions.microsoft.com> wrote in message
> news:53062774-BF60-415F-9043-33DEE1EC07EC@.microsoft.com...
>>I have two queries joined with a union, one query by itself takes 34ms to
>> complete and the other runs by itself in 340ms but when they are joined
>> by
>> the union (or a union all) the combined query takes an incredible one
>> minute
>> and 15 seconds. Why does the union com with such an incredible cost?
>> The query is:
>> select
>> U.[Name] COLLATE SQL_Latin1_General_CP1_CI_AS as UserID
>> ,U.LastName COLLATE SQL_Latin1_General_CP1_CI_AS + ', ' + U.FirstName
>> COLLATE SQL_Latin1_General_CP1_CI_AS + ' ' + U.MiddleName COLLATE
>> SQL_Latin1_General_CP1_CI_AS + ' (' + P.PartnerName COLLATE
>> SQL_Latin1_General_CP1_CI_AS + ')' as UserName
>> from Team..Users U with (nolock)
>> Left Join vwTeamPartners P with (nolock) on U.ID = P.UserID
>> where P.UserID is not null and Len(U.Name) = 6 and Len(U.FirstName) > 0
>> and
>> Len(U.LastName) > 0 and Lower(Substring(U.Name,1,1)) ='v' and
>> IsNumeric(Substring(U.Name,2,5))=1
>> union all
>> select
>> Case Len(E.EmplID)
>> When 6 then E.EmplID
>> When 5 then 'C' + E.Emplid
>> else null
>> end as UserID
>> ,E.Full_Name + ' (' + E.DeptID + ')' as UserName
>> from vwPS_Employees E with (nolock)
>> left join vwTeamUsers T with (nolock) on
>> Case Len(E.EmplID)
>> When 6 then E.EmplID
>> When 5 then 'C' + E.EmplID
>> end = T.[Name] collate database_default
>> where E.Empl_Status in('A','P','L','S') and E.DeptID <> '000' and
>> Len(E.EmplID) in (5,6) and T.[ID] is not null
>> Order by UserName
>sql

Killer Union

I have two queries joined with a union, one query by itself takes 34ms to
complete and the other runs by itself in 340ms but when they are joined by
the union (or a union all) the combined query takes an incredible one minute
and 15 seconds. Why does the union com with such an incredible cost?
The query is:
select
U.[Name] COLLATE SQL_Latin1_General_CP1_CI_AS as UserID
,U.LastName COLLATE SQL_Latin1_General_CP1_CI_AS + ', ' + U.FirstName
COLLATE SQL_Latin1_General_CP1_CI_AS + ' ' + U.MiddleName COLLATE
SQL_Latin1_General_CP1_CI_AS + ' (' + P.PartnerName COLLATE
SQL_Latin1_General_CP1_CI_AS + ')' as UserName
from Team..Users U with (nolock)
Left Join vwTeamPartners P with (nolock) on U.ID = P.UserID
where P.UserID is not null and Len(U.Name) = 6 and Len(U.FirstName) > 0 and
Len(U.LastName) > 0 and Lower(Substring(U.Name,1,1)) ='v' and
IsNumeric(Substring(U.Name,2,5))=1
union all
select
Case Len(E.EmplID)
When 6 then E.EmplID
When 5 then 'C' + E.Emplid
else null
end as UserID
,E.Full_Name + ' (' + E.DeptID + ')' as UserName
from vwPS_Employees E with (nolock)
left join vwTeamUsers T with (nolock) on
Case Len(E.EmplID)
When 6 then E.EmplID
When 5 then 'C' + E.EmplID
end = T.[Name] collate database_default
where E.Empl_Status in('A','P','L','S') and E.DeptID <> '000' and
Len(E.EmplID) in (5,6) and T.[ID] is not null
Order by UserName
We are not really going to be able to tell why, unless we can see the view
statements, structure of base tables, query plans, etc. I do have a couple
of questions though... why all the collate clauses? Why not let the front
end deal with parentheses, concatenation, etc.? Why left join with
vwTeamPartners and then make it an inner join by including it in the where
clause? Why left join with vwTeamUsers and then make it an inner join by
including it in the where clause?
"Roy Sinclair" <RoySinclair@.discussions.microsoft.com> wrote in message
news:53062774-BF60-415F-9043-33DEE1EC07EC@.microsoft.com...
>I have two queries joined with a union, one query by itself takes 34ms to
> complete and the other runs by itself in 340ms but when they are joined
> by
> the union (or a union all) the combined query takes an incredible one
> minute
> and 15 seconds. Why does the union com with such an incredible cost?
> The query is:
> select
> U.[Name] COLLATE SQL_Latin1_General_CP1_CI_AS as UserID
> ,U.LastName COLLATE SQL_Latin1_General_CP1_CI_AS + ', ' + U.FirstName
> COLLATE SQL_Latin1_General_CP1_CI_AS + ' ' + U.MiddleName COLLATE
> SQL_Latin1_General_CP1_CI_AS + ' (' + P.PartnerName COLLATE
> SQL_Latin1_General_CP1_CI_AS + ')' as UserName
> from Team..Users U with (nolock)
> Left Join vwTeamPartners P with (nolock) on U.ID = P.UserID
> where P.UserID is not null and Len(U.Name) = 6 and Len(U.FirstName) > 0
> and
> Len(U.LastName) > 0 and Lower(Substring(U.Name,1,1)) ='v' and
> IsNumeric(Substring(U.Name,2,5))=1
> union all
> select
> Case Len(E.EmplID)
> When 6 then E.EmplID
> When 5 then 'C' + E.Emplid
> else null
> end as UserID
> ,E.Full_Name + ' (' + E.DeptID + ')' as UserName
> from vwPS_Employees E with (nolock)
> left join vwTeamUsers T with (nolock) on
> Case Len(E.EmplID)
> When 6 then E.EmplID
> When 5 then 'C' + E.EmplID
> end = T.[Name] collate database_default
> where E.Empl_Status in('A','P','L','S') and E.DeptID <> '000' and
> Len(E.EmplID) in (5,6) and T.[ID] is not null
> Order by UserName
>
|||UNIONS and anything but Inner joins are always expensive.
There is alwasy a better way to do it, as long as you are using stored
procedures as the method of access.
If you are not, then you have bigger problems
The biggest issue is that both selects have to complete in entirity before
the union can begin.
Things I noticed about your Query:
Your Collates are in series in the same column of the select.
Only the last one would count, and it is the default for SQL.
They should be omitted.
The only time Collate is normally seen is when you have different
collations in the return from multiple linked servers.
Performance Hit 2 )
always specify the schema, Database..Table Only works if the only schema
is dbo.
It forces QA to check the sys.objects table for table ownership and
access
WAIT WAIT WAIT
Your using a case statment in a join ?
Your Joining to Views, I bet they are well written as this one.
Did you put indexes on your views.
If we are Left joining P but P.userid can't be null, THAT's AN Inner
I understand now this is an example of how to get a 3 minute execution on
2 tables with 2 rows of data each.
Hire A DBA
SELECT
U.[Name] as UserID
, U.LastName + ', ' + U.FirstName + ' ' + U.MiddleName + ' (' +
P.PartnerName + ')' as UserName
FROM
Team..Users U with (nolock)
Left Join vwTeamPartners P with (nolock) on U.ID = P.UserID
WHERE
P.UserID is not null
and Len(U.Name) = 6
and Len(U.FirstName) = 0
and Len(U.LastName)=0
and Lower(Substring(U.Name,1,1)) ='v'
and IsNumeric(Substring(U.Name,2,5))=1
UNION ALL
SELECT
Case Len(E.EmplID)
When 6 then E.EmplID
When 5 then 'C' + E.Emplid
else null
end as UserID
,E.Full_Name + ' (' + E.DeptID + ')' as UserName
from
vwPS_Employees E with (nolock)
left join vwTeamUsers T with (nolock) on
Case Len(E.EmplID)
When 6 then E.EmplID
When 5 then 'C' + E.EmplID
end = T.[Name]
where
E.Empl_Status in('A','P','L','S')
and E.DeptID < '000'
and Len(E.EmplID) in (5,6)
and T.[ID] is not null
Order by
UserName
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:5E67B352-323C-4433-8126-F7C3B4A5FC17@.microsoft.com...
> We are not really going to be able to tell why, unless we can see the view
> statements, structure of base tables, query plans, etc. I do have a
> couple of questions though... why all the collate clauses? Why not let
> the front end deal with parentheses, concatenation, etc.? Why left join
> with vwTeamPartners and then make it an inner join by including it in the
> where clause? Why left join with vwTeamUsers and then make it an inner
> join by including it in the where clause?
>
> "Roy Sinclair" <RoySinclair@.discussions.microsoft.com> wrote in message
> news:53062774-BF60-415F-9043-33DEE1EC07EC@.microsoft.com...
>

killer query - limit usage ?

how do i limit SQL servers CPU usage ? (2005 express)
killer queries are slowing all other services.
mem limited already.
thanks
scottThe best thing to do is optimise the queries, use Profiler , identify the
slow ones and optimise
--
Jack Vamvas
___________________________________
Need an IT job? http://www.ITjobfeed.com/SQL
"Scott" <s@.yahoo.co.uk> wrote in message
news:O$WwN0wtHHA.292@.TK2MSFTNGP02.phx.gbl...
> how do i limit SQL servers CPU usage ? (2005 express)
> killer queries are slowing all other services.
> mem limited already.
> thanks
> scott
>|||how do i ID the problematic queries ?
thanks for reponse
scott|||must be a way to limit cpu server side too !
too much data is the prob.|||Windows 2003 has a feature called WSRM that can limit resources by app. I
guess you could limit the amount of CPU taken up by express but that would
not be the long term solution. You need to tune the database and the app
that is hitting the database so that it doesn't use too much resources in
the first place. Too much data is not an answer it is how it is being used.
But the more data and the heavier the usage the more likely that you need to
upgrade to another edition of SQL Server. Here are some links that may get
you started.
http://www.sql-server-performance.com/sql_server_performance_audit10.asp
Performance Audit
http://www.microsoft.com/technet/prodtechnol/sql/2005/library/operations.mspx
Performance WP's
http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
http://www.sql-server-performance.com/sql_server_performance_audit.asp
Hardware Performance CheckList
http://www.sql-server-performance.com/best_sql_server_performance_tips.asp
SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=224587 Troubleshooting App
Performance
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_perfmon_24u1.asp
Disk Monitoring
http://sqldev.net/misc/WaitTypes.htm Wait Types
--
Andrew J. Kelly SQL MVP
"Scott" <s@.yahoo.co.uk> wrote in message
news:u%23jrUgytHHA.2004@.TK2MSFTNGP03.phx.gbl...
> must be a way to limit cpu server side too !
> too much data is the prob.
>|||thanks very much for response.
all the best.
scott|||quick question. How can query optimization help my query:
select * from [table]
when my table has 5 million records.
surly the only way to sort this is to limit the cpu ?
sorry to keep posting
scott|||That query will hardly use enough CPU to measure, so limiting CPU
would not get you very far. The query is limited by disk speed for
reads and/or network speed for returning the results. I don't see any
way to throttle either one, but then I don't expect such a simple
query to cause much of a bottleneck.
Roy Harvey
Beacon Falls, CT
On Tue, 26 Jun 2007 09:04:07 +0100, "Scott" <s@.yahoo.co.uk> wrote:
>quick question. How can query optimization help my query:
>select * from [table]
>when my table has 5 million records.
>surly the only way to sort this is to limit the cpu ?
>sorry to keep posting
>scott
>|||On Jun 26, 2:04 pm, "Scott" <s...@.yahoo.co.uk> wrote:
> quick question. How can query optimization help my query:
> select * from [table]
> when my table has 5 million records.
> surly the only way to sort this is to limit the cpu ?
> sorry to keep posting
> scott
Hi, select * from [table] with 5 million records will not slow your
server down. However if you issue an update then it may slow things
down and you may need to optimize your query. You can also restrict
access to large tables if that solves the problem.|||Keeping in mind the things already said about your query why would you do
something like that in the first place? What are you going to do with all
the columns and all the 5 million rows? A human certainly isn't going to
make sense of that much data. If you only need a few of them you need to add
a proper WHERE clause and indexes to support it.
--
Andrew J. Kelly SQL MVP
"Scott" <s@.yahoo.co.uk> wrote in message
news:%23vpOve8tHHA.4612@.TK2MSFTNGP04.phx.gbl...
> quick question. How can query optimization help my query:
> select * from [table]
> when my table has 5 million records.
> surly the only way to sort this is to limit the cpu ?
> sorry to keep posting
> scott
>|||very good point Andrew, sorry for being a little dim.
i read something about a READ ONLY option 2 which is supposed to speed up
queries.
Thanks for your time, great help
Scott|||Not sure what you mean by option2 but if you place the filegroup or database
in read only mode sql server will not take out any locks when you read it
since it knows no one can change it while you read. If no one is making
changes you can also use the READ UNCOMMITED isolation level to achieve the
same end results.
--
Andrew J. Kelly SQL MVP
"Scott" <s@.yahoo.co.uk> wrote in message
news:O5L%23RnkuHHA.3544@.TK2MSFTNGP03.phx.gbl...
> very good point Andrew, sorry for being a little dim.
> i read something about a READ ONLY option 2 which is supposed to speed up
> queries.
> Thanks for your time, great help
> Scott
>

killer query - limit usage ?

Windows 2003 has a feature called WSRM that can limit resources by app. I
guess you could limit the amount of CPU taken up by express but that would
not be the long term solution. You need to tune the database and the app
that is hitting the database so that it doesn't use too much resources in
the first place. Too much data is not an answer it is how it is being used.
But the more data and the heavier the usage the more likely that you need to
upgrade to another edition of SQL Server. Here are some links that may get
you started.
http://www.sql-server-performance.com/sql_server_performance_audit10.asp
Performance Audit
http://www.microsoft.com/technet/prodtechnol/sql/2005/library/operations.mspx
Performance WP's
http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
http://www.sql-server-performance.com/sql_server_performance_audit.asp
Hardware Performance CheckList
http://www.sql-server-performance.com/best_sql_server_performance_tips.asp
SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=224587 Troubleshooting App
Performance
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_perfmon_24u1.asp
Disk Monitoring
http://sqldev.net/misc/WaitTypes.htm Wait Types
Andrew J. Kelly SQL MVP
"Scott" <s@.yahoo.co.uk> wrote in message
news:u%23jrUgytHHA.2004@.TK2MSFTNGP03.phx.gbl...
> must be a way to limit cpu server side too !
> too much data is the prob.
>
That query will hardly use enough CPU to measure, so limiting CPU
would not get you very far. The query is limited by disk speed for
reads and/or network speed for returning the results. I don't see any
way to throttle either one, but then I don't expect such a simple
query to cause much of a bottleneck.
Roy Harvey
Beacon Falls, CT
On Tue, 26 Jun 2007 09:04:07 +0100, "Scott" <s@.yahoo.co.uk> wrote:

>quick question. How can query optimization help my query:
>select * from [table]
>when my table has 5 million records.
>surly the only way to sort this is to limit the cpu ?
>sorry to keep posting
>scott
>
|||On Jun 26, 2:04 pm, "Scott" <s...@.yahoo.co.uk> wrote:
> quick question. How can query optimization help my query:
> select * from [table]
> when my table has 5 million records.
> surly the only way to sort this is to limit the cpu ?
> sorry to keep posting
> scott
Hi, select * from [table] with 5 million records will not slow your
server down. However if you issue an update then it may slow things
down and you may need to optimize your query. You can also restrict
access to large tables if that solves the problem.
|||Keeping in mind the things already said about your query why would you do
something like that in the first place? What are you going to do with all
the columns and all the 5 million rows? A human certainly isn't going to
make sense of that much data. If you only need a few of them you need to add
a proper WHERE clause and indexes to support it.
Andrew J. Kelly SQL MVP
"Scott" <s@.yahoo.co.uk> wrote in message
news:%23vpOve8tHHA.4612@.TK2MSFTNGP04.phx.gbl...
> quick question. How can query optimization help my query:
> select * from [table]
> when my table has 5 million records.
> surly the only way to sort this is to limit the cpu ?
> sorry to keep posting
> scott
>
|||Not sure what you mean by option2 but if you place the filegroup or database
in read only mode sql server will not take out any locks when you read it
since it knows no one can change it while you read. If no one is making
changes you can also use the READ UNCOMMITED isolation level to achieve the
same end results.
Andrew J. Kelly SQL MVP
"Scott" <s@.yahoo.co.uk> wrote in message
news:O5L%23RnkuHHA.3544@.TK2MSFTNGP03.phx.gbl...
> very good point Andrew, sorry for being a little dim.
> i read something about a READ ONLY option 2 which is supposed to speed up
> queries.
> Thanks for your time, great help
> Scott
>

killer query - limit usage ?

how do i limit SQL servers CPU usage ? (2005 express)
killer queries are slowing all other services.
mem limited already.
thanks
scottThe best thing to do is optimise the queries, use Profiler , identify the
slow ones and optimise
Jack Vamvas
___________________________________
Need an IT job? http://www.ITjobfeed.com/SQL
"Scott" <s@.yahoo.co.uk> wrote in message
news:O$WwN0wtHHA.292@.TK2MSFTNGP02.phx.gbl...
> how do i limit SQL servers CPU usage ? (2005 express)
> killer queries are slowing all other services.
> mem limited already.
> thanks
> scott
>|||how do i ID the problematic queries ?
thanks for reponse
scott|||must be a way to limit cpu server side too !
too much data is the prob.|||Windows 2003 has a feature called WSRM that can limit resources by app. I
guess you could limit the amount of CPU taken up by express but that would
not be the long term solution. You need to tune the database and the app
that is hitting the database so that it doesn't use too much resources in
the first place. Too much data is not an answer it is how it is being used.
But the more data and the heavier the usage the more likely that you need to
upgrade to another edition of SQL Server. Here are some links that may get
you started.
http://www.sql-server-performance.c...nce_audit10.asp
Performance Audit
http://www.microsoft.com/technet/pr...perfmonitor.asp Perfmon counters
http://www.sql-server-performance.c...mance_audit.asp
Hardware Performance CheckList
http://www.sql-server-performance.c...rmance_tips.asp
SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=224587 Troubleshooting App
Performance
http://msdn.microsoft.com/library/d.../>
on_24u1.asp
Disk Monitoring
http://sqldev.net/misc/WaitTypes.htm Wait Types
Andrew J. Kelly SQL MVP
"Scott" <s@.yahoo.co.uk> wrote in message
news:u%23jrUgytHHA.2004@.TK2MSFTNGP03.phx.gbl...
> must be a way to limit cpu server side too !
> too much data is the prob.
>|||thanks very much for response.
all the best.
scott|||quick question. How can query optimization help my query:
select * from [table]
when my table has 5 million records.
surly the only way to sort this is to limit the cpu ?
sorry to keep posting
scott|||That query will hardly use enough CPU to measure, so limiting CPU
would not get you very far. The query is limited by disk speed for
reads and/or network speed for returning the results. I don't see any
way to throttle either one, but then I don't expect such a simple
query to cause much of a bottleneck.
Roy Harvey
Beacon Falls, CT
On Tue, 26 Jun 2007 09:04:07 +0100, "Scott" <s@.yahoo.co.uk> wrote:

>quick question. How can query optimization help my query:
>select * from [table]
>when my table has 5 million records.
>surly the only way to sort this is to limit the cpu ?
>sorry to keep posting
>scott
>|||On Jun 26, 2:04 pm, "Scott" <s...@.yahoo.co.uk> wrote:
> quick question. How can query optimization help my query:
> select * from [table]
> when my table has 5 million records.
> surly the only way to sort this is to limit the cpu ?
> sorry to keep posting
> scott
Hi, select * from [table] with 5 million records will not slow your
server down. However if you issue an update then it may slow things
down and you may need to optimize your query. You can also restrict
access to large tables if that solves the problem.|||Keeping in mind the things already said about your query why would you do
something like that in the first place? What are you going to do with all
the columns and all the 5 million rows? A human certainly isn't going to
make sense of that much data. If you only need a few of them you need to add
a proper WHERE clause and indexes to support it.
Andrew J. Kelly SQL MVP
"Scott" <s@.yahoo.co.uk> wrote in message
news:%23vpOve8tHHA.4612@.TK2MSFTNGP04.phx.gbl...
> quick question. How can query optimization help my query:
> select * from [table]
> when my table has 5 million records.
> surly the only way to sort this is to limit the cpu ?
> sorry to keep posting
> scott
>sql

Kill Session statement does not work

I logged in through query anayzer as system admin and then logged in a second session from a different user ID. From the sa window I issued "sp_who" and found the session id of 55 for the second logged in session. I then issued "KILL 55". When I reissued
"sp_who" it showed the session id was gone, but when I opened the user query analyzer window, I was still allowed to issue SELECT statements. Shouldnt the user session window close or leave some type of message to show that the session was logged out and
the window is no longer active?
> Shouldnt the user session window close or leave some type of message
> to show that the session was logged out and the window is no longer
active?
The actual behavior is that Query Analyzer will try to reestablish the
connection when you try to execute a query against a closed connection. You
can see this with a Profiler trace. Whether or not QA should notify the
user when this occurs is debatable.
Hope this helps.
Dan Guzman
SQL Server MVP
"Jack Wachtler" <jack_wachtler@.comcast.net> wrote in message
news:AA7FD110-6FD0-44D5-A011-EE320063ABC8@.microsoft.com...
> I logged in through query anayzer as system admin and then logged in a
second session from a different user ID. From the sa window I issued
"sp_who" and found the session id of 55 for the second logged in session. I
then issued "KILL 55". When I reissued "sp_who" it showed the session id was
gone, but when I opened the user query analyzer window, I was still allowed
to issue SELECT statements. Shouldnt the user session window close or leave
some type of message to show that the session was logged out and the window
is no longer active?

Kill Session statement does not work

I logged in through query anayzer as system admin and then logged in a secon
d session from a different user ID. From the sa window I issued "sp_who" and
found the session id of 55 for the second logged in session. I then issued
"KILL 55". When I reissued
"sp_who" it showed the session id was gone, but when I opened the user query
analyzer window, I was still allowed to issue SELECT statements. Shouldnt t
he user session window close or leave some type of message to show that the
session was logged out and
the window is no longer active?Hi,
Query analyzer will establish back the connection to SQL server on issuing
any SQL / DML statements.
Thanks
Hari
MCDBA
"Jack Wachtler" <jack_wachtler@.comcast.net> wrote in message
news:AA7FD110-6FD0-44D5-A011-EE320063ABC8@.microsoft.com...
> I logged in through query anayzer as system admin and then logged in a
second session from a different user ID. From the sa window I issued
"sp_who" and found the session id of 55 for the second logged in session. I
then issued "KILL 55". When I reissued "sp_who" it showed the session id was
gone, but when I opened the user query analyzer window, I was still allowed
to issue SELECT statements. Shouldnt the user session window close or leave
some type of message to show that the session was logged out and the window
is no longer active?|||> Shouldnt the user session window close or leave some type of message
> to show that the session was logged out and the window is no longer
active?
The actual behavior is that Query Analyzer will try to reestablish the
connection when you try to execute a query against a closed connection. You
can see this with a Profiler trace. Whether or not QA should notify the
user when this occurs is debatable.
Hope this helps.
Dan Guzman
SQL Server MVP
"Jack Wachtler" <jack_wachtler@.comcast.net> wrote in message
news:AA7FD110-6FD0-44D5-A011-EE320063ABC8@.microsoft.com...
> I logged in through query anayzer as system admin and then logged in a
second session from a different user ID. From the sa window I issued
"sp_who" and found the session id of 55 for the second logged in session. I
then issued "KILL 55". When I reissued "sp_who" it showed the session id was
gone, but when I opened the user query analyzer window, I was still allowed
to issue SELECT statements. Shouldnt the user session window close or leave
some type of message to show that the session was logged out and the window
is no longer active?

kill long running query

I am changing some field types in a large table
by creating the new table and then selecting the
data into it. So no changes are being made to the
existing data.
I started this at 5:00 last night, and by 11:00
it became apparent that it wasn't going to finish
until this weekend.
I can run queries in UNCOMMITTED READ isolation level
and see how many images have been copied. By 11:00
it was only 12% done.
So I cancelled the query at 11:00. 12 hours later,
it is about half way done rolling back. As a result
we can't access the table and get any work done today.
I don't need this transaction rolled back. I could
just drop the temporary table and be done with it.
I've looked at the KILL copmmand, but that just causes
it to rollback, so it doesn't help.
I could reboot the server, but as far as I know, it might
pick up where it left off and continue rolling back the
transaction.
Is there any way out of this hole that I've dug myself into?
Thanks,
Brad.> So I cancelled the query at 11:00. 12 hours later,
> it is about half way done rolling back. As a result
> we can't access the table and get any work done today.
If it is rolling back a SELECT INTO a new table, why would this prevent you
from accessing the existing table? Or did you mean to explain that the
rollback is consuming the server in terms of resources?|||Aaron Bertrand [SQL Server MVP] wrote :
>> So I cancelled the query at 11:00. 12 hours later,
>> it is about half way done rolling back. As a result
>> we can't access the table and get any work done today.
> If it is rolling back a SELECT INTO a new table, why would this prevent you
> from accessing the existing table? Or did you mean to explain that the
> rollback is consuming the server in terms of resources?
I don't know the answer to that.
I assume that it has affected so many rows
(I was copying the entire table to a new table)
that the locks have been promoted to table level.
2G rows.
The script I was using use ISOLATION LEVEL SERIALIZABLE.
The question has been rendered moot, as the server crashed.
Thanks,
Brad.|||Aaron Bertrand [SQL Server MVP] wrote on 4/4/2008 :
>> So I cancelled the query at 11:00. 12 hours later,
>> it is about half way done rolling back. As a result
>> we can't access the table and get any work done today.
> If it is rolling back a SELECT INTO a new table, why would this prevent you
> from accessing the existing table? Or did you mean to explain that the
> rollback is consuming the server in terms of resources?
No, I don't know why it would be adding locks
on the SOURCE table.
What led me to think that was that queries against
any other table would return reasonably, while
queries against either of those tables would run
up to 30 minutes without returning before I killed them.
Thanks,
Brad.|||2 billion rows, yeah that's going to be tough. If you have the space, I
would suggest making a backup of the database, restoring it as a different
database, and then copy the rows from the restored copy. Then you can use a
much lower isolation level because you don't have to worry about people
coming in and changing the source rows while you are copying...
"Brad White" <bwhite_at_inebraska_dot_com@.remove.nul> wrote in message
news:mn.24b57d840b74b3fd.81110@.remove.nul...
> Aaron Bertrand [SQL Server MVP] wrote :
>> So I cancelled the query at 11:00. 12 hours later,
>> it is about half way done rolling back. As a result
>> we can't access the table and get any work done today.
>> If it is rolling back a SELECT INTO a new table, why would this prevent
>> you from accessing the existing table? Or did you mean to explain that
>> the rollback is consuming the server in terms of resources?
> I don't know the answer to that.
> I assume that it has affected so many rows
> (I was copying the entire table to a new table)
> that the locks have been promoted to table level.
> 2G rows.
> The script I was using use ISOLATION LEVEL SERIALIZABLE.
> The question has been rendered moot, as the server crashed.
> Thanks,
> Brad.
>

kill long running query

I am changing some field types in a large table
by creating the new table and then selecting the
data into it. So no changes are being made to the
existing data.
I started this at 5:00 last night, and by 11:00
it became apparent that it wasn't going to finish
until this weekend.
I can run queries in UNCOMMITTED READ isolation level
and see how many images have been copied. By 11:00
it was only 12% done.
So I cancelled the query at 11:00. 12 hours later,
it is about half way done rolling back. As a result
we can't access the table and get any work done today.
I don't need this transaction rolled back. I could
just drop the temporary table and be done with it.
I've looked at the KILL copmmand, but that just causes
it to rollback, so it doesn't help.
I could reboot the server, but as far as I know, it might
pick up where it left off and continue rolling back the
transaction.
Is there any way out of this hole that I've dug myself into?
Thanks,
Brad.
> So I cancelled the query at 11:00. 12 hours later,
> it is about half way done rolling back. As a result
> we can't access the table and get any work done today.
If it is rolling back a SELECT INTO a new table, why would this prevent you
from accessing the existing table? Or did you mean to explain that the
rollback is consuming the server in terms of resources?
|||Aaron Bertrand [SQL Server MVP] wrote :
> If it is rolling back a SELECT INTO a new table, why would this prevent you
> from accessing the existing table? Or did you mean to explain that the
> rollback is consuming the server in terms of resources?
I don't know the answer to that.
I assume that it has affected so many rows
(I was copying the entire table to a new table)
that the locks have been promoted to table level.
2G rows.
The script I was using use ISOLATION LEVEL SERIALIZABLE.
The question has been rendered moot, as the server crashed.
Thanks,
Brad.
|||Aaron Bertrand [SQL Server MVP] wrote on 4/4/2008 :
> If it is rolling back a SELECT INTO a new table, why would this prevent you
> from accessing the existing table? Or did you mean to explain that the
> rollback is consuming the server in terms of resources?
No, I don't know why it would be adding locks
on the SOURCE table.
What led me to think that was that queries against
any other table would return reasonably, while
queries against either of those tables would run
up to 30 minutes without returning before I killed them.
Thanks,
Brad.
|||2 billion rows, yeah that's going to be tough. If you have the space, I
would suggest making a backup of the database, restoring it as a different
database, and then copy the rows from the restored copy. Then you can use a
much lower isolation level because you don't have to worry about people
coming in and changing the source rows while you are copying...
"Brad White" <bwhite_at_inebraska_dot_com@.remove.nul> wrote in message
news:mn.24b57d840b74b3fd.81110@.remove.nul...
> Aaron Bertrand [SQL Server MVP] wrote :
> I don't know the answer to that.
> I assume that it has affected so many rows
> (I was copying the entire table to a new table)
> that the locks have been promoted to table level.
> 2G rows.
> The script I was using use ISOLATION LEVEL SERIALIZABLE.
> The question has been rendered moot, as the server crashed.
> Thanks,
> Brad.
>

Kill command don't work

I've this problem:
A process (SPID 62) with some locks on some tables. The application which
had generated the query are not running, is closed! In SQL server I can see
the SPI 62 that are blocking other users.
I try to kill the process (KILL 62) but nothing! The SPID 62 not die!
I need to restare sql server to solve my problem.
The query that SPID62 are running is a normal INSERT INTO without any
problems or other...
How may I do to obtain more information around the fact that the SQL KILL
command not work?
I've already tried with KILL 62 WITH: Kill 62 with status only and I obtain
: "Extimated... rollbak 0%,... 0 minutes"
But the process rest in rollback and not stop.
and why SQL server if the client go off not stop this process itself?
thanks in advance.
TeoI has this case at a customer site just yesterday. It turned out that the table they inserted into
had a trigger which executed an extended stored procedure. If any external (to SQL Server) code
becomes hung, you cannot kill that SPID.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Teo(I)" <TeoI@.discussions.microsoft.com> wrote in message
news:73DE3473-DFAC-4C77-A645-057871F3DDEC@.microsoft.com...
> I've this problem:
> A process (SPID 62) with some locks on some tables. The application which
> had generated the query are not running, is closed! In SQL server I can see
> the SPI 62 that are blocking other users.
> I try to kill the process (KILL 62) but nothing! The SPID 62 not die!
> I need to restare sql server to solve my problem.
> The query that SPID62 are running is a normal INSERT INTO without any
> problems or other...
> How may I do to obtain more information around the fact that the SQL KILL
> command not work?
> I've already tried with KILL 62 WITH: Kill 62 with status only and I obtain
> : "Extimated... rollbak 0%,... 0 minutes"
> But the process rest in rollback and not stop.
> and why SQL server if the client go off not stop this process itself?
> thanks in advance.
> Teo|||DBCC traceon 3604 on SPID 62:
Process id 62 killed by hostname SERVER01, host process ID 6816...
but this isn't true! the spid is alive..!!!
If I retry i obtain:
Process id 62 killed by hostname SERVER01, host process ID 6816...
I don't understand!|||well... I've a trigger too... But my trigger do another insert in a second
table (backup table) on a different db in the same istance. In this case a
rollback or kill must work.

Kill command don't work

I've this problem:
A process (SPID 62) with some locks on some tables. The application which
had generated the query are not running, is closed! In SQL server I can see
the SPI 62 that are blocking other users.
I try to kill the process (KILL 62) but nothing! The SPID 62 not die!
I need to restare sql server to solve my problem.
The query that SPID62 are running is a normal INSERT INTO without any
problems or other...
How may I do to obtain more information around the fact that the SQL KILL
command not work?
I've already tried with KILL 62 WITH: Kill 62 with status only and I obtain
: "Extimated... rollbak 0%,... 0 minutes"
But the process rest in rollback and not stop.
and why SQL server if the client go off not stop this process itself?
thanks in advance.
Teo
I has this case at a customer site just yesterday. It turned out that the table they inserted into
had a trigger which executed an extended stored procedure. If any external (to SQL Server) code
becomes hung, you cannot kill that SPID.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Teo(I)" <TeoI@.discussions.microsoft.com> wrote in message
news:73DE3473-DFAC-4C77-A645-057871F3DDEC@.microsoft.com...
> I've this problem:
> A process (SPID 62) with some locks on some tables. The application which
> had generated the query are not running, is closed! In SQL server I can see
> the SPI 62 that are blocking other users.
> I try to kill the process (KILL 62) but nothing! The SPID 62 not die!
> I need to restare sql server to solve my problem.
> The query that SPID62 are running is a normal INSERT INTO without any
> problems or other...
> How may I do to obtain more information around the fact that the SQL KILL
> command not work?
> I've already tried with KILL 62 WITH: Kill 62 with status only and I obtain
> : "Extimated... rollbak 0%,... 0 minutes"
> But the process rest in rollback and not stop.
> and why SQL server if the client go off not stop this process itself?
> thanks in advance.
> Teo
|||DBCC traceon 3604 on SPID 62:
Process id 62 killed by hostname SERVER01, host process ID 6816...
but this isn't true! the spid is alive..!!!
If I retry i obtain:
Process id 62 killed by hostname SERVER01, host process ID 6816...
I don't understand!
|||well... I've a trigger too... But my trigger do another insert in a second
table (backup table) on a different db in the same istance. In this case a
rollback or kill must work.

Kill command don't work

I've this problem:
A process (SPID 62) with some locks on some tables. The application which
had generated the query are not running, is closed! In SQL server I can see
the SPI 62 that are blocking other users.
I try to kill the process (KILL 62) but nothing! The SPID 62 not die!
I need to restare sql server to solve my problem.
The query that SPID62 are running is a normal INSERT INTO without any
problems or other...
How may I do to obtain more information around the fact that the SQL KILL
command not work?
I've already tried with KILL 62 WITH: Kill 62 with status only and I obtain
: "Extimated... rollbak 0%,... 0 minutes"
But the process rest in rollback and not stop.
and why SQL server if the client go off not stop this process itself?
thanks in advance.
TeoI has this case at a customer site just yesterday. It turned out that the ta
ble they inserted into
had a trigger which executed an extended stored procedure. If any external (
to SQL Server) code
becomes hung, you cannot kill that SPID.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Teo(I)" <TeoI@.discussions.microsoft.com> wrote in message
news:73DE3473-DFAC-4C77-A645-057871F3DDEC@.microsoft.com...
> I've this problem:
> A process (SPID 62) with some locks on some tables. The application which
> had generated the query are not running, is closed! In SQL server I can se
e
> the SPI 62 that are blocking other users.
> I try to kill the process (KILL 62) but nothing! The SPID 62 not die!
> I need to restare sql server to solve my problem.
> The query that SPID62 are running is a normal INSERT INTO without any
> problems or other...
> How may I do to obtain more information around the fact that the SQL KILL
> command not work?
> I've already tried with KILL 62 WITH: Kill 62 with status only and I obtai
n
> : "Extimated... rollbak 0%,... 0 minutes"
> But the process rest in rollback and not stop.
> and why SQL server if the client go off not stop this process itself?
> thanks in advance.
> Teo|||DBCC traceon 3604 on SPID 62:
Process id 62 killed by hostname SERVER01, host process ID 6816...
but this isn't true! the spid is alive..!!!
If I retry i obtain:
Process id 62 killed by hostname SERVER01, host process ID 6816...
I don't understand!|||well... I've a trigger too... But my trigger do another insert in a second
table (backup table) on a different db in the same istance. In this case a
rollback or kill must work.sql

keywords context summary

I use fts to query a sql server 2005 db and the results are displayed
on a web page.
Out of a large text how can a summary be extracted "including" also my
keywords? Like more contextual summary.
google has a fancy way of formating the search results and display a
keyword contextual description for every link in their search
any idea?
thxke
This is difficult. For text and image data you really don't have a good way
other than incorporating indexing services and generating hyperlinks to
seeing the data.
Here is an example of how to do this:
http://www.indexserverfaq.com/SQLhitHighlighting.htm
For small char (typically under 200 bytes) use charindex or patindex. For
larger amounts of data it is more efficient to mark it up client side.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"xke" <xkeops@.gmail.com> wrote in message
news:1170996261.834692.202170@.h3g2000cwc.googlegro ups.com...
>I use fts to query a sql server 2005 db and the results are displayed
> on a web page.
> Out of a large text how can a summary be extracted "including" also my
> keywords? Like more contextual summary.
> google has a fancy way of formating the search results and display a
> keyword contextual description for every link in their search
> any idea?
> thxke
>

Keyword Query

I have a sample photo database where we have added keywords to search for photos. I wanted a way to list all of the keywords that are in the database individually. The problem is in my keyword field there are many keywords seperated by a comma.

Ex: "bull, barrel, rodeo, western, cowboy" would in the keyword field for one photo.

I wanted to select distinct all of the individual words from each keyword field in all of the records.

Can this be done? What would the query look like?

I am looking for a list like:

bull
barrel
rodeo
western
cowboy

Any suggestions?

Thanks,
RobCREATE TABLE myTable99 (Photo Id int, Keyword vatchar(256))
GO|||Yes, you can get the distinct keywords from your table. You need to use LOOP to fetch each value of the colomn and assign it to the variable. And then you need to split string based on ",". Insert the seperated keyword into the temporary table. At last, you just do

SELECT DISTINCT Keyword FROM temporary TABLE

to get the distinct keyword.|||Got cut short...

far as I know you need some code...this example would be 1 row from a cursor for example...

USE Northwind
GO

SET NOCOUNT ON

DECLARE @.x varchar(8000), @.y int, @.z int

DECLARE @.tbl table (col1 varchar(8000))

SELECT @.x = 'Brett|No|Rhyme|to|Well', @.y = 1, @.z = CHARINDEX('|',@.x,1)-1

WHILE @.z <> -1
BEGIN
INSERT INTO @.tbl (col1) SELECT SUBSTRING(@.x, @.y, @.z-@.y+1)
SELECT @.y = @.z + 2
SELECT @.z = CHARINDEX('|',@.x,@.y)-1
END

INSERT INTO @.tbl (col1) SELECT SUBSTRING(@.x, @.y, LEN(@.x)-@.y+2)

SELECT LEN(col1), col1 FROM @.tbl
GO

SET NOCOUNT OFF|||Brett, I see what you are doing and I understand what is going on but I don't know how to get my data into where you have 'Brett|No|Rhyme|to|Well'.

My field name is keyword and the table name is TblPhotos and the Database name is CTM_samples. How would I select the keyword values and insert them into the temp
table?

Thanks alot for your explination.|||You'll need a cursor...

do a fetch and assign the columns to variables...

do the loop

then do the insert

See?|||The following query:

SELECT photo,
NullIf(
SubString(',' + keyword + ',' , counter, CharIndex(',' , ',' + keyword + ',' , counter) - counter) , '') AS keywords
FROM photos, stringlen
WHERE counter <= Len(',' + keyword + ',') AND SubString(',' + keyword + ',' , counter - 1, 1) = ','
AND CharIndex(',' , ',' + keyword + ',' , counter) - counter > 0

will return:

photo1 bull
photo1 barrel
photo1 rodeo
photo1 western
photo2 eiffel
photo2 tower
photo2 paris

The key here it create a 'stringlen' table with an counter field with incrementing numbers. So, if your longest keyword column contains 200 characters, then you would have values 1-200 in your counter field to cover the substring manipulation.

key/value pair table update query

I'm working with a table that I've created called Config which contains key/value pairs used to get and set site-wide settings. I'm now trying to create a web form which updates the table but I'm not sure how to create the most effective UPDATE query.

Table of course takes this form key | value
----------
config_setting1 | value1
config_setting2 | value2

I'm working with a System.Collections.Specialized.StringDictionary Class object which contains all of the pairs from my webform... anybody have a creative way to build an UPDATE string using this object?

Thanks for any help and suggestions,
ecolner@.yahoo.comI suggest you to create static method to your database class that updates the configuration. The idea is simple.

1) Create parameterized SqlCommand for updates (update conf set value=@.value where setting=@.setting).
2) Start database transaction.
3) Iterate through you string dictionary and on every turn evaluate @.value and @.setting parameters again and then execute the command.
4) If there is errors then roll back database changes, otherwise commit the changes
5) Dispose the update command

Keep prior select results in the Results pane

I run query A in SQL server (Management Studio). I slightly change the query and want to run it again. How do I keep the results of the prior run in the Results pane so that I can compare them.

Thanks,

Skender

You can open mulitple query Windows to do that, or just copy the results out to another window.

Buck Woody

|||

Oracle had a code you put in and saves the results in the same window (spool on). I was hoping SQL server has the same thing.